PostgreSQL Table Partitioning: Range, List, and Operational Gotchas

Why partition a large table

Say a table grows to hundreds of millions of rows. Queries still work, but vacuum takes longer, indexes balloon, and a routine DELETE of old data locks the table for minutes. PostgreSQL declarative partitioning splits one logical table into smaller physical pieces while keeping a single interface for queries.

Partitioning helps when:

• You routinely query or delete by a specific column, like created_at or tenant_id.
• You need to drop old data quickly (DROP PARTITION is faster than DELETE).
• You want vacuum and analyze to run on smaller chunks.

It is not a magic performance button. If your queries do not filter on the partition key, PostgreSQL scans every partition, which can be slower than a single well-indexed table.

Partitioning types in PostgreSQL

PostgreSQL supports three declarative methods:

Range and list are usually the most operationally transparent. Hash partitions spread rows evenly but make range queries and archival harder.

Creating a partitioned table

Here is a range-partitioned events table by month:

CREATE TABLE events (
    id bigserial,
    tenant_id text NOT NULL,
    created_at timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Important: the partition key must be part of every unique or primary key constraint. That is why created_at is included in the primary key above.

Now create the partitions. PostgreSQL does not automatically create future partitions, so you need them before inserting data:

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');

Inserts route automatically:

INSERT INTO events (tenant_id, created_at, payload)
VALUES ('acme', '2024-01-15 08:30:00+00', '{"action": "login"}');

Query pruning means PostgreSQL skips partitions that cannot match the WHERE clause:

EXPLAIN SELECT * FROM events WHERE created_at >= '2024-02-01';

In the plan, you should see only the relevant partition scanned, not the whole hierarchy.

List partitioning for multi-tenant data

If you isolate data by customer, list partitioning keeps each tenant in its own physical table:

CREATE TABLE invoices (
    id bigserial,
    customer_id text NOT NULL,
    amount numeric NOT NULL,
    issued_at timestamptz NOT NULL
) PARTITION BY LIST (customer_id);

CREATE TABLE invoices_acme PARTITION OF invoices
    FOR VALUES IN ('acme');

CREATE TABLE invoices_globex PARTITION OF invoices
    FOR VALUES IN ('globex');

A DEFAULT partition catches anything that does not match:

CREATE TABLE invoices_default PARTITION OF invoices DEFAULT;

Maintenance: the part no one tells you about

Partitioning creates child tables. Maintenance commands run on the parent, but vacuum and analyze operate per partition.

Adding partitions ahead of time is a common operational task. A typical approach is a scheduled job that creates the next month's range partition before the first of the month.

Dropping old data is where partitioning shines:

ALTER TABLE events DETACH PARTITION events_2023_01;
DROP TABLE events_2023_01;

Detaching is fast and does not bloat the parent table with dead tuples. After detaching, you can archive or drop the table.

Indexes exist on each partition. You create them on the parent, and PostgreSQL propagates them:

CREATE INDEX idx_events_tenant_created ON events (tenant_id, created_at);

Operational gotchas

Unique constraints and foreign keys

Every unique constraint must include the partition key. This means a standalone id column cannot be unique unless you add created_at or tenant_id to it. Foreign keys referencing partitioned tables have restrictions in older PostgreSQL versions; check your version before designing around them.

Partitioning an existing table

You cannot convert a regular table into a partitioned table in place. You must create a new partitioned parent, copy data, and swap. For large tables, use pg_partman or logical replication to avoid extended downtime.

Default partitions and query planning

As noted, default partitions can become dumping grounds that hurt pruning. If you use one, periodically check that hot queries are not scanning it.

Too many partitions

Thousands of partitions increase planner overhead and memory usage. If you partition by day and retain years of data, consider monthly or weekly partitions instead.

JSONB and partitioning

You can partition on a scalar extracted from JSONB using an expression partition key (PostgreSQL 12+), but expression partitioning adds planner complexity:

CREATE TABLE events (
    id bigserial,
    payload jsonb NOT NULL
) PARTITION BY LIST ((payload->>'tenant_id'));

Keep it simple unless you have a clear reason.

Rollout checklist

• Identify a column that most queries filter on
• Confirm primary and unique keys include that column
• Test query plans with EXPLAIN to verify partition pruning
• Schedule partition creation for future time ranges
• Plan a retention job that detaches or drops old partitions
• Verify vacuum and analyze settings work per partition
• Test failover and backup procedures with partitioned tables

Takeaway

Partitioning solves operational problems around data retention, vacuum, and large-table maintenance, but it shifts complexity into schema design and operations. Start with range partitioning on a time column, keep the partition count manageable, and always verify query plans before deploying to production.

IFLEON can help design and operate PostgreSQL workloads at scale.