LLM Best Practices

Postgres: Partitioning

Last updated 4 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Partition tables that are very large or that need retention by dropping old data; do not partition medium tables. A partitioned table has no rows of its own; child partitions hold the data and the planner prunes the ones a query cannot touch. Pick range partitioning for time-series data, automate the lifecycle with pg_partman, and verify pruning with EXPLAIN. The umbrella rules live in postgres.

Partition when the table is large or retention needs it

The Postgres docs give a rule of thumb: partitioning pays off when the table would otherwise exceed the physical memory of the server. Partition earlier when retention must be a DROP, or when vacuum on one huge table cannot keep up (postgres-vacuum). Do not partition lookup, configuration, or low-churn tables; each partition adds planning cost, and the planner handles up to a few thousand partitions fairly well. Merging partitions back into one table later requires a rewrite.

Pick range partitioning for time-series data

Range partitioning splits on a continuous column, usually a timestamp. Use it when queries filter on that column and rows arrive in order. Monthly partitions are a sensible default for events, logs, and audits.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  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_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Every primary key or unique constraint on a partitioned table must include all partition key columns, and the key cannot use expressions. Uniqueness is enforced per partition, so “globally unique id” needs the partition column in the key. An insert with no matching partition fails unless a DEFAULT partition exists.

Pick list or hash partitioning for non-time splits

  • List: map discrete values (region, a small set of tenants) to partitions. Add a DEFAULT partition for unknown values. Avoid it when the value set is large or unbounded.
  • Hash: PARTITION BY HASH (key) spreads writes evenly across N partitions. Pruning works only for equality on the key, so use it for point lookups, not range scans.

A DEFAULT partition has a cost: attaching a new partition scans the default under an ACCESS EXCLUSIVE lock unless a CHECK constraint proves it holds no matching rows. Add that constraint before attaching.

Automate the lifecycle with pg_partman

pg_partman 5.x creates future partitions and removes old ones. It requires Postgres 14 or later and native partitioning only.

CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'created_at',
  p_interval     => '1 month',
  p_premake      => 4
);
 
UPDATE partman.part_config
SET retention = '12 months', retention_keep_table = false
WHERE parent_table = 'public.events';

Call partman.run_maintenance_proc() from pg_cron, an external scheduler, or the background worker; nothing creates partitions until maintenance runs. retention_keep_table defaults to true, which only detaches expired partitions and keeps the tables. Set it to false to drop them.

Rely on partition pruning, and verify it

Pruning is automatic when the query filters on the partition key. Constants and stable expressions such as now() - interval '7 days' are pruned at executor startup; volatile functions are not.

EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-08';
-- Append node scans only events_2026_10

Check with postgres-explain: an Append over every partition means the predicate did not allow pruning, and Subplans Removed shows what startup pruning dropped.

Know the row and key behavior

  • Updating the partition key moves the row to the matching partition automatically.
  • Foreign keys can reference partitioned tables and originate from them; the referenced unique key must include the partition key.
  • Exclusion constraints must include the partition key columns and compare them for equality.
  • Detach old data without blocking: ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY;, then drop or archive it.

See postgres-indexes for BRIN on partitioned time-series and migrations for rollout discipline.

Backlinks