Postgres in depth · Chapter 42 of 65
Where a row actually lives
By the end of this chapter you can find a table's file on disk, read one of its 8 KB pages with pageinspect, find a single row by its ctid, and explain why column order and long values change how much space a row takes. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Safety, security and storage
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Arjun asks a simple question: "the lesson progress table has 1,955 rows; how much disk is that, and why?" Priya has a harder one: LCO will soon log a billion lesson events, and every wasted byte per row becomes a gigabyte.
Until now a table was rows and columns. To answer either question you need to see what Postgres actually writes: files, pages, line pointers and tuples. This chapter opens them up with the lab's tools; the next two chapters build on what you see here.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
where-is-it.sql: the lessons table on disk
Priya wants to know where lessons lives. Return one row with three columns: file_path (the table's file, relative to the data folder), pages (how many 8 KB pages the file has, from its size in bytes) and toast_table (the name of its TOAST table, as text, such as pg_toast.pg_toast_12345).
Ask Postgres for all three: the Check's copy of the database has other numbers than yours.
- Task 2
find-tara.sql: Tara's row on its page
Find Tara Menon's row in students on disk. Return one row: page and item (the two numbers of her row's ctid), lp_off (where her row starts inside the page, in bytes) and lp_len (how many bytes it takes), read from that page with pageinspect.
Do not type the page or the item: the second dataset puts her somewhere else.
- Task 3
page-space.sql: how full is each page?
For every page of lesson_progress, return one row: page (from 0), line_pointers (how many line pointers the page has) and free_bytes (the free space between the line pointers and the rows). Order: by page.
Work both numbers out from page_header: line pointers are 4 bytes each and start right after the 24-byte header, and the free space lies between lower and upper. Count the pages from the table's size, not by hand.
- Task 4
toast.sql: where a long transcript goes
Kabir pasted a 13,199-character transcript into the first lesson of SQL Fundamentals (Welcome to the course, in its section at position 1); the lesson shows the UPDATE to run in your database. Return one row: chars (its length in characters), stored_bytes (how many bytes Postgres stores for that value) and toast_table_bytes (the size in bytes of lessons' TOAST table).
- Task 5 · interview question
Interview: column-order.sql, no bytes wasted
LCO will store a billion lesson events. Create the table lesson_events with these columns: student_id bigint, lesson_id integer, event_at timestamptz, watched smallint, is_done boolean. Put them in the order that wastes no bytes on alignment padding.
The Check inserts a row and reads its size on the page with pageinspect: it must be as small as these five columns can be.
Careful: the order above is not the best one. Each value must start at a multiple of its type's alignment, and the gaps cost bytes in every row.
What you will learn
- Files and forks: where a table lives on disk.
- Pages: the 8 KB unit Postgres reads and writes, and its layout.
- Tuples and ctid: a row's header and its physical address.
- Alignment: why column order changes a row's size.
- TOAST: where values too big for a page go.
More in Safety, security and storage
Build "Where a row actually lives" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.