SQLite Basics: Index Selected Rows with Partial Indexes

An application may keep years of completed jobs while repeatedly looking up a small active queue. A partial index stores entries only for rows that satisfy its WHERE condition. It can also enforce a uniqueness rule that applies only to active records.

Partial indexes require SQLite 3.8.0 or later. Older versions cannot read or write a database containing one. Their predicates cannot contain subqueries, bound parameters, or nondeterministic functions. See the partial-index reference.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. It prints pipe-separated rows and displays missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

Each invocation starts with a fresh in-memory database. The examples are independent, and the toy data disappears when the connection closes.

The raw EXPLAIN QUERY PLAN rows below were observed on SQLite 3.53.1. Numeric fields, wording, and planner choices can differ between versions or datasets. Treat them as diagnostics, not a stable API or a timing result.

1. Index only the pending queue

Save as index-pending-job-queue.sql:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    project TEXT NOT NULL,
    status TEXT NOT NULL,
    priority INTEGER NOT NULL
);
INSERT INTO jobs VALUES
    (1, 'docs', 'pending', 2),
    (2, 'docs', 'done', 9),
    (3, 'docs', 'pending', 5),
    (4, 'api', 'pending', 7);
CREATE INDEX jobs_pending_priority
ON jobs(project, priority DESC, id)
WHERE status = 'pending';
EXPLAIN QUERY PLAN
SELECT id, priority FROM jobs
WHERE project = 'docs' AND status = 'pending'
ORDER BY priority DESC, id;
SELECT id, priority FROM jobs
WHERE project = 'docs' AND status = 'pending'
ORDER BY priority DESC, id;

Run:

python3 run-sql.py index-pending-job-queue.sql

Output:

3|0|55|SEARCH jobs USING COVERING INDEX jobs_pending_priority (project=?)
3|5
1|2

The plan names jobs_pending_priority, and the query returns the higher-priority documentation job first. Job 2 is absent from both the result and this index because it is already done. The project column narrows the lookup; the remaining keys match the requested ordering.

Keep status = ‘pending’ explicit in the query. SQLite must establish that an index excluding completed jobs still contains every candidate row. A lookup that omits the status restriction cannot generally use this index to answer the same project-wide question.

2. Enforce uniqueness only for active records

Save as reserve-active-user-handles.sql:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    handle TEXT NOT NULL,
    active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE UNIQUE INDEX accounts_active_handle
ON accounts(handle) WHERE active = 1;
INSERT INTO accounts VALUES
    (1, 'river', 1),
    (2, 'river', 0),
    (3, 'river', 0);
INSERT OR IGNORE INTO accounts VALUES (4, 'river', 1);
SELECT 'duplicate inserted', changes();
UPDATE accounts SET active = 0 WHERE id = 1;
INSERT INTO accounts VALUES (4, 'river', 1);
SELECT id, handle, active FROM accounts ORDER BY id;

Run:

python3 run-sql.py reserve-active-user-handles.sql

Output:

duplicate inserted|0
1|river|0
2|river|0
3|river|0
4|river|1

Only one active account can reserve river, while inactive historical records may retain that handle. The first attempt to insert account 4 changes zero rows. Deactivating account 1 releases the reservation, and the later insert succeeds.

OR IGNORE keeps this demonstration running; an ordinary conflicting INSERT raises a uniqueness error. In an application, surface that error rather than silently dropping a requested account. This index uses SQLite’s default case-sensitive text comparison, so river and River are distinct. Choose normalization and collation rules separately if that is not the intended identity policy.

3. Exclude missing references without losing valid matches

Save as index-known-external-references.sql:

CREATE TABLE deliveries (
    id INTEGER PRIMARY KEY,
    external_ref TEXT,
    label TEXT NOT NULL
);
INSERT INTO deliveries VALUES
    (1, NULL, 'Not assigned'),
    (2, 'X-20', 'Packed'),
    (3, 'X-21', 'Shipped');
CREATE INDEX deliveries_external_ref
ON deliveries(external_ref) WHERE external_ref IS NOT NULL;
EXPLAIN QUERY PLAN
SELECT label FROM deliveries WHERE external_ref = 'X-20';
SELECT id, label FROM deliveries WHERE external_ref = 'X-20' ORDER BY id;
EXPLAIN QUERY PLAN
SELECT id FROM deliveries WHERE external_ref IS NULL;
SELECT id, label FROM deliveries WHERE external_ref IS NULL ORDER BY id;

Run:

python3 run-sql.py index-known-external-references.sql

Output:

3|0|62|SEARCH deliveries USING INDEX deliveries_external_ref (external_ref=?)
2|Packed
2|0|216|SCAN deliveries
1|Not assigned

Equality with X-20 can only match a non-NULL reference, so the first plan can use the partial index even without an explicit IS NOT NULL term. The missing-reference query must find rows excluded from that index; the observed second plan scans deliveries.

This is a useful contrast with the queue example: SQLite recognizes certain implications, including ordinary comparisons against a column whose partial-index predicate is IS NOT NULL. Do not assume it proves every logically equivalent rewrite. Check the actual query shape you ship.

A partial index is a narrower data structure, not a promise of faster queries. Measure representative reads and writes before adopting one. The CREATE INDEX reference covers index creation and uniqueness; the query-plan guide explains the diagnostic output. SQLite maintains index membership as rows change.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py index-pending-job-queue.sql reserve-active-user-handles.sql index-known-external-references.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.