PostgreSQL

Almost every surprising thing PostgreSQL does traces back to one design decision: an UPDATE does not modify a row, it writes a new version of it and leaves the old one behind. That is multiversion concurrency control, and it buys readers that never block writers. It costs you dead rows, table bloat, a background process whose job is to clean them up, and a 32-bit counter that will destroy your database if that process ever stops keeping pace.

Process and memory architecture

The supervisor process (historically the postmaster, and still the postgres binary) listens for connections and forks a new backend process for every connection. Client and backend then talk directly, with no further involvement from the supervisor. Alongside the backends the cluster runs a set of background processes: the checkpointer, the background writer, the WAL writer, the autovacuum launcher and its workers, and (from 18) I/O workers.

The per-connection process model is why connection count is expensive in PostgreSQL and why connection pooling is a near-universal deployment requirement. That subject is out of scope here; see PgBouncer for pooling and Pgpool-II if you also need read/write splitting.

Memory splits into one shared region and a per-operation allocation, and confusing the two is the most common sizing error.

work_mem is not a per-query budget. The documentation is explicit: a complex query may perform several sorts and hashes at the same time, each generally allowed to use work_mem, and many sessions may be doing this concurrently. Total memory used can therefore be many multiples of the configured value. A plan with eight hash joins at work_mem = 64MB and hash_mem_multiplier = 2.0 can reach roughly 1GB in a single backend. Partitionwise joins and aggregates make this worse: the documentation warns that the number of work_mem-restricted nodes grows linearly with the number of partitions scanned, which is why enable_partitionwise_join and enable_partitionwise_aggregate both default to off.

The safe pattern is a conservative global work_mem with per-role overrides for the handful of reporting queries that need more:

ALTER ROLE analytics SET work_mem = '256MB';

maintenance_work_mem behaves differently. Only one such operation runs per session at a time, so it can safely be much larger than work_mem. The caveat is autovacuum: up to autovacuum_max_workers copies of it may be allocated concurrently. Set autovacuum_work_mem separately if you raise maintenance_work_mem for index builds.

effective_cache_size allocates nothing at all. It tells the planner how much of the table it can assume is already cached, which makes index scans look cheaper. On a dedicated database host, a value in the region of shared buffers plus the OS page cache is reasonable; the 4GB default is usually far too low on modern hardware and biases the planner towards sequential scans.

MVCC: xmin, xmax, dead tuples and bloat

Every row version carries hidden system columns. Two of them define visibility:

You can look at them directly:

SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 42;

A transaction sees a row version if xmin committed and is in the past relative to its snapshot, and xmax is either zero or belongs to a transaction that has not committed. A non-zero xmax on a visible row is normal: usually the deleting transaction has not committed, or was rolled back.

This is why an UPDATE writes a new row version instead of modifying in place: the old version must remain readable by any transaction whose snapshot predates the update. The old version becomes a dead tuple once no snapshot can still see it. DELETE produces dead tuples the same way; it only sets xmax.

Dead tuples that are not reclaimed are bloat: physical space in the table and its indexes that holds nothing useful. A table that is updated heavily and vacuumed rarely grows without bound even though its live row count is constant, and every sequential scan pays for the empty space.

Two mechanisms limit the damage. The first is heap-only tuples (HOT): if an update touches no indexed column and there is room on the same page, the new version goes on that page with no new index entries, and intermediate versions can be removed during ordinary operation including by SELECTs. Lowering a table’s fillfactor increases the chance of a HOT update. BRIN, the only summarising index in core, does not block HOT.

The second is VACUUM. It removes dead row versions from tables and indexes and marks the space available for reuse, but it does not return that space to the operating system, except where whole pages at the end of the table are free and an exclusive lock is readily available. Only VACUUM FULL (or CLUSTER, or a table-rewriting ALTER TABLE) shrinks the file, and all of them take an ACCESS EXCLUSIVE lock and need extra disk space roughly equal to the table size while they run. Autovacuum never issues VACUUM FULL. The goal of routine vacuuming is not minimum size, it is steady-state size: often enough that you never need VACUUM FULL.

Transaction ID wraparound

Transaction IDs are 32 bits. Normal XIDs are compared using modulo-2³² arithmetic, so for any given XID two billion others are “older” and two billion are “newer”. The space is a circle with no endpoint. A row version stays in the past for the next two billion transactions. Past that point it suddenly appears to be in the future, and becomes invisible. The data is still on disk; you just cannot read it.

The fix is freezing. VACUUM marks sufficiently old row versions frozen, which means they are treated as older than every normal XID regardless of where the counter is. Since 9.4 this is a flag bit and not a rewrite of xmin, so the original xmin is preserved for forensics. Every table in every database must be vacuumed at least once every two billion transactions.

“Autovacuum to prevent wraparound” is what happens when the ordinary triggers have not fired in time. Any table whose pg_class.relfrozenxid is older than autovacuum_freeze_max_age is vacuumed even if autovacuum is turned off. That vacuum is aggressive: it visits every page that might contain unfrozen XIDs, not just pages with dead tuples, so on a large cold table it is a full-table read that appears from nowhere and cannot be cancelled without simply coming back.

Monitor the age directly. This is the single most valuable maintenance query on the page:

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

If freezing falls far enough behind, the server warns at forty million transactions from wraparound:

WARNING:  database "mydb" must be vacuumed within 39985967 transactions

and at fewer than three million remaining it refuses to assign new XIDs:

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"

Read-only transactions still work; anything that writes fails. The documented recovery is not single user mode. Commit or roll back old prepared transactions found in pg_prepared_xacts, end long-running transactions found via age(backend_xid) in pg_stat_activity, drop abandoned replication slots with a large age(xmin) in pg_replication_slots, then run a plain database-wide VACUUM as a superuser. Do not use VACUUM FULL here: it requires an XID and will fail, or in superuser mode consume one and make things worse. Do not use VACUUM FREEZE either. It does more work than is needed to restore service.

Note the three usual causes in that list. A forgotten replication slot, an idle-in-transaction session and an orphaned prepared transaction each hold back the xmin horizon, and stop vacuum from freezing anything however aggressively it is configured.

Autovacuum thresholds

Autovacuum consists of a persistent launcher that starts one worker per database every autovacuum_naptime seconds, and the workers themselves. Worker processes do not count against max_connections.

A table is vacuumed when the number of dead tuples exceeds:

vacuum threshold = Min(autovacuum_vacuum_max_threshold,
                       autovacuum_vacuum_threshold
                       + autovacuum_vacuum_scale_factor * pg_class.reltuples)

The 20% scale factor is the problem. On a 10,000-row table it means vacuuming after about 2,050 dead tuples, which is fine. On a 500 million row table it means waiting for 100 million dead tuples before autovacuum even starts. By that point the vacuum is enormous, the table is badly bloated, and query plans built on stale statistics are already wrong. The 0.1 analyze scale factor has the same shape: 50 million modified rows before ANALYZE runs.

PostgreSQL 18 addresses the vacuum half of this with autovacuum_vacuum_max_threshold, which caps the computed threshold at 100 million tuples by default. That is still a lot. On 14-17 there is no cap at all, and on every version the analyze threshold remains uncapped. Override per table:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 2000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 2000,
  autovacuum_vacuum_cost_delay = 2
);

The cost is more frequent vacuum I/O against your busiest table, which is nearly always the right trade: the alternative is one very large vacuum instead of many small ones. Two further caveats: autovacuum never processes partitioned tables themselves, only their partitions, so ANALYZE on the parent must be scheduled manually; and it cannot touch temporary tables at all.

WAL, checkpoints and durability

Every change is written to the write-ahead log before the modified data page reaches disk. Recovery replays WAL from the last checkpoint. A checkpoint flushes dirty buffers and establishes the point from which replay begins.

full_page_writes exists because an OS crash during a page write can leave a torn page - part old, part new - and row-level WAL records cannot repair that. Writing the full image after each checkpoint guarantees replay can reconstruct it. The cost is WAL volume, concentrated in a burst immediately after every checkpoint. The documented way to reduce it is to checkpoint less often, by raising checkpoint_timeout and max_wal_size; the trade is longer crash recovery. Turning full_page_writes off carries risks similar to disabling fsync, only smaller. It is not a tuning knob.

synchronous_commit is the real durability dial, and unlike fsync it does not risk an inconsistent database. In off mode a crash may lose recently acknowledged transactions, but the database ends up exactly as if they had been cleanly aborted. The maximum exposure is three times wal_writer_delay.

If synchronous_standby_names is empty, only on and off differ; the other three behave like on. remote_apply is the setting that makes a read on a standby guaranteed to see a write that has just been acknowledged, and the documentation is blunt that it causes much larger commit delays because it waits for replay. It is set per transaction, which is the sensible way to use it:

BEGIN;
SET LOCAL synchronous_commit = 'remote_apply';
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

Index types

B-tree is the default and correct for most cases. The others are not general-purpose replacements; each answers a specific class of query.

BRIN is the one with a hard prerequisite: it stores summaries per range of physical blocks, so it is only effective when the column correlates with the table’s physical order. On an append-only event log keyed by insertion time it is far smaller than the equivalent B-tree; on a randomly ordered column it is close to useless.

Prefix-matching with B-tree needs a text-pattern operator class outside the C locale:

CREATE INDEX idx_users_email_prefix ON users (email text_pattern_ops);
CREATE INDEX idx_docs_search ON docs USING GIN (to_tsvector('english', body));
CREATE INDEX idx_events_ts ON events USING BRIN (created_at);

Replication

Physical (streaming) replication ships WAL byte for byte. The standby is an exact block-level copy of the primary, all databases, all objects, read-only. It needs wal_level = replica or higher.

Logical replication decodes WAL into row changes and applies them on a subscriber, per publication. It needs wal_level = logical, which increases WAL volume, particularly with REPLICA IDENTITY FULL. Its restrictions are the deciding factor: DDL is not replicated, sequence data is not replicated, large objects are not replicated, and only tables can be published, not views, materialised views or foreign tables. A schema change on the publisher that the subscriber has not received will error the apply worker until you fix it by hand.

A replication slot makes the primary retain WAL until the consumer has confirmed it. That is what stops a standby from falling irrecoverably behind. It is also the standard mechanism for taking a PostgreSQL server down. With max_slot_wal_keep_size = -1, the default, a slot may retain an unlimited amount of WAL. A standby that has been switched off for a fortnight, or a logical subscriber whose apply worker is stuck on a schema mismatch, will fill pg_wal until the filesystem is full and the primary stops. The same slot also holds back xmin, so vacuum cannot freeze and wraparound age climbs at the same time.

Set a bound, and monitor:

ALTER SYSTEM SET max_slot_wal_keep_size = '64GB';
SELECT pg_reload_conf();

SELECT slot_name, slot_type, active, wal_status, safe_wal_size,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
       age(xmin) AS xmin_age, inactive_since
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

The trade-off is explicit: bounding retention means a slot can be invalidated and its consumer will need rebuilding, instead of the primary going down. On 18, idle_replication_slot_timeout gives a time-based version of the same protection, evaluated at checkpoint time.

hot_standby_feedback deserves a warning of its own. It stops the standby cancelling long read queries by telling the primary not to clean up rows the standby still needs. Bloat on the primary is then controlled by query duration on the replica. The documentation notes it “can cause database bloat on the primary for some workloads”. Prefer raising max_standby_streaming_delay when you can tolerate replication lag instead.

Diagnostics

Three views answer most questions.

pg_stat_activity, one row per backend, is the first thing to look at:

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       age(backend_xid) AS xid_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle'
ORDER BY xact_start;

The state to hunt for is idle in transaction. A backend in that state holds its snapshot open, so it pins the xmin horizon and blocks vacuum cluster-wide. A large age(backend_xid) on such a session is the direct cause of bloat you cannot vacuum away. idle_in_transaction_session_timeout is the fix; transaction_timeout, added in 17, bounds total transaction duration regardless of state.

pg_stat_user_tables shows where the bloat is and whether autovacuum is keeping up:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       n_mod_since_analyze, last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

n_dead_tup climbing while last_autovacuum stays old means autovacuum is either not triggering (thresholds too high for the table size) or not finishing (too few workers, or throttled by the cost delay). n_tup_hot_upd versus n_tup_upd in the same view tells you what fraction of updates avoided index maintenance; a low ratio on a heavily updated table is an argument for lowering fillfactor or dropping an index. Note these counters are eventually consistent, flushed no more than once per second, and are reset by an unclean shutdown.

EXPLAIN (ANALYZE, BUFFERS) reports what the query actually did:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM events WHERE created_at > now() - interval '1 day';

ANALYZE executes the statement, so wrap it in a transaction you roll back if it writes. Compare estimated rows against actual: a large discrepancy means stale or insufficient statistics, and points at ANALYZE or default_statistics_target (default 100) rather than at the plan. BUFFERS separates shared hits from reads, which tells you whether a slow node is CPU-bound or waiting on I/O. A Sort Method line reporting a disk sort means that node exceeded work_mem; log_temp_files records the same spills cluster-wide. From 18, BUFFERS is included in EXPLAIN ANALYZE automatically.

Version-specific guides

• PostgreSQL 19 - the next major release, currently in beta: REPACK, the WAIT command, logical replication of sequences, parallel and prioritised autovacuum, plan advice, and the full upgrade-breakers list.

What changed, and in which release

Three of these bite on upgrade, not on adoption. Data checksums on by default means pg_upgrade refuses to run between clusters whose checksum settings differ; use initdb --no-data-checksums to match an old cluster. Eager freezing in 18 changes the I/O profile of ordinary vacuums on large mostly-static tables: more work per normal vacuum, less work at the next aggressive one. And autovacuum_vacuum_max_threshold will make autovacuum fire more often on very large tables than it did on 17, which is the intent, but it is a behaviour change on a table you may have tuned by hand.

What PostgreSQL is not

It is a single-primary database. Physical replicas are read-only, and promotion is a manual operation unless something else is deciding: Patroni with etcd, Consul or ZooKeeper is the usual answer, with HAProxy or keepalived owning the virtual IP. Nothing in core does automatic failover.

It does not pool connections, and its per-connection process model makes that omission expensive. Put PgBouncer in front of it in transaction mode, or Pgpool-II if you also want SELECTs routed to replicas. Neither belongs in the database itself.

And it does not shard. Logical replication moves rows between clusters; it does not partition a workload across them. That is an application-level or extension-level decision.