HOT Updates and fillfactor
A table taking thousands of updates per second is generating enormous WAL volume and index bloat. HOT updates are the structural fix — when they can happen at all.
PostgreSQL never updates a row in place: an UPDATE writes an entirely new row version and marks the old one dead, which normally means every index on the table needs a new entry pointing at that new version too — real WAL volume and real index growth, on every single update, regardless of whether the indexed columns themselves actually changed. A HOT (Heap-Only Tuple) update is the specific, valuable exception: when the new row version fits on the exact same heap page as the old one, and none of the table's indexed columns changed, PostgreSQL can skip writing any new index entries entirely, linking the old and new versions directly within the page instead.\n\nThe "fits on the same page" condition is where fillfactor comes in: a freshly loaded table at the default fillfactor of 100 packs every page essentially full, leaving no room for a new row version to land alongside the old one — every update is forced onto a different page, and HOT becomes structurally rare no matter how careful the application is. Lowering fillfactor deliberately reserves free space on each page at load time specifically so future updates have somewhere to land. This lab measures the real difference directly, and separately confirms the one condition under which HOT is impossible regardless of fillfactor: the updated column being indexed.
fillfactor=70, Measured
CREATE TABLE sql_hot_demo (id int PRIMARY KEY, counter int, indexed_val int, junk text) WITH (fillfactor=70);
CREATE INDEX ON sql_hot_demo(indexed_val);
INSERT INTO sql_hot_demo SELECT g, 0, g, repeat('a',50) FROM generate_series(1,5000) g;
UPDATE sql_hot_demo SET counter = counter + 1; -- x3 rounds, non-indexed column
SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot
FROM pg_stat_user_tables WHERE relname = 'sql_hot_demo';
n_tup_upd | n_tup_hot_upd | pct_hot
-----------+---------------+---------
15000 | 9468 | 63.1
With 30% of each page deliberately left empty at load time, 63.1% of updates to a non-indexed column found room to stay on the same page — a real, measured majority.
The Same 3 Rounds, at the Default fillfactor=100
CREATE TABLE sql_hot_default (id int PRIMARY KEY, counter int, junk text); -- fillfactor defaults to 100
-- identical 3 update rounds
n_tup_upd | n_tup_hot_upd | pct_hot
-----------+---------------+---------
15000 | 90 | 0.6
The identical update pattern, on a table packed to 100% at load time — only 0.6% HOT. With no reserved free space, almost every update is forced onto a different page, and HOT becomes structurally rare. fillfactor did not make HOT possible in principle; it made room for HOT to actually happen.
Updating the Indexed Column: Zero, Always
UPDATE sql_hot_demo SET indexed_val = indexed_val + 1000000;
SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot
FROM pg_stat_user_tables WHERE relname = 'sql_hot_demo';
n_tup_upd | n_tup_hot_upd | pct_hot
-----------+---------------+---------
20000 | 9468 | 47.3
n_tup_upd grew by 5,000 (this new round of updates), but n_tup_hot_upd stayed at exactly 9,468 — not one of these 5,000 updates was HOT, diluting the cumulative rate down from 63.1% to 47.3%. Changing an indexed column always requires a new index entry, regardless of how much free space the page has.
HOT (Heap-Only Tuple) update
An UPDATE where the new row version is written to the same heap page as the old version, and no indexed column changed value. PostgreSQL links the old and new versions directly within the page and skips writing any new index entries at all — real savings in both WAL volume and index growth, available only when both conditions hold simultaneously.
fillfactor
A per-table (or per-index) storage parameter controlling what percentage of each page PostgreSQL fills when initially writing data — the remainder is deliberately left empty. The default of 100 packs pages completely full, leaving no room for a HOT update's new row version to land nearby; a lower value (commonly 70-90 for heavily-updated tables) trades some extra disk space for headroom that keeps HOT updates possible for longer between VACUUMs.
🔥 Measure the Real HOT Rate at fillfactor=70
Create a table with fillfactor=70 and an indexed column, run 3 rounds of updates on a non-indexed column, and check the real HOT rate.
psql -U postgres -d beer_db -c "CREATE TABLE sql_hot_demo (id int PRIMARY KEY, counter int, indexed_val int, junk text) WITH (fillfactor=70);" -c "CREATE INDEX ON sql_hot_demo(indexed_val);" -c "INSERT INTO sql_hot_demo SELECT g, 0, g, repeat('a',50) FROM generate_series(1,5000) g;" -c "ANALYZE sql_hot_demo;"psql -U postgres -d beer_db -c "UPDATE sql_hot_demo SET counter = counter + 1;" -c "UPDATE sql_hot_demo SET counter = counter + 1;" -c "UPDATE sql_hot_demo SET counter = counter + 1;"psql -U postgres -d beer_db -c "SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot FROM pg_stat_user_tables WHERE relname = 'sql_hot_demo';"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_hot_demo (id int PRIMARY KEY, counter int, indexed_val int, junk text) WITH (fillfactor=70);" -c "CREATE INDEX ON sql_hot_demo(indexed_val);" -c "INSERT INTO sql_hot_demo SELECT g, 0, g, repeat('a',50) FROM generate_series(1,5000) g;" -c "ANALYZE sql_hot_demo;" SET CREATE TABLE CREATE INDEX INSERT 0 5000 ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "UPDATE sql_hot_demo SET counter = counter + 1;" -c "UPDATE sql_hot_demo SET counter = counter + 1;" -c "UPDATE sql_hot_demo SET counter = counter + 1;" SET UPDATE 5000 UPDATE 5000 UPDATE 5000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot FROM pg_stat_user_tables WHERE relname = 'sql_hot_demo';" SET n_tup_upd | n_tup_hot_upd | pct_hot -----------+---------------+--------- 15000 | 9468 | 63.1 (1 row)
📊 Compare Against the Default fillfactor, Same Update Pattern
Run the identical 3-round update pattern on a table at the default fillfactor=100, and compare the real HOT rate.
psql -U postgres -d beer_db -c "CREATE TABLE sql_hot_default (id int PRIMARY KEY, counter int, junk text);" -c "INSERT INTO sql_hot_default SELECT g, 0, repeat('a',50) FROM generate_series(1,5000) g;" -c "ANALYZE sql_hot_default;"psql -U postgres -d beer_db -c "UPDATE sql_hot_default SET counter = counter + 1;" -c "UPDATE sql_hot_default SET counter = counter + 1;" -c "UPDATE sql_hot_default SET counter = counter + 1;"psql -U postgres -d beer_db -c "SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot FROM pg_stat_user_tables WHERE relname = 'sql_hot_default';"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_hot_default (id int PRIMARY KEY, counter int, junk text);" -c "INSERT INTO sql_hot_default SELECT g, 0, repeat('a',50) FROM generate_series(1,5000) g;" -c "ANALYZE sql_hot_default;" SET CREATE TABLE INSERT 0 5000 ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "UPDATE sql_hot_default SET counter = counter + 1;" -c "UPDATE sql_hot_default SET counter = counter + 1;" -c "UPDATE sql_hot_default SET counter = counter + 1;" SET UPDATE 5000 UPDATE 5000 UPDATE 5000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot FROM pg_stat_user_tables WHERE relname = 'sql_hot_default';" SET n_tup_upd | n_tup_hot_upd | pct_hot -----------+---------------+--------- 15000 | 90 | 0.6 (1 row)
🚫 Confirm Updating an Indexed Column Makes HOT Impossible
Update the indexed_val column on the fillfactor=70 table, and confirm directly that not one of these updates was HOT.
psql -U postgres -d beer_db -c "UPDATE sql_hot_demo SET indexed_val = indexed_val + 1000000;"psql -U postgres -d beer_db -c "SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot FROM pg_stat_user_tables WHERE relname = 'sql_hot_demo';"student@lab:~$ psql -U postgres -d beer_db -c "UPDATE sql_hot_demo SET indexed_val = indexed_val + 1000000;" SET UPDATE 5000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/n_tup_upd,1) AS pct_hot FROM pg_stat_user_tables WHERE relname = 'sql_hot_demo';" SET n_tup_upd | n_tup_hot_upd | pct_hot -----------+---------------+--------- 20000 | 9468 | 47.3 (1 row)
Lab 3.4.3 complete. HOT updates and fillfactor, measured directly:\n\n\n fillfactor=70, non-indexed updates : ✅ 63.1% HOT, measured\n fillfactor=100 (default), same test : ✅ 0.6% HOT — dramatic, real contrast\n Indexed column updated : ✅ 0 new HOT updates, confirmed directly\n
Enable JavaScript to run the live terminal and track your progress.