One Index, and Write Throughput Craters Anyway
A Postgres user_sessions table has exactly one index: a B-tree
on last_active_at, which is updated on essentially every request.
Write throughput is far worse than "one extra index write per update"
would suggest, and table bloat / autovacuum load is unusually high —
for a table with only a single index.
Beyond simple write-count scaling with the number of indexes, what's actually happening?
Updating an indexed column disables Postgres's HOT (Heap-Only Tuple) optimization — the single biggest fast path for cheap updates.
Normally, when Postgres updates a row and there's free space on the same heap page, it can write the new row version in place without touching any index at all — as long as no indexed column's value changed, the existing index entry still correctly points to the (unchanged) key, and Postgres's internal HOT-chain mechanism resolves visibility without extra index maintenance. This is what makes updates on tables with several indexes on other columns reasonably cheap.
The moment an UPDATE changes a value that is indexed — here,
last_active_at, updated on nearly every request specifically
because it's the column being tracked — HOT is disabled for that
update: Postgres must write a fresh index entry (the old one now
points to a stale value) in addition to a new heap tuple, and the old
heap tuple becomes a dead tuple only VACUUM reclaims. So the real
cost isn't "1 index = 1 extra write" — it's "the one index that
exists sits on exactly the column guaranteed to defeat the biggest
single update-path optimization Postgres has," producing bloat and
vacuum pressure wildly disproportionate to the index count.
Fix: avoid indexing a column that changes on (nearly) every write
when you can — bucket last_active_at to a coarser granularity (e.g.
5-minute buckets) before indexing it, or move high-churn "liveness"
tracking to a separate store better suited to constant writes (a
cache like Redis, or a table with no index at all on the hot column),
keeping the indexed table's hot-path columns stable.
Share this question