Designing DB partitions you don't have to babysit
Designing DB Partitions You Don't Have to Babysit
Building systems that don't page you at 3 AM—and fostering teams that keep it that way.
By Ruslan Tolkachev, Staff Systems Engineer (Databases & Data Infra)
🚩 The "Maintenance Window" Nightmare
Imagine this: six months after deployment, your p_future partition is bloated with 800 million rows. Why? Because your initial growth projections were optimistic and didn't survive the actual workload. Now, every ALTER statement required to fix the mess demands a maintenance window that nobody is willing to schedule.
The actual DDL to manage boundaries is trivial—just a few lines of code. The real challenge is selecting a partition key that doesn't leak into your application logic.
⚡ TL;DR
- The Strategy: Partition by the Primary Key, not by
created_at. - The Automation: Use a background service to manage boundaries based on actual observed growth.
- The Result: Queries continue using existing keys, partition pruning happens automatically, and the partition column remains an implementation detail, never leaking into the app code.
- Versatility: This "watch-and-adjust" pattern works for hash and list partitioning as well.
📉 The "Slow Dashboard" Symptom
It happened a week after the partitioning deploy: the orders dashboard started lagging. The team's immediate reaction was to blame the new indexing strategy. However, the truth was hidden in the EXPLAIN output:
EXPLAIN ANALYZE output:
Partitions: orders_p2025_01, orders_p2025_02, orders_p2025_03 ... (36 lines total)
This was occurring on a simple query:
SELECT * FROM orders WHERE id = 12345;
The database was scanning every single partition because the WHERE clause lacked a created_at filter, and created_at was the partition key. A lookup that previously required a single index probe now required thirty-six.
The "Quick Fix" Spiral
The proposed solution is always the same: “Just add created_at >= '2024-11-01' to the dashboard query.”
This creates a domino effect:
- Update the dashboard query.
- Update the audit page.
- Update the admin tool.
- Update the migration scripts.
Three months later, the team has an internal lint rule flagging any SELECT from orders without a date filter. Code reviews now consist of the same repetitive question: "Is this performant?" "Did you add the partition filter?"
The partition key has evolved from a storage optimization into a rigid contract that every single query must honor.
🛠️ The Partition Key Problem
Both MySQL and PostgreSQL enforce a strict rule: The partition key must be part of any primary key or unique constraint on the table.
Why this rule exists
This is a requirement for data correctness. If the primary key didn't include the partition key, the database would have to scan every single partition to ensure a value is truly unique before allowing an insert.
The Consequences
If you choose to partition by created_at, you can no longer have a simple PRIMARY KEY (id). The date column is now forced into the primary key, regardless of whether your application logic requires it.
The Uniqueness Gap
The most dangerous side effect is that id is no longer unique in the eyes of the database. Uniqueness is now enforced on the tuple .
The database will happily accept two different rows with the same id as long as their timestamps differ. While your application might still assume id is unique, the schema no longer guarantees it. You cannot fix this with a separate UNIQUE (id) constraint because that, too, would require the partition key.
🚀 Performance: const vs. ref
This isn't just about schema design; it fundamentally alters the optimizer's behavior.
| Primary Key Strategy | Access Type | Optimizer Behavior |
|---|---|---|
PRIMARY KEY (id) | const | Knows exactly one row matches; stops immediately. |
PRIMARY KEY (id, created_at) | ref | Performs a prefix scan; assumes multiple rows could match. |
When using a composite key, the optimizer relies on index statistics rather than a guaranteed single-row match. This can lead to suboptimal query plans further up the execution tree.
To regain the const lookup performance, the caller must provide the full primary key:
-- ❌ Now a 'ref' lookup (potentially many rows)
SELECT * FROM orders WHERE id = 1;
-- ✅ Back to 'const' (but only if the app knows the exact timestamp)
SELECT * FROM orders WHERE id = 1 AND created_at = '2026-04-01 12:34:56';
This is the same "leakage" as partition pruning, just from a different angle.
💻 Implementation Comparison
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 is now 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')),
...
);
The table functions, and the data is partitioned. But the problem emerges the moment a developer runs a query without created_at.
🔍 Understanding Partition Pruning
Partition pruning is the primary reason to use partitioning. It allows the database to ignore partitions that cannot possibly contain the requested data.
Example Comparison:
- Pruned Query:
SELECT * FROM orders WHERE id = 12345 AND created_at = '2026-03-01';Reads 1 partition. - Non-Pruned Query:
SELECT * FROM orders WHERE id = 12345;Reads every partition.
The first query is the ideal, but the second query is the one that happens thousands of times per second in a typical application. On a partitioned table wh...