PostgreSQL Storage Internals: Pages, Tuples and TOAST

PostgreSQL storage internals come down to a few nested units. Every table is stored as a sequence of 8KB pages, each page holds a number of tuples (row versions), the pages are stored in segment files of up to 1GB, and values too large to fit comfortably in a page are moved to a side table by TOAST. All of those files live in one data directory, the database cluster, created by initdb.

This layout directly affects performance and capacity planning. PostgreSQL reads and writes whole pages, never single rows, so the number of rows per page decides how much I/O a scan costs. This guide covers each layer, works through a sizing calculation for a 5-million-row table, and then maps out the data directory. Examples use PostgreSQL 16.

Pages: the 8KB unit of PostgreSQL storage

A page (also called a block) is the smallest unit PostgreSQL moves between disk and memory. The size is 8KB (8,192 bytes), fixed when PostgreSQL is compiled. Each page belongs to exactly one relation: a page of the orders table never contains rows from customers, and index pages are separate from table pages.

A heap (table) page is laid out like this:

+-------------+----------------------------+-------------------+---------------------------+
| Page header | Line pointers (4 B each) ->|    free space     |<- tuples (from the end)   |
|  24 bytes   |  grow forward              |                   |   grow backward           |
+-------------+----------------------------+-------------------+---------------------------+

• The page header (24 bytes) stores the LSN of the last WAL record that changed the page, a checksum, and offsets marking where free space starts and ends.
• Line pointers (item identifiers) are 4-byte slots that point to each tuple's position within the page.
• Tuples are written from the end of the page toward the front.
• Free space is whatever remains in the middle. When it runs out, new rows go to another page.

Index pages add a "special space" at the end for index-specific data. Heap pages don't use it.

Because tuples are addressed through line pointers, a row's physical address is a pair of (page number, line pointer number), exposed as the hidden ctid column. Indexes store these addresses, which is how an index lookup finds the heap row. See the practical indexing strategy guide for what that means for index design.

Tuples and their hidden columns

A tuple is one version of a row. Besides your data, every tuple carries a header of 23 bytes, padded to 24 on 64-bit systems. The fields that matter most are:

You can look at the hidden columns directly:

SELECT ctid, xmin, xmax, id, status
FROM orders
LIMIT 3;

xmin and xmax are how PostgreSQL decides which version of a row each transaction can see. That mechanism, MVCC, is covered in ACID and MVCC explained. For storage, the takeaway is that an UPDATE writes a new tuple and leaves the old one in place until vacuum removes it, so a heavily updated table takes more pages than its live row count suggests.

To look inside a page, use the pageinspect extension (superuser only):

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('orders', 0));

Each row of the output is one line pointer: its offset in the page, the tuple length, and the tuple's header fields.

Segment files: tables on disk

Each table is stored in files named after its relfilenode, a number that usually starts equal to the table's OID. A file grows until it reaches 1GB, and then PostgreSQL starts the next segment:

16402      first 1GB of the table
16402.1    next 1GB
16402.2    ...
16402_fsm  free space map: which pages have room for new tuples
16402_vm   visibility map: which pages contain only rows visible to everyone

The 1GB limit keeps files manageable on every filesystem PostgreSQL supports. Indexes follow the same scheme with their own relfilenode.

Don't guess the path. Ask PostgreSQL:

SELECT pg_relation_filepath('orders');
-- base/16384/16402

Here 16384 is the OID of the database and 16402 is the relfilenode. The relfilenode can change after TRUNCATE, VACUUM FULL or CLUSTER, which rewrite the table into a new file, so always look it up instead of hard-coding it.

TOAST: storing large values outside the page

A tuple has to fit inside one 8KB page. Long text, big jsonb documents or bytea blobs won't, so PostgreSQL uses TOAST (The Oversized-Attribute Storage Technique).

When a row is larger than about 2KB (the TOAST_TUPLE_THRESHOLD, roughly a quarter of a page), PostgreSQL works through its variable-length columns:

1. Compress the value in place (pglz by default, or lz4).
2. If the row is still too big, move the value out of line into the table's TOAST table.
3. Split the moved value into chunks of about 2KB, stored as rows in the TOAST table.

The main row keeps only an 18-byte pointer to the chunks:

Main table row:  [ id | title | body -> toast pointer ]
TOAST table:     [ chunk_id, chunk_seq=0, data ][ chunk_id, chunk_seq=1, data ] ...

Only variable-length types (text, varchar, jsonb, bytea, arrays and so on) can be TOASTed. A single value can be up to 1GB.

Each column has a storage strategy you can change:

EXTERNAL is useful for large text or bytea that you often read with substring(), because PostgreSQL can fetch only the chunks it needs. Switching compression to lz4 is often faster than pglz:

-- find the TOAST table behind a table
SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'articles';

ALTER TABLE articles ALTER COLUMN body SET STORAGE EXTERNAL;
ALTER TABLE articles ALTER COLUMN payload SET COMPRESSION lz4;  -- applies to new values

Two practical consequences:

• SELECT * on a table with large TOASTed columns fetches and decompresses them even when the application ignores them. List only the columns you need.
• Updating other columns does not copy an unchanged TOASTed value. The new tuple reuses the existing pointer.

Worked example: sizing a 5-million-row table

Say you're about to load 5 million rows into a table with five columns a to e of different types, including a text column. Load a sample and measure the average stored row size:

SELECT avg(pg_column_size(t.*)) AS avg_row_bytes
FROM events t;

pg_column_size on the whole row returns its size including the 24-byte tuple header. Say the answer is 849 bytes. That's below the ~2KB TOAST threshold, so every row stays in the main table.

Step 1: rows per page. The quick estimate is 8,192 / 849 = 9.6, so 9 rows. A more careful calculation gives the same answer here:

• Usable space: 8,192 − 24 (page header) = 8,168 bytes.
• Each tuple is padded to a multiple of 8: 849 becomes 856, plus a 4-byte line pointer, for 860 bytes per row.
• 8,168 / 860 = 9.5, so 9 rows per page, with about 428 bytes left unused.

Step 2: pages for the table. 5,000,000 / 9 = 555,556 pages.

Step 3: bytes. 555,556 × 8,192 = 4,551,114,752 bytes, about 4.24GB.

Step 4: segment files. One segment is 1GB = 1,024 × 1,024 × 1,024 = 1,073,741,824 bytes. Divided by 8,192, that is 131,072 pages per segment. Then 555,556 / 131,072 = 4.24, so the table needs 5 files: 16402, 16402.1, 16402.2 and 16402.3 full, and 16402.4 holding the remaining 31,268 pages (about 244MB).

Check your estimate after loading:

SELECT pg_size_pretty(pg_relation_size('events'))       AS heap,
       pg_size_pretty(pg_table_size('events'))          AS heap_toast_fsm_vm,
       pg_size_pretty(pg_total_relation_size('events')) AS with_indexes;

The overhead matters more for narrow rows. A row of bigint, integer and timestamptz holds 20 bytes of data, so a naive estimate suggests 409 rows per page. With alignment padding (24 bytes of data), the 24-byte header and the line pointer, each row really takes 52 bytes, so only 157 rows fit. Column order affects padding too: placing 8-byte columns before 4-byte ones avoids gaps.

The database cluster and initdb

In PostgreSQL, a database cluster is not a group of servers. It is one data directory, served by one postmaster on one port, containing any number of databases that share the same roles, configuration and WAL. How that server runs its processes and memory is covered in PostgreSQL architecture.

initdb creates a new cluster:

initdb -D /var/lib/postgresql/16/data --encoding=UTF8 --data-checksums

-D sets the data directory. --data-checksums enables page checksums, which are off by default in PostgreSQL 16 and can't simply be switched on later without the pg_checksums tool and downtime. On Debian and Ubuntu, pg_createcluster 16 main wraps initdb and places the config files in /etc/postgresql/16/main.

Starting, stopping and reloading:

pg_ctl -D /var/lib/postgresql/16/data start
pg_ctl -D /var/lib/postgresql/16/data stop -m fast   # fast is the default mode
pg_ctl -D /var/lib/postgresql/16/data reload         # re-read config files
sudo systemctl restart postgresql                     # packaged installs on Linux

On Windows, the installer registers a service you can control with net start and net stop (for example postgresql-x64-16). Some settings, such as shared_buffers and max_connections, need a restart. SELECT name FROM pg_settings WHERE pending_restart; lists changes that are waiting for one.

Data directory layout

template0, template1 and the postgres database

initdb creates three databases:

• template1 is what CREATE DATABASE copies by default. Anything you add to it, such as an extension, appears in every new database.
• template0 is a pristine copy that doesn't allow connections (datallowconn = false), so it can't be modified by accident. Use it when restoring a dump or when you need a different encoding or locale.
• postgres is an empty default database that tools and utilities connect to. You can drop it, but many tools expect it to exist.

Since PostgreSQL 15 their OIDs are fixed: template1 is 1, template0 is 4 and postgres is 5. So base/5/ is always the postgres database.

CREATE DATABASE reports TEMPLATE template0 ENCODING 'UTF8';

If template1 gets polluted, rebuild it from template0 while connected to another database:

ALTER DATABASE template1 IS_TEMPLATE false;
DROP DATABASE template1;
CREATE DATABASE template1 TEMPLATE template0 IS_TEMPLATE true;

FAQ

What is the page size in PostgreSQL?

8KB by default. It can only be changed by compiling PostgreSQL with a different block size, which is rarely worth it.

What is the maximum table size in PostgreSQL?

32TB with the default 8KB page size. The table is split into 1GB segment files, so no single file gets that large.

When does PostgreSQL use TOAST?

When a row exceeds roughly 2KB. PostgreSQL first compresses large variable-length values, then moves them out of line into the TOAST table in chunks of about 2KB.

Why is my table much bigger than the data I inserted?

Tuple headers, alignment padding, line pointers, dead row versions left by updates and deletes, and the free space map and visibility map all add to the size. Compare pg_relation_size with pg_total_relation_size to see how much is indexes.

Can I delete old files in pg_wal to free space?

No. Removing WAL files by hand can make the cluster unrecoverable. Find out why WAL is retained instead, for example a failing archive command or an inactive replication slot.

Storage internals checklist

• Estimate rows per page from the real average row size, including the 24-byte header, padding and line pointer.
• Order columns from widest fixed-length to narrowest to reduce padding.
• Keep large, rarely read values in columns you don't select by default, and consider lz4 compression.
• Look up file paths with pg_relation_filepath instead of guessing.
• Create new clusters with --data-checksums, and never touch pg_wal by hand.

If you're planning capacity for a growing PostgreSQL database, Vectorkub can help with the design and the numbers.