PostgreSQL Internals: Pages,Tuples, and MVCC

When writing SQL, developers operate primarily with clean relational abstractions: tables, rows, and columns. We issue queries, filter records with indexes, and update values assuming the database modifies data in place.

Under the hood, however, PostgreSQL manages data through a very specific physical architecture. It organizes disk files into uniform 8 KB pages, stores rows as versioned tuples within an unordered heap, references records through physical coordinates called ctid pointers, and maintains transaction isolation using Multi-Version Concurrency Control (MVCC).

Understanding these mechanical realities is essential for diagnosing table bloat, tuning index performance, understanding query execution plans, and managing long-running transaction locks.

In PostgreSQL, every relation (table) maps to physical files on the host file system. These files reside within the database cluster directory (specifically under base/<database_oid>/<relfilenode>).

PostgreSQL structures each table file as a contiguous array of fixed-size blocks called Pages (or disk blocks).

When reading a table, the database engine does not pull fractional bytes from disk. It issues a file seek to that byte offset and reads the entire 8 KB page into memory, caching it in Shared Buffers.

In database literature, the term Heap refers to the unordered collection of table pages on disk and in memory where actual row data lives.

Unlike database engines that cluster tables directly inside a primary key B+ Tree index (such as MySQL InnoDB), PostgreSQL keeps table data in an unordered heap. Rows are stored in whichever page currently has sufficient free space, completely independent of primary key ordering.

A PostgreSQL page must store variable-length rows, track available space, and prevent internal fragmentation. To accomplish this, every 8 KB page is divided into four distinct regions.

The page header contains internal metadata, including the Write-Ahead Log sequence number (LSN), page flags, and two dynamic boundary pointers:

Immediately following the page header is an array of 4-byte line pointers (item pointers). Each line pointer records:

Line pointers grow downward as new rows are added.

The contiguous memory span between pd_lower and pd_upper. When pd_lower meets pd_upper, the page is considered full.

The raw binary payload containing row headers and column data. Tuples are inserted starting from the very end of the 8 KB block and grow upward.

This bidirectional growth architecture ensures that variable-length rows can be stored efficiently without needing to reorganize the entire page when rows vary in length.

In PostgreSQL terminology, a logical row may exist as multiple physical versions over time. Each physical version of a row stored in the heap is called a Tuple.

Every tuple is addressed by an internal identifier known as the Current Tuple ID (ctid):

For instance, a tuple with ctid (0, 1) represents:

The ctid provides direct physical indexing. When PostgreSQL knows a tuple's ctid, it does not search or scan; it reads the target 8 KB page, follows the line pointer offset, and accesses the tuple immediately in constant O(1) time.

When you create an index (such as a standard B-Tree index on a column like item_id), PostgreSQL constructs a separate tree structure stored in its own index file.

The leaf pages of a PostgreSQL B-Tree index do not hold full row records. Instead, they store simple pairs of (Indexed Value, ctid).

A defining characteristic of PostgreSQL is that it does not overwrite existing data in place during an update.

When an UPDATE command executes:

Consider updating the price of item 100 from $10 to $20:

PostgreSQL places the new tuple version at Line Pointer 3 on Page 0:

Meanwhile, the B-Tree index leaf page is updated to include both entries:

When a subsequent query runs SELECT price FROM items WHERE item_id = 100;, the index returns two matching entries: (0, 1) and (0, 3).

To decide which version of the row a query should receive, PostgreSQL relies on its transaction visibility mechanism.

To support Multi-Version Concurrency Control (MVCC) without acquiring shared read locks on table data, PostgreSQL includes system headers in every tuple:

Every transaction operates with an active database Snapshot. A snapshot defines the boundary of committed, active, and future transactions relative to the running query.

Let us trace how two concurrent transactions observe the table after Transaction 7 commits:

• Tuple (0, 1): xmin = 1, xmax = 7. Because Transaction 7 committed before Transaction 10 began, this version is dead to Transaction 10.

• Tuple (0, 3): xmin = 7, xmax = 0. Transaction 7 is committed and no transaction has deleted this record.

• Tuple (0, 1): xmin = 1, xmax = 7. Because Transaction 7 is in the future relative to Transaction 5's snapshot horizon, Transaction 5 cannot see modifications made by Transaction 7. The xmax = 7 is ignored.

• Tuple (0, 3): xmin = 7. This tuple was created after Transaction 5 started, making it completely invisible to Transaction 5.

Through this mechanism, both transactions read consistent, isolated data concurrently without blocking one another.

While append-only MVCC enables high read/write concurrency, it introduces storage management overhead.

A tuple is only considered dead when its xmax is older than the oldest active transaction snapshot in the database cluster.

As long as an open transaction (like Transaction 5) can still legitimately view an older tuple version, that tuple is alive and cannot be removed.

When long-running queries, idle transactions with uncommitted blocks (IDLE in transaction), or abandoned connection pool handles remain open:

The PostgreSQL VACUUM worker scans heap pages and index structures to:

VACUUM does not typically shrink table file sizes on the operating system file system; instead, it creates reusable space inside existing 8 KB pages so upcoming INSERT and UPDATE commands do not allocate additional disk blocks.

PostgreSQL’s physical storage engine is built around deliberate architectural trade-offs:

Keeping these foundational internals in mind makes it much easier to write efficient queries, design performant indexes, and keep your production databases healthy.

For more distributed database architectures and engineering trade-offs, explore our System Design Fundamentals Guide, our analysis of the Axios npm Supply Chain Incident, and our Fullstack Web Development Course.

You can also explore our Backend Development Program and DevOps & Cloud Services Track to learn database administration and production deployment in hands-on labs at instudia Dimapur.