Process Automation · PostgreSQL · Observability · Reliability

The claim query is not a dashboard: counting a Postgres queue without SKIP LOCKED

Published · Updated

The query that claims work from a queue table hides rows on purpose. Reuse it to answer "how much is pending?", and the number drops exactly when the pipeline is busiest.

A queue table in Postgres usually earns its keep with one query: claim a row, lock it, skip whatever is already held. That query is also the most tempting thing to reuse when someone asks how much work is pending. The locking clause documentation is explicit that skipping locked rows provides an inconsistent view of the data, usable for queue-like tables with multiple consumers and not suitable for general-purpose work. Counting is general-purpose work.

Claiming hides rows on purpose

With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. For a consumer, that is the entire point: contention becomes someone else's row rather than a wait. For a count, it inverts the meaning of the result. The rows missing from the output are precisely the rows currently being processed, so the figure shrinks when the pipeline is busiest and grows back when it idles.

NOWAIT trades silence for an error

With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. Borrowing that for a reporting query converts ordinary contention into a reported failure, which then reads as a pipeline incident rather than two workers meeting on one row. Neither locking clause belongs in a reporting path. Read the queue with no locking clause at all and group by state instead.

The state column is the thing you can count

A lock is transient and invisible once the transaction ends. A state column is durable. If each row records its state, its attempt count and when it was claimed, a count becomes a statement about work rather than about locks. White-box monitoring exists for exactly this: it allows detection of imminent problems, failures masked by retries, and so forth. A row that eventually succeeded after retrying is indistinguishable from a clean one unless attempts were written down.

When nobody is watching, the count is the evidence

Four markets, one codebase publishes 8 SEO articles every day across 4 country domains in 7 languages, with manual intervention required: 0. A personal platform publishes articles autonomously, daily, with weekly content operations time of about 0 h. In setups like these, nobody is logged in to notice that a number looks wrong. Undercounting in-flight work stops being cosmetic; it is the only signal anyone will ever read.

A counting contract for queue tables

Four rules keep the two jobs apart. Reporting queries carry no locking clause. Every row holds an explicit state rather than one inferred from timestamps. Attempt counts are stored, so work that recovered stays visible afterwards. And the pending figure is derived from states, never from how many rows a claim query happened to return. The claim query answers what to do next; it was never designed to answer how much is left.

Before trusting a queue dashboard, read the query behind the pending number. If it carries SKIP LOCKED, that number is lowest exactly when load is highest, and the rows it drops are the ones most worth seeing. Separating claim from count costs one extra query and a state column, and it turns an inconsistent view into a figure a pipeline running daily without supervision can actually be judged on.

Sources

Sources

PostgreSQL 18 Documentation — SELECT (The Locking Clause) — https://www.postgresql.org/docs/current/sql-select.html

Google SRE Book — Monitoring Distributed Systems — https://sre.google/sre-book/monitoring-distributed-systems/

Neurolinks case study — Four markets, one codebase — https://neurolinks.be/work/tatano-energy

Neurolinks case study — A personal brand that publishes itself — https://neurolinks.be/work/matthieu-pesesse-media

Working on a project where these methods apply?