Table partitioning looks like one feature until you try to move a partitioned schema between database engines — then it turns out to be five unrelated features wearing the same name. PostgreSQL, MySQL, SQL Server, and Oracle all support splitting a large table into smaller physical pieces, but the syntax, the constraints on partition keys, and even what counts as a "partition" diverge enough that a schema diff tool has to treat them as genuinely different objects, not variations on one pattern.
Why partitioning exists in the first place
Partitioning splits one logical table into multiple physical storage units, usually by a range, list, or hash of a column's values. The motivation is almost always one of: keeping indexes small enough to stay in memory, making bulk deletes cheap (drop a partition instead of running a slow DELETE), or letting the query planner skip entire partitions it knows can't match a filter. None of that is dialect-specific — the pain starts once you write the DDL.
PostgreSQL: declarative partitioning, key must be in every unique constraint
PostgreSQL's declarative partitioning (PARTITION BY RANGE, LIST, or HASH) creates a parent table with no storage of its own and child tables attached with PARTITION OF ... FOR VALUES. The catch that trips up most migrations: every unique index and primary key on a partitioned table must include the partition key as one of its columns. A PRIMARY KEY (id) that works fine on an unpartitioned table will be rejected the moment you partition by created_at, unless the key becomes PRIMARY KEY (id, created_at). That's a schema-shape change, not just a storage change, and it ripples into every foreign key that references the table.
MySQL: partitioning is a storage clause, not a separate object
MySQL folds partitioning into the CREATE TABLE statement itself via a trailing PARTITION BY clause, and it enforces a stricter rule than PostgreSQL: every column referenced by the partitioning expression must be part of every unique key on the table, including the primary key — not just present in one of them. MySQL also historically required KEY or HASH partitioning functions to be deterministic, which rules out partitioning by expressions involving timezones or non-immutable functions. Comparing a MySQL partitioned table against a target dialect means checking the partition expression's determinism as much as its syntax.
SQL Server: partition functions and schemes are separate, reusable objects
SQL Server splits the concept in two: a PARTITION FUNCTION defines the boundary values, and a PARTITION SCHEME maps each resulting range onto a filegroup. A table's CREATE TABLE ... ON schemeName(columnName) clause just points at a scheme that already exists. This indirection is powerful — the same function can back multiple schemes across different filegroup layouts — but it means a full schema diff has to track three dependent objects (function, scheme, table) instead of one, and get the drop/create order right when any of them changes.
Oracle: the most flexible syntax, with composite partitioning built in
Oracle supports range, list, hash, and composite partitioning (range-hash, range-list, and so on) natively, plus interval partitioning, which auto-creates new range partitions as data arrives past the last defined boundary — no cron job or maintenance script needed. That convenience is exactly what breaks a migration to a dialect without an equivalent: an Oracle interval-partitioned table has no direct MySQL or SQL Server counterpart, and the honest translation is either a fixed set of pre-created partitions or an application-level job that adds them on a schedule.
What this means for cross-dialect schema comparison
- Partition key differences are structural, not cosmetic — they change which columns must appear in unique constraints.
- Dialects that don't support a source table's partitioning strategy (interval partitioning being the sharpest example) need a documented fallback, not a silent drop.
- SQL Server's function/scheme split means dependency order matters for generated DDL, the same way it does for views and triggers.
- Partition pruning behavior differs enough between engines that a migration which preserves the DDL can still change query performance — worth validating separately from schema correctness.
This is exactly the kind of divergence a schema diff tool needs to flag rather than paper over. FoxSchema's readiness panel separates objects that translate cleanly from ones that need a manual look before you migrate, so a partitioned table doesn't quietly turn into an unpartitioned one on the other side. See how it compares dialect-specific structures like this in the documentation, or read the related guide on the expand/contract pattern for rolling out a repartitioned table without downtime.
Ready to see how your own partitioned tables compare across dialects? Self-host FoxSchema or check the install guide to get started.
