Storage and pages
Under every table is a file of fixed-size pages, 8192 bytes each in PostgreSQL. Follow page 0 of a small books table from disk into shared buffers, watch three rows and their line pointers fill it from both ends, read a row's address (its ctid), see an UPDATE add a new row version, and learn why the change reaches the write-ahead log before the page goes back to disk.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01A query needs one 40-byte row from the books table. How much does PostgreSQL read from the file?
The page is the unit of input and output. PostgreSQL copies the whole 8192-byte page into shared buffers and finds the row there; later reads of that page can skip the disk.
02An empty page has pd_lower = 24 and pd_upper = 8192. After one INSERT they are 28 and 8152. What happened?
Line pointers are allocated from the front (pd_lower moves up by 4), rows from the back (pd_upper moves down by the row's size, rounded up to 8). The free space is what is left between them: 8124 bytes.
03SELECT ctid, * FROM books shows Holes at (0,3). What does that mean?
A ctid is (block number, line pointer number). The pointer, not the ctid, holds the byte offset, so the row can be moved inside the page (for example to squeeze out gaps) without its ctid changing.
04UPDATE books SET stock = 1 WHERE id = 3 runs. What is on page 0 right after it?
PostgreSQL keeps old row versions so that transactions that started earlier can still read them (MVCC, multiversion concurrency control). Here the new version fits in the same page, and the old one points on to it. (Because this is a HOT update in one page, a later visit to the page can also prune the old version before VACUUM runs.)
05The changed page 0 is in shared buffers. What must happen before PostgreSQL may write it back to the table file?
This is the write-ahead rule: the log record reaches permanent storage before the data page does. After a crash, replaying the WAL rebuilds any change whose page never made it to the file.
Glossary
- Page (block)
- A fixed-size chunk of a table file, 8192 bytes by default in PostgreSQL. The database always reads and writes whole pages.
- Shared buffers
- PostgreSQL's pool of page slots in memory (128 MB by default). Pages are read into it, changed there, and written back later.
- Page header (pd_lower / pd_upper)
- The first 24 bytes of a page. Among other things it holds pd_lower, just past the last line pointer, and pd_upper, the start of the rows at the back. The free space lies between them.
- Line pointer
- A 4-byte entry after the header that gives one row's offset and length in the page. Line pointers are numbered from 1.
- ctid
- The physical address of a stored row version (a tuple): (page number, line pointer number), such as (0,3).
- Dirty page
- A page in memory that has been changed but not yet written back to the file on disk.
- WAL and checkpoint
- The write-ahead log (WAL) is a file of change records, written to disk before the changed pages. A checkpoint is a moment when every dirty page is written back to the table files, so older WAL is no longer needed for recovery.
- VACUUM
- PostgreSQL's clean-up command. It removes old row versions that no transaction can still see, so their space in the page can be reused.
Notes and sources
- The page in the figure is not to scale: the header and these 3 rows use only 164 of its 8,192 bytes.
- Row sizes assume a usual 64-bit build, where rows start on 8-byte boundaries, and short text values (under 127 bytes) with a 1-byte length header. Long values are moved out of the page (TOAST).
- Here the new version fits in the same page, so PostgreSQL can make it a heap-only (HOT) update; the old row version points on to the new one. A later visit to the page can then prune the old version without waiting for VACUUM. Without room, the new version goes to another page.
- PostgreSQL may write a dirty page before the commit too (to free a buffer), but never before the WAL record that describes the change has reached disk.
- SQLite also stores a database as pages (4096 bytes by default), but a table there is a B-tree keyed by rowid, not a heap with ctids.
- The 185 rows-per-page figure ignores the tiny slack at the end. PostgreSQL also caps a heap page at 291 line pointers (with 8 kB pages).
- PostgreSQL documentation: Database Page Layout
- PostgreSQL documentation: Database File Layout
- PostgreSQL documentation: System Columns (ctid)
- PostgreSQL documentation: Resource Consumption (shared_buffers)
- PostgreSQL documentation: Preset Options (block_size)
- PostgreSQL documentation: Write-Ahead Logging (WAL)
- PostgreSQL documentation: Routine Vacuuming
- PostgreSQL documentation: Heap-Only Tuples (HOT)
- PostgreSQL documentation: TOAST
- PostgreSQL documentation: Free Space Map
- PostgreSQL documentation: pageinspect
- SQLite: Database File Format
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 13: Data Storage Structures
- Ramakrishnan and Gehrke, Database Management Systems (3rd ed.), chapter 9: Storing Data: Disks and Files
Quiz Unlocks after the last step.