beginnerintermediateadvanced

PostgreSQL · Section 19

Storage and Table Internals

Level: advanced
Read: 32 min
Concepts: 6

A heap table's lack of any guaranteed physical row order, the page/tuple physical structure underneath it (including the tuple header that carries xmin/xmax, the real visibility information from MVCC), TOAST as the transparent mechanism for values too large to fit in a page, the concrete reason UPDATE can grow a table's physical size even at a constant row count, HOT updates as the cheaper path available when no indexed column changed and the page has room, and fillfactor as the deliberate, per-table trade-off that makes HOT updates more or less likely.

PostgreSQL overview

Learn PostgreSQL Tutorial - Full Course for BeginnersfreeCodeCamp.org

What is true here

A heap table stores rows in no guaranteed order — physical location depends on free space, not any key.
A page (8 KB default) holds many tuples; each tuple's header carries xmin/xmax, the real visibility information a snapshot checks.
TOAST compresses, then relocates, oversized field values once a row exceeds ~2 KB, transparently to ordinary SQL.
UPDATE can grow a table's physical size with a perfectly constant row count, because every UPDATE adds a new tuple.
A HOT update skips new index entries when no indexed column changed and the page has room — fillfactor is the lever that controls how often that room exists.

What you will be able to do

Explain why a heap table's physical layout carries no ordering guarantee, and why ctid is not a stable identifier
Relate a table's page count and size directly to its tuple count and TOAST usage
Explain why a table can grow physically with a constant row count, and monitor the right signals for it
Judge when lowering fillfactor for HOT-update eligibility is actually worth its storage trade-off

From an unordered heap to a cheaper update path

Heap + pages

rows stored wherever there's room

UPDATE grows physical size

new tuple, old one dead until VACUUM

HOT + fillfactor

reserve room, skip index maintenance

The physical structure

Heap tables, pages and tuples, and TOAST for values too large to fit.

Heap Tables

standardintermediate

PostgreSQL's default table storage is a heap: rows are stored in no particular guaranteed order, physically wherever there is room, rather than sorted by any key the way a clustered-index table in some other databases is. Finding a specific row by value therefore normally requires an index — without one, PostgreSQL must scan the heap looking at every row.

Think of it as

Think of a heap table as a big, unordered box of rows rather than a filing cabinet sorted by a key — a new row goes wherever there is free space (a page with room, or a new page at the end), not into a specific sorted position. This is exactly why an index exists as a separate structure: the heap itself makes no promises about order, so anything that needs fast lookup by value needs a separate, ordered structure (an index) pointing back into the heap.

sql

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);
-- rows land wherever there's free space -- NOT necessarily in id order
-- physically on disk, even though the PRIMARY KEY constraint (backed by
-- an index) enforces uniqueness and enables fast lookup by id

What we're doing: Show that a heap table's physical row order does not match insertion or key order after some updates and deletes create gaps that later inserts reuse.

heap_no_guaranteed_order.sqlsql

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
INSERT INTO orders (total) VALUES (100), (200), (300);
DELETE FROM orders WHERE id = 2;  -- frees up space where row 2 was

INSERT INTO orders (total) VALUES (400);
-- this NEW row may physically land in the space DELETE just freed,
-- not necessarily "at the end" -- the heap has no ordering guarantee

SELECT ctid, id, total FROM orders ORDER BY id;
-- ctid (physical location) does not correlate with id order --
-- confirming the heap's physical layout is independent of key order

3: Deleting row 2 frees physical space somewhere in the table.
5–6: A later insert may reuse that freed space rather than appending strictly at the end.
10: ctid exposes the actual physical location — comparing it against id order makes the lack of a physical ordering guarantee directly observable.

Output

 ctid  | id | total
-------+----+-------
 (0,1) |  1 |   100
 (0,4) |  4 |   400
 (0,3) |  3 |   300

Why this works: The physical ctid values do not increase monotonically with id — row 4 physically landed before row 3 in this example, which is completely normal and expected for a heap: nothing about heap storage promises physical order matches any column's logical order, which is exactly why a query that needs rows "in order" relies on an index or an explicit ORDER BY, never on assumed physical layout.

Assuming rows come back in insertion or primary-key order without an explicit ORDER BY

Wrong

sql

SELECT * FROM orders;
-- assumption: "this will come back in id order, since id increments
-- and rows are inserted in order" -- true ONLY by accident, on a
-- freshly-loaded table with no deletes/updates -- not guaranteed

Better

sql

SELECT * FROM orders ORDER BY id;
-- explicit ordering -- correct and guaranteed regardless of the
-- table's actual physical heap layout

What you see: A report or export that relied on unordered SELECT results "happening" to come back in a sensible order works fine in development, then produces visibly scrambled output in production once the table has seen enough deletes/updates to disturb its incidental physical layout.

Why: A heap table's physical storage order is an implementation detail with no guarantee attached to it — any code that depends on a particular row order must say so explicitly with ORDER BY, since the heap itself will happily return rows in whatever order is physically convenient, which can and does change as the table is written to over time.

Remember: A heap table stores rows in no guaranteed order — physical location depends on free space, not any key. Without an index, finding specific rows requires scanning the whole heap. Never assume a particular row order without an explicit ORDER BY, since the heap's physical layout is an implementation detail that can and does change.

Pages, Tuples and Visibility Information

coreadvanced

A table's file is divided into fixed-size pages (8 KB by default), and each page holds a number of tuples — the physical name for a single row version. Every tuple carries a header with system columns (xmin, xmax, and others) that record which transaction created it and, if applicable, which one superseded it — this header is the actual visibility information MVCC snapshots check against.

Think of it as

A page is the unit PostgreSQL actually reads and writes to disk — an I/O operation touches a whole page, never a single row in isolation, which is why packing more live tuples per page (versus wasting space on dead ones) directly affects how much I/O a query needs. Each tuple's header is small, fixed-format bookkeeping — not user data — that exists purely so a snapshot can answer "is this specific tuple visible to me" without consulting anything outside the tuple itself.

sql

SHOW block_size;  -- 8192 (8 KB), the default page size

-- expose a tuple's system columns directly
SELECT ctid, xmin, xmax, * FROM accounts WHERE id = 1;
-- ctid = (page_number, item_number_within_page) -- the tuple's physical address

What we're doing: Use ctid to see multiple tuples sharing the same page, and pg_relation_size / a row count to relate page count to table size directly.

pages_and_tuples.sqlsql

SELECT ctid, id FROM accounts ORDER BY ctid LIMIT 5;
--  ctid  | id
-- -------+----
-- (0,1)  |  1
-- (0,2)  |  2
-- (0,3)  |  3   -- all three tuples share page 0 -- (page_number, item_number)

SELECT pg_relation_size('accounts') / current_setting('block_size')::int AS page_count;
-- directly relates the table's total size to how many 8 KB pages it occupies

2–6: The first number in ctid is the page — several tuples with ctid (0, N) all physically live on the same page.
8: This computation makes the page/tuple relationship concrete: total size really is page_count × 8 KB.

Output

 ctid  | id 
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3

 page_count 
------------
         42

Why this works: Seeing multiple rows share the exact same page number in their ctid makes the otherwise-abstract idea of "a page holds many tuples" concrete and directly observable, and relating pg_relation_size to block_size shows precisely how a table's on-disk size is just page_count × 8 KB — the same arithmetic that explains why bloat (dead tuples wasting page space) directly inflates page_count.

Assuming ctid is a stable, permanent row identifier safe to store or reference elsewhere

Wrong

sql

-- storing ctid somewhere to "remember" a row's location for later:
CREATE TABLE bookmarks (row_location tid, note text);
INSERT INTO bookmarks VALUES ('(0,1)', 'important row');
-- later, after an UPDATE or VACUUM moves things around:
SELECT * FROM accounts WHERE ctid = '(0,1)';  -- may now point at a
                                                -- COMPLETELY DIFFERENT row

Better

sql

-- use the actual primary key -- stable and meaningful, unlike ctid
CREATE TABLE bookmarks (account_id int REFERENCES accounts(id), note text);
SELECT * FROM accounts WHERE id = 1;  -- always finds the correct row,
                                        -- regardless of physical movement

What you see: A ctid stored for later reference silently starts pointing at a different, unrelated row after any UPDATE, VACUUM, or VACUUM FULL physically moves tuples around — with no error, just quietly wrong data.

Why: ctid identifies a tuple's CURRENT physical location, not a stable logical identity — an UPDATE moves a row to a new tuple with a new ctid, and VACUUM FULL can rewrite the entire table's physical layout, so any ctid captured earlier can become meaningless or, worse, silently valid-but-wrong at any later point.

From a table file down to a single tuple's visibility fields

Table file

divided into fixed-size pages

Page (8 KB default)

header + line pointers + tuple data

Tuple

one physical row version

Tuple header

xmin, xmax — the actual visibility information

Remember: A page (8 KB default) is the fixed-size unit PostgreSQL actually reads/writes; a tuple is one physical row version living inside a page, with a header carrying xmin/xmax — the real visibility information a snapshot checks. ctid exposes a tuple's current physical address but is NOT a stable identifier — it changes on UPDATE/VACUUM FULL. Use the real primary key for anything that must persist.

TOAST for Oversized Values

coreadvanced

TOAST (The Oversized-Attribute Storage Technique) is PostgreSQL's mechanism for storing values too large to fit in an 8 KB page alongside the rest of their row — once a row would exceed roughly 2 KB, PostgreSQL compresses large field values and, if that is not enough, moves them out-of-line into a separate TOAST table, leaving only a small pointer in the main row.

Think of it as

TOAST exists to reconcile two conflicting facts: PostgreSQL's page size is fixed at 8 KB and a tuple can never span pages, yet PostgreSQL also wants to let you store a multi-megabyte JSONB document or a long text column without a special "large object" API. TOAST resolves this by making the large-value problem invisible at the SQL level — you read and write the column normally, and PostgreSQL transparently compresses and/or relocates the actual bytes behind the scenes, needing only a small in-row pointer to find them again.

sql

-- every table with a TOAST-able column gets an associated TOAST table automatically
SELECT relname, reltoastrelid::regclass AS toast_table
  FROM pg_class
 WHERE relname = 'articles' AND reltoastrelid != 0;

-- per-column storage strategy can be tuned (rarely needed):
ALTER TABLE articles ALTER COLUMN body SET STORAGE EXTERNAL;  -- skip compression

What we're doing: Store a large text value, confirm a TOAST table exists for the table, and observe that querying the column works completely normally despite the value being physically relocated.

toast_transparency.sqlsql

CREATE TABLE articles (id SERIAL PRIMARY KEY, body text);
INSERT INTO articles (body) VALUES (repeat('x', 1000000));  -- ~1 MB value

SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'articles';
-- pg_toast.pg_toast_XXXXX -- confirms a TOAST table was created for this table

SELECT length(body) FROM articles WHERE id = 1;
-- 1000000 -- reads back correctly and completely, with no special syntax --
-- the fact that the value lives out-of-line is entirely invisible here

2: A 1 MB value, vastly larger than an 8 KB page — this could not possibly fit in a normal row.
4–5: A TOAST table exists automatically for any table with a TOAST-able column, not just this one after the fact.
7–8: Reading the value back requires nothing special — TOAST's relocation is completely transparent at the SQL level.

Output

 reltoastrelid 
---------------------
 pg_toast.pg_toast_16412

 length 
---------
 1000000

Why this works: This demonstrates the entire point of TOAST: a value far larger than a page can be stored and retrieved through completely ordinary SQL, with the compression/relocation machinery working invisibly underneath — the developer never needs to know or care that the actual bytes live in a separate table.

Assuming SELECT * is cheap on a table with large TOASTed columns, when only small columns are actually needed

Wrong

sql

-- listing view only needs id and title, but:
SELECT * FROM articles;
-- fetches the full, possibly multi-megabyte "body" column for every
-- row, even though the listing never displays it -- forces PostgreSQL
-- to detoast (decompress + fetch from the TOAST table) every value

Better

sql

SELECT id, title FROM articles;
-- avoids touching the TOASTed "body" column entirely --
-- no detoast cost paid for data that is never used

What you see: A listing page that only shows titles is unexpectedly slow and I/O-heavy, because SELECT * is pulling and decompressing a large TOASTed text column for every row in the result, even though nothing in the response actually uses it.

Why: PostgreSQL only pays TOAST's decompression/out-of-line-fetch cost for columns actually referenced by a query — selecting exactly the columns needed, rather than *, avoids detoasting large values the query has no use for, which is a real, measurable cost specifically because those values are NOT stored inline with the rest of the row.

What happens once a row exceeds ~2 KB

INSERT INTO articles (body) VALUES (repeat('x', 1000000));

articles

Main table row — keeps only a small pointer once body is TOASTed

repeat('x', 1000000)

Oversized value — compressed first, then moved out-of-line if still too big

TOAST's two mechanisms, applied in order

TOAST's two mechanisms, applied in order
Mechanism	What it does
Compression	shrinks a large field value in place, tried first
Out-of-line storage	moves the (possibly still-compressed) value to a separate TOAST table, leaving a pointer

Remember: TOAST activates once a row exceeds roughly 2 KB — it compresses large field values first, then moves them out-of-line into a separate per-table TOAST table if still too big, leaving only a pointer inline. It is fully transparent to ordinary SQL. SELECT * on a table with large TOASTed columns pays a real, avoidable detoast cost for columns a query never actually uses.

The cost, and the shortcut

Why UPDATE grows a table physically, and the HOT/fillfactor mechanism that can make it cheaper.

Why UPDATE Can Increase Table Size

coreintermediate

Because UPDATE creates a new row version rather than overwriting the old one in place, every UPDATE physically ADDS a tuple to the table's storage — the old version becomes a dead tuple, still occupying its page, until VACUUM reclaims it. This means a table that never grows its row count can still grow steadily in physical size, purely from sustained UPDATE traffic, if vacuuming is not keeping pace.

Think of it as

It is intuitive to think of UPDATE as "the same amount of data, just with new values" — but physically, PostgreSQL treats it as an INSERT of a new tuple plus marking the old one superseded, which briefly (and, without enough vacuuming, not-so-briefly) requires MORE storage than before the UPDATE, not the same amount. A table's steady-state size under heavy UPDATE churn is really a balance: how fast new versions are created versus how fast VACUUM reclaims the old ones — and that balance can tip toward unbounded growth if vacuuming ever falls behind for long enough.

sql

SELECT count(*), pg_size_pretty(pg_relation_size('accounts')) FROM accounts;
-- run this before and after a batch of UPDATEs to see size grow with a
-- CONSTANT row count

What we're doing: Demonstrate table size growing purely from repeated UPDATEs, with the row count staying perfectly constant throughout.

update_grows_size.sqlsql

SELECT count(*), pg_size_pretty(pg_relation_size('accounts')) FROM accounts;
-- count = 10000, size = 720 kB

UPDATE accounts SET balance = balance + 1;  -- touches every row
UPDATE accounts SET balance = balance + 1;
UPDATE accounts SET balance = balance + 1;

SELECT count(*), pg_size_pretty(pg_relation_size('accounts')) FROM accounts;
-- count = 10000 (UNCHANGED), size = 2160 kB -- roughly TRIPLED,
-- from 3 rounds of UPDATE creating new tuples each time

2: A baseline: 10,000 rows at a known size.
4–6: Three UPDATE passes over every row — no row is added or removed, only modified.
8–9: Row count is IDENTICAL to before, but size has grown substantially — this is the dead tuples from the superseded versions, not yet reclaimed by VACUUM.

Output

 count | pg_size_pretty 
-------+----------------
 10000 | 720 kB

(after 3 UPDATE passes)

 count | pg_size_pretty 
-------+----------------
 10000 | 2160 kB

Why this works: This is a direct, mechanical demonstration that row count and physical size are genuinely independent measurements — the table's row count never changed, but each UPDATE pass added a full set of new tuples while the old ones sat as dead weight, growing the table roughly proportionally to how many times every row was touched.

Assuming a stable row count means storage/disk usage is also stable

Wrong

sql

-- monitoring dashboard tracks row count only:
SELECT count(*) FROM accounts;  -- "10000, unchanged -- storage must be fine"
-- meanwhile disk usage alerts are firing, unexplained by this metric

Better

sql

-- monitor actual physical size AND dead tuple ratio, not just row count:
SELECT count(*) AS rows,
       pg_size_pretty(pg_relation_size('accounts')) AS size,
       n_dead_tup
  FROM accounts, pg_stat_user_tables
 WHERE relname = 'accounts';

What you see: Disk usage grows steadily on a table whose row count monitoring shows no change at all, leaving the team confused about where the growth is coming from until they check pg_relation_size and n_dead_tup directly.

Why: Row count only tells you how many LOGICAL rows exist — it says nothing about how many physical tuple versions (live and dead) currently occupy disk space, which is exactly why a heavily-UPDATEd table with a rock-stable row count can still be the actual source of unexplained disk growth.

Row count stays flat; physical size does not

UPDATE runs

row count unchanged

New tuple added

a real, physical write

Old tuple left dead

still occupies its page

VACUUM reclaims it

space made reusable — if vacuuming keeps pace

What actually changes table size

What actually changes table size
Operation	Effect on row count	Effect on physical size
INSERT	increases	increases
UPDATE	unchanged	can increase — new tuple added, old one dead until VACUUM
DELETE	decreases	unchanged until VACUUM reclaims the newly-dead tuple

Remember: UPDATE creates a new tuple and leaves the old one as a dead tuple occupying space until VACUUM reclaims it — a table can grow in physical size from UPDATE activity alone, with row count completely unchanged. Monitor pg_relation_size and n_dead_tup, not just row count, to actually track storage growth.

HOT (Heap-Only Tuple) Updates

coreadvanced

A HOT (Heap-Only Tuple) update is a special, cheaper case of UPDATE that applies when the update does not change any indexed column AND there is enough free space on the same page to fit the new row version — when both hold, PostgreSQL can skip creating new index entries entirely, and can even clean up the superseded old version opportunistically (during later reads, not only during VACUUM) without waiting for a full VACUUM pass.

Think of it as

An ordinary UPDATE is expensive partly because every index on the table needs a new entry pointing at the new tuple — even if the update only changed a column no index cares about. HOT recognizes that if no indexed column changed, no index actually needs to know anything happened; the new tuple can just be linked from the old one's location, and any index lookup still finds it via that existing entry. This is exactly why HOT-eligible updates are cheaper: they skip work that would otherwise be pure overhead for indexes that have nothing to say about what changed.

sql

CREATE TABLE accounts (id INT PRIMARY KEY, balance NUMERIC, notes text)
  WITH (fillfactor = 70);  -- leave 30% free space per page, favoring HOT updates

-- an update to a NON-indexed column, with room on the page: HOT-eligible
UPDATE accounts SET notes = 'reviewed' WHERE id = 1;

What we're doing: Compare a HOT-eligible update (non-indexed column) against a non-HOT update (indexed column changed) using pg_stat_user_tables' n_tup_hot_upd counter as direct evidence.

hot_vs_non_hot.sqlsql

SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_tup_upd = 0, n_tup_hot_upd = 0

UPDATE accounts SET notes = 'reviewed' WHERE id = 1;  -- notes is NOT indexed
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_tup_upd = 1, n_tup_hot_upd = 1 -- this update qualified as HOT

UPDATE accounts SET id = 999 WHERE id = 1;  -- id IS indexed (primary key)
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_tup_upd = 2, n_tup_hot_upd = 1 -- the SECOND update did NOT qualify

3–5: Updating a plain, non-indexed column — this is exactly the case HOT is designed for.
7–9: Updating the primary key column forces a non-HOT update — every index on this row now needs attention.

Output

 n_tup_upd | n_tup_hot_upd 
-----------+---------------
         1 |             1

(after the second UPDATE)

 n_tup_upd | n_tup_hot_upd 
-----------+---------------
         2 |             1

Why this works: n_tup_hot_upd staying at 1 while n_tup_upd climbs to 2 is direct, queryable proof of which updates qualified — the difference between the two UPDATE statements was purely which column changed, confirming HOT eligibility is determined column-by-column, not by the UPDATE statement's shape in general.

Adding an index on a frequently-updated column without considering the HOT-eligibility cost

Wrong

sql

-- "last_seen_at" is updated on every user request, and someone adds
-- an index on it for a rarely-run analytics query:
CREATE INDEX idx_last_seen ON users (last_seen_at);
-- every single last_seen_at update now DISQUALIFIES from HOT --
-- a substantial, ongoing cost for an index used occasionally

Better

sql

-- if the analytics query is rare, consider whether a full index is
-- worth disqualifying every write from HOT -- a partial index, a less
-- frequently updated summary table, or accepting a slower analytics
-- query might cost less overall
DROP INDEX idx_last_seen;
-- last_seen_at updates are HOT-eligible again

What you see: Write throughput on a hot, frequently-updated table drops noticeably after adding an index on one of its frequently-changed columns, and n_tup_hot_upd relative to n_tup_upd shows a sharp decline for that table.

Why: Every index on a column disqualifies any UPDATE that touches that column from the HOT optimization, permanently converting what used to be a cheap, index-entry-free update into one that must maintain every affected index — a cost worth weighing explicitly against how much the new index is actually used, especially for columns updated far more often than they are queried by that index.

What HOT eligibility skips

Ordinary UPDATE

+Indexed column changed, or no room on the page
+New entry created in every affected index
+Old version cleanup waits for VACUUM

HOT-eligible UPDATE

−No indexed column changed, and room exists on the page
−No new index entries at all
−Old version can be pruned opportunistically, before VACUUM

HOT-eligible vs ordinary UPDATE

HOT-eligible vs ordinary UPDATE
Condition	HOT-eligible	Ordinary UPDATE
Indexed column changed?	no	yes (or HOT conditions otherwise unmet)
Room on the same page?	yes	no
New index entries created?	no	yes, for every affected index
Old version cleanup	can happen opportunistically, before VACUUM	waits for VACUUM

Remember: A HOT update applies when an UPDATE touches no indexed column AND fits in the same page's free space — it skips creating new index entries and can be cleaned up opportunistically before VACUUM runs. n_tup_hot_upd vs n_tup_upd in pg_stat_user_tables shows exactly how often it is happening. Indexing a frequently-updated column disqualifies those updates from HOT — a real, measurable trade-off.

Why Fillfactor Can Matter for High-Update Workloads

standardadvanced

fillfactor is a per-table storage parameter (default 100, meaning pages are packed as full as possible) that reserves free space on each page at write time — a lower fillfactor deliberately leaves room on every page specifically so that a later UPDATE has somewhere to put its new row version on the SAME page, which is a requirement for that update to qualify as a cheaper HOT update.

Think of it as

Fillfactor is a trade-off between space efficiency and update efficiency, decided up front. Packing pages as full as possible (the default) is the right choice for tables that are mostly read or rarely updated, since it minimizes the number of pages needed to store the data. But for a table under heavy UPDATE churn, that same full-packing works against HOT: a full page has nowhere for an updated row's new version to go, forcing it onto a DIFFERENT page (breaking HOT eligibility even if no indexed column changed) and needing full index maintenance anyway.

sql

CREATE TABLE sessions (id SERIAL PRIMARY KEY, last_active timestamptz)
  WITH (fillfactor = 70);  -- 30% of every page reserved for future updates

-- applying it to an existing table requires a rewrite to take effect:
ALTER TABLE sessions SET (fillfactor = 70);
VACUUM FULL sessions;  -- reorganizes existing data to honor the new setting

What we're doing: Compare HOT-update rates between a default-fillfactor table and a lowered-fillfactor table under the same UPDATE workload.

fillfactor_hot_comparison.sqlsql

CREATE TABLE sessions_default (id INT PRIMARY KEY, last_active timestamptz);
CREATE TABLE sessions_tuned (id INT PRIMARY KEY, last_active timestamptz)
  WITH (fillfactor = 70);

-- fill both fully, then run the same repeated UPDATE workload on each
UPDATE sessions_default SET last_active = now() WHERE id BETWEEN 1 AND 1000;
UPDATE sessions_tuned SET last_active = now() WHERE id BETWEEN 1 AND 1000;

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / GREATEST(n_tup_upd, 1), 1) AS hot_pct
  FROM pg_stat_user_tables WHERE relname IN ('sessions_default', 'sessions_tuned');

2–3: The only difference between the two tables is fillfactor — everything else about the schema and workload is identical.
9–10: hot_pct makes the practical effect of fillfactor directly comparable between the two tables.

Output

     relname       | n_tup_upd | n_tup_hot_upd | hot_pct 
--------------------+-----------+---------------+---------
 sessions_default   |      1000 |           420 |    42.0
 sessions_tuned     |      1000 |           950 |    95.0

Why this works: The tuned table's reserved free space gives far more updates somewhere to land on the same page, directly raising the HOT percentage — this is fillfactor's entire practical effect made visible: not a magic performance switch, but a deliberate reservation of space that specifically improves the odds of the cheaper HOT-update path.

Lowering fillfactor on a table that is mostly read, expecting a general performance improvement

Wrong

sql

-- a reporting table, read constantly, updated rarely:
ALTER TABLE monthly_reports SET (fillfactor = 50);
-- "lower fillfactor sounds like an optimization, so apply it everywhere"
-- reality: this table gains almost nothing from HOT (rare updates),
-- while every SEQUENTIAL SCAN now reads 50% more pages for the same data

Better

sql

-- leave read-heavy, rarely-updated tables at the default:
ALTER TABLE monthly_reports SET (fillfactor = 100);
-- reserve a lower fillfactor specifically for tables with real,
-- frequent UPDATE churn on non-indexed columns

What you see: Sequential and index scan performance degrades on a table after lowering its fillfactor, because every page now holds fewer live rows, forcing more pages (and more I/O) to be read for the same amount of data — with no offsetting benefit, since the table is rarely updated.

Why: Fillfactor is specifically a trade: reserved free space helps ONLY the case where an in-place HOT update needs somewhere to land — a table that is rarely or never updated gains nothing from that reservation while paying its full cost (more pages, more I/O) on every read, which is why fillfactor tuning should be applied deliberately per table's actual workload, not as a blanket setting.

Choosing a fillfactor

Choosing a fillfactor
Workload	Suggested fillfactor	Why
Mostly read, rarely updated	100 (default)	maximize space efficiency — no benefit to reserving room
Heavy UPDATE churn on non-indexed columns	lower (e.g. 70-90)	reserves room for HOT updates to stay on the same page
Append-only (INSERT only, no UPDATE)	100 (default)	nothing to reserve room for

Remember: fillfactor (default 100, fully packed) reserves free space on each page for future in-place updates — a lower fillfactor improves the odds an UPDATE qualifies as a cheap HOT update by giving it room to land on the same page. Only worth lowering for tables with genuine, frequent UPDATE churn — it costs disk space and read efficiency on tables that do not need it.

See also: hot updates · table and index bloat