Scrolls
4 min read
On this page

Table Partitioning

Partitioning splits a large table into smaller physical pieces while keeping it logically one table. Queries automatically access only the relevant partitions. PostgreSQL has supported declarative partitioning since version 10, and it has gotten better with each release.

When to Partition

Partition when:

  • A table exceeds 100GB or has hundreds of millions of rows.
  • Queries almost always filter on a specific column (date, tenant, category).
  • You need to efficiently drop or archive old data.
  • You want partition-level VACUUM and maintenance.

Do not partition when:

  • The table is small (under a few million rows).
  • Queries do not filter on the partition key.
  • You have not tried indexing first.

Partitioning adds operational complexity. It is a tool for specific problems, not a default for every table.

Declarative Partitioning Syntax

Creating a Partitioned Table

CREATE TABLE events (
    id bigserial,
    event_type text NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

The parent table holds no data. All data lives in the partitions.

Creating Partitions

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

The FROM value is inclusive, the TO value is exclusive. There must be no gaps or overlaps.

Range Partitioning

The most common strategy. Partition by date for time-series data.

CREATE TABLE orders (
    id bigserial,
    customer_id int NOT NULL,
    amount numeric NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE orders_2024_q3 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE orders_2024_q4 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

Range partitioning works with any orderable data type: dates, integers, numerics.

A Default Partition

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Rows that do not match any partition go here. Without a default partition, inserts that do not match any partition fail with an error.

Check the default partition periodically — if rows are landing there, you are missing partitions.

List Partitioning

Partition by a discrete set of values. Good for category or region columns.

CREATE TABLE customers (
    id serial,
    name text NOT NULL,
    region text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('us-east', 'us-west', 'us-central');

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('eu-west', 'eu-central', 'eu-north');

CREATE TABLE customers_apac PARTITION OF customers
    FOR VALUES IN ('ap-southeast', 'ap-northeast', 'ap-south');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

List partitioning is also useful for multi-tenant applications where you partition by tenant_id (if you have a manageable number of tenants).

Hash Partitioning

Distributes rows evenly across partitions using a hash of the partition key. Useful when there is no natural range or list, but you want to split a large table.

CREATE TABLE logs (
    id bigserial,
    session_id uuid NOT NULL,
    message text,
    created_at timestamptz NOT NULL
) PARTITION BY HASH (session_id);

CREATE TABLE logs_p0 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_p1 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_p2 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_p3 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash partitioning does not support partition pruning for range queries (WHERE created_at > ...) because the hash destroys ordering. It only prunes on equality (WHERE session_id = ...).

Partition Pruning

The query planner eliminates partitions that cannot contain matching rows. This is the primary performance benefit of partitioning.

-- Only scans the relevant monthly partition
EXPLAIN SELECT * FROM orders WHERE created_at = '2024-06-15';

                                QUERY PLAN
--------------------------------------------------------------------------
 Append  (cost=0.00..25.00 rows=5 width=64)
   ->  Seq Scan on orders_2024_q2  (cost=0.00..25.00 rows=5 width=64)
         Filter: (created_at = '2024-06-15')

Only orders_2024_q2 is scanned. All other partitions are pruned.

Runtime Partition Pruning

PostgreSQL also prunes at execution time when the partition key value comes from a parameter or subquery.

PREPARE get_orders (timestamptz) AS
SELECT * FROM orders WHERE created_at = $1;

EXPLAIN ANALYZE EXECUTE get_orders('2024-06-15');

 Append  (cost=0.00..25.00 rows=5 width=64) (actual time=0.020..0.025 rows=3 loops=1)
   Subplans Removed: 3
   ->  Seq Scan on orders_2024_q2  ...

"Subplans Removed: 3" means three partitions were pruned at runtime.

When Pruning Fails

Partition pruning requires the WHERE clause to reference the partition key directly.

-- Pruning works
SELECT * FROM orders WHERE created_at >= '2024-04-01' AND created_at < '2024-07-01';

-- Pruning FAILS — function wrapping prevents it
SELECT * FROM orders WHERE date_trunc('month', created_at) = '2024-06-01';

-- Pruning FAILS — no filter on the partition key
SELECT * FROM orders WHERE customer_id = 42;

When pruning fails, PostgreSQL scans all partitions. This can be slower than a single unpartitioned table because of the overhead of the Append node.

Primary Keys & Unique Constraints

The partition key must be part of any primary key or unique constraint.

-- This works
CREATE TABLE events (
    id bigserial,
    created_at timestamptz NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- This FAILS
CREATE TABLE events (
    id bigserial PRIMARY KEY,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);
-- ERROR: unique constraint must include all partitioning columns

This is a fundamental limitation. If you need a globally unique id, use UUIDs or sequences (which are global) and accept that the primary key includes the partition key.

Multi-Level Partitioning

You can partition partitions.

CREATE TABLE events (
    id bigserial,
    event_type text NOT NULL,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (event_type);

CREATE TABLE events_2024_clicks PARTITION OF events_2024
    FOR VALUES IN ('click');

CREATE TABLE events_2024_views PARTITION OF events_2024
    FOR VALUES IN ('view');

CREATE TABLE events_2024_other PARTITION OF events_2024 DEFAULT;

Use multi-level partitioning sparingly. It multiplies operational complexity.

Common Pitfalls

  • Partitioning small tables. If the table fits in memory, partitioning adds overhead without benefit. Try indexing first.
  • Forgetting to create future partitions. Inserts fail if no partition matches and there is no default partition. Use pg_partman or a cron job to create partitions ahead of time.
  • Wrapping the partition key in a function in WHERE clauses. This prevents partition pruning and forces a scan of all partitions.
  • Not including the partition key in the primary key. This is required by PostgreSQL and surprises developers accustomed to simple serial primary keys.
  • Creating too many partitions. Each partition is a separate table with its own files, catalog entries, and planning overhead. Thousands of partitions slow down planning. Keep it to hundreds at most.
  • Assuming partitioning speeds up all queries. Only queries that filter on the partition key benefit from pruning. Other queries may be slower.

Key Takeaways

  • Declarative partitioning (v10+) splits a table into physical pieces transparent to queries.
  • Range partitioning by date is the most common and most effective strategy.
  • Partition pruning eliminates irrelevant partitions at plan time and execution time. It requires direct references to the partition key in WHERE clauses.
  • The partition key must be part of all primary keys and unique constraints.
  • Start with a default partition to catch rows that do not match any defined partition.
  • Partition for operational benefits (archival, maintenance) and query performance on large tables — not as a default strategy for every table.