Inside database data files (2/3): PostgreSQL

After InnoDB, PostgreSQL. Both engines solve the same problems (store rows, find them fast, survive a crash, let people read while others write) but made almost opposite choices. Knowing them explains the need for VACUUM, table bloat, or why the first SELECT after a big load writes to disk.

The data directory

First habit to lose: looking for tables by name. PostgreSQL refers to everything by number. Each database has an OID, which is its directory name under base/. Each table, index or sequence has a relfilenode, which is its file name. That number changes when the table is rewritten (TRUNCATE, VACUUM FULL, CLUSTER, some ALTER TABLE): PostgreSQL creates a new file and switches over, which makes those operations crash-safe.

SELECT oid, datname FROM pg_database;
SELECT pg_relation_filepath('orders');        -- base/16384/24576
SELECT pg_size_pretty(pg_total_relation_size('orders'));   -- table + indexes + TOAST

• global/ holds the catalogs shared by every database (roles, database list) and pg_control, the small file that says where to resume after a crash;
• pg_wal/ is the write-ahead log (WAL), in 16 MB segments: the equivalent of InnoDB’s redo;
• pg_xact/ keeps each transaction’s status in two bits: in progress, committed, aborted. That’s what tells whether a row version is visible;
• pg_tblspc/ holds symlinks to tablespaces created elsewhere. In PostgreSQL, a tablespace is just a directory on another disk.

A relation: forks and segments

Each table is really a small set of files. The main fork holds the data, split into files of at most 1 GB (24576, 24576.1…). That limit dates from when not every filesystem handled large files, and it still makes backup and copying simpler. The free space map records each page’s approximate free space, so an INSERT quickly finds a page to write to. The visibility map keeps two bits per page: “every row is visible to everyone” and “every row is frozen”. It lets VACUUM skip pages that are already clean, and lets index-only scans avoid reading the table.

Anatomy of a page

Pages are 8 KB (a compile-time constant, BLCKSZ). Their structure is the same for tables and indexes:

• the 24-byte header holds pd_lsn, the WAL position of the last change. PostgreSQL uses it for a simple rule: a page can’t be written to disk before the WAL describing it is. It also holds the checksum (on by default at initdb since PostgreSQL 18, requested with initdb -k before), and two offsets, pd_lower and pd_upper, bounding the free space;
• line pointers form an array at the start of the page. Each holds a tuple’s offset and length. A row’s address, its ctid, is (page number, pointer number), e.g. (0,2). That indirection lets a tuple move within the page for defragmentation without changing its address, so without touching the indexes;
• tuples are written from the end of the page, upwards. When pd_lower (end of the pointers) meets pd_upper (start of the tuples), the page is full;
• the special space at the end of the page is empty for a table. B-tree indexes keep links to neighbouring pages and the tree level there.

You can look at all of it with the pageinspect extension, on a test database:

CREATE EXTENSION pageinspect;
SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('orders', 0));
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('orders', 0));

A tuple

Every row version starts with a 23-byte header, rounded up to 24 by alignment. The key fields are t_xmin (the transaction that created this version), t_xmax (the one that deleted or replaced it) and t_ctid (the address of the next version). Three practical details:

• alignment: each column is aligned to its type (8 bytes for bigint and timestamp). A boolean followed by a bigint wastes 7 bytes. Ordering columns from largest to smallest can shrink a big table by 10–20%;
• NULLs take only a bit in the bitmap, which is only present if the row has at least one NULL;
• TOAST: when a row exceeds about 2 KB, PostgreSQL first compresses large values (pglz, or lz4 if you ask), then, if that’s not enough, moves them to a side table, pg_toast.pg_toast_<oid>, in chunks. The row keeps only an 18-byte pointer. It’s transparent, but a SELECT * reading a TOASTed column fetches those chunks: select only the columns you need.

An UPDATE changes nothing in place

This is the fundamental difference from InnoDB and Oracle. In PostgreSQL, an UPDATE is a DELETE followed by an INSERT: the transaction writes a complete new version of the row, and records its number in the old one’s xmax. There’s no undo log. The old versions are the undo, and they live in the table.

To decide whether a version is visible, a transaction compares xmin and xmax with its snapshot: the version is visible if its creation is committed and older than the snapshot, and its deletion didn’t happen, was aborted or is newer. Checking pg_xact every time would be expensive. So the first transaction that reads a version records the result in hint bits in the header (“xmin committed”, “xmax aborted”…). That explains something that often surprises people: the first SELECT after a big load writes to disk, because it sets those bits on every page.

What it implies: VACUUM, HOT and freezing

• VACUUM removes versions no transaction can see any more, makes their space reusable (updating the free space map) and updates the visibility map. It doesn’t give space back to the filesystem, except for empty pages at the end of the file. Only VACUUM FULL (or the pg_repack extension, without a long lock) rewrites the table;
• a transaction left open too long blocks cleanup of every version newer than it, across the whole database. It’s the first thing to check when a table bloats: SELECT pid, xact_start, state FROM pg_stat_activity ORDER BY xact_start;;
• HOT updates (Heap-Only Tuple): if no indexed column changes and the new version fits in the same page, PostgreSQL leaves the indexes alone. The index still points at the old line pointer, which leads to the new version through the ctid chain. Leaving room in pages improves the odds of HOT: ALTER TABLE orders SET (fillfactor = 90);;
• freezing: transaction IDs are 32 bits and eventually wrap around, after about two billion transactions. So that a very old row doesn’t suddenly look “in the future”, VACUUM marks it frozen, visible to all. If autovacuum falls too far behind, PostgreSQL eventually refuses writes to protect itself: that’s why you never disable autovacuum.

Indexes and the WAL

A B-tree index uses the same 8 KB pages. Its entries hold the key and the tuple’s ctid, i.e. a physical address, unlike InnoDB which stores the primary key. An index lookup is therefore one descent plus one page read. But the index knows nothing about visibility: the table must be read to know whether the version found is visible, unless the visibility map says the page is all-visible. That’s what makes index-only scans efficient on a well-vacuumed table.

Finally, protection against torn pages: the problem is the same as InnoDB’s (8 KB written by a disk that guarantees 4 KB), the solution differs. With full_page_writes = on, the first change to a page after each checkpoint writes the full page image to the WAL. After a crash, PostgreSQL starts again from that intact image. It’s the equivalent of the doublewrite buffer, and the reason WAL volume jumps right after each checkpoint.

Last post in the series: Oracle, which shares InnoDB’s separate undo but takes the logic further, with locks stored in the blocks themselves.

---

This post is part of a series of sysadmin notes (tag “sysadmin notebook”).
Series “Inside database data files”, part 2/3. · ← Previous post · Next post → · Version française