Partitioning: How We Cut Down Our MySQL DB Load

As applications mature and user adoption grows, database tables often accumulate million or even tens of million of records, Over time, larger datasets lead to bigger indexes, slower queries, longer maintenance windows, and increased infra costs, We were also facing the similar challenges in one of our production applications where order volume was growing steadily. After a few years in production, the table had grown to a few tens of millions of rows. The indexing sizes swelled, and we ended up hitting performance bottlenecks. It was an operational nightmare as we scrambled to get the performance back on track.

This post walks through why we decided to partition the table, the trade-offs we considered before committing to it, and the steps we followed to implement it safely in production.

To keep the examples simple, let’s assume we have a transactions table while created_at is the column used by most of our time-bound queries and the column we eventually chose for partitioning

The problem

Every query that touched the transactions table whether it was a customer-facing API, an internal report, or an internal dashboard, was scanning against a table that had been accumulating data since day one. Most of these queries only cared about a recent window of time (a year, sometimes two), but MySQL had no way of knowing that without physically scanning through years of rows and their indexes.

We’d already been through several rounds of optimization before this, better indexing, query rewrites, caching hot lookups and those bought us real headroom. But eventually we hit a ceiling that indexing alone couldn’t fix: the table itself was too large for its own good, and its size kept working against every query that only needed a slice of it.

The key insight was simple: almost every high-traffic query filters on the created_at column, and we rarely need more than a year or two of it in the “hot” path. So the fix wasn’t just a better index. It was making sure DB never had to look at years of data it didn’t need to.

Why partitioning, and why it isn’t a casual decision

Partitioning was the right lever for this specific problem, but it’s not something we went into lightly. It comes with real trade-offs:

You lose foreign keys. MySQL does not allow foreign key constraints on a partitioned table, either as the parent or the child. Referential integrity that used to live in the database has to move up into the application layer. On the application side this also meant updating our JPA/Hibernate entity mappings, Hibernate will otherwise still try to generate a foreign key constraint from the relationship annotation, so we had to explicitly mark it as unconstrained:

 @JoinColumn(name = "transaction_id", foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT)).

Without this, Hibernate’s DDL generation (or a schema-validation step) would keep trying to add back the very constraint we’d just dropped at the database level.

• The partitioning column must be part of every unique key, including the primary key. If your primary key doesn’t already include that column, you have to change it which has ripple effects on anything that assumes the old primary key shape.

• Every existing row needs a valid value in the partitioning column. Nulls and out-of-range values have to be cleaned up first, or the partitioning scheme won’t make sense.

• It changes how the table behaves operationally: backups, schema changes, and even some query plans behave differently once a table is partitioned.

Given the size of the table, we didn’t want to discover any of this the hard way on production. So we cloned the production database, ran the entire process against the clone first, verified it end to end, and only then repeated it on the live table.

Step-by-step: what we did

1. Identify every foreign key touching transactions, both keys defined on other tables that reference transactions, and keys defined on transactions itself referencing other tables:

SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'transactions'
AND TABLE_SCHEMA = DATABASE();

2. Drop the foreign keys from dependent tables (e.g. items):

ALTER TABLE items DROP FOREIGN KEY FK3s9vxneb3dk3plhpv9s213so0;
--repeated for each dependent table

3. Check the existing range of created_at, so we knew what we were working with:

SELECT MIN(created_at), MAX(created_at) FROM transactions;

4. Clean up rows that would break the partitioning scheme. Before partitioning the table, make sure every existing row has a valid value in the partitioning column. Check with below query

SELECT COUNT(*) FROM transactions WHERE created_at IS NULL;

If any null rows remain, you have to decide how to backfill them with a real value.

5. Find and drop the remaining foreign keys defined on transactions itself

SELECT 
    tc.CONSTRAINT_NAME,
    kcu.COLUMN_NAME,
    kcu.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA   = kcu.TABLE_SCHEMA
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND  tc.TABLE_NAME      = 'transactions'
AND  tc.TABLE_SCHEMA    = DATABASE();

ALTER TABLE transactions DROP FOREIGN KEY FK91hpo6ynfiex02lrim71wr2d0;
ALTER TABLE transactions DROP FOREIGN KEY FKb1443sk0bxprtkqree3o2qk90;
ALTER TABLE transactions DROP FOREIGN KEY FKn3ck7o7uiar2un2sswdc8nm12;
ALTER TABLE transactions DROP FOREIGN KEY FKpxtb8awmi0dk6smoh2vp1litg;

6. Rebuild the primary key to include created_at. This is a hard MySQL requirement ,every unique key on a partitioned table, including the primary key, must include the column(s) used in the partitioning expression:

ALTER TABLE transactions
    MODIFY created_at datetime(6) NOT NULL,
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (id, created_at),
    ALGORITHM=INPLACE,
    LOCK=NONE;

Why combine id and created_at in the primary key?

The combined (id, created_at) primary key allowed us to satisfy MySQL’s partitioning requirement while retaining id as the primary identifier for our table.

There is also an important operational benefit to doing this as part of the same schema change: we could modify the column definition, remove unused columns, and rebuild the primary key in a single DDL operation instead of performing multiple separate table alterations.

In the above query we ran, you might have noticed we have used ALGORITHM=INPLACE,LOCK=NONE, which allows MySql to perform the supported table alteration as an online DDL operation, minimizing disruption to concurrent reads and writes compared with a traditional table-copy operation. However, LOCK=NONE should not be interpreted as an absolute guarantee of zero locking metadata locks can still occur, and the exact behavior depends on the MySQL version and the specific alteration being performed.

For a table with tens of millions of rows, being able to perform the supported parts of the schema change online was particularly useful because it reduced the impact on the application during the migration.

7. Create the partitions, keyed on the year of created_at:

ALTER TABLE transactions
 PARTITION BY RANGE COLUMNS(created_at) (
 PARTITION p_past VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
 PARTITION p2022 VALUES LESS THAN ('2023-01-01') ENGINE = InnoDB,
 PARTITION p2023 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
 PARTITION p2024 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
 PARTITION p2025 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB,
 PARTITION p2026 VALUES LESS THAN ('2027-01-01') ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB

);

8. Verify the partitions exist and look correct:

SELECT 
    PARTITION_NAME,
    PARTITION_DESCRIPTION,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME   = 'transactions'
AND   TABLE_SCHEMA = DATABASE()
ORDER BY PARTITION_ORDINAL_POSITION;

9. Confirm partition pruning is actually happening. This is the step that tells you whether any of this was worth it. MySQL should only scan the partition(s) that overlap the query’s date range, not the whole table:

EXPLAIN SELECT * FROM transactions 
WHERE created_at BETWEEN '2026-01-01' AND '2026-04-10';

The EXPLAIN output showed only the relevant partition being touched, confirming pruning was working as expected.

The result

After rolling this out, we saw a dramatic drop in database load, fewer rows and index entries scanned per query translated directly into less CPU and I/O pressure on the primary, and our date-filtered reports and transaction APIs got noticeably faster. Peak load dropped by more than 80%, and the daily saturation spikes we’d grown used to simply stopped happening.

A few honest caveats

In the interest of giving the full picture, not just the win:

• Referential integrity is now our responsibility, not the database’s. Since MySQL doesn’t allow foreign keys on partitioned tables, we lost the safety net of the database rejecting an orphaned row. We now have to enforce those relationships in application code and catch violations through monitoring instead of a constraint failure.

• On production, an ALTER TABLE … PARTITION BY of this scale is a heavy, locking operation if run directly, so it’s worth running it during a low-traffic maintenance window, rather than as a direct ALTER TABLE against the live table. The direct statements above are exactly what we validated on the clone first, for that reason, then executed against the live table.

• Partitioning isn’t a substitute for good indexing : it works alongside it. Pruning gets you to the right partition; you still need the right index inside that partition for the query to be fast.

• Old data doesn’t disappear: it just sits in colder partitions. If we ever need to archive or purge pre-2018-style data, partitioning also makes that a much simpler DROP PARTITION instead of a slow, index-heavy DELETE.

Partitioning isn’t something we’d reach for casually, and it took real planning and cleaning the data, reworking the primary key, and giving up the convenience of foreign keys, but for a table where the access pattern is this clearly time-bound, it was the right trade-off.