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.

  1. 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.

  2. 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.

  3. 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.

  4. 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).

  5. 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.