beginnerintermediateadvanced

PostgreSQL · Storage

Storage and Table Internals

Heap tables with no guaranteed order, pages/tuples/xmin-xmax, TOAST for oversized values, why UPDATE can grow a table with a constant row count, HOT updates, and the fillfactor trade-off behind them

No guaranteed ordera heap table stores rows wherever there's room
8 KB pages hold many tuplesthe tuple header carries xmin/xmax
TOAST is transparentcompress, then relocate — invisible to SQL
HOT skips index maintenancewhen no indexed column changed + room on the page

1.Heap Tables

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.

Key features

A heap table stores rows in no guaranteed order — physical location depends on available free space, not any key value
Without an index, finding rows matching a condition requires a sequential scan — reading every row in the table
PostgreSQL's heap is unlike a clustered-index table in some other database systems, where rows ARE physically ordered by the primary key
CLUSTER can physically reorder a heap table according to an index, once, as a one-time operation — but the table does not stay sorted as new writes come in afterward
Every table needs at least one index to make targeted lookups fast; a heap table with no indexes at all always requires a sequential scan

Command

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

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.

2.Pages, Tuples and Visibility Information

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.

Key features

A page (also called a block) is the fixed-size unit of storage PostgreSQL reads/writes — 8 KB by default
A tuple is the physical, on-disk representation of one row version — the term "tuple" and "row version" are used interchangeably in storage discussions
Every tuple has a fixed-size header holding system columns: xmin (creating transaction), xmax (superseding transaction, if any), plus additional bookkeeping fields
A page also has its own header and a line pointer array indexing the tuples it contains, plus a special "hint bit" area used to cache whether a tuple's creating/superseding transaction is known to be committed
More live tuples fitting per page means fewer pages need to be read for the same amount of live data — this is the direct, physical reason bloat (dead tuples wasting page space) hurts query performance

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

Command

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

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.

3.TOAST for Oversized Values

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.

Key features

TOAST activates once a row's total size exceeds TOAST_TUPLE_THRESHOLD (normally 2 KB, not the full 8 KB page size)
It first tries compression on large field values; if the row is still too big, it moves values out-of-line into a separate, per-table TOAST table
It works toward TOAST_TUPLE_TARGET (also normally 2 KB, adjustable per table) — shrinking the row until it fits or no more gains are available
Only variable-length types (text, bytea, jsonb, and similar) are TOAST-able — fixed-size types like integer never need it
TOAST is fully transparent to ordinary SQL — SELECT/INSERT/UPDATE on a TOASTed column work exactly as they would on any other column, with the relocation handled automatically

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

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

Command

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

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.

4.Why UPDATE Can Increase Table Size

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.

Key features

UPDATE creates a new tuple; the old one becomes a dead tuple, occupying its original space until VACUUM reclaims it
A table's row COUNT staying constant does not mean its physical SIZE stays constant — sustained UPDATE churn with vacuuming keeping pace can still leave some steady-state amount of reclaimable dead space
If vacuuming falls behind (blocked by a long transaction, undersized autovacuum settings), dead tuples accumulate faster than they are reclaimed, and the table grows without bound even at a fixed row count
A table can grow in size from UPDATE activity alone, with zero net change in row count — this can surprise anyone reasoning purely in terms of "how many rows exist"
pg_relation_size (actual physical size) diverging significantly from a rough "row count × expected row size" estimate is a direct signal of accumulated dead space

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

Row count stays flat; physical size does not

eventually

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

Command

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

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.

5.HOT (Heap-Only Tuple) Updates

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.

Key features

HOT applies when an UPDATE does not modify any column referenced by an index (BRIN indexes are a documented exception — a HOT update can still occur even if they would need updating)
HOT also requires enough free space on the SAME page to fit the new tuple version — insufficient free space forces an ordinary, non-HOT update even if no indexed column changed
A HOT update skips creating new entries in every applicable index — a real, measurable cost saved for tables with several indexes
HOT-updated chains can be pruned opportunistically during ordinary page access (even a SELECT), reclaiming space without waiting for VACUUM
A lower fillfactor (leaving more free space per page at table-creation/rewrite time) increases the odds an UPDATE finds room on the same page and qualifies for HOT

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

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

Command

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;

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.

6.Why Fillfactor Can Matter for High-Update Workloads

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.

Key features

fillfactor is set per table (or per index) as a storage parameter, default 100 (fully packed)
A lower fillfactor (e.g. 70-90) leaves that percentage of free space unused at initial fill time, reserved for future in-place updates
Reserved free space is what makes a HOT update possible — without room on the same page, an update cannot stay HOT-eligible regardless of whether it touched an indexed column
Lowering fillfactor is a genuine trade-off: the table occupies MORE disk space up front (for the same row count) in exchange for a better chance of cheap HOT updates later
fillfactor is set at CREATE TABLE time or via ALTER TABLE ... SET (fillfactor = N), but existing data is only reorganized to match on the next VACUUM FULL or CLUSTER, not retroactively

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

Command

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

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.

Spotted a mistake on this page? Tell us.

Read Sheet