A job queue and a partial index#
A worker picks the next pending jobs. Almost all rows are done, so a partial index on pending jobs stays small. We ran this on PostgreSQL 16.15.
CREATE TABLE jobs (
id bigint PRIMARY KEY,
status text NOT NULL,
queue text NOT NULL,
run_at timestamptz NOT NULL
);
INSERT INTO jobs
SELECT g,
CASE WHEN g % 1000 = 0 THEN 'pending' ELSE 'done' END,
'mail',
now() - (g || ' seconds')::interval
FROM generate_series(1, 1000000) g;
CREATE INDEX jobs_pending_run_at ON jobs (run_at) WHERE status = 'pending';
ANALYZE jobs;
EXPLAIN (COSTS OFF)
SELECT id FROM jobs WHERE status = 'pending' ORDER BY run_at LIMIT 10;
-- Limit
-- -> Index Scan using jobs_pending_run_at on jobs
This is the check a reviewer usually sees in the pull request. It shows what the planner does with a literal, which the application never sends.
A parameter of another type#
A support page looks up one job by ID. The application binds the ID as numeric, for example because the value reaches the query as a decimal type:
PREPARE by_id(numeric) AS SELECT status FROM jobs WHERE id = $1;
EXPLAIN (COSTS OFF) EXECUTE by_id(4200);
Gather
Workers Planned: 2
-> Parallel Seq Scan on jobs
Filter: ((id)::numeric = '4200'::numeric)
PostgreSQL has no bigint = numeric operator in the primary key's index operator class, so it casts the column to numeric for every row and cannot use the index. With a bigint parameter the same query uses jobs_pkey. Nothing fails: the page is slow, and the time grows with the table. A new index does not help either, for the same reason. The fix belongs in the application, where it binds the parameter with the column's type.
A generic plan cannot use the partial index#
PostgreSQL plans a prepared statement for its parameter values at first, and may switch to a generic plan that works for any value after five executions. A generic plan does not know that $1 will be 'pending', so it cannot prove that the partial index's condition holds:
PREPARE next_jobs(text) AS
SELECT id FROM jobs WHERE status = $1 ORDER BY run_at LIMIT 10;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE next_jobs('pending');
Limit
-> Gather Merge
Workers Planned: 2
-> Sort
Sort Key: run_at
-> Parallel Seq Scan on jobs
Filter: (status = $1)
That execution took 25 ms, against 0.3 ms with the index. In the default mode, auto, PostgreSQL kept choosing parameter-specific plans in our run: after seven executions, pg_prepared_statements showed 7 custom plans and 0 generic ones, because the generic plan's estimated cost was much higher. The PREPARE documentation describes how it makes that comparison. A connection or role with plan_cache_mode = force_generic_plan, or a data distribution where the costs come closer, can make the switch. If the status is always 'pending', write it into the query as a literal so the planner can match the partial index's condition every time.
What to rehearse#
Capture the statements the application sends to the table, with their parameter types, from the driver's logging or from pg_stat_statements. On a staging copy with the production row count and statistics, prepare each one with the same types and run it at least six times, then EXPLAIN it again. Check pg_prepared_statements for its plan counts. The rehearsal passes when every execution uses the index you added.
The partial index documentation explains when the planner can match a partial index's condition. A new index also adds work to every write on the table, so measure write latency in the same rehearsal.
Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.