The question sounds naive. The answer reveals everything about how databases actually work under load.
This question comes up in database engineering interviews because the correct answer has layers. You can tell whether someone truly understands database internals by how they respond. A shallow answer is “indexes take disk space.” A good answer explains exactly what happens to your write throughput when you over-index. A great answer comes with numbers.
The short version: every index you add to a table is a tax on every write. Not just storage. Not just disk. Every INSERT, UPDATE and DELETE on that table now has to maintain every index you created, whether that index is being used by any current query or not. The database does not wait for a convenient moment. It does it inline, synchronously, before the transaction completes.
This is where the interview question gets interesting.
What an Index Actually Is
Before the costs, a quick grounding in mechanics. When you create an index on a column, PostgreSQL builds a separate data structure (a B-tree by default) that maps column values to the physical locations of rows on disk. The B-tree keeps itself balanced: every path from root to leaf is the same depth, regardless of how much data you have. This is why indexed lookups are O(log n) instead of O(n).
The structure looks like this:
- At the top: a root page with high-level range dividers
- In the middle: internal pages pointing to narrower ranges
- At the bottom: leaf pages containing the actual (value, row pointer) pairs
A query using an index walks from root to the matching leaf, follows the row pointer to the actual heap storage, and fetches the data. Three to five page reads instead of a full table scan. On a table with millions of rows, this is the difference between 1ms and 800ms.
This is why indexes exist. The problem is what people skip when reading that explanation: the leaf pages store a copy of the indexed value plus a pointer for every row in the table. That copy costs something. And it keeps costing something every time the underlying data changes.
The First Cost: Write Amplification Is Proportional to Index Count
Here is the arithmetic that most engineers do not do when they create an index.
Assume a table with five indexes. Every INSERT does not write one record. It writes one row to the heap, plus one index entry into each of the five index structures. Five additional writes, each potentially hitting a different part of the index B-tree. The effective write overhead is 5x the base cost. Not 5x total cost, but the write path is five times as long for the index portion.
Now scale that to UPDATE. An UPDATE that changes an indexed column has to delete the old index entry and insert a new one. Two index operations per column changed, per index affected. PostgreSQL has an optimization called HOT updates (Heap Only Tuple) that can skip index updates when the indexed column values do not change, but the moment any indexed column changes, HOT is not applicable.
DELETE has the same overhead. Every index entry pointing to the deleted row has to be marked for cleanup by autovacuum later.
The result: on write-heavy tables, every new index you add slows down every write operation on that table. Forever. Not for the first few weeks while the cache is cold, but permanently, because index maintenance is not optional.
What the Numbers Look Like in Production
Percona published a controlled benchmark using PostgreSQL 17.4 on a Dell PowerEdge server with AMD EPYC 7452 processors and 1 TiB of RAM. Not a toy setup. They ran a mixed INSERT/UPDATE/SELECT workload with varying index counts across the same schema.
The result was clear: throughput dropped from approximately 1,400 transactions per second with 7 indexes down to approximately 600 TPS with 39 indexes. That is 42% of original throughput.
Average transaction time moved from 11ms to 26ms as index count went from 7 to 39.
The important detail: the PostgreSQL cache hit ratio stayed above 99.7% throughout. The performance loss was not from disk I/O. Data was coming from memory. The bottleneck was the CPU and memory overhead of maintaining index structures in the shared buffer pool during write operations. Unused indexes competing for cache space meant less room for the table data pages that the queries actually needed.
This is the number worth knowing. In a system where every row in every table had an index on every column, you would not be running at “somewhat reduced” throughput. You would be fighting your database for every write.
The Second Cost: Indexes Live in Memory Whether You Use Them or Not
Index pages have to remain in the shared buffer pool because write operations need them. Every INSERT dirtying an index page forces that page into memory. The page competes for cache space with table heap pages that queries are actually reading.
The consequence is indirect but real: as you add more indexes, the effective usable memory for your working dataset shrinks. A query doing a full scan of a hot table now has to share cache with the index pages for fifteen other columns that no query is actually using for filtering.
This is why you can run a well-tuned database with a 99.7% cache hit ratio and still lose 58% of write throughput to index overhead. The cache hit ratio is measuring reads, not writes. The writes are the problem.
The Third Cost: WAL Multiplication
Write-Ahead Logging (WAL) exists for crash recovery and replication. Every change to a PostgreSQL table generates a WAL record. What gets less attention: every change to an index page also generates a WAL record.
With 15 indexes on a table, an INSERT generates roughly 16 WAL records instead of 1. More WAL means more I/O to disk for WAL writes, more data streamed to read replicas, more WAL to replay after a crash, and longer recovery times.
This scales. On high-write tables, WAL volume from index maintenance becomes a measurable fraction of total WAL output. More WAL means slower replication lag. Slower replication lag means your read replicas are returning stale data for longer windows than you expect.
The Fourth Cost: Autovacuum Gets Slower
PostgreSQL uses autovacuum to clean up dead tuples left behind by UPDATEs and DELETEs. The more indexes a table has, the more index entries autovacuum must process during cleanup. It is not unusual for autovacuum to appear “stuck” on a table for hours, showing no visible progress, when the actual work happening is index cleanup behind the scenes.
Index bloat is a related problem. Indexes accumulate dead entries over time. A table that sees a lot of UPDATE and DELETE traffic will eventually have indexes with significant wasted space. This increases their size, which increases the memory they consume, which makes the write amplification problem worse.
The fix for index bloat is REINDEX. Running REINDEX locks the table (REINDEX CONCURRENTLY is available but still adds overhead). If you have 40 indexes, REINDEX is not a quick operation.
The Fifth Cost: Low-Cardinality Columns Get You Nothing
This is the one that surprises people. Even if you do index every column, the query planner may not use many of those indexes.
A column with low cardinality (few distinct values) is a weak candidate for a B-tree index. Consider a status column with four possible values: active, inactive, pending, deleted. If your table has 10 million rows and 70% of them are active, an index on status = 'active' is near useless. PostgreSQL's query planner will calculate that reading 7 million rows via an index scan (each row requiring a separate disk lookup) is slower than a single sequential scan of the whole table.
The planner uses statistics collected by ANALYZE to estimate how selective each column is, and for low-cardinality columns it will often skip the index entirely and scan the table directly. You pay the write amplification cost and the memory cost. The index takes up space, slows your writes, and never gets used. It just sits there maintaining itself on every INSERT.
Index selectivity is the fraction of rows an index condition actually eliminates. A useful index eliminates most rows. Indexing user_id on a user_events table might eliminate 99.99% of rows for a single user. Indexing country on the same table might eliminate 3%.
What the Good Answer Sounds Like
When an interviewer asks why you would not index every column, the answer they are looking for goes something like this:
Every index is a write tax. Inserts, updates and deletes have to maintain all indexes on the table, whether those indexes are being used by active queries or not. In a Percona benchmark on PostgreSQL 17 with a mixed workload, going from 7 to 39 indexes dropped throughput from 1,400 TPS to 600 TPS with data fully in memory. The cache hit ratio was above 99.7%, so the loss came entirely from index maintenance overhead. Beyond throughput, indexes consume shared buffer space, multiply WAL volume and slow autovacuum. And for low-cardinality columns, the planner may not use the index at all, so you get the write cost with no read benefit.
The right approach is to index selectively: columns that appear in WHERE clauses with high selectivity, join keys, and columns used for sorting in expensive queries. Not everything.
How to Find the Indexes You Are Already Paying For Without Benefit
If this is making you wonder about your current schema, PostgreSQL makes it easy to check. The pg_stat_user_indexes view tracks how many times each index has been used since statistics were last reset:
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan AS times_used,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
last_idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;This returns every index with zero recorded scans, sorted by how much space it is wasting. The last_idx_scan column (added in PostgreSQL 16) tells you the last time the index was actually used. Useful for catching indexes that were busy last quarter but have been idle since a query rewrite changed the access pattern.
Before dropping anything from this list: verify the statistics have not been recently reset with pg_stat_reset(). An index that shows zero scans right after a stats reset tells you nothing. Run this query over a representative traffic period, at minimum a full week, before treating any index as safe to remove.
One caution: primary key indexes and unique constraint indexes should not be removed, even if idx_scan is zero. They enforce data integrity and PostgreSQL will rebuild them on constraint creation. Filter for those separately.
The Question Behind the Question
“Why not index every column?” is really asking: do you understand that database performance is a system of trade-offs, not a list of features to enable?
Indexes are not configuration settings. They are data structures that the database maintains in parallel with your actual data. Each one makes reads faster for specific query patterns and makes writes slower for the entire table. The goal is not to maximise index coverage. The goal is to build the minimum set of indexes that satisfies your actual query patterns at the selectivity levels your data distribution supports.
Everything else is debt. And unlike technical debt, index debt has a real-time cost measured in milliseconds per transaction.
