AlgoInsight

System Design

How PostgreSQL Works Internally

Postgres is the default serious database for good reason - but most engineers use it without knowing what happens inside. Here's the internal architecture: the processes, the memory, how a query flows, and the MVCC design that makes it tick.

By Admin2026-06-1816 min readpostgresqldatabasesinternalsmvccwalsystem-design

PostgreSQL has quietly become the default serious database for a huge swath of the industry - reliable, feature-rich, and free. Most engineers use it every day without ever knowing what happens inside when they run a query. And that's fine for getting work done, but understanding Postgres internally changes how you reason about performance, concurrency, and the occasional mysterious behavior. This guide opens the box: the process architecture, the memory, how a query actually flows from text to result, and the MVCC design that's central to how Postgres handles concurrency.

This isn't an exhaustive source-code tour - it's the mental model that makes Postgres make sense.

The process architecture: a family of processes

The first thing to understand about Postgres is that it's built on processes, not threads. When Postgres is running, it's not one program - it's a family of cooperating processes.

At the center is a supervisor process (historically called the postmaster). It starts up, listens for incoming client connections, and - crucially - when a client connects, it spawns a dedicated backend process to serve that connection. So every client connection gets its own backend process, which handles all of that client's queries for the life of the connection.

Around these, a set of background processes handle shared housekeeping: writing dirty data to disk, managing the write-ahead log, running cleanup, gathering statistics, and so on. These run independently of any client, keeping the whole system healthy.

graph TD
    C1[Client 1] --> PM[Supervisor process<br/>listens for connections]
    C2[Client 2] --> PM
    PM --> B1[Backend process 1]
    PM --> B2[Backend process 2]
    B1 --> SHM[(Shared memory<br/>shared buffers, WAL buffers)]
    B2 --> SHM
    BG[Background processes<br/>writer, WAL writer, vacuum] --> SHM
    SHM --> DISK[(Disk: data files + WAL)]

This diagram shows PostgreSQL's process architecture. Clients connect to a central supervisor process, which spawns a dedicated backend process for each connection. The backend processes, along with a set of background processes such as the writer, the write-ahead-log writer, and vacuum, all share a common shared-memory area containing the shared buffers and WAL buffers. That shared memory is backed by disk, which holds the data files and the write-ahead log.

One practical consequence of the process-per-connection model: connections aren't free. Each backend process consumes memory and resources, so a flood of connections is expensive - which is exactly why connection pooling matters with Postgres. You put a pooler in front so that many application clients share a smaller set of actual database connections, instead of each opening its own costly backend.

Memory: shared and private

Postgres memory comes in two broad kinds, mirroring the process structure:

The interplay matters: the shared buffer pool absorbs repeated reads across all connections, while each backend's private memory handles the per-query work. Tuning these is much of Postgres performance tuning.

How a query flows

When you send a query, it goes through a clear pipeline inside the backend process serving your connection:

  1. Parse. The SQL text is parsed into an internal tree representation, checking syntax and resolving the tables and columns referenced.
  2. Plan / optimize. The query planner (optimizer) considers different ways to execute the query - which indexes to use, which join methods, in what order - estimates the cost of each, and picks the plan it judges cheapest. This is where the decisions about using an index versus scanning a table get made (the EXPLAIN command shows you the chosen plan).
  3. Execute. The executor runs the chosen plan, reading data from the shared buffers (or disk), applying filters and joins, and producing the result rows, which stream back to the client.

This parse-plan-execute pipeline is why two queries that return the same data can perform very differently: the planner's choices, driven by its cost estimates and the available indexes and statistics, determine how much work the execution actually does.

MVCC: how Postgres handles concurrency

Here's the part that's most distinctive about Postgres internally, and most worth understanding: Multi-Version Concurrency Control (MVCC).

The problem MVCC solves: when many transactions read and write the same data concurrently, how do you let them proceed without readers and writers blocking each other? A naive approach uses locks - a writer locks a row, and readers wait. That's correct but slow, because reads (the common case) get blocked by writes.

Postgres's answer is MVCC: instead of overwriting data in place, an update creates a new version of the row, leaving the old version in place. Each transaction sees a consistent snapshot of the data as of when it started - readers see the version that was current for their snapshot, even while a writer is creating a newer version. The headline result: readers never block writers, and writers never block readers. A long-running read query sees a stable snapshot and doesn't hold up writes; a write doesn't hold up reads. This is a huge part of why Postgres handles concurrent workloads well.

The cost of this elegance: those old row versions pile up. Every update leaves a dead version behind, and those dead tuples accumulate, consuming space and eventually slowing things down. Which leads directly to the one piece of Postgres housekeeping every user should know about.

VACUUM: cleaning up after MVCC

Because MVCC leaves dead row versions around, Postgres needs a way to reclaim them - that's VACUUM. The vacuum process finds dead tuples (old versions no longer visible to any transaction) and marks their space as reusable, keeping tables from bloating endlessly. Postgres runs an autovacuum process automatically in the background to do this, which is why most users never think about it - until a table that's updated heavily starts bloating because vacuum couldn't keep up, and queries slow down.

Understanding that MVCC creates dead tuples and that VACUUM cleans them up explains a whole category of Postgres performance issues. Heavy update or delete workloads generate lots of dead tuples; if vacuum falls behind, tables bloat and slow down. It's the maintenance cost of MVCC's concurrency benefit.

Storage: how tables actually live on disk

To complete the picture, it helps to know how Postgres physically stores data. A table is stored as a collection of fixed-size blocks called pages (typically 8 kilobytes each). Each page holds a number of rows - in Postgres terminology, tuples. When Postgres needs a row, it reads the page containing it into the shared buffers, and the row is found within that page.

This page-based storage is why the shared buffer pool works the way it does: data moves between disk and memory a page at a time, and the buffer pool caches recently-used pages. It's also why MVCC works as it does - when a row is updated, the new version is a new tuple (often on the same or a nearby page), and the old tuple stays until vacuum reclaims it. The "heap" is the term for this collection of pages holding the table's tuples, stored without inherent order - which is why indexes matter so much: they impose fast access paths onto the unordered heap.

Understanding pages and tuples explains a lot: why Postgres reads more than just your one row (it reads the whole page), why the buffer pool caches at page granularity, and why MVCC's dead tuples take up space within pages until vacuumed.

Indexes and the planner's statistics

Indexes in Postgres are separate structures (usually B-trees) that provide fast access paths into the unordered heap - the planner uses them to avoid scanning every page. But here's a subtlety worth knowing: the planner decides whether to use an index based on statistics it keeps about your data - roughly how many rows there are, how values are distributed, how selective a condition is likely to be.

These statistics are gathered by background processing (and by vacuum's analyze step), and they're why the planner can make good decisions - but also why stale statistics cause bad ones. If your data changes dramatically and the statistics haven't caught up, the planner may estimate wrongly and choose a poor plan (using an index when a scan would be better, or vice versa). This is a real source of "why did my query suddenly get slow?" mysteries, and it's why keeping statistics current matters. The planner is only as good as its picture of your data.

The background processes, in more detail

The background processes do the work that keeps Postgres healthy while backends serve queries:

These processes run continuously and independently, which is how Postgres keeps the data files current, the log durable, and the dead tuples in check - all without blocking the backends serving your queries. Much of Postgres's reliability comes from this division of labor between connection-serving backends and housekeeping background processes.

A concrete trace: running a SELECT

Tie it all together with what happens when you run a simple SELECT filtering on an indexed column. Your client sends the SQL to its backend process. The backend parses it into a tree. The planner looks at the query, checks its statistics, sees there's a usable index on the filtered column, estimates that using the index is cheaper than scanning the whole table, and produces a plan that uses the index. The executor runs that plan: it walks the index to find the matching tuples' locations, reads the relevant pages into the shared buffers (from memory if already cached, from disk if not), pulls the matching tuples, applies MVCC visibility rules (showing you the row versions valid for your transaction's snapshot), and streams the results back to your client.

Every concept above - backend process, parse-plan-execute, statistics, indexes, pages and tuples, shared buffers, MVCC - appears in that one simple query. A SELECT you've run a thousand times is, internally, all of this machinery cooperating: a dedicated process serving your connection, a planner reasoning from statistics about the cheapest path, an executor walking index and heap and applying snapshot visibility, all reading through a shared cache backed by disk. That's Postgres, running.

Durability: the write-ahead log

Finally, durability - how Postgres makes sure your committed data survives a crash. The mechanism is the Write-Ahead Log (WAL): before changing the actual data files, Postgres first writes a record of the change to the WAL, a sequential log on disk. Only after the WAL record is safely written is the transaction considered committed. If Postgres crashes, on restart it replays the WAL to recover any committed changes that hadn't yet made it into the data files.

This is the standard durability technique across serious databases, and it's worth knowing that the WAL is also what powers Postgres replication and change data capture - the same ordered log of changes that guarantees durability is the stream that replicas and CDC pipelines consume. (See the dedicated guides on durability after a crash, and on change data capture.)

Tip: Emeka's note from the data trenches: The two Postgres internals that bite people in production, in my experience, are the connection model and vacuum. The connection model: because each connection is a real process, an application that opens thousands of direct connections will crush a Postgres server - always put a connection pooler in front of Postgres at scale; it's not optional. And vacuum: on a heavily-updated table, MVCC's dead tuples accumulate, and if autovacuum can't keep up, the table bloats and queries degrade in ways that look mysterious until you understand MVCC. Knowing these two - pool your connections, and respect vacuum on write-heavy tables - prevents the most common Postgres-at-scale pain I've seen.

Interview: You won't often be asked to recite Postgres internals in an interview, but understanding them pays off when reasoning about database behavior. Knowing that Postgres uses MVCC (so readers don't block writers), that it's process-per-connection (so pooling matters), and that it uses a WAL for durability lets you reason about concurrency, connection limits, and crash safety with real understanding rather than guesswork. And the concepts here - MVCC, WAL, query planning - are general database ideas that apply well beyond Postgres.

The bottom line

Internally, PostgreSQL is a family of processes - a supervisor that spawns a dedicated backend process per connection, plus background processes for housekeeping - sharing a memory area whose most important piece is the shared buffer pool that caches data pages. A query flows through parse, plan (where the optimizer chooses indexes and join methods), and execute. Its most distinctive design is MVCC: updates create new row versions rather than overwriting, so readers never block writers and vice versa - at the cost of dead tuples that VACUUM must reclaim. And durability comes from the write-ahead log, which also powers replication and CDC.

Hold this model - processes, shared buffers, parse-plan-execute, MVCC, vacuum, WAL - and Postgres stops being a black box. Its performance characteristics and occasional quirks become things you can reason about. We cover database internals and the concepts behind them across System Design Fundamentals.

Go deeper

More articles