TopolearnThese internals explain table bloat, update performance and why fillfactor and column choices matter.
Each table is a heap: a file of 8 kB pages. A page has a header, an array of line pointers, and tuples growing from the end. Each tuple has a ~23-byte header containing xmin and xmax (the inserting and deleting transaction ids that MVCC uses for visibility). A row's physical address is its ctid (page, item); you can select it, and it changes on every update.
Rows must fit in a page, so large values are handled by TOAST: values over about 2 kB are compressed (pglz, or lz4 if configured) and, if still large, moved to a separate TOAST table in chunks, leaving a pointer in the row. Selecting only the columns you need avoids de-TOASTing big values.
An UPDATE writes a new tuple version. Normally every index must then get a new entry pointing at it — costly with many indexes. A HOT (heap-only tuple) update avoids that when two conditions hold: no indexed column changed, and the new version fits on the same page. The old tuple then points to the new one, and indexes stay untouched; pruning cleans the chain later without a full VACUUM.
To get more HOT updates on frequently updated tables, lower the fillfactor (e.g. 80–90) so pages keep free space, and avoid indexing columns that change often (like updated_at). Check the ratio with n_tup_hot_upd vs n_tup_upd in pg_stat_user_tables.
Example
CREATE TABLE player (id int PRIMARY KEY, name text, score int)
WITH (fillfactor = 80);
INSERT INTO player SELECT g, 'p' || g, 0 FROM generate_series(1, 1000) g;
SELECT ctid, xmin, xmax, id FROM player WHERE id = 1; -- (0,1) | 750 | 0 | 1
UPDATE player SET score = score + 10 WHERE id = 1;
SELECT ctid, xmin FROM player WHERE id = 1; -- (0,..) new version, new xmin
UPDATE player SET score = score + 1 WHERE id % 10 = 0; -- score isn't indexed
SELECT pg_stat_force_next_flush();
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'player';
-- 101 | 101: all HOT, the pages had free space (fillfactor 80)
CREATE TABLE blob (body text);
INSERT INTO blob VALUES (repeat('x', 100000));
SELECT pg_column_size(body) FROM blob; -- ~1.2 kB: compressed by TOASTRemember
- Rows live in 8 kB pages; xmin/xmax drive MVCC visibility
- TOAST compresses and moves large values out of line
- HOT updates skip index writes if no indexed column changes and the page has room
Watch out for
- Indexing a frequently updated column like updated_at, which prevents HOT updates.