Skip to content

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)
  1. The cost of doing heavy work inside the request
  2. The Postgres jobs table
  3. The queue worker with FOR UPDATE SKIP LOCKED
  4. Retries and failed jobs
  5. When a dedicated tool is worth it
  6. Common mistakes
  7. Frequently asked questions
  8. Checklist

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;
  });
}
  1. fakeEmail stands in for the email provider and takes 300 ms.
  2. placeOrderInline creates the order and waits for the email: the request lasts as long as the send, and a failed send fails the order.
  3. placeOrder creates the order and inserts the job with enqueue inside sql.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.
the same order, with the email inside and outside the requestbefore · the request sends the emailrequestINSERTsendEmail() · 300 ms201if the provider fails or stalls, so does the orderafter · the request enqueues and answersrequestINSERT order + INSERT jobs201 in 2–4 msworkerclaimJob()sendEmail()doneanother process; a failure is retriedthe shopper no longer waits for the email providermeasured on local PGlite with a fake 300 ms sender: 307 ms vs 2–4 ms

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';
  1. type says what to do (order-confirmation) and payload carries the data as jsonb: the order id and the address, not the HTML.
  2. status only accepts pending, running, done and failed, enforced by the CHECK.
  3. attempts counts how many times the job was claimed; max_attempts caps it, 5 by default.
  4. run_at is when the job may run: now() on enqueue, a future time after a failure. locked_at records when a worker claimed it and last_error keeps the latest error.
  5. The partial index jobs_pending_run_at only holds pending rows, ordered by run_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;
}
  1. sql can be the Postgres.js client or the tx of a transaction, so the job commits together with the order.
  2. sql.json(payload) sends the object as JSON. With JSON.stringify(payload) instead, our test's jsonb column stored a string and payload->>'email' returned null.
  3. Every other column takes its default, so the job starts pending and due. RETURNING id gives back its id, as a string because it is a bigint.

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;
}
  1. The inner SELECT finds the oldest pending job that is due and keeps one.
  2. FOR UPDATE locks that row until the transaction ends; SKIP LOCKED means that if another transaction already locked it, the row is skipped and the next one taken instead of waiting.
  3. The outer UPDATE sets it to running, stamps locked_at and adds one to attempts.
  4. 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 returns null.
  5. 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.

two workers, two different jobs: SKIP LOCKED skips the taken rowjobs table1pendinglocked by A2pendinglocked by B3pending4pending5pendingworker Aarrives firstworker Btakes 2, no waitingskips 1SELECT id FROM jobs WHERE status = 'pending' AND run_at <= now()ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKEDwithout SKIP LOCKED, B would wait for A to commita locked row is skipped: an inconsistent view, fine for queue-like tables

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);
  1. sendOrderEmail is the fake sender; your provider's client goes here. handlers maps each type to its function.
  2. One connection (max: 1) is all a worker loop needs.
  3. Every minute, recoverStuck rescues stuck jobs, and work claims 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)
  1. Four work loops in parallel: 50 emails for 50 orders, spread across all four.
  2. 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.
  3. The single-statement claim without FOR UPDATE SKIP LOCKED also 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);
  }
}
  1. With no job, runOne returns null. A type with no handler counts as one more error instead of crashing the worker.
  2. If the handler finishes, the job moves to done.
  3. If it throws, the job goes back to pending or, once it reaches max_attempts, stays failed, with the message in last_error.
  4. The wait grows as backoffSeconds * 2 ** (attempts - 1) from 30 s, added to now() in run_at. Until then, claimJob does not see the job.
  5. AND attempts = ${job.attempts} guards both final writes: if another worker claimed the job in the meantime, attempts has changed and the late worker cannot overwrite its result. work repeats runOne and 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:

AttemptStatus afterWait until the next one
1pending30 s
2pending60 s
3pending120 s
4pending240 s
5failedNone: 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.

the states of a job and what changes themstatuswhentimestampsclaimedpendingenqueue()run_at = now()yesrunningclaimJob()locked_at = now()nodonehandler succeedslocked_at = NULLnopendingfails, n < max_attemptsnow() + 30 s · 2^(n-1)when duefailedfails, n = max_attempts—neverpendingstuck > 15 minlocked_at = NULLyesa failure goes back to the queue until attempts run outn = attempts · tested waits: 30, 60, 120 and 240 s, then failed

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`;
}
  1. It finds running jobs claimed more than minutes minutes ago, 15 by default.
  2. It puts them back to pending, or to failed when they are out of attempts, so a job that kills the worker every time does not loop forever.
  3. It notes the reason in last_error and 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:

ToolWhat it says it isWhere jobs live
pg-bossA 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 WorkerA high performance job queue for PostgreSQL, written in Node.js, that starts jobs in milliseconds with LISTEN/NOTIFY.PostgreSQL
BullMQA 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 SQSFully 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 SELECT followed by an UPDATE leaves a gap: in our test, 200 emails for 50 orders. Use one statement with FOR UPDATE SKIP LOCKED.
  • Running the job inside the transaction. Send the email before COMMIT and 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 in running forever.
  • Expecting strict ordering. The SELECT docs warn that ORDER BY with a locking clause can return rows out of order under Read Committed, and SKIP LOCKED lets 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 done jobs older than a few days with a periodic DELETE, and review failed ones 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 status with a CHECK, attempts, max_attempts, run_at and locked_at, plus a partial index on pending.
  • 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 pending with exponential backoff and ends in failed after max_attempts.
  • recoverStuck runs 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

Luis Morales

Luis Morales

Frontend and Shopify developer, EcomLabs

@luismoralesdev on Instagram

Published November 3, 2026

← Blog