How PostgreSQL Really Works Under the Hood: A Visual Guide
Most developers treat PostgreSQL as a black box — you write a query, rows come back, and everything seems fine. That works until it doesn't. Slow queries, bloated tables, mysterious locks, and replication lag all trace back to internals that are rarely taught but frequently relevant. Here is a structured look at how Postgres actually processes your data.
The Journey of a Single Query
When a query hits your Postgres instance, it travels through a well-defined pipeline before a single row is touched.
- Parser — The raw SQL string is tokenized and validated syntactically, producing a parse tree.
- Rewriter — Rule-based transformations are applied. This is how views get expanded — a
SELECTagainst a view is silently rewritten into the underlying query. - Planner / Optimizer — The most consequential step. The planner generates candidate execution plans, estimates their cost using statistics stored in
pg_statistic, and picks the cheapest one. Bad statistics = bad plans. - Executor — The winning plan is executed, pulling rows through a tree of nodes (Seq Scan, Index Scan, Hash Join, etc.).
Understanding this pipeline matters the moment you start reading EXPLAIN ANALYZE output. Each node in that output corresponds directly to the executor tree.
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.name;
The BUFFERS option reveals cache hits vs. disk reads — a critical signal when diagnosing performance.
MVCC: Why Postgres Never Overwrites Data
Multi-Version Concurrency Control (MVCC) is the mechanism that lets readers and writers coexist without locking each other out. The key idea: Postgres never modifies a row in place. Instead, every UPDATE writes a new version of the row and marks the old one as expired. Every DELETE marks a row as dead without removing it immediately.
Each row carries two hidden system columns:
xmin— the transaction ID that created this row version.xmax— the transaction ID that deleted or superseded it (zero if the row is still live).
When a query executes, it receives a snapshot — a consistent view of which transactions were committed at that moment. Rows are included or excluded based on xmin/xmax against that snapshot. This means two long-running transactions can read the same table simultaneously and each see a coherent, stable picture even as other writes occur.
The MVCC Trade-off: Table Bloat and VACUUM
Dead row versions accumulate. Left unchecked, they inflate table and index size, slow sequential scans, and can eventually exhaust the 32-bit transaction ID space — a catastrophic event known as transaction ID wraparound. VACUUM is the process that reclaims dead tuples. AUTOVACUUM runs this automatically, but on high-write tables it is worth tuning aggressively:
autovacuum_vacuum_scale_factor— lower this for large tables so autovacuum triggers earlier.autovacuum_vacuum_cost_delay— reduce it if autovacuum is falling behind writes.
The Buffer Cache: Your First Layer of Performance
Postgres does not read pages directly from disk for every query. It maintains a shared buffer cache (configured by shared_buffers) — a pool of 8 KB pages held in memory. When the executor needs a page, it checks the cache first. A cache hit costs nanoseconds; a disk read costs milliseconds.
The practical implication: shared_buffers is often the single highest-leverage configuration parameter. A commonly accepted starting point is 25% of available RAM, though working-set size matters more than any rule of thumb.
Beyond the buffer cache, Postgres also leverages the OS page cache, which is why servers with ample RAM tend to perform well even before Postgres-level tuning.
Write-Ahead Logging: Durability Without Sacrificing Throughput
Every change in Postgres is first written to the Write-Ahead Log (WAL) before it touches the actual data files. This guarantees durability — if the server crashes mid-write, WAL records let Postgres replay changes and restore a consistent state on restart.
WAL is also the backbone of streaming replication. Standby servers continuously consume WAL records generated by the primary, applying them to maintain an up-to-date replica. This is why replication lag is measured in WAL bytes — a lagging standby simply hasn't applied recent WAL segments yet.
Key WAL tuning levers for high-throughput SaaS applications:
wal_level— set toreplicaorlogicaldepending on replication needs.synchronous_commit— setting this toofftrades strict per-transaction durability for significant write throughput gains, acceptable for non-critical writes.checkpoint_completion_target— spread checkpoint I/O over time to avoid spikes.
Indexes: Choosing the Right Weapon
Postgres ships with multiple index types, and picking the wrong one wastes storage and CPU.
| Index Type | Best For |
|---|---|
| B-tree | Equality, range queries — the default, covers 90% of cases |
| GIN | Full-text search, JSONB containment queries |
| GiST | Geometric data, nearest-neighbor searches |
| BRIN | Append-only, naturally ordered large tables (timestamps, IDs) |
| Hash | Strict equality only, rarely preferred over B-tree |
A common mistake on SaaS platforms is indexing every column "just in case." Indexes consume storage, slow down writes, and must be maintained by VACUUM. Index only what your query planner will actually use — and verify with pg_stat_user_indexes that indexes are being hit.
What This Means for Software Teams
Understanding Postgres internals is not academic. Every architectural decision — schema design, connection pooling strategy, replication topology, caching layer — intersects with these mechanisms. Teams that grasp MVCC design schemas with bloat in mind. Teams that understand WAL make informed trade-offs between durability and throughput. And teams that respect the planner write queries the optimizer can reason about, rather than fighting it with unnecessary complexity.
At Code!nk Technologies, these internals inform how we architect data layers for SaaS platforms and custom applications — from the initial schema to production performance tuning. The database is rarely the bottleneck when it is understood well.
Source: PGSimCity — How PostgreSQL Works, Nikolay S. — https://nikolays.github.io/PGSimCity/
