Partitioning Basics

8 examples to get you started with Partitioning - 5 basic and 3 intermediate.

Prerequisites

• PostgreSQL 18.4 cluster with DDL privileges.
• Understanding of your query filters (WHERE clauses) and retention policy.

Basic Examples

1. Range Partition by Month

Split time-series events by calendar month - the most common pattern.

CREATE TABLE events (
  event_id    bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at timestamptz NOT NULL,
  payload     jsonb NOT NULL,
  PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);
 
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
 
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

• Parent table holds no rows - data lives in child partitions.
• Partition key (occurred_at) must appear in PRIMARY KEY and UNIQUE constraints.
• Range bounds are half-open: FROM inclusive, TO exclusive.

Related: Declarative Partitioning DDL - parent/child setup

2. List Partition by Region

Route rows to regional partitions when queries filter on a discrete set.

CREATE TABLE orders (
  order_id  bigint GENERATED ALWAYS AS IDENTITY,
  region    text NOT NULL,
  amount    numeric(12, 2) NOT NULL,
  PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);
 
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu', 'uk');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('apac');

• List partitioning fits low-cardinality enum-like keys.
• Group low-volume regions into one partition to avoid empty children.
• Queries with WHERE region = 'eu' prune to orders_eu.

Related: Partition Pruning - prove plans skip partitions

3. Hash Partition for Even Spread

Distribute rows when no natural range or list filter exists.

CREATE TABLE sessions (
  session_id  uuid NOT NULL,
  tenant_id   bigint NOT NULL,
  created_at  timestamptz NOT NULL,
  PRIMARY KEY (session_id, tenant_id)
) PARTITION BY HASH (tenant_id);
 
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

• Hash spreads write load across fixed partition count.
• Modulus must match total hash partition count.
• Pruning requires WHERE tenant_id = ? (or IN list), not range scans across all tenants.

4. Default Partition Catch-All

Capture rows that do not match explicit partitions during rollout.

CREATE TABLE events_default PARTITION OF events DEFAULT;
 
INSERT INTO events (occurred_at, payload)
VALUES ('2027-05-15', '{"type": "signup"}');
-- lands in events_default until you add events_2027_05

• Default partition prevents insert failures for unmapped keys.
• Monitor default partition growth - it disables optimal pruning for those rows.
• Replace with explicit partitions and migrate rows out of default.

Related: Partitioning Pitfalls - default partition traps

5. Indexes on Partitioned Tables

Indexes on the parent propagate to existing and future children.

CREATE INDEX events_occurred_at_idx ON events (occurred_at);
 
CREATE INDEX events_payload_gin_idx ON events USING gin (payload);

• Create indexes on parent for consistent DDL across partitions.
• Each child stores its own physical index files.
• Align indexes with prune-friendly predicates in WHERE.

Intermediate Examples

6. Attach Preloaded Historical Partition

Load data offline, then attach as a partition for fast cutover.

CREATE TABLE events_2025_12 (
  event_id    bigint GENERATED BY DEFAULT AS IDENTITY,
  occurred_at timestamptz NOT NULL,
  payload     jsonb NOT NULL
);
 
COPY events_2025_12 (occurred_at, payload) FROM '/tmp/events_2025_12.csv' CSV;
 
ALTER TABLE events ATTACH PARTITION events_2025_12
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

• ATTACH validates bounds against parent constraint.
• Pre-create CHECK constraints on standalone table before attach for faster validation.
• Useful for backfills and archival imports.

Related: Retention & Detach - detach and drop old data

7. Prove Pruning with EXPLAIN

Confirm the planner scans only relevant partitions.

EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM events
WHERE occurred_at >= '2026-01-15' AND occurred_at < '2026-02-01';

• Look for Partitions pruned: N or child scan nodes for events_2026_01 only.
• Without prune-friendly predicates, every partition is scanned.
• Run after statistics update: ANALYZE events;

Related: Partition Pruning - pruning rules

8. Detach Old Partition for Retention

Remove a month from the live table without deleting row-by-row.

ALTER TABLE events DETACH PARTITION events_2025_01;
 
-- Optional: archive detached table, then drop
ALTER TABLE events_2025_01 RENAME TO events_archive_2025_01;

• DETACH turns the child into a standalone table instantly.
• Drop or archive the detached table to reclaim space.
• Far faster than DELETE FROM events WHERE occurred_at < ... on billion-row tables.

Related: Retention & Detach - archive workflows

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

NzA2MjAyfDU5OW9pZ2N8b2kuc2VkaXVnZWRvYw==