Postgres walkthrough

Setup: a student table with 3 records (49:04).

  1. Inspect free space with the pg_freespacemap extension, which exposes free space per page as a table-like function (49:04, 49:26).
    • Block 0 reports 97% full with only 3 tuples, which makes sense given Postgres' 8 KB pages (49:26, 49:45).
  2. Insert 1,000 tuples using the generate_series idiom; the table now holds 1,003 tuples across 7 pages (49:45, 50:05, 50:32).
    • The free space query returns all zeros — implausible, since the last page cannot be exactly 100% utilized (50:05, 50:32).
  3. Find the files on disk with pg_relation_filepath (50:52).
    • Path resolves under the base directory to a per-database numeric directory (16835) containing the table's numeric file (16653) (51:11, 51:24, 51:46, 52:08).
    • The table data file is 56 KB — a multiple of 8 KB, i.e. 7 pages (52:08).
    • Sibling files: _fsm = free space map, _vm = visibility map (deferred to the transactions lectures) (52:28).
  4. Why the zeros: Postgres does not keep the in-memory free space map perfectly in sync with disk. Being slightly wrong only costs some unused space, which is eventually reclaimed (52:49).
  5. Run VACUUM — Postgres' garbage collector for tables. Afterwards the first six pages report full and the last one is only partially used (52:49, 53:22, 53:43).
  6. Delete ~half the tuples (coin flip via random() < 0.5). This time the free space map is updated immediately and many pages show free space (53:43, 53:59, 54:12).
  7. Insert 10 more tuples, vacuum, re-query: block 0 went from 46% free to 40% full — the new tuples were placed in the first page, not appended at the end (54:29, 55:03).
    • Demonstrates that the relational model is unordered: the DBMS puts tuples wherever free space exists (55:21).

SQL Server contrast

  • A metadata command lists per-page IDs together with page types — index allocation map vs data page — i.e. SQL Server tracks both where a page is and what it contains. Postgres does not track this (55:40, 56:01).

Q&A from the demo

  • Can Postgres compact partially filled pages? Yes — VACUUM FULL rewrites the table into a new copy packed in order to minimize pages, but it is expensive: it locks the whole table and blocks other updates (56:23, 56:40, 57:00).
  • When does an application call vacuum? It should not — vacuum has triggers (e.g. roughly 10% of tuples updated) or can be scheduled nightly; it is an administrator-level concern (57:19, 57:33, 57:45).
  • Why map both tables and indexes in the directory? Same mechanism serves any database object; there is no reason not to reuse it (57:45, 58:02, 58:23).
  • Does the index directory entry track values? No. The page directory only says where to find a page. Interpreting it — B+ tree, hash table — happens after landing inside the page (58:23, 58:40, 58:55).