An index is a promise to maintain another useful representation of the data. The query that motivates it receives the obvious benefit, while writes, storage and maintenance receive much of the bill. I would evaluate an index as a workload-level change rather than an isolated improvement to one slow SELECT.
Treat the index as maintained state
PostgreSQL's index introduction makes the basic tradeoff explicit: indexes speed retrieval but add overhead to data changes. That overhead is not accurately described as one extra disk write per index. Page locality, caching, WAL, page splits, uniqueness checks and the shape of an update all affect the actual work. A small index whose upper pages remain hot behaves differently from a large randomly accessed index. I would begin by asking which operations will maintain the structure and which queries will use it, then measure the resulting mix.
Consider an illustrative service with two thousand writes per second and a reporting query executed twice per hour. An index that saves ten seconds on each report but adds a small cost to every write might still be worthwhile if those reports are critical. The decision cannot follow from the report's percentage improvement alone. It needs a value judgement about latency objectives, capacity and operational cost. Conversely, an index supporting a rare uniqueness constraint may be essential even when no read query uses it. Its purpose is correctness, which should be recorded separately from retrieval speed.
References: [1] PostgreSQL: Introduction to indexes
Match the ordering to the actual query contract
Suppose a PostgreSQL application lists a tenant's newest completed jobs using tenant_id, status, created_at and id. A plausible candidate index begins with equality-filtered tenant and status columns, followed by the ordering columns. The exact choice depends on cardinality, selectivity and the other queries sharing the table. Adding id as a tie-breaker also makes pagination deterministic when several jobs have the same timestamp. The example below is a candidate to investigate, not a universal recommendation. Its usefulness must be checked against representative data and the planner's chosen execution strategy.
I would inspect rows examined, sort work, buffer activity and the difference between estimates and actual rows. A query that looks fast on a small development table may become expensive when one tenant owns most of the records or when completed jobs dominate the dataset. A partial index can sometimes focus maintenance on a useful subset, but its predicate must match the application's queries and its population can change over time. Index design is therefore connected to data distribution and query semantics, not only the spelling of columns in a WHERE clause.
CREATE INDEX jobs_tenant_status_newest
ON jobs (tenant_id, status, created_at DESC, id DESC);
EXPLAIN (COSTS, VERBOSE)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 17 AND status = 'completed'
ORDER BY created_at DESC, id DESC
LIMIT 50;References: [1] PostgreSQL: Introduction to indexes
An update can lose an important optimisation
PostgreSQL's heap-only tuple optimisation can avoid creating new index entries for suitable updates when the updated columns are not referenced by relevant indexes and there is enough room on the same heap page. The documentation includes qualifications, including treatment of summarising indexes. This means adding an ordinary index to a frequently changed column can alter the write path even when the new index is small. The cost is not just maintaining that one structure; it may also remove an optimisation available to updates before the index existed.
A covering index deserves the same scrutiny. Including extra columns can make some reads cheaper, but those columns are now stored in the maintained structure and updates to them can matter. I would compare update patterns, heap-only update rates, WAL volume and table growth before and after the change. The test should include realistic update batches and page occupancy, not only inserts into an empty table. A read improvement can be valuable enough to justify the extra work, but the evidence should show the entire change in behaviour rather than assuming included data is free.
References: [2] PostgreSQL: Heap-only tuples
Estimate the recurring bill with stated assumptions
An illustrative capacity estimate can make the tradeoff concrete before benchmarking. Assume an index adds an average of 80 bytes of stored entry data per row across 100 million rows. That is 8 GB in decimal units before accounting for page overhead, free space, alignment and other implementation details. If the workload changes 5 million relevant rows daily, the maintained logical entry volume is already substantial, but multiplying 5 million by 80 bytes is not a prediction of physical I/O. Page writes and logging make that relationship workload dependent.
The estimate is useful as a lower-level planning prompt, not an exact cost model. Ask whether the index fits in the available cache, how much larger backups become and whether replicas can keep up with the additional WAL. A read replica does not make write maintenance disappear: it still has to reproduce relevant state changes. I would also examine maintenance windows and available disk headroom for building or rebuilding the structure. An index that is inexpensive once settled may still require a carefully controlled construction process on a busy large table.
References: [1] PostgreSQL: Introduction to indexes
Evaluate a portfolio rather than accumulating fixes
Indexes often accumulate one incident at a time. A new query gets a new index, while older overlapping structures remain because their purpose is unclear. I would maintain an inventory connecting each index to a constraint or a bounded set of important query shapes. Similar column lists do not automatically mean one index is redundant: ordering, predicates, included columns and uniqueness can change the contract. Equally, two individually reasonable indexes may together impose more write cost than the workload can justify.
Usage statistics need interpretation. A quiet reporting index may be essential at month-end; a low scan count can hide a high-value latency objective; a constraint-supporting index should not be removed because it is rarely read. Observe a representative business cycle and review code paths before removing a structure. The counterargument is that this process feels heavy for a small table. That is fair. For small low-write datasets, simplicity and developer time can dominate. The level of investigation should scale with the write rate, table size and consequence of getting the decision wrong.
References: [1] PostgreSQL: Introduction to indexes
Separate query proof from production rollout
A useful plan on a staging copy does not establish that creating the index during peak traffic is harmless. Database engines offer different online or concurrent construction mechanisms, each with its own restrictions and failure states. I would check the exact version's documented procedure, transaction restrictions, lock interactions and cleanup path rather than assuming the word concurrent means no operational effect. Construction still reads data, consumes CPU and writes new state. Long-running transactions and concurrent schema work can make the surrounding process more complicated than the final index definition suggests.
The rollout should have an observable budget: expected duration range, acceptable replication lag, disk headroom and a way to identify blocked work. Cancel or pause according to the supported mechanism if those limits are exceeded. After construction, verify the actual production plan and write behaviour because statistics and data skew may differ from the test environment. This is also where a narrowly scoped rollback matters. Dropping the candidate may restore some cost, but it does not undo every page split, queued request or temporary capacity impact that occurred while building it.
References: [1] PostgreSQL: Introduction to indexes
Choose evidence that includes both sides
My acceptance criteria would pair the target read improvement with write latency, throughput, WAL generation, storage growth and replica health. Use the same realistic workload before and after the change and keep durability settings constant. Report tail latency when the application depends on it; a faster median can coexist with worse contention for the most important transactions. Where a benchmark cannot represent production skew, say so and use a gradual rollout or an observational period to close that uncertainty.
The broader principle is that derived structures should have an owner and a reason to exist. Indexes are often the right answer, and refusing them because writes become slightly more expensive would be as simplistic as adding them for every query. The useful question is whether the maintained representation buys enough retrieval speed, ordering or correctness for its recurring cost. Framing the decision that way turns index tuning from a series of local rescues into deliberate control over how the database spends work across the whole application.
References: [1] PostgreSQL: Introduction to indexes[2] PostgreSQL: Heap-only tuples
Sources and further reading
- PostgreSQL: Introduction to indexes
Primary documentation establishes retrieval benefits and update overhead. The candidate schema, capacity figures and rollout criteria are illustrative engineering analysis.
- PostgreSQL: Heap-only tuples
Primary documentation defines conditions for PostgreSQL's HOT optimisation. The essay's workload implications are conditional on those documented conditions.