SQL Partial Index
Index only the rows that match a condition — smaller, faster, and often all you actually need.
What & Why
A partial index stores entries only for rows matching the index's WHERE condition. It can stay smaller and more selective when queries repeatedly target one durable subset such as unchurned users.
See How It Works
BUSINESS QUESTION
Growth frequently filters active users where churned_at is NULL.
| id | created_at | country | channel | plan | activated_at | churned_at |
|---|---|---|---|---|---|---|
| 101 | 2024-01-15 09:10:00+00 | US | organic | pro | 2024-01-16 14:25:00+00 | NULL |
| 102 | 2024-02-10 11:05:00+00 | CA | paid | free | NULL | 2024-03-20 10:00:00+00 |
| 103 | 2024-03-12 16:35:00+00 | GB | referral | pro | 2024-03-13 08:15:00+00 | NULL |
| 104 | 2024-04-01 13:20:00+00 | US | organic | free | 2024-04-03 12:00:00+00 | NULL |
EXAMPLE QUERY
SELECT
id,
channel,
plan,
created_at
FROM growth.users
WHERE churned_at IS NULL
ORDER BY created_at DESC, id;RESULT — users eligible for the active-user predicate
| id | channel | plan | created_at |
|---|---|---|---|
| 104 | organic | free | 2024-04-01 13:20:00+00 |
| 103 | referral | pro | 2024-03-12 16:35:00+00 |
| 101 | organic | pro | 2024-01-15 09:10:00+00 |
These are the rows a churned_at IS NULL partial index would contain.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario