Vocabulary behind every incident

Multi-Version Concurrency Control lets readers and writers proceed concurrently by keeping row versions instead of blocking every read.

A compact structure that records whether a heap page is all-visible or all-frozen, which helps PostgreSQL skip unnecessary heap checks.

An append-only record of every change written before the change reaches the data files, so committed work survives a crash and can be replayed during recovery.

The background process that reclaims space from dead tuples and refreshes planner statistics without a human running VACUUM by hand.

A row version that is no longer visible to any transaction but still occupies space on the heap page until vacuum removes it.

PostgreSQL numbers transactions with a 32-bit counter; if very old rows are never frozen, the counter can appear to wrap and hide committed data. Vacuum freezing prevents it.

The Oversized-Attribute Storage Technique that transparently compresses and/or moves large column values into a side table so main rows stay within a page.

The shared-memory cache PostgreSQL uses to hold heap and index pages, reducing how often it must read from the operating system and disk.

A point at which PostgreSQL flushes dirty buffers to disk and records a WAL position, bounding how much WAL must be replayed after a crash.

The subsystem that grants and queues table- and object-level locks, tracking what each transaction holds and what it waits for.

A lock taken on individual tuples (for example by UPDATE or SELECT ... FOR UPDATE) so concurrent writers coordinate without blocking readers.

PostgreSQL's implementation of SERIALIZABLE that detects dangerous read/write dependency cycles between concurrent transactions and aborts one to preserve serial equivalence.

A routine that runs after deadlock_timeout on a waiting backend, builds the wait-for graph, and cancels one transaction when it finds a lock cycle.

An optimization where an UPDATE that does not change any indexed column can store the new row version on the same page without adding index entries.

The cost-based optimizer that turns a parsed query into an execution plan by estimating the cost of alternative scans, joins, and orderings.

Sampled distributions of column values stored in pg_statistic that the planner uses to estimate row counts and choose plans.

Each client connection is served by its own backend process; max_connections caps how many can exist at once, after which new connections are refused.

When PostgreSQL studies a column with ANALYZE, it records a short list of the values that appear most often, together with how frequent each one is. That is the Most Common Values (MCV) list.

For the values that are not on the most-common list, PostgreSQL splits them into buckets that each hold about the same number of rows, and remembers the boundary value between buckets. That structure is an equi-depth histogram.

n_distinct is PostgreSQL's estimate of how many different values a column holds. A positive number is a plain count; a negative number is a fraction of the table, for example -1 means every value is unique.

Selectivity is the fraction of rows a condition is expected to keep, a number between 0 and 1. A selectivity of 0.01 means "this filter passes about 1% of the rows."

Correlation measures how closely the physical order of rows on disk matches the sorted order of a column's values, on a scale from -1 to 1. A value near 1 means the table is almost already in that column's order.

The statistics target is a knob that controls how detailed a column's statistics are, how many most-common values and histogram buckets ANALYZE keeps, and how many rows it samples. The default is 100.

Normally PostgreSQL studies each column on its own. Extended statistics, created with CREATE STATISTICS, tell it to study several columns together so it learns that they are related, for example that city and country move together.

Reservoir sampling is a technique for picking a fixed-size random sample from a stream of unknown length in a single pass. PostgreSQL uses it inside ANALYZE to choose its sample rows fairly, without knowing the row count in advance.

pg_statistic is the system catalog where ANALYZE stores the statistical portrait of every column. pg_stats is a friendly view on top of it that you can actually read in plain SQL.

Every 8 KB page keeps a small array of slots near its top. Each slot, a line pointer, records where a row lives inside the page and what state it is in. Indexes and queries reach a row through its line pointer, not by a fixed offset, so the row's body can move within the page while the pointer stays put.

The xmin horizon is the age of the oldest snapshot still alive anywhere in the system. PostgreSQL is only allowed to remove a dead row version if it is older than this line, because any transaction newer than the horizon might still need to see it.

A TID is the physical address of a row version: a pair of numbers written (block, slot). The first is which page the row lives on, the second is which line-pointer slot on that page. The system column ctid exposes it on every table.

To know whether a row is visible, PostgreSQL must know whether the transaction that created or deleted it committed. Checking the commit log every time is expensive, so the first reader that resolves the answer writes it onto the row as a tiny flag, a hint bit. Later readers trust the flag and skip the lookup.

Heap pruning is the lightweight, on-the-fly cleanup PostgreSQL does to a single page while a query is already looking at it. It reclaims the space of dead row versions on that page and collapses HOT chains, but it stays inside the one page and never touches indexes.

fillfactor is a per-table (or per-index) setting that tells PostgreSQL how full to pack each page when inserting. At the default 100 it fills pages completely; at 80 it leaves 20% of every page empty on purpose.

The first thing PostgreSQL builds from your SQL text is a raw parse tree: a structural representation of the sentence produced purely from grammar. It captures what you typed without checking whether any of the tables or columns actually exist.

After parsing the syntax, PostgreSQL runs parse analysis and produces a query tree: the same statement, but with every name resolved to a real catalog object, every type checked, and shortcuts like SELECT * expanded. It is the meaning of your query, not just its shape.

Between analysis and planning, PostgreSQL runs the rewriter. It applies rules and expands views, so a query against a view is mechanically rewritten into an equivalent query against the underlying tables before any planning happens.

The planner's output is a plan tree: a tree of operations, sequential scan, index scan, hash join, sort, that, executed bottom-up, produces your result. It is the finished strategy the executor will follow.

A portal is the container that holds the state of a query while it runs: its plan, its position in the result, and the memory it uses. When you open a cursor, you are creating a portal you can fetch from a few rows at a time.

A utility statement is any command that is not a planned query, CREATE TABLE, ALTER, VACUUM, SET, GRANT. Instead of going through the optimizer and executor, these run directly through a dispatcher called ProcessUtility.

A Path is one candidate way to produce a result for a table or join, a sequential scan, an index scan, a hash join, annotated with a cost estimate but not the full execution detail. The optimizer generates many Paths and compares them cheaply before committing to one.

Every plan node has two cost numbers. Startup cost is the work done before the first row can be returned; total cost is the work to return all rows. EXPLAIN shows them as the cost=0.00..123.45 pair.

random_page_cost is the planner's estimate of how expensive a random disk read is, relative to a sequential read fixed at 1.0. The default of 4.0 says a random read is modelled as four times costlier than a sequential one.

Pathkeys are how the planner records the sort order a path produces its rows in. An index scan on (created_at) has pathkeys saying "already ordered by created_at"; a sequential scan has none.

The enable_* settings (enable_seqscan, enable_indexscan, enable_hashjoin, and friends) let you discourage a particular strategy. Turning one off does not truly forbid it, it adds a large penalty to that path's cost so the planner picks something else if it can.

When the executor runs a plan, each plan node becomes a live executor node (a PlanState) that holds the running state for that step, its position, its buffers, its children. Each one knows how to return the next tuple when asked.

A TupleTableSlot is the small holder that carries the current row between executor nodes. It can point straight at a tuple still sitting on a disk page, or hold a freshly built row in memory, without forcing a copy each time a node hands a row upward.

A pipelined node can hand up a result row as soon as it has one, a sequential scan, an index scan, a nested loop. A blocking node must read all of its input before it can return even the first row, a sort, a hash build, an aggregate over unsorted data.

Demand-pull execution means rows flow because the top of the plan asks for them. Each node, when asked for a tuple, asks its own children for tuples, and so on down to the scans. Nothing is pushed; everything is pulled from above, one row at a time.

Projection is the step where a node builds the specific columns and expressions its output should contain, the SELECT list. It takes the input tuple and computes the result columns, whether that is a plain column, price * 1.2, or upper(name).

The high key is an upper-bound value stored as the first item on a B-tree page (every page except the rightmost on its level). It promises that every key on the page is less than or equal to it. A search compares its target against the high key to decide whether the key it wants might actually live on the right sibling instead.

Each B-tree page stores a pointer to its right sibling at the same level (the btpo_next field). These pointers chain every page on a level into a left-to-right list. A search that discovers its key has moved (via the high key) follows the right-link to find it, rather than going back up to the root.

A page split happens when an insert lands on a B-tree page that is already full. PostgreSQL (_bt_split() in nbtinsert.c) allocates a new page, moves part of the entries to it, rewires the sibling links, and posts a new pointer (downlink) up to the parent. If the parent is full too, the split cascades upward, and at the top it can create a whole new root.

When a B-tree splits, the boundary key copied up to the parent (a pivot tuple) only needs to be just distinct enough to separate the left page from the right. Suffix truncation (_bt_truncate() in nbtutils.c, PG12+) keeps only the leading column(s) needed to tell them apart and discards the trailing ones. The pivot becomes a router, not a copy of real data.

When a B-tree leaf fills up with many rows that share the same key value, deduplication (_bt_dedup_pass() in nbtdedup.c, PG13+) merges them into one posting-list tuple: a single copy of the key plus an array of the heap TIDs that have it. Instead of "key, key, key, key" you store "key → [tid1, tid2, tid3, tid4]".

The commit log is a small on-disk ledger (in the pg_xact/ directory) that stores, for every transaction id, just two bits of status: in progress, committed, aborted, or sub-committed. A row's xmin/xmax say which transaction wrote it; the commit log says whether that transaction actually committed.

An SLRU is a small, fixed-size, least-recently-used cache of 8KB pages, implemented once in slru.c and reused for several internal logs. It is deliberately simpler than the main shared-buffer pool: a handful of buffers, basic LRU eviction, no full buffer manager. The same SLRU code backs the commit log (pg_xact), subtransaction map (pg_subtrans), and multixact data (pg_multixact), among others.

Every backend can cache up to 64 of its own active subtransaction ids in shared memory. A subtransaction is created by a SAVEPOINT or a PL/pgSQL block with an EXCEPTION handler. Open more than 64 at once and the cache overflows: from then on, visibility checks for those transactions must look up the pg_subtrans SLRU on disk/cache instead of the fast in-memory array.

When several transactions hold a shared lock on the same row at once (for example SELECT ... FOR SHARE, or foreign-key checks), a single xmax slot cannot list them all. PostgreSQL allocates a multixact, an id that stands for a set of transactions and their lock modes, and stores the membership in the pg_multixact SLRUs (offsets and members).

pg_stat_slru is a system view (PostgreSQL 13+) that reports activity for each internal SLRU cache, one row per cache (CommitTs, MultiXactMember, MultiXactOffset, Subtrans, Xact, and others). Its columns count buffer hits, disk reads, writes, and pages zeroed.

A WAL record is one entry in the write-ahead log describing a single change PostgreSQL is about to make. It starts with a fixed XLogRecord header (total length, transaction id, a link to the previous record's LSN, a resource-manager id, and a CRC checksum), followed by which pages it touches and the change payload itself.

A resource manager is a subsystem that knows how to write and replay its own kind of WAL record. Heap, B-tree, transaction commit, sequences, and more each register one in the RmgrTable, providing callbacks, most importantly a redo function used during recovery and a desc function used to print the record. Each WAL record's header carries an xl_rmid that routes it to the correct manager.

A full-page write is a copy of an entire 8KB data page written into the WAL the first time that page is modified after a checkpoint, instead of just the small row-level change. It exists to protect against torn pages: if a crash interrupts an 8KB write so the page is half-old and half-new, recovery restores the whole page from the FPI and replays forward.

The WAL-before-data rule says a modified data page may not be flushed to disk until the WAL describing that modification is already durable. PostgreSQL enforces it by stamping each page with the LSN of the last WAL record that changed it (pd_lsn); the buffer manager checks that the WAL is flushed up to that LSN before writing the page out.

pg_walinspect is a contrib extension (PostgreSQL 15+) that lets you decode the write-ahead log from inside SQL. pg_get_wal_records_info() lists individual records between two LSNs, and pg_get_wal_stats() summarizes them per resource manager, counts, record bytes, and full-page-image bytes. It is the in-database equivalent of the command-line pg_waldump.

Deforming is the process of turning a packed on-disk row (a byte string with a header, optional NULL bitmap, and padded column values) back into usable column values. PostgreSQL walks the tuple left to right in heap_deform_tuple(), applying each type's alignment, and fills two arrays: values[] (the Datums) and isnull[].

The NULL bitmap is a compact run of bits in the tuple header (t_bits[]), one bit per column, recording which columns are NULL. A 0 bit means that column is NULL and is not stored in the row body at all. The bitmap is present only when the row actually contains at least one NULL, signalled by the HEAP_HASNULL flag.

Each PostgreSQL data type has an alignment requirement (attalign in pg_attribute): 8-byte, 4-byte, 2-byte, or 1-byte. When a row is laid out, each column's start is rounded up to its alignment boundary, and the gap bytes inserted to get there are padding. An int4 followed by an int8, for example, leaves a 4-byte hole.

attcacheoff is a cached byte offset for a column within a row. If every column before it is fixed-width and NOT NULL, that column always sits at the same offset, so PostgreSQL stores it once and jumps straight there, O(1) access during deforming, no need to walk the earlier columns.

Every heap row begins with a fixed header (HeapTupleHeaderData) of about 23 bytes carrying the MVCC fields xmin/xmax, the row's own location pointer (ctid), and flag words (t_infomask) recording things like "this row has NULLs" or "this row has variable-width columns". t_hoff marks where the header (plus any NULL bitmap) ends and the column data begins.

Fast-path locking lets a backend record a weak relation lock (AccessShareLock, RowShareLock, or RowExclusiveLock, the kinds ordinary queries take, which never conflict with each other) in its own PGPROC rather than in the shared lock table. Each backend has 16 such slots, so the common case skips the partitioned lock table and its LWLocks entirely.

A LOCALLOCK is a backend's private record of the locks it currently holds and how many times it has acquired each. Before touching any shared memory, a backend checks this local table; if it already holds the requested lock, the request is satisfied locally with no shared-memory work at all.

The shared lock table is divided into 16 partitions, each protected by its own lightweight lock and chosen by a hash of the lock tag. Splitting it this way means two backends locking different objects usually touch different partitions and don't contend with each other on the same internal lock.

pg_locks is a system view exposing every lock currently held or awaited across the server: the object type, the relation, the lock mode, whether it was granted, and, importantly, a fastpath boolean showing whether the lock lives in a backend's private fast-path slots or in the shared lock table.

A memory context is a named arena that owns a group of allocations. PostgreSQL allocates from the current context with palloc and, instead of freeing objects one by one, resets or deletes the whole context, reclaiming every allocation inside it at once. Contexts form a tree rooted at TopMemoryContext; deleting a parent frees all its children.

palloc(size) is PostgreSQL's internal allocator call. Unlike malloc, it takes no context argument, it allocates from the global CurrentMemoryContext, so where the memory lands depends on the context in effect. pfree releases one chunk, but it is rarely used: the normal pattern is to let the context be reset or deleted later.

The AllocSet (in aset.c) is the default memory-context implementation behind palloc. It requests OS memory in blocks that grow geometrically, carves them into chunks for individual allocations, and keeps freed chunks on 11 power-of-two freelists (8 B to 8 KB) for reuse. Allocations above the chunk limit get their own dedicated block.

The per-tuple memory context is a tiny arena the executor uses for evaluating expressions on a single row (ExprContext.ecxt_per_tuple_memory). It is reset between every row, so scratch memory from one tuple is thrown away before the next is processed.

pg_backend_memory_contexts is a system view (PostgreSQL 14+) listing every memory context in the current backend, name, parent, depth level, and total/used/free bytes. It exposes the live context tree so you can see exactly where a backend's memory is going.

The postmaster is the supervisor process that starts a PostgreSQL server. It creates the shared memory segment, opens the listening socket, forks a dedicated backend for each client connection, launches the background worker processes, and monitors all its children. Every other PostgreSQL process is its child.

The checkpointer is the background process that performs checkpoints: it flushes all dirty shared buffers to disk and writes a checkpoint WAL record, giving crash recovery a bounded place to start. It also absorbs fsync requests handed off by backends and spreads its writes over time to avoid I/O spikes.

The background writer trickles dirty shared buffers out to disk ahead of demand, so that when a backend needs a free buffer it usually finds a clean one instead of stalling to write a dirty page itself. It is the checkpointer's lighter-touch sibling, working continuously rather than in bursts.

The WAL writer flushes the write-ahead log to disk in the background on a regular cadence, so that busy backends and asynchronous commits don't each have to fsync the WAL themselves. A backend committing synchronously still flushes its own WAL, but the WAL writer handles the steady background flushing.

The autovacuum launcher is the background process that schedules vacuuming. It wakes on autovacuum_naptime, decides which databases need attention, and dispatches per-database autovacuum workers that actually run VACUUM and ANALYZE on tables with enough dead tuples or stale statistics.

Patroni is an open-source agent that runs beside each PostgreSQL node and automates high availability. It stores cluster state in a distributed consensus store (etcd, Consul, ZooKeeper, or Kubernetes), elects a single primary via a leader key, promotes a standby on failure, and rejoins old primaries with pg_rewind. PostgreSQL provides the failover primitives; Patroni decides when to use them.

etcd is a small, strongly-consistent key-value store that uses the Raft consensus algorithm to agree on data across an odd number of nodes (3 or 5). A write only succeeds if a majority (quorum) of nodes agree. In a Patroni cluster it is the "source of truth" (the DCS) that holds the leader key and member state.

The leader key is a single entry in the consensus store that marks which node is currently primary. It carries a TTL (time-to-live); the primary's agent renews it on every loop. If the key is not renewed before the TTL expires, it disappears and the surviving nodes race to recreate it with an atomic operation, exactly one wins and promotes.

A watchdog is a timer (often /dev/watchdog) that reboots a node unless it is regularly "pet" by a healthy process. On a Patroni primary, the agent pets the watchdog; if the agent hangs and can't demote PostgreSQL, the watchdog fires and reboots the node, forcibly removing the old primary before a new one can rise.

pg_promote() is the PostgreSQL function that tells a standby to stop following the primary, exit recovery, and become a primary in its own right. Promotion ends replay, opens the database for writes, and advances the timeline so future WAL is distinguishable from the old primary's.

pgBackRest is a widely used open-source backup and restore tool for PostgreSQL. It manages a repository of physical backups (full, differential, incremental, and block-incremental) plus archived WAL, using PostgreSQL's own backup API, and restores to any point in time by replaying WAL on top of a chosen backup.

A differential backup copies only the files that have changed since the last full backup. To restore, you need exactly two pieces: that full backup plus one differential. Each differential grows over the week as more changes accumulate, but restore stays simple.

An incremental backup copies only the files changed since the last backup of any type, full, differential, or another incremental. It is the smallest and fastest backup to take, but restoring requires the full backup plus every incremental in the chain, applied in order.

A block incremental backup goes finer than whole files: within a changed file it stores only the individual blocks (pages) that changed, rather than re-copying the entire file. pgBackRest introduced this in version 2.46. It compares block checksums against the previous backup to decide what to store.

A stanza is pgBackRest's configuration unit for a single PostgreSQL cluster, it ties together that cluster's data directory, its repository location, and its backup/archive settings under one name. You initialize it once with stanza-create and then reference it on every command via --stanza.

Cascading replication is when a standby streams WAL onward to other standbys instead of every standby connecting directly to the primary. The intermediate standby runs its own walsender and relays WAL downstream, forming a tree of replicas (primary → tier-1 standby → tier-2 standbys).

A cascaded standby is a replica that streams its WAL from another standby rather than from the primary. Its primary_conninfo points at an upstream standby (which must have hot_standby = on and spare WAL sender slots), and it is read-only like any standby.

A delayed standby is a replica deliberately held a fixed interval behind the primary. It keeps receiving and writing WAL in real time, but postpones applying it by the configured delay, so its visible data is, say, an hour in the past while all the recent WAL sits safely on its disk.

recovery_min_apply_delay is the standby setting that creates a delayed standby. It tells the startup process to wait until a transaction's commit timestamp plus this interval has passed before applying that commit's WAL. The logic lives in recoveryApplyDelay() and keys off the timestamps carried in commit records.

Apply lag (replay lag) is the gap between WAL a standby has received and WAL it has actually applied. On a healthy replica it's tiny; on a delayed standby it's held large on purpose. You can see it as the distance between pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn().

A failover slot is a logical replication slot marked (with failover = true) to survive a failover. PostgreSQL 17 mirrors such slots onto a standby, so when that standby is promoted the logical subscribers' bookmark already exists on the new primary at a safe position.

Slot synchronization is the PostgreSQL 17 mechanism that copies failover-enabled replication slots from the primary onto a standby. A background slot sync worker (or the pg_sync_replication_slots() function) reads the primary's slots and maintains local synced copies, advanced only to positions the standby has safely received.

sync_replication_slots is the PostgreSQL 17 standby setting that, when turned on, launches the slot sync worker to keep failover-enabled slots synchronized from the primary. It needs three things in place: a physical slot back to the primary (primary_slot_name), hot_standby_feedback = on, and the database name in primary_conninfo.

synchronized_standby_slots is a PostgreSQL 17 setting on the primary. You list the physical replication slots of your failover-target standbys, and the primary's logical walsenders hold back changes until those standbys have confirmed the WAL, so a logical subscriber can never read past the node you'd promote.

catalog_xmin is the oldest transaction id whose system catalog row versions must still be retained. Logical replication slots hold it back because logical decoding needs the catalog as it looked when each change was made, to interpret the WAL correctly. You can see it on a slot in pg_replication_slots.

RPO is the maximum amount of data you can afford to lose in a disaster, expressed as a time window ("at most 30 seconds"). In an asynchronous PostgreSQL setup your real RPO is simply the replication lag at the instant of failure, the WAL the disaster site hadn't received yet.

RTO is the maximum time you can be down before service is restored after a disaster. For PostgreSQL it breaks into parts: detecting the failure, deciding to fail over, promoting the standby (pg_promote()), and repointing clients to the new primary.

Asynchronous replication means the primary commits and returns to the client without waiting for any standby to confirm it received the WAL. The standby catches up a moment later. It's the default and the only practical choice across long (cross-region) network distances.

A switchover is a planned, controlled reversal of roles: the current primary steps down cleanly and a standby is promoted, with no data loss because everything is in sync first. It contrasts with a failover, which is the unplanned, emergency version after the primary has already failed.

A DR drill is a deliberate rehearsal of your disaster-recovery procedure, typically a planned switchover or a restore-and-promote in the DR region, run while everything is healthy, to prove the plan works and to measure your real RPO and RTO.