Table Partitioning: SQL Server vs PostgreSQL
On this page
SQL Server vs PostgreSQL partitioning
You partition for the same reasons in both databases: removing old data instantly, letting queries skip irrelevant ranges, and keeping maintenance on huge tables manageable. The building blocks differ:
| SQL Server | PostgreSQL | |
|---|---|---|
| Define boundaries | CREATE PARTITION FUNCTION | PARTITION BY RANGE (col) on the table |
| Map partitions to storage | CREATE PARTITION SCHEME to filegroups | Not needed. Each partition is its own table. Tablespaces are optional. |
| A partition is | An internal piece of one table | A real table you can query, index and vacuum directly |
| Add a range | ALTER PARTITION FUNCTION ... SPLIT RANGE | CREATE TABLE ... PARTITION OF ... FOR VALUES ... |
| Remove old data fast | SWITCH out to a staging table, then drop it | DETACH PARTITION, then DROP TABLE |
| Strategies | RANGE only (LEFT or RIGHT boundaries) | RANGE, LIST and HASH |
| Rows outside every range | Always go to the first or last partition | Rejected, unless you add a DEFAULT partition |
Two rows deserve attention. There's no partition function or scheme to manage, because each range is declared when you create its partition. And PostgreSQL ranges are explicit: a row that fits no partition is an error, not something silently stored in an edge partition. That's safer, but it means someone must create next month's partition in advance.
The orders table, partitioned by year
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY,
customer_id integer NOT NULL,
order_date date NOT NULL,
total_amount numeric(12,2) NOT NULL,
PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
CREATE INDEX ON orders (customer_id);
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (1, '2025-06-01', 50), (2, '2026-03-15', 75);
SELECT tableoid::regclass AS partition, order_date FROM orders;Three rules to know:
- Ranges include the start and exclude the end.
FROM ('2026-01-01') TO ('2027-01-01')holds all of 2026. It's like SQL Server's RANGE RIGHT, and no boundary value falls between partitions. - The primary key must include the partition key. PostgreSQL checks uniqueness within each partition, so
PRIMARY KEY (order_id)alone is rejected. This is the same rule as aligned unique indexes in SQL Server, but strictly enforced. - Indexes created on the parent are created on every partition, including future ones.
LIST and HASH partitions follow the same pattern: PARTITION BY LIST (region) with FOR VALUES IN ('US', 'CA'), or PARTITION BY HASH (id) with FOR VALUES WITH (MODULUS 4, REMAINDER 0).
Partition pruning
When a query filters on the partition key, the planner skips partitions that can't contain matching rows:
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01';
-- Seq Scan on orders_2026 orders
-- Filter: ((order_date >= '2026-06-01'::date) AND (order_date < '2026-07-01'::date))Only orders_2026 appears. As with indexes (Day 17), wrap the key in a function, like WHERE date_trunc('month', order_date) = ..., and pruning stops working. When the value is a parameter known only at run time, pruning happens during execution and EXPLAIN ANALYZE shows it as Subplans Removed.
Retention: DETACH and DROP instead of SWITCH
SQL Server's sliding window, SWITCH out then drop, becomes:
ALTER TABLE orders DETACH PARTITION orders_2025;
DROP TABLE orders_2025;
CREATE TABLE orders_2027 PARTITION OF orders FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');Both are catalog changes. No rows are deleted one by one, and nothing is left for vacuum. A plain DETACH briefly takes a strong lock on the parent. DETACH PARTITION ... CONCURRENTLY avoids that, but can't be used while a DEFAULT partition exists. Automate creating future partitions with a scheduled job or the pg_partman extension.
One migration tip. If the DEFAULT partition collects rows for a year that later gets its own partition, creating that partition fails until you move those rows out. Monitor the DEFAULT partition and keep it empty.
In DataGrip, partitions show up as tables in the schema, and Explain Plan (Day 15) shows at a glance which partitions a query touched.
Check yourself
PARTITION BY RANGE (order_date) with one partition per month using FROM (first day) TO (first day of next month). The archive job becomes DETACH PARTITION followed by DROP TABLE, or by pg_dump first if the data must be kept.
Include the partition key in the primary key, for example PRIMARY KEY (order_id, order_date). PostgreSQL enforces uniqueness separately inside each partition, so it can only guarantee a key is unique across the table when the key includes the partitioning column. If order_id alone must be globally unique, enforce it another way, such as with a sequence.
Nobody created the new year's partition, and there's no DEFAULT partition. Create partitions ahead of time with a scheduled job or pg_partman, and alert when the next one is missing.
When a query filters on the partition key, PostgreSQL skips partitions that can't contain matching rows, at planning time or, with parameters, at execution time. Wrapping the key in a function, or filtering on a different column, prevents pruning, so every partition is scanned.
ALTER TABLE orders DETACH PARTITION orders_2023 CONCURRENTLY, which turns the partition into a standalone table that you can archive or drop. CONCURRENTLY avoids blocking queries on the parent table for most of the operation.