Build a PostgreSQL worker queue with SKIP LOCKED

SKIP LOCKED can help workers avoid each other. It cannot make a job system reliable by itself.

A worker can crash after claiming, repeat a side effect after losing a response, starve old jobs behind priority traffic, hold a transaction too long, or retry poison work forever. The queue needs leases, idempotent handlers, retry policy, terminal states, indexes, and observable recovery.

PostgreSQL documents that SKIP LOCKED provides an inconsistent view unsuitable for general queries but useful for multiple consumers accessing a queue-like table.

We will use that behavior only during a short claim transaction, then perform slow work outside the lock while a lease makes abandoned claims recoverable.

The lock prevents simultaneous claims. The system design handles everything after.

Decide when Postgres is enough

A database queue fits modest workloads and teams that value transactional proximity, but throughput, fanout, isolation, and retention needs may justify dedicated infrastructure.

I would pressure-test that decision with four questions:

• What volume and latency exist?
• Must enqueue share a transaction?
• How large are payloads?
• What failure isolation is required?

The failure mode here is choosing a database queue because no new service sounds simpler. In small product systems that need multiple workers to claim durable jobs without duplicate concurrent execution, while handling crashes, retries, poison jobs, priority, scheduling, observability, retention, and operational recovery, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a queue fit decision record. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is infrastructure proportional to the workload. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a queue fit decision record beside the question “What volume and latency exist?” before the first implementation review. The next pass would use “Must enqueue share a transaction?” to test the boundary, then “How large are payloads?” to expose the state most likely to be missed. I would keep “What failure isolation is required?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports infrastructure proportional to the workload.

Design the job schema

Jobs need stable IDs, type and schema version, bounded payload or reference, status, schedule, priority, attempt policy, lease, idempotency key, and timestamps.

The practical review starts here:

• What identifies the intent?
• How does payload evolve?
• When is it eligible?
• Which fields support recovery?

Those questions keep storing only a JSON blob and processed boolean from becoming the default. I would capture the decision in a versioned jobs table, then use it while the work is still cheap to change. For database-backed background job execution, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like durable lifecycle and queryable operations. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a versioned jobs table part of the working surface. I would use it to answer “What identifies the intent?” while scope is still flexible, and “How does payload evolve?” before code or content becomes expensive to unwind. During QA, “When is it eligible?” and “Which fields support recovery?” become concrete checks rather than discussion prompts. That sequence turns database-backed background job execution into something the team can operate and gives me a specific outcome to report: durable lifecycle and queryable operations.

Index the eligible path

The worker's hot query should use a partial or targeted index aligned with ready status, run time, priority, and stable tie-breaking.

Before implementation, I would answer:

• Which rows are scanned?
• Does order match the index?
• How large is ready backlog?
• Can vacuum keep up?

The artifact is an EXPLAIN-backed eligibility index. Its job is to expose the tradeoff early enough that design, engineering, support, or product can disagree with something concrete. The common trap is adding SKIP LOCKED to an unbounded sequential scan; it moves uncertainty downstream and makes the final interface carry a problem the system never resolved.

For me, the useful receipt is predictable claims as history grows. That connects a short claim transaction using FOR UPDATE SKIP LOCKED, a lease, stable job identity, idempotent handlers, and explicit terminal states to an observable result instead of a process claim.

I would test this with one typical case and one boundary case. The typical case should make “Which rows are scanned?” easy to answer. The boundary should force a decision about “Does order match the index?” and “How large is ready backlog?.” I would record both in an EXPLAIN-backed eligibility index, including the part that stayed unresolved after the first pass. The final check, “Can vacuum keep up?,” is where the artifact earns its place: it either supports predictable claims as history grows, or it shows exactly why another iteration is needed.

Keep claim transactions short

Select and mark ownership in one compact transaction, then commit before network calls or expensive computation.

I would use these prompts during the working review:

• Which rows are locked?
• Can claim and update be atomic?
• How large is the batch?
• When is the transaction released?

If the team slips into holding row locks for the full job execution, the product can still look complete while its operating rule stays ambiguous. I would make a single claim statement or transaction the shared reference and keep it small enough to update as evidence changes.

The standard is low contention between workers and product queries. That tells me whether the decision helped the product, not merely whether the document was completed.

The working sequence is small: draft a single claim statement or transaction, review it against “Which rows are locked?,” implement the narrowest useful path, and then return with evidence for “Can claim and update be atomic?.” I would use “How large is the batch?” to inspect product consequence and “When is the transaction released?” to decide whether the result is stable enough to ship. This keeps holding row locks for the full job execution visible as a known risk and makes low contention between workers and product queries the release receipt rather than a hopeful conclusion.

Use leases for crash recovery

A running job needs an expiry after which another worker can reclaim it, with heartbeat only for work that legitimately exceeds the base lease.

I would pressure-test that decision with four questions:

• How long is normal work?
• When does the lease expire?
• Can stale workers settle?
• How is clock use bounded?

The failure mode here is assuming process death will reset running state. In small product systems that need multiple workers to claim durable jobs without duplicate concurrent execution, while handling crashes, retries, poison jobs, priority, scheduling, observability, retention, and operational recovery, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a lease and conditional settlement contract. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is automatic recovery without two authoritative workers. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a lease and conditional settlement contract beside the question “How long is normal work?” before the first implementation review. The next pass would use “When does the lease expire?” to test the boundary, then “Can stale workers settle?” to expose the state most likely to be missed. I would keep “How is clock use bounded?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports automatic recovery without two authoritative workers.

Make handlers idempotent

At-least-once execution means external messages, payments, files, and mutations need an idempotency boundary beyond the queue lock.

The practical review starts here:

• What side effect can repeat?
• Which key deduplicates it?
• How long is the receipt retained?
• Can the provider replay a result?

Those questions keep treating one claim as exactly-once execution from becoming the default. I would capture the decision in a per-handler idempotency receipt, then use it while the work is still cheap to change. For database-backed background job execution, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like safe retries after ambiguous outcomes. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a per-handler idempotency receipt part of the working surface. I would use it to answer “What side effect can repeat?” while scope is still flexible, and “Which key deduplicates it?” before code or content becomes expensive to unwind. During QA, “How long is the receipt retained?” and “Can the provider replay a result?” become concrete checks rather than discussion prompts. That sequence turns database-backed background job execution into something the team can operate and gives me a specific outcome to report: safe retries after ambiguous outcomes.

Classify retries

Retry count, schedule, maximum age, and terminal reason should follow failure class rather than one universal exponential loop.

Before implementation, I would answer:

• Is the failure transient?
• Will retry change anything?
• How much delay is safe?
• When does an operator take over?

The artifact is a retry and terminal-state matrix. Its job is to expose the tradeoff early enough that design, engineering, support, or product can disagree with something concrete. The common trap is retrying validation and permission failures forever; it moves uncertainty downstream and makes the final interface carry a problem the system never resolved.

For me, the useful receipt is bounded recovery without poison-job storms. That connects a short claim transaction using FOR UPDATE SKIP LOCKED, a lease, stable job identity, idempotent handlers, and explicit terminal states to an observable result instead of a process claim.

I would test this with one typical case and one boundary case. The typical case should make “Is the failure transient?” easy to answer. The boundary should force a decision about “Will retry change anything?” and “How much delay is safe?.” I would record both in a retry and terminal-state matrix, including the part that stayed unresolved after the first pass. The final check, “When does an operator take over?,” is where the artifact earns its place: it either supports bounded recovery without poison-job storms, or it shows exactly why another iteration is needed.

Prevent starvation

Priority, scheduled work, large batches, and continuously arriving traffic can keep lower-priority jobs from ever being claimed.

I would use these prompts during the working review:

• Can priority age?
• Is ordering stable?
• Do tenants need fairness?
• What backlog age triggers action?

If the team slips into ordering only by priority descending, the product can still look complete while its operating rule stays ambiguous. I would make a fairness and queue-age policy the shared reference and keep it small enough to update as evidence changes.

The standard is important work first without abandoned ordinary work. That tells me whether the decision helped the product, not merely whether the document was completed.

The working sequence is small: draft a fairness and queue-age policy, review it against “Can priority age?,” implement the narrowest useful path, and then return with evidence for “Is ordering stable?.” I would use “Do tenants need fairness?” to inspect product consequence and “What backlog age triggers action?” to decide whether the result is stable enough to ship. This keeps ordering only by priority descending visible as a known risk and makes important work first without abandoned ordinary work the release receipt rather than a hopeful conclusion.

Build operator visibility

Teams need ready/running/retry/dead counts, oldest age, attempt distribution, handler latency, lease expiry, failure class, and safe replay controls.

I would pressure-test that decision with four questions:

• Which backlog is unhealthy?
• Can a job be inspected safely?
• Who may retry?
• What will replay do?

The failure mode here is exposing a generic rerun button and raw sensitive payload. In small product systems that need multiple workers to claim durable jobs without duplicate concurrent execution, while handling crashes, retries, poison jobs, priority, scheduling, observability, retention, and operational recovery, that can hide the exact boundary a reviewer or teammate needs to understand. My working artifact would be a queue operations view with permissioned actions. I want it close enough to the implementation that it can change the work, not created afterward to decorate the story.

The result I would look for is diagnosable and controlled background work. That is a narrower claim than saying the whole system improved, but it is also one I can verify and defend.

In practice, I would put a queue operations view with permissioned actions beside the question “Which backlog is unhealthy?” before the first implementation review. The next pass would use “Can a job be inspected safely?” to test the boundary, then “Who may retry?” to expose the state most likely to be missed. I would keep “What will replay do?” for the release check because it asks whether the decision still holds outside the ideal path. The work is ready to move when the artifact can explain the choice and the observed result supports diagnosable and controlled background work.

Test concurrency and death

Verification should run competing workers, kill processes after claim and side effect, expire leases, flood priorities, replay IDs, and inspect plans under history volume.

The practical review starts here:

• Can two workers own one lease?
• Can stale settlement win?
• Can side effects duplicate?
• Does claim remain indexed?

Those questions keep testing one worker processing ten clean jobs from becoming the default. I would capture the decision in a multi-worker failure harness, then use it while the work is still cheap to change. For database-backed background job execution, the artifact should make ownership, constraint, and next action visible without requiring a private explanation.

Success would look like evidence that the queue survives the reason it exists. If I cannot point to that evidence, I have a direction, not a finished decision.

The implementation move is to make a multi-worker failure harness part of the working surface. I would use it to answer “Can two workers own one lease?” while scope is still flexible, and “Can stale settlement win?” before code or content becomes expensive to unwind. During QA, “Can side effects duplicate?” and “Does claim remain indexed?” become concrete checks rather than discussion prompts. That sequence turns database-backed background job execution into something the team can operate and gives me a specific outcome to report: evidence that the queue survives the reason it exists.

What I would show in the work

The public version needs evidence from the work itself. For this topic, the first five artifacts I would reach for are:

• a queue fit decision record
• a versioned jobs table
• an EXPLAIN-backed eligibility index
• a single claim statement or transaction
• a lease and conditional settlement contract

I would not publish all five at equal weight. One should orient the reader, one should reveal the hardest tradeoff, and one should prove the result. The others can live in a downloadable note or appear as supporting frames. That edit matters because a short claim transaction using FOR UPDATE SKIP LOCKED, a lease, stable job identity, idempotent handlers, and explicit terminal states becomes harder to understand when every process detail is treated as equally important.

I would also show one rejected direction. The useful version is specific: which option looked attractive, which constraint made it wrong, and what evidence supported the narrower choice. That gives an engineering manager something real to question and keeps the case study from reading like the final answer was obvious from the beginning.

# select
WHERE status='ready' AND run_at<=now()
Order by priority desc, run_at, id; FOR UPDATE SKIP LOCKED; LIMIT batch size.
# update
set running + lease
One statement or CTE returns claimed rows with worker_id, lease_expires_at, attempts+1, and trace correlation.
# settle
WHERE id=? AND worker_id=?
Completion is conditional on current lease ownership; retry computes next run_at; dead state records failure taxonomy.

Resource path

The practical follow-up I would build is a PostgreSQL queue starter with jobs schema, claim query, lease, worker identity, retry schedule, idempotency key, heartbeat, dead-letter state, indexes, metrics, cleanup, and concurrency tests. I am treating that as a resource backlog item, not pretending the adjacent downloads below are the same artifact. The related cards cover useful pieces of the workflow today; this specific file should only be published when its examples, fields, and instructions are complete.

The first version should stay concise: context, constraint, decision, evidence, owner, and follow-up. Its value would come from helping someone repeat this exact review, not from adding another generic PDF to the site.

Review checklist

The article-specific review questions are:

• What volume and latency exist?
• What identifies the intent?
• Which rows are scanned?
• Which rows are locked?
• How long is normal work?
• What side effect can repeat?
• Is the failure transient?
• Can priority age?
• Which backlog is unhealthy?
• Can two workers own one lease?

I would add two editorial checks before publishing: can a recruiter find the point in the first minute, and can an engineer trace at least one claim to an implementation or production receipt? If either answer is no, the article needs another edit.

Implementation notes

For database-backed background job execution, I would write the implementation note before polish. It would name the changed surface, source of truth, owner, failure boundary, and verification path. Those details prevent the principle from floating above the actual code or operational workflow.

The proof signals I care about are specific to this article:

• safe retries after ambiguous outcomes
• bounded recovery without poison-job storms
• important work first without abandoned ordinary work
• diagnosable and controlled background work
• evidence that the queue survives the reason it exists

I would choose two or three of those signals for the first release rather than instrumenting everything. The strongest pair usually combines one direct behavior check with one operating check: a route and a data query, a keyboard path and a support state, a handler replay and a reconciliation result, or a migration count and a rendered screen.

The follow-up belongs in the note before shipping. It should say what remains temporary, what evidence would trigger another pass, and who owns that decision. That is how the first version stays intentionally narrow without making the boundary invisible.

Case-study packaging

I would structure the case-study version around the four visual lessons already established:

• Claim quickly, execute outside the transaction, settle explicitly.
• Every job moves through durable states.
• Failure class decides the next transition.
• The claim query must stay narrow and indexed.

The opening frame explains the product pressure. The middle two show the decision moving through the system. The last frame is the receipt: what was checked, what held, and what remained unresolved. That order lets the reader move from product judgment into implementation detail without reconstructing the whole project first.

I would include one caveat tied to small product systems that need multiple workers to claim durable jobs without duplicate concurrent execution, while handling crashes, retries, poison jobs, priority, scheduling, observability, retention, and operational recovery: a data limit, rollout boundary, unsupported state, external dependency, or result that is still directional. A precise caveat makes the evidence easier to trust because it shows where the claim stops.

The final test is whether the page creates a better conversation. If the artifact helps someone ask a sharper question about product judgment, implementation detail, or release proof in a live interview, it belongs in the story.

Interview angle

In an interview, I would explain this through a short claim transaction using FOR UPDATE SKIP LOCKED, a lease, stable job identity, idempotent handlers, and explicit terminal states. The story should start with the product pressure, then move into the system constraint, the artifact, and the proof. That order keeps the answer grounded. It also gives the interviewer several places to go deeper: data, frontend architecture, design systems, support, migration, accessibility, or release process.

The strongest version of the answer includes a tradeoff. I want to be able to say what I chose, what I left alone, and how I knew the work helped. That is more credible than presenting every project as a clean win.

The hiring signal

A SKIP LOCKED queue is a hiring signal because it shows I understand where database locking helps, where it does not, and how to build the operating system around a compact concurrency primitive.

That is the level I want this site to communicate. The work should show taste, but it should also show operating judgment. It should make me look like someone who can enter a real product system, understand the messy middle, ship the useful version, and leave enough proof for the next person to trust it.