PostgreSQL internals and extension development
You inspect heap pages, tuple headers, MVCC visibility, WAL records and the planner with tools, and write a small extension in C to understand PostgreSQL from the inside.
Level 501 · 13 lessons · 12 practice labs
Lessons
Storage
- Heap pages and tuples
- xmin, xmax and visibility rules
- TOAST: storing large values
- The free space and visibility maps
- Short task: trace an UPDATE on the page
Log and transactions
- WAL records with pg_waldump
- Transaction ID wraparound and freezing
- HOT updates
- Short task: rescue a database warning of wraparound
Planner and extensions
- Inside the planner: paths and costs
- Hooks and extension structure
- Writing a function extension in C
- Capstone: write your own metrics extension
Practice labs
- Trace an UPDATE on the page: Update a row and use pageinspect to show where the old and new versions sit on the page and their flags.
- Raise the share of HOT updates: Raise a table's HOT update ratio by changing fillfactor and index choice and measure it from statistics.
- Apply the visibility rules by hand: Work out which versions are visible from given xmin, xmax and snapshot values and confirm with a real query.
- See a large value go to TOAST: Insert a long text, find the TOAST table and compare main and TOAST sizes.
- Change the compression method: Change a JSONB column's compression method and measure the space and read-speed difference.
- Interpret the free space map: Read the free space and visibility maps with extensions after deletes and predict where new rows will go.
- Read WAL records with pg_waldump: Insert into a table, find the matching WAL records with pg_waldump and explain their types.
- Watch freeze age: Calculate transaction ID age per database and table and rank the riskiest tables.
- Rescue a database warning of wraparound: Find the blocking transaction in a lab database warning of wraparound and bring it to safety with a freezing VACUUM.
- Compare a plan's path costs: Change cost settings and show how the planner switches between index and sequential scans.
- Write a simple C function extension: Compile and load a C function that computes the greatest common divisor as an extension.
- Write a hook extension that counts queries: Write a small extension using an executor hook to collect query count and time and expose them through a view.
About this course
You inspect heap pages, tuple headers, MVCC visibility, WAL records and the planner with tools, and write a small extension in C to understand PostgreSQL from the inside.
What you will learn
- Storage
- Log and transactions
- Planner and extensions
Prerequisites
You should be experienced in the topics of the same path up to level 401.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 501; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.