Your SQL sees one table. Underneath, one table per month.
Each row is routed by created_at to the partition whose bounds hold it. Every partition lives on the same server.
Split by a key, like sharding. Same win?
Bad: More pieces, more capacity
Every insert still hits one server and one log.
Good: One server, better organised
SQL and transactions don’t change. Capacity is sharding’s job.
Ask for 15 Sep to 15 Oct. Eleven partitions never open.
The planner checks the WHERE clause against each partition’s bounds before reading a page. No index needed to skip them.
≈ 90 GB
of WAL to DELETE one month
Then vacuum reads ≈ 465 GB, and the disk doesn’t shrink. Detach and drop the partition instead, kilobytes of WAL and 105 GB back.
PRIMARY KEY (txn_id) on a table partitioned by month? PostgreSQL and MySQL both refuse.
Why
There’s no global index. Each partition’s index sees only its own rows, so equal keys must land together: add created_at.
Partition payments by what?
Bad: Hash by payer, for balance
Each partition mixes every month: date ranges visit all, no cheap drop.
Good: Range by created_at
Months stay together: queries prune by time; retention is a drop.
Where partitioning bites back
- No key in the query? It probes every partition
- A function on the key defeats pruning
- Update the key and the row moves partitions
- Too many partitions slow planning and locking
Remember
Partitioning is a data-management tool, not a capacity tool. Range by created_at so queries prune and retention is a drop; unique keys must include created_at.