Declarative Partitioning DDL

Parent/child tables and constraint exclusion let PostgreSQL route inserts and prune scans without manual trigger-based routing.

Recipe

Quick-reference recipe card - copy-paste ready.

CREATE TABLE measurements (
  sensor_id   integer NOT NULL,
  measured_at timestamptz NOT NULL,
  value       double precision NOT NULL,
  PRIMARY KEY (sensor_id, measured_at)
) PARTITION BY RANGE (measured_at);
 
CREATE TABLE measurements_2026_q1 PARTITION OF measurements
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
 
CREATE TABLE measurements_2026_q2 PARTITION OF measurements
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
 
ALTER TABLE measurements ADD CONSTRAINT measurements_value_check
  CHECK (value >= 0);

When to reach for this:

• Tables exceeding manageable vacuum/backup windows as a single heap.
• Queries consistently filter on a partition key (time, tenant, region).
• Retention policy drops or archives whole time windows.

Working Example

BEGIN;
 
CREATE TABLE audit_log (
  log_id      bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id   bigint NOT NULL,
  logged_at   timestamptz NOT NULL DEFAULT now(),
  action      text NOT NULL,
  details     jsonb,
  PRIMARY KEY (log_id, tenant_id, logged_at)
) PARTITION BY RANGE (logged_at);
 
CREATE TABLE audit_log_2026_h1 PARTITION OF audit_log
  FOR VALUES FROM ('2026-01-01') TO ('2026-07-01');
 
CREATE TABLE audit_log_2026_h2 PARTITION OF audit_log
  FOR VALUES FROM ('2026-07-01') TO ('2027-01-01');
 
CREATE INDEX audit_log_tenant_logged_idx
  ON audit_log (tenant_id, logged_at DESC);
 
-- Insert routes to correct child automatically
INSERT INTO audit_log (tenant_id, action, details)
VALUES (42, 'login', '{"ip": "10.0.0.1"}');
 
-- Inspect partition tree
SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = 'audit_log'::regclass;
 
COMMIT;

What this demonstrates:

• Declarative PARTITION BY on parent with bound-defined children.
• Composite PK includes partition key columns (logged_at).
• Indexes created on parent exist on each partition.

Deep Dive

How It Works

• Parent is a partitioned table - inserts and queries target parent; planner expands to children.
• Each child inherits column definitions; bounds stored as partition constraints.
• Constraint exclusion (partition pruning) skips children whose bounds cannot match the query predicate.
• ONLY parent syntax addresses parent without children for DDL edge cases.

DDL Operations

SQL Notes

-- Template for monthly automation
DO $$
DECLARE
  start_date date := date_trunc('month', now())::date;
  end_date   date := (start_date + interval '1 month')::date;
  part_name  text := 'events_' || to_char(start_date, 'YYYY_MM');
BEGIN
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
    part_name, start_date, end_date
  );
END;
$$;

Gotchas

• PK without partition key - DDL fails on partitioned tables. Fix: include partition column in PK and UNIQUE constraints.
• Overlapping bounds - attach fails or causes ambiguous routing. Fix: half-open ranges, automated partition creation.
• Too many partitions - thousands of daily partitions hurt planning. Fix: weekly/monthly partitions; see best practices.
• Global UNIQUE without partition key - not supported across partitions. Fix: include partition key or use surrogate id per partition scope.
• FOR EACH ROW trigger on parent only - behavior differs from non-partitioned tables. Fix: test triggers on each child or use application logic.

Alternatives

FAQs

SELECT c.relname, pg_get_expr(c.relpartbound, c.oid)
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'audit_log'::regclass;

Related

• Partitioning Basics - range/list/hash intro
• Partition Pruning - planner behavior
• Retention & Detach - lifecycle management
• Partitioning Pitfalls - common mistakes

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.