LLM Best Practices

Postgres: Partitioning

4 min read

View markdown source

Overview

Partition huge tables, not medium ones. Postgres 17 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 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.

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.

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 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.

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.

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 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 postgres-indexes for the BRIN pairing on partitioned time-series and migrations for the rollout discipline.

Graph View

Backlinks