Dither and Bicker

PostgreSQL Partial Indexes for Sparse Job Queues and Soft-Deleted Tables

November 12, 2026 • By Chris • Database Engineering

Back in 2014 I wrote about anti-join patterns in MySQL. A related problem shows up constantly in PostgreSQL when a table holds tens of millions of historical rows, yet worker processes only ever query the small fraction that are still pending.

The Problem With Full-Table Status Indexes

A standard CREATE INDEX idx_jobs_status ON jobs (status, created_at) indexes every completed row from the past three years. The index balloons in shared buffers, slows down inserts, and bloats vacuum cycles—even though nobody polls finished rows in the hot path.

Restricting the Index With a WHERE Predicate

PostgreSQL lets you attach a WHERE clause directly to the index definition:

CREATE INDEX idx_jobs_pending_created
  ON jobs (created_at)
  WHERE status = 'pending';

If 99.5% of rows are done, this partial index stays roughly 200× smaller than a full-table index and fits comfortably in RAM. Just make sure your worker query repeats the exact predicate (WHERE status = 'pending') so the planner proves the index covers it, as detailed in the PostgreSQL Partial Indexes documentation.