How PostgreSQL stores data: pages, heap, tuples, and TOAST Basic

Problem

A documents table can look very simple in SQL: each row has id, title, and body. But a row is not a block of data written intact to a single place on disk.

PostgreSQL splits a relation’s file into pages; each page has a control area, free space, and tuples. If body is too large, PostgreSQL may also compress it or move that value into an associated TOAST table.

Knowing these layers answers common operational questions: why a table with a few columns takes a lot of disk, what ctid points to, why pg_column_size(body) differs from the total table size, and why a row with large JSON or text can still live in an ordinary table.

This article describes the default table access method heap in PostgreSQL 17. heap here is how PostgreSQL organizes and finds row versions in a table; it does not mean rows are always stored in id order, nor is it the heap tree structure from algorithms courses.

Core concepts

Picture a library storing trip records. SQL gives us a way to name the data: table documents, row id = 42, column body. When PostgreSQL stores data physically, it needs smaller units:

• Page is a fixed-size block, usually 8 kB, used as the read/write unit for tables and indexes. The actual size is decided when PostgreSQL is compiled.
• Heap is the default table access method. A heap relation’s file is a sequence of pages; a table’s pages are not guaranteed to be ordered by column value or insert order.
• Tuple is the physical version of a row in the heap. A tuple holds the header PostgreSQL needs to manage versions plus the column values; in PostgreSQL 17 the tuple header usually takes 23 bytes before the NULL bitmap and data, excluding padding.
• TOAST is the mechanism for handling large values of certain variable-length types. PostgreSQL can compress the value or store it as chunks in a side relation, while the main tuple keeps a small pointer.

Each layer solves a different problem: the page bounds the I/O unit, the heap manages tuples within a relation, the tuple carries the values and metadata of a row version, and TOAST keeps a large value from taking up a whole tuple in the main page.

How it works

Loading diagram…

From table name to page

When the application queries SELECT body FROM documents WHERE id = 42, PostgreSQL does not open a file named documents and search for the string 42. The catalog describes the relation and its columns; each relation has its own storage.

A regular heap table has these forks:

Fork	Role
Main fork	The main file, containing pages with the same basic format
Free space map (FSM)	Helps find pages with free space
Visibility map (VM)	Tracks pages confirmed to contain only visible tuples

A page fills from both ends toward the middle: line pointers grow from the front, tuples grow from the back. The page is full when the two sides meet.

|<------------------------------ 8192 byte ------------------------------>|
+--------+-----+-----+-----+---------------------+-------+-------+-------+
| header | lp1 | lp2 | lp3 |     free space      | tuple3| tuple2| tuple1|
| 24 B   | 4 B | 4 B | 4 B |                     |       |       |       |
+--------+-----+-----+-----+---------------------+-------+-------+-------+
          ---> grows                                    <--- grows

A heap page has these main parts:

Part	Role
Page header	Page information, including the boundary between the control area and the data area
Line pointers (ItemIdData)	An array of slots pointing to each tuple’s position and size; each slot usually takes 4 bytes
Free space	Space not yet allocated to a new line pointer or tuple
Tuple items	Tuple contents; allocated backward from the end of the page toward the line pointers
Special space	An area reserved for the access method; empty for a regular heap table

PostgreSQL 17 reserves the first 24 bytes of a page for the page header. Line pointers separate an item’s identifying position from the tuple’s byte position.

When PostgreSQL needs to compact free space in a page, tuples can move within the page while their line pointers keep the same slot. That is why ctid can keep pointing to the same slot after some page cleanup operations.

Even so, applications should not use ctid as a durable key: an update can create a new tuple version, and maintenance operations can change the physical location.

Inside a tuple

A tuple is more than its SQL values. In order, a tuple consists of:

1. Header: the transaction ID that created the version (xmin), the transaction ID that deleted or replaced it (xmax), ctid, status flags, and the column count.
2. NULL bitmap (if present).
3. Padding to align the data.
4. Column values.

Byte placement depends on data types and alignment rules, so you cannot simply add up string lengths to get a tuple’s exact size.

ctid has the form (block number, item number): the block number identifies the page in the relation, and the item number identifies the line pointer on that page.

It answers “where is this row version currently in the relation?”, not “what is the durable identity of this row?”. For a business identifier, use the primary key.

Variable-length data uses the varlena format. Types such as text, bytea, and jsonb can hold values of varying size, but not every type supports TOAST.

When a row contains TOAST-able fields, PostgreSQL keeps small values directly in the tuple when they fit. A table having an associated TOAST relation does not mean every value has been moved out.

What do UPDATE and DELETE leave behind?

UPDATE does not modify in place: it creates a new tuple and marks the old one with xmax. The old tuple still takes space in the page until VACUUM reclaims it. This is the origin of bloat; see MVCC and VACUUM, autovacuum, and bloat.

How are large values TOASTed?

A tuple cannot span multiple pages. When a row is wider than TOAST_TUPLE_THRESHOLD (usually 2 kB), PostgreSQL compresses and/or moves large values out until the row is shorter than TOAST_TUPLE_TARGET (also usually 2 kB, adjustable per table with toast_tuple_target). The threshold applies to the whole row, not to each column.

Four storage strategies, set with ALTER TABLE ... ALTER COLUMN ... SET STORAGE:

Strategy	Compression	Out-of-line	Use when
PLAIN	No	No	Types that cannot be TOASTed (default for int, etc.)
EXTENDED	Yes	Yes	Default for text, jsonb, bytea: compress first, move out only if still large
EXTERNAL	No	Yes	You need fast substring on large text/bytea: read only the needed part
MAIN	Yes	Only as a last resort	You want to keep it in the main tuple whenever possible

A TOAST-able value can be at most 1 GB. The compression algorithm is chosen per column with COMPRESSION or via default_toast_compression.

When data is stored out-of-line on disk, PostgreSQL splits the (possibly compressed) value into chunks in a separate TOAST table:

• Each chunk has a value ID, a chunk sequence number, and the chunk data.
• An index on the identifying fields lets reads reassemble chunks in the right order.
• The tuple in the main table keeps a pointer with the information needed to fetch the value, instead of carrying the whole payload.

Reads stay transparent at the SQL level: SELECT body returns the full value. The physical difference is that the backend may need to read TOAST chunks and decompress them before handing the value to the executor or client. That cost depends on the query:

Operation	TOAST cost
Query needs only id, title	Does not read the body chunks
UPDATE another column of a row whose body is out-of-line	Free, since the unchanged value is kept as is
SELECT * on a table with many TOAST columns	Fetches and decompresses all of them

Tracing a row with SQL

The example below creates a temporary table, inserts a short string and a large, nearly non-repeating payload, then observes the datum size, TOAST pointer, and relation size. The temporary table exists only in the current psql session. Run it from the examples/ directory once PostgreSQL 17 in Compose is ready:

docker compose up -d postgres
docker compose exec -T postgres psql -U hub -d hub < src/postgres-storage/storage-layout.sql

Or copy the contents of examples/src/postgres-storage/storage-layout.sql from the repo into a PostgreSQL 17 session. The functions used:

Function	Tells you
pg_column_size	The stored size of the datum
pg_column_compression	Whether the value is compressed
pg_column_toast_chunk_id	Whether the value has a pointer to TOAST chunks on disk
pg_class.reltoastrelid	Links the table to its TOAST relation, to compare main fork size with total storage

Read the results as relationships, not memorized byte counts: the short row is inline with no TOAST chunk; the large row has a TOAST pointer. Exact sizes vary with page size, header, alignment, compression algorithm, and version.

pg_total_relation_size includes the table’s TOAST and indexes; pg_relation_size by default measures only the main fork of the relation passed in.

In a run with Compose’s postgres:17-alpine image, the datum observation returned:

label	characters	pg_column_size	compression	TOAST pointer?
short	40	51 bytes	NULL	no
large	16,000	16,000 bytes	NULL	yes

Why is the 40-character short string 51 bytes? The demo string is a Vietnamese sentence whose accented characters take 2-3 bytes in UTF-8, so 40 characters become 50 bytes, plus 1 byte of short-value header. characters counts characters; pg_column_size counts stored bytes.

In the same run:

Component	Size
Heap main fork	8192 bytes
TOAST table with index	64 kB
Total relation	88 kB

These are measurements from one specific run; the numbers depend on patch version, page size, and relation layout.

pg_column_size of an out-of-line value is the size stored in the TOAST table, not the size of the pointer in the main tuple.

The large value reports 16,000 bytes because the payload is nearly incompressible (compression is NULL), so the stored size equals the original size. For compressible data, the number would be smaller than the character count.

Real-world example

An article service has small id, title, status columns and a body averaging 30 kB. The article list selects only the three small columns and so reads no TOAST chunks, while the detail page reads body exactly once per view.

No need to split the table: PostgreSQL has already moved body into a TOAST table. What to do is forbid SELECT * in the list code. A separate table is only worth it when body has a different life cycle or retention policy (archiving, time-based deletion).

Trade-offs

Pros

• Pages have a fixed size, so PostgreSQL manages I/O and free space in consistent units.
• Line pointers let tuples move within a page without immediately changing the item’s identifying slot.
• TOAST lets rows hold large values while keeping the main tuple compact for the many queries that do not need the payload.

Cons

• Reading or updating a TOAST column can mean reading chunks, decompressing, and extra I/O.
• ctid is the physical address of one tuple version, not a stable application identifier.
• Relation size includes page headers, line pointers, free space, old tuple versions, indexes, and TOAST; a single number cannot explain why a table grew.

A quick way to remember it: the page is the container, the line pointer is the slot, the tuple is the row version, TOAST is the side storage for large values.

The trade-off is that queries fetching only small columns can be lighter, while queries needing the large payload still pay to fetch and process it.

Common mistakes

Common pitfall

Assuming a row always sits in one contiguous byte range and is durably located by ctid. A tuple does not span pages; TOAST can move large values into another relation, and an update can create a tuple version at a new location.

Use the primary key for application identity, and use ctid and the size functions for physical diagnosis at a specific point in time.

Checklist when designing a table with large columns

1. List queries select only the small columns they need; no SELECT * on tables with TOAST columns, since it fetches and decompresses everything.
2. Use the primary key as the application identifier; do not store ctid in another system.
3. When a table “bloats”, separate the size layers: pg_relation_size (main fork) versus pg_total_relation_size (including TOAST and indexes).
4. Use pg_column_size and pg_column_toast_chunk_id to tell whether a value is inline or out-of-line, instead of adding up string lengths.
5. Switch to SET STORAGE EXTERNAL only when you really need fast substring on large text/bytea; the default EXTENDED compresses first.
6. Move a large column to a separate table only when it has a different life cycle or retention policy; TOAST already separates the payload from the main tuple.

Three self-check questions:

1. Why can pg_total_relation_size('documents') be larger than pg_relation_size('documents')?
2. If a table has a TOAST table, does that prove every text value is stored outside the heap?
3. Why should you not store a row’s ctid in another system as a durable identifier?

Summary

Key takeaways

• A heap table is a sequence of pages; each page holds a page header, line pointers, free space, and tuples.
• A tuple is the physical version of a row, with an MVCC header and column values; byte layout depends on data types and alignment.
• ctid identifies the physical location of the current tuple version and does not replace the primary key.
• TOAST can compress large variable-length values or move them into a TOAST table; SQL still reads the value normally.
• Next: MVCC explains why there are multiple tuple versions, VACUUM and bloat explains how they are cleaned up
• Use pg_column_size, pg_column_toast_chunk_id, pg_relation_size, and pg_total_relation_size to examine the different size layers.

References

• PostgreSQL 17 — Database Page Layout
• PostgreSQL 17 — TOAST
• PostgreSQL 17 — Database File Layout
• PostgreSQL 17 — Database Object Size Functions
• PostgreSQL 17 — pg_class