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
EXPLAINproof 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
timestamptzUTC. Pruning bounds must match stored representation. - Ban functions on partition key in WHERE. No
date_truncwrapper in production SQL templates. - Run
EXPLAINin 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.
Related
- 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+.