Skip to content
Tutorials Center
My learning

Topics

Lesson 68 of 90 ยท Partitioning and Scaling

Declarative Partitioning

Split big PostgreSQL tables with declarative partitioning. Learn range, list and hash partitions, the default partition, pruning, indexes, ATTACH and DETACH.

A table with a few hundred million rows is still just a table to PostgreSQL, but everything around it gets heavier: indexes no longer fit in memory, VACUUM runs for hours, and deleting last year's data generates a mountain of dead rows. Declarative partitioning lets you keep one logical table in your application while PostgreSQL stores it as many smaller physical tables, called partitions.

The general idea, with a hand-built simulation, is covered in Table Partitioning and Tablespaces. This lesson is the PostgreSQL-specific version: the exact syntax, the rules PostgreSQL enforces, what the planner does for you, and how to add and remove partitions safely. PostgreSQL's partitioning commands only exist on a real server, so the PostgreSQL blocks below are read-only. The runnable examples use plain SQL to simulate the decisions PostgreSQL makes (which partition a row lands in, which partitions a query must read).

One table on top, many tables below

Parent table Holds no rows itself. You query and insert here.
Partitions Ordinary tables that each store a slice of the rows.
Partition key The column (or expression) that decides the slice.
1 / 2

Creating a range-partitioned table

You declare the partitioning method on the parent, then create each partition with PARTITION OF:

SQL
CREATE TABLE sensor_readings (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  device_id   integer     NOT NULL,
  recorded_at timestamptz NOT NULL,
  temperature numeric(5,2),
  PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE sensor_readings_2026_01 PARTITION OF sensor_readings
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE sensor_readings_2026_02 PARTITION OF sensor_readings
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Remember one rule about bounds: FROM is inclusive and TO is exclusive. A row stamped exactly 2026-02-01 00:00:00 belongs to February. Because the end of one partition is the start of the next, there are no gaps and no overlaps, and PostgreSQL refuses to create a partition whose range overlaps an existing one. You can also use the special bounds MINVALUE and MAXVALUE for open-ended ranges.

The key can be several columns or an expression, for example PARTITION BY RANGE (device_id, recorded_at) or PARTITION BY RANGE ((recorded_at::date)). Keep it simple unless you have a measured reason: most queries must filter on the key for partitioning to pay off.

List and hash partitioning

A list partition holds explicit values, which suits categories such as region or tenant type:

SQL
CREATE TABLE customers (
  id      bigint NOT NULL,
  country text   NOT NULL,
  name    text   NOT NULL,
  PRIMARY KEY (id, country)
) PARTITION BY LIST (country);

CREATE TABLE customers_dach  PARTITION OF customers FOR VALUES IN ('DE', 'AT', 'CH');
CREATE TABLE customers_south PARTITION OF customers FOR VALUES IN ('FR', 'ES', 'IT');

A hash partition spreads rows evenly when there is no natural range. You choose a modulus (the number of partitions) and give each partition a remainder:

SQL
CREATE TABLE sessions (
  user_id bigint NOT NULL,
  token   text   NOT NULL,
  PRIMARY KEY (user_id, token)
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash partitioning only prunes for equality conditions (WHERE user_id = 42). Its main benefit is keeping every partition about the same size, not skipping data.

Seeing where rows land

PostgreSQL routes each inserted row by comparing its key against the partition bounds. The next example reproduces that decision with a bounds table. The boundary row dated exactly 2026-02-01 goes to February, and a row outside every range finds no partition.

SQL Routing rows by partition bounds

Press Run (or Ctrl + Enter) to see the result here.

Without a matching partition, a real insert fails with an error saying that no partition of the relation was found for the row. A default partition prevents that failure:

SQL
CREATE TABLE sensor_readings_default PARTITION OF sensor_readings DEFAULT;

Row 5 would now land there. The default partition is a safety net, not a destination. Rows that pile up in it are a sign that your automation forgot to create a partition (see Managing Partitions in Production). It also has a cost: creating a new partition makes PostgreSQL scan the default partition to be sure no existing row belongs to the new range, and that scan holds locks. A table has at most one default partition, and hash partitioning does not have one.

Partition pruning

When a query filters on the partition key, the planner compares the condition with the partition bounds and leaves out partitions that cannot match. This is partition pruning, controlled by enable_partition_pruning (on by default). Pruning works at plan time for constants and also at execution time for parameters and values that only become known while the query runs, such as the inner side of a nested loop.

SQL
EXPLAIN
SELECT avg(temperature)
FROM sensor_readings
WHERE recorded_at >= '2026-02-10' AND recorded_at < '2026-02-20';

The plan shows a scan of only sensor_readings_2026_02 instead of every partition. If the filter does not mention the key, or wraps it in a function the planner cannot reason about, every partition is scanned. See EXPLAIN and EXPLAIN ANALYZE for reading the output.

SQL Which partitions does a query have to read?

Press Run (or Ctrl + Enter) to see the result here.

The first query touches one partition, the second touches two, and a query that ignores the key reads all four. That difference is the whole reason to choose a key that your common filters use.

Indexes, keys and constraints

  • Indexes on the parent are partitioned indexes: CREATE INDEX ON sensor_readings (device_id) creates a matching index on every existing partition and on every partition you add later.
  • Unique and primary keys must include the partition key. Each partition enforces uniqueness only among its own rows, so PostgreSQL cannot promise global uniqueness on columns that do not determine the partition. That is why the examples use PRIMARY KEY (id, recorded_at).
  • Foreign keys can point from a partitioned table to a normal table, and since PostgreSQL 12 normal tables can reference a partitioned table, provided the referenced key includes the partition key.
  • Row movement: an UPDATE that changes the partition key moves the row to the right partition automatically. It works, but it is a delete plus an insert, so avoid designs that update the key often.
  • CREATE INDEX CONCURRENTLY cannot be used on the parent. The usual way around it is CREATE INDEX ... ON ONLY sensor_readings, build each partition's index concurrently, then ALTER INDEX ... ATTACH PARTITION.

For the general behavior of indexes, see How Indexes Work in PostgreSQL.

Attaching and detaching partitions

Partitions are ordinary tables, so you can move them in and out of the parent. This is how you load and retire data without long locks.

SQL
-- Load: prepare a standalone table, then attach it
CREATE TABLE sensor_readings_2026_03
  (LIKE sensor_readings INCLUDING DEFAULTS INCLUDING INDEXES);

-- ... bulk load the data here, for example with COPY ...

ALTER TABLE sensor_readings_2026_03
  ADD CONSTRAINT mar_range CHECK (recorded_at >= '2026-03-01' AND recorded_at < '2026-04-01');

ALTER TABLE sensor_readings ATTACH PARTITION sensor_readings_2026_03
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

ALTER TABLE sensor_readings_2026_03 DROP CONSTRAINT mar_range;

Without the CHECK constraint, ATTACH must scan the whole table to prove every row fits. With a matching constraint it skips the scan. Attaching takes a light lock on the parent (SHARE UPDATE EXCLUSIVE), so normal reads and writes continue.

SQL
-- Retire: detach, archive, then drop
ALTER TABLE sensor_readings DETACH PARTITION sensor_readings_2025_01 CONCURRENTLY;
-- pg_dump the table or move it to cheaper storage here
DROP TABLE sensor_readings_2025_01;

DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14 and later) avoids blocking queries, but it cannot run inside a transaction block and does not work while the parent has a default partition. The plain form takes a stronger lock on the parent, so keep it short. Dropping a partition is a metadata operation. It frees the disk space immediately and leaves no dead rows behind, which is why it beats DELETE for retention. Compare with VACUUM, Autovacuum and Bloat.

When partitioning is the wrong tool

  • The table is small. A few million rows with good indexes rarely needs partitioning. Extra partitions add planning work.
  • Queries do not filter on the key. They will scan every partition, which can be slower than one table with a good index.
  • You need global uniqueness on something unrelated to the key.
  • You hoped it would replace indexes. It does not. Every partition still needs the indexes that your other queries use.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A range partition is declared FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'). Where does a row dated 2026-03-01 go?

    FROM is inclusive and TO is exclusive, so 2026-03-01 belongs to the next range. If no partition covers it, the default partition receives it, otherwise the insert fails.
  2. 2. Why must a primary key on a partitioned table include the partition key?

    There is no global index across partitions. Including the partition key guarantees that two equal values can only ever meet inside the same partition, where the local unique index can catch them.
  3. 3. Which query is most likely to benefit from partition pruning on a table partitioned by recorded_at?

    Only a filter on the partition key lets the planner compare against partition bounds and skip partitions. The other queries have to visit every partition.
  4. 4. What is the main advantage of DETACH and DROP over DELETE for removing a month of old data?

    Dropping a partition removes its files directly, so there is nothing for autovacuum to clean up. DELETE marks each row dead and the space is reclaimed later.

Summary

  • Declarative partitioning stores one logical table as many partitions, chosen by range, list or hash on a partition key.
  • Range bounds are FROM inclusive and TO exclusive; hash uses MODULUS and REMAINDER.
  • A default partition catches rows that fit nowhere, but it should stay empty and it slows down adding partitions.
  • The planner prunes partitions when the query filters on the key; other queries scan everything.
  • Primary and unique keys must include the partition key; indexes on the parent are created on every partition.
  • ATTACH PARTITION (with a matching CHECK) and DETACH PARTITION CONCURRENTLY load and retire data with little locking.

Next: Managing Partitions in Production.