Partitioning

Range, list and hash partitions, partition pruning, dropping old data, and the limits.

Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.

Before thisScan types
On this page
  1. Range, list and hash
  2. Partition pruning
  3. Hash partitioning
  4. Removing old data
  5. The limits
  6. In MySQL
  7. Sources

Partitioning splits one large table into several smaller tables, its partitions, that the database still presents as one. Queries that only need some partitions skip the rest, and old data can be removed by dropping a whole partition instead of deleting millions of rows. It is not a way to spread load across servers; that is sharding.

Range, list and hash#

  • Range partitions by intervals of a column, usually time: one partition per month or per day. The sample events table is partitioned by month on at. This is by far the most common kind.
  • List partitions by explicit values: one partition per country or per tenant tier.
  • Hash spreads rows evenly by a hash of the key, for when no natural range exists but you still want smaller pieces.
sql
select relname as partition, pg_get_expr(relpartbound, oid) as holds
from pg_class
where relispartition and relname like 'events_2024_0%'
order by relname
limit 3;
Result 3 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
partitionholds
events_2024_01FOR VALUES FROM ('2024-01-01 00:00:00+00') TO ('2024-02-01 00:00:00+00')
events_2024_02FOR VALUES FROM ('2024-02-01 00:00:00+00') TO ('2024-03-01 00:00:00+00')
events_2024_03FOR VALUES FROM ('2024-03-01 00:00:00+00') TO ('2024-04-01 00:00:00+00')

Partition pruning#

Pruning is where most of the benefit comes from. When a condition compares the partition key with values, the planner checks each partition's bounds and leaves out the ones that cannot match:

sql
select count(*) from events where at >= '2024-11-01' and at < '2024-12-01';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
Aggregate  (cost=123.40..123.41 rows=1 width=8) (actual time=2.800..2.802 rows=1.00 loops=1)
  Buffers: shared hit=37
  ->  Seq Scan on events_2024_11 events  (cost=0.00..111.05 rows=4937 width=0) (actual time=0.010..1.625 rows=4937.00 loops=1)
        Filter: ((at >= '2024-11-01 00:00:00+00'::timestamp with time zone) AND (at < '2024-12-01 00:00:00+00'::timestamp with time zone))
        Buffers: shared hit=37
Planning:
  Buffers: shared hit=23
Planning Time: 0.255 ms
Execution Time: 2.828 ms

The plan reads one partition. The same question asked through a function of the key cannot be pruned, so every partition is read:

sql
select count(*) from events where date_trunc('month', at) = '2024-11-01';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
Aggregate  (cost=1350.28..1350.29 rows=1 width=8) (actual time=18.341..18.350 rows=1.00 loops=1)
  Buffers: shared hit=448
  ->  Append  (cost=0.00..1349.52 rows=304 width=0) (actual time=11.275..17.141 rows=4937.00 loops=1)
        Buffers: shared hit=448
        ->  Seq Scan on events_2024_01 events_1  (cost=0.00..114.52 rows=26 width=0) (actual time=1.218..1.218 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 5101
              Buffers: shared hit=38
        ->  Seq Scan on events_2024_02 events_2  (cost=0.00..107.59 rows=24 width=0) (actual time=1.079..1.080 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 4773
              Buffers: shared hit=36
        ->  Seq Scan on events_2024_03 events_3  (cost=0.00..114.52 rows=26 width=0) (actual time=1.135..1.135 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 5101
              Buffers: shared hit=38
        ->  Seq Scan on events_2024_04 events_4  (cost=0.00..111.07 rows=25 width=0) (actual time=1.098..1.099 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 4938
              Buffers: shared hit=37
        ->  Seq Scan on events_2024_05 events_5  (cost=0.00..114.52 rows=26 width=0) (actual time=1.135..1.135 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 5101
              Buffers: shared hit=38
        ->  Seq Scan on events_2024_06 events_6  (cost=0.00..111.05 rows=25 width=0) (actual time=1.086..1.087 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 4937
              Buffers: shared hit=37
        ->  Seq Scan on events_2024_07 events_7  (cost=0.00..114.53 rows=26 width=0) (actual time=1.132..1.132 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 5102
              Buffers: shared hit=38
        ->  Seq Scan on events_2024_08 events_8  (cost=0.00..114.53 rows=26 width=0) (actual time=1.132..1.133 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 5102
              Buffers: shared hit=38
        ->  Seq Scan on events_2024_09 events_9  (cost=0.00..111.05 rows=25 width=0) (actual time=1.110..1.110 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 4937
              Buffers: shared hit=37
        ->  Seq Scan on events_2024_10 events_10  (cost=0.00..114.53 rows=26 width=0) (actual time=1.135..1.135 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 5102
              Buffers: shared hit=38
        ->  Seq Scan on events_2024_11 events_11  (cost=0.00..111.05 rows=25 width=0) (actual time=0.005..2.630 rows=4937.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Buffers: shared hit=37
        ->  Seq Scan on events_2024_12 events_12  (cost=0.00..109.03 rows=24 width=0) (actual time=1.111..1.112 rows=0.00 loops=1)
              Filter: (date_trunc('month'::text, at) = '2024-11-01 00:00:00+00'::timestamp with time zone)
              Rows Removed by Filter: 4869
              Buffers: shared hit=36
Planning Time: 0.216 ms
Execution Time: 18.411 ms

The one month of events exercise asks you to fix exactly this. Pruning also happens while a query runs, for values that are only known then, such as parameters of a prepared statement; the plan then reports how many partitions it skipped.

Hash partitioning#

sql
create table sessions (id bigint not null, user_id int not null, started_at timestamptz not null) partition by hash (user_id);
create table sessions_0 partition of sessions for values with (modulus 4, remainder 0);
create table sessions_1 partition of sessions for values with (modulus 4, remainder 1);
create table sessions_2 partition of sessions for values with (modulus 4, remainder 2);
create table sessions_3 partition of sessions for values with (modulus 4, remainder 3);
insert into sessions select g, 1 + abs(hashint4(g)) % 20000, timestamptz '2024-06-01' from generate_series(1, 40000) g;
sql
select tableoid::regclass as partition, count(*) as rows from sessions group by 1 order by 1;
Result 4 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
partitionrows
sessions_09834
sessions_110188
sessions_210200
sessions_39778
sql
select * from sessions where user_id = 42;
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
Seq Scan on sessions_2 sessions  (cost=0.00..203.12 rows=55 width=20)
  Filter: (user_id = 42)

The rows are spread almost evenly, and a query for one user reads one partition. A query for a range of users gets no pruning at all: hash partitions have no order.

Removing old data#

With time-range partitions, deleting a month is a metadata change instead of a huge DELETE that writes WAL for every row and leaves the table full of dead rows:

sql
alter table events detach partition events_2024_01;
sql
select count(*) as events_still_visible from events;
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
events_still_visible
54899

DETACH PARTITION turns January into an ordinary table (archive it, or DROP it); DETACH … CONCURRENTLY does it without blocking queries for long. New partitions for coming months are typically created ahead of time by a scheduled job.

The limits#

  • Unique constraints and primary keys must include the partition key, because each partition enforces uniqueness only within itself. There are no global indexes across partitions.
  • Too many partitions slows planning. Hundreds are fine; tens of thousands are not. Partition by month or day, not by hour, unless you have measured.
  • Queries that do not filter on the partition key read every partition, and can be slower than on one big table.
  • It is still one server. Partitioning makes a big table manageable; it does not add CPU, memory or disks.

In MySQL#

Not run here; from the MySQL reference manual. MySQL supports range, list, hash and key partitioning, with pruning on the partitioning key. As in PostgreSQL, every unique key must include all partitioning columns, and dropping a partition is the fast way to remove old data.

Sources#

Esc
↑ ↓ to move↵ to open