Library knowledge map
See how it all connects, and jump straight to what's next
This is the library graph — not the engine schematic. Search finds a page; this map shows what it connects to. For “how is PostgreSQL built as a machine?”, use the Architecture atlas.
Browse the whole library
The same graph as a plain, linked index — grouped by kind, most-connected first, with each entry's direct connections listed beneath it.
Errors
102· SQLSTATE failures with reproduction + fixA session asked to lock a row with NOWAIT, but another session already had that row locked.
Two or more transactions waited on locks held by each other, forming a cycle.
The REPEATABLE READ concurrent-update form of SQLSTATE 40001: a REPEATABLE READ transaction attempted to update a row that a concurrently committed transaction…
A session tried to SET a parameter that can only be changed by restarting the server.
A statement ran longer than the configured statement_timeout, and PostgreSQL canceled it.
One statement inside a transaction failed.
A row was inserted with a value that a unique column already has.
A DROP DATABASE was attempted while another session was still connected to that database.
A session left an open transaction sitting idle (no query in flight) for longer than idle_in_transaction_session_timeout.
A CREATE TABLE named a relation that already exists.
A non-superuser connection attempt arrived after every non-reserved connection slot was already in use.
A row referenced a value in another table through a FOREIGN KEY, but that value doesn't exist there.
A string literal was cast to a type whose input function couldn't parse it.
A column or table has a CHECK constraint, and an INSERT or UPDATE produced a row where that check evaluated to false.
A column was declared NOT NULL, and an INSERT or UPDATE tried to leave it null anyway.
A query referenced a table that PostgreSQL couldn't find.
An expression divided a number by zero. PostgreSQL raises an error instead of returning infinity or an undefined result.
A query referenced a column name PostgreSQL couldn't find on that table.
A value didn't fit in the numeric type's allowed range.
PL/pgSQL code called RAISE EXCEPTION with no explicit error code.
Referencing a type that has not been created (or is misspelled or out of the search_path) raises this undefined-object error; 'currency' is not a built-in type.
Object-specific DDL like DROP INDEX only accepts that object type; running DROP INDEX against a table raises this wrong-object-type error with a hint to use DR…
A query referenced a bare column name that exists in more than one table in the FROM clause, so PostgreSQL could not tell which table you meant.
A client was abruptly killed mid-query, severing the connection while the server still had output to send.
COPY fails when an input row has more fields than the target column list expects; a three-field, tab-separated line loaded into a narrower target raises this b…
A table can have at most one PRIMARY KEY; declaring PRIMARY KEY on two columns separately raises this invalid-table-definition error.
A table has an EXCLUDE constraint, and a new row conflicts with an existing row under the constraint's comparison — most commonly, two overlapping time ranges…
A PL/pgSQL SELECT ... INTO STRICT found zero matching rows. PostgreSQL raised an error instead of silently leaving the target null.
A PL/pgSQL ASSERT whose condition evaluates to false raises this error with the assertion's message; it signals a violated invariant, not ordinary data validat…
Deleting or updating a parent row still referenced by a child fails when the foreign key is ON DELETE/UPDATE RESTRICT.
VACUUM (and a few other commands) refuse to run while a transaction block is still open.
A PL/pgSQL CASE statement with no matching WHEN and no ELSE raises this at runtime when the selector matches none of the branches.
A CREATE TABLE listed the same column name twice.
A CLOSE (or FETCH/MOVE) named a cursor that was never declared, or was already closed.
A date/time value was well-formed text, but named a value that doesn't exist on the calendar or falls outside PostgreSQL's supported range.
A CREATE ROLE named a role that already exists.
Preparing a statement with a name that is already prepared in the session raises this error; prepared-statement names must be unique per session.
CREATE SCHEMA fails if a schema of that name already exists; IF NOT EXISTS makes the creation idempotent.
A function declared to return a value must execute a RETURN on every path; a code path that falls off the end without RETURN raises this SQL-routine exception…
A foreign key must reference columns backed by a PRIMARY KEY or UNIQUE constraint on the parent; referencing unconstrained columns raises this invalid-foreign-…
INSERT ... ON CONFLICT DO UPDATE was written without telling PostgreSQL which unique index or constraint should decide the conflict. DO UPDATE always needs an…
A string was too long to fit in a length-limited column.
A Bind message in the extended query protocol supplied a different number of parameter values than the prepared statement expected.
A value was cast to a type with no defined conversion path.
A comparison cannot combine two different explicit COLLATE clauses; applying 'C' to one side and 'POSIX' to the other raises this collation-mismatch error.
date_trunc() was called with a field name it doesn't recognize.
A DROP ROLE was attempted while that role still owned a table.
CREATE FUNCTION fails when a function with the same name and argument types already exists; CREATE OR REPLACE updates it instead of colliding.
Each table or alias in a FROM clause must have a unique name; using the same alias twice raises this duplicate-alias error.
A function was called with an argument type it doesn't accept.
GET STACKED DIAGNOSTICS reads details of the exception currently being handled, so it is only valid inside an EXCEPTION handler; using it elsewhere raises this…
An expression index may only use IMMUTABLE functions, because a changing result would silently corrupt the index; a non-immutable function in the expression ra…
An INTERVAL literal whose field value exceeds the storage limits of the interval type raises this data exception; here a months value larger than a signed 32-b…
A connection attempt named a database that doesn't exist.
A text value was cast to a date/time type, but PostgreSQL couldn't parse it as any recognized date/time format at all.
xmlcomment() rejects text that would produce an illegal XML comment — XML comments cannot contain a double hyphen or end with a hyphen.
XMLPARSE(CONTENT ...) still requires well-formed markup; an unclosed tag raises this data exception with a DETAIL about the premature end of data.
XMLPARSE(DOCUMENT ...) requires well-formed XML; mismatched opening and closing tags raise this data exception with a DETAIL pointing at the mismatch.
xmlpi() rejects an illegal processing-instruction target; the name 'xml' (in any case) is reserved and cannot be used as a target.
A query passed a negative value to LIMIT. PostgreSQL requires LIMIT to be non-negative; use LIMIT ALL or NULL to mean “no limit”.
PostgreSQL raises this data exception when ln(), log(), or log(b, x) is asked for the logarithm of a value that is zero or negative, which is undefined for the…
The OFFSET clause must skip a non-negative number of rows; an OFFSET that evaluates to a negative value raises this data exception.
A window frame's PRECEDING/FOLLOWING offset must be non-negative; a negative frame bound raises this data exception.
A command that only makes sense inside an open transaction block was issued with none active.
The ntile(n) window function divides rows into n ranked buckets and requires n to be a positive integer; zero or a negative bucket count raises this data excep…
ORDER BY and GROUP BY can reference output columns by position, but the position must be within the select list; a position beyond the number of selected colum…
EXECUTE or DEALLOCATE of a prepared-statement name that was never prepared (or was already deallocated) raises this invalid-statement-name error.
Schema names beginning with 'pg_' are reserved for system schemas; creating one raises this reserved-name error.
nextval() fails once a non-cycling sequence hits its MAXVALUE; a sequence capped at 2 raised the limit error on the third call.
A timestamptz literal whose explicit UTC offset lies outside the valid range (about -15:59 to +15:59) raises this data exception; +16:00 is out of range.
A UNION mixed two branches whose types can't be converted to each other.
A UNIQUE constraint was added to a partitioned table without including all of the partition key columns.
width_bucket(operand, low, high, count) requires the bucket count to be a positive integer; passing zero or a negative count raises this data exception.
Writing through a view defined WITH CHECK OPTION fails when the new or updated row would fall outside the view's WHERE condition; the row must remain visible t…
power(0, n) with a negative exponent is undefined because it implies division by zero, so PostgreSQL raises this data exception instead of returning infinity.
An INSERT supplied an explicit value for a GENERATED ALWAYS AS IDENTITY column.
A non-aggregated column in the SELECT list of a grouped query must appear in GROUP BY or be wrapped in an aggregate; otherwise PostgreSQL raises this grouping…
COPY ... WITH (FREEZE) was attempted while a cursor was still open in the same transaction. PostgreSQL refused because it can no longer guarantee no one else c…
PostgreSQL started an SSL handshake on a new connection, then the peer closed the TCP socket before the handshake finished.
Declaring a cursor with a name already open in the same transaction raises this error; cursor names must be unique within a transaction.
A function's named parameters must be distinct; declaring two parameters with the same name raises this invalid-function-definition error.
When PostgreSQL cannot derive a collation for a string operation, it raises this error and hints to add an explicit COLLATE clause.
Interpreting bytes as text in a given encoding fails when the bytes are not valid there; 0xff is never a valid standalone UTF-8 byte.
The ESCAPE clause of LIKE accepts an empty string or exactly one character; a multi-character escape string raises this data exception.
JSON_VALUE must resolve to a single scalar; a path that returns an array, object, or multiple items raises this data exception — use JSON_QUERY for non-scalars.
substring(... FOR count) was given a negative count. PostgreSQL treats a negative substring length as a data error rather than returning an empty string.
JSON_VALUE raises this when the path matches nothing and the ON EMPTY behavior is ERROR; a DEFAULT ...
nth_value(expr, n) returns the value from the n-th row of the window frame and requires n to be a positive integer; zero or a negative position raises this dat…
A PL/pgSQL variable declared NOT NULL was assigned a null value.
A write was attempted inside a transaction explicitly marked READ ONLY.
ROLLBACK TO SAVEPOINT (or RELEASE SAVEPOINT) fails if the named savepoint was never established in the current transaction, raising this savepoint exception.
A CREATE TABLE statement referenced a schema name that doesn't exist yet.
PostgreSQL's parser rejected the statement because it didn't match valid SQL grammar — here, an unquoted reserved key word used as a table name.
A PL/pgSQL SELECT ... INTO STRICT matched more than one row. PostgreSQL raised an error instead of silently picking one of them.
Converting text to a target encoding fails when a character has no representation there; the euro sign (U+20AC) has no LATIN1 equivalent.
A connection attempt supplied the wrong password for a password-authenticated role.
A regular expression pattern passed to a PostgreSQL function was not valid — usually unbalanced parentheses or brackets.
A scalar subquery matched more than one row.
No cross-links yet.
A bit string value was stored into a bit(n) column whose length didn't exactly match n.
No cross-links yet.
A function was called with more positional arguments than PostgreSQL allows in a single call.
No cross-links yet.
A query referenced a positional parameter ($1, $2, ...) outside of any context that supplies a value for it.
No cross-links yet.
Runbooks
75· Step-by-step operational recipesBy default statement_timeout, lock_timeout, and idle_in_transaction_session_timeout are all 0 — unlimited.
Some type changes rewrite the whole table; others do not.
When queries hang on a lock, pg_blocking_pids and pg_locks tell you exactly who is waiting on whom.
A session that opens a transaction, does one write, then sits idle keeps its locks the whole time.
A retry policy opened a fresh backend every time a query timed out, until every non-reserved slot in max_connections was gone.
FOR UPDATE SKIP LOCKED lets multiple workers pull from the same queue table without stepping on each other — worker two grabs the next batch instead of stallin…
pg_stat_activity, pg_locks and the blocking chain describe only this second — restoring service deletes them.
xact_rollback against xact_commit is a one-line health check nobody runs.
A plain DETACH PARTITION died on a 2 s lock_timeout because one reader was open.
PostgreSQL indexes the parent key of a foreign key but never the child column.
Range partitioning by month lets the planner skip every partition a query does not need.
Indexes bloat too. REINDEX INDEX CONCURRENTLY rebuilds an index without blocking reads and writes — here a bloated index shrinks from 19 MB to 4,832 kB.
When CREATE INDEX CONCURRENTLY fails partway, it leaves an INVALID index behind that still costs writes but serves no reads.
One session held a row with SELECT ... FOR UPDATE and a second sat behind it for 1.5 seconds on a transactionid wait. No table lock was involved, which is why…
VACUUM cannot remove dead rows newer than the oldest running transaction's snapshot.
A CIC that reports 0 blocks done is usually not stuck — it is waiting.
wait_event_type and wait_event in pg_stat_activity tell you why a session is not running — waiting on a lock, on the client, or sleeping.
Instead of a blocking SET NOT NULL that scans the whole table under lock, add a CHECK (col IS NOT NULL) NOT VALID, VALIDATE it without a heavy lock, then flip…
Multixact IDs have their own counter, their own freeze thresholds and their own way of shutting a cluster down — and almost nobody monitors them.
Advisory locks let application code claim a named lock so only one worker runs a job at a time.
After five executions PostgreSQL may freeze a prepared statement onto a generic plan.
An index on (courier_id) is redundant when an index on (courier_id, booked_at) already exists — the composite serves both.
pg_stat_user_indexes records how often each index is scanned.
An ALTER TABLE waits for an exclusive lock, and every reader that arrives behind it queues too — one migration stalls everyone.
CLUSTER and VACUUM FULL hold ACCESS EXCLUSIVE for the whole rewrite, so the only useful question is how much longer.
COPY reports its row count only when it commits.
pg_locks mixes relation, tuple and transaction ID locks into one flat view.
Dead tuples leave a table larger than its live data.
A replication slot guarantees WAL is kept until the consumer reads it — but an abandoned, inactive slot keeps that WAL forever and fills the disk.
Page 10,000 of an OFFSET query re-reads everything before it.
A real PG16 top-level DO lab committed 300 single-row transactions in each mode.
When a GROUP BY does not fit in work_mem, the planner falls back to a sort that spills to disk (external merge, 5912kB here).
A sort bigger than work_mem spills to temp files and does an external merge.
Add a CHECK as NOT VALID and it enforces new writes immediately while skipping the scan of existing rows; VALIDATE later scans without blocking writes.
You built the index and the planner still scans the table.
When a sort or hash exceeds work_mem it spills to disk as an 'external merge'.
A table with autovacuum_enabled=false is never cleaned, no matter how much it bloats.
pg_stat_statements aggregates every query by normalized shape, so you can rank by total time spent — not just per-call time.
random_page_cost defaults to 4.0, a spinning-disk assumption.
checkpoint_completion_target changes the pacing window for dirty-buffer writes.
effective_cache_size tells the planner how much memory it can assume is available for caching.
VACUUM prints nothing until it is done. pg_stat_progress_vacuum names the phase and the block counter, so you can tell 3,445 of 19,608 blocks scanned from a jo…
Turning wal_compression on cut the WAL for a full-table update from 82 MB to 68 MB — 17.1% with pglz, 16.1% with lz4.
Flipping pg_hba.conf to scram-sha-256 while roles still store md5 verifiers logs every one of them out on the same reload.
Large values live in a separate TOAST table and it bloats independently.
Measure WAL bytes and elapsed time for a representative workload, then project that rate across checkpoint_timeout.
A lookup that used to be instant now reads the whole table.
SELECT count(*) on a big table scans every row, every time.
Requested checkpoints rising faster than timed checkpoints means WAL volume is ending checkpoint cycles early.
When a bitmap scan runs out of work_mem it stops tracking individual rows and rechecks whole pages.
The default autovacuum threshold (20% of the table) is far too lax for a hot table.
A big aggregate runs single-threaded while your cores sit idle.
On an append-only table whose rows arrive in physical order, a BRIN index stores per-block ranges instead of per-row pointers.
No cross-links yet.
Point-lookup joins want a nested loop over an index; missing that index forces a hash join that scans both tables.
No cross-links yet.
A WITH block can be an optimization fence that spills to disk before your WHERE clause runs.
No cross-links yet.
Add the columns a query returns as INCLUDE payload and the index can answer it without touching the table.
No cross-links yet.
Same table, same 1,200,000 updates. At fillfactor 100 only 319 of them were HOT and the heap grew to 59 MB. At fillfactor 70, 912,015 were HOT — 76.0% — and th…
No cross-links yet.
The same 5,000-row index scan took 0.887 ms selecting three narrow columns and 120.265 ms once it touched the TOASTed body.
No cross-links yet.
A single cache hit ratio hides which part of the workload is doing the damage.
No cross-links yet.
An UNLOGGED table skips the WAL, so PostgreSQL truncates it during crash recovery rather than serve contents it cannot vouch for.
No cross-links yet.
When two columns are correlated the planner multiplies their selectivities and guesses badly.
No cross-links yet.
In a composite index, column order decides whether a range query can use it.
No cross-links yet.
The jsonb containment operator @> cannot use a B-tree.
No cross-links yet.
Without a current visibility map the planner often will not pick a true index-only scan at all.
No cross-links yet.
The cache hit ratio from pg_stat_database tells you how often reads are served from shared_buffers instead of disk.
No cross-links yet.
pg_current_wal_lsn plus pg_wal_lsn_diff lets you measure exactly how much WAL a workload produces.
No cross-links yet.
If queries only ever touch a small slice of a table, index just that slice.
No cross-links yet.
PostgreSQL's 32-bit transaction IDs must be frozen before they wrap.
No cross-links yet.
The BUFFERS option shows shared hit (found in cache) versus read (fetched from disk).
No cross-links yet.
A fourteen-relation star join spent 378.737 ms planning.
No cross-links yet.
After a bulk load the planner's stats lag reality, so it estimates 1 row where there are 312 and picks the wrong plan.
No cross-links yet.
Fetching the latest child row per parent by scanning all children and de-duplicating is wasteful.
No cross-links yet.
work_mem is a per-operation budget, not a per-server one.
No cross-links yet.
ANALYZE on a partitioned table is really one job per partition plus one for the parent.
No cross-links yet.
pg_stat_user_tables exposes dead-tuple counts, so you can compute a bloat percentage and watch it.
No cross-links yet.
Parameters
36· Server settings (GUCs) you tuneTerminates a session that has an open transaction but is genuinely idle (no query in flight) for longer than this many milliseconds.
Controls how long a backend waits while blocked on a lock before PostgreSQL runs its deadlock-detection cycle check.
Aborts any statement that waits longer than this many milliseconds while trying to acquire a lock.
Sets the minimum execution time above which all statements will be logged.
Age at which to autovacuum a table to prevent transaction ID wraparound.
No cross-links yet.
Vacuum cost amount available before napping, for autovacuum.
No cross-links yet.
Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
No cross-links yet.
Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
No cross-links yet.
Sets the planner's assumption about the total size of the data caches.
No cross-links yet.
Number of simultaneous requests that can be handled efficiently by the disk subsystem.
No cross-links yet.
Allows feedback from a hot standby to the primary that will avoid query conflicts.
No cross-links yet.
Sets the maximum memory to be used for maintenance operations.
No cross-links yet.
Sets the maximum number of simultaneously running WAL sender processes.
No cross-links yet.
Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
No cross-links yet.
Sets the planner's estimate of the cost of a sequentially fetched disk page.
No cross-links yet.
Controls the size of PostgreSQL's shared buffer cache and influences cache hit rate, checkpoint behavior, and memory pressure.
No cross-links yet.
Compresses full-page writes written in WAL file with specified method.
No cross-links yet.
Sets per-operation memory for sorts, hashes, and similar executor nodes, so one query can consume it many times over.
No cross-links yet.
Concepts
34· Interview-grade internals, four lensesThe default planner assumes columns are independent: for 'a=x AND b=y' it multiplies the per-column selectivities, P(a)*P(b).
HashAggregate builds an in-memory hash table keyed by the grouping columns.
Eight table-level (heavyweight) lock modes, ordered weak to strong: AccessShare, RowShare, RowExclusive, ShareUpdateExclusive, Share, ShareRowExclusive, Exclus…
Read the plan top-down to see what runs — the root is the final step.
Every row version carries xmin (inserting xid) and xmax (deleting/superseding xid).
The Gather node is the seam. Below it the work runs in N background worker processes — plus, by default, the leader itself. Above it the leader collects the pa…
There is no ranking. The planner cost-models all three join strategies for every join pair and keeps whichever comes out cheapest. Nested loop wins when one si…
An nbtree index is a Lehman & Yao B-link tree.
A ctid is an ItemPointer: (block number, line-pointer index).
ExecutorStart/standard_ExecutorStart create an EState and call ExecInitNode to build a PlanState tree mirroring the Plan.
A prepared, parameterized statement has two ways to run.
Every heap tuple stores xmin (the inserting xid).
A HOT update keeps everything on one page.
A Sort node holds tuples in an in-memory array sized by work_mem.
It is not only VACUUM. When an index scan follows an index entry to its heap tuple and finds that tuple dead to every running transaction, the executor sets sc…
REPEATABLE READ in PostgreSQL is snapshot isolation (SI).
There are three pruning moments, not one. (1) plan-time (planner) pruning: when the partition-key restriction is a constant the planner knows, prune_append_rel…
ANALYZE for a table enters analyze_rel() (commands/analyze.c), which locks the relation and calls do_analyze_rel().
For each table the autovacuum daemon computes a dead-tuple threshold: vacthresh = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples (def…
In a non-unique btree, a run of tuples that all share the same key value gets merged into one physical posting-list tuple: the key stored once, then a sorted a…
A leaf fills up, a new entry has to go on it, and the B-tree splits the page.
One big bulk operation could trash the entire shared buffer cache.
PostgreSQL doesn't keep a true LRU. Each shared buffer has a usage_count (0..5). A 'clock sweep' hand walks the buffer array; it skips any pinned buffer or one…
A row header has a single t_xmax slot for 'who locks/deletes me'.
Every transaction's final outcome lives in the commit log — clog, on disk under pg_xact — as exactly two bits per xid.
Every heap relation carries a separate Free Space Map fork (_fsm).
PostgreSQL can't span a row across heap pages, so any variable-length value that would make the tuple exceed TOAST_TUPLE_THRESHOLD (~2032 bytes on a default 8K…
Transaction IDs are 32-bit, and 32 bits run out.
The visibility map (VM) is a 2-bits-per-heap-page bitmap (all-visible, all-frozen).
A full-page image (FPI) is exactly what it sounds like: a complete copy of an 8KB page, written into WAL.
Every change is described by one or more WAL records before the modified data page is allowed to reach disk.
A sequence guarantees UNIQUE, monotonically increasing values -- not gap-free ones.
Before PG14, taking a snapshot meant walking every backend's PGPROC entry and copying out xids and subxids.
PostgreSQL caches up to 64 of a transaction's active subxids directly in its shared-memory PGPROC slot.
Glossary
107· Plain-English definitionsMulti-Version Concurrency Control lets readers and writers proceed concurrently by keeping row versions instead of blocking every read.
Selectivity is the fraction of rows a condition is expected to keep — a number between 0 and 1.
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 th…
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 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.
The cost-based optimizer that turns a parsed query into an execution plan by estimating the cost of alternative scans, joins, and orderings.
The checkpointer is the background process that performs checkpoints: it flushes all dirty shared buffers to disk and writes a checkpoint WAL record, giving cr…
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…
The subsystem that grants and queues table- and object-level locks, tracking what each transaction holds and what it waits for.
A memory context is a named arena that owns a group of allocations.
A page split happens when an insert lands on a B-tree page that is already full.
Patroni is an open-source agent that runs beside each PostgreSQL node and automates high availability.
pgBackRest is a widely used open-source backup and restore tool for PostgreSQL.
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.
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 sh…
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…
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…
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 bounda…
A failover slot is a logical replication slot marked (with failover = true) to survive a failover.
Fast-path locking lets a backend record a weak relation lock (AccessShareLock, RowShareLock, or RowExclusiveLock — the kinds ordinary queries take, which never…
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…
An incremental backup copies only the files changed since the last backup of any type — full, differential, or another incremental.
The leader key is a single entry in the consensus store that marks which node is currently primary.
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.
When several transactions hold a shared lock on the same row at once (for example SELECT ...
n_distinct is PostgreSQL's estimate of how many different values a column holds.
The NULL bitmap is a compact run of bits in the tuple header (t_bits[]), one bit per column, recording which columns are NULL.
palloc(size) is PostgreSQL's internal allocator call.
Pathkeys are how the planner records the sort order a path produces its rows in.
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.
A pipelined node can hand up a result row as soon as it has one — a sequential scan, an index scan, a nested loop.
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.
RTO is the maximum time you can be down before service is restored after a disaster.
Every backend can cache up to 64 of its own active subtransaction ids in shared memory.
The AllocSet (in aset.c) is the default memory-context implementation behind palloc.
attcacheoff is a cached byte offset for a column within a row.
The background process that reclaims space from dead tuples and refreshes planner statistics without a human running VACUUM by hand.
The autovacuum launcher is the background process that schedules vacuuming.
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.
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 instea…
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…
catalog_xmin is the oldest transaction id whose system catalog row versions must still be retained.
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, ab…
A row version that is no longer visible to any transaction but still occupies space on the heap page until vacuum removes it.
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-li…
A delayed standby is a replica deliberately held a fixed interval behind the primary.
Demand-pull execution means rows flow because the top of the plan asks for them.
A differential backup copies only the files that have changed since the last full backup.
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…
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).
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).
To know whether a row is visible, PostgreSQL must know whether the transaction that created or deleted it committed.
The per-tuple memory context is a tiny arena the executor uses for evaluating expressions on a single row (ExprContext.ecxt_per_tuple_memory).
pg_stat_slru is a system view (PostgreSQL 13+) that reports activity for each internal SLRU cache — one row per cache (CommitTs, MultiXactMember, MultiXactOffs…
pg_statistic is the system catalog where ANALYZE stores the statistical portrait of every column.
pg_walinspect is a contrib extension (PostgreSQL 15+) that lets you decode the write-ahead log from inside SQL.
The enable_* settings (enable_seqscan, enable_indexscan, enable_hashjoin, and friends) let you discourage a particular strategy.
The postmaster is the supervisor process that starts a PostgreSQL server.
Projection is the step where a node builds the specific columns and expressions its output should contain — the SELECT list.
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,…
A resource manager is a subsystem that knows how to write and replay its own kind of WAL record.
Each B-tree page stores a pointer to its right sibling at the same level (the btpo_next field).
A lock taken on individual tuples (for example by UPDATE or SELECT ...
RPO is the maximum amount of data you can afford to lose in a disaster, expressed as a time window ("at most 30 seconds").
The shared-memory cache PostgreSQL uses to hold heap and index pages, reducing how often it must read from the operating system and disk.
Slot synchronization is the PostgreSQL 17 mechanism that copies failover-enabled replication slots from the primary onto a standby.
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.
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…
sync_replication_slots is the PostgreSQL 17 standby setting that, when turned on, launches the slot sync worker to keep failover-enabled slots synchronized fro…
synchronized_standby_slots is a PostgreSQL 17 setting on the primary.
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.
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), an…
A TupleTableSlot is the small holder that carries the current row between executor nodes.
A WAL record is one entry in the write-ahead log describing a single change PostgreSQL is about to make.
A watchdog is a timer (often /dev/watchdog) that reboots a node unless it is regularly "pet" by a healthy process.
Apply lag (replay lag) is the gap between WAL a standby has received and WAL it has actually applied.
Asynchronous replication means the primary commits and returns to the client without waiting for any standby to confirm it received the WAL.
A cascaded standby is a replica that streams its WAL from another standby rather than from the primary.
Cascading replication is when a standby streams WAL onward to other standbys instead of every standby connecting directly to the primary.
Each PostgreSQL data type has an alignment requirement (attalign in pg_attribute): 8-byte, 4-byte, 2-byte, or 1-byte.
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.
Heap pruning is the lightweight, on-the-fly cleanup PostgreSQL does to a single page while a query is already looking at it.
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.
A LOCALLOCK is a backend's private record of the locks it currently holds and how many times it has acquired each.
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.
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/fr…
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…
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.
The first thing PostgreSQL builds from your SQL text is a raw parse tree: a structural representation of the sentence produced purely from grammar.
recovery_min_apply_delay is the standby setting that creates a delayed standby.
Reservoir sampling is a technique for picking a fixed-size random sample from a stream of unknown length in a single pass.
PostgreSQL's implementation of SERIALIZABLE that detects dangerous read/write dependency cycles between concurrent transactions and aborts one to preserve seri…
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…
Sampled distributions of column values stored in pg_statistic that the planner uses to estimate row counts and choose plans.
A TID is the physical address of a row version: a pair of numbers written (block, slot).
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.
A utility statement is any command that is not a planned query — CREATE TABLE, ALTER, VACUUM, SET, GRANT.
A compact structure that records whether a heap page is all-visible or all-frozen, which helps PostgreSQL skip unnecessary heap checks.
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 fs…
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.
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 xmin horizon is the age of the oldest snapshot still alive anywhere in the system.
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.
No cross-links yet.
fillfactor is a per-table (or per-index) setting that tells PostgreSQL how full to pack each page when inserting.
No cross-links yet.
Lessons
37· Source-grounded internals lessonsFor any non-trivial query there are many ways to get the answer — different scan methods, join orders, and join algorithms.
PostgreSQL storage is organized into fixed-size blocks, 8KB by default (BLCKSZ).
Historically each PostgreSQL query ran in a single backend process.
MVCC stores many versions of each row; a snapshot is the rule that selects which versions a statement or transaction may see.
The optimizer chooses plans by estimating how many rows each operation produces.
Because MVCC leaves dead tuples behind (see the MVCC article), something must reclaim that space and, just as importantly, advance the cluster's frozen-XID hor…
B-tree assumes a total ordering of scalar keys.
A plain index scan is great for high selectivity (few rows) and a sequential scan is great for low selectivity (most rows).
Every SQL statement goes through parse → analyze → rewrite → plan → execute.
Hot standby lets a streaming standby serve read-only queries while it continuously replays WAL from the primary.
Readers and writers do not block each other — but DDL blocks everyone.
Where physical replication ships raw WAL blocks, logical replication ships logical row changes — "insert this row", "update that row" — reconstructed from the…
For ordinary non-locking SELECT/INSERT/UPDATE/DELETE, PostgreSQL readers do not block writers and writers do not block readers.
A partitioned table is one logical table split into many physical child tables by a partition key.
The PostgreSQL executor runs a tree of plan nodes, each pulling rows from its children on demand (the Volcano/iterator model, src/backend/executor/).
A replica (physical standby or logical subscriber) consumes WAL from the primary.
By default PostgreSQL replication is asynchronous: a primary commits once its own WAL is durable, without waiting for any standby.
A tuple must fit within a single 8KB page — PostgreSQL does not span a row across blocks.
work_mem is the memory budget for a single sort, hash, or similar operation — not per query and not per connection.
PostgreSQL’s default index is nbtree — a Lehman–Yao B-tree in src/backend/access/nbtree/.
Batched backfills keep transactions short so vacuum and HOT can keep up, and so locks and replication lag stay bounded.
shared_buffers is a fixed-size array of 8KB page frames allocated in shared memory at startup.
An ordinary CREATE INDEX takes a SHARE lock on the table for its entire duration.
Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock — the strongest table lock, conflicting with every other lock mode including the ACCESS SHARE that a pl…
Because of MVCC, every UPDATE writes a new heap tuple version.
Over time tables and indexes accumulate bloat — dead tuples and empty space left by MVCC.
pg_rewind is a frontend utility (src/bin/pg_rewind), not backend postmaster code: it reuses an old primary’s data directory by copying blocks that diverged aft…
After the planner chooses a Plan tree, the executor builds a parallel PlanState tree, initializes nodes (ExecInitNode), and pulls tuples by calling each node's…
Streaming replication keeps a standby byte-for-byte identical to its primary by shipping the primary's WAL stream and replaying it continuously.
Replication protects against hardware failure, but it faithfully copies mistakes — a DROP TABLE or bad UPDATE replicates instantly to every standby.
A transaction is durable when, after COMMIT returns, its effects survive a crash.
Blue/green and major-version cutovers often use logical replication as the data plane.
Expand/contract is an application deployment pattern (dual-write or dual-read windows), not a single PostgreSQL subsystem.
How PostgreSQL implements ADD COLUMN and constraint changes matters for downtime: some paths are metadata-only; others rewrite the table.
No cross-links yet.
PostgreSQL uses a process-per-connection model: the postmaster fork()s a dedicated backend for every client connection (src/backend/postmaster/postmaster.c).
No cross-links yet.
VIPs, pooler primary/replica routing, and read scaling are topology choices around PostgreSQL, not a single backend module.
No cross-links yet.
PostgreSQL streaming replication can host a physical standby, but **choosing a single primary after failure is an orchestration problem**, not a single backend…
No cross-links yet.
Interview
11· Real interview questionsA strong answer explains snapshot behavior, anomaly protection, retry cost, and the operational burden of high-contention workloads.
A strong answer connects dead tuples, storage bloat, xid horizon pressure, and degraded read performance.
They want the chain from raw sample rows to per-column statistics in pg_statistic, and then how the planner turns a WHERE clause into a row estimate.
The interviewer is checking that you know ANALYZE reads a bounded sample of blocks, not the whole relation — and that the sample size is fixed by default_stati…
They want the mechanism: 32-bit xids compared modulo 2^31, the commit log recording commit status, and freezing as the thing that lets old xids be recycled saf…
They want to hear that a multixact appears when several transactions lock the SAME row in a way a single xid can't represent, that it lives in its own 32-bit s…
They're checking that you know the stats-view counters and pg_class.reltuples are estimates, and that the exact physical truth (plus dead-tuple and free-space…
This is a signal probe, not a trivia question — they want scale, ownership, and a decision made under pressure.
They want evidence you can isolate a novel issue to a minimal, defensible reproduction and that you understand how upstream PostgreSQL actually accepts a fix.
Every signal has to earn its keep: it must map to a decision, and its collection cost and false-positive rate have to be smaller than the pain it prevents.
They're probing whether you know the per-table counter reset exists and, more importantly, that it resets ACTIVITY counters — not the planner's column statisti…
Pathways
3· Guided end-to-end journeysExplain MVCC, vacuum/freeze, buffers, and WAL from first principles with source anchors.
Interpret planner choices instead of cargo-culting indexes.
Explain MVCC, locking, and isolation levels without hand-waving.