Intermediate LevelPostgreSQL
How does Declarative Table Partitioning (Range, List, Hash) work in PostgreSQL 10+?
❓ Interview Prompt: Explain Declarative Partitioning, `PARTITION BY RANGE`, `PARTITION BY LIST`, `PARTITION BY HASH`, partition pruning, and dynamic routing.
PostgreSQL 10+ supports native Declarative Table Partitioning:
Partition Strategies:
•
'RANGE': Partitions data by continuous numerical or date ranges ('PARTITION BY RANGE (created_at)').
•
'LIST': Partitions data by explicit value lists (e.g. 'country_code').
•
'HASH': Distributes rows across N hash partitions for even load balancing.
•
Partition Pruning: The Query Planner automatically skips non-matching child partition tables during queries!
Code Implementation Example
sql
-- Create partitioned parent table
CREATE TABLE sales (
id BIGINT GENERATED ALWAYS AS IDENTITY,
amount NUMERIC(10,2),
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
-- Create child partition tables for specific months
CREATE TABLE sales_2026_01 PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE sales_2026_02 PARTITION OF sales
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');💡Creating declarative range-partitioned tables in PostgreSQL.
🎯 Interview Key Takeaway
Use declarative RANGE partitioning for historical time-series tables to enable fast partition pruning and quick DROP PARTITION retention cleanup.
#Partitioning#Declarative Partitioning#RANGE#Partition Pruning#Intermediate
Back to all PostgreSQL Questions→