Indexes vs Write Speed: Weighing the Tradeoff on Busy PostgreSQL Tables

Overview

After a few rounds of "add an index to fix that slow query", a busy table can end up with a dozen indexes. Reads are fast, but inserts and updates have slowed, vacuum takes longer, and storage has grown. Indexes are not free, and on write-heavy tables the cost is real.

This guide gives a practical way to decide which indexes earn their place.

1. Understand What an Index Costs

Every index on a table must be maintained when rows change:

• An INSERT adds an entry to every index on the table.
• A DELETE leaves dead entries that vacuum must later clean up in every index.
• An UPDATE creates a new row version. If the update cannot use a heap-only tuple (HOT) update, for example because an indexed column changed or the page is full, every index gets a new entry.

So a table with eight indexes does roughly nine pieces of write work per insert: one for the table and one for each index. This is write amplification. Indexes also consume disk and compete for memory in shared buffers, pushing out data that queries actually need.

2. Find Indexes That Are Not Used

PostgreSQL tracks index usage in pg_stat_user_indexes. Look for indexes with zero scans.

SELECT s.schemaname,
       s.relname      AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique        -- keep unique and primary key indexes
ORDER BY pg_relation_size(s.indexrelid) DESC;

Be careful interpreting this:

• Counters reset when statistics are reset, and some failover or restore operations reset them. Check pg_stat_database.stats_reset to see how long the data covers.
• Usage may be seasonal. An index used only by a monthly report will show zero scans for weeks.
• On replicas, counters are tracked separately. An index unused on the primary may serve read queries on a replica.
• Indexes backing unique, primary key or exclusion constraints enforce rules even when never scanned.

Collect stats over a full business cycle before deciding.

3. Look for Redundant Indexes

Two indexes can overlap. An index on (customer_id) is usually redundant if you also have (customer_id, created_at), because the multicolumn index can serve queries on its leading column. Compare index definitions per table.

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY indexname;

Not every overlap is removable. A smaller single-column index can be faster for some queries and sorts, so test before dropping.

4. Use Partial Indexes to Index Less

If queries only touch a slice of the table, index only that slice. A partial index is smaller, cheaper to maintain, and only updated when a row matches its predicate.

-- Only a small share of orders are pending at any time
CREATE INDEX CONCURRENTLY idx_orders_pending_created
    ON orders (created_at)
    WHERE status = 'pending';

The planner can use it only when the query's WHERE clause implies the index predicate. A query with status = 'pending' qualifies; a query with a parameter the planner cannot prove matches may not. Always confirm with EXPLAIN.

5. Drop Safely

Dropping an index is hard to undo on a large table because rebuilding takes time. Use a cautious process:

1. Confirm zero usage over a representative period, including replicas.
2. Save the definition from pg_indexes so you can recreate it.
3. Drop without blocking: DROP INDEX CONCURRENTLY idx_name;
4. Watch query latency and slow-query logs afterwards.

DROP INDEX CONCURRENTLY cannot run inside a transaction block, and it cannot be used on indexes backing constraints.

6. Decide With the Workload in Mind

A rough way to weigh a candidate index:

• How often does the query run, and how slow is it without the index?
• How many writes per second hit this table?
• How large will the index be, and will it fit in memory with the rest of the working set?
• Could a partial or covering design reach the same goal with less cost?

A read-mostly reporting table can afford many indexes. A high-volume event or queue table usually cannot. For append-heavy data, also think about whether partitioning or a separate reporting copy is a better answer than yet another index.

Conclusion

Treat indexes as a budget. Measure which ones are used, remove redundant or dead ones carefully, and prefer partial indexes when queries target a small slice. Verify each change against your real workload.

If a busy table is showing slow writes and bloat and you would like a review of its indexing, I can help. Contact me.

Additional Resources

• PostgreSQL indexes documentation
• PostgreSQL partial indexes
• PostgreSQL cumulative statistics views