One Table Many Pieces
Declarative partitioning lets you split a large table into smaller physical tables called partitions while queries still treat it as one table. The database routes each row to the right partition by a key you choose.
Partition Strategies
- Range partitioning groups rows by a range, such as one partition per month of dates.
- List partitioning groups by a fixed set of values, such as one partition per region.
- Hash partitioning spreads rows evenly across a number of partitions using a hash of the key.
Why It Helps
- Partition pruning lets a query skip partitions that cannot match its filter, scanning far less data.
- Dropping old data is a fast detach or drop of a whole partition instead of a slow bulk delete.
- Each partition can be vacuumed and indexed on its own.
Watch Out
Every query should filter on the partition key to benefit, indexes are usually per partition, and too many partitions add planning overhead.
Key idea
Declarative partitioning splits a big table by a key so queries prune irrelevant partitions and old data drops fast, as long as queries filter on the partition key.