Designing Partitioning You Don't Have to Babysit

Database Architecture System DesignSix months in, p_future holds 800M rows because the growth projection didn't survive the workload, and every ALTER to fix it needs a maintenance window nobody wants to schedule. The boundary management is two lines of DDL; the harder part is picking a partition key that doesn't leak into application code.By Ruslan TolkachevTL;DRPartition by the primary key, not by created_at, and let a background service manage boundaries based on observed growth. Queries keep using the keys they already have, partition pruning works automatically, and the partition column never leaks into application code. The same “service watches and adjusts” pattern applies to hash and list partitioning with different operations.The orders dashboard started loading slowly the week after the partitioning deploy, and the team’s first instinct is to blame the new index strategy. The actual culprit shows up in EXPLAIN: thirty-six lines of Partitions: orders_p2025_01, orders_p2025_02, ... on a query that’s just SELECT * FROM orders WHERE id = 12345. The plan reads every partition because the WHERE clause doesn’t include created_at, and created_at is the partition key. The lookup that used to be one index probe is now thirty-six.The proposed fix is the one that always gets proposed: add created_at >= '2024-11-01' to the dashboard query. It works. The plan drops to one partition. Then the audit page does the same thing, then the admin tool, then the migration script. Three months later there’s an internal lint rule that flags any SELECT FROM orders without a date filter, and code reviews include “did you add the partition filter?” as a standard check. The partition key has stopped being a storage decision and become a contract every query has to honor. Forgetting still produces no error. Just slowness.The partition key problemBoth PostgreSQL and MySQL require the partition key to be part of any primary key or unique constraint on the table. That rule exists for correctness: if the primary key didn’t include the partition key, the database couldn’t enforce uniqueness without scanning every partition.The consequence is that if you want to partition by created_at, you can’t just have PRIMARY KEY (id) anymore. You need PRIMARY KEY (id, created_at).

The date column is now part of the primary key whether your application needed it to be or not.The more subtle cost is that id is no longer unique in the eyes of the database. Uniqueness is enforced on the tuple (id, created_at): the database will cheerfully accept two rows with the same id as long as they have different timestamps. The application probably still treats id as unique, but nothing in the schema guarantees it. And you can’t recover the guarantee with a separate UNIQUE (id) constraint: both MySQL and PostgreSQL require every unique constraint on a partitioned table to include the partition key columns. The uniqueness property has effectively been traded away.This isn’t purely cosmetic; it changes the query plans the optimizer is willing to generate:With PRIMARY KEY (id), WHERE id = 1 is a constant-time lookup. MySQL’s EXPLAIN shows this as the const access type; the optimizer knows exactly one row matches and the executor stops after finding it. Joins on id are eq_ref, the fastest join access type.With PRIMARY KEY (id, created_at), the same query becomes a ref lookup: a prefix scan on the leftmost index column that could, as far as the database is concerned, return multiple rows. Joins that used to be eq_ref become ref. Cardinality estimates fall back to index statistics instead of the guaranteed “one row” assumption, which can push the optimizer toward worse plans further up the query tree.To get the old const plan back, every lookup has to spell out the full primary key:1 2 3 4 5 -- Was a const lookup, now a ref lookup (one of potentially many rows) SELECT * FROM orders WHERE id = 1;

-- Back to const, but only if the caller knows the created_at SELECT * FROM orders WHERE id = 1 AND created_at = '2026-04-01 12:34:56'; That’s the same leakage as partition pruning, from a different angle: the partition key has forced its way into queries that had nothing to do with dates, first to get pruning and now to get single-row access.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 -- Before partitioning CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL );

-- After partitioning by month CREATE TABLE orders ( id BIGINT AUTO_INCREMENT, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at) -- created_at forced into the PK ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')), PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')), ... ); At this point everything still works. The table accepts inserts, queries return correct results, and the partition boundaries exist. The problem shows up the first time someone runs a query that doesn’t include created_at in the WHERE clause.Partition pruning only works if you ask for itPartition pruning is the optimization that makes partitioning worth doing. When a query’s WHERE clause restricts the partition key, the database can skip partitions that can’t possibly match. A query for last week’s orders only reads the one or two partitions that contain last week’s data.That optimization depends on the partition key appearing in the WHERE clause. A query that filters on anything else doesn’t get pruned; it scans every partition.1 2 3 4 5 -- This query scans every partition.

There are 36 of them. SELECT * FROM orders WHERE id = 12345;

-- This one prunes to a single partition SELECT * FROM orders WHERE id = 12345 AND created_at >= '2026-03-01' AND created_at < '2026-04-01'; The first query is the kind of lookup that happens constantly: fetch an order by its primary key. On a non-partitioned table, it’s a single index seek. On a partitioned table where the pruning key isn’t in the WHERE clause, it’s a separate index probe against every partition: 36 index lookups instead of one. Still fast in absolute terms, but much worse than the non-partitioned version, which is the opposite of why partitioning was introduced.The “fix” teams usually land on is to add the partition key to every query that touches the table. That’s a leaky abstraction. A storage decision is now a contract with every caller: new code has to remember the partition filter, old code has to be audited, the ORM has to be configured around it.No error, just slownessA query that should prune but doesn’t still returns correct results. The plan just scans every partition. No exception, no warning, no flag in the application logs, only an EXPLAIN that nobody reads until a dashboard times out. Most teams discover the failure by reviewing slow-query logs after a partition deploy, not from anything the database surfaces during query execution.Static partition boundaries don’t age wellThe other thing that tends to go wrong is hardcoding partition boundaries at table creation time. The initial layout reflects whatever the team’s growth projection looked like at that moment. Six months later the traffic pattern has changed, some partitions are 10x larger than others, and the p_future catch-all partition is holding half the table.1 2 3 4 5 6 7 -- Defined at creation: looks reasonable PARTITION p2026_q1 VALUES LESS THAN (100000000), PARTITION p2026_q2

VALUES LESS THAN (200000000), ...

-- Six months later: growth accelerated, p_future is now the entire active workload PARTITION p_future VALUES LESS THAN MAXVALUE -- 800M rows and growing Manually splitting and rebalancing partitions is operational work nobody wants to own. It requires scheduling maintenance windows, running ALTER TABLE ... REORGANIZE PARTITION against tables that might be hundreds of gigabytes, coordinating with application teams, and not making a mistake. It tends not to happen until there’s a performance incident, and at that point the fix is expensive.The shape of the better approachThe primary key already exists. For tables using BIGINT AUTO_INCREMENT, it’s monotonically increasing: newer rows have larger IDs. That’s the property range partitioning needs. The primary key is the partition key. 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL ) PARTITION BY RANGE (id) ( PARTITION p0001 VALUES LESS THAN (100000000), PARTITION p0002 VALUES LESS THAN (200000000), PARTITION p0003 VALUES LESS THAN (300000000), PARTITION p_future VALUES LESS THAN MAXVALUE ); Every query that filters by id (which is most of them) gets partition pruning for free, with no changes to application code. Range queries by ID prune across a small number of partitions. Point lookups prune to exactly one. The primary key is already in every WHERE clause that matters, because it’s the primary key.The trade-off is that partition boundaries aren’t directly defined by time anymore, which looks like it breaks time-based retention. In practice this is less of a trade-off than it looks; the point of partitioning often isn’t retention but keeping index sizes manageable, making maintenance operations cheap, and bounding the blast radius of a bad query.

When retention is a goal, boundaries can still be chosen to align with time. They just get picked at DDL time by the partitioner service, rather than baked into the schema. See Time-aligned boundaries without a date in the key.Automating range partition managementEverything up to this point assumes range partitioning: partitions defined by continuous boundaries on an ordered value (an ID range, a date range). The operational work is mechanical: watch the active partition fill up, split the MAXVALUE catch-all into a new bounded partition before that happens, and drop partitions that have fallen past the retention threshold. A small service running on a schedule is enough to keep the layout healthy. The hard part isn’t the logic, it’s doing it safely: running DDL against a large table without locking out writes, handling partial failures, and recovering cleanly if the service crashes mid-operation.1 2 3 4 5 6 -- Split the catch-all partition into a new bounded partition + new catch-all -- This is the operation the service runs periodically ALTER TABLE orders REORGANIZE PARTITION p_future INTO ( PARTITION p0037 VALUES LESS THAN (3700000000), PARTITION p_future VALUES LESS THAN MAXVALUE ); REORGANIZE PARTITION on an empty catch-all partition is fast; there’s nothing to move. If you split the catch-all before any rows land above the split point, the operation is metadata-only. The service’s job is to stay ahead of the write workload: split the catch-all when it’s still small or empty, not when it’s already holding hundreds of millions of rows.What makes the service tricky in productionThe logic is a couple of DDL statements. The hard parts are everything around them: not locking out writes during the REORGANIZE, surviving a service crash mid-DDL (idempotency on retry), handling concurrent migration tooling that’s also taking ACCESS EXCLUSIVE on the table, and having a clear runbook for “the service has stalled and the catch-all is now 200M rows, what do we do.” Production-grade partitioners usually spend more code on the operations bracket than on the DDL itself.There’s no single right target; it depends on what’s driving the partitioning in the first place.