Table Partitioning

Imagine an app that stores every UPI payment or every page view. After a few years the table holds billions of rows. Queries on last week's data slow down, indexes become enormous, and deleting data older than three years takes hours and bloats the table. Partitioning splits one big logical table into smaller physical tables, called partitions, while your queries still see a single table.

In this lesson you will create range, list and hash partitions, watch PostgreSQL skip partitions it does not need, add and remove partitions, and learn when partitioning is worth the extra work.

Declarative partitioning

You create a partitioned table with PARTITION BY and a partition key, then create partitions that each hold a part of the key's values. The partitioned table itself stores no rows; every row goes into exactly one partition:

CREATE TABLE payments (
  payment_id bigint GENERATED ALWAYS AS IDENTITY,
  paid_on date NOT NULL,
  method text NOT NULL,
  amount numeric(10,2) NOT NULL
) PARTITION BY RANGE (paid_on);

CREATE TABLE payments_2025_q1 PARTITION OF payments
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE payments_2025_q2 PARTITION OF payments
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

INSERT INTO payments (paid_on, method, amount) VALUES
  ('2025-01-15', 'upi', 799.00), ('2025-03-02', 'card', 15999.00),
  ('2025-04-20', 'upi', 2499.00), ('2025-06-30', 'netbanking', 6499.00);

SELECT tableoid::regclass AS stored_in, paid_on, method, amount
FROM payments
ORDER BY paid_on;

Query OK
Query OK
Query OK
Query OK, 4 rows affected
stored_in        | paid_on    | method     | amount
-----------------+------------+------------+---------
payments_2025_q1 | 2025-01-15 | upi        | 799.00
payments_2025_q1 | 2025-03-02 | card       | 15999.00
payments_2025_q2 | 2025-04-20 | upi        | 2499.00
payments_2025_q2 | 2025-06-30 | netbanking | 6499.00
(4 rows)

tableoid::regclass shows which partition each row lives in. Range bounds include the FROM value and exclude the TO value, so quarters fit together without gaps or overlaps. Inserting a date outside every partition fails with "no partition of relation found for row", unless you add a DEFAULT partition to catch the rest.

Partition pruning

When a query filters on the partition key, PostgreSQL reads only the partitions that can contain matching rows. This is partition pruning, and EXPLAIN shows it:

CREATE TABLE payments (paid_on date NOT NULL, amount numeric(10,2)) PARTITION BY RANGE (paid_on);
CREATE TABLE payments_2025_q1 PARTITION OF payments FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE payments_2025_q2 PARTITION OF payments FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
CREATE TABLE payments_2025_q3 PARTITION OF payments FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

EXPLAIN (COSTS OFF)
SELECT sum(amount) FROM payments WHERE paid_on >= '2025-05-01' AND paid_on < '2025-06-01';

EXPLAIN (COSTS OFF)
SELECT sum(amount) FROM payments WHERE amount > 1000;

Query OK
Query OK
Query OK
Query OK
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate
  ->  Seq Scan on payments_2025_q2 payments
        Filter: ((paid_on >= '2025-05-01'::date) AND (paid_on < '2025-06-01'::date))
(3 rows)

QUERY PLAN
---------------------------------------------------
Aggregate
  ->  Append
        ->  Seq Scan on payments_2025_q1 payments_1
              Filter: (amount > '1000'::numeric)
        ->  Seq Scan on payments_2025_q2 payments_2
              Filter: (amount > '1000'::numeric)
        ->  Seq Scan on payments_2025_q3 payments_3
              Filter: (amount > '1000'::numeric)
(8 rows)

The first query touches only the second quarter. The second filters on amount, not on the key, so every partition must be scanned. Choose a partition key that most of your important queries filter on, usually a date.

List and hash partitioning

CREATE TABLE branch_sales (branch text NOT NULL, amount numeric) PARTITION BY LIST (branch);
CREATE TABLE branch_sales_north PARTITION OF branch_sales FOR VALUES IN ('Rohtak', 'Delhi', 'Lucknow');
CREATE TABLE branch_sales_south PARTITION OF branch_sales FOR VALUES IN ('Kochi', 'Chennai');
CREATE TABLE branch_sales_other PARTITION OF branch_sales DEFAULT;

CREATE TABLE sessions (user_id bigint NOT NULL, data jsonb) PARTITION BY HASH (user_id);
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ...and REMAINDER 1, 2 and 3

Removing old data instantly

This is often the biggest win. Deleting a quarter's worth of rows from a normal table means a huge DELETE, followed by VACUUM work. With partitions, you detach or drop the old partition, which is almost instant and leaves no dead rows behind:

ALTER TABLE payments DETACH PARTITION payments_2025_q1;
DROP TABLE payments_2025_q1;
-- or, on a busy server:
ALTER TABLE payments DETACH PARTITION payments_2025_q1 CONCURRENTLY;

A detached partition becomes an ordinary table, so you can archive it with pg_dump before dropping it. New partitions are added the same way, with CREATE TABLE ... PARTITION OF ahead of time, often by a scheduled job or the pg_partman extension.

Indexes, keys and limits

An index created on the partitioned table is created on every partition automatically, including future ones. A primary key or unique constraint must include the partition key columns, because each partition can only check uniqueness within itself. So PRIMARY KEY (payment_id) is not allowed on the table above, but PRIMARY KEY (payment_id, paid_on) is.

Common mistakes

• Partitioning a small table. Indexes are simpler and faster below many millions of rows.
• A key that queries do not filter on. No pruning means every partition is scanned.
• Forgetting future partitions. Inserts fail when no partition matches; create them ahead or add a DEFAULT partition.
• Expecting a unique ID alone to be the primary key. It must include the partition key.

Key points

• PARTITION BY RANGE, LIST or HASH creates a partitioned table; partitions are created with PARTITION OF.
• Queries see one table; rows are stored in the matching partition.
• Partition pruning skips partitions when the WHERE clause filters on the key.
• Dropping or detaching a partition removes old data almost instantly.
• Indexes on the parent are created on every partition; unique keys must include the partition key.
• Partition only very large tables whose queries and retention follow the key.

SET TimeZone = 'Asia/Kolkata';
CREATE TABLE app_logs (logged_at timestamptz NOT NULL, level text, message text)
  PARTITION BY RANGE (logged_at);
CREATE TABLE app_logs_2026_01 PARTITION OF app_logs
  FOR VALUES FROM ('2026-01-01 00:00+05:30') TO ('2026-02-01 00:00+05:30');
CREATE TABLE app_logs_2026_02 PARTITION OF app_logs
  FOR VALUES FROM ('2026-02-01 00:00+05:30') TO ('2026-03-01 00:00+05:30');
CREATE TABLE app_logs_other PARTITION OF app_logs DEFAULT;

INSERT INTO app_logs VALUES
  ('2026-01-10 09:15+05:30', 'info', 'Server started'),
  ('2026-02-03 18:40+05:30', 'error', 'Payment timeout'),
  ('2026-03-01 08:00+05:30', 'info', 'Backup finished');

SELECT tableoid::regclass AS partition, logged_at, level
FROM app_logs
ORDER BY logged_at;