How to Choose a Database Index Without Slowing Writes
To choose a database index, index the columns your queries filter, join and sort on most often, and expect every index you add to make inserts, updates and deletes a little more expensive. The skill is in judging whether a given read gets faster by more than the corresponding writes get slower. This guide covers how to choose a database index by looking at the actual mechanics: which columns earn their place, how composite and covering indexes behave, and what each one costs you in write throughput and storage.
What an index actually costs on write
Most relational engines store a table either as a heap with separate index structures (PostgreSQL, SQL Server) or as a clustered index where the table rows live inside the primary key's B tree (InnoDB, and SQL Server when a clustered index exists). Either way, an index is a balanced tree, usually a B+ tree, keyed on the indexed columns plus a pointer back to the row. A write that touches an indexed column has to locate the right leaf page in every affected tree, insert or delete the entry, and possibly split or merge pages.
That extra work is not free, and it is not constant. A random insert into a large index causes page splits and more I/O than an append at the end of the tree. So a monotonically increasing key such as an auto-increment integer or a timestamp is cheaper to maintain than a random key such as a UUID, because the writes land in the same rightmost pages. On a write-heavy table, several secondary indexes can dominate the cost of an insert. The row itself is one write; each index is another.
You can see the write cost directly in the engine's own statistics. On PostgreSQL, for example, look at how many times each index has been updated versus how often it has been scanned:
SELECT relname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
An index with a high idx_tup_read and a low idx_scan is doing very little reading. If it also sits on a hot table, it is a candidate for removal. Run SELECT pg_stat_reset(); if you want a clean window, but be aware that resets are global and blunt.
How to choose database index columns from your query workload
Start from queries, not from tables. Collect the statements that matter: the ones on the critical path, the ones that show up in slow query logs, the ones your application calls on every page load. For each, write down the columns in its WHERE clause, its JOIN conditions, its ORDER BY and its GROUP BY. Those are your candidates.
Then check what the planner already does. On PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) shows whether the engine uses a sequential scan, an index scan or a bitmap heap scan, and how many buffers each step touched. On MySQL, EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE gives the equivalent. A column that appears in a filter but is never selective, meaning it matches a large fraction of rows, is usually a poor single-column index: the planner will often prefer a sequential scan anyway, and you have paid the write cost for nothing.
Selectivity is the guiding principle. An index on a column with a handful of distinct values, such as a boolean flag or a status enum with three states, rarely helps on its own. An index on a column with many distinct values, such as an email address or a foreign key, is usually worth it. The exception is when the low-cardinality column is combined with something else in a composite index, or when a partial index can restrict the tree to the small slice you actually query.
Partial and expression indexes
If most of your queries only touch a subset of rows, a partial index keeps the tree small and the write cost low. In PostgreSQL:
CREATE INDEX CONCURRENTLY idx_orders_open
ON orders (customer_id, created_at)
WHERE status = 'open';
The CONCURRENTLY keyword builds the index without blocking writes, at the cost of taking longer and needing to be retried if it fails. Use it on live tables. Expression indexes work the same way for computed predicates, such as lower(email), but only if your queries use the same expression.
Composite indexes and the leftmost prefix rule
A composite index on (a, b, c) can serve queries that filter on a, on a and b, or on all three, because the tree is ordered by those columns in that sequence. It generally cannot serve a query that filters only on b or only on c. That is the leftmost prefix rule, and it is the single most common reason a composite index goes unused.
Order the columns by how you query them. Put equality predicates first, then range predicates, then the column you sort on. A query like WHERE tenant_id = ? AND created_at > ? ORDER BY created_at wants the index (tenant_id, created_at). Reversing the order gives you a tree that can filter on created_at but has to scan across tenants to find the right one, which is much worse. One well-ordered composite index often replaces two or three single-column indexes, which is a net saving on writes.
Watch for redundant indexes. If you have (a) and (a, b), the single-column index is almost always unnecessary because the composite covers every query the single one would serve. Drop it and you remove a write cost from every insert.
Covering indexes and index-only scans
A covering index includes every column a query needs, so the engine can answer from the index alone without visiting the table heap. In PostgreSQL this is called an index-only scan; in MySQL it is a covering index scan; in SQL Server it is done with INCLUDE columns. The benefit is fewer page reads per query. The cost is a wider index, more storage and more write work, because the extra columns must be maintained.
Add covering columns via INCLUDE where the engine supports it, so they are stored in the leaf pages but not used for ordering:
CREATE INDEX idx_users_email_cover
ON users (email) INCLUDE (display_name, created_at);
Only do this when a specific, frequent query benefits and the extra columns are small. A covering index that includes a large text or blob column will bloat storage and slow every write to the table. Check the plan afterward: an index-only scan shows up as Index Only Scan in PostgreSQL's EXPLAIN, and the Heap Fetches figure tells you how often it still had to touch the table.
Verify, then iterate
Every index decision is a hypothesis, so test it. Create the index on a copy of production data if you can, run the real queries, and compare the plan and the timing. Watch the write side too: measure insert throughput before and after, and check the index sizes with SELECT pg_size_pretty(pg_relation_size('index_name')); or the equivalent in your engine. If a new index does not change the plan for the queries you built it for, drop it.
Next, take one slow query from your log, run EXPLAIN (ANALYZE, BUFFERS) on it, and note which columns the filter and sort touch. Compare that list against your existing indexes and see whether a single well-ordered composite index would cover the query. Make that one change, measure both the read and the write side, and keep it only if the trade is clearly in your favour. Then move to the next query.
