Postgres with Drizzle · Chapter 37 of 40
The partition strategy
By the end of this chapter you can split a big table into pieces Postgres manages: rows find their piece, queries read only the pieces they need, and a month comes off in one statement. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Production Patterns
- 7 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Every post ever written lives in one posts table. Rohan's dashboard asks for last month's posts, and its index covers every year. Vikram's policy says posts older than two years must go: today that is a DELETE that visits every old row, leaves dead rows for VACUUM, and runs for ages.
Splitting the posts by hand into posts_2024_01, posts_2024_02 and so on fixes both, but then every query must know which table to use. Anya wants Postgres to do the split, so the app keeps talking to one table.
What you build: 7 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
createPartitionedPostsTable: a posts table split by date
Write createPartitionedPostsTable(). It drops partitioned_posts if it exists (with its partitions), then creates partitioned_posts with the columns id (serial), author_id (integer, not null), content (text, not null) and created_at (timestamptz, not null, default now), a primary key on id and created_at together, partitioned by range on created_at.
Send raw SQL with getPool().query. Return an object with created: true. Afterwards: partitioned_posts exists as a partitioned table (relkind p in pg_class).
- Task 2
createMonthlyPartition: one partition for one month
Write createMonthlyPartition(year, month). Both are numbers; month runs from 1 to 12. It creates a partition of partitioned_posts named partitioned_posts_YYYY_MM, with the month in two digits (partitioned_posts_2024_03 for March 2024), that takes created_at from the first day of that month (included) up to the first day of the next month (not included).
Return an object with partitionName (that name) and created: true. Afterwards: the partition is listed as a child of partitioned_posts.
- Task 3
createMonthlyPartition: December rolls into the next year
The same createMonthlyPartition(year, month) must work for December: createMonthlyPartition(2024, 12) creates partitioned_posts_2024_12 for created_at from 2024-12-01 (included) to 2025-01-01 (not included), and returns partitionName and created: true.
The check then inserts a post dated 2024-12-15 into partitioned_posts and expects to find it in partitioned_posts_2024_12.
- Task 4
insertIntoPartitionedTable: let Postgres route the rows
Write insertIntoPartitionedTable(posts). posts is an array of objects with author_id (a number), content and created_at (an ISO date string such as 2024-01-15T10:00:00Z). Insert each one into partitioned_posts itself, the parent, never into a partition by name.
Return an array of the inserted rows, whole rows as the insert returns them, in the same order as posts. Afterwards: each row is stored in the partition for its month (the check creates January and February 2024 first, then reads each partition).
- Task 5
queryWithPartitionPruning: rows for a date range, and the plan
Write queryWithPartitionPruning(startDate, endDate). Both are date strings such as 2024-02-01. It selects id, author_id, content and created_at from partitioned_posts where created_at is at or after startDate and before endDate, ordered by created_at. Then it runs the same query again under EXPLAIN (FORMAT JSON).
Return an object with rows (the rows of the first query) and plan (the value of the QUERY PLAN column in the one row EXPLAIN returns: an array). With the check's January, February and March posts, the February range gives one row, Feb post.
- Task 6
detachOldPartition: take one month off
Write detachOldPartition(year, month). Both are numbers. It detaches the partition partitioned_posts_YYYY_MM (month in two digits) from partitioned_posts, then counts the rows in that table, which now stands on its own.
Return an object with detached: true and remainingRows: that count, a number, not a string. Afterwards: partitioned_posts no longer returns those rows, and the detached table still has them (the check detaches January with two posts and expects 2, and 1 row left in the parent).
- Task 7
getPartitionStats: list the partitions and their sizes
Write getPartitionStats(). It returns one row per partition of partitioned_posts, with the columns partition_name (the partition's name), total_bytes (its total size from pg_total_relation_size), size (that size made readable by pg_size_pretty) and estimated_rows (pg_class's reltuples as a bigint).
Return result.rows, ordered by partition_name. The check creates the January, February and March 2024 partitions first and expects exactly those three, in that order.
What you will learn
- PARTITION BY and PARTITION OF: a parent that holds no rows, and the partitions that do.
- Routing and bounds: each row goes to the partition it fits.
- Partition pruning: a filter on the key skips the other partitions, as EXPLAIN shows.
- DETACH PARTITION and pg_inherits: take a partition off, and list them.
More in Production Patterns
Build The partition strategy yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. 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 with Drizzle are free. This one comes with the all labs plan.