Partition pruning is paid for in your primary key definition

Pruning happens in the planner, before a row is read. The database compares the predicate against each child table's declared range bounds and opens only the children that could hold matching rows, so a query filtered from 2024-03-01 opens March and skips the rest ^[8]. The author is blunt that this skipping is the entire point, and that everything else is a cost paid to get it ^[8].

The predicate carries the weight here; the storage layout is just where that weight lands. Omit the partition key from the WHERE clause and there is nothing to prune against, every child gets opened, and one large scan becomes forty smaller ones plus coordination overhead, which the post calls strictly worse than where you started ^[9]. With monthly ranges, forty children is forty months, a bit over three years of scan events fanned out to answer one query ^[1].

The change that outlives the migration is in the constraint. The declaration is `PARTITION BY RANGE (created_at)` with one child per month ^[10], and the primary key then has to carry the key column: `PRIMARY KEY (id, created_at)` ^[11]. You cannot keep a global unique constraint on `id` alone, because enforcing it would mean checking every partition ^[12]. That is the same property in both directions. The reason a dated query can ignore 39 children is the reason a uniqueness check cannot. Foreign keys pointing at the partitioned table get awkward, and an ORM that assumes a single-column primary key becomes work ^[13].

Their billion describes their table's shape, tied to their own access pattern rather than any general rule. Tens of thousands of orders a day, a dozen or more events per parcel, append-only ^[2]. At the floor of "tens of thousands", 20,000 orders times 12 events is 240,000 rows a day, or roughly 7.2 million in a month ^[3], and a billion rows divided by a dozen events is around 83 million parcels of history ^[2]. That shape is what makes the monthly split work, along with an access pattern where dashboards look at today, merchant tracking at the last few weeks, and analytics at a month ^[14]. The post is explicit that the precondition is access pattern, not size ^[6].

For the number to transfer, your table needs every hot query bound on the partition column, index maintenance and retention already unschedulable rather than merely slow, and a DELETE of a hundred million rows that you currently cannot run without flattening write throughput for hours ^[5]. If your queries filter on several dimensions with no dominant one, you get the complexity and none of the pruning ^[9], and the post's own advice is to index properly instead ^[4].

Then there is the part with no recipe. Every tutorial shows the CREATE TABLE form; almost none show how to move a live billion-row table into that shape without downtime you cannot afford ^[15]. The version I read ends mid-word at exactly that sentence, which is a fittingly literal demonstration of the gap ^[15]. The honest way to file this work is as a schema change with an application-side blast radius, sized by whoever owns the ORM mapping, with the faster query arriving as the closing line of the justification, well after the constraints have had their say.