← Lab Runs in your browser

Do this later

Your app often needs to do something later: send this email in two hours, retry this webhook in five minutes. A job scheduler is the part that remembers to do it. Below: the simple version that breaks, the table that fixes it, and the three problems the table brings with it.

Live demo PostgreSQL Queues

The simple way

setTimeout(sendEmail, 2 * 60 * 60 * 1000); // in two hours

It works on your laptop. In production it breaks:

The server restarts and the timer is gone. The email never goes out.
The timer lives in one process, so you can't add servers to share the work.
If sending fails, nothing tries again.
Anything set more than about 24.8 days ahead fires right away instead.
You can't ask what's waiting to run. It's all in memory.

Put the jobs in a table

Each row is one job, with a time it should run. Worker processes keep asking the table: anything due now? If the server dies, the rows are still there. If there's too much work, start more workers.

CREATE TABLE jobs (
  id         uuid PRIMARY KEY,
  type       text,         -- which code runs it
  run_at     timestamptz,  -- not before this time
  status     text,         -- waiting, running, done, dead
  attempts   int,
  locked_at  timestamptz   -- when a worker took it
);

That brings three new problems. The demo below shows each one.

Run it

1. Two workers grab the same job

Worker A reads "Email 5 is due". At the same moment, Worker B reads the same thing. Both send it, and the customer gets it twice. The fix is to take a job in one step: lock it and mark it as yours together, so other workers skip it.

Ten emails, three workers.

What happens shows up here.

Both versions in SQL

-- read what's due
SELECT * FROM jobs
 WHERE status = 'pending' AND run_at <= now()
 LIMIT 2;

-- ...another worker can read the same rows right here...

-- then mark it
UPDATE jobs SET status = 'running' WHERE id = ANY($1);

UPDATE jobs SET status = 'running', locked_at = now()
 WHERE id IN (
   SELECT id FROM jobs
    WHERE status = 'pending' AND run_at <= now()
    ORDER BY run_at
    LIMIT 2
    FOR UPDATE SKIP LOCKED   -- lock them; others skip locked rows
 )
RETURNING *;

2. A job fails

The mail server is down and sending throws an error. Giving up is wrong. Try again in 2 seconds, then 4, then 8, so you don't hammer a server that's already struggling. After three tries, stop and move the job to a dead pile for a person to look at.

Six emails. The mail server fails 7 times in 10.

What happens shows up here.

The retry in code

const attempts = job.attempts + 1;
if (attempts < job.max_attempts) {
  // 2s, 4s, 8s... capped at 60, plus a random bit so failed
  // jobs don't all come back at the same moment
  const wait = Math.min(2 ** attempts, 60) + Math.random();
  // back to 'pending', with run_at = now() + wait
} else {
  // status = 'dead', for a person to look at
}

With a server that fails 7 times in 10, about a third of the jobs still end on the dead pile. That's the right result. The promise is that every job ends somewhere, not that every job succeeds.

3. A worker crashes halfway through

Worker A takes a job, marks it "running", and crashes. Now the job says "running" forever and nobody touches it. The fix is a cleanup loop, called a reaper: if a job has said "running" for too long, put it back in the queue.

Four emails, two workers. Worker A crashes on its second job.

What happens shows up here.

The reaper in SQL

-- every second
UPDATE jobs SET status = 'pending', locked_at = NULL
 WHERE status = 'running'
   AND locked_at < now() - interval '30 seconds';

Queues have a name for that 30 seconds: the visibility timeout. Amazon SQS uses exactly that term.

The catch

Press "Crash after sending" above. The worker sent the email, then died before it wrote "done". The reaper can't tell the difference, so it puts the job back and the email goes out a second time.

You can't fully prevent that. So the job itself has to be safe to run twice: it checks whether this email already went out before sending it. That's called idempotency, and it's the next lab.

What this isn't

The demo is a model. The table is a list in memory, each query waits a short random time, and the clock runs five times faster than real time. It doesn't run Postgres.

The same three fixes run against a real Postgres 16 in Node, with three workers and a reaper. Two emails, sent with "read, then mark", went out 4, 4, 6, 6, 2, 6, 4 and 6 times across eight runs. With the one-step claim they went out twice, once each, in five runs out of five. A full run ends like this:

$ npm run after          # one-step claim, retries, reaper
  reaper: reset 1 stuck job(s)

final job states: [ { status: 'dead', n: 2 }, { status: 'done', n: 7 } ]

A table is a good place to start, not the answer at any size. Workers asking every 100 ms means up to 100 ms of delay and a steady stream of empty queries. Postgres can notify workers instead, and at high volume a dedicated queue like SQS or RabbitMQ is the better tool. The three problems stay the same; they just get solved for you.

Where this came from

Interview preparation, published. "Design a job scheduler" is a common system design question. I built it instead of drawing it, to see where it actually breaks.

Transactions, and what they cost → Worker pools, measured →