---
title: "Postgres: Partitioning"
slug: "postgres-partitioning"
category: "backend"
tags: ["backend", "postgres", "partitioning", "scaling", "time-series"]
status: "stable"
last_updated: 2026-08-14
summary: "Range-partition huge time-series tables, automate with pg_partman, and avoid partitioning small tables where the planner overhead outweighs the win."
related:
  [
    "[[backend/postgres]]",
    "[[backend/postgres-indexes]]",
    "[[backend/postgres-vacuum]]",
    "[[backend/postgres-explain]]",
    "[[backend/migrations]]",
  ]
---

## Overview

Partition huge tables, not medium ones. Postgres 18 ships declarative partitioning: a parent table with no rows and child partitions that hold the data. The planner prunes partitions at query time. This page covers the three partition strategies, when each fits, the `pg_partman` workflow, and the foreign-key caveats. The umbrella rules live in [[backend/postgres]].

## Pick range partitioning for time-series and ordered data

Range partitioning splits the table by a continuous column, usually a timestamp. Each child holds one month, one day, or one ID range. Range is the right choice when queries filter on the partition key and rows arrive in order.

```sql
CREATE TABLE events (
  id bigserial,
  created_at timestamptz NOT NULL,
  user_id bigint NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
```

Note the primary key includes the partition column. Postgres requires every unique constraint to contain the partition key, because uniqueness is enforced per partition.

## Pick list partitioning for known categorical splits

List partitioning maps a column's discrete values to partitions. Use it for region codes, tenant IDs in a small multi-tenant system, or status enums where one bucket is hot.

```sql
CREATE TABLE orders (id bigserial, region text NOT NULL, ...)
  PARTITION BY LIST (region);

CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('de', 'fr', 'es');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;
```

Always declare a `DEFAULT` partition so an unknown value does not fail the insert. List partitioning is wrong when the value set is large or unknown; reach for hash instead.

## Pick hash partitioning for even write spread

Hash partitioning splits rows by `hashfunc(key) MOD N`. Use it to spread write load across N partitions when the natural key is skewed. The planner only prunes equality on the hash key, so hash loses pruning on range queries. Pick it for write-heavy workloads with point lookups by key, not for analytics scans.

## Use one partition per month for time-series

Monthly partitions are the right default for events, logs, audits, and metrics. They keep partition counts manageable (under 100 active partitions), make `DROP TABLE` the retention policy, and align with most reporting windows.

- Sub-monthly partitions (daily, weekly) make sense above a billion rows per month.
- Yearly partitions are too coarse; the parent grows past the size where vacuum and indexes stay cheap; see [[backend/postgres-vacuum]].
- Pre-create the next two months at the end of the current month; an insert with no matching partition fails.

## Automate the lifecycle with pg_partman

`pg_partman` pre-creates future partitions on a schedule and drops old ones past a retention boundary.

```sql
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => '1 month',
  p_premake => 3
);
```

Run `SELECT partman.run_maintenance_proc()` from cron or a background worker. Set retention via `partman.part_config`. Without `pg_partman`, an insert with no matching partition fails; pre-create by hand and you will forget.

## Lean on constraint exclusion for pruning

Modern partition pruning is automatic for queries that include the partition key with a constant or stable expression. The planner removes irrelevant partitions during planning and execution.

```sql
EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-08';
-- Append node visits only events_2026_05
```

Validate pruning with [[backend/postgres-explain]]; an `Append` node that lists every partition means the predicate did not allow pruning. Volatile expressions (`now() - interval '1 day'`) sometimes block pruning; bind the timestamp in the application or use a stable function wrapper.

## Know the foreign-key caveats

Foreign keys from a partitioned table to another table work since Postgres 12. Foreign keys pointing into a partitioned table are supported since Postgres 15.

- A unique constraint on a partitioned table must include the partition key. That breaks the natural pattern of "globally unique id with monthly partitioning"; carry the partition key in the FK target.
- A row cannot move across partitions on update unless you set the partition key in the `UPDATE`; the engine deletes from the old partition and inserts into the new one.
- `ON DELETE CASCADE` from a partitioned parent works; verify behavior with a test before relying on it.

## Do not partition small tables

The planner pays a per-partition cost on every query. Below a few million rows the overhead outweighs the win.

- Partition when the table exceeds 100 GB or 100 million rows, or when retention requires `DROP TABLE` style pruning.
- Do not partition lookup tables, configuration tables, or low-churn entities; index them and move on.
- A premature partition scheme is hard to undo; merging partitions back into one table requires a rewrite.

See [[backend/postgres-indexes]] for the BRIN pairing on partitioned time-series and [[backend/migrations]] for the rollout discipline.

## Related

- [[backend/postgres]]
- [[backend/postgres-indexes]]
- [[backend/postgres-vacuum]]
- [[backend/postgres-explain]]
- [[backend/postgres-replication]]
- [[backend/migrations]]