---
title: "PostgreSQL partitioning in practice: partition keys, pruning, indexes and retention"
description: "How PostgreSQL partitioning behaves in practice: choosing the key and size, checking pruning with EXPLAIN, indexes, keys, retention and the Ecto gotchas."
author: Federico Meini
date: 2026-08-12
tags: [postgresql, partitioning, performance, elixir]
language: en
url: https://fedme.dev/blog/postgresql-partitioning-in-practice
---

# PostgreSQL partitioning in practice: partition keys, pruning, indexes and retention

PostgreSQL partitioning pays off when almost every query and every maintenance job lines up with one column, usually a timestamp. Queries then touch a few partitions, the indexes for recent data stay small, vacuum works on manageable pieces, and retention becomes dropping a table. It does nothing for a well-indexed lookup, and it makes every query that ignores the key slower. At Turn.io I partitioned production tables of more than 1 TB to get performance back as message data grew; this post collects the fundamentals I check before and after doing that.

Everything below was verified on PostgreSQL 18.6 against a 4-million-row `messages` table split into monthly range partitions, with version notes where behaviour differs. To move an existing large table without downtime, see [How to partition a 1 TB PostgreSQL table without downtime](https://fedme.dev/blog/partitioning-a-1tb-postgres-table-without-downtime).

## TL;DR

- Partition by the column nearly every query filters on, and never update it.
- Planning cost grows with the partitions a query *can't* prune, so keep the count modest unless your queries prune well.
- Check pruning with `EXPLAIN`: pruned partitions are missing from the plan, or counted in `Subplans Removed`.
- Keep the key a bare column compared with a parameter. Functions on it, and Ecto's `ago/2` on `timestamptz`, defeat plan-time pruning.
- Build indexes with `CREATE INDEX ON ONLY`, `CONCURRENTLY` per partition, then `ALTER INDEX ... ATTACH PARTITION`.
- Primary keys, unique constraints and referencing foreign keys must include the key.
- Retire data with `DETACH PARTITION ... CONCURRENTLY` (PostgreSQL 14+), which needs no default partition.
- Autovacuum never analyzes the parent. Run `ANALYZE` yourself.

## When partitioning helps, and when it doesn't

It helps with time-ordered, append-heavy data where most reads hit recent rows: the recent partitions' indexes fit in memory, old partitions stop changing and get frozen once, and deleting a month is `DROP TABLE` instead of a huge `DELETE` followed by vacuum.

It doesn't help if the real problem is a missing index or a bad plan, if the hot queries look rows up by something other than the key, or if the table is just "big". Every query without the key now visits every partition. Fix plans first; partitioning adds operational surface and should buy something concrete.

## Choosing the key and the partition size

Three rules for the key. Nearly every query must filter on it, because pruning compares it with values in the query. It must never change: updating it moves the row to another partition, a delete plus insert (I watched a row move from `messages_p2026_05` to `messages_p2026_10`). And it should spread writes predictably. For messages and events that means `RANGE (inserted_at)`, with bounds in UTC.

For size, the [docs](https://www.postgresql.org/docs/current/ddl-partitioning.html) say the planner handles "up to a few thousand partitions fairly well" when queries prune all but a few. Too few, and partitions stay too big to gain anything. Too many, and planning time and per-session memory grow, because each partition touched loads its metadata into that backend. With 1,000 daily partitions, a query pruned at plan time took 3 ms to plan on my laptop and one that couldn't be pruned took about 100 ms. Monthly is a sensible default for message data; go smaller only when a month is too big to vacuum or index comfortably.

## Range, list or hash

```sql
CREATE TABLE messages_p2026_03 PARTITION OF messages
  FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE by_region (region text NOT NULL, x int) PARTITION BY LIST (region);
CREATE TABLE by_region_eu PARTITION OF by_region FOR VALUES IN ('eu', 'uk');
CREATE TABLE by_region_other PARTITION OF by_region DEFAULT;

CREATE TABLE tenant_events (tenant_id int NOT NULL, payload text) PARTITION BY HASH (tenant_id);
CREATE TABLE tenant_events_0 PARTITION OF tenant_events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
```

Range suits time, with an inclusive lower bound and exclusive upper bound. List suits a small set of known values. Hash spreads writes evenly but prunes only on equality, doesn't help retention, and can't have a default ("a hash-partitioned table may not have a default partition").

## Partition pruning at plan time and at execution time

With literal values the planner prunes, and the other partitions simply aren't in the plan:

```sql
EXPLAIN (COSTS OFF)
SELECT count(*) FROM messages
WHERE contact_id = 42
  AND inserted_at >= '2026-03-01 00:00:00+00' AND inserted_at < '2026-04-01 00:00:00+00';
```

```text
 Aggregate
   ->  Index Only Scan using messages_p2026_03_contact_id_inserted_at_index on messages_p2026_03 messages
         Index Cond: ((contact_id = 42) AND (inserted_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND ...
```

With `now()` or a parameter in a generic plan, the value isn't known when planning, so Postgres prunes when execution starts and reports how many partitions it dropped:

```sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT count(*) FROM messages
WHERE contact_id = 42 AND inserted_at >= now() - interval '7 days';
```

```text
 Aggregate (actual rows=1.00 loops=1)
   ->  Append (actual rows=2.00 loops=1)
         Subplans Removed: 15
         ->  Index Only Scan using messages_p2026_09_contact_id_inserted_at_index on messages_p2026_09 messages_1 ...
         ->  Index Only Scan using messages_p2026_10_contact_id_inserted_at_idx on messages_p2026_10 messages_2 ...
         ->  Seq Scan on messages_p2026_11 messages_3 (actual rows=0.00 loops=1)
         ->  Seq Scan on messages_p2026_12 messages_4 (actual rows=0.00 loops=1)
```

A prepared statement forced to a generic plan (`SET plan_cache_mode = force_generic_plan`) gave the same `Subplans Removed: 15`. The third phase happens during execution, for values that change inside the query, such as the inner side of a nested loop. There, pruned partitions are shown as `(never executed)`, as the [docs](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING) describe.

`enable_partition_pruning` is on by default; turning it off is only useful to compare plans. One trap I hit: with it off, partitions still carrying old `CHECK` constraints were excluded anyway by `constraint_exclusion = partition`. Only after I dropped those redundant CHECKs did all 19 partitions show up.

## Queries without the partition key

They still work; they visit every partition. `SELECT * FROM messages WHERE id = 123456` produced 19 index scans, one per partition. That's fine occasionally, but a hot path should carry the timestamp.

Functions on the column hide it from pruning. Both of these scanned every partition:

```sql
SELECT count(*) FROM messages WHERE inserted_at::date = '2026-03-05';
SELECT count(*) FROM messages WHERE date_trunc('month', inserted_at) = '2026-03-01 00:00:00+00';
```

Write ranges instead: `inserted_at >= '2026-03-05' AND inserted_at < '2026-03-06'`.

## Planning cost and partitionwise joins

Planning scales with the partitions left after plan-time pruning. On my 1,000-partition test table:

| Query shape | Planning time |
| --- | --- |
| Literal or bound `timestamptz` range | 3 to 4 ms |
| No filter on the key | about 100 ms |
| `inserted_at >= now() - interval '1 day'` | about 95 to 130 ms |

PostgreSQL 18's release notes list faster planning for queries touching many partitions, but the shape of the problem is the same: prune early, or pay per partition.

`enable_partitionwise_join` and `enable_partitionwise_aggregate` are off by default because they make planning more expensive. They can help when you join two tables partitioned the same way on the key, or aggregate by it, but the planner still decides by cost: on my test table, turning the aggregate setting on didn't change the plan for `GROUP BY inserted_at` or `GROUP BY contact_id, inserted_at`. Enable them per session for the queries that need them, and check:

```sql
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;
EXPLAIN (COSTS OFF)
SELECT contact_id, inserted_at, count(*) FROM messages GROUP BY 1, 2;
```

## Indexes on partitioned tables

`CREATE INDEX` on the parent builds on every partition while blocking writes, and `CONCURRENTLY` is refused: "cannot create index on partitioned table "messages" concurrently". The workaround from the [docs](https://www.postgresql.org/docs/current/ddl-partitioning.html) is three steps; psql's `\gexec` runs each generated statement separately, so it works for `CONCURRENTLY`:

```sql
CREATE INDEX messages_direction_inserted_at_index ON ONLY messages (direction, inserted_at);

SELECT format('CREATE INDEX CONCURRENTLY %I ON %I (direction, inserted_at)',
              c.relname || '_direction_inserted_at_index', c.relname)
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'messages'::regclass \gexec

SELECT format('ALTER INDEX messages_direction_inserted_at_index ATTACH PARTITION %I',
              c.relname || '_direction_inserted_at_index')
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'messages'::regclass \gexec

SELECT indisvalid FROM pg_index
WHERE indexrelid = 'messages_direction_inserted_at_index'::regclass;
```

The parent index starts invalid (`f`) and turned valid (`t`) once the last partition's index was attached. New partitions get it automatically.

## Primary keys and unique constraints

Each partition enforces uniqueness only within itself, so Postgres requires every unique constraint to include the key: "unique constraint on partitioned table must include all partitioning columns". The primary key becomes `(id, inserted_at)`. Upserts follow: `ON CONFLICT (id)` fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification", and the target must be `(id, inserted_at)`. For a global dedup key such as a provider's message id, keep a small separate table with its own unique index.

## Foreign keys

Foreign keys *from* a partitioned table work as usual (PostgreSQL 11+). Foreign keys *to* one work from PostgreSQL 12, but must reference a unique key, which now includes the timestamp:

```sql
CREATE TABLE message_reactions (
  id bigserial PRIMARY KEY,
  message_id bigint NOT NULL,
  message_inserted_at timestamptz NOT NULL,
  emoji text NOT NULL,
  FOREIGN KEY (message_id, message_inserted_at) REFERENCES messages (id, inserted_at)
);
```

Referencing `messages (id)` alone fails with "there is no unique constraint matching given keys". Two more behaviours: detaching a partition that still has referenced rows fails ("removing partition "messages_p2025_06" violates foreign key constraint ..."), and PostgreSQL 18 adds `NOT VALID` foreign keys on partitioned tables, so you can validate them later without blocking writes.

## Retention with DETACH PARTITION CONCURRENTLY

```sql
ALTER TABLE messages DETACH PARTITION messages_p2025_06 CONCURRENTLY;
DROP TABLE messages_p2025_06;
```

Without `CONCURRENTLY`, detaching takes `ACCESS EXCLUSIVE` on the parent. With it (PostgreSQL 14+), [ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html) uses two transactions: `SHARE UPDATE EXCLUSIVE` on parent and partition, then a wait for every transaction using the table, then the final step. The restrictions (the first two errors are from my test):

- Inside a transaction block: "ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block".
- With a default partition: "cannot detach partitions concurrently when a default partition exists".
- Only one partition per table can be pending detach. If it's interrupted, finish with `DETACH PARTITION ... FINALIZE`.

It also adds a CHECK duplicating the old bound to the detached table (`messages_p2025_06_inserted_at_check` in my test), which is handy if you ever re-attach it. Compared with deleting millions of rows and vacuuming, it's trivial.

## Autovacuum and ANALYZE per partition

Autovacuum treats each partition as a table and never processes the parent. The [docs](https://www.postgresql.org/docs/current/routine-vacuuming.html) point out the consequence: nothing runs `ANALYZE` on a partitioned table, so run it yourself after loading data or when the distribution shifts. Since PostgreSQL 18, `ANALYZE ONLY messages` refreshes the parent's statistics without re-analyzing every partition.

Autovacuum settings live on partitions. `ALTER TABLE messages SET (autovacuum_vacuum_scale_factor = 0.01)` fails with "cannot specify storage parameters for a partitioned table", and `LIKE ... INCLUDING ALL` didn't copy a partition's autovacuum settings, so set them in whatever creates new partitions.

## Ecto and Elixir notes

Ecto doesn't care about the database primary key, so the schema can keep `id` as its primary key. But `Repo.get(Message, id)` scans every partition's index. Where you have the timestamp, use it: `Repo.get_by(Message, id: id, inserted_at: inserted_at)`. Upserts need `conflict_target: [:id, :inserted_at]`.

Watch how time filters are built. On a `timestamptz` column, `ago/2` compiles to `$2::timestamp + (-7::decimal::numeric * interval '1 day')`, and that cast to `timestamptz` isn't immutable, so pruning waits until execution: on my 1,000-partition table, planning took 95 to 145 ms instead of 3.7 ms. Pinning a computed `DateTime` fixes it. With Ecto's default `timestamp` columns, `ago/2` pruned at plan time.

```elixir
cutoff = DateTime.add(DateTime.utc_now(), -7, :day)

from m in Message,
  where: m.contact_id == ^contact_id and m.inserted_at >= ^cutoff,
  select: count()
```

Migrations can declare a partitioned table directly. This generated `PRIMARY KEY ("id","inserted_at") ... PARTITION BY RANGE (inserted_at)`:

```elixir
create table(:events, primary_key: false, options: "PARTITION BY RANGE (inserted_at)") do
  add :id, :bigserial, primary_key: true
  add :contact_id, :bigint, null: false
  add :payload, :map
  add :inserted_at, :utc_datetime_usec, primary_key: true
end
```

`DETACH ... CONCURRENTLY` and `CREATE INDEX CONCURRENTLY` must run outside a transaction: in a migration, set `@disable_ddl_transaction true` and `@disable_migration_lock true` (the [Safe Ecto Migrations](https://github.com/fly-apps/safe-ecto-migrations) guide covers the trade-offs). From application code such as an Oban job, call `Repo.query!/3` outside `Repo.transaction/1`; that worked in my test. For creating partitions ahead of time, the migration post has the Oban worker I use.

## If you're partitioning a table

Most partitioning problems show up in `EXPLAIN` long before they show up in production latency, and fixing them early is cheap. If your Postgres (or Elasticsearch) cluster is slowing down as data grows and you'd like help choosing keys, checking plans or planning a migration, [that's what I do](https://fedme.dev/services/postgres-elasticsearch).