Introduction

Hello, everyone! In this first post, we’ll explore together how PostgreSQL works internally, aiming to build a mental model that lets you reason about performance and understand how data is stored and retrieved.

It’s common in the industry, for instance, to find ORMs mapping instructions from a high-level programming language into SQL. The query gets executed without any need to understand what happens behind it, and this abstraction does its job well, as long as everything stays within expected bounds. However, as your system starts to scale, problems begin to surface: queries that suddenly slow down with no change to the codebase, tables whose size grows disproportionately to their actual data volume, or indexes that were created but are simply never used.

In these cases, I’d argue that understanding how the RDBMS operates internally, paired with good monitoring practices, is what separates an effective fix from a merely palliative attempt, one that borders on trial and error.

A good mental model lets you navigate the complexity of a tool backed by decades of research, like PostgreSQL, and quickly iterate on hypotheses that lead to solving a problem, armed with the data collected by the tools in your observability stack.

Like any model, this one is a simplification, and therefore has its limits. Take the Rutherford atomic model, taught in the first chemistry classes, as an example: a small, dense nucleus, orbited by electrons the way planets orbit the sun. We know an atom is far from behaving this way; electrons don’t occupy defined orbits, they exist in probability clouds. Even so, this model is still taught, because it’s simple enough to support reasoning about chemical bonds and reactions, without requiring years of study in quantum mechanics from the student. The model we’ll build here for PostgreSQL follows the same logic: it doesn’t aim to exhaust decades of database systems research, but to be precise enough to support good engineering decisions in day-to-day work.

The path of a query, from connection to response

Postgres adopts a process-per-connection model: every new connection to the database spawns a new process (a backend), created as a fork of the main process, called the postmaster.

The postmaster is the first process started when a Postgres server comes up, and it’s responsible for listening for new connections and delegating each one to a dedicated backend process. This is precisely why opening and closing connections frequently is said to be an expensive operation, which is what gave rise to pooling tools like PgBouncer.

Establishing that connection is, in practice, the first of six phases a query goes through before returning a result, namely:

  1. Connection: the client establishes a connection with the postmaster, which delegates handling of the query to a dedicated backend.
  2. Parse: parser receives the raw SQL text and converts it into a syntax tree, the parse tree.
  3. Semantic Analysis: semantic analyzer validates that tree, checking whether the referenced tables and columns actually exist. It then transforms it into a more complete representation of the query.
  4. Rewrite: rewriter applies rules and expands views, when any are involved.
  5. Plan: planner builds an execution plan, choosing among the different possible strategies the one it estimates to be most efficient.
  6. Execution: executor walks through that plan and produces the result, fetching and returning the requested data.

Deeping dive into tables

It’s in the executor that a query finally stops being an abstract structure and starts touching actual data. Fetching and returning a row means, in practice, locating a specific page in memory or on disk, inside a file that represents the table. To understand what happens at that moment, and why seemingly simple operations, like an UPDATE, carry implications that go well beyond “swapping one value for another”, we need to drop a level and look at how a table is actually organized under the hood.

A table in Postgres isn’t a single block of data, but a hierarchy of layers, each one contained within the one above it: tablespace, file, page, and tuple.

At the top sits the tablespace, which defines which directory on the filesystem will physically store the data for a given set of tables. By default, every Postgres cluster is born with one tablespace, pg_default, but you can create others, for instance, to direct specific tables to a faster disk. Within a tablespace, each table maps to one or more files on disk. It’s worth noting that Postgres identifies these files internally by a number, called the relfilenode, rather than by the name you use in SQL; that’s why renaming a table is a virtually instant operation, since only the metadata changes, the file on disk stays exactly the same.

Those files, in turn, aren’t read byte by byte. They’re divided into fixed-size blocks called pages, 8 KB each by default. That value is set at compile time. The page is Postgres’s smallest unit of reading and writing. This means that even if a query only needs a single row, the database always loads the entire page that contains it, whether from disk or from the buffer cache. That’s why the size and physical layout of data within a page have a direct impact on performance: the more relevant rows that fit into a single page, the fewer pages need to be read from disk to satisfy a query, and the less buffer cache memory gets consumed in the process.

Finally, inside each page, live the tuples. The technical term for each physical row of a table.

Understanding MVCC

This internal structure of Postgres turns out to be especially useful for understanding MVCC. Each tuple, besides the row’s actual data, carries a small header with control fields, among them xmin and xmax. The first holds the identifier of the transaction that created that version of the row; the second, the identifier of the transaction that made it obsolete, whenever that has already happened.

These two fields exist because Postgres never overwrites a tuple when it’s updated. When you run an UPDATE, Postgres doesn’t modify the existing row: it inserts a new tuple, with the updated values and a new xmin, and marks the old tuple with the xmax of the current transaction, signaling that it’s no longer the valid version of that row. A DELETE follows the same logic: the tuple isn’t removed right away, it just receives an xmax, becoming considered dead from that point on.

This behavior of never overwriting, but instead always creating new versions, is what we call MVCC, or multiversion concurrency control. It’s what allows Postgres to keep multiple transactions reading and writing at the same time without relying on constant locks between them: each transaction sees the version of the row that was valid at the moment it began, even if another transaction is, at that exact instant, creating a newer version of the same row.

Execution plans, and wrapping up this reflection

So far, we’ve built the pieces of the puzzle separately: how a query moves through Postgres’s components to form a response, how a table is organized into pages and tuples, and what MVCC does behind an UPDATE.

To close things out for today, it’s worth mentioning that there’s a tool that directly exposes everything we’ve built up to this point: EXPLAIN. Running EXPLAIN ANALYZE on a query lets you watch Postgres reveal the planner’s decisions, something like this:

1
2
3
4
5
6
7

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..18334.00 rows=12 width=72) (actual time=0.02..142.31 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999992
Planning Time: 0.15 ms
Execution Time: 142.38 ms

You can already spot most of what we’ve discussed in this single line: Seq Scan tells us Postgres swept through the table page by page looking for tuples with customer_id = 42, discarding nearly a million tuples along the way, and cost shows the planner’s estimate being compared against actual time, that is, what actually happened during execution. This is the point where theory and practice meet: pages, tuples, and the cost of scanning them stop being concepts and actually show up in the command’s output.

As mentioned earlier, this mental model we’ve built together is useful and should serve as a foundation for forming hypotheses when you run into problems in production. That said, keep in mind it needs to be backed by real metrics. That’s the subject we’ll dig into in a future post.

Thank you, folks =)

References and further reading

POSTGRESQL GLOBAL DEVELOPMENT GROUP. PostgreSQL Documentation. [S. l.], 2026. Available at: https://www.postgresql.org/docs/. Accessed on: Sep. 5, 2026.

ESPINO, Jesús. Deep Dive Into a SQL Query: a journey through PostgreSQL’s query processing. [S. l.]: Leanpub, 2026. E-book.