Table Partition Planner

Demo

Click to play with sound.

---
name: Table Partition Planner
description: Produces a partitioning or sharding plan for an oversized table — range/list/hash strategy, partition-key choice, composite-key and pruning constraints, and a verdict on whether to partition at all. Use when one table has grown past tens of millions of rows or hundreds of GB, vacuum/autovacuum on it runs for hours, retention deletes are giant DELETEs, or you are deciding between range/list/hash partitioning or sharding a hot table. Do NOT use when the goal is choosing indexes for specific slow queries — use index-advisor instead.
---
# Table Partition Planner

Plan how to split one too-large table into partitions (or shards) keyed to its real access and retention patterns — or prove it should stay a single indexed table.

## Workflow

1. **Confirm partitioning is warranted.** Get the row count and on-disk size. Below roughly 50-100M rows or a few hundred GB, a well-indexed single table usually beats a partitioned one; recommend staying single unless retention or vacuum pain forces the issue. Never partition to compensate for a missing index — that is index-advisor's job; add the index first and re-measure.
2. **Characterize the access pattern.** Capture the hot queries' WHERE clauses, the retention/deletion policy, and write distribution. The partition key must come from this evidence, not a guess.
3. **Pick the strategy from that pattern.**
   - **Range** — time-series or any naturally ordered key (e.g. `created_at` by month). Old data leaves via `DROP`/`DETACH PARTITION` instead of a giant `DELETE`.
   - **List** — a small fixed set of discrete values (region, tenant tier).
   - **Hash** — no natural range but you must cap any single child's size; use a fixed modulus to spread writes evenly.
4. **Choose the partition key.** It must appear in the WHERE clause of hot queries (or the planner cannot prune and scans every child), align with the retention policy (drop by time), and align with the largest indexes. Reject any column that is frequently updated — moving a row across partitions on UPDATE is expensive.
5. **Resolve the constraint and operational requirements.**
   - In Postgres declarative partitioning, every unique constraint and primary key must include the partition key — design composite keys accordingly.
   - Automate partition creation (pg_partman or a scheduled job) so writes never hit a missing partition.
   - Keep partition count bounded (low hundreds, not thousands) to avoid planning bloat.
   - Verify foreign-key behavior against your engine version's partitioning limits.
6. **Decide partition vs shard.** Partitioning stays on one node. Reach for sharding (Citus, Vitess, or app-level routing) only when a single node's write throughput or storage is the hard ceiling — it adds distributed-transaction and rebalancing cost. If sharding, pick a shard key with even distribution and no cross-shard joins on the hot path.
7. **Write the plan.** State chosen strategy, key, composite-key changes, creation/automation mechanism, retention operation, and the migration approach to convert the existing table.