Improve Performance with Range Partitioning in PostgreSQL

A table that has collected rows for years answers a question about last month by reading all of those years. Range partitioning fixes that in PostgreSQL: you declare the table PARTITION BY RANGE on a date column and give it one child table per period. PostgreSQL stores each row in the child whose range covers its date, and a query that filters on that date reads only the children that can hold a match.

CREATE TABLE company.transactions (
  transaction_id BIGSERIAL,
  order_id BIGINT,
  transaction_date DATE NOT NULL,
  amount NUMERIC(10,2)
) PARTITION BY RANGE (transaction_date);

CREATE TABLE company.transactions_before_2025
PARTITION OF company.transactions
FOR VALUES FROM (MINVALUE) TO ('2025-01-01');

CREATE TABLE company.transactions_2025
PARTITION OF company.transactions
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

The parent, company.transactions, stores no rows itself. You insert into it and query it as before, and the two partitions hold the data. Every statement on this page ran on PostgreSQL 18.6, in this order, and each result is copied from that run.

Where each row goes

Each partition covers an interval of transaction_date, and the intervals may not overlap. The lower bound after FROM is included and the upper bound after TO is excluded, as CREATE TABLE defines them. So 2025-01-01 belongs to transactions_2025, not to the partition that ends there, and two neighbors can share a bound without a gap or an overlap. MINVALUE means no lower bound at all; MAXVALUE does the same for the upper end.

PostgreSQL keeps each partition's bounds in the catalog, and pg_get_expr prints them back:

SELECT c.relname AS partition_name,
       pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'company.transactions'::regclass
ORDER BY 1;

Inserts name the parent, exactly as they would for an ordinary table. The system column tableoid then tells you which partition each row landed in:

INSERT INTO company.transactions (order_id, transaction_date, amount) VALUES
(201, '2024-12-31', 450.00),
(202, '2025-01-01', 320.50),
(203, '2025-02-05', 125.00);

SELECT tableoid::regclass AS partition, transaction_id, order_id, transaction_date, amount
FROM company.transactions
ORDER BY transaction_id;

The transaction_id values run 1, 2, 3 across both partitions because they come from one sequence that belongs to the parent. A row that no partition covers is refused:

INSERT INTO company.transactions (order_id, transaction_date, amount)
VALUES (204, '2026-03-10', 80.00);

ERROR:  no partition of relation "transactions" found for row
DETAIL:  Partition key of the failing row contains (transaction_date) = (2026-03-10).

Creating the 2026 partition before the first 2026 row arrives avoids this error, and a default partition, covered further down, catches such rows at a price. An UPDATE that changes the date moves the row to its new partition, which PostgreSQL performs as a DELETE and an INSERT:

UPDATE company.transactions
SET transaction_date = '2024-12-05'
WHERE order_id = 203
RETURNING tableoid::regclass AS partition, order_id, transaction_date;

Check that a query reads fewer partitions

The speed comes from partition pruning: the planner compares the WHERE clause with each partition's bounds and leaves out every partition that cannot match. EXPLAIN shows which partitions are left:

EXPLAIN (COSTS OFF)
SELECT SUM(amount) AS total_amount
FROM company.transactions
WHERE transaction_date >= DATE '2025-01-01';

 Aggregate
   ->  Seq Scan on transactions_2025 transactions
         Filter: (transaction_date >= '2025-01-01'::date)

The plan names only transactions_2025. The years before 2025 are never read, however many rows they hold. Pruning is controlled by enable_partition_pruning, which is on by default.

Pruning needs a condition on the partition key itself. A query that filters on order_id gives the planner nothing to compare with the bounds, so it reads every partition. A function wrapped around the key hides it in the same way:

EXPLAIN (COSTS OFF)
SELECT SUM(amount) FROM company.transactions
WHERE EXTRACT(YEAR FROM transaction_date) = 2025;

 Aggregate
   ->  Append
         ->  Seq Scan on transactions_before_2025 transactions_1
               Filter: (EXTRACT(year FROM transaction_date) = '2025'::numeric)
         ->  Seq Scan on transactions_2025 transactions_2
               Filter: (EXTRACT(year FROM transaction_date) = '2025'::numeric)

Written as a range on the column, transaction_date >= '2025-01-01' AND transaction_date < '2026-01-01', the same question reads one partition. So choose the partition key from the column that your slow queries filter on, and choose the period from the range they ask for: if most of them ask about one month, monthly partitions let each of them read one partition. The manual says that the planner handles up to a few thousand partitions fairly well, provided that typical queries prune all but a small number of them, and that planning time and memory grow with every partition left after pruning.

Primary keys and indexes

A primary key on the parent has to include the partition key:

ALTER TABLE company.transactions ADD PRIMARY KEY (transaction_id);

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

Each partition can check uniqueness only among its own rows, as the limitations section of the manual explains, so the key has to carry the column that decides the partition. The same holds for every unique constraint, and a foreign key that points at this table has to name both columns:

ALTER TABLE company.transactions ADD PRIMARY KEY (transaction_id, transaction_date);
CREATE INDEX ON company.transactions (order_id);

SELECT tablename, indexname
FROM pg_indexes
WHERE schemaname = 'company'
ORDER BY 1, 2;

An index created on the parent is created on every partition, and the partitions that you create or attach later get it too. CREATE INDEX CONCURRENTLY is not allowed on the parent; the manual's workaround creates the index ON ONLY the parent, builds each partition's index concurrently, and attaches them.

Add next year's partition, and detach old ones

A new partition is one statement, and it arrives with the parent's indexes:

CREATE TABLE company.transactions_2026
PARTITION OF company.transactions
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

SELECT indexname FROM pg_indexes
WHERE schemaname = 'company' AND tablename = 'transactions_2026'
ORDER BY 1;

A table that you filled beforehand, for example from a bulk load, joins the parent with ATTACH PARTITION. PostgreSQL then scans the table to check that every row fits the range, under an ACCESS EXCLUSIVE lock on that table, while the parent takes only SHARE UPDATE EXCLUSIVE. A CHECK constraint that already proves the range lets it skip the scan:

CREATE TABLE company.transactions_2027
  (LIKE company.transactions INCLUDING DEFAULTS);

ALTER TABLE company.transactions_2027
  ADD CONSTRAINT transactions_2027_range
  CHECK (transaction_date >= DATE '2027-01-01'
     AND transaction_date <  DATE '2028-01-01');

INSERT INTO company.transactions_2027 (order_id, transaction_date, amount)
VALUES (301, '2027-01-05', 10.00);

SET client_min_messages = debug1;
ALTER TABLE company.transactions
  ATTACH PARTITION company.transactions_2027
  FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');
RESET client_min_messages;

ALTER TABLE company.transactions_2027 DROP CONSTRAINT transactions_2027_range;

The debug level makes PostgreSQL report what it did. Among the lines it printed during the attach:

DEBUG:  building index "transactions_2027_pkey" on table "transactions_2027" serially
DEBUG:  building index "transactions_2027_order_id_idx" on table "transactions_2027" serially
DEBUG:  partition constraint for table "transactions_2027" is implied by existing constraints

The last line is the skipped scan. The attach still built the parent's two indexes on the new partition. Once it is attached, the CHECK is redundant, and the manual recommends dropping it.

Old data leaves the table the way it came in, one partition at a time:

ALTER TABLE company.transactions
  DETACH PARTITION company.transactions_before_2025 CONCURRENTLY;

DROP TABLE company.transactions_before_2025;

After DETACH, transactions_before_2025 is an ordinary table again, still holding its two rows, so you can dump it, move it to cheaper storage or drop it. The manual says that dropping or detaching a partition is far faster than a bulk operation and avoids the VACUUM work that a large DELETE leaves behind.

With CONCURRENTLY, the parent takes a SHARE UPDATE EXCLUSIVE lock, so queries and inserts on the other partitions carry on; without it, the detach takes ACCESS EXCLUSIVE on the parent. CONCURRENTLY cannot run inside a transaction block:

ERROR:  ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block

What a default partition costs

A partition declared DEFAULT takes every row that fits no other partition, so the 2026 insert above would have succeeded:

CREATE TABLE company.transactions_default
PARTITION OF company.transactions DEFAULT;

INSERT INTO company.transactions (order_id, transaction_date, amount)
VALUES (205, '2028-02-01', 60.00);

SELECT tableoid::regclass AS partition, order_id, transaction_date
FROM company.transactions
WHERE order_id = 205;

The price comes with the next partition. Creating one scans the default partition, and a row in the new range stops it:

CREATE TABLE company.transactions_2028
PARTITION OF company.transactions
FOR VALUES FROM ('2028-01-01') TO ('2029-01-01');

ERROR:  updated partition constraint for default partition "transactions_default" would be violated by some row

A default partition also blocks every concurrent detach, as ALTER TABLE states:

ERROR:  cannot detach partitions concurrently when a default partition exists

To add the 2028 partition, move its rows out of the default partition in the same transaction:

BEGIN;
CREATE TEMP TABLE moved ON COMMIT DROP AS
  SELECT * FROM company.transactions_default
  WHERE transaction_date >= '2028-01-01' AND transaction_date < '2029-01-01';
DELETE FROM company.transactions_default
  WHERE transaction_date >= '2028-01-01' AND transaction_date < '2029-01-01';
CREATE TABLE company.transactions_2028
PARTITION OF company.transactions
FOR VALUES FROM ('2028-01-01') TO ('2029-01-01');
INSERT INTO company.transactions SELECT * FROM moved;
COMMIT;

Afterwards, the row of order 205 is in transactions_2028. If you keep a default partition, treat it as an alarm rather than storage: create each period's partition ahead of time, and check that the default stays empty.

A partitioned table in DbSchema

DbSchema runs every statement on this page in its SQL Editor. Open it with Editor → SQL Editor in the toolbar, or Ctrl+E, type the statement, and press Run to send it to the connected PostgreSQL database.

When DbSchema reverse-engineers the schema, it draws the parent table with its columns and leaves the partitions off the diagram, so a table split into years shows as one box. To see the partitions and their bounds, run the catalog query from Where each row goes in the same SQL Editor, which shows the result in a grid.

Double-click the table header to open the Edit Table dialog. Its Options tab holds the clauses appended to CREATE TABLE (Edit a table), and DbSchema puts the table's PARTITION BY clause there. For a table that doesn't exist yet, you can set PARTITION BY RANGE in that tab. OK adds the table to the DbSchema model and, if you're connected, creates it in the database. The Options tab applies only when the table is created, and PostgreSQL itself cannot turn a regular table into a partitioned one, so an existing table moves to partitions through a new table and a copy of its rows.

The SQL Editor, reverse engineering and the diagram are in the free DbSchema Community Edition. Download DbSchema, connect it to your PostgreSQL database, and run EXPLAIN on the queries you want faster in the SQL Editor to see which partitions they read.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download