Working with SQL Partitioning
1. Understanding Partitioning Benefits
| Benefit | How |
|---|---|
| Partition pruning | Planner skips irrelevant partitions |
| Faster maintenance | DETACH partition for fast bulk archive |
| Index size | Smaller per-partition indexes fit in memory |
| Parallel ops | Vacuum/analyze per partition |
| Lifecycle | DROP old partitions in O(1) |
2. Creating Range Partitions
Example: Postgres Range Partitioning
CREATE TABLE events (
id SERIAL, ts TIMESTAMPTZ NOT NULL, payload JSONB
) PARTITION BY RANGE (ts);
CREATE TABLE events_2026_05 PARTITION OF events
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE events_2026_06 PARTITION OF events
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');| Use | Example |
|---|---|
| Time-based | One partition per month/day |
| Numeric range | id ranges for sharding |
| Default partition | Catches rows outside defined ranges |
3. Creating List Partitions
| Property | Detail |
|---|---|
| Discrete values | e.g., region IN ('US','EU','APAC') |
| Syntax (PG) | FOR VALUES IN ('US','CA') |
| Use | Geographic, status, tenant grouping |
4. Creating Hash Partitions
| Property | Detail |
|---|---|
| Even distribution | Hash of key % modulus |
| Syntax (PG) | FOR VALUES WITH (MODULUS 8, REMAINDER 0..7) |
| Use | Write hotspot avoidance, balanced shards |
| Limitation | No range pruning, must include hash key in queries |
5. Creating Composite Partitions
| Strategy | Example |
|---|---|
| Range + Hash | Range by month, sub-hash by user_id |
| List + Range | List by region, sub-range by date |
| Sub-partition | Multi-level partition tree |
6. Querying Partitioned Tables
| Tip | Detail |
|---|---|
| Include partition key in WHERE | Enables pruning |
| EXPLAIN | Verify only relevant partitions scanned |
| Constraint exclusion (PG) | SET constraint_exclusion = partition; (legacy) |
| Cross-partition queries | Slow — scan all partitions |
7. Managing Partitions
| Operation | Syntax (PG) |
|---|---|
| Attach | ALTER TABLE parent ATTACH PARTITION child FOR VALUES ...; |
| Detach | ALTER TABLE parent DETACH PARTITION child; |
| Drop | DROP TABLE child; |
| Tools | pg_partman (automation) |
8. Indexing Partitioned Tables
| Aspect | Detail |
|---|---|
| Partitioned index (PG 11+) | Create on parent; auto-propagates |
| Local index | Per-partition, can use different strategies |
| Global index (Oracle) | One index across all partitions |
| Unique across partitions | Must include partition key in PG |
9. Maintaining Partition Statistics
| Task | Detail |
|---|---|
| ANALYZE per partition | Important for accurate planning |
| Autovacuum tuning | Per-partition thresholds for hot partitions |
| Incremental stats (Oracle) | Only re-stat changed partitions |
10. Migrating Data Between Partitions
| Operation | Approach |
|---|---|
| Move row across partitions | UPDATE key column triggers move (PG 11+) |
| Reorg | Detach, rewrite, re-attach |
| Archive | Detach + move to cold storage |
| Split / merge partitions | Custom SQL or pg_partman |