Why Partition: Pruning, Maintenance
Understand the wins of partitioning: query pruning, faster VACUUM, easier archival.
Why Partition: Pruning, Maintenance is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is Partitioning?
Splitting one logical table into many physical tables (partitions), each holding a subset of rows. Queries scan only relevant partitions.
Why Bother?
Big tables hurt:
- Indexes grow huge, lookups get slower
- VACUUM takes hours
- Backups are slow
- Some queries scan unnecessarily
Partitioning addresses all four.
Partition Pruning
The planner can SKIP partitions that can't match a WHERE clause:
EXPLAIN SELECT * FROM events
WHERE ts >= '2024-03-01' AND ts < '2024-04-01';
-- Only March partition scanned; older partitions skipped.
-- Plan rows: 1M, not 100B.When to Partition
Partition when:
- Table is in the 100+ GB range
- You have a natural partition key (date, tenant, region)
- Most queries filter on that key
- Old data is archive-able or droppable
When NOT to Partition
Don't partition just because it sounds cool:
- Tables under a few GB don't benefit
- If queries don't filter by the partition key, you get NO pruning
- Adds operational complexity (managing partitions)
Partitioning Strategies
PostgreSQL supports three:
- Range — partitions cover ranges of values (dates, ids)
- List — partitions hold specific values (countries, tenants)
- Hash — modular hashing for even distribution
Cheap Drop
Dropping a partition is instant — no row-by-row delete:
DROP TABLE events_2023;
-- 100GB gone in milliseconds, no VACUUM aftermath.Easier Maintenance
VACUUM, REINDEX, ANALYZE operate per-partition. Maintenance windows fit between partitions instead of needing one giant batch.
Smaller Indexes per Partition
Each partition has its own indexes. A B-tree on a 1GB partition has fewer levels than on 1TB — lookups are faster.
Partitioning Is a Tool, Not a Magic Bullet
You don't get free perf — you get pruning for queries that filter by the partition key, and easier maintenance. If those don't apply, you'll just add complexity.
PostgreSQL Native (Declarative) Partitioning
Available since PG 10. Each partition is a real table:
CREATE TABLE events (
id BIGSERIAL,
ts TIMESTAMPTZ NOT NULL,
user_id BIGINT,
data JSONB
) PARTITION BY RANGE (ts);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');Pre-PG10: Inheritance Partitioning
Legacy systems use table inheritance + triggers. Avoid for new code — declarative partitioning is simpler and faster.
Recap
Partitioning shines on big, time-bucketed or tenant-segmented tables.
- Pruning skips irrelevant partitions
- Per-partition maintenance is faster
- DROP TABLE replaces 100GB DELETE
- Not magic — only helps if queries match the partition key
Quick Check
You partition a table by month and run SELECT * FROM events WHERE country = 'US' with no date filter. Does partitioning help?
Frequently asked questions
Is the “Why Partition: Pruning, Maintenance” lesson free?
Yes — the full text of “Why Partition: Pruning, Maintenance” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Why Partition: Pruning, Maintenance”?
Understand the wins of partitioning: query pruning, faster VACUUM, easier archival. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Why Partition: Pruning, Maintenance” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Why Partition: Pruning, Maintenance
- Range, List and Hash Partitioning
- Detaching and Attaching Partitions
- Querying Across Partitions Efficiently