Heap Storage

Every table you create with SQL Basics has to live somewhere on disk. Postgres uses a heap — an unordered collection of fixed-size pages, each holding a handful of rows — and understanding its shape explains a lot of behavior that otherwise looks like magic, from why SELECT * on a huge table is slow to why deleted rows don't shrink a table immediately.

Pages

A table's data file is divided into 8KB pages (also called blocks). Every read and write happens a whole page at a time — Postgres never touches less than 8KB of disk, even to read a single row. A page holds:

• a header (checksums, free space pointers)
• an array of item pointers (line pointers), each pointing at a tuple later in the same page
• the tuples themselves, packed in from the end of the page backwards

Tuples

A tuple is one physical row version. Every tuple carries a fixed header in front of your actual column data, including:

• xmin — the transaction ID that created this tuple version
• xmax — the transaction ID that deleted/replaced it (0 if still live)
• ctid — this tuple's own physical address, (page_number, item_index)

You can see ctid directly:

CREATE TABLE accounts (id serial PRIMARY KEY, balance numeric);
INSERT INTO accounts (balance) VALUES (100), (250), (75);

SELECT ctid, * FROM accounts;
--  ctid  | id | balance
-- (0,1)  |  1 |     100
-- (0,2)  |  2 |     250
-- (0,3)  |  3 |      75

(0,1) means "page 0, item pointer 1." ctid changes every time a row is updated — that's the first hint that UPDATE isn't an in-place edit, which is the whole subject of the next lesson, MVCC.

Relation files on disk

Each table (and each index) is backed by one or more physical files under the data directory, named after the table's relfilenode, not its name:

base/<database_oid>/<relfilenode>

-- find yours:
SELECT pg_relation_filepath('accounts');
-- base/16384/16391

Files are capped at 1GB each — a table larger than that spills into 16391.1, 16391.2, and so on, all still logically one relation. This is also why ALTER TABLE ... RENAME is instant: the file name never changes, only the catalog entry does.

To see how many pages (and bytes) a table actually occupies:

SELECT pg_size_pretty(pg_relation_size('accounts'));
-- 8192 bytes  (one page, for a table this small)

Rows shrink out of a page only when VACUUM reclaims the space old tuple versions leave behind — a delete or update doesn't free anything immediately. Next: what happens when a single column's value is too big to fit in a page at all, in TOAST, FSM & Visibility Map.