Lab: keeping updates heap-only

Before you start

The scenario: a sessions table whose last_seen column is updated on every request. Our numbers come from PostgreSQL 18.3 on a laptop. Commits are made asynchronous for this lab so the measurement is about updates, not disk flushes.

CREATE PROCEDURE touch(n int) LANGUAGE plpgsql AS $$
DECLARE target int;
BEGIN
  FOR i IN 1..n LOOP
    target := 1 + (random() * 199999)::int;     -- pick the row first, so the update uses the primary key
    UPDATE sessions SET last_seen = clock_timestamp() WHERE id = target;
    COMMIT;
  END LOOP;
END $$;

Pick the row before the update. WHERE id = 1 + (random() * 199999)::int looks equivalent, but random() is evaluated again for every row, so it scans the whole table and updates a random number of rows. Our first attempt did exactly that.

Run it four ways

For each combination of fillfactor (100 or 80) and an index on last_seen (without, with):

SET synchronous_commit = off;
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
  id int PRIMARY KEY, user_id int NOT NULL, last_seen timestamptz NOT NULL, data text
) WITH (fillfactor = 100);                        -- or 80
CREATE INDEX ON sessions (user_id);
-- CREATE INDEX ON sessions (last_seen);          -- for the indexed runs
INSERT INTO sessions SELECT g, g % 50000, now(), repeat('x', 100) FROM generate_series(1, 200000) g;
VACUUM ANALYZE sessions;

\timing on
CALL touch(100000);
SELECT pg_stat_force_next_flush();
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'sessions';
SELECT pg_size_pretty(pg_indexes_size('sessions'));

Measure the WAL around CALL touch with pg_current_wal_insert_lsn(), as in Write-ahead logging.

What happened

Without an index on last_seen, almost every update was HOT: the new version went on the same page and no index changed. Even at the default fillfactor of 100, pages freed space as old versions were pruned, so 92% fit. Leaving 20% of each page free at insert time made it 100%.

With an index on last_seen, the update changes an indexed column, so HOT is impossible regardless of free space. Every update had to add an entry to every index on the table, including user_id, whose value had not changed. The log doubled, the updates took a third longer, and the indexes grew.

Clean up

DROP TABLE sessions;
DROP PROCEDURE touch(int);

Check yourself

1. What two conditions must hold for an update to be HOT?

   Show answer

   No indexed column changes, and the page holding the old version has enough free space for the new one.

   I could explain this without looking

2. Why did indexing last_seen also make the user_id index grow?

   Show answer

   An update that is not HOT needs a new entry for the new row version in every index on the table, even indexes whose columns did not change.

   I could explain this without looking

3. What does lowering fillfactor to 80 do, and when is it worth it?

   Show answer

   Inserts leave 20% of each page free, so updates can put the new version on the same page. It helps tables with frequent updates to non-indexed columns, at the cost of a larger table; for tables that are never updated, 100 is best.

   I could explain this without looking