SQLite Partial Indexes: CREATE INDEX ... WHERE

A Partial Index Covers Only Some Rows

A regular index has an entry for every row in the table. A partial index has entries only for rows matching a WHERE clause you supply when you create it. Smaller index, fewer pages to walk, less work on every insert and update that doesn't touch the indexed slice.

The syntax is a normal CREATE INDEX with a WHERE tacked on:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending only contains entries for rows where status = 'pending'. Shipped, cancelled, and refunded orders aren't in it at all. If 95% of your orders table is historical and you mostly query the open ones, that's a 20× smaller index for the same query speed.

When the Planner Will Actually Use It

A partial index is only usable when SQLite can prove your query restricts to the same rows the index covers. The cleanest way is to repeat the index's WHERE clause in the query:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

The plan should mention USING INDEX idx_orders_pending. Drop the status = 'pending' from the query and the planner falls back to a full table scan — it has no way to know the query stays inside the indexed subset.

The rule of thumb: the query's WHERE must imply the index's WHERE. Equality on the same column and value is the safe, obvious case. Inequalities and OR get hairier; check with EXPLAIN QUERY PLAN.

Why Bother — The Three Wins

Three concrete reasons partial indexes pay off:

1. Smaller on disk. Only the matching rows are stored. For a "1% of the table is hot" workload, the index is roughly 1% of a full one.
2. Cheaper writes. Inserts and updates only touch the index when the row matches the filter. A status = 'shipped' insert on the table above doesn't touch idx_orders_pending at all.
3. Same lookup speed. A B-tree lookup is logarithmic in the index size. Smaller index, slightly faster lookups, but the bigger gain is everything around it — fewer cache misses, less I/O.

If a column is highly skewed — most rows have one value, you only care about the rare other values — that's the textbook case for a partial index.

Partial Unique Indexes (the Killer Feature)

Plain UNIQUE constraints apply to every row. That's a problem the moment you introduce soft deletes:

-- Fails: there are two rows with email = 'a@x.com', even though one is deleted.
CREATE UNIQUE INDEX idx_users_email ON users(email);

A partial unique index lets you enforce uniqueness only over the rows that matter:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- soft-deleted
    ('a@x.com', '2026-01-05'),  -- soft-deleted again
    ('a@x.com', NULL);          -- one live row, allowed

SELECT email, deleted_at FROM users;

Three rows, same email, no constraint violation — because only the deleted_at IS NULL row participates in the uniqueness check. Try inserting a second live row with the same email and SQLite raises UNIQUE constraint failed.

This pattern shows up everywhere: one active subscription per customer, one primary address per user, one open invoice per order. Partial unique indexes express it directly.

Indexing Around NULL

NULL interacts oddly with indexes. A common goal is "ignore the NULLs entirely" — say, you have a sparse external_id column where most rows are NULL but the populated ones must be unique:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

Two NULLs coexist happily; the EXT-001 and EXT-002 rows are guaranteed unique. The index is also smaller — NULL rows aren't stored at all — so lookups by external_id are quick even when the table grows.

What the Filter Can Reference

The WHERE clause of a partial index is restrictive. It can reference:

• Columns of the table being indexed.
• Literal constants.
• A small set of deterministic built-in functions.

It can't reference:

• Other tables.
• Subqueries.
• Non-deterministic functions like random() or CURRENT_TIMESTAMP.
• Parameters or variables.

This makes sense — SQLite has to evaluate the filter on every row insert and update, and the result has to be stable. So this works:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

But WHERE created_at > date('now') would not — date('now') changes over time, so the set of indexed rows would shift under SQLite's feet.

A Sanity-Check Workflow

When you add a partial index, walk through three checks:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. Does the planner pick the index?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Does it ignore queries outside the filter?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Does it skip queries missing the filter entirely?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Query 1 should use idx_jobs_runnable. Queries 2 and 3 should fall back to a scan (or a different index, if you have one). If the planner picks the partial index for a query you didn't expect, re-read the filter — it might be broader than you think.

When Not to Reach for One

Partial indexes are sharp. Reasons to skip:

• The filter matches most of the table. If "active" is 90% of your rows, a partial index is a regular index with extra steps. Just index the column.
• Your queries don't include the filter literally. If your code uses an ORM that builds WHERE status IN (?, ?, ?) or computes the filter dynamically, the planner often won't recognize the match. Test with EXPLAIN QUERY PLAN, don't assume.
• The hot subset shifts over time. A partial index on "orders from the last 30 days" sounds appealing but isn't expressible — the filter has to be deterministic. You'd need to rebuild the index, or pick a different schema (a separate recent_orders table or a archived boolean you flip nightly).

When the filter is stable and matches a small slice of a large table, partial indexes are some of the highest-leverage tuning you can do in SQLite.

Next: Reading Query Plans

Most of this page leaned on EXPLAIN QUERY PLAN to confirm an index actually got used. That tool deserves a page of its own — how to read its output, what the keywords mean, and how to spot the difference between a happy index lookup and a sneaky full scan. That's next.