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 key, typically time, so queries that filter on that key read only matching partitions, and old data can be dropped as whole partitions. Its value depends on queries filtering by the partition key. SQLite has no native partitioning; you can still write the aligned range filters and per-period summaries partitioned engines reward.

Dataset: Energy

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count rows per month slice. Grouping hour_ending_utc by month shows how a time-partitioned table would be split—each month is one logical partition of the hourly data.the pattern

   example.sql Run

   sql›

   SELECT
     substr(hour_ending_utc, 1, 7) AS month,
     COUNT(*) AS rows_in_partition
   FROM hourly_demand
   GROUP BY month;

2. 2

   Filter one authority and one month. PJM demand for March 2024 with a half-open range on hour_ending_utc matches how partitioned engines read only the partitions you need.

   step-2.sql Run

   sql›

   SELECT COUNT(*) AS hours
   FROM hourly_demand
   WHERE ba_code = 'PJM'
     AND hour_ending_utc >= '2024-03-01'
     AND hour_ending_utc < '2024-04-01';

common mistake

A filtered result in SQLite is not evidence of partition elimination. Queries that do not filter on the partition key read every partition.