How to Set Up Partitioning in PostgreSQL

There’s a moment that happens on every project with a table that grows fast — logs, events, orders, sensor readings — where queries that used to take milliseconds start taking seconds, VACUUM starts taking hours, and index maintenance becomes a genuine operational burden. The first time I hit that wall, on a table with hundreds of millions of event rows, partitioning is what got the project back on track. In this article, I’ll walk through how table partitioning works in PostgreSQL, the different partitioning strategies, real setup examples, and the operational lessons I’ve picked up managing partitioned tables in production.

What Partitioning Actually Does

Partitioning splits one logical table into multiple physical tables (partitions) behind the scenes, while letting you query it as if it were a single table. Each partition holds a subset of the rows, defined by a partitioning strategy — by range, by list, or by hash. The database automatically routes inserts to the correct partition and, crucially, can skip scanning partitions entirely when a query’s filter makes it obvious they can’t contain relevant rows. This is called partition pruning, and it’s the main reason partitioning improves performance so dramatically for the right workloads.

The Three Partitioning Strategies

Range Partitioning

Splits data based on a range of values in one or more columns — the classic use case being time-based data.

CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

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

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

This is the strategy I reach for most often, since so much of the data I deal with — logs, events, orders, transactions — naturally partitions by time.

List Partitioning

Splits data based on a specific set of discrete values, most naturally suited to things like region, tenant, or status.

CREATE TABLE customers (
    id BIGSERIAL,
    name TEXT NOT NULL,
    region TEXT NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US', 'CA');

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('DE', 'FR', 'ES', 'IT');

CREATE TABLE customers_other PARTITION OF customers
    DEFAULT;

Note the DEFAULT partition — it catches any value that doesn’t match another partition’s list, which prevents inserts from failing outright when new, unanticipated values show up. I always create a default partition for list partitioning unless I’m certain the set of values is truly fixed and closed.

Hash Partitioning

Distributes rows evenly across a fixed number of partitions based on a hash of the partition key, useful when you want to spread load evenly but don’t have a natural range or list to partition on.

CREATE TABLE sessions (
    id BIGSERIAL,
    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);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

I use hash partitioning less often than range partitioning, mostly for cases where I need to spread heavy write load across partitions evenly and there’s no natural time or category axis to split on.

Primary Keys and Unique Constraints

One constraint that trips people up: in a partitioned table, any unique constraint (including the primary key) must include the partition key as part of the constraint.

CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

This is a real design constraint, not just boilerplate — PostgreSQL enforces uniqueness per-partition using local indexes, so it can’t guarantee global uniqueness on a column that isn’t part of the partition key. If you need id alone to be globally unique, you’ll need a different approach, like generating IDs from a sequence or UUID that’s guaranteed unique regardless (which most surrogate keys already are), and accepting that PostgreSQL isn’t the one enforcing that particular uniqueness constraint across partitions.

Indexing Partitioned Tables

Creating an index on the parent table automatically creates a matching index on every partition, including future ones:

CREATE INDEX idx_events_event_type ON events (event_type);

This single statement propagates down to every existing partition and applies automatically to any new partition attached later. This is one of the conveniences that makes declarative partitioning (the modern approach, since PostgreSQL 10) so much nicer than the old inheritance-based partitioning trick people used before native partitioning existed.

Partition Pruning in Action

Let’s see pruning at work:

EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-02-01' AND created_at < '2026-02-15';

The query plan should show only the events_2026_02 partition being scanned — the planner recognizes that no other partition could possibly contain matching rows and skips them entirely, without even opening them. This is the core performance win of partitioning: instead of one giant index across hundreds of millions of rows, you get much smaller, more manageable indexes per partition, and most queries only ever touch one or two of them.

Make sure enable_partition_pruning is on (it’s on by default):

SHOW enable_partition_pruning;

Attaching and Detaching Partitions

One of the biggest operational wins of partitioning, especially for time-series data, is that you can drop an entire partition instantly instead of running a slow, transaction-log-heavy DELETE.

-- Instantly and cheaply remove old data
ALTER TABLE events DETACH PARTITION events_2025_01;
DROP TABLE events_2025_01;

Compare that to DELETE FROM events WHERE created_at < '2025-02-01', which on a huge table would generate massive amounts of WAL, bloat the table with dead tuples that then need vacuuming, and take a long time to run. Dropping a partition is nearly instant, because it’s a metadata operation, not a row-by-row delete.

You can also attach an existing table as a new partition, which is useful for staging and validating data before it becomes “live”:

CREATE TABLE events_2026_03 (LIKE events INCLUDING ALL);
-- load and validate data into events_2026_03 here
ALTER TABLE events ATTACH PARTITION events_2026_03
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Automating Partition Creation

Manually creating a new monthly partition every month is exactly the kind of thing I forget to do until something breaks. There are a couple of solid approaches:

Option 1: pg_partman, a widely used extension that automates partition creation and retention.

CREATE EXTENSION IF NOT EXISTS pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month'
);

pg_partman also handles automatic retention (dropping old partitions after a configured period) via a scheduled maintenance function, which I typically run through pg_cron or an external scheduler.

Option 2: A simple scheduled function, if I want to avoid adding another extension dependency:

CREATE OR REPLACE FUNCTION create_monthly_partition() RETURNS void AS $$
DECLARE
    start_date DATE := date_trunc('month', now() + interval '1 month');
    end_date DATE := start_date + interval '1 month';
    partition_name TEXT := 'events_' || to_char(start_date, 'YYYY_MM');
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

Then schedule it to run monthly with pg_cron or an external job scheduler, always creating next month’s partition ahead of time so inserts never hit a missing partition.

What Happens Without a Matching Partition

If a row’s partition key value doesn’t match any existing partition and there’s no default partition, the insert fails outright:

ERROR:  no partition of relation "events" found for row

This is exactly why automating partition creation ahead of time (not on-demand) matters — I always make sure the next period’s partition exists well before it’s needed, rather than creating it reactively when the first insert for that period fails.

Common Use Cases

• Time-series data — logs, events, metrics, IoT sensor readings — partitioned by time range, with old partitions dropped or archived on a retention schedule.
• Multi-tenant applications — partitioned by tenant ID (list or hash), which can also help with data isolation and per-tenant maintenance.
• Large transactional tables — orders, transactions — partitioned by date, where most queries only care about a recent window of time.
• Regulatory data retention — partitioning makes it trivial to enforce “keep exactly N months of data” policies by dropping old partitions on schedule.

Troubleshooting Tips

Queries aren’t pruning partitions the way I expect. Check that the WHERE clause uses the partition key directly with a comparison the planner can reason about statically. Wrapping the partition key in a function (like date_trunc(created_at) instead of comparing created_at directly) can sometimes prevent pruning, since the planner can’t always see through arbitrary expressions.

Inserts failing with “no partition found.” You’re missing a partition for that value range. Check your automation is actually running and creating partitions far enough ahead.

Too many partitions hurting planning time. Having thousands of partitions can actually slow down query planning, since the planner has to consider (and often prune) each one. If you’re at that scale, consider coarser partition granularity (monthly instead of daily) or a two-level partitioning scheme (partition by month, sub-partition by another key).

Unique constraint errors when the constraint doesn’t include the partition key. Remember: any unique index or primary key on a partitioned table must include the partitioning column.

Old, un-pruned queries against the whole table are still slow. If a common query pattern doesn’t filter on the partition key at all, partitioning won’t help it — it might even add slight overhead. Partitioning is a performance win specifically when your query patterns tend to filter on the partition key.

Best Practices

1. Choose a partition key that matches your actual query patterns. If most queries filter by date, partition by date. Partitioning on a column nobody filters by gains you nothing.
2. Automate partition creation ahead of time, never reactively.
3. Automate retention (dropping old partitions) if you have a data retention policy — this is one of partitioning’s biggest operational wins.
4. Always include the partition key in unique constraints and the primary key.
5. Keep partition granularity reasonable. Don’t create thousands of tiny partitions if a coarser granularity would serve your query patterns just as well — planning overhead is real.
6. Create a default partition for list partitioning unless your value set is truly fixed and closed.
7. Test pruning behavior with EXPLAIN on your actual production query patterns before assuming partitioning is helping.
8. Consider pg_partman if you want mature, battle-tested automation rather than maintaining your own scheduled function.

A Real-World Example: Migrating a Live Table to Partitioning

One of the trickiest parts of partitioning isn’t setting it up on a new table — it’s converting an existing, live, non-partitioned table into a partitioned one without a long outage. Here’s the general approach I’ve used successfully on tables with hundreds of millions of rows:

-- Step 1: create the new partitioned table under a temporary name
CREATE TABLE events_partitioned (LIKE events INCLUDING ALL)
PARTITION BY RANGE (created_at);

CREATE TABLE events_partitioned_2026_01 PARTITION OF events_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- ... create partitions covering the full historical range

-- Step 2: backfill historical data in batches, to avoid one enormous transaction
INSERT INTO events_partitioned
SELECT * FROM events
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-08';
-- repeat in weekly or daily batches across the full history

I always backfill in small batches rather than one giant INSERT ... SELECT, both to avoid a single enormous transaction that bloats WAL and holds resources for a long time, and so I can pause or resume the backfill if something goes wrong partway through.

-- Step 3: once history is backfilled, catch up on any rows written since the backfill started
INSERT INTO events_partitioned
SELECT * FROM events
WHERE created_at >= (SELECT max(created_at) FROM events_partitioned)
ON CONFLICT DO NOTHING;

-- Step 4: swap the tables inside a short transaction
BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;
COMMIT;

The swap itself (step 4) needs an ACCESS EXCLUSIVE lock briefly, but because all the slow work (backfilling) happened beforehand against the new table, the actual cutover is fast — typically well under a second even on large tables, since renaming a table is a metadata operation, not a data-copying one. I keep events_old around for a while after the cutover as a safety net before finally dropping it.

Sub-Partitioning

For very high-volume tables, a single level of partitioning sometimes isn’t enough — a monthly partition might still be huge on its own. PostgreSQL supports partitioning a partition itself, which I’ve used for multi-tenant time-series data where I need both an even spread across tenants and time-based retention:

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
    PARTITION BY HASH (tenant_id);

CREATE TABLE events_2026_01_p0 PARTITION OF events_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_2026_01_p1 PARTITION OF events_2026_01
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- and so on

This gives me time-based retention (dropping whole months) combined with even write distribution across tenants within each month. I don’t reach for sub-partitioning unless a single level genuinely isn’t enough, since it adds real complexity to reason about — but for the largest tables I’ve worked with, it’s been worth it.

Frequently Asked Questions

Does partitioning help small tables? Generally no — the overhead of managing multiple partitions and the added query planning complexity usually outweighs any benefit until a table is large enough that a single index or sequential scan is genuinely becoming a bottleneck, which in my experience is usually somewhere in the tens of millions of rows, though it depends heavily on row width and query patterns.

Can I change the partition key after creating the table? Not directly — there’s no ALTER TABLE ... PARTITION BY to change the key on an existing partitioned table. You’d need to create a new partitioned table with the desired key and migrate data over, similar to the live-migration approach described above.

Do foreign keys work with partitioned tables? Yes, PostgreSQL supports foreign keys referencing and referenced by partitioned tables, though there are some historical version-dependent limitations worth checking against your specific PostgreSQL version, particularly around referencing a partitioned table from another table.

What happens to SERIAL/identity columns across partitions? A single sequence is shared across all partitions when defined on the parent table, so IDs stay unique across the whole partitioned table, not just within one partition — this is different from the primary key mechanics I mentioned earlier, which is about local uniqueness enforcement, not ID generation itself.

Wrapping Up

Partitioning isn’t something I reach for on every table — for a modestly sized table, it just adds complexity without meaningful benefit. But once a table crosses into the tens or hundreds of millions of rows, especially time-series or naturally-segmented data, partitioning turns “unmanageable” back into “boring and predictable,” which is exactly what you want from a database at scale. Get the partition key right, automate creation and retention, and confirm pruning is actually happening with EXPLAIN — that combination has saved several projects of mine from tables that were quietly becoming unmanageable.