The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Create a range-partitioned table | CREATE TABLE events (event_id bigint, occurred_at timestamptz NOT NULL) PARTITION BY RANGE (occurred_at); | View examples |
| Create a list-partitioned table | CREATE TABLE accounts (account_id bigint, region_code text NOT NULL) PARTITION BY LIST (region_code); | View examples |
| Create a hash-partitioned table | CREATE TABLE jobs (job_id bigint, tenant_id bigint NOT NULL) PARTITION BY HASH (tenant_id); | View examples |
| Create a monthly partition | CREATE TABLE events_2026_08 PARTITION OF events FOR
VALUES
FROM ('2026-08-01') TO ('2026-09-01'); | View examples |
| Create a default partition | CREATE TABLE events_default PARTITION OF events DEFAULT; | View examples |
| Create a list partition | CREATE TABLE accounts_americas PARTITION OF accounts FOR
VALUES IN ('BR', 'CA', 'US'); | View examples |
| Create a hash partition | CREATE TABLE jobs_h0 PARTITION OF jobs FOR
VALUES
WITH (MODULUS 4, REMAINDER 0); | View examples |
| Create matching child indexes | CREATE INDEX events_tenant_time_idx
ON events (tenant_id, occurred_at); | View examples |
| Declare partition-safe uniqueness | PRIMARY KEY (event_id, occurred_at) | View examples |
| Filter with matching range bounds | WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00' AND occurred_at < TIMESTAMPTZ '2026-09-01 00:00:00+00' | View examples |
| Inspect selected partitions | EXPLAIN (COSTS OFF)
SELECT count(*)
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'; | View examples |
| Check pruning configuration | SHOW enable_partition_pruning; | View examples |
| Build an attachable table | CREATE TABLE events_2026_09 (LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS); | View examples |
| Validate the intended bound | ALTER TABLE events_2026_09 VALIDATE CONSTRAINT events_2026_09_bound; | View examples |
| Attach a prepared partition | ALTER TABLE events ATTACH PARTITION events_2026_09 FOR
VALUES
FROM ('2026-09-01') TO ('2026-10-01'); | View examples |
| List a partition hierarchy | SELECT relid, parentrelid, isleaf, level
FROM pg_partition_tree('events'::regclass); | View examples |
| Inspect partition bounds | SELECT relname, pg_get_expr(relpartbound, oid)
FROM pg_class
WHERE relispartition
ORDER BY relname; | View examples |
| Analyze the partitioned parent | ANALYZE events; | View examples |
Partitioning is a physical design decision, not a universal speed switch. Use it when query predicates and data-retention boundaries consistently align with a key, then verify pruning with real plans. Production designs also need future partitions, parent statistics, indexes, uniqueness rules, and lock-aware attach or detach procedures before the first row arrives.
Step by step
Detailed examples
Partition only around a durable access boundary
Range partitioning fits ordered retention units such as months, list partitioning fits stable enumerations, and hash partitioning distributes a key when no natural ranges exist. Choose a key that appears directly in important predicates and groups data that is created, archived, or retired together. The parent is virtual storage: rows live in leaf partitions, and inserts without a matching partition fail unless a default partition exists.
CREATE TABLE events (
event_id bigint NOT NULL,
tenant_id bigint NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb NOT NULL,
PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);Make range boundaries contiguous and unambiguous
A range partition includes its FROM bound and excludes its TO bound, so adjacent partitions should share the same boundary. Use typed values and one time-zone convention for timestamptz keys. A default partition can protect ingestion from a missing future partition, but it should be monitored: rows in its proposed range can prevent a later partition from being attached, and PostgreSQL may need to scan it while validating new bounds.
CREATE TABLE events_2026_08
PARTITION OF events
FOR VALUES FROM ('2026-08-01 00:00:00+00')
TO ('2026-09-01 00:00:00+00');
CREATE TABLE events_default
PARTITION OF events DEFAULT;Use list and hash schemes for the right cardinality
List partitions work for controlled value sets, but a partition per fast-growing tenant can create excessive planning and session-memory overhead. Hash partitions trade human-readable ranges for even distribution; every remainder from zero through modulus minus one must be represented before all values can be routed. Changing the modulus later requires data movement, so test the expected scale first.
CREATE TABLE accounts (
account_id bigint NOT NULL,
region_code text NOT NULL
) PARTITION BY LIST (region_code);
CREATE TABLE accounts_americas
PARTITION OF accounts FOR VALUES IN ('BR', 'CA', 'US');
CREATE TABLE jobs (
job_id bigint NOT NULL,
tenant_id bigint NOT NULL
) PARTITION BY HASH (tenant_id);
CREATE TABLE jobs_h0
PARTITION OF jobs FOR VALUES WITH (MODULUS 4, REMAINDER 0);Design indexes per leaf and uniqueness across the tree
An index created on the partitioned parent is virtual, with matching physical indexes maintained on its partitions and future children. Pruning itself uses partition bounds, not indexes; index each leaf for the rows that remain after pruning. A UNIQUE or PRIMARY KEY constraint on a partitioned table must include every partition-key column, because PostgreSQL cannot enforce uniqueness between partitions when the key omits that boundary.
CREATE INDEX events_tenant_time_idx
ON events (tenant_id, occurred_at DESC);
SELECT indexrelid::regclass AS partitioned_index,
indrelid::regclass AS partitioned_table,
indisvalid
FROM pg_index
WHERE indrelid = 'events'::regclass;Verify pruning from predicates, not assumptions
Partition pruning removes children whose bounds cannot satisfy a predicate. Write conditions directly against the partition key with compatible types and boundary logic, then inspect EXPLAIN under representative literals or parameters. Pruning can occur during planning, plan initialization, or execution; EXPLAIN may report Subplans Removed, and EXPLAIN ANALYZE can mark repeatedly pruned children as never executed. Casting or transforming the partition key can make the relationship harder to prove.
SHOW enable_partition_pruning;
PREPARE event_count(timestamptz, timestamptz) AS
SELECT count(*)
FROM events
WHERE occurred_at >= $1
AND occurred_at < $2;
EXPLAIN (COSTS OFF)
EXECUTE event_count(
TIMESTAMPTZ '2026-08-01 00:00:00+00',
TIMESTAMPTZ '2026-09-01 00:00:00+00'
);Prepare and validate partitions before attachment
Building a standalone table lets a deployment create indexes, load data, and validate the intended range before making it visible through the parent. A valid CHECK constraint matching the new bound lets PostgreSQL skip a full validation scan during ATTACH PARTITION. Attachment uses a SHARE UPDATE EXCLUSIVE lock on the parent, while the table being attached is locked more strongly; rehearse the complete operation and lock budget. A default partition may also need an exclusion constraint to avoid a validation scan.
CREATE TABLE events_2026_09
(LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE events_2026_09
ADD CONSTRAINT events_2026_09_bound
CHECK (occurred_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
AND occurred_at < TIMESTAMPTZ '2026-10-01 00:00:00+00')
NOT VALID;
ALTER TABLE events_2026_09
VALIDATE CONSTRAINT events_2026_09_bound;
ALTER TABLE events
ATTACH PARTITION events_2026_09
FOR VALUES FROM ('2026-09-01 00:00:00+00')
TO ('2026-10-01 00:00:00+00');Audit the hierarchy and bounds from catalogs
Treat the partition map as production state that can drift from the calendar or deployment plan. pg_partition_tree reports the complete hierarchy, including subpartitions, while pg_get_expr renders each child's stored bound. Run read-only audits ahead of boundary changes to detect missing periods, unexpected default routing, excessive depth, and incorrectly named children.
SELECT t.relid::regclass AS relation,
t.parentrelid::regclass AS parent,
t.level,
t.isleaf,
pg_get_expr(c.relpartbound, c.oid) AS bound,
pg_size_pretty(pg_total_relation_size(t.relid)) AS total_size
FROM pg_partition_tree('events'::regclass) AS t
JOIN pg_class AS c ON c.oid = t.relid
ORDER BY t.level, t.relid::regclass::text;Maintain parent statistics and cap partition count
Autovacuum processes ordinary leaf tables, but changes in children do not trigger autoanalyze on a partitioned parent. Run ANALYZE on the parent when whole-tree queries depend on inherited statistics, especially after bulk loads or partition rotation. More partitions are not automatically better: children that survive pruning add planning work, and sessions consume memory for metadata they touch. Test the intended workload and retention horizon rather than choosing granularity by habit.
ANALYZE events;
SELECT relid::regclass AS leaf,
n_live_tup,
n_dead_tup,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relid IN (
SELECT relid
FROM pg_partition_tree('events'::regclass)
WHERE isleaf
)
ORDER BY relid::regclass::text;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: Table Partitioningpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: CREATE TABLEpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: ALTER TABLEpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: System Information Functions and Operatorspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Routine Vacuumingpostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.