Postgres Indexes Under Write Load: Every Index Is a Tax on Every Write

An index speeds up reads and slows down writes. That trade is well known and almost never quantified, so tables accumulate indexes nobody removes.
The cost is larger than it looks. Every INSERT updates every index on the table. Every update that changes an indexed column updates that index — and critically, an update that touches any indexed column may lose the HOT (heap-only tuple) optimization, which turns a cheap in-page update into one that writes to every index on the table. One badly chosen index can measurably slow writes that do not even reference it.
Three techniques give you most of the read benefit at a fraction of the write cost.

1. Partial indexes If your query always filters on a predicate, put the predicate in the index: -- Full index: every row, updated on every insert. CREATE INDEX idx_orders_status ON orders (status);

-- Partial: only rows in a state you actually query, and one that is a
-- small fraction of the table. Rows that never match are never indexed,
-- so inserting a completed order touches this index not at all.
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status IN ('pending', 'processing');
On a table where 99% of rows are completed, the partial index is roughly 1% of the size, and — because completed orders never enter it — inserting and updating them skips it entirely. This is the single highest-leverage index pattern in Postgres and it is underused.
The catch: the planner will only use it if it can prove your query’s WHERE clause implies the index predicate. WHERE status = 'pending' works. WHERE status = ANY($1) with a parameter array does not, because the value is not known at plan time. Check with EXPLAIN using realistic parameters, not literals.

1. Covering indexes with INCLUDE An index-only scan avoids touching the heap at all — but only if every column the query needs is in the index. INCLUDE adds payload columns without making them part of the search key: CREATE INDEX idx_orders_customer_covering ON orders (customer_id, created_at DESC) INCLUDE (total_cents, status); INCLUDE columns live only in leaf pages, so they do not bloat internal nodes or affect the sort order. The index is larger than a plain one but supports index-only scans. Index-only scans depend on the visibility map, which is maintained by VACUUM. On a heavily-updated table with lazy autovacuum, the visibility map is stale and the “index-only” scan falls back to heap fetches anyway. Check Heap Fetches in EXPLAIN (ANALYZE, BUFFERS) — if it is high, tune autovacuum before adding more covering indexes.
2. Find what you are paying for UNUSED_INDEXES = """ SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, pg_relation_size(s.indexrelid) AS size_bytes, s.idx_scan FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid WHERE s.idx_scan < %(min_scans)s AND NOT i.indisunique -- unique indexes enforce constraints AND NOT i.indisprimary AND pg_relation_size(s.indexrelid) > %(min_bytes)s ORDER BY pg_relation_size(s.indexrelid) DESC; """

DUPLICATE_INDEXES = """
-- Indexes whose column list is a PREFIX of another index's column list.
-- (a) is redundant if (a, b) exists: the composite serves both.
SELECT a.indexrelid::regclass AS redundant,
b.indexrelid::regclass AS covered_by,
pg_size_pretty(pg_relation_size(a.indexrelid)) AS wasted
FROM pg_index a
JOIN pg_index b
ON a.indrelid = b.indrelid
AND a.indexrelid <> b.indexrelid
AND array_to_string(b.indkey, ' ') LIKE array_to_string(a.indkey, ' ') || '%%'
WHERE NOT a.indisunique AND NOT a.indisprimary;
"""

WRITE_AMPLIFICATION = """
-- Index count and total index bytes per write. The ratio of index size to
-- table size is a rough proxy for how much each write costs you.
SELECT
t.relname AS table_name,
count(i.indexrelid) AS index_count,
pg_size_pretty(pg_relation_size(t.oid)) AS table_size,
pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS index_size,
round(sum(pg_relation_size(i.indexrelid))::numeric
/ NULLIF(pg_relation_size(t.oid), 0), 2) AS index_to_table_ratio,
s.n_tup_ins + s.n_tup_upd + s.n_tup_del AS writes
FROM pg_class t
JOIN pg_stat_user_tables s ON s.relid = t.oid
LEFT JOIN pg_index i ON i.indrelid = t.oid
WHERE t.relkind = 'r'
GROUP BY t.relname, t.oid, s.n_tup_ins, s.n_tup_upd, s.n_tup_del
HAVING count(i.indexrelid) > 3
ORDER BY index_to_table_ratio DESC;
"""

def audit(conn, min_scans: int = 50, min_bytes: int = 10 * 1024 * 1024) -> dict:
"""An index-to-table ratio above ~1.5 with high write volume is a strong
candidate for pruning. Read idx_scan with care: stats reset on
restart and on pg_stat_reset(), so a 'never used' index may just be
new. Check pg_stat_get_db_stat_reset_time() before believing it."""
with conn.cursor() as cur:
cur.execute("SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()")
reset_at = cur.fetchone()[0]
cur.execute(UNUSED_INDEXES, {"min_scans": min_scans, "min_bytes": min_bytes})
unused = cur.fetchall()
cur.execute(DUPLICATE_INDEXES)
dupes = cur.fetchall()
cur.execute(WRITE_AMPLIFICATION)
amplification = cur.fetchall()
return {
"stats_since": reset_at,
"unused": unused,
"wasted_bytes": sum(r[4] for r in unused),
"redundant": dupes,
"write_amplification": amplification,
}
The stats_since field is not decoration. Dropping an index because idx_scan = 0 when statistics were reset two days ago is how you delete the index that serves the monthly reporting job.
Dropping safely
Postgres 12+ can deactivate an index without dropping it:
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_status'::regclass;
-- Planner now ignores it. Writes still maintain it, so this tests the READ
-- impact only - but that is the risky half. Watch for a week, then DROP.
And always build with CONCURRENTLY on a live table:
CREATE INDEX CONCURRENTLY idx_new ON orders (customer_id);
-- No ACCESS EXCLUSIVE lock. Slower, two table passes, and it can leave an
-- INVALID index behind if it fails - check indisvalid afterwards and drop
-- the corpse, or the next CREATE INDEX CONCURRENTLY will trip over it.
The habit worth building
Add index review to the same cadence as dependency updates. Every quarter: run the audit, drop what is genuinely unused, and convert full indexes to partial where a predicate is stable.
The reason this matters more over time is that indexes are added in response to a slow query and essentially never removed when that query changes. After three years a hot table has eleven indexes, four of which serve queries that no longer exist, and every write pays for all eleven.

We do data platform and database performance work at SoluLab — the InfuseNet case study covers one at scale.