Background job queues

Put a job on a durable queue (a broker, or a PostgreSQL table claimed with SKIP LOCKED) and let workers process it outside the request. Reliability comes from acknowledging only after the work is done, retrying failures with backoff, capping attempts and moving poison jobs to a dead-letter queue, and making each job idempotent because it may run more than once. Enqueue in the same transaction as the business change, or through an outbox, so a rollback doesn't send the email anyway.

Visibility timeouts (SQS) or a lease column such as a pushed-back run_at (PostgreSQL) hide in-progress jobs; row locks from SKIP LOCKED last only until commit. Set the lease longer than the job takes.

Store enough in the job to run it later: ids, not whole objects that may change.

Separate queues per priority or job type so slow jobs don't block urgent ones.

Monitor queue depth, age of the oldest job and failure rate.

CREATE TABLE jobs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind text NOT NULL,
  attempts int NOT NULL DEFAULT 0,
  run_at timestamptz NOT NULL DEFAULT now(),
  done bool NOT NULL DEFAULT false
);
CREATE INDEX jobs_ready ON jobs (run_at) WHERE NOT done;
INSERT INTO jobs (kind) VALUES ('welcome_email'), ('resize_avatar');
-- Claim one job and lease it for 5 minutes: the row lock ends at
-- commit, so pushing run_at hides the job from other workers. If
-- this worker dies, the job becomes visible again after the lease.
UPDATE jobs
SET attempts = attempts + 1, run_at = now() + interval '5 minutes'
WHERE id = (
  SELECT id FROM jobs
  WHERE NOT done AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING kind, attempts;
-- A second worker now gets the next job, not the leased one
UPDATE jobs
SET attempts = attempts + 1, run_at = now() + interval '5 minutes'
WHERE id = (
  SELECT id FROM jobs
  WHERE NOT done AND run_at <= now()
  ORDER BY run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING kind, attempts;
-- On success the worker sets done = true for its job id

Common mistakes

• Acknowledging before doing the work.
• Enqueuing outside the transaction, so jobs run for rolled-back data.
• No maximum attempts, so a poison job retries forever.

Durable queue, ack after work, retries with DLQ, idempotent jobs, and transactional enqueue.

Follow-up questions

Think of your answer first, then open the hint.

1. Postgres table or a broker?

   Show hint

   A table is simple and transactional up to moderate volume; brokers for high throughput and fan-out.

2. What's a visibility timeout?

   Show hint

   How long a received job stays hidden before another worker may take it.