Partitioning is a tool with sharp edges. Used right, you drop a year of old data in milliseconds and your hot indexes stay tiny. Used wrong, you make queries slower and operations more painful. This post is the working set.

When to partition

Partition when one or more applies:

  1. Table > 100 GB and growing.
  2. Time-series data with heavy date-range queries.
  3. Cheap retention is needed: drop yearly partitions.
  4. Per-tenant isolation at the partition level.

Don’t partition when:

  • Table < 100 GB and queries are fine.
  • Queries don’t filter by partition key (no pruning benefit).
  • You don’t have a clear partition key.

Range partitioning (most common)

CREATE TABLE events (
    id          bigint NOT NULL,
    occurred_at timestamptz NOT NULL,
    user_id     bigint NOT NULL,
    payload     jsonb NOT NULL
) PARTITION BY RANGE (occurred_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');

Queries filtered by occurred_at only scan relevant partitions:

EXPLAIN SELECT * FROM events
WHERE occurred_at >= '2026-02-15' AND occurred_at < '2026-02-20';
-- Only events_2026_02 scanned

Hash partitioning

CREATE TABLE messages (
    user_id bigint NOT NULL,
    body    text   NOT NULL
) PARTITION BY HASH (user_id);

CREATE TABLE messages_p0 PARTITION OF messages FOR VALUES WITH (modulus 8, remainder 0);
CREATE TABLE messages_p1 PARTITION OF messages FOR VALUES WITH (modulus 8, remainder 1);
-- ... 8 total

Even distribution; good for high-throughput by-user queries. No retention benefit (you can’t drop “old” partitions).

List partitioning

CREATE TABLE orders (
    region text NOT NULL,
    ...
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-north');

For categorical splits. Useful for per-region or per-tenant.

Indexes

In Postgres, you can create indexes on the parent (Postgres creates them on each partition):

CREATE INDEX events_user_idx ON events (user_id);
CREATE INDEX events_at_idx   ON events (occurred_at);

Each partition gets its own index — small, fast.

Foreign keys

Foreign keys to a partitioned table: supported. Foreign keys from a partitioned table to other tables: supported.

But: keep them simple. Cross-partition FK enforcement is per-partition.

pg_partman: automation

Manually creating partitions is a chore. pg_partman:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control      := 'occurred_at',
    p_type         := 'range',
    p_interval     := 'monthly',
    p_premake      := 4
);

A maintenance job creates new partitions ahead of time and (optionally) drops old:

SELECT partman.run_maintenance();

Schedule via pg_cron or external cron.

Retention

Drop a partition; instant.

DROP TABLE events_2024_01;

vs

DELETE FROM events WHERE occurred_at < '2024-02-01';
-- 2 hours; bloat; index churn

This is often the biggest practical win.

Querying

-- GOOD: pruning works
SELECT * FROM events
WHERE occurred_at BETWEEN '2026-02-01' AND '2026-02-10';

-- BAD: no pruning (function on partition key)
SELECT * FROM events
WHERE date_trunc('day', occurred_at) = '2026-02-15';

-- BAD: no pruning (no partition key in WHERE)
SELECT * FROM events WHERE user_id = 123;

Always include the partition key with simple comparisons. Use EXPLAIN to confirm pruning.

Migration to partitioned

-- 1. Create partitioned table
CREATE TABLE events_new (...) PARTITION BY RANGE (occurred_at);
-- + partition definitions

-- 2. Backfill in batches
INSERT INTO events_new SELECT * FROM events WHERE occurred_at < '2026-01-01';
-- ... in chunks

-- 3. Cut over
BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
COMMIT;

-- 4. Drop old after monitoring

Or use logical replication for zero-downtime moves.

Common mistakes

1. Partitioning a small table

20GB table; partition into 24 monthly partitions of <1GB each. Now you have 24 mostly-empty tables and queries that don’t get faster. Just don’t.

2. Partition key not in queries

Partition by created_at, queries are by user_id. Every query scans all partitions. No pruning.

3. Manual partition creation

You create partitions monthly by hand. You forget. Inserts fail at midnight. Use pg_partman.

4. Too many partitions

100 daily partitions × 5 indexes each = 500 indexes for one table. Planner overhead climbs. Keep partition count <100 for most workloads.

5. Foreign keys + many partitions

Each partition validates the FK. Lots of partitions = slow inserts. Consider denormalizing or relaxing.

What I’d ship today

  • Range-partition time-series tables when they cross 100GB.
  • pg_partman to manage creation + retention.
  • Monthly for moderate volume; weekly/daily for high volume.
  • Validate pruning with EXPLAIN ANALYZE on representative queries.
  • Document the partition strategy in the repo so future engineers don’t break it.

Read this next

If you want my pg_partman setup + retention runbook, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .