PostgreSQL architecture

Article.

“Why PostgreSQL?” is easy to answer with a feature list. The senior answer explains its operational behaviour from three design choices: a process per connection, old row versions kept in the table itself, and a write-ahead log as the single source of durability. Connection limits, bloat, VACUUM, replication and most outages follow from those three.

Why does a PostgreSQL table grow when you only update rows, and stop shrinking after you delete them? The answer is in how MVCC stores rows. Outputs below are from PostgreSQL 18.4 in Docker, default settings.

Processes: one backend per connection

postmaster (pid 1)       accepts, forks backends
├── client backend       one process per connection
├── checkpointer         dirty pages at checkpoints
├── background writer    dirty pages in between
├── walwriter            flushes WAL buffers
├── autovacuum launcher  starts vacuum workers
├── io worker 0..2       async reads (new in 18)
└── logical replication launcher

That is the real process list of an idle 18.4 server. Every client connection forks a new backend process with its own memory and catalogue caches. Consequences:

• Connections are expensive. pgbench, read-only queries, four clients: 27,122 transactions a second on persistent connections, 388 when each transaction opened a new one (2.5 ms per connection).
• Connections are limited. max_connections defaults to 100. Each idle backend still costs memory and makes snapshots slower to take, so thousands of direct connections hurt even when idle.
• So every production deployment pools: a pool in the application (SQLAlchemy, psycopg_pool) and often PgBouncer in transaction mode in front (Connection pooling).

Memory

• shared_buffers sits on top of the OS page cache, so data is often cached twice. The usual starting point is about 25% of RAM, leaving the rest to the OS cache, which PostgreSQL relies on deliberately.
• work_mem is not a per-server limit. A query with three hash joins and a sort may use four times it (hashes up to hash_mem_multiplier, 2.0, times more), in every backend running such a query. 200 connections × 4 nodes × 64 MB is 50 GB: raising it globally is how servers get OOM-killed. Raise it per session or per role for the reports that need it.
• When a sort or hash exceeds it, it spills to disk; EXPLAIN ANALYZE shows Sort Method: external merge Disk: ... (Reading a query plan).

Storage and MVCC

Tables are heaps: unordered 8 kB pages of row versions (tuples). Indexes point at a tuple’s physical address, its ctid (page, slot). Unlike MySQL’s InnoDB, the table is not organised by the primary key; the primary key is an ordinary index.

An UPDATE writes a new row

SELECT ctid, xmin, xmax, * FROM acct;
--  (0,1) | 783 | 0 | id=1 bal=100
UPDATE acct SET bal = bal - 10 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM acct;
--  (0,3) | 784 | 0 | id=1 bal=90      new version, new place

The old version at (0,1) is still on the page, now with xmax = 784: the transaction that ended it. xmin is the transaction that created a version. Each transaction’s snapshot decides which versions it sees, which is how readers never block writers and writers never block readers (Transaction isolation levels).

The price: every UPDATE and DELETE leaves a dead tuple behind, and something must reclaim it.

HOT updates

If no indexed column changes and the page has room, the new version goes on the same page and indexes are not touched: a heap-only tuple (HOT) update. Updating an indexed column, or a full page, means a new entry in every index on the table. That is why indexing a frequently updated column such as updated_at costs more than the index itself, and why hot tables are sometimes created with fillfactor = 90 to leave room on each page.

TOAST

Values over about 2 kB are compressed and moved out of line into a TOAST table. A SELECT * that touches a large jsonb or text column pays to fetch and decompress it even if the application ignores it.

VACUUM and the xmin horizon

VACUUM removes dead tuples that no running transaction can still see, marks their space reusable, and updates the visibility map that index-only scans depend on. Autovacuum runs it when a table’s dead tuples pass a threshold (VACUUM, autovacuum, table bloat, and transaction wraparound).

The catch is the xmin horizon: a dead tuple can only be removed when it is older than the oldest snapshot still open. One long transaction holds back cleanup for the whole database:

-- a REPEATABLE READ transaction open, 100,000 rows updated, then VACUUM
tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable

-- after that transaction committed
tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
table size: 7080 kB before and after

Two facts from that output. Cleanup waited for the transaction, not for VACUUM.

And the table did not shrink: VACUUM makes space reusable inside the file, it does not return it to the OS. Only VACUUM FULL or pg_repack rewrites the table smaller, and VACUUM FULL locks it exclusively while it does.

Things that hold the horizon back: a long transaction, an idle in transaction session, an abandoned replication slot, a long query on a replica with hot_standby_feedback on, or a forgotten prepared transaction.

WAL: durability and replication

Every change is written to the write-ahead log before the data page changes, and a commit returns once its WAL record is flushed. Data pages are written later by the background writer and the checkpointer; after a crash, recovery replays WAL from the last checkpoint (WAL, Checkpoints, and fsync).

The same log is the replication stream. A physical replica replays WAL byte for byte, giving an identical read-only copy. Logical replication decodes WAL into row changes, which can feed another PostgreSQL version, a subset of tables, or CDC tools such as Debezium (Replication and Logical Decoding).

synchronous_commit chooses how much of this a commit waits for, from not flushing locally to applied on a synchronous replica.

What changed in PostgreSQL 18

The last one matters operationally: before 18, a major-version upgrade started with empty statistics and bad plans until ANALYZE finished.

PostgreSQL vs MySQL (InnoDB), in brief

Neither layout is better in general. The clustered index makes primary-key range scans cheap and secondary-index lookups a second B-tree descent; PostgreSQL’s heap makes every index equal and pays in VACUUM instead (MySQL).

Where it breaks in production

Connection storms

A deploy restarts 50 pods, each opening a pool of 20: 1,000 connections against max_connections = 100, FATAL: sorry, too many clients already, and the new pods fail health checks. Size pools from the database’s limit downward, and put PgBouncer in front when the number of clients is not under your control.

idle in transaction

An application that opens a transaction, then calls an external API before committing, leaves sessions idle in transaction: they hold locks and the xmin horizon. Bloat grows on every hot table, queries slow down, and nothing in the application logs explains why. Set idle_in_transaction_session_timeout, and keep network calls out of transactions. To find who holds the horizon:

SELECT pid, state, now() - xact_start AS xact_age,
       backend_xmin, left(query, 40) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start
LIMIT 5;

The oldest xact_start at the top is the session to look at; check pg_replication_slots and pg_prepared_xacts as well, which this view does not show.

A replication slot nobody reads

A logical slot whose consumer died keeps every WAL segment from that point, and the disk fills with pg_wal. Since 13, max_slot_wal_keep_size caps it; monitor pg_replication_slots for inactive slots.

Transaction ID wraparound

Transaction IDs are 32-bit. If VACUUM cannot freeze old tuples, because it is blocked by the horizon or too slow on a huge table, the database stops accepting writes to protect itself. It is rare, it is a full outage, and the warnings appear in the log long before; alert on age(datfrozenxid).

work_mem raised globally

A slow report is fixed by setting work_mem = 256MB in the config. Weeks later, a traffic spike runs many such queries at once and the OOM killer takes the postmaster down. Set it per role or per query.

The trade not taken

PostgreSQL chose processes over threads, and in-table versions over an undo log. Processes isolate crashes and make the code simpler, at the cost of expensive connections, which the ecosystem answers with poolers. Keeping old versions in the heap makes rollback instant and readers never wait, at the cost of VACUUM and bloat.

An undo log, as in InnoDB and Oracle, keeps the table compact but makes long-running readers reconstruct old rows and can fail them with “snapshot too old”. Both trades show up as the operational work a PostgreSQL team does: pooling and vacuum tuning.

Related

• VACUUM, autovacuum, table bloat, and transaction wraparound - tuning autovacuum
• WAL, Checkpoints, and fsync - WAL and checkpoints
• Connection pooling - pools and PgBouncer
• Transaction isolation levels - snapshots
• Replication and Logical Decoding - replication
• MySQL - the InnoDB comparison
• SQL and NoSQL - choosing a database