lesson 69 Performance & query plans · advanced

Partitioning & workload trade-offs

Reason about partitioning choices for different workloads.

the idea

Partitioning splits a large table by a defined key. It can aid pruning and lifecycle management, but is not a universal query accelerator. SQLite has no SQL Server partition function/scheme. Practice time-aligned filtering here; use the reference notes to understand native warehouse partitioning.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT sale_key,order_date,sales
FROM fact_sales
WHERE order_date>='2025-02-01' AND order_date<'2025-03-01';

Watch for: Partitioning and clustering are different. Do not report partition elimination from a SQLite plan.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Filter a date slice of the fact. Partitioning ideas show up as pruning a date range. Narrow fact_sales to one month before aggregating.

   step-1.sql Run

   sql›

   SELECT sale_key, order_date, sales
   FROM fact_sales
   WHERE order_date >= '2025-02-01' AND order_date < '2025-03-01'
   ORDER BY order_date
   LIMIT 8;

2. 2

   Query within the partition window. Date predicates that match partition boundaries let engines skip irrelevant slices. Keep the range half-open.

   step-2.sql Run

   sql›

   SELECT sale_key,order_date,sales
   FROM fact_sales
   WHERE order_date>='2025-02-01' AND order_date<'2025-03-01';