MariaDB Table Partitioning: When It Helps and When It Doesn't

I've been burned by table partitioning twice in the last five years — once it saved a client's reporting pipeline, and once it made things measurably worse. The difference wasn't the feature, it was whether I actually understood the query patterns before I reached for the hammer.

If you're staring at a audit_log or events table that's crossed 50 million rows and your DBAs (or you, wearing the DBA hat at 11pm) are getting nervous, here's what I've learned the hard way.

What Partitioning Actually Does

MariaDB's partitioning splits a single logical table across multiple physical sub-tables — the partitions. From your application's perspective, it's still one table. Under the hood, MariaDB can skip entire partitions when the query's WHERE clause aligns with the partition key. That skip is called partition pruning, and it's the whole reason you're interested in this.

For an audit log, the canonical use case is range partitioning by date. You insert rows forever, queries almost always filter by a recent time window, and you need to drop old data periodically. Partitioning is genuinely good at all three of those things — when your schema and queries cooperate.

The trap is assuming partitioning is a general-purpose "this table is big" fix. It isn't.

The Setup That Actually Works

I'll use a real schema pattern I've used for a healthcare client's audit trail. HIPAA requires keeping access logs for six years, and the table was sitting at around 200 million rows. Read queries were almost always "show me activity for this user over the last 30 days" or "show me all events in this date range."

That's a perfect partitioning candidate. Here's how I set it up:

CREATE TABLE audit_log (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at    DATETIME        NOT NULL,
    user_id       INT UNSIGNED    NOT NULL,
    resource_type VARCHAR(64)     NOT NULL,
    resource_id   INT UNSIGNED    NOT NULL,
    action        VARCHAR(32)     NOT NULL,
    ip_address    VARCHAR(45)     NOT NULL,
    payload       JSON,
    PRIMARY KEY (id, created_at),  -- partition key must be in PK
    KEY idx_user_created (user_id, created_at),
    KEY idx_resource (resource_type, resource_id, created_at)
)
ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2022_q1 VALUES LESS THAN (TO_DAYS('2022-04-01')),
    PARTITION p2022_q2 VALUES LESS THAN (TO_DAYS('2022-07-01')),
    PARTITION p2022_q3 VALUES LESS THAN (TO_DAYS('2022-10-01')),
    PARTITION p2022_q4 VALUES LESS THAN (TO_DAYS('2023-01-01')),
    PARTITION p2023_q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p2023_q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION p2023_q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
    PARTITION p2023_q4 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_future  VALUES LESS THAN MAXVALUE
);

A few things to note in that DDL:

The primary key must include the partition key. This trips everyone up the first time. MariaDB requires that the partition expression columns appear in every unique index, including the PK. So if you have PRIMARY KEY (id) and try to partition on created_at, you'll get an error. The workaround — PRIMARY KEY (id, created_at) — works fine but means your application joins and lookups by id alone still work, because id is still unique within the composite key.

I use quarterly partitions, not monthly. Monthly partitions on a table that needs to live for six years means 72+ partitions. MariaDB opens all partition files on table access, and with InnoDB you can hit file descriptor and memory overhead that's not trivial. Quarterly keeps it manageable — 24-28 partitions over a retention window.

The p_future catch-all partition is not optional. Without MAXVALUE, any insert with a created_at beyond your last explicit partition boundary throws an error and your app breaks. I always keep a p_future and use a scheduled job to split it out as the quarter approaches.

Here's the Laravel Artisan command I run quarterly to reorganize the future partition:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class ReorganizeAuditPartitions extends Command
{
    protected $signature   = 'audit:reorganize-partitions';
    protected $description = 'Split p_future into a concrete quarterly partition + new future catch-all';

    public function handle(): int
    {
        $now     = Carbon::now();
        $quarter = (int) ceil($now->month / 3);
        $year    = $now->year;
        $label   = "p{$year}_q{$quarter}";

        // End of this quarter
        $endOfQuarter = Carbon::create($year, $quarter * 3, 1)
            ->endOfMonth()
            ->addDay()
            ->startOfDay();

        $boundary = $endOfQuarter->format('Y-m-d');

        $sql = "
            ALTER TABLE audit_log
            REORGANIZE PARTITION p_future INTO (
                PARTITION {$label} VALUES LESS THAN (TO_DAYS('{$boundary}')),
                PARTITION p_future VALUES LESS THAN MAXVALUE
            )
        ";

        $this->info("Running: {$sql}");
        DB::statement($sql);
        $this->info('Partition reorganized successfully.');

        return self::SUCCESS;
    }
}

I schedule this to run on the first day of each quarter. The REORGANIZE PARTITION operation in MariaDB is online for InnoDB — it takes a metadata lock briefly but doesn't lock reads for the duration. On a quiet maintenance window it's fine.

Dropping Old Partitions: Where Partitioning Shines

This is the feature that actually saved this client. Before partitioning, purging six-year-old records was a DELETE with millions of rows, running in batches, causing IO spikes, taking hours, making the DBA nervous.

After partitioning:

ALTER TABLE audit_log DROP PARTITION p2022_q1;

That's it. It's a metadata operation. It completes in milliseconds regardless of how many rows were in that partition. For retention-heavy tables — audit logs, event streams, telemetry — this alone justifies the complexity of setting up partitioning.

The Gotchas That Bit Me

Partition pruning only works if the query filter uses the partition key directly. If your query looks like this:

SELECT * FROM audit_log
WHERE user_id = 42
AND created_at >= '2024-01-01';

MariaDB prunes correctly — it only scans the relevant partitions. But if you wrap created_at in a function:

SELECT * FROM audit_log
WHERE user_id = 42
AND DATE(created_at) >= '2024-01-01';  -- no pruning

No pruning. Full scan across all partitions. I've seen this in ORM-generated queries, especially when someone adds a DATE() cast in a scope without thinking about it. Always run EXPLAIN PARTITIONS to verify pruning is happening.

Indexes are local to partitions. A KEY idx_user_created (user_id, created_at) exists in each partition separately. If you query by user_id without a created_at filter, MariaDB has to scan that index in every partition. With 24 partitions and millions of rows each, that's worse than a single-table index scan in some cases. I've seen queries that were fast on a non-partitioned table get slower after partitioning because the access pattern didn't include the date range.

AUTO_INCREMENT and the composite PK interact oddly under replication. On a Galera cluster (which I run for a couple of clients), AUTO_INCREMENT with composite PKs can cause gaps or conflicts depending on your wsrep_auto_increment_control settings. It's manageable but you need to know it's there.

ALTER TABLE on a partitioned table is slow. Adding a column, changing a column type — these operations have to touch every partition. On a 200M-row table split across 24 partitions, an ALTER that would've been fast with pt-online-schema-change on a normal table now has to rebuild 24 sub-tables. Use pt-osc or MariaDB's ALGORITHM=INSTANT where available, and test your migrations on a copy of production before you run them.

When I'd Reach for Partitioning

• The table is genuinely large (50M+ rows) and grows continuously
• You have a clear, dominant time-based access pattern — almost every query includes a date range filter
• You need to purge old data regularly and can't afford the IO cost of batch DELETEs
• Your partition key can live in the primary key without breaking your application

When I Wouldn't

• The table is big but queries don't filter by date. Partitioning won't help and may hurt.
• You're hoping partitioning will fix a missing index. It won't. Add the index first.
• The table has frequent schema changes. The ALTER overhead across partitions will make your deployments miserable.
• You're on a shared hosting environment or have tight file descriptor limits. Partition files add up fast.

For a couple of clients I've had tables in the 30-80M row range where the right answer was a partial index, a covering index, or archiving old rows to a separate audit_log_archive table with a simple application-level routing rule. Partitioning felt like overkill and introduced complexity I'd have to explain to the next developer.

If your query patterns are mixed — sometimes you filter by date, sometimes you don't, sometimes you're doing aggregations across the whole table — partitioning usually makes some queries faster and others slower. That's a hard trade-off to live with.

The Bottom Line

Partitioning a large audit table is the right call when your access patterns are predominantly date-scoped and you need painless retention enforcement. It's the wrong call when you're treating it as a general performance fix for a table that's just... big.

Run EXPLAIN PARTITIONS before and after. If you don't see partition pruning on your hottest queries, you're adding operational complexity for nothing. The 200M-row healthcare table I mentioned? Quarterly partition drops went from a three-hour batch job to a one-liner. That was worth every minute of setup time.