Postgres job queue: stop sending emails inside the request
From the work/ 16 min read
A jobs table, a Node worker that claims rows with FOR UPDATE SKIP LOCKED, retries with exponential backoff and stuck-job recovery. Tested on PGlite.
- Databases
- Node.js
In this article (8)
To get email sending out of the request, write the work to a Postgres jobs table and respond right away. A separate process, the worker, claims each job with FOR UPDATE SKIP LOCKED, runs it, and marks it done or schedules a retry. That is a job queue with no new services to run.
Below are the table, a Node worker on Postgres.js, retries and stuck-job recovery, plus the tests we ran against PGlite (PostgreSQL 18.6 compiled to WebAssembly). We covered the FOR UPDATE row lock in how to prevent inventory overselling; here we add SKIP LOCKED.
The cost of doing heavy work inside the request
When a shopper pays, your endpoint creates the order and sends a confirmation email. If the email goes out inside the same request, the shopper waits on the email provider, which brings four problems:
- Latency. In our demo, with a fake 300 ms sender, the order answered in 307 ms; with the email enqueued, in 2–4 ms. It is a local test, so your network will give other numbers, but the request stops waiting.
- Coupled failures. If the provider is down, the order fails; if you catch the error, the email is lost without a trace.
- No retries. A temporary error, such as an SMTP
421, should be retried in a minute. - Your caller's timeouts. According to Shopify's HTTPS webhook documentation, Shopify allows one second to connect and five for the whole request, and with no response it retries 8 times over 4 hours. The same page recommends queuing to respond within those five seconds.
Firing the send without await does not help: the promise lives in process memory and disappears, with no record, on a restart or a deploy. The alternative is for the request to store only what needs doing. These are two versions of the same order, from demo.mjs:
const fakeEmail = () => new Promise((resolve) => setTimeout(resolve, 300));
async function placeOrderInline(email) {
const [order] = await sql`INSERT INTO orders (email) VALUES (${email}) RETURNING id`;
await fakeEmail(); // the request waits for the provider
return order.id;
}
async function placeOrder(email) {
return sql.begin(async (tx) => {
const [order] = await tx`INSERT INTO orders (email) VALUES (${email}) RETURNING id`;
await enqueue(tx, "order-confirmation", { orderId: order.id, email });
return order.id;
});
}
fakeEmailstands in for the email provider and takes 300 ms.placeOrderInlinecreates the order and waits for the email: the request lasts as long as the send, and a failed send fails the order.placeOrdercreates the order and inserts the job withenqueueinsidesql.begin. Both commit together or neither does, so there is never a pending email for an order that does not exist; one of our tests checks it.
Scroll the figure sideways to see all of it
A webhook works the same way: verify, enqueue, answer 200, as we explain in how to verify Shopify webhook HMAC signatures.
The Postgres jobs table
A queue in Postgres is a table where every row is a job. This is schema.sql, the same one the tests use:
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
type text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'running', 'done', 'failed')),
attempts int NOT NULL DEFAULT 0,
max_attempts int NOT NULL DEFAULT 5,
run_at timestamptz NOT NULL DEFAULT now(),
locked_at timestamptz,
last_error text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX jobs_pending_run_at ON jobs (run_at) WHERE status = 'pending';
typesays what to do (order-confirmation) andpayloadcarries the data asjsonb: the order id and the address, not the HTML.statusonly acceptspending,running,doneandfailed, enforced by theCHECK.attemptscounts how many times the job was claimed;max_attemptscaps it, 5 by default.run_atis when the job may run:now()on enqueue, a future time after a failure.locked_atrecords when a worker claimed it andlast_errorkeeps the latest error.- The partial index
jobs_pending_run_atonly holdspendingrows, ordered byrun_at.
Finished jobs pile up while pending ones stay few, which is the case in PostgreSQL's partial index documentation, whose example is a small set of heavily queried unbilled orders. The same page warns that the index is only used when the query's WHERE implies its predicate, so the worker must ask for status = 'pending' exactly. With 20,000 rows and 50 pending, EXPLAIN on the worker's query in PGlite showed Index Scan using jobs_pending_run_at.
Enqueuing is an INSERT. This is enqueue.mjs:
export async function enqueue(sql, type, payload) {
const [job] = await sql`
INSERT INTO jobs (type, payload)
VALUES (${type}, ${sql.json(payload)})
RETURNING id`;
return job.id;
}
sqlcan be the Postgres.js client or thetxof a transaction, so the job commits together with the order.sql.json(payload)sends the object as JSON. WithJSON.stringify(payload)instead, our test'sjsonbcolumn stored a string andpayload->>'email'returnednull.- Every other column takes its default, so the job starts
pendingand due.RETURNING idgives back its id, as a string because it is abigint.
The queue worker with FOR UPDATE SKIP LOCKED
The worker claims a job, runs it and records the outcome. Claiming is the delicate part: with several workers, each job must go to exactly one. This is the function, at the top of worker.mjs:
export async function claimJob(sql) {
const [job] = await sql`
UPDATE jobs
SET status = 'running', locked_at = now(), attempts = attempts + 1, updated_at = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending' AND run_at <= now()
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING *`;
return job ?? null;
}
- The inner
SELECTfinds the oldest pending job that is due and keeps one. FOR UPDATElocks that row until the transaction ends;SKIP LOCKEDmeans that if another transaction already locked it, the row is skipped and the next one taken instead of waiting.- The outer
UPDATEsets it torunning, stampslocked_atand adds one toattempts. RETURNING *returns the updated row. With nothing pending no row is updated, which PostgreSQL's UPDATE documentation says is not an error, and the function returnsnull.- It is a single statement and its own transaction: the lock lasts as long as the
UPDATE, not the email. After that,status = 'running'keeps the other workers away.
The locking clause of SELECT puts it this way: with SKIP LOCKED, rows that cannot be locked immediately are skipped. It warns that this gives an inconsistent view of the data, so it is not for general-purpose work, but it can avoid lock contention between multiple consumers of a queue-like table. For stock it would be a bug, as the overselling article explains.
Scroll the figure sideways to see all of it
Without SKIP LOCKED, per the explicit locking documentation, a SELECT FOR UPDATE that hits the locked row waits for the other transaction to finish: nothing runs twice, but workers queue up behind each other. With no lock at all, in two statements, two workers can read the same id before either marks it.
To run the worker, start.mjs connects, registers the handlers and enters the work loop:
import postgres from "postgres";
import { work, recoverStuck } from "./worker.mjs";
// Fake sender: replace it with your email provider's client.
async function sendOrderEmail({ orderId, email }) {
await new Promise((resolve) => setTimeout(resolve, 300));
console.log(`email sent to ${email} for order ${orderId}`);
}
const sql = postgres(process.env.DATABASE_URL, { max: 1 });
const handlers = { "order-confirmation": sendOrderEmail };
setInterval(() => recoverStuck(sql).catch(console.error), 60_000);
await work(sql, handlers);
sendOrderEmailis the fake sender; your provider's client goes here.handlersmaps eachtypeto its function.- One connection (
max: 1) is all a worker loop needs. - Every minute,
recoverStuckrescues stuck jobs, andworkclaims and runs jobs in a loop.
In demo.mjs we started PGlite with its socket server, launched start.mjs as a separate Node process and placed three orders: each answered in 2–4 ms, the worker printed all three email sent to … lines, and the three jobs ended done with attempts at 1.
What our tests prove, and what they do not
queue.test.mjs uses Node's test runner and PGlite on 127.0.0.1, with one connection per worker, as separate processes would have. This is the part of the node --test --test-reporter=spec queue.test.mjs output that covers concurrency:
sent 50 emails for 50 orders; per worker 13 / 13 / 10 / 14
✔ 4 workers in parallel: each of 50 jobs is processed exactly once (2141.5746ms)
naive claim: 200 emails for 50 orders
✔ control: a two-step claim (SELECT, then UPDATE) processes jobs more than once (411.4018ms)
no locking clause, PGlite: 50 emails for 50 orders
✔ PGlite limit: the same single UPDATE without FOR UPDATE SKIP LOCKED also passes here (125.0162ms)
B waited 318 ms and got job 1 (per the docs, SKIP LOCKED in real Postgres skips row 1 and returns job 2)
✔ PGlite limit: a second connection waits for an open transaction instead of skipping its row (357.4703ms)
- Four
workloops in parallel: 50 emails for 50 orders, spread across all four. - The control test claims in two statements with no lock, and every order gets four emails. It proves the statements from the four connections really interleave.
- The single-statement claim without
FOR UPDATE SKIP LOCKEDalso passes on PGlite, and a second connection waits for an open transaction instead of skipping its row.
That is the limit of these tests. PGlite is a single-connection database; its socket server multiplexes, and its README warns that not all use cases are guaranteed to work as on a normal Postgres. Its code runs one query at a time and, while a transaction is open, only serves that connection, so two transactions never overlap and SKIP LOCKED never has anything to skip. What is proven: with interleaved statements every job is processed exactly once, and a two-step claim fails at that. That SKIP LOCKED avoids waiting between concurrent transactions comes from the PostgreSQL documentation; we did not measure it.
Retries and failed jobs
Once a job is claimed, runOne runs it and records how it ended. This is the rest of worker.mjs, except recoverStuck:
export async function runOne(sql, handlers, { backoffSeconds = 30 } = {}) {
const job = await claimJob(sql);
if (!job) return null;
try {
const handler = handlers[job.type];
if (!handler) throw new Error(`no handler for job type ${job.type}`);
await handler(job.payload, job);
await sql`
UPDATE jobs SET status = 'done', locked_at = NULL, updated_at = now()
WHERE id = ${job.id} AND attempts = ${job.attempts}`;
return job;
} catch (err) {
const status = job.attempts >= job.max_attempts ? "failed" : "pending";
const delay = backoffSeconds * 2 ** (job.attempts - 1);
await sql`
UPDATE jobs SET status = ${status}, locked_at = NULL, last_error = ${String(err?.message ?? err)},
run_at = now() + make_interval(secs => ${delay}), updated_at = now()
WHERE id = ${job.id} AND attempts = ${job.attempts}`;
return job;
}
}
const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
export async function work(sql, handlers, { idleMs = 1000, signal } = {}) {
while (!signal?.aborted) {
if (!(await runOne(sql, handlers))) await sleep(idleMs);
}
}
- With no job,
runOnereturnsnull. Atypewith no handler counts as one more error instead of crashing the worker. - If the handler finishes, the job moves to
done. - If it throws, the job goes back to
pendingor, once it reachesmax_attempts, staysfailed, with the message inlast_error. - The wait grows as
backoffSeconds * 2 ** (attempts - 1)from 30 s, added tonow()inrun_at. Until then,claimJobdoes not see the job. AND attempts = ${job.attempts}guards both final writes: if another worker claimed the job in the meantime,attemptshas changed and the late worker cannot overwrite its result.workrepeatsrunOneand sleeps one second when the queue is empty.
The retry test uses a handler that always fails with SMTP 421: try again later, checks that claimJob does not return the job early, and moves run_at forward so it does not really wait. This is what the table held after each attempt:
| Attempt | Status after | Wait until the next one |
|---|---|---|
| 1 | pending | 30 s |
| 2 | pending | 60 s |
| 3 | pending | 120 s |
| 4 | pending | 240 s |
| 5 | failed | None: never claimed again |
That is about seven and a half minutes in all. For more room, raise max_attempts or backoffSeconds. A failed job keeps its last_error; to retry it, set it back to pending with attempts = 0.
Scroll the figure sideways to see all of it
Jobs stuck in running
If the worker dies after claiming a job (a restart, a deploy), the row stays running forever. That is what recoverStuck is for:
export async function recoverStuck(sql, minutes = 15) {
return sql`
UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
locked_at = NULL, last_error = 'stuck in running, lock expired', updated_at = now()
WHERE status = 'running' AND locked_at < now() - make_interval(mins => ${minutes})
RETURNING id, status`;
}
- It finds
runningjobs claimed more thanminutesminutes ago, 15 by default. - It puts them back to
pending, or tofailedwhen they are out of attempts, so a job that kills the worker every time does not loop forever. - It notes the reason in
last_errorand returns each job's id and new status.
Pick minutes longer than your slowest job: if a live one crosses the limit, it runs twice. Our test forces that: A claims the job and hangs, we age its locked_at, recoverStuck frees it and B finishes it. When A wakes up and fails, the attempts guard drops its write and the job ends done with attempts at 2. Remove the guard and the test fails: A sets a finished job back to pending.
It also shows the real guarantee: every job runs at least once, not exactly once. Make handlers idempotent: check whether that order already has its email, and if your provider accepts an idempotency key, pass a stable one, as in idempotency keys for payments.
When a dedicated tool is worth it
The table and about sixty lines of Node cover the basics. For more, there are dedicated tools. This is what each says about itself:
| Tool | What it says it is | Where jobs live |
|---|---|---|
| pg-boss | A job queue for Node.js built on PostgreSQL, relying on SKIP LOCKED, with priority queues, dead letter queues, retries with exponential backoff and cron. | PostgreSQL 13 or higher (needs Node 22.12+) |
| Graphile Worker | A high performance job queue for PostgreSQL, written in Node.js, that starts jobs in milliseconds with LISTEN/NOTIFY. | PostgreSQL |
| BullMQ | A fast and robust distributed job and message queue; its docs describe at-least-once delivery and ask for idempotent processors. | Redis or compatibles, and also PostgreSQL |
| Amazon SQS | Fully managed message queuing for microservices, distributed systems and serverless applications. | An AWS service |
Our rule: start with the table if you already run Postgres and your volume fits. Switch when you need cron, priorities, job dependencies, a dashboard, or that volume off your main database. pg-boss and Graphile Worker stay in Postgres and keep the advantage of enqueuing in the order's transaction; with Redis or SQS the job lives outside your database and you build that guarantee yourself.
Common mistakes
- Claiming in two steps. A
SELECTfollowed by anUPDATEleaves a gap: in our test, 200 emails for 50 orders. Use one statement withFOR UPDATE SKIP LOCKED. - Running the job inside the transaction. Send the email before
COMMITand the row stays locked and the connection busy for the whole send. Claim, commit, then do the work. - Forgetting stuck jobs. Without
recoverStuck, a worker that dies leaves jobs inrunningforever. - Expecting strict ordering. The
SELECTdocs warn thatORDER BYwith a locking clause can return rows out of order under Read Committed, andSKIP LOCKEDlets one job jump ahead of another. The order is approximate. - Handlers that are not idempotent. The queue guarantees at least one run: if the email went out and the worker died before marking it, the retry sends it again.
- Letting the table grow. Our recommendation: delete
donejobs older than a few days with a periodicDELETE, and reviewfailedones before deleting them.
Frequently asked questions
Do I have to poll the table every second?
With the partial index, polling an empty queue every second is cheap. For less delay there is LISTEN/NOTIFY: per the NOTIFY documentation, a notification sent in a transaction is only delivered if it commits, the payload must be shorter than 8000 bytes by default, and only sessions that already ran LISTEN receive it. Use it to wake the worker and keep the polling. It is not part of the tested code.
How many workers should I run?
Start with one or two and watch how many pending jobs are past their run_at. If that number keeps growing, add workers, keeping in mind that each one holds a Postgres connection.
Can I schedule a job for later?
Yes: insert the row with a future run_at, for example now() + interval '1 hour'. claimJob ignores it until then, just like a waiting retry; the retry test checks that behavior.
What happens if the worker crashes right after sending the email?
The job stays running; after 15 minutes, recoverStuck sets it back to pending and another worker runs it again. The shopper gets two emails unless the handler first checks whether it already sent one.
Checklist
- The request stores the job in the same transaction as the order and responds.
- The table has
statuswith aCHECK,attempts,max_attempts,run_atandlocked_at, plus a partial index onpending. - The worker claims each job in one statement:
UPDATE … WHERE id = (SELECT … FOR UPDATE SKIP LOCKED) RETURNING *. - The email is sent outside any transaction.
- A failure goes back to
pendingwith exponential backoff and ends infailedaftermax_attempts. recoverStuckruns every minute with a limit longer than the slowest job.- Final writes carry the
AND attempts = …guard, and handlers are idempotent. - Finished jobs are deleted after a set period.
With a queue, the request does the minimum and the slow part retries on its own. Start with your webhooks: verify the Shopify webhook HMAC, enqueue and respond. If the job touches stock, revisit how to prevent inventory overselling; if it charges money, how idempotency keys stop double charges.
Saw this on Instagram? Comment COLAS on the Reel and I'll send you the guide.
Written by

Published November 3, 2026