PostgreSQL interview question · Most asked
How does declarative partitioning work in PostgreSQL, and when should you use it?
Summary
A partitioned table is split into child tables by RANGE, LIST or HASH on a key. Queries filtering on the key touch only relevant partitions (pruning), and old data can be removed by detaching or dropping a partition instantly. It suits very large, time-based or tenant-based tables.
Longer Answer
Benefits: fast retention (DROP TABLE events_2024_01), smaller per-partition indexes, vacuuming per partition, and pruning. Costs: primary keys and unique constraints must include the partition key; queries that do not filter on the key scan every partition; thousands of partitions increase planning time. Create future partitions ahead of time (pg_partman automates this), and use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) to detach without blocking.
Key Points
- RANGE / LIST / HASH.
- Pruning on the partition key.
- Instant retention by dropping partitions.
- Unique keys must include the partition key.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?