PartitioningIntermediate
1Question 1 of 1
sqlHow does table partitioning work in PostgreSQL using Declarative Partitioning?
Explanation & Concept
Declarative Partitioning splits one logically massive table into smaller physical tables (partitions) based on a partition key (such as Range, List, or Hash).
This dramatically improves query performance through 'partition pruning' (the planner skips scanning irrelevant partitions entirely) and allows instant data purging via `DROP TABLE partition_name` instead of expensive mass `DELETE` queries.
Code Example
sql
-- Create master partitioned table by range (Year)
CREATE TABLE metrics (
id BIGINT,
recorded_at TIMESTAMPTZ NOT NULL,
cpu_usage NUMERIC
) PARTITION BY RANGE (recorded_at);
-- Create individual year partitions
CREATE TABLE metrics_2025 PARTITION OF metrics
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE metrics_2026 PARTITION OF metrics
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');How It Works & Key Takeaways
Queries filtering by `recorded_at >= '2026-06-01'` automatically prune `metrics_2025` from disk reading, speeding up query execution by half and keeping working memory focused on recent data.
Mock Test · 1 Questions · Optional
Ready for Full-Text Search?
Take a quick test to evaluate yourself, or move straight to the next module.
Intermediate1 questions