Lesson 11 · Storage engines · Module 1

What an Index Costs

Lesson 08 ended on a one-line invoice: every index taxes every write. This lesson opens the invoice. The surprising item on it is that an index on status can slow down an UPDATE that only ever touches last_seen_at — a column the index has never heard of.

The win in this lesson: you will be able to explain why adding an index slows writes that do not mention the indexed column, and what a table's fillfactor has to do with it.

1. The complaint

The shape of the report is always the same. A team adds an index to fix a slow dashboard query. The dashboard gets fast. A week later a completely unrelated write path — a heartbeat, a counter, a last_seen_at touch — is measurably slower, and the table on disk is growing faster than the row count is.

Nobody changed that write path. The index is not on any column it writes. The usual first theory is lock contention, and the usual first theory is wrong.

The real answer is that in PostgreSQL an UPDATE is not an edit. It is an insert plus a tombstone, and whether the indexes get dragged into it depends on something as unglamorous as how much empty space happened to be left on one 8 KB page.

2. Mechanism: an UPDATE writes a new row

Why the old version survives

"In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC…): the row version must not be deleted while it is still potentially visible to other transactions."

PostgreSQL 18 documentation, Routine Vacuuming · 24.1.2 Recovering Disk Space

That is the whole of MVCC in one sentence, and it is a good trade: readers never block writers, because readers can still see the version that was current when they started. The price is that every UPDATE leaves litter behind — a dead tuple, a row version nobody will ever read again, still occupying space until something clears it.

Why the indexes get dragged in

Now the part that produces the complaint. An index entry does not point at a row; it points at a physical location — a page number and an item slot. Write the new version somewhere else, and every index that referenced the old location is now wrong, so every index needs a new entry:

"To allow for high concurrency, PostgreSQL uses multiversion concurrency control (MVCC) to store rows. However, MVCC has some downsides for update queries. Specifically, updates require new versions of rows to be added to tables. This can also require new index entries for each updated row, and removal of old versions of rows and their index entries can be expensive."

PostgreSQL 18 documentation, 66.7 Heap-Only Tuples (HOT)

Every index. Not the indexes whose columns changed — all of them, because all of them are holding a pointer that just went stale. That single sentence is the answer to the lesson's question: the index on status is rewritten by an update to last_seen_at because it was pointing at where the row used to live.

The escape hatch: heap-only tuples

Postgres has an optimisation for exactly this waste, and it is precise about when it applies:

"To help reduce the overhead of updates, PostgreSQL has an optimization called heap-only tuples (HOT). This optimization is possible when:

• The update does not modify any columns referenced by the table's indexes, not including summarizing indexes. The only summarizing index method in the core PostgreSQL distribution is BRIN.

• There is sufficient free space on the page containing the old row for the updated row."

PostgreSQL 18, 66.7 Heap-Only Tuples (HOT)

Read those two conditions as one sentence: if the new version can stay on the same page, and no indexed column changed, the indexes never have to know the update happened. The old item slot becomes a redirect and the index pointer stays valid.

HOT pays twice, and the second payment is the one people forget:

"New index entries are not needed to represent updated rows, however, summary indexes may still need to be updated."

"When a row is updated multiple times, row versions other than the oldest and the newest can be completely removed during normal operation, including SELECTs, instead of requiring periodic vacuum operations."

PostgreSQL 18, 66.7 Heap-Only Tuples (HOT)

So a HOT update does not merely skip the index writes. Its litter can be swept up by ordinary traffic — even by a SELECT — instead of waiting for VACUUM. A non-HOT update does neither.

3. What it costs

Put the two paths side by side. Assume a table with three indexes and an UPDATE that touches one non-indexed column.

Three things compound here. The write amplification is the obvious one. The second is WAL volume: dirty pages become WAL records, WAL is what replicas consume, so a table that has stopped doing HOT updates quietly raises replication lag everywhere downstream — Lesson 01's problem, arriving from an unexpected direction.

The third is that the debris does not clean itself:

"The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained."

PostgreSQL 18, Routine Vacuuming · 24.1.2

Which sets up the vicious circle worth naming out loud. Dead tuples consume the free space on a page. Less free space means fewer HOT updates. Fewer HOT updates means more dead tuples, in the table and in every index. A table can fall off the HOT path and stay off it.

4. The knobs

Knob one: fillfactor

The second HOT condition — free space on the page — is the one you can actually buy:

"The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page, and makes heap-only tuple updates more likely."

PostgreSQL 18 documentation, CREATE TABLE · Storage Parameters

And the docs give the rule for who should pay for it: "For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate."

That is the trade, stated plainly. A lower fillfactor deliberately wastes disk and makes every sequential scan read more pages, in exchange for keeping updates HOT. You are buying write cost with read cost. On an append-mostly table that is a bad deal; on a row you rewrite forty times a day it is an excellent one.

"You can increase the likelihood of sufficient page space for HOT updates by decreasing a table's fillfactor. If you don't, HOT updates will still happen because new rows will naturally migrate to new pages and existing pages with sufficient free space for new row versions. The system view pg_stat_all_tables allows monitoring of the occurrence of HOT and non-HOT updates."

PostgreSQL 18, 66.7 Heap-Only Tuples (HOT)

Measure before you turn the knob. The ratio is a single query:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM   pg_stat_all_tables
WHERE  n_tup_upd > 0
ORDER  BY n_tup_upd DESC
LIMIT  20;

-- and, if hot_pct is low on a heavily updated table:
ALTER TABLE sessions SET (fillfactor = 80);
-- note: existing pages keep their current packing until rewritten.

Indexes have a fillfactor too, and it means something related but distinct — how full B-tree leaf pages are packed at build time. The default is 90, and the docs are blunt about raising it: "A fillfactor setting of 100 otherwise risks harming performance: even a few updates or inserts will cause a sudden flood of page splits." That is Lesson 08's cascading page split, priced.

Knob two: CREATE INDEX CONCURRENTLY

The other cost of an index is the moment you add it. A plain CREATE INDEX on a live table is an outage:

"When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done."

PostgreSQL 18 documentation, CREATE INDEX · CONCURRENTLY

It is not free, and the docs say exactly what it costs: "PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete."

That is the same bargain as every other availability mechanism on this course: more total work, spread out, so that nobody is blocked. Two operational consequences follow, and both bite people.

-- Cannot run inside a transaction block, so most migration
-- frameworks need an explicit escape hatch for this statement.
CREATE INDEX CONCURRENTLY idx_sessions_status ON sessions (status);

-- If it fails, it leaves a broken index behind. Check for them:
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY idx_sessions_status;  -- then try again

"If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an 'invalid' index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead."

PostgreSQL 18, CREATE INDEX · Building Indexes Concurrently

Read that last clause twice. A failed concurrent build leaves you with the worst possible object: an index that helps no query and taxes every write. It is this lesson's thesis in its purest form.

5. Residual risk

Suppose you do all of it — audit the indexes, drop the unused ones, set a sensible fillfactor, build concurrently. What can still go wrong?

Fillfactor is not retroactive. Setting it changes how future page packing works. Pages already packed to 100% stay that way until they are rewritten, so the improvement arrives gradually, or not at all on a table that is mostly updated rather than inserted. Getting it immediately means a table rewrite — VACUUM FULL or CLUSTER — and the docs are clear that these "require an ACCESS EXCLUSIVE lock". You have converted a slow write path into a maintenance window.

One new indexed column can undo all of it. The first HOT condition is binary and table-wide. The day someone adds an index on the column your hot write path updates — a status, a updated_at, a counter — every update on that table stops being HOT at once. The change looks like one line in a migration and behaves like a step change in write cost.

Autovacuum is a background process competing with your workload. The docs note that "VACUUM creates a substantial amount of I/O traffic, which can cause poor performance for other active sessions." Push more dead tuples at it and you do not get a clean table; you get a heavier background job at exactly the times you are busiest. And when it falls far enough behind, it stops being a performance problem and becomes the availability problem from Lesson 07.

The honest summary: HOT is an optimisation, not a guarantee. You can make it likely. You cannot make it certain, and nothing in the query text tells you which path a given UPDATE took — only pg_stat_all_tables does, after the fact.

6. Check yourself

7. Back to your world

Two queries, ten minutes, on whatever database you are responsible for. First, list every index with idx_scan = 0 from pg_stat_user_indexes: those are pure cost, and now you know the cost is not only inserts but the updates they push off the HOT path. Second, find your most-updated tables and read n_tup_hot_upd against n_tup_upd. A heavily updated table sitting near zero per cent HOT is telling you something specific — either an indexed column is churning, or the pages have no room.

Then carry the habit into the next design review. When someone proposes an index, the question is no longer "will it help the query" but "which write path does it take off the HOT path, and how often does that path run?"