PostgreSQL Architecture and Processes

PostgreSQL architecture is the map from a SQL request to durable bytes on disk. In this lesson you will learn what happens after a client connects, which server processes cooperate to execute the statement, where table changes live before and after commit, and how to diagnose the common cases where those processes appear stuck. This chapter sits at the foundation of the course because later topics such as indexes, transactions, replication, and tuning all depend on the same process model.

Purpose and Outcome

PostgreSQL uses a process-per-connection architecture. A small parent process, often called the postmaster in older documentation and visible as the main postgres server process, accepts new connections. Each accepted client gets a dedicated backend process. Background processes handle shared work such as writing dirty buffers, flushing write-ahead log records, checkpointing, vacuum maintenance, statistics, and replication. The outcome is a database server whose moving parts can be inspected with operating system tools and catalog views instead of treated as a black box.

Core Process Model

A running PostgreSQL instance is a cluster: one data directory containing configuration files, write-ahead log files, and one or more databases. The server starts by reading configuration, opening shared memory, creating lightweight locks and buffer structures, then launching background workers. Client programs such as psql, application pools, and migration tools connect over TCP or a Unix socket. Authentication happens before SQL execution; after that, the assigned backend parses, rewrites, plans, and executes statements for that session.

The dedicated backend does not write every changed row directly to the table file at commit time. PostgreSQL first records the logical physical change in the write-ahead log, usually abbreviated WAL. Data pages are cached in shared buffers and become dirty when modified. WAL must be flushed far enough for the commit record before PostgreSQL can acknowledge a durable commit. The dirty table and index pages can be written later by the background writer, checkpointer, or the backend itself when memory pressure requires it. This separation is why commits can be fast while crash recovery remains possible.

PostgreSQL concurrency is built around MVCC, multi-version concurrency control. An update creates a new tuple version instead of overwriting the old one in place. Transaction identifiers and tuple visibility rules let one backend see a stable snapshot while another backend commits newer data. Vacuum later removes tuple versions no active snapshot can still need. That means long-running transactions are not merely slow queries; they can prevent cleanup and cause table and index bloat.

Important Background Processes

Configuration and API Anatomy

The process architecture is controlled through configuration and observed through catalog views. max_connections caps backend processes. shared_buffers sizes the main page cache inside PostgreSQL. WAL behavior is shaped by settings such as wal_level, checkpoint timing, and synchronous commit policy. Autovacuum settings control when maintenance workers wake up and how aggressively they work. These settings interact: raising connections without a pool increases process count and memory demand, while raising buffers without understanding the operating system cache can move pressure rather than solve it.

The main inspection APIs are SQL views. pg_stat_activity shows backends, states, wait events, transaction age, and current queries. pg_stat_bgwriter exposes checkpoint and buffer writing counters. pg_stat_wal shows WAL generation and write activity on installations that expose it. pg_stat_all_tables shows live and dead tuples plus vacuum and analyze history. These views are the normal starting point before changing configuration.

Example 1: Inspect Live Sessions and Background State

This first example asks the server what processes it is currently using. It is intentionally read-only, so it is safe in a development database and usually safe in production for a privileged diagnostic session.

SELECT pid, backend_type, state, wait_event_type, wait_event
FROM pg_stat_activity
ORDER BY backend_type, pid;

SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_backend
FROM pg_stat_bgwriter;

The first query returns one row for each visible backend or background process. A normal interactive psql session often appears as client backend with state active while the query runs, then idle afterward. Background rows may include the checkpointer, autovacuum launcher, WAL writer, or logical replication workers depending on configuration. The second query returns cumulative checkpoint and buffer-write counters since statistics were last reset. If buffers_backend rises quickly during write-heavy work, client backends are doing buffer writes themselves, which can show up as request latency.

Example 2: Watch One Backend Hold a Snapshot

The next example demonstrates why PostgreSQL can serve readers and writers at the same time. Run the setup once, then use two terminals connected to the same database.

DROP TABLE IF EXISTS architecture_demo;
CREATE TABLE architecture_demo (
    id integer PRIMARY KEY,
    note text NOT NULL
);
INSERT INTO architecture_demo VALUES (1, 'original');

-- Session A
BEGIN;
SELECT note FROM architecture_demo WHERE id = 1;

-- Session B
UPDATE architecture_demo SET note = 'changed' WHERE id = 1;
COMMIT;
SELECT note FROM architecture_demo WHERE id = 1;

-- Session A
SELECT note FROM architecture_demo WHERE id = 1;
COMMIT;
SELECT note FROM architecture_demo WHERE id = 1;

With the default READ COMMITTED isolation level, Session A’s first select returns original. Session B updates and commits, and its final select returns changed. Session A’s second select also returns changed because each statement gets a fresh snapshot at this isolation level. If you change Session A to BEGIN ISOLATION LEVEL REPEATABLE READ;, its second select continues to return original until it commits. That behavior is MVCC in action: different sessions can observe different valid versions according to snapshot rules.

Example 3: Relate Dead Tuples to Vacuum

This example connects architecture to maintenance. Updates leave old tuple versions behind. Vacuum is the process that marks no-longer-visible versions reusable.

DROP TABLE IF EXISTS vacuum_demo;
CREATE TABLE vacuum_demo AS
SELECT generate_series(1, 1000) AS id, 'v1'::text AS payload;

UPDATE vacuum_demo SET payload = 'v2';

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_all_tables
WHERE relname = 'vacuum_demo';

VACUUM vacuum_demo;

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_all_tables
WHERE relname = 'vacuum_demo';

After the update, statistics may show approximately 1000 live tuples and approximately 1000 dead tuples once stats catch up. The exact timing is asynchronous, so rerunning the select after a short pause may be necessary. After manual vacuum, last_vacuum is populated and n_dead_tup should drop or trend down. The important lesson is not the exact counter value; it is that PostgreSQL’s architecture turns updates into new tuple versions, and maintenance processes reclaim the obsolete versions later.

Design Choices and Trade-offs

The process-per-connection model is simple to reason about and isolates faults better than a purely threaded design, but it makes thousands of direct application connections expensive. Production systems commonly place a connection pooler between application workers and PostgreSQL so the database runs a bounded number of backends. Pooling changes session semantics, so features that rely on session state, prepared statements, temporary tables, or advisory locks must be reviewed carefully.

MVCC reduces reader-writer blocking, but it shifts work into vacuum and storage management. A system with long reports, abandoned transactions, or disabled autovacuum may continue accepting writes while silently accumulating dead tuples. WAL provides crash recovery and replication foundations, but it adds write amplification. Synchronous durability is safer after power loss; relaxed commit settings can improve latency for workloads that can replay or tolerate losing the last acknowledged-looking transaction only when the application has explicitly accepted that risk.

Failure Modes and Troubleshooting

Connection exhaustion: the symptom is an error such as too many clients or application requests hanging while trying to connect. The usual cause is unbounded application workers, missing pooling, or sessions left idle in transaction. Diagnose with SELECT state, count(*) FROM pg_stat_activity GROUP BY state; and inspect the oldest transactions. Correct it by fixing leaks, using a pooler, setting transaction timeouts, and keeping max_connections consistent with memory.

Autovacuum falling behind: symptoms include growing table size, slower index scans, stale row estimates, or warnings about transaction ID wraparound. Causes include long transactions, tables with heavy churn, too few autovacuum workers, or cost settings that are too conservative. Diagnose with pg_stat_all_tables, transaction ages in pg_stat_activity, and table size functions. Correct the oldest blocking transaction first, then tune per-table autovacuum settings or run targeted manual vacuum during a maintenance window.

Checkpoint or WAL I/O stalls: symptoms include periodic latency spikes, high commit time, or waits on WAL or data file I/O. Causes include slow storage, checkpoints requested too frequently, bulk writes, or insufficient WAL capacity for bursts. Diagnose by comparing checkpoints_timed and checkpoints_req, looking at wait events, and measuring storage latency outside PostgreSQL. Correct by smoothing write bursts, increasing checkpoint headroom where appropriate, and placing WAL on reliable low-latency storage.

Security, Performance, and Reliability Implications

Architecture affects security because every backend runs with an authenticated database role and can hold locks, memory, and snapshots. Least-privilege roles limit damage from compromised application credentials, but operational roles also need controlled visibility into diagnostic views. Performance depends on bounding active backends, keeping working sets near memory, and avoiding long transactions that block cleanup. Reliability depends on WAL integrity, tested backups, and enough disk space for WAL and table growth during outages or replication lag.

Hands-on Lab: Trace a Statement Through the Server

Prerequisites: a local PostgreSQL instance, psql access to a scratch database, and permission to create and drop a table. Do not use a production database for this lab.

1. Create a table and load sample rows using the setup from Example 3.
2. Open a second terminal and run SELECT pid, state, backend_type, query FROM pg_stat_activity WHERE backend_type = 'client backend';.
3. In the first terminal, start BEGIN;, run an update against vacuum_demo, and leave the transaction open.
4. In the second terminal, query pg_stat_activity again and note the open transaction state and query text.
5. Commit the first terminal, run the dead-tuple query from Example 3, then run VACUUM vacuum_demo;.
6. Verify that the session state returns to idle and the table statistics show vacuum activity.
7. Cleanup with DROP TABLE IF EXISTS vacuum_demo;.

Verification is successful when you can identify your backend process, explain why the open transaction is visible, and show that vacuum updates maintenance statistics after old tuple versions are no longer needed. Rollback is simply ROLLBACK; before commit if you want to abandon the update; after commit, cleanup drops only the scratch table.

Assessment Exercises

1. A service has 600 application workers and sets max_connections to 800. Explain two risks this creates and propose a safer connection architecture.
2. In READ COMMITTED, why can two selects in the same transaction see different row versions after another session commits?
3. A table receives constant updates and n_dead_tup grows for hours. List the catalog views you would inspect and the order in which you would correct likely causes.
4. Why does PostgreSQL flush WAL before acknowledging a durable commit instead of writing every changed table page immediately?
5. During a latency incident, checkpoints_req rises much faster than expected. What does that suggest, and what evidence would you gather before changing settings?

Summary

PostgreSQL is organized around a parent server process, per-client backend processes, shared memory, WAL, MVCC tuple versions, and background maintenance workers. SQL performance and reliability problems often become understandable when you map a symptom to that architecture: too many sessions mean too many backends, slow commits often involve WAL, bloat often means old tuple versions and delayed vacuum, and checkpoint spikes are write scheduling problems. Keep this process model in mind throughout the course; it is the base layer underneath query planning, indexing, transactions, backup, and replication.