Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 3 · Lesson 19 · 4 min read

Table Partitioning: SQL Server vs PostgreSQL

On this page
  1. SQL Server vs PostgreSQL partitioning
  2. The orders table, partitioned by year
  3. Partition pruning
  4. Retention: DETACH and DROP instead of SWITCH
  5. Check yourself

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 ServerPostgreSQL
Define boundariesCREATE PARTITION FUNCTIONPARTITION BY RANGE (col) on the table
Map partitions to storageCREATE PARTITION SCHEME to filegroupsNot needed. Each partition is its own table. Tablespaces are optional.
A partition isAn internal piece of one tableA real table you can query, index and vacuum directly
Add a rangeALTER PARTITION FUNCTION ... SPLIT RANGECREATE TABLE ... PARTITION OF ... FOR VALUES ...
Remove old data fastSWITCH out to a staging table, then drop itDETACH PARTITION, then DROP TABLE
StrategiesRANGE only (LEFT or RIGHT boundaries)RANGE, LIST and HASH
Rows outside every rangeAlways go to the first or last partitionRejected, 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

SQL
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:

SQL
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:

SQL
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.