TABLE PARTITIONING SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

Dividing a large table into smaller physical segments (partitions) based on a column value, improving query performance and maintenance operations.

Divides a large table into smaller pieces (partitions) based on a partition key (range, list, or hash). Improves query performance through partition pruning and enables efficient maintenance like archiving old partitions. Essential for tables with billions of rows.

Compatibility

Details

Database System	Support Status	Since Version	Notes
MySQL	✓ Supported	5.1	Supports RANGE, LIST, HASH, and KEY partitioning. All partitions must live in the same schema. Foreign keys referencing partitioned tables are not supported.
PostgreSQL	✓ Supported	10	Declarative partitioning (PARTITION BY RANGE/LIST/HASH) added in PG 10. Earlier versions required trigger-based inheritance partitioning. Each partition is a real child table.
SQL Server	✓ Supported	2005	Requires creating a PARTITION FUNCTION and PARTITION SCHEME before the table. Partitioning is an Enterprise Edition feature (Standard Edition has limited support since 2016 SP1).
Oracle	✓ Supported	8i	One of the most mature partitioning implementations. Supports RANGE, LIST, HASH, COMPOSITE (range-hash, range-list), INTERVAL, and REFERENCE partitioning. Requires the Partitioning option.
SQLite	✗ Not Supported	—	No native table partitioning. The common workaround is to use separate tables per partition and combine them with views or UNION ALL queries.

Partitioning is a mature feature in Oracle and SQL Server, reasonably capable in MySQL and PostgreSQL, and absent in SQLite. Key differences: Oracle's INTERVAL partitioning auto-creates new partitions as data arrives — the other engines require explicit partition management. SQL Server's two-step PARTITION FUNCTION + SCHEME approach is verbose but separates the partitioning logic from the storage layout. PostgreSQL's declarative partitioning since version 10 is clean but foreign keys referencing the parent partitioned table were only fully supported from PG 12. Partition pruning — the optimizer skipping irrelevant partitions — requires that the partition key appear in the WHERE clause; always verify with EXPLAIN. Common pitfalls: MySQL's restriction on foreign keys on partitioned tables, and the fact that a `SELECT *` on the parent in PostgreSQL returns rows from all partitions transparently.

Standard Syntax

Version Support

Per-Database Syntax & Notes

MySQL

Supports RANGE, LIST, HASH, and KEY partitioning. All partitions must live in the same schema. Foreign keys referencing partitioned tables are not supported.

CREATE TABLE orders ( id INT, order_date DATE ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION pmax VALUES LESS THAN MAXVALUE );

PostgreSQL

Declarative partitioning (PARTITION BY RANGE/LIST/HASH) added in PG 10. Earlier versions required trigger-based inheritance partitioning. Each partition is a real child table.

CREATE TABLE orders ( id INT, order_date DATE ) PARTITION BY RANGE (order_date); CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

SQL Server

Requires creating a PARTITION FUNCTION and PARTITION SCHEME before the table. Partitioning is an Enterprise Edition feature (Standard Edition has limited support since 2016 SP1).

CREATE PARTITION FUNCTION pf_date (DATE) AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01'); CREATE PARTITION SCHEME ps_date AS PARTITION pf_date ALL TO ([PRIMARY]); CREATE TABLE orders ( id INT, order_date DATE ) ON ps_date(order_date);

Oracle

One of the most mature partitioning implementations. Supports RANGE, LIST, HASH, COMPOSITE (range-hash, range-list), INTERVAL, and REFERENCE partitioning. Requires the Partitioning option.

CREATE TABLE orders ( id NUMBER, order_date DATE ) PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) ( PARTITION p_before_2023 VALUES LESS THAN (DATE '2023-01-01') );