Partitioning in PostgreSQL: Range, List, Hash, Partition Pruning and Archiving with Attach/Detach

Version note: The examples in this post were run with PostgreSQL 18.7, .NET 10 and Npgsql 10.0.3 in a single-machine lab. Timings belong to this lab; what matters in the comparisons is the number of buffers read and the number of partitions in the plan.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, decided to split four years of order data into months. We split a copy of the orders table into monthly partitions on the created_at column: orders_p, 50 partitions, the same 1 million orders, the same (customer_id, created_at DESC) index in every partition. The "My orders" query we have followed since post 01:

SELECT order_id, status, total_amount, created_at FROM orders_p
WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20;

Guess: is this query faster or slower on the partitioned table than on the unpartitioned one? We ran the same query on both tables three times each with EXPLAIN (ANALYZE, BUFFERS); the values were identical every time. The first line is the unpartitioned orders, the second orders_p; the first Buffers is the pages read by the query itself, the second by planning:

 Limit (actual rows=10.00 loops=1)    Buffers: shared hit=16    Buffers: shared hit=125  Planning Time: 0.176 ms  Execution Time: 0.061 ms
 Limit (actual rows=10.00 loops=1)    Buffers: shared hit=109    Buffers: shared hit=3627  Planning Time: 2.832 ms  Execution Time: 0.321 ms

The partitioned table read about 7 times as many buffers and took 16 times as long to plan. The query doesn't filter on the partition key (created_at), so PostgreSQL looked into all 50 indexes of all 50 partitions. Partitioning is not a tool for speeding up queries; its real job is managing the data's life cycle. In this post we set up PostgreSQL's declarative partitioning, measure when pruning works, and do the archiving that took batches in post 03 in milliseconds, by detaching old months' partitions from the table.

When Does Partitioning Help?

Partitioning splits a big table into smaller tables that look like one table from the outside. The gains are in three places: detaching or dropping a whole partition instead of deleting old data (no log volume, no lock waits, no bloat left behind); doing maintenance work (VACUUM, index rebuilds) in small pieces; and reading only the relevant partitions when a query filters on the partition key (partition pruning). The third is often the expected gain, but it is the least guaranteed: a good index already does the same job.

The cases where it's unnecessary are clear too. If the table is under a few GB and grows slowly, partitioning's complexity outweighs its gain. If most queries don't use the partition key, every query spreads over all partitions, as we saw above. Bookstore's case favours partitioning: the orders table grows every month (we estimated 3.1 GB a year in post 01, with 10x growth), most reports are per month, and orders older than 3 years go to the archive.

Declarative Partitioning: Range, List and Hash

In PostgreSQL a partitioned table is a parent that holds no data itself; rows live in child tables defined with PARTITION OF. The first attempt ran into a restriction:

CREATE TABLE orders_p (
    order_id     bigint GENERATED BY DEFAULT AS IDENTITY,
    customer_id  int NOT NULL,
    status       text NOT NULL,
    total_amount numeric(12,2) NOT NULL,
    currency     char(3) NOT NULL DEFAULT 'TRY',
    created_at   timestamptz NOT NULL,
    PRIMARY KEY (order_id)
) PARTITION BY RANGE (created_at);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "orders_p" lacks column "created_at" which is part of the partition key.

Each partition's index can only guarantee uniqueness within that partition; PostgreSQL doesn't keep a global unique index. So primary keys and unique constraints must include the partition key. The order's primary key became (order_id, created_at). That has a side effect: order_id alone is no longer guaranteed unique by the database; the identity column produces unique values in practice, but nothing stops the application from writing an id by hand. Order items were split on the same month boundaries, the created_at column was copied into the items, and the foreign key got two columns:

CREATE TABLE order_items_p (
    order_id   bigint NOT NULL,
    line_no    smallint NOT NULL,
    created_at timestamptz NOT NULL,
    book_id    int NOT NULL REFERENCES books,
    quantity   int NOT NULL,
    unit_price numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, line_no, created_at),
    FOREIGN KEY (order_id, created_at) REFERENCES orders_p
) PARTITION BY RANGE (created_at);

CREATE INDEX ix_orders_p_customer_created ON orders_p (customer_id, created_at DESC);

An index defined on the parent is created automatically on every partition, including partitions added later. A function opens the months; it skips months that already exist, so the same function can safely be called every day:

CREATE FUNCTION create_month_partitions(p_from date, p_to date)
RETURNS int
LANGUAGE plpgsql AS $$
DECLARE
    m date;
    created int := 0;
BEGIN
    FOR m IN SELECT generate_series(date_trunc('month', p_from), p_to, interval '1 month')::date LOOP
        CONTINUE WHEN to_regclass('orders_' || to_char(m, 'YYYY_MM')) IS NOT NULL;
        EXECUTE format('CREATE TABLE %I PARTITION OF orders_p FOR VALUES FROM (%L) TO (%L)',
                       'orders_' || to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
        EXECUTE format('CREATE TABLE %I PARTITION OF order_items_p FOR VALUES FROM (%L) TO (%L)',
                       'order_items_' || to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
        created := created + 1;
    END LOOP;
    RETURN created;
END
$$;

SELECT create_month_partitions('2022-11-01', '2026-12-01');

The lower bound of a range is inclusive and the upper bound exclusive; the partition between 2026-10-01 and 2026-11-01 holds all of October, and the milliseconds at the end of a month never get lost between two partitions. 50 partitions were opened, and 1 million orders and 2.5 million items were moved with INSERT ... SELECT. The other two kinds briefly: list partitioning is for discrete values (country, region, tenant), and hash partitioning splits a key with no natural range into equal parts:

CREATE TABLE customers_by_country (
    customer_id int NOT NULL,
    country     char(2) NOT NULL,
    email       text NOT NULL,
    PRIMARY KEY (customer_id, country)
) PARTITION BY LIST (country);
CREATE TABLE customers_tr PARTITION OF customers_by_country FOR VALUES IN ('TR');
CREATE TABLE customers_eu PARTITION OF customers_by_country FOR VALUES IN ('DE', 'NL');
CREATE TABLE customers_other PARTITION OF customers_by_country DEFAULT;

CREATE TABLE cart_items (
    cart_id  uuid NOT NULL,
    book_id  int NOT NULL,
    quantity int NOT NULL,
    PRIMARY KEY (cart_id, book_id)
) PARTITION BY HASH (cart_id);
CREATE TABLE cart_items_0 PARTITION OF cart_items FOR VALUES WITH (MODULUS 4, REMAINDER 0);

    partition    | count
-----------------+-------
 customers_tr    | 50000
 customers_eu    | 33334
 customers_other | 16666

Hash partitioning doesn't help archiving, because old data is spread over every partition; it's used to split the maintenance of a big table or to prepare for the sharding we'll see in post 09. For Bookstore's orders table the right choice is range by date.

Partition Pruning: Plan Time and Execution Time

Pruning means working out from a query's filter which partitions aren't needed and never reading them. October's revenue:

SELECT count(*), sum(total_amount) FROM orders_p
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=352
   ->  Seq Scan on orders_2026_10 orders_p (actual rows=42233.00 loops=1)
         Filter: ((created_at >= '2026-10-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-11-01 00:00:00+00'::timestamp with time zone))
         Buffers: shared hit=352
 Planning Time: 0.922 ms
 Execution Time: 3.075 ms

There's a single partition in the plan; the planner eliminated the other 49 during planning. The same query on the unpartitioned orders table read 16,667 buffers with a parallel seq scan (29.9 ms). With an index on created_at it dropped to 7,514 buffers (18.2 ms): October's 42 thousand rows were spread over 7,396 different pages of the table, because the rows had been updated and rewritten after loading. In production every operation that updates an order's status has the same effect. A partition keeps a month's rows physically together.

If the value isn't known at plan time, pruning happens during execution. A prepared statement's generic plan and a filter using now():

SET plan_cache_mode = force_generic_plan;
PREPARE month_revenue(timestamptz, timestamptz) AS
SELECT count(*), sum(total_amount) FROM orders_p WHERE created_at >= $1 AND created_at < $2;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE month_revenue('2026-10-01', '2026-11-01');
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders_p WHERE created_at >= now() - interval '7 days';

               ->  Parallel Append (actual rows=14077.67 loops=3)
                     Buffers: shared hit=352
                     Subplans Removed: 49
                     ->  Parallel Seq Scan on orders_2026_10 orders_p_1 (actual rows=14077.67 loops=3)
...
               ->  Parallel Append
                     Subplans Removed: 48
                     ->  Parallel Seq Scan on orders_2026_11 orders_p_1
                     ->  Parallel Seq Scan on orders_2026_12 orders_p_2

Subplans Removed is the number of partitions eliminated at the start of execution. Since Npgsql's prepared statements and PgBouncer's max_prepared_statements (post 02) can lead to generic plans, it's worth recognising this line: pruning still works in a generic plan, but only if the filter applies directly to the partition key. Filters that wrap the column in a function, such as created_at::date = $1, prevent pruning.

Queries That Don't Use the Partition Key, and Partition Counts

Back to the "My orders" query. The plan had 50 index scans (shortened):

 Limit (actual rows=10.00 loops=1)
   Buffers: shared hit=7 read=102
   ->  Append (actual rows=10.00 loops=1)
         ->  Index Scan using orders_2026_12_customer_id_created_at_idx on orders_2026_12 orders_p_50 (actual rows=0.00 loops=1)
               Index Cond: (customer_id = 4242)
               Buffers: shared hit=2
         ->  Index Scan using orders_2026_11_customer_id_created_at_idx on orders_2026_11 orders_p_49 (actual rows=0.00 loops=1)
               Index Cond: (customer_id = 4242)
               Buffers: shared read=2
...
         ->  Index Scan using orders_2022_11_customer_id_created_at_idx on orders_2022_11 orders_p_1 (actual rows=0.00 loops=1)
               Index Cond: (customer_id = 4242)
               Buffers: shared read=1

The customer has 10 orders; since 20 rows were never reached, Limit couldn't stop early and every one of the 50 partitions was walked from the index root to a leaf. Queries like this grow linearly with the number of partitions. To see the cost of overdoing the partition count, we split the same data into daily partitions (1,522 partitions):

orders:  Buffers: shared hit=16  Buffers: shared hit=130  Planning Time: 0.218 ms  Execution Time: 0.038 ms
orders_p:  Buffers: shared hit=95  Buffers: shared hit=2957  Planning Time: 2.138 ms  Execution Time: 0.294 ms
orders_daily:  Buffers: shared hit=2783  Buffers: shared hit=78275  Planning Time: 49.300 ms  Execution Time: 10.535 ms

In this measurement orders_p had 40 partitions (after the archiving below). With 1,522 partitions planning alone takes 49 ms; the 50 ms p99 target of the SLO from post 01 is used up by this one query's planning. The PostgreSQL documentation says a few thousand partitions work reasonably well if most queries can prune down to a few partitions, and that planning time and memory grow when many partitions remain after pruning. Each session also loads the metadata of the partitions it touches into its own memory. A practical rule: choose the partition size by your archiving and maintenance unit (a month for Bookstore), make sure most queries filter on the partition key, and leave the rest to indexes. For "My orders" the fix is to add a reasonable lower date bound to the query (say, the last 2 years) or to design this screen with few enough partitions that their number doesn't matter.

Archiving with Detach: One Command Instead of Post 03's Batch Job

In post 03 we moved orders older than 3 years from the live table to the archive with keyset batches. On a partitioned table the same job means detaching the old month's partition from the parent and moving it to another schema. No data is copied or deleted; only the catalog changes, and only that change is written to the WAL. On the first try the foreign key got in the way:

ALTER TABLE orders_p DETACH PARTITION orders_2022_11 CONCURRENTLY;

ERROR:  removing partition "orders_2022_11" violates foreign key constraint "order_items_p_order_id_created_at_fkey_1"
DETAIL:  Key (order_id, created_at)=(1, 2022-11-15 10:33:36+00) is still referenced from table "order_items_p".

The referencing side, the items' partition, has to be detached first. The detached order_items_2022_11, however, keeps its foreign key, which still points at orders_p; until that constraint is dropped, the orders' partition still can't be detached. The order is: detach the items, drop the items' foreign key, detach the orders.

We measured why CONCURRENTLY matters. Session A runs an 8-second report on orders_p; at second 1 B starts the detach, and at second 2 C runs a customer's "My orders" query:

== plain
A long report: Time: 8007.962 ms (00:08.008)
B detach: Time: 6975.415 ms (00:06.975)
C my orders: Time: 5963.788 ms (00:05.964)

== concurrently
C my orders: Time: 3.156 ms
waiting: ALTER TABLE orders_p DETACH PARTITION orders_2022_12 CONCURR / Lock:virtualxid
A long report: Time: 8021.425 ms (00:08.021)
B detach: Time: 7008.036 ms (00:07.008)

A plain DETACH PARTITION needs an ACCESS EXCLUSIVE lock on the parent. It waits because of A's lock, and while waiting it sits at the head of the lock queue, so even a simple read that comes after it (C) waits 6 seconds. CONCURRENTLY takes only SHARE UPDATE EXCLUSIVE on the parent and runs in two transactions: first it makes the partition invisible to new queries, then it waits for the older transactions that might see that partition to finish. C finished in 3 ms; B waited until A was done, but kept nobody waiting. The cost: CONCURRENTLY doesn't run inside a transaction block and doesn't run at all if the table has a default partition (we'll see both shortly).

We put these steps into a job that runs every day. The job first opens the partitions for the next 3 months, then moves months older than 36 months to the archive schema:

async Task ArchiveMonthAsync(DateOnly month, CancellationToken ct)
{
    string m = month.ToString("yyyy_MM", CultureInfo.InvariantCulture);
    Stopwatch sw = Stopwatch.StartNew();
    // order_items references orders: detach the child side first and drop its foreign key
    await ExecuteAsync($"ALTER TABLE order_items_p DETACH PARTITION order_items_{m} CONCURRENTLY", ct);
    await ExecuteAsync($"ALTER TABLE order_items_{m} DROP CONSTRAINT order_items_p_order_id_created_at_fkey", ct);
    await ExecuteAsync($"ALTER TABLE orders_p DETACH PARTITION orders_{m} CONCURRENTLY", ct);
    await ExecuteAsync($"ALTER TABLE orders_{m} SET SCHEMA archive", ct);
    await ExecuteAsync($"ALTER TABLE order_items_{m} SET SCHEMA archive", ct);
    long rows = await ScalarAsync<long>($"SELECT count(*) FROM archive.orders_{m}", ct);
    Console.WriteLine($"archived {month:yyyy-MM}: {rows} orders in {sw.ElapsedMilliseconds} ms");
}

The month names go into the SQL as strings because they are generated in code; they are not outside input but formatted from a DateOnly. We started the job while an 8-client pgbench load was running (each transaction is one "My orders" read and one new order):

created 2 new monthly partitions up to 2027-02
archived 2023-01: 1823 orders in 34 ms
archived 2023-02: 2382 orders in 22 ms
archived 2023-03: 3868 orders in 18 ms
archived 2023-04: 4265 orders in 21 ms
archived 2023-05: 5457 orders in 30 ms
archived 2023-06: 5944 orders in 20 ms
archived 2023-07: 7250 orders in 17 ms
archived 2023-08: 7899 orders in 17 ms
archived 2023-09: 8462 orders in 19 ms
archived 2023-10: 9941 orders in 15 ms

progress: 4.0 s, 7745.8 tps, lat 1.029 ms stddev 0.648, 0 failed
progress: 8.0 s, 8413.1 tps, lat 0.948 ms stddev 0.717, 0 failed
progress: 12.0 s, 9780.0 tps, lat 0.815 ms stddev 0.188, 0 failed
progress: 16.0 s, 9820.1 tps, lat 0.812 ms stddev 0.185, 0 failed
number of failed transactions: 0 (0.000%)

We had detached November and December 2022 by hand in the experiment above; the job moved the remaining ten months, each in under 40 ms; in the 4–8 second window where the job ran, throughput dipped slightly and no transaction failed. The row-by-row copying, WAL and replica lag of post 03's batch job are absent here. The tables in the archive are ordinary tables: they can be dumped with pg_dump and dropped, moved to a separate database, or read in place for an "old orders" screen.

The Default Partition and Opening New Months Ahead

A row arriving with a date that has no partition fails. The first idea for preventing that is a default partition, but it has two costs:

CREATE TABLE orders_default PARTITION OF orders_p DEFAULT;
ALTER TABLE orders_p DETACH PARTITION orders_2027_02 CONCURRENTLY;
INSERT INTO orders_p (customer_id, status, total_amount, created_at) VALUES (4242, 'Pending', 99.90, '2027-03-02');
SELECT create_month_partitions('2027-03-01', '2027-03-01');

ERROR:  cannot detach partitions concurrently when a default partition exists
INSERT 0 1
ERROR:  updated partition constraint for default partition "orders_default" would be violated by some row
CONTEXT:  SQL statement "CREATE TABLE orders_2027_03 PARTITION OF orders_p FOR VALUES FROM ('2027-03-01') TO ('2027-04-01')"
PL/pgSQL function create_month_partitions(date,date) line 8 at EXECUTE

With a default partition, CONCURRENTLY can't be used, so the archive job has to lock the parent. A row that lands in the default partition also blocks opening that month's partition; the job doesn't run until the row is moved by hand. In addition, whenever a new partition is added, PostgreSQL scans the default partition to check that none of its rows belong to the new range. Bookstore doesn't use a default partition; instead the job opens the next 3 months ahead, and there's an alert when "the furthest partition is less than 2 months away". That way writes don't stop even if the job fails to run for a few days.

There are ready-made tools for this automation too: the pg_partman extension manages opening partitions, retention and archiving, and pg_cron schedules the maintenance function from inside the database. We didn't install either in the lab. On managed services, check which extensions are supported. EF Core doesn't know about partitioned tables in its model; you create the table and the function in a migration with migrationBuilder.Sql, and in EF queries orders_p looks like an ordinary table. Don't forget to declare the composite primary key on the entity with HasKey(o => new { o.OrderId, o.CreatedAt }); otherwise the key EF knows and the key in the database diverge.

VACUUM, ANALYZE and PostgreSQL 18

Autovacuum doesn't process a partitioned parent, only its children. The parent holds no data, so it needs no VACUUM, but the planner uses the parent's statistics for queries that span the whole table, and only a manually run ANALYZE produces those statistics:

    relname     | n_mod_since_analyze | manual | auto
----------------+---------------------+--------+------
 orders_2026_11 |                   0 | t      | t
 orders_p       |                   0 | t      | f

After the 182 thousand orders pgbench added, autovacuum analysed the month's partition but never the parent. PostgreSQL 18 brings two changes: VACUUM and ANALYZE given a parent now process its children by default, and the new ONLY option processes only the parent:

ANALYZE ONLY orders_p;
ANALYZE orders_p;

ANALYZE
Time: 43.883 ms
ANALYZE
Time: 590.088 ms

Refreshing the parent's statistics doesn't require analysing the children again; adding a daily ANALYZE ONLY orders_p to the maintenance job is enough.

In Production

Watch for half-finished detaches. DETACH ... CONCURRENTLY runs in two transactions, so if the job is killed between them the partition stays in a "detach pending" state. According to the PostgreSQL documentation, ALTER TABLE ... DETACH PARTITION ... FINALIZE completes the work in that case. The job should check for partitions in this state at the start of every run (pg_inherits.inhdetachpending).

Long transactions hold up the detach. In the lab an 8-second report held the detach for 7 seconds; a transaction left open for hours or a forgotten idle in transaction session holds up the job for hours. Give the job a lock_timeout and let it try again the next day when it times out.

Bring the partition key into the queries. If most reports and lists include a date range, partitioning's benefit carries over to queries too. Queries such as "best-selling books of all time" go to every partition; moving them to a summary table or a reporting database is better.

The replica has the same tables. Detach and SET SCHEMA reach the replica through the WAL as catalog changes and are small; the 22 MB lag of post 03's bulk delete doesn't happen here. But the older transactions CONCURRENTLY waits for don't include queries on the replica; a report on the replica reading an old partition can be cancelled by the hot standby conflict from post 06.

Common Pitfalls

Partitioning for performance: the "My orders" query read 7 times as many buffers on the partitioned table. Index and query first, partitioning after.

Partitions that are too small: with daily partitions (1,522 of them) planning alone took 49 ms for the same query. Choose the partition by your archiving and maintenance unit.

Wrapping the partition key in a function: WHERE created_at::date = '2026-10-15' or date_trunc('month', created_at) = ... turns pruning off; write it as a range (created_at >= ... AND created_at < ...).

Treating the default partition as a safety net: it blocks CONCURRENTLY detaches, and one row with a wrong date stops the new month from being opened. Partitions opened ahead and an alert are safer.

Forgetting foreign keys: when the referencing table's partition is detached, the foreign key stays on the detached table and blocks detaching the parent's partition. Encode the order in the job.

A Small Experiment

★☆☆ Add AND created_at >= now() - interval '2 years' to the "My orders" query. What happens to the number of partitions in the plan and the buffers read?

★★☆ Add a second level that splits orders_p into 8 parts by HASH on customer_id (sub-partitioning: each month's partition is itself split by hash). Compare the plans of the "My orders" and monthly revenue queries with the single-level table.

★★★ Add lock_timeout = '5s' to the archive job and kill the job in the middle of a detach. Find the partition's inhdetachpending state, write a recovery step that completes it with FINALIZE, and verify by running the job again.

In the next post we do the same job on SQL Server: partition functions and schemes, the difference between RANGE LEFT and RANGE RIGHT, aligned indexes, partition elimination in the execution plan, and a sliding window that moves the oldest month to the archive instantly with SWITCH.