Postgres in depth · Chapter 54 of 65
A billion lesson events
By the end of this chapter you can split a huge table into partitions, write queries that read only the partitions they need, and add and remove whole months of data in milliseconds. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Inside Postgres
- 4 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
LCO's event log grows by three million rows a day; Kabir's plan for next year means a billion. Arjun reads one month at a time, yet every query walks an index over all of them. Sana must delete events older than a year, and a DELETE of 90 million rows would run for hours, write all of them to the WAL (chapter 45) and leave 90 million dead tuples for VACUUM (chapter 44). One table is the wrong shape for data with a lifetime. Partitioning turns it into many smaller tables that look like one.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
partitioned.sql: a table cut into months
lesson_events will reach a billion rows. Create events_by_month with the same six columns as lesson_events (id, student_id, lesson_id, event_type, watched_seconds, happened_at; all NOT NULL, no identity), partitioned by range on happened_at, with a primary key on id and happened_at, and one partition per month of 2026 from January to September, named events_2026_01 to events_2026_09.
Afterwards, loading every row of lesson_events must put each event in its own month's partition.
Careful: a range includes its start and excludes its end, so each month ends exactly where the next one begins; times are India time.
- Task 2
pruning.sql: read one month, not nine
The Check builds events_by_month as task 1 asks and loads lesson_events into it. Count the events of type complete in May 2026 (India time), so that Postgres reads only May's partition.
Return one column, completions. Afterwards the plan of your query must name events_2026_05 and no other partition.
Careful: Postgres can skip partitions only when the condition compares the partition key itself with constants.
- Task 3
attach.sql: October arrives
October's 5,000 events were loaded into a separate table, events_2026_10 (same columns, every row in October 2026), so the load did not touch the live table. Make it the October partition of events_by_month.
Afterwards events_2026_10 must be the partition for October (from 1 October up to 1 November) and events_by_month must show its 5,000 events.
Careful: attaching checks every row against the range while holding a lock; a CHECK constraint that already proves the range lets Postgres skip that scan.
- Task 4 · interview question
Interview: detach.sql, archive January
"How do you remove a month of old events from a huge table without a giant DELETE?" Take January out of events_by_month but keep it as a table of its own for the archive.
Afterwards events_2026_01 must still exist with all its rows, no longer a partition, and events_by_month must return no January events.
Careful: dropping the table would lose January; a DELETE would write WAL for every row and leave dead tuples behind.
What you will learn
- Declarative partitioning: PARTITION BY RANGE, LIST and HASH, and partitions.
- Keys on partitioned tables: why the primary key must include the partition key.
- Partition pruning: at planning time and at run time.
- ATTACH and DETACH: adding and removing a partition, cheaply.
More in Inside Postgres
Build "A billion lesson events" 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.