Building reliable background job processing with PostgreSQL, Node.js, and a few important distributed systems concepts.
At some point, almost every backend application needs to execute something asynchronously.
Maybe you need to process an order, send a webhook, schedule an API request, or retry an operation that failed.
The typical solution is to introduce a message queue.
For Node.js applications, that often means BullMQ, Redis, and one or more worker processes.
There's nothing wrong with that approach. BullMQ is a great tool, and Redis is extremely useful for background processing.
But while building Asynclay, a service for scheduling and executing background HTTP jobs, I wanted to explore a different approach:
Could PostgreSQL itself handle the job queue reliably enough for my use case?
I was already using PostgreSQL to persist jobs, accounts, projects, and execution history. Introducing another infrastructure dependency just for job coordination didn't seem necessary at this stage.
So I decided to build a PostgreSQL-backed dispatcher.
It started with a simple query.
Then came concurrency, race conditions, worker crashes, retries, leases, and the realization that building a reliable queue is much more interesting than simply polling a database.
Here's how I approached it.
1. The simplest possible job queue
The initial idea was straightforward.
Each job would have:
- A target URL
- A JSON payload
- An execution time
- A status
- An attempt counter
A simplified PostgreSQL table might look like this:
CREATE TABLE jobs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
target_url TEXT NOT NULL,
payload JSONB NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
run_at TIMESTAMPTZ NOT NULL,
next_attempt_at TIMESTAMPTZ,
attempts INTEGER NOT NULL DEFAULT 0,
max_attempts INTEGER NOT NULL DEFAULT 3,
locked_until TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
The first implementation could simply query for pending jobs:
SELECT *
FROM jobs
WHERE status = 'PENDING'
AND run_at <= NOW()
ORDER BY run_at
LIMIT 10;
Then execute each job:
for (const job of jobs) {
await executeJob(job);
}
For a single worker running under ideal conditions, this seems reasonable.
Unfortunately, it breaks as soon as multiple workers are involved.
2. The concurrency problem
Imagine two application instances running the same dispatcher.
Both execute the query at approximately the same time.
Worker A Worker B
| |
| SELECT pending jobs |
| | SELECT pending jobs
| |
| Job #123 | Job #123
| |
| Execute Job #123 | Execute Job #123
| |
Both workers found the same pending job.
Both execute it.
This is particularly dangerous when the job triggers an external side effect, such as charging a customer or creating an order.
A simple application-level mutex wouldn't solve the problem because the workers might run in different processes or servers.
I needed a way to coordinate workers through PostgreSQL.
That's where FOR UPDATE SKIP LOCKED comes in.
3. Understanding FOR UPDATE SKIP LOCKED
PostgreSQL supports row-level locking through SELECT ... FOR UPDATE.
When a transaction selects a row with FOR UPDATE, it acquires a lock that prevents another transaction from acquiring a conflicting lock on that row until the first transaction completes.
For a queue, this is useful because multiple workers can safely claim different jobs.
But there's a problem with ordinary FOR UPDATE: workers can end up waiting for rows locked by other workers.
Adding SKIP LOCKED changes that behavior.
SELECT *
FROM jobs
WHERE status = 'PENDING'
AND run_at <= NOW()
ORDER BY run_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
Instead of waiting for locked rows, PostgreSQL skips them.
Imagine three available jobs:
Job #101
Job #102
Job #103
Worker A locks Job #101.
While that transaction is still open, Worker B executes the same query.
Because Job #101 is locked, Worker B skips it and can claim Job #102.
Worker A Worker B
| |
| Lock Job #101 |
| | Skip Job #101
| | Lock Job #102
| |
| Process #101 | Process #102
This gives us a useful foundation for concurrent job dispatching.
One important detail: SKIP LOCKED provides an intentionally inconsistent view of the table. That's generally acceptable for a queue, but it's not appropriate for arbitrary business queries requiring a consistent snapshot.
4. Claiming jobs atomically
The next step was ensuring that claiming a job and changing its status happened inside the same database transaction.
A simplified SQL implementation looks like this:
BEGIN;
SELECT id
FROM jobs
WHERE status = 'PENDING'
AND run_at <= NOW()
ORDER BY run_at, id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- Update the selected jobs before committing.
COMMIT;
A more compact approach uses a common table expression:
WITH claimable AS (
SELECT id
FROM jobs
WHERE status = 'PENDING'
AND run_at <= NOW()
ORDER BY run_at, id
LIMIT 10
FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET
status = 'PROCESSING',
attempts = attempts + 1,
locked_until = NOW() + INTERVAL '60 seconds'
WHERE id IN (SELECT id FROM claimable)
RETURNING *;
This statement selects available jobs, locks them, updates their state, and returns the claimed rows.
Concurrent workers executing it won't normally claim the same row at the same time.
For a production implementation, the claim predicate should also account for retryable jobs, execution limits, and any account-level concurrency rules.
For example:
WHERE (
status = 'PENDING'
AND run_at <= NOW()
)
OR (
status = 'RETRYING'
AND next_attempt_at <= NOW()
)
The important principle is:
Claim the job atomically before performing the external operation.
5. Never keep the database transaction open during HTTP execution
This was one of the most important architectural decisions.
At first, it might seem convenient to lock the job and execute it while the transaction is still active.
Something like:
await dataSource.transaction(async (manager) => {
const job = await claimJob(manager);
await axios.post(job.targetUrl, job.payload);
await markCompleted(manager, job.id);
});
But this is a bad idea.
HTTP requests can take several seconds or fail entirely.
Keeping the transaction open during that time means holding database locks and consuming a database connection while waiting for an external service.
Instead, I separated claiming from execution.
1. Begin transaction
2. Lock and claim job
3. Mark PROCESSING
4. Commit transaction
5. Execute HTTP request
6. Persist execution result
In TypeScript, the structure becomes:
const claimedJobs = await claimJobs();
await Promise.allSettled(
claimedJobs.map(job => executeJob(job))
);
The database transaction is short.
The potentially slow network operation happens afterward.
However, this creates another problem.
What happens if the worker crashes after claiming a job?
6. Recovering jobs after worker crashes
Imagine this sequence:
Worker claims Job #123
|
v
Status = PROCESSING
|
v
Worker crashes
|
X
The job remains in PROCESSING.
Without a recovery mechanism, it could stay there forever.
To solve this, I introduced a lease.
When a worker claims a job, it sets a deadline:
locked_until = NOW() + INTERVAL '60 seconds'
The lease represents the period during which the worker is expected to complete the execution.
If the worker crashes and never updates the job, the lease eventually expires.
A separate recovery process can find expired jobs:
SELECT *
FROM jobs
WHERE status = 'PROCESSING'
AND locked_until < NOW();
It can then move them back to a retryable state or mark them as permanently failed if the maximum attempt count has been reached.
A simplified recovery operation might look like:
UPDATE jobs
SET
status = CASE
WHEN attempts >= max_attempts
THEN 'FAILED'
ELSE 'RETRYING'
END,
next_attempt_at = CASE
WHEN attempts >= max_attempts
THEN NULL
ELSE NOW()
END,
locked_until = NULL
WHERE status = 'PROCESSING'
AND locked_until < NOW();
In a real implementation, recovery should coordinate with concurrent workers, usually by claiming expired rows transactionally.
There is also an important limitation: a lease is not a guarantee that the original worker has stopped executing.
If an HTTP request hangs beyond the lease duration, another worker may recover and retry the job while the original request is still running.
That means the HTTP timeout, lease duration, and recovery behavior must be designed together.
For my current workload, I use a shorter HTTP timeout than the processing lease, with additional time reserved for database updates.
For long-running jobs, I would consider lease renewal, worker fencing, or a different execution model.
7. Retries with exponential backoff
Failures are inevitable when executing external HTTP requests.
The target server might be temporarily unavailable.
The connection might time out.
A dependency might be experiencing an outage.
Immediately retrying every failed request can make the situation worse.
Instead, I use exponential backoff.
A simple formula is:
function calculateRetryDelay(attempt: number): number {
const baseDelay = 30_000;
return baseDelay * Math.pow(2, attempt - 1);
}
This produces:
| Attempt | Delay before next retry |
|---|---|
| 1 | 30 seconds |
| 2 | 60 seconds |
| 3 | 120 seconds |
The actual number of retries depends on the configured maximum attempt count.
After a failed execution, the job can be updated:
UPDATE jobs
SET
status = 'RETRYING',
next_attempt_at = NOW() + INTERVAL '30 seconds',
locked_until = NULL
WHERE id = $1;
The dispatcher later picks it up using the same claim mechanism.
For a larger system, I'd also introduce jitter to prevent many failed jobs from retrying simultaneously.
One subtle but important point: retries should be based on the semantics of the operation. Not every HTTP failure is transient, and retrying permanent errors indefinitely is rarely useful.
8. The exactly-once execution problem
This is where things become especially interesting.
Suppose a worker sends an HTTP request:
Worker
|
| POST /process-payment
v
Target API
|
| Payment processed successfully
v
HTTP 200
But before the worker can save the successful result, its process crashes.
The database still says:
PROCESSING
Eventually, the lease expires.
Another worker retries the job.
The target API receives the same request again.
The job was executed twice, even though PostgreSQL prevented two workers from claiming it concurrently.
This isn't a bug in SKIP LOCKED.
It's a fundamental problem when coordinating database state with external side effects.
You cannot make an arbitrary HTTP request and a local PostgreSQL update part of the same atomic transaction.
That's why Asynclay uses at-least-once delivery semantics.
A job may be delivered more than once.
The target application should be prepared to handle duplicates.
One useful approach is to include a stable job identifier:
X-Asynclay-Job-Id: 123
X-Asynclay-Attempt: 2
The receiving application can store processed job IDs and reject or safely return a previous result for duplicates.
For example:
CREATE TABLE processed_jobs (
job_id UUID PRIMARY KEY,
processed_at TIMESTAMPTZ DEFAULT NOW()
);
For operations that modify business state, the idempotency record and the business operation should be committed atomically whenever possible.
Otherwise, the target could save the idempotency record, crash before performing the operation, and incorrectly treat future deliveries as completed.
This is an important distinction:
A queue can coordinate job delivery, but exactly-once business effects require cooperation from the receiving application.
9. Handling concurrency limits
Another requirement I encountered was limiting the number of simultaneously executing jobs.
For example, one account might be allowed two concurrent executions while another has a higher limit.
A simple in-memory counter doesn't work reliably across multiple application instances.
If Worker A and Worker B each see one active job, they might both start another execution and exceed the account's limit.
I used PostgreSQL transactions and account-level row locking to coordinate these decisions.
Conceptually:
Begin transaction
|
v
Lock account row
|
v
Count active jobs
|
v
Check concurrency limit
|
v
Claim eligible jobs
|
v
Commit
This serializes concurrency decisions for the same account across workers.
There are tradeoffs.
A heavily used account can become a contention point, and repeatedly counting active jobs isn't free.
For the current scale of the project, however, this is a reasonable tradeoff for correctness and simplicity.
If throughput grows significantly, the design can evolve toward more specialized scheduling and coordination.
10. Indexing matters
Polling PostgreSQL every second is not inherently a problem.
Polling a large table without appropriate indexes is.
The dispatcher frequently queries jobs by status and scheduled execution time.
Useful indexes include:
CREATE INDEX idx_jobs_pending
ON jobs (run_at, id)
WHERE status = 'PENDING';
And:
CREATE INDEX idx_jobs_retrying
ON jobs (next_attempt_at, id)
WHERE status = 'RETRYING';
For recovery:
CREATE INDEX idx_jobs_processing_lease
ON jobs (locked_until)
WHERE status = 'PROCESSING';
These partial indexes help PostgreSQL focus on the rows relevant to each operation.
Of course, indexes aren't free.
Every status transition also updates index entries, so the exact index strategy should be validated against real query plans and workload patterns.
I would rather start with a few targeted indexes and measure than create an index for every column.
11. What I learned about PostgreSQL as a queue
After implementing the dispatcher, retries, leases, and recovery, I came away with a few conclusions.
PostgreSQL is more capable than people sometimes assume
For moderate workloads, PostgreSQL can coordinate background jobs effectively.
Row-level locks and SKIP LOCKED provide a practical foundation for concurrent workers.
If your application already depends on PostgreSQL, using it for job coordination can significantly reduce operational complexity.
SKIP LOCKED doesn't solve everything
It solves an important part of concurrent job claiming.
It does not automatically solve:
- Worker crashes
- Expired leases
- Duplicate external side effects
- Retry policies
- Fair scheduling
- Account-level concurrency
- Monitoring
- Long-running execution
Those problems still require application-level design.
Reliability is mostly about failure handling
The happy path is simple:
Claim → Execute → Complete
The difficult cases are:
Claim → Crash
Execute → Timeout
Execute → Success → Crash before saving
Retry → Target processes duplicate
Lease expires → Original worker still running
A reliable job system is defined by how it handles these situations, not just how quickly it executes successful jobs.
PostgreSQL isn't always the right choice
I wouldn't recommend building your own PostgreSQL queue for every application.
If you need very high throughput, sophisticated scheduling, complex workflows, or mature operational tooling, an established solution may be a better fit.
BullMQ, RabbitMQ, dedicated workflow engines, and managed background-job services all solve different problems.
For my use case, PostgreSQL offered a good balance between reliability, operational simplicity, and control.
12. Where this led: Asynclay
This implementation became part of Asynclay, a developer tool I'm building for background HTTP jobs.
The idea is straightforward:
Your application
|
| POST job
v
Asynclay
|
| PostgreSQL queue
| Scheduling
| Retries
| Recovery
v
Your HTTP endpoint
Instead of deploying and maintaining a separate queue and worker infrastructure, an application submits an HTTP job and lets Asynclay handle its execution.
It's intentionally narrower than a full workflow engine.
The goal isn't to replace BullMQ, Celery, or other established tools.
It's to make one specific use case easier:
"I already have an HTTP endpoint. I just need it executed later, reliably."
I'm currently looking for developers willing to try it and share feedback, especially around reliability, developer experience, and where this approach might fall short.
You can check it out at asynclay.com.
Final thoughts
What started as a simple PostgreSQL polling loop turned into a practical exercise in concurrency control and distributed systems.
FOR UPDATE SKIP LOCKED was the foundation, but the most valuable lessons came from handling failures around it.
If you're building a queue yourself, my biggest recommendation is to think about the failure cases before optimizing throughput.
Ask yourself:
What happens if the worker crashes at every possible point in the execution flow?
The answers will tell you far more about the reliability of your system than a successful benchmark.
Have you used PostgreSQL for background jobs in production? I'd be interested to hear how you handled concurrency, worker recovery, and duplicate execution.
Top comments (1)
tr.ee/dev-to