Partitioning Best Practices

Q: How do I name partitions?

events_2026_03 for March 2026 - sortable, grep-friendly in ops scripts.

Q: What metrics trigger partition review?

pg_class child count, EXPLAIN planning time, autovacuum duration on parent, insert errors at month boundary.

Q: Should foreign tables hold archives?

Optional pattern: detach, dump to parquet, query via postgres_fdw or external engine for rare audits.

Plan partition count; avoid daily partitions forever. Partition for prune-friendly access patterns and operable retention, not because the table "feels big."

How to Use This List

Complete before partitioning a table over 50M rows.
Revisit when plan time or partition count crosses alerting thresholds.
Every production partitioned table needs an owner for partition automation cron.
Store EXPLAIN proof of pruning in the table's runbook.

A - Strategy Selection

Partition on predicates queries always use. Time (occurred_at), tenant (tenant_id), or region - not arbitrary IDs.
Prefer monthly or weekly range partitions for time-series. Daily only at extreme volume with automation and monitoring.
Cap active partition count under a few hundred. Thousands of children degrade planning and catalog queries.
Document retention policy per partitioned table. Detach schedule, backup requirement, legal hold exceptions.
Prove need with size and vacuum metrics. Partitioning is not a substitute for missing indexes.

B - DDL and Keys

Include partition key in PRIMARY KEY and UNIQUE. Plan global uniqueness via side tables if needed.
Automate future partition creation (N+2 ahead). Never rely on manual midnight DDL.
Index partition key leading columns matching query filters. (tenant_id, occurred_at DESC) for tenant-time queries.
Avoid default partition in steady state. Use only during migration; alert on default growth.
Test ATTACH/DETACH on staging clones with production row counts. Validate lock duration before prod.

C - Query and Application

Require partition key filter in hot-path queries. Repository layer rejects unbounded time scans in OLTP.
Store timestamps as timestamptz UTC. Pruning bounds must match stored representation.
Ban functions on partition key in WHERE. No date_trunc wrapper in production SQL templates.
Run EXPLAIN in CI for canonical queries. Assert expected child partition names appear.
Educate BI tools to pass time windows. Ad-hoc full-table reports belong on replica or warehouse.

D - Operations

Use DETACH + DROP for retention, not bulk DELETE. Protect autovacuum and replication lag.
Dump detached partitions before DROP when compliance requires. Verify backup restore quarterly.
Monitor partition count, default partition size, and plan time p95. Alert before users notice latency.
ANALYZE parent after bulk load to a new child. Stale stats cause bad plans across partitions.
Document reattach/restore procedure. Ops can recover one month without full cluster restore.

FAQs

What is a good first table to partition?

Append-only event or audit logs with time-bounded queries and 90-day retention - clear key, clear prune, clear detach.

Monthly vs weekly partitions?

Weekly when single-month partitions exceed comfortable size (roughly 50-100GB+) or retention is weekly. Otherwise monthly reduces catalog overhead.

Should indexes be local or global?

PostgreSQL uses per-child physical indexes created via parent DDL. There is no separate "global" index across partitions without partition key.

How do I name partitions?

events_2026_03 for March 2026 - sortable, grep-friendly in ops scripts.

Can partitioning replace sharding?

No. Partitioning is single-node (or single primary). Multi-node write scale needs Citus, application sharding, or split databases.

What metrics trigger partition review?

pg_class child count, EXPLAIN planning time, autovacuum duration on parent, insert errors at month boundary.

Is hash partitioning good for time-series?

Rarely. Hash spreads writes but time-range reports scan all partitions. Prefer range with time filter.

How does partitioning interact with PgBouncer?

Transparent - pooling does not affect pruning. Ensure prepared statements still pass partition-friendly parameters.

Should foreign tables hold archives?

Optional pattern: detach, dump to parquet, query via postgres_fdw or external engine for rare audits.

When is partitioning premature?

Small dimension tables, heavily updated rows scattered across time, or queries without partition key filters.

Partitioning Basics - introductory examples
Partition Pruning - prove scan reduction
Retention & Detach - lifecycle ops
Partitioning Pitfalls - mistakes to avoid

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

Partitioning Best Practices

Plan partition count; avoid daily partitions forever. Partition for prune-friendly access patterns and operable retention, not because the table "feels big."

How to Use This List

Complete before partitioning a table over 50M rows.
Revisit when plan time or partition count crosses alerting thresholds.
Every production partitioned table needs an owner for partition automation cron.
Store EXPLAIN proof of pruning in the table's runbook.

A - Strategy Selection

Partition on predicates queries always use. Time (occurred_at), tenant (tenant_id), or region - not arbitrary IDs.
Prefer monthly or weekly range partitions for time-series. Daily only at extreme volume with automation and monitoring.
Cap active partition count under a few hundred. Thousands of children degrade planning and catalog queries.
Document retention policy per partitioned table. Detach schedule, backup requirement, legal hold exceptions.
Prove need with size and vacuum metrics. Partitioning is not a substitute for missing indexes.

B - DDL and Keys

Include partition key in PRIMARY KEY and UNIQUE. Plan global uniqueness via side tables if needed.
Automate future partition creation (N+2 ahead). Never rely on manual midnight DDL.
Index partition key leading columns matching query filters. (tenant_id, occurred_at DESC) for tenant-time queries.
Avoid default partition in steady state. Use only during migration; alert on default growth.
Test ATTACH/DETACH on staging clones with production row counts. Validate lock duration before prod.

C - Query and Application

Require partition key filter in hot-path queries. Repository layer rejects unbounded time scans in OLTP.
Store timestamps as timestamptz UTC. Pruning bounds must match stored representation.
Ban functions on partition key in WHERE. No date_trunc wrapper in production SQL templates.
Run EXPLAIN in CI for canonical queries. Assert expected child partition names appear.
Educate BI tools to pass time windows. Ad-hoc full-table reports belong on replica or warehouse.

D - Operations

Use DETACH + DROP for retention, not bulk DELETE. Protect autovacuum and replication lag.
Dump detached partitions before DROP when compliance requires. Verify backup restore quarterly.
Monitor partition count, default partition size, and plan time p95. Alert before users notice latency.
ANALYZE parent after bulk load to a new child. Stale stats cause bad plans across partitions.
Document reattach/restore procedure. Ops can recover one month without full cluster restore.

FAQs

What is a good first table to partition?

Append-only event or audit logs with time-bounded queries and 90-day retention - clear key, clear prune, clear detach.

Monthly vs weekly partitions?

Weekly when single-month partitions exceed comfortable size (roughly 50-100GB+) or retention is weekly. Otherwise monthly reduces catalog overhead.

Should indexes be local or global?

PostgreSQL uses per-child physical indexes created via parent DDL. There is no separate "global" index across partitions without partition key.

How do I name partitions?

events_2026_03 for March 2026 - sortable, grep-friendly in ops scripts.

Can partitioning replace sharding?

No. Partitioning is single-node (or single primary). Multi-node write scale needs Citus, application sharding, or split databases.

What metrics trigger partition review?

pg_class child count, EXPLAIN planning time, autovacuum duration on parent, insert errors at month boundary.

Is hash partitioning good for time-series?

Rarely. Hash spreads writes but time-range reports scan all partitions. Prefer range with time filter.

How does partitioning interact with PgBouncer?

Transparent - pooling does not affect pruning. Ensure prepared statements still pass partition-friendly parameters.

Should foreign tables hold archives?

Optional pattern: detach, dump to parquet, query via postgres_fdw or external engine for rare audits.

When is partitioning premature?

Small dimension tables, heavily updated rows scattered across time, or queries without partition key filters.

Partitioning Basics - introductory examples
Partition Pruning - prove scan reduction
Retention & Detach - lifecycle ops
Partitioning Pitfalls - mistakes to avoid

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

Partitioning Best Practices

Search Documentation

Partitioning Best Practices