SQL Academy · Lesson

Querying Across Partitions Efficiently

Write queries that benefit from partition pruning, and verify pruning with EXPLAIN.

Querying Across Partitions Efficiently is a free SQL Academy lesson on CoddyKit — lesson 4 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.

Filter on the Partition Key

Pruning only works when the WHERE clause includes the partition key:

-- Prunes (uses date partitioning):
SELECT * FROM events WHERE ts >= '2024-03-01' AND ts < '2024-04-01';

-- No pruning — scans all partitions:
SELECT * FROM events WHERE user_id = 42;

Composite Pruning

The planner can prune by multiple keys in multi-level partitioned tables:

SELECT * FROM events
WHERE ts >= '2024-03-01' AND ts < '2024-04-01'
  AND user_id = 42;
-- Prunes by date AND by hash partition.

EXPLAIN Shows Pruning

Check the plan to confirm pruning is happening:

EXPLAIN SELECT * FROM events WHERE ts >= '2024-03-01' AND ts < '2024-04-01';
-- Append
--   -> Seq Scan on events_2024_q1
-- Only Q1 partition shown; others pruned.

Constraint Exclusion vs Partition Pruning

Modern PG uses fast "partition pruning" by default. The older "constraint exclusion" was slower; ensure enable_partition_pruning = on.

Pruning at Execution Time

Even with parameterised queries (PREPARE), PG can prune at execution time:

PREPARE p(timestamptz, timestamptz) AS
SELECT * FROM events WHERE ts >= $1 AND ts < $2;

EXECUTE p('2024-03-01', '2024-04-01');
-- Pruning happens at execute, not at parse.

Cross-Partition Indexes

Indexes propagate to partitions. A query on the indexed column works across all partitions — but the planner scans each one's index.

Cross-Partition Aggregates

A GROUP BY on the partition key benefits most. GROUP BY on other columns scans every partition.

Parallel Per-Partition Execution

PostgreSQL can execute scans across partitions in parallel (PG 11+):

SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT COUNT(*) FROM events;
-- Parallel Append over partitions.

Joins Across Partitioned Tables

If both partitioned tables share the partition key, "partition-wise join" is faster — each partition joined independently:

SET enable_partitionwise_join = on;
-- Now PG can join events to event_metrics partition-by-partition.

Avoiding Common Mistakes

Don't apply functions to the partition key in WHERE — kills pruning
Don't forget the partition key in your queries
Don't partition into thousands of tiny partitions — planning overhead dominates

Sane Partition Counts

Aim for tens to low hundreds of partitions, not thousands. Each partition has overhead in planning. For very fine-grained needs, sub-partition.

Maintenance Per Partition

VACUUM, ANALYZE, REINDEX run per partition — and can run in parallel. The win compounds the more partitions you have (within reason).

Recap

Partitioning rewards queries that filter by the partition key.

WHERE on partition key → pruning
Confirm with EXPLAIN
Enable partition-wise join for matched joins
Don't over-partition

Quick Check

You have events partitioned by month on ts. Which query benefits from partition pruning?

Frequently asked questions

Is the “Querying Across Partitions Efficiently” lesson free?

Yes — the full text of “Querying Across Partitions Efficiently” 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 “Querying Across Partitions Efficiently”?

Write queries that benefit from partition pruning, and verify pruning with EXPLAIN. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Querying Across Partitions Efficiently” 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

← Back to SQL Academy