PostgreSQL forks a process per connection rather than using a thread pool. Everything shared between sessions therefore lives in one shared-memory segment, a connection is expensive enough that pooling is mandatory rather than optional, and a backend that crashes takes down only its own session.

It owns the shared-memory segment and restarts the cluster if a backend dies abnormally, because a crashed backend may have left shared memory inconsistent. Background processes it starts include the checkpointer, background writer, WAL writer, autovacuum launcher and workers, and the archiver.

Eviction uses a clock sweep over per-buffer usage counts rather than strict LRU. Large sequential scans are given a small ring buffer so they cannot evict the whole pool. Unlike InnoDB, PostgreSQL does not bypass the filesystem cache, so a page can be held twice.

A pin is not a lock: several backends can pin the same buffer concurrently. Vacuum needs a cleanup lock — exclusive plus a pin count of one — before it can defragment a page, which is why an open cursor can block vacuum on that page and shows as a BufferPin wait.

It sits between spinlocks (a few instructions) and the heavyweight lock manager (held across statements). LWLock waits in pg_stat_activity mean contention on a shared-memory structure, which is a different problem with a different fix from a Lock wait.

Because the catalog is tables, DDL is transactional and rolls back cleanly — the most valuable operational property PostgreSQL has over Oracle and MySQL. The cost is that catalogs bloat like any other table, so churn that creates and drops objects has a price.

Reading the catalog on every query would be intolerable, so every process caches it. A cache in every process needs invalidation, broadcast through shared memory, which is why heavy DDL under load costs more than the DDL itself.

PostgreSQL plans the first five executions with the actual values, then compares the average custom plan cost with the generic plan cost and keeps the generic one if it is not worse. plan_cache_mode overrides the heuristic. The cache is per backend, so a pooler in transaction mode can defeat it.

A page carries a 24-byte header, then line pointers growing down from it while tuples grow up from the end. The gap between them is the free space. Everything the course measures — scan cost, bloat, WAL volume — is counted in pages.

The indirection is what lets a tuple move within its page without any index needing to know. A line pointer can be normal, a redirect (used by HOT chains), dead, or unused; that state is what vacuum and page pruning manipulate.

The header is not generic overhead — it is MVCC written to disk. xmin and xmax name the transactions that created and deleted the version, t_ctid points at the next version, and the infomask bits cache commit status and mark HOT chains.

A tuple may not span pages, so a value over roughly 2 kB is compressed first and, if still too large, chunked at about 2 000 bytes into a side table with its own index. Reading one large value is then an index scan, which SELECT * pays for even when the column is unused.

The main fork holds the data; _fsm is the free-space map, _vm the visibility map, _init the template for unlogged relations. They are separate files because they are written and read on different schedules.

It is deliberately approximate: exactness would cost more to maintain than it saves. It is updated by vacuum, which is one reason a table that is never vacuumed keeps extending rather than reusing space it already has.

The all-visible bit is what makes an index-only scan possible, since the index itself carries no visibility information. The all-frozen bit is what lets an aggressive vacuum skip pages it has already frozen. Both are maintained by vacuum, so both decay after a write burst.

Visibility is a predicate over these two, the snapshot, and the commit log. Because they live in the row rather than in an undo segment, PostgreSQL can decide visibility locally with no reconstruction — and must clean up the dead versions afterwards.

Taken by scanning the ProcArray, so its cost grows with the number of backends. The oldest live snapshot in the cluster is what bounds every vacuum, which is why one idle transaction can bloat tables it never touched.

Without it, every visibility check would consult the commit log. With it, a read-only SELECT can dirty pages and generate write I/O — which is the usual explanation for a first scan being far slower than the second with no caching story to match.

Cheap while the working set of transaction IDs fits in its buffers. Subtransactions have their own equivalent, and each backend caches only 64 subtransaction IDs; past that the snapshot is marked overflowed and every visibility check falls back to that structure, collapsing throughput.

Each backend advertises at most 64 of them. Beyond that limit the snapshot overflows and visibility checks become far more expensive cluster-wide. An exception handler inside a hot loop is therefore an architectural decision, not a style choice.

It requires two things: no indexed column changed, and room on the page — which is why fillfactor must be spent up front. One index on a frequently-updated column disables it for that table. n_tup_hot_upd against n_tup_upd is the health metric, and it has no analogue in InnoDB because the problem does not arise there.

It returns space to the table, not to the filesystem, which is why disk usage does not drop. It is throttled by a cost budget shared among autovacuum workers — the setting most often left at a default that cannot keep up on a large table.

Not a bug but an equilibrium, set by the update rate and the interval between vacuums. InnoDB bloats too; the difference is that PostgreSQL makes it visible and gives you tools, while engines with undo hide it until a rebuild.

Transaction IDs are 32-bit and compared modulo 2^32, so "older than" only means anything within a 2^31 window. Rows must be frozen before that window passes or they would appear to be in the future. This is why vacuum is mandatory rather than tidy, and it is pure inherited cost: InnoDB and Oracle use 64-bit identifiers and have no equivalent.

It bounds cleanup for every table. Four unrelated things hold it back: an idle-in-transaction session, a long analytics query, an inactive replication slot, and hot_standby_feedback from a standby. Read it from pg_stat_activity.backend_xmin and pg_replication_slots.xmin.

SSI uses these to detect the two consecutive read-write dependencies that make a snapshot-isolation history unserializable, and aborts one transaction rather than blocking. They escalate from tuple to page to relation under memory pressure, which is where false-positive aborts come from.

An operator class supplies the operators and support functions a method needs for a given type. This is why a user-defined type can be indexed without changing PostgreSQL, and why PostGIS and pgvector can exist as extensions rather than forks.

It is also why LIKE 'abc%' needs text_pattern_ops under a non-C collation: the default class orders text by collation rules, which do not match prefix comparison.

Lehman–Yao: a reader that lands on a page which has just split compares against the high key and walks right, rather than restarting from the root. This is what lets PostgreSQL avoid locking the tree on descent.

Added in PostgreSQL 14. It is what stops an index on a frequently-updated column from growing without bound between vacuums. PostgreSQL still does not merge underfull index pages, so an index that shrinks stays large until REINDEX.

Possible only where the visibility map marks the page all-visible, because index entries carry no visibility information. It therefore degrades silently into heap fetches after a write burst — visible as Heap Fetches in EXPLAIN (ANALYZE). InnoDB needs no equivalent condition.

The index type behind JSONB, full-text search and array containment. Because one row update touches many keys, GIN buffers new entries in a pending list; that makes writes cheap and makes the search that triggers the flush expensive.

Supplying consistent, union, penalty and picksplit defines a tree for any predicate, including ones with no total order: overlaps, contains, nearest-neighbour. Exclusion constraints fall out of it, and it is the reason PostGIS exists.

Orders of magnitude smaller than a B-tree, and useful only where the column correlates with physical order. It is a filter, not a lookup structure: an update that breaks correlation degrades it silently. pg_stats.correlation is the number that predicts whether it will work.

A random page costs 4 and a tuple 0.01 by default — ratios chosen for a spinning disk, which is why random_page_cost is usually wrong on SSD. effective_cache_size is a hint about the operating system cache, not an allocation.

Derived mechanically from the statistics: equality against the most-common-values list or 1/n_distinct, ranges by histogram interpolation, AND by multiplication. The multiplication assumes the predicates are independent, which is where most estimation error starts.

CREATE STATISTICS supplies the joint distribution the independence assumption is missing, in three kinds: ndistinct, dependencies and mcv. They are not collected automatically and are not used for joins, so they repair a specific shape of error rather than the general one.

Exhaustive dynamic programming over join orders is fine to about ten relations. Past geqo_threshold PostgreSQL searches genetically and no longer guarantees the best plan — and, unlike Oracle or SQL Server, offers no hints or plan baselines to override the result.

A plan with several such nodes multiplies it, and parallel workers multiply it again, so worst-case memory is a number nobody configured. A hash that does not fit spills to batches. Oracle and SQL Server manage a bounded server-wide budget instead; this is a clear PostgreSQL weakness.

Every page stores the LSN of the change that last modified it, which encodes the write-ahead rule: a page may not reach disk before the WAL describing it. LSNs also drive every replication progress measure — sent, write, flush, replay.

Storage guarantees atomicity at 512 bytes or 4 kB, so a power cut can leave a torn page that redo cannot repair. WAL volume therefore spikes right after every checkpoint. InnoDB solves the same problem with the doublewrite buffer, paying a constant 2× instead.

Recovery is bounded by the WAL written since the last one, so the interval is the recovery-time dial. It trades directly against full-page writes: shortening it shortens recovery and increases WAL volume. A requested checkpoint — one triggered by hitting max_wal_size — is a warning sign; a timed one is not.

Setting it off risks losing recent commits, not corrupting data — the distinction most often got wrong. It can be set per transaction, so one session can opt out for bulk work while the rest of the system stays durable.

Without it, a promoted standby's WAL could be confused with the original primary's from the same LSN onward. It is why point-in-time recovery can be repeated without overwriting the history it came from.

Nothing needs interpreting, so it is cheap and cannot drift — but both ends must share major version, architecture and block size, the whole cluster is copied, and nothing can be filtered. MySQL replicates a logical binlog instead, which is more flexible and less faithful.

It protects the replica and endangers the primary: a slot for a standby that never returns fills the disk, and a logical slot also holds back the xmin horizon. Watch wal_status in pg_replication_slots, and set max_slot_wal_keep_size.

The standby cannot both apply and preserve, so it waits max_standby_streaming_delay and then cancels. hot_standby_feedback prevents it by holding the primary’s xmin horizon back — moving the cost to bloat on the primary rather than removing it.

It is what makes per-table, cross-version replication and CDC possible. The decoder must be able to identify a row, hence REPLICA IDENTITY, and DDL is not replicated. It is also the only supported path to a major-version upgrade with near-zero downtime.

Taken by most rewriting DDL. Because lock requests queue in arrival order, a request for it that is itself instantaneous will stall every later query on the table while it waits behind one long transaction. The fix is lock_timeout plus retry, not a faster statement.

A waiting strong request blocks every request behind it, even ones that would not conflict with the current holder. pg_blocking_pids() names who is at the front. MySQL metadata locks pile up the same way, so this is not a PostgreSQL-specific flaw.

Row locks normally live in the tuple header rather than in the lock table, which is why PostgreSQL never escalates row locks to table locks the way SQL Server does. Multixacts are the price: they have their own shared-memory structure and their own wraparound to manage.

It waits for existing transactions between passes and cannot run inside a transaction block — both direct consequences of the visibility rules. If it fails it leaves an invalid index behind that must be dropped and rebuilt.

Plan-time pruning needs constants; run-time pruning handles parameters and shows as never executed subplans. Each partition is a real relation with its own indexes, statistics and autovacuum state, which explains most surprises — including that a unique index must contain the partition key.

It partitions "the database is slow" into Lock, LWLock, BufferPin, IO, Client and IPC waits, each implicating a different mechanism. PostgreSQL keeps no history of them, so sampling — or pg_wait_sampling — is required; Oracle ships that history as ASH and AWR.

Rank by total time rather than mean: the query that matters is usually fast and frequent, not slow and rare. Its per-call I/O and WAL columns turn it into a write-cost ranking as well.

Transaction mode is the only mode that saves real resources, and it breaks everything that assumes a session: prepared statements, session-scoped advisory locks, SET, LISTEN/NOTIFY, temporary tables. Needing an external component here is the direct price of the process-per-connection design.

Added in PostgreSQL 17 via pg_basebackup --incremental, summarize_wal and pg_combinebackup. Oracle RMAN has had block-level incrementals for decades; in practice most PostgreSQL teams use pgBackRest or Barman rather than core tooling.

The only bounded allocation in the whole configuration. Roughly 25 % of RAM is a starting point rather than timidity: PostgreSQL reads and writes through the OS page cache as well, so memory given here is taken from a cache the design still depends on. A setting that would be right for InnoDB is wrong here for exactly that reason.

Per node, per backend, per parallel worker — so the worst case is a product of four numbers nobody configured, and nothing enforces a total. Set it modestly for the common query and raise it per role for the exceptional one; sizing the global value for your worst plan is how a machine that looked fine for a year runs out of memory during a traffic peak.

Vacuum holds dead tuple identifiers here between its heap and index passes; too little forces repeated full sweeps of every index, multiplying total I/O by the index count. A GIN build leans on it harder still, since its work scales with total keys rather than rows. Per worker, so multiply by autovacuum_max_workers when budgeting.

At large shared_buffers the page tables mapping it into every backend become significant, since hundreds of processes each map the same region. Huge pages cut the entry count by a factor of 512, saving memory and TLB pressure. Worth setting to on with pages reserved on any machine with more than a few gigabytes of shared buffers.

The single most valuable per-table override in PostgreSQL. At the default a table is permanently up to 20 % dead tuples, and the absolute wait grows without limit: 100 million dead rows on a 500-million-row table is most of a day. Set it around 0.01 on large tables. Not tuning — required configuration.

Added in PostgreSQL 18, and effectively the project conceding that the unbounded scale factor was a known-bad default for large tables. It bounds the damage but does not remove the case for a per-table scale factor, which triggers far sooner.

Shared across all autovacuum workers, not allocated per worker — which is why raising autovacuum_max_workers slows each one down instead of speeding cleanup up. The default was chosen when a database’s disk was a shared spinning one; 1000–2000 is routinely appropriate on modern storage. Raise this before adding workers.

An append-only table creates no dead tuples, so before PostgreSQL 13 it was never vacuumed — and therefore never frozen and never marked all-visible, so index-only scans did not work on it and wraparound crept up. This trigger exists for time-series tables specifically.

Under 10 % of the usable transaction-id window, and the margin exists so the forced vacuum has time to finish on a large table. Alert on age(datfrozenxid) rather than on this: a steadily rising value means freezing is losing, and the remaining time can be days rather than months.

The highest-value single setting in this course for a cluster that lacks it. An idle-in-transaction session holds the xmin horizon and bloats every table in the cluster, can sit at the head of a lock queue and take a table down, and occupies a pool slot. There is essentially no legitimate reason to sit idle in a transaction for minutes.

The highest-value planner change on modern hardware. The default encodes a mechanical seek on a spinning disk; on SSD or NVMe the real ratio is nearer 1.1, and leaving it at 4 makes the planner systematically reject index scans it should choose. It remains the default only because changing it would move plans on every existing installation.

Allocates nothing — it is a planner input describing shared_buffers plus the OS page cache together, so the planner can judge how much of an index scan’s repeated access will hit memory. Set too low, index scans look unattractive. Half to three quarters of system memory is a reasonable value.

The sample is 300 × this value and does not scale with the table, which is why distinct-value estimates degrade on large tables while frequency estimates stay accurate. Raise it on the specific columns whose estimates are wrong rather than globally: a higher global value lengthens every ANALYZE and adds planning time to every query.

Simultaneously a planning-time control and the only built-in way to constrain join order. Setting it to 1 makes the planner honour your written join order exactly — the closest thing PostgreSQL has to a join hint, and a commitment to maintaining that order by hand. Set it per query, never globally.

Exhaustive dynamic programming is Θ(3ⁿ), affordable to about this many relations. Past it the genetic search gives no optimality guarantee and is seeded randomly, so a large-join query can receive different plans on different executions — a candidate cause when warehouse query performance is erratic with no other change.

The automatic heuristic builds five custom plans and then switches to the generic plan if it costs less, and never revisits that decision. force_custom_plan pays planning time on every execution and buys parameter-aware plans — the fix when a skewed column causes erratic latency, and when a partitioned table loses plan-time pruning.

Recovery time is proportional to it and WAL volume is inversely proportional, because every page is re-imaged once per cycle. The costs are asymmetric in frequency — WAL is paid every second, recovery only after a crash — so 15–30 minutes is right for most systems, which is the opposite of what the default suggests.

A checkpoint triggered by this is *requested* rather than timed, and a high proportion of requested checkpoints is a feedback loop: more checkpoints means more full-page images means more WAL means more checkpoints. The default is small for any busy system, and raising it costs disk and nothing else.

Off by default and close to a free win. It compresses the page images specifically — the dominant term in WAL volume — and typically recovers a large fraction of the cost of a scattered write pattern. Try it before considering full_page_writes = off, which trades the same reduction for a risk of silent corruption.

The defence against torn pages, which redo cannot repair because replay needs a structurally valid page to start from. Safe to disable only on storage with atomic 8 kB writes or a copy-on-write filesystem; on ordinary devices it risks corruption discovered weeks later.

Five positions, each naming a different failure it does not survive. Setting it off risks losing recent commits, not corruption — a different thing from fsync = off entirely. Per-transaction granularity is the feature: relax it for the ingest that replays and leave the payments alone.

Unlimited by default, which converts "a replica went offline" into "the primary filled its disk and stopped accepting writes". Setting it chooses which thing breaks: one consumer, marked lost and rebuilt, rather than the primary. Belongs in the base configuration of any cluster with replication.

Stops standby queries being cancelled, by holding the primary’s xmin horizon back — so the cost moves to bloat on the primary rather than disappearing. Right for a replica running short queries, where the horizon is held briefly. Wrong for a reporting replica, which wants a high max_standby_streaming_delay instead so the cost stays local.

On a dedicated reporting replica, set this very high or to -1: lag does not matter there and the primary is unaffected, which is the cheapest place to put a cost that cannot be removed. On a failover candidate keep it low, since that replica must stay caught up.

Use the quorum form, ANY k (…). Naming a single standby makes the primary's availability depend on it — take that standby down for maintenance and every commit hangs. One synchronous standby combines the latency cost with an availability penalty and buys nothing the quorum form does not.

Sizes shared memory at startup — the lock table alone is allocated max_locks_per_transaction × (max_connections + max_prepared_transactions) slots — so raising it speculatively costs immediately. It is a capacity number derived from λ·E[S] plus headroom for autovacuum, replication and an admin connection, not a number to raise when connections run out.

An average across transactions rather than a per-transaction cap: one statement may take far more as long as the total fits. It is reached by queries touching hundreds of partitions, since each partition is locked separately, and the failure is "out of shared memory" rather than a wait.

A detection-latency knob, not a tolerance for deadlocks. Detection is lazy because lock waits are common and deadlocks are rare, so checking on every request would add cost to the overwhelmingly common case. The default rarely needs changing.

The fix for the lock-queue pile-up, and the reason it works is that a waiting strong lock blocks everything behind it — so the unavailable window is however long the DDL waits. Set it to a few seconds around every migration and retry; failing is the desired outcome.

Set per role rather than globally, so analytical roles get a longer bound than the application path. Together with idle_in_transaction_session_timeout it bounds the two ways a session can hold the xmin horizon indefinitely.