Create an index for the exact SQLite queue claim query

By Batu · English technical notes

Also published in our Blogger archive.

Verify index columns and execute the matching query

The index puts state first for the equality predicate and created second for the queue ordering. PRAGMA index_info returns indexed columns in sequence, so the script verifies state,created rather than just the index name. It also runs the candidate query and picks id 2 as the earliest queued row.

No EXPLAIN QUERY PLAN appears, so this is not a planner or speed claim. The script stops before the conditional UPDATE that actually marks the selected job claimed.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(id INTEGER, state TEXT, created INTEGER)")
    connection.execute("CREATE INDEX job_claim ON job(state, created)")
    columns = [row[2] for row in connection.execute("PRAGMA index_info(job_claim)")]
    connection.executemany("INSERT INTO job VALUES (?, ?, ?)", [(1, "done", 1), (2, "queued", 20), (3, "queued", 30)])
    next_id = connection.execute("SELECT id FROM job WHERE state = 'queued' ORDER BY created LIMIT 1").fetchone()[0]
    assert columns == ["state", "created"]
    assert next_id == 2
    print("index=job_claim columns=state,created next=id-2")

Expected stdout:

index=job_claim columns=state,created next=id-2

Sources

- SQLite CREATE INDEX

- SQLite PRAGMA index_info

- SQLite query planning

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.