Partitioning
In one line
Partitioning divides a table into smaller pieces by range/list/hash — improves query speed and maintenance.
In simple words
Partitioning splits a large table into smaller, more manageable pieces called partitions. The table still appears as one logical table to applications, but the data is physically stored in separate partitions.
Types:
- Range partitioning — by value ranges (e.g., orders by date: 2023, 202, 2025)
- List partitioning — by discrete values (e.g., by region: US, EU, Asia)
- Hash partitioning — by hash of a column (even distribution)
Benefits:
- Query pruning — only scan relevant partitions (e.g., query 2024 data only scans 2024 partition)
- Maintenance — archive/drop old partitions without affecting others
- Parallelism — queries can scan partitions in parallel
- Storage — each partition can be on different storage tiers
Example
-- PostgreSQL range partitioning by date
CREATE TABLE orders (
id SERIAL,
created_at DATE NOT NULL,
total DECIMAL
) PARTITION BY RANGE (created_at);
-- Create partitions by year
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- Query only scans the 2024 partition
SELECT * FROM orders WHERE created_at >= '2024-06-01';
-- Plan: Seq Scan on orders_2024 (not orders_2023!)Partitioning splits orders by year. A query for 2024 data only scans the orders_2024 partition — partition pruning skips 2023 automatically.
Common confusions
Confused with: sharding
The difference: Partitioning splits a table within a single database into smaller pieces. Sharding distributes data across multiple database servers. Partitioning is single-node; sharding is multi-node. Partitioning improves query speed; sharding improves scale-out capacity.
Related terms
Related Resources
Related Lessons
- Database Scaling
Partitioning.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.