Huny's Dev Blog
huny.dev

Prevent Duplicate Pending Jobs with a SQLite Partial Unique Index

Use a SQLite partial unique index, UPSERT, and RETURNING to suppress duplicate pending jobs while preserving completed history.

Hun Jang
Hun Jang Nov 17, 2026

If the same job can enter a SQLite queue twice, use a partial unique index with WHERE done_at IS NULL. It allows one pending row per tenant and job key while preserving multiple completed rows. The Python example checks whether a row was inserted and shows the scope of the guarantee.

Several cyan data rows highlighted inside an amber frame

Why including completed history causes problems

UNIQUE(tenant_id, job_key) blocks new rows with the same key even after completion. Adding the completion timestamp with UNIQUE(tenant_id, job_key, done_at) leaves pending duplicates possible, because SQLite treats NULL values as distinct for UNIQUE constraints. This model indexes only rows whose completion timestamp is NULL and declares both key columns NOT NULL. SQLite UNIQUE constraints · Partial unique indexes

Let the INSERT handle duplicate detection

If you query for an existing pending job and then insert separately, another request can insert the same key in between. Let the index enforce the rule in the database and specify the same columns and predicate in the INSERT’s ON CONFLICT target. Here, RETURNING id returns an ID only when a row is inserted. A duplicate produces no row, so an API that needs the existing job’s ID must define a separate lookup. SQLite UPSERT · RETURNING

Save the example as sqlite_pending_jobs.py and run python3 sqlite_pending_jobs.py. Its RETURNING clause requires SQLite 3.35.0 or later.

Verify tenant scope and re-enqueue after completion

The example ran on November 17, 2026 with Python 3.12.14 and an in-memory SQLite 3.53.1 database. A duplicate insert within the same tenant was ignored, while another tenant could use the same key. After completing two successive jobs and enqueuing one more, the test found two completed rows and one pending row for the same tenant and key.

Check these conditions before applying it to a queue

Existing duplicate pending rows cause index creation to fail. First filter with WHERE done_at IS NULL and identify duplicates with GROUP BY tenant_id, job_key HAVING COUNT(*) > 1. Then decide which row to retain according to the application’s rules. If jobs already running must also block another enqueue with the same key, include that state in both the index predicate and the conflict target. Partial unique indexes · SQLite UPSERT

This example checks enqueue rules on one connection. Claiming jobs across workers, durable completion, and avoiding duplicate external effects require separate design. Because a completed job frees the key for reuse, requests that need permanent idempotency should have an idempotency key policy separate from job history.

한국어 버전 · SQLite 부분 UNIQUE 인덱스로 대기 작업 중복 등록 막기SQLite 부분 UNIQUE 인덱스로 대기 작업 중복 등록 막기

추천 글