Partitioning is one of those features where the checkbox comparison lies. Both MySQL 8 and PostgreSQL 16/17 "support partitioning," and if the checkbox were the whole story, this article would be a sentence. In practice the two implementations make very different tradeoffs, and the one that matters most, foreign keys, tends to surprise MySQL teams at exactly the moment partitioning becomes attractive: when a table has grown large enough that deletes hurt.
I have operated partitioned tables on both engines. This is the comparison I wish someone had handed me before the first one, aimed at teams running MySQL today and weighing a move.
What MySQL partitioning gives you
MySQL's partitioning is built into the server and supports RANGE, LIST, HASH, and KEY strategies, plus COLUMNS variants of the first two that avoid needing an integer-returning expression. It works only with InnoDB (and NDB), and the table is a single logical object whose partitions are managed through ALTER TABLE: ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION, and EXCHANGE PARTITION for swapping a partition with a standalone table.
The headline constraint is the unique-key rule: every unique key on a partitioned table, including the primary key, must include all columns of the partitioning expression. This is a direct consequence of the fact that all indexes on a partitioned MySQL table are local to each partition, and uniqueness can only be enforced within one partition. So partitioning a table by created_at means created_at joins the primary key, which changes what "the primary key" means to your application and to anything that syncs the table downstream.
Used within its limits, honest credit, it works. RANGE partitioning by date with periodic DROP PARTITION is a perfectly good retention strategy on MySQL, and dropping a partition is vastly cheaper than the equivalent DELETE.
The foreign key wall
Here is the collision: partitioned InnoDB tables cannot have foreign keys at all. Not as the referencing side, not as the referenced side. The moment you partition a table, every FK touching it must be dropped, and every table referencing it loses its guarantee.
This constraint quietly shapes architecture. The tables that most want partitioning, events, orders, measurements, audit logs, are usually the tables most connected to the rest of the schema. MySQL forces a choice between partitioning and referential integrity, and teams resolve it by either giving up FKs around the big table (feeding the FK-less culture that MySQL is already known for) or by not partitioning and absorbing painful deletes. Both resolutions are costs, and neither shows up in the feature matrix.
PostgreSQL removed this tradeoff in stages: since version 11 a partitioned table can reference other tables, and since version 12 it can be referenced by foreign keys. On PostgreSQL 16/17, foreign keys and partitioning simply compose. PostgreSQL does share one structural rule with MySQL: unique constraints and primary keys on a partitioned table must include the partition key, because its indexes are likewise per-partition. That constraint is inherent to local indexes; the FK prohibition was not.
Declaring partitions in PostgreSQL
PostgreSQL's declarative partitioning (version 10 onward, considerably matured since) supports RANGE, LIST, and HASH. Partitions are real tables attached to a parent, which turns out to be the load-bearing design decision: anything you can do to a table, you can do to a partition, including giving it its own indexes, its own autovacuum settings, or (via attach and detach) its own lifecycle.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
occurred_at timestamptz NOT NULL,
customer_id bigint NOT NULL REFERENCES customers (id),
payload jsonb,
PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;Note the REFERENCES clause sitting inside a partitioned table definition, which is the sentence-long version of the previous section, and the DEFAULT partition catching rows outside every declared range, an option MySQL RANGE partitioning has no equivalent for (out-of-range inserts simply error). Indexes created on the parent propagate to all partitions automatically, including future ones. Sub-partitioning works by declaring a partition itself partitioned. For the full setup, including index strategy and the pg_partman automation most teams end up wanting, see the PostgreSQL partitioning guide.
Pruning: plan time and run time
Pruning, skipping partitions the query cannot need, is the performance point of the exercise, and both engines do it for the straightforward cases: a WHERE clause with constants over the partition key prunes on both. The differences appear off the happy path.
PostgreSQL prunes at plan time for constants, and again at execution time for values not known until then: bound parameters in prepared statements with generic plans, and values arriving from the other side of a join or a subquery. Execution-time pruning matters more than it sounds, because ORMs and connection poolers push workloads toward prepared statements. EXPLAIN makes the behavior inspectable: pruned partitions either vanish from the plan or appear as never-executed subplans.
On MySQL, pruning is a plan-time affair driven by the partitioning expression, and EXPLAIN's partitions column shows what survived. It works well for equality and range predicates on the partition column, with documented limits around expression shapes. On both engines the same operational rule applies: the queries must actually filter on the partition key, or every query fans out to every partition and partitioning has made things slower, not faster. That query-shape audit belongs on the migration review checklist for any partitioned table making the move.
Partition-wise joins and aggregates
PostgreSQL has two planner capabilities with no MySQL counterpart worth the name: partition-wise join, which joins two compatibly partitioned tables partition-by-partition instead of as two giant relations, and partition-wise aggregation, which pushes grouping down to each partition. Both are off by default (enable_partitionwise_join and enable_partitionwise_aggregate) because planning cost grows with partition count, and both can be dramatic wins for large analytical queries over aligned partitioned tables. Parallel query also composes with partitioning, with workers spread across partition scans.
The flip side, stated plainly because vendors will not: thousands of partitions inflate planning time and memory on PostgreSQL, and per-partition metadata is not free on either engine. Partition counts in the tens to low hundreds per table remain the comfortable zone; date-based schemes should pick granularity accordingly.
Maintenance is where the models diverge most
Retention on MySQL is ALTER TABLE ... DROP PARTITION, and archival swaps use EXCHANGE PARTITION. They work, but they are DDL on the shared table object, and the locking is all-or-nothing.
PostgreSQL's partitions-are-tables model pays off here. DETACH PARTITION CONCURRENTLY (version 14+) removes a partition from the parent while other sessions keep querying it, after which the detached table can be archived, dumped, or dropped independently on its own schedule. Loading works in reverse: build and index a standalone table, then ATTACH PARTITION, with the validation scan avoidable by declaring a matching CHECK constraint first.
-- Retire last year's data without blocking readers
ALTER TABLE events DETACH PARTITION events_2025_07 CONCURRENTLY;
-- Attach a pre-built, pre-indexed month without a validation scan
ALTER TABLE events_2026_08_staging
ADD CONSTRAINT staging_range CHECK (
occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01');
ALTER TABLE events ATTACH PARTITION events_2026_08_staging
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');Autovacuum treats each partition as the table it is, so vacuum debt stays local and a bloated old partition never forces a whole-table operation. The one recurring chore is creating future partitions ahead of the data, which is exactly what pg_partman automates; run without it, a missing next-month partition either errors inserts or silently swells the DEFAULT partition, depending on your setup.
How MonPG helps after you land on PostgreSQL
Partitioned tables have their own monitoring surface. A query family that stops pruning after an innocent-looking change fans out across every partition, and its block reads multiply. The DEFAULT partition growing means the partition-creation job has been failing quietly. Per-partition autovacuum, bloat on the busiest current partition, and lock waits during attach and detach windows are all signals with no equivalent on an unpartitioned table.
MonPG covers this on PostgreSQL: query-family history that makes a pruning regression a visible step change, table-level growth and dead-tuple tracking that treats each partition individually, lock and long-transaction visibility for maintenance windows, and autovacuum evidence per partition. It monitors PostgreSQL only, and partitioned fleets are where that focus earns its keep. The PostgreSQL monitoring guide lays out the baseline; pair it with the partitioning guide above and the big table stops being the scary part of the schema.