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

snapshot

A captured record of which transactions had committed at a chosen instant — effectively a set of XIDs. MVCC compares each tuple's xmin/xmax against it to decide visibility. An isolation level is simply a choice of when this snapshot is taken and how long it is held. — intro'd in lesson 0005 (mechanism from 0004)

Read Committed

Postgres's default isolation level: each statement takes a fresh snapshot at the instant it begins, so two statements in one transaction can see different data. Conflicting writes re-read the latest committed row rather than erroring. — intro'd in lesson 0005

Repeatable Read (snapshot isolation)

One snapshot is taken at the transaction's first statement and held to commit, giving stable reads and no phantoms. A write to a row changed by a concurrent transaction after the snapshot fails with "could not serialize access due to concurrent update" and must be retried. Postgres's RR is full snapshot isolation (stronger than the SQL standard's RR). — intro'd in lesson 0005

Serializable (SSI)

Repeatable Read plus a watchdog: Serializable Snapshot Isolation monitors read/write dependencies (via predicate locking) and aborts a transaction whose interleaving has no equivalent serial order — catching write skew, which RR allows. Aborts with "could not serialize access due to read/write dependencies among transactions." — intro'd in lesson 0005

write skew

A serialization anomaly snapshot isolation permits: two transactions each read an overlapping set, each verify a constraint that currently holds, and each write based on that read — individually valid, jointly impossible in any serial order. Prevented only at Serializable. — intro'd in lesson 0005

serialization failure (SQLSTATE 40001)

The error class both Repeatable Read and Serializable raise on a conflict they can't resolve. Applications at those levels must catch it and retry the whole transaction; a single 40001 retry loop covers both. — intro'd in lesson 0005

cost (in EXPLAIN)

A plan node's cost=startup..total, in arbitrary units where one sequential page fetch is conventionally 1.0. It is for comparing plans, not a time in seconds, and a node's cost includes all its children. — intro'd in lesson 0006

rows / width (in EXPLAIN)

rows is the planner's estimate of rows a node emits (after its own filtering) — not rows scanned, and not actual. width is the estimated average output row size in bytes. Actual counts appear only with ANALYZE. — intro'd in lesson 0006

EXPLAIN ANALYZE

Runs EXPLAIN and actually executes the query, printing real time, row counts, and loops beside the estimates. Because it executes, a data-modifying statement really runs — wrap it in BEGIN … ROLLBACK to inspect safely. The top diagnostic is comparing estimated vs actual rows. — intro'd in lesson 0006

BUFFERS

An EXPLAIN option (implicitly on with ANALYZE) reporting blocks hit (served from the cache/buffer pool) vs read (fetched from disk). The read count is what makes an otherwise-cheap-looking plan slow on a cold cache. — intro'd in lesson 0006

Bitmap Heap Scan

An access path that builds a bitmap of matching tuple locations from an index, then reads the heap in physical page order. Postgres's middle gear for "many matches, scattered" — between a single Index Scan and a full Seq Scan. — intro'd in lesson 0006

cost-based planner

Postgres's query planner estimates the cost of every candidate plan (seq scan, index scan, bitmap scan, join orders) and executes the cheapest. An index is never "ignored" — it is chosen only when its estimated cost is lowest. — intro'd in lesson 0008

selectivity

The fraction of a table's rows a condition matches. It drives the plan: few rows → index scan; a medium/scattered set → bitmap heap scan; a large fraction → sequential scan (one sweep beats many random heap jumps). — intro'd in lesson 0008

seq_page_cost / random_page_cost

Planner cost constants: a sequential page fetch is 1.0, a random (out-of-order) page fetch defaults to 4.0 — the ratio that makes index heap-jumps look expensive. On SSDs or a heavily-cached database, lower random_page_cost (toward ~1.1) so index scans win their fair comparisons. — intro'd in lesson 0008

effective_cache_size

The planner's assumption about how much OS + Postgres cache is available to a query (default 4GB). It doesn't allocate memory — it's an estimate; a higher value makes index scans more likely, a lower value makes sequential scans more likely. — intro'd in lesson 0008

expression index

An index built on an expression rather than a bare column, e.g. CREATE INDEX ON t (lower(email)). Needed because a predicate that wraps a column in a function (WHERE lower(email) = …) cannot use a plain index on the raw column. The Postgres answer to MySQL's "wrapped column" trap. — intro'd in lesson 0008

enable_seqscan (diagnostic)

A session setting that, when turned off, penalizes sequential scans so you can see what an index plan would cost via EXPLAIN ANALYZE. A diagnostic to confirm mis-set costs or stale stats — not a production setting. — intro'd in lesson 0008

WAL (Write-Ahead Log)

The durability log. Its central rule: a change's WAL record must be flushed to durable storage before the data page it changes is written. So at commit only the (sequential) WAL must be flushed, not every scattered data page. Postgres's WAL is the equivalent of InnoDB's redo log — pure roll-forward; there is no undo log. — intro'd in lesson 0009

REDO / roll-forward recovery

Crash recovery replays WAL records to re-apply any committed change that hadn't yet reached the data files. It starts from the last checkpoint's redo record, not the beginning of time. — intro'd in lesson 0009

checkpoint

A point at which all dirty data pages are flushed to disk and a checkpoint record is written to the WAL. Recovery replays WAL only from the latest checkpoint, and WAL segments before it can be recycled. Frequent checkpoints shorten recovery but add I/O; infrequent ones do the reverse. — intro'd in lesson 0009

synchronous_commit

The durability dial. on (default) makes COMMIT wait for the local WAL flush before returning. off returns earlier for speed, risking loss of a small window of recent commits on a crash — but not corruption; the database stays consistent (unlike fsync=off). Analogue of InnoDB's innodb_flush_log_at_trx_commit. — intro'd in lesson 0009

LSN (log sequence number)

A position in the WAL stream (e.g. from pg_current_wal_lsn()), advancing as changes are logged. The ordering handle behind recovery and replication. — intro'd in lesson 0009

streaming (physical) replication

The default replication: a standby continuously receives the primary's WAL records and replays them, producing a byte-for-byte copy of the whole cluster (same version), serving read-only queries. The basis of high-availability failover. — intro'd in lesson 0010

asynchronous vs synchronous replication

Async (default): WAL is shipped after commit, so a standby lags and a primary crash loses transactions not yet shipped (failover data-loss window). Sync (set synchronous_standby_names): commit waits until the standby has the WAL on disk — no loss, but a standby outage can stall commits. It's synchronous_commit extended over the network. — intro'd in lesson 0010

logical replication

Replication of row-level changes by replication identity (usually the primary key) via a publish/subscribe model — "copy the meaning," in contrast to physical byte-by-byte copying. Enables replicating a subset of tables, across major versions or platforms, into a writable target. The closer cousin of MySQL's binlog replication. — intro'd in lesson 0010