graphile-worker

Area: Background jobs
Related: PostgreSQL, Microsoft Graph

What is it?

graphile-worker is a job queue for Node.js that uses PostgreSQL as its only storage. Your web app adds a job (“send this email”). A separate worker process picks it up, runs your code, retries it if it fails, and removes it when it succeeds. No Redis, no RabbitMQ, no SQS, just the Postgres you already have.

// enqueue, from anywhere that can talk to the database
await addJob("email.send", { emailId: "e_123" });
 
// handle, in the worker process
const taskList = {
  "email.send": async (payload, helpers) => {
    await sendEmail(payload.emailId);
  },
};

It’s made by the Graphile team (who also make PostGraphile). It’s popular in TypeScript/Postgres stacks where adding another piece of infrastructure isn’t worth it.

Is it backend?

Yes. It only ever runs on a server, as Node code, usually in its own long-running process. The browser never touches it. It stores its state in PostgreSQL, but it isn’t “part of the database”. It’s a backend service that uses the database the way your web server does.

Why background jobs at all?

Some work shouldn’t happen inside a web request:

• Slow: sending email, generating PDFs, calling a slow API. The user shouldn’t wait for it.
• Unreliable: the email provider is down for a minute. You want automatic retries, not a 500 error.
• Scheduled: “every hour, reconcile billing”, “every night, clean up”.
• Must happen even if the request dies: the user closed the tab, the server restarted.

The pattern: the request records what needs doing (fast, reliable), and returns. A worker does it later, retrying until it works.

Why Postgres as the queue?

The usual alternative is Redis plus a library like BullMQ. Using Postgres instead has one huge advantage:

Transactional enqueue

BEGIN;
INSERT INTO email_queue (...) VALUES (...) RETURNING id;
SELECT graphile_worker.add_job('email.send', json_build_object('emailId', <id>));
COMMIT;

The job and your data are in the same transaction. Either both are saved or neither is. With a separate queue like Redis, there are always two failure cases:

• You save the row, then the Redis call fails → the email is never sent.
• You add the job, then the database transaction rolls back → the worker sends an email about something that doesn’t exist.

Fixing that with Redis needs the “transactional outbox” pattern. With a Postgres queue you get it for free.

Other reasons:

• One less service to run, monitor, back up and secure.
• Jobs are rows. You can look at them with SQL.
• Backups of your database include your queue.

The limits

• Throughput: it handles thousands of jobs per second on decent hardware, which covers almost every app. Millions of tiny jobs per minute is the point where a dedicated queue makes sense.
• Every job is a few writes to Postgres. Very high job volume adds load to the same database your app uses.
• No built-in fan-out or pub/sub between services. It’s a work queue, not an event bus.

How it works inside

1. add_job inserts a row into graphile-worker’s jobs table (in its own graphile_worker schema) and sends a Postgres NOTIFY on the jobs:insert channel.
2. Workers are LISTENing on that channel, so they wake up within milliseconds. They also poll on a timer, as a fallback in case a notification is missed.
3. A worker grabs a job with a query using FOR UPDATE SKIP LOCKED. Many workers can run at once, and each grabs a different job without waiting on the others.
4. It marks the job as locked by itself and runs your task function.
5. Success → the job row is deleted.
6. Error (your function throws) → attempts goes up, the error is stored on the job, and run_at is pushed into the future (backoff). Another worker picks it up when the time comes.
7. After max_attempts failures, the job stays in the table, permanently failed, for you to inspect.

Retries and backoff

The default max_attempts is 25. The delay before the next attempt is:

exp(min(attempts, 10)) seconds

That’s about 2.7 s, 7 s, 20 s, 55 s, 2.5 min, 7 min, 18 min, 50 min, 2.25 h, then about 6 hours for every attempt after that. With 25 attempts, a job keeps trying for about four days before giving up. That’s often right for “the email provider had an outage”. For other jobs, set a lower max_attempts.

Crashed workers

If a worker dies while holding a job (server killed, out of memory), the job stays “locked”. graphile-worker treats locks older than 4 hours as abandoned and lets another worker take the job. That’s a long time. It’s one more reason jobs should be quick, and why a separate “sweep” job that re-queues stuck work can be worth having.

Adding jobs

From JavaScript

import { quickAddJob } from "graphile-worker";
 
await quickAddJob(
  { connectionString: process.env.DATABASE_URL },
  "email.send",
  { emailId },
  { maxAttempts: 8, jobKey: `email.send:${emailId}`, runAt: new Date(Date.now() + 60_000) },
);

Inside a task, use helpers.addJob(...), which reuses the worker’s connection.

From SQL

SELECT graphile_worker.add_job(
  'email.send',
  payload      => '{"emailId": "e_123"}'::json,
  job_key      => 'email.send:e_123',
  max_attempts => 8,
  run_at       => now() + interval '1 minute',
  queue_name   => 'emails',
  priority     => 0
);

The SQL form is useful because any code with a database connection can enqueue, even code that doesn’t depend on graphile-worker at all. It also works inside your own transactions.

The options that matter

• job_key: a unique name for the job. Adding a job with a key that already exists doesn’t create a second job. By default (job_key_mode = 'replace') it updates the existing job’s payload and run time instead. This gives you:
  • De-duplication: enqueue “reindex note 42” ten times, get one job.
  • Debouncing: each new add pushes run_at later, so the job runs once after things go quiet.
  • Other modes: preserve_run_at (update the payload but keep the original time) and unsafe_dedupe (keep the existing job unchanged).
• queue_name: jobs in the same named queue run one at a time, in order. Use it when order matters, such as “all jobs for this tenant”, without slowing down everything else.
• run_at: run later. This is how delayed jobs and reminders work.
• priority: lower numbers run first.
• max_attempts: how many tries before giving up.

Writing tasks

import type { Task } from "graphile-worker";
 
export const sendEmail: Task = async (payload, helpers) => {
  const { emailId } = payload as { emailId: string };
  helpers.logger.info(`sending ${emailId}`, { attempt: helpers.job.attempts });
 
  const row = await loadEmailRow(emailId);
  if (!row || row.status === "sent") return; // already done: succeed and stop
 
  await provider.send(render(row));          // throws → retried with backoff
  await markSent(emailId);
};

The most important rule: jobs run at least once

A job can run more than once. Example: the email sends, then the worker crashes before it can mark the job done. The job is retried, and without care the email goes out twice.

So every task must be idempotent:

• Check state first (“already sent? return”).
• Use idempotency keys with external APIs that support them (Stripe, many email providers).
• Make writes safe to repeat (ON CONFLICT DO NOTHING, unique constraints).

Keep payloads small

Put ids in the payload, not data. Store the real content in your own table and load it in the task. Then:

• the job always works on the current data, not a stale copy
• your own table is the record of what happened (audit trail)
• secrets and personal data aren’t spread around job rows

Throwing vs returning

• Throw for “try again later” (network errors, rate limits, provider down).
• Return normally for “done, or impossible, don’t retry” (the row was deleted, the address is invalid). Record why in your own table. Retrying a permanent failure 25 times only adds noise.

Recurring jobs (cron)

import { run, parseCronItems } from "graphile-worker";
 
await run({
  connectionString: process.env.DATABASE_URL,
  concurrency: 2,
  taskList,
  parsedCronItems: parseCronItems([
    { task: "email.sweep", match: "*/5 * * * *" },  // every 5 minutes
    { task: "billing.reconcile", match: "7 * * * *" }, // at minute 7 of every hour
  ]),
});

• graphile-worker records each cron run in its own table (known_crontabs), so with several workers running, each scheduled run still happens once.
• Backfill: if the worker was down at 3:07, it can run the missed job when it comes back, if you set a backfill period. By default, missed runs are skipped.
• Cron times are evaluated in UTC.

There’s also a crontab text format (a crontab file or string, one line per job). Its grammar is strict about task identifiers. A task name with dots, like billing.reconcile, may be rejected there, and that error happens at startup, so the whole worker fails to boot. Passing cron items as objects through parseCronItems avoids the text grammar entirely.

Running the worker

import { run } from "graphile-worker";
 
const runner = await run({
  connectionString: process.env.DATABASE_URL,
  concurrency: 5,   // jobs this process runs at the same time
  taskList,
});
await runner.promise; // resolves when the runner stops

• On first start, it creates and migrates its own schema (graphile_worker). The database user needs permission to do that, at least the first time.
• Concurrency is per process. Several processes (or servers) can run at once safely, since SKIP LOCKED keeps them from colliding.
• It handles SIGTERM/SIGINT by finishing running jobs and stopping. Give the platform enough shutdown time for your slowest job, or that job gets retried.
• It needs its own database connections: roughly concurrency plus a couple more. Count them in your connection budget.

Hosting it

It’s a long-running process, not a request handler. It doesn’t fit serverless functions that stop after a response. Options: a separate container, a separate VM process, or a side process in the same container (simpler, but it then shares CPU and restarts with your web server).

Some platforms (Azure App Service, for example) assume every container is a web server. If the container never opens a port, the platform decides it failed to start and kills it. A worker on such a platform needs a tiny HTTP health endpoint, just to keep the platform happy.

Observability

Because jobs are rows, plain SQL tells you how the queue is doing:

-- what's waiting, by task
SELECT task_identifier, count(*) FROM graphile_worker.jobs GROUP BY 1;
 
-- what's failing
SELECT task_identifier, attempts, last_error, run_at
FROM graphile_worker.jobs
WHERE attempts > 0
ORDER BY attempts DESC;

(The internal table layout changed in recent versions. Use the public graphile_worker.jobs view, not the private tables underneath.)

Alerts worth having:

• Queue depth growing over time → workers are down or too slow.
• Oldest job age → something is stuck.
• Jobs at max attempts → permanent failures someone should look at.
• Worker heartbeat → the process is alive at all. A worker that’s been crash-looping looks exactly like “no jobs for a while” unless you check.

Pattern: audit table + job + sweep

For important side effects like email, a pattern that holds up well:

1. Write the intent to your own table first (email_queue with status pending), with a unique dedupe key.
2. Add a job that carries only the row id, with job_key = 'email.send:<id>', in the same transaction.
3. The task loads the row, does the work, updates the status, and logs every attempt.
4. A sweep cron job (every few minutes) looks for rows still pending with no job, which can happen if the enqueue failed or the job was lost, and re-adds their job. Because of the job_key, re-adding is harmless.

Your table is the source of truth. The queue is just the delivery mechanism. Nothing is lost even if the queue misbehaves.

Alternatives

	Storage	Notes
graphile-worker	Postgres	transactional enqueue, low latency via LISTEN/NOTIFY, Node
pg-boss	Postgres	similar idea, more features (throttling, pub/sub-like), Node
BullMQ	Redis	very fast, rich features, needs Redis, no transactional enqueue
Sidekiq / Celery	Redis or RabbitMQ	the Ruby and Python equivalents
Temporal / Inngest	their own servers	durable multi-step workflows, much bigger systems
Cloud queues (SQS, Azure Service Bus)	managed	huge scale, but another service and no shared transaction with your DB

A real-world example

A SaaS app runs graphile-worker as a third process next to its web app and its real-time server, deployed as its own container.

• Tasks:
  • email.send delivers one queued email.
  • email.sweep runs every 5 minutes and re-schedules queue rows whose job was lost.
  • billing.reconcile runs hourly at minute 7. It re-syncs Stripe subscriptions, which repairs any missed Stripe webhooks, and prunes the webhook dedupe table. With no Stripe keys configured, it does nothing.
• Audit first. Every email is inserted into email_queue with a unique (tenant_id, dedupe_key) before anything is sent. The job is added with raw SQL (graphile_worker.add_job) from the shared database package, so other services can enqueue emails without depending on graphile-worker. The payload is only the row id, the job key is email.send:<id>, and max_attempts is 8.
• Cron items as objects. The schedule goes through parseCronItems. The crontab text grammar rejected the dotted task names, and that once stopped the runner from starting at all.
• Concurrency defaults to 2, set by an environment variable.
• Health port. The worker opens a small health listener on port 8080 before doing anything slow. The hosting platform kills containers that never open a port. Before that listener existed, the worker crash-looped for days without running a single job, and nothing alerted, because an empty queue looks the same as a dead worker.
• What it doesn’t do. Jobs that would read users’ files don’t run on the worker. The app only has delegated permissions, so a background process has no user token to read files with. That work runs inside a web request, with the user’s own token.

Gotchas

• Jobs can run twice. Make every task idempotent.
• Put ids in payloads, not data.
• Throw only for errors worth retrying.
• The default 25 attempts with exponential backoff is about four days. Lower it where that’s wrong.
• A crashed worker’s job stays locked for 4 hours.
• Dotted task names can fail in the crontab text format. Use parseCronItems.
• The worker is a long-running process. Some hosts need it to open a port.
• Count the worker’s connections in your Postgres budget.
• Monitor that the worker is alive, not just the queue length.

Practice

1. Run Postgres in Docker. Write a worker with one task that logs its payload. Add jobs from SQL with graphile_worker.add_job and watch them run almost instantly.
2. Make the task throw on the first two attempts. Watch attempts, last_error and run_at change in graphile_worker.jobs.
3. Add the same job 10 times with one job_key. Count how many times it runs.
4. Put jobs in a queue_name and show they run one at a time.
5. Add a cron item that runs every minute. Start two workers and confirm it still runs once per minute.
6. Build the audit-table + job + sweep pattern for a fake “send notification” and kill the worker mid-job.

Sources worth reading

• graphile-worker docs (worker.graphile.org), especially Tasks, Job keys, Cron and Exponential backoff
• PostgreSQL docs: SELECT … FOR UPDATE SKIP LOCKED, LISTEN/NOTIFY
• Brandur Leach: Transactionally staged job drains in Postgres
• microservices.io: Transactional outbox pattern