Reference · Glossary
Glossary
Canonical definitions for this course. When a term is defined here, lessons use it exactly this way.
Living documentGrows with each lessonContext: PostgreSQL (current, 16/17+)
- heap
- Postgres's name for a table's main data area: an unordered
collection of pages holding row versions. Unlike an InnoDB table (which is
a B+tree clustered by primary key), a heap has no inherent order, and indexes are
stored separately from it.
— intro'd in lesson 0001
- tuple (row version)
- One physical version of a row inside a heap page. A single logical row can have
several tuples at once (a live one plus expired ones) because MVCC keeps old
versions until they're reclaimed.
— intro'd in lesson 0001
- MVCC (Multiversion Concurrency Control)
- Postgres's concurrency model: it keeps multiple versions of a row so each SQL
statement sees a consistent snapshot of the data as of some point in time.
Its headline guarantee: reading never blocks writing and writing never blocks
reading.
— intro'd in lesson 0001
- dead tuple
- A row version that is expired (superseded by an
UPDATE or removed by
a DELETE) and no longer visible to any transaction, but still occupying
space in the heap. Reclaimed by VACUUM.
— intro'd in lesson 0001
- bloat
- Heap (or index) space consumed by dead tuples that have not yet been reclaimed.
A direct consequence of "never update in place": repeated
UPDATEs grow
a table well beyond its logical size until VACUUM catches up.
— intro'd in lesson 0001
- VACUUM
- The maintenance operation that reclaims space from dead tuples so it can be reused
by new rows. Normally run automatically by autovacuum. The
Postgres counterpart to InnoDB's undo-log purge. Plain
VACUUM marks
space reusable inside the table but does not return it to the OS.
— intro'd in lesson 0001, expanded in 0002
- autovacuum
- The background feature that automates
VACUUM and ANALYZE.
Vacuums a table once dead tuples exceed
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples
(defaults 50 and 0.2 → roughly 20% of rows dead). Lower the scale factor for large,
hot tables so they vacuum sooner.
— intro'd in lesson 0002
- VACUUM FULL
- A heavier operation that rewrites the entire table with no dead space, shrinking
the file on disk — but it takes an
ACCESS EXCLUSIVE lock (blocks all
reads and writes). A last resort for badly bloated tables, not routine maintenance.
— intro'd in lesson 0002
- HOT update (Heap-Only Tuple)
- An
UPDATE optimization: when no indexed column changes and
there is room on the same heap page, the new version is chained in-page and
no new index entries are created. Avoids index write amplification,
and the chain can be pruned during ordinary reads. Encouraged by a lower
fillfactor.
— intro'd in lesson 0002
- fillfactor
- A per-table storage setting (percentage) that reserves free space on each page at
insert time. Below 100 it leaves room for future in-page row versions, raising the
chance an
UPDATE can be a HOT update.
— intro'd in lesson 0002
- secondary index
- In Postgres, every index: a structure stored separately from the heap
that points into it. There is no clustered index — contrast InnoDB, where the table
itself is the one clustered index and only the rest are secondary.
— intro'd in lesson 0001
- ctid / TID
- A tuple's physical address as
(page, slot) — how an index entry
points into the heap. It is not stable: an UPDATE
creates a new tuple with a new ctid, which is why you can watch the
keystone happen by selecting ctid before and after an update.
— intro'd in lesson 0001
- visibility map
- A bit per heap page marking it "all-visible" (every tuple on it is visible to all
transactions). Because visibility lives in the heap, not the index, this map is what
lets an index-only scan skip the heap fetch when the bit is set.
Maintained by
VACUUM — so index-only scans are only
fast on a well-vacuumed table.
— intro'd in lesson 0001, full treatment in 0003
- heap fetch
- The second step of an index scan: after the index yields a
ctid,
Postgres reads the actual tuple from the heap page. An index-only scan tries to avoid
it via the visibility map. In EXPLAIN (ANALYZE) the Heap Fetches:
line counts how many were still needed (0 = fully index-only).
— intro'd in lesson 0003
- index-only scan
- An access path that answers a query from the index alone, skipping the heap fetch —
but only for heap pages whose visibility-map bit is set (all-visible). Falls back to a
normal index scan on pages that aren't. Requires the index to contain every column the
query needs.
— intro'd in lesson 0003
- covering index / INCLUDE
- An index that contains every column a query needs, so it can be served by an
index-only scan. Postgres lets you add non-key payload columns with
INCLUDE (…): they ride along in the index but aren't part of the search
key. The Postgres analogue of an InnoDB covering index — but subject to the
visibility-map condition.
— intro'd in lesson 0003
- XID (transaction ID) · xmin / xmax
- A 32-bit counter Postgres assigns to transactions. Each heap tuple's header stores
xmin (the XID that inserted it) and xmax (the XID that
expired it). MVCC decides visibility by comparing these against the reader's snapshot:
an insertion XID "in the future" is not visible.
— intro'd in lesson 0004
- transaction ID wraparound
- Because XIDs are 32-bit and compared with modulo-2³² (circular) arithmetic, from any
point there are ~2 billion "past" and ~2 billion "future" XIDs. An unmaintained old row
that drifts more than 2 billion transactions behind re-enters the "future" half and
becomes invisible — silent data loss. Prevented by freezing.
— intro'd in lesson 0004
- freezing / FrozenTransactionId
- VACUUM's third job: marking sufficiently-old, all-visible rows as frozen so
they are treated as inserted by the special
FrozenTransactionId, always
older than every normal XID and thus immune to wraparound. Why every table must be
vacuumed at least once per two billion transactions.
— intro'd in lesson 0004
- anti-wraparound autovacuum
- A forced autovacuum that freezes a table once its oldest unfrozen XID reaches
autovacuum_freeze_max_age (default 200 million) — it runs even if
autovacuum is disabled. If freezing keeps failing, Postgres warns, then refuses to
assign new XIDs (writes fail, reads continue) to avoid data loss.
— intro'd in lesson 0004