GoKawiilTL;DR Partition 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 problem
Both 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.
, is a constant-time lookup. MySQL’s EXPLAIN shows this as the access type; the optimizer knows exactly one row matches and the executor stops after finding it. Joins on are , 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.
... continue reading