Partitioning in PostgreSQL: when you need it and how it works

Java · Go · Node · Python

What partitioning in PostgreSQL is, why you need it, and when you're better off without it. We cover the RANGE/LIST/HASH types, choosing a key, managing partitions with pg_partman, and the main practical win — instant removal of old data.

Published 27 июня 2026 ~8 min read Author: Вадим Викулин

← Back to the section

Picture a table of events that accumulates several million rows per day. After a year it holds billions of records. Queries slow down, automatic cleanup can't keep up, and deleting old data via DELETE turns into an hour-long operation that hammers the disk. This is exactly the situation partitioning was created for.

What partitioning is

Partitioning is the splitting of one large table into several physical parts (partitions) according to a defined rule. From the outside you keep working with a single table: you run INSERT, SELECT, UPDATE as usual. PostgreSQL decides on its own which partition to write a row to and which partitions to scan for a query.

The key thing is to understand the difference between "one logical table" and "several physical files on disk". When you write:

SELECT * FROM event_log WHERE occurred_at >= '2026-05-01' AND occurred_at < '2026-06-01';

PostgreSQL sees that the query only touches May and reads just one partition, event_log_2026_05. The other months are not physically touched at all. This is called partition pruning.

When partitioning is justified

Partitioning solves specific problems. Without the problem, you don't need the tool.

Worth considering if:

The table is larger than 50 GB (or 100 million rows) and keeps growing.
It's time-series data: events, metrics, logs — where every row has a timestamp.
Old data needs to be removed regularly: last month's, last year's.
Autovacuum can't keep up with the table — it constantly falls behind.

Unnecessary if:

The table is smaller than 10 GB. Regular indexes will do better and without the complexity.
Queries rarely use the intended partition key in WHERE. Then there will be no partition pruning — only overhead.
Data is spread absolutely evenly and queries read everything at once — that's not partitioning, that's closer to sharding.

Declarative partitioning

PostgreSQL 10 introduced a convenient syntax. First you create a "parent" table specifying the partitioning type, then you add partitions:

CREATE TABLE event_log (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    payload     jsonb NOT NULL,
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE event_log_2026_05 PARTITION OF event_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE event_log_2026_06 PARTITION OF event_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Notice that the primary key includes occurred_at — the partitioning column. This is a mandatory PostgreSQL requirement. Without it you can't create the PK.

After that everything works transparently:

-- INSERT goes into the parent table, PostgreSQL itself places it in the right partition
INSERT INTO event_log (occurred_at, payload) VALUES (now(), '{"type":"click"}');

-- SELECT with partition pruning — only event_log_2026_05 is read
SELECT * FROM event_log
WHERE occurred_at >= '2026-05-15' AND occurred_at < '2026-05-20';

The three partitioning types

RANGE — for time-based data

The most common type. Each partition is responsible for a range of values — for example, one month or one year.

PARTITION BY RANGE (occurred_at);

Good for time-series, period-based archives, numeric scales.

LIST — for categories

When data is split by a fixed set of values: region, document type, customer group identifier.

CREATE TABLE order_doc (...) PARTITION BY LIST (region);

CREATE TABLE order_doc_eu    PARTITION OF order_doc FOR VALUES IN ('EU', 'UK');
CREATE TABLE order_doc_usa   PARTITION OF order_doc FOR VALUES IN ('US', 'CA');
CREATE TABLE order_doc_other PARTITION OF order_doc DEFAULT;

The DEFAULT section accepts all rows that don't fall into any of the named partitions. Without it, an INSERT with an unknown value fails with an error.

HASH — even distribution

Partitions are determined by the remainder of dividing a column's hash by the number of partitions.

PARTITION BY HASH (user_id);

CREATE TABLE user_event_p0 PARTITION OF user_event FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_event_p1 PARTITION OF user_event FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- and so on

Used rarely. HASH doesn't give partition pruning for range queries — only for an exact match (WHERE user_id = ?). It helps distribute write load evenly, but is almost never used in practice for queries that filter by range.

How to choose a partition key

Choosing the key is the most important decision in partitioning. The wrong key reduces the benefit to zero.

There's a single rule: the key must be in the WHERE of almost every query against this table.

Good examples:

An events table with WHERE occurred_at > ? → key occurred_at.
A multi-tenant system where every query runs in the context of a single customer → key tenant_id.
An order archive with period-based queries → key created_at.

Bad examples:

Partitioning orders by status when most queries filter by customer_id — there will be no pruning, PostgreSQL will read all partitions.
Partitioning customers by country when 90% of records are in one country — one partition will be huge, the rest nearly empty.

Before creating partitions it's useful to check the data distribution:

SELECT date_trunc('month', occurred_at) AS m, count(*)
FROM event_log GROUP BY m ORDER BY m;

If the data is unevenly distributed — rethink the key.

Partition size

Aim for 1–50 GB per partition. For time-series this means:

Monthly partitions: with a few million events per month.
Weekly: with tens of millions of events per week.
Daily: with hundreds of millions of events per day.

An important point about the number of partitions: beyond 1000 partitions the planner starts to slow down noticeably. Beyond 10,000 — the cluster runs very slowly. If you need daily partitions for several years, think about archiving ahead of time.

Managing partitions

Create partitions in advance

If a row lands in the table but the needed partition doesn't exist yet, the INSERT fails with an error (unless there's a DEFAULT partition). Create partitions 1–2 periods ahead.

CREATE TABLE event_log_2026_07 PARTITION OF event_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

You can automate this with the pg_partman extension:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.event_log',
    p_control      => 'occurred_at',
    p_type         => 'native',
    p_interval     => '1 month',
    p_premake      => 4         -- create 4 partitions ahead
);

-- called on a schedule (cron or pg_cron)
SELECT partman.run_maintenance('public.event_log');

pg_partman creates new partitions automatically and can drop stale ones according to configurable retention rules.

Instant removal of old data — the main win

This is usually what people partition time-series data for:

DROP TABLE event_log_2025_05;   -- instant, no load

Compare that with a regular delete:

DELETE FROM event_log WHERE occurred_at < '2026-01-01';

With millions of rows, this DELETE:

Does a full scan to find the rows.
Creates a huge number of MVCC markers (PostgreSQL doesn't physically remove rows right away).
Requires autovacuum to run to reclaim disk space.
Can take tens of minutes with heavy load on disk and WAL.

DROP TABLE of a partition removes the file from disk instantly — no scan, no MVCC, no WAL.

Detaching a partition without dropping it

Sometimes you don't want to delete old data but move it — for example, into an archive table or another storage:

ALTER TABLE event_log DETACH PARTITION event_log_2025_05;
-- now event_log_2025_05 is an ordinary standalone table

PostgreSQL 14+ added an option that doesn't lock the parent table:

ALTER TABLE event_log DETACH PARTITION event_log_2025_05 CONCURRENTLY;

Indexes on partitioned tables

An index created on the parent table is automatically created on every partition:

CREATE INDEX ON event_log (payload->>'event_type');
-- PostgreSQL creates the index on event_log_2026_05, event_log_2026_06, and so on

Unique indexes have a restriction: they must include the partition key. Uniqueness is guaranteed only within a single partition. If you need global uniqueness (for example, by UUID), set up a separate lookup table.

Common mistakes

Partitioning a small table. If you have less than 10 GB of data, partitioning adds complexity with no tangible benefit. Good indexes and a tuned autovacuum will do better.

The key isn't used in queries. If queries don't filter by the partition key, PostgreSQL reads all partitions — that's slower than an ordinary table with an index.

Too many partitions. Daily partitioning over several years easily produces thousands of partitions. Remember the ~1000-partition limit for the planner to work well.

Not creating a partition in advance. An INSERT without the needed partition fails. Use pg_partman or a script in a scheduler job.

Changing the partitioning column's value via UPDATE. PostgreSQL effectively deletes the row from one partition and inserts it into another — that's inefficient and can cause unexpected locks. The partition key must be immutable.

Migrating an existing table to a partitioned one

If a table already exists and has grown large, you can move it into a partitioned structure:

-- 1. Create a new partitioned table
CREATE TABLE event_log_new (LIKE event_log INCLUDING ALL)
    PARTITION BY RANGE (occurred_at);
-- Create the partitions...

-- 2. Copy the data
INSERT INTO event_log_new SELECT * FROM event_log;

-- 3. Rename atomically
BEGIN;
ALTER TABLE event_log RENAME TO event_log_old;
ALTER TABLE event_log_new RENAME TO event_log;
COMMIT;

DROP TABLE event_log_old;

For a 500 GB table the copy takes several hours, and you'll need space for both versions at once. For production environments without downtime the scheme is more involved.

In short

Partitioning is one logical table split into several physical ones by a defined rule.
The main win for time-series: DROP TABLE of an old partition is instant, whereas a DELETE of millions of rows takes tens of minutes.
Signs it's a fit: table larger than 50 GB, time-series, regular removal of old data, autovacuum can't keep up.
Three types: RANGE (time-based data, the most common), LIST (categories), HASH (rare, only for even writes).
The partition key must be in the WHERE of most queries — otherwise partition pruning doesn't work.
The primary key must include the partitioning column.
Target partition size: 1–50 GB; more than 1000 partitions slows the planner.
pg_partman automates creating new partitions and dropping stale ones.
Don't partition tables smaller than 10 GB — regular indexes will do better.