--- title: 'pg_cron as a Clock, Not a Worker' date: '2026-10-21' description: 'Per-customer scheduled jobs, several workers, no duplicates. In Sumalos pg_cron only inserts a row and sends a NOTIFY; the work stays in the app.' author: Pablo Fernandez tags: - Postgres - pg_cron - Scheduling - Workers - TypeScript source: 'https://www.pablofr.com/blog/pg-cron-as-a-clock' --- "Tell me every Monday at 9 which payments are due this week." Someone types that to Suma, the assistant in [Sumalos](/blog/pixel-art-ai-assistant), and from then on a job exists that belongs to one person in one organization, runs at 9:00 Madrid time for as long as they want it, and must run exactly once each Monday. Not zero times because a worker was restarting at 8:59. Not twice because two workers both noticed it was 9. Multiply that by every person who asks for a reminder, a monthly summary or "in three days, remind me to call María", and the scheduler can't be a list in a config file. The schedules are data, they change at runtime, and they have owners. [pg_cron](https://github.com/citusdata/pg_cron) looks made for this: cron inside Postgres, with jobs you create and delete with SQL. The tempting design is to have it run the job's SQL directly. I went the other way. In Sumalos pg_cron is only a clock. At each firing it writes down that the time has come, and everything else, the work, the retries, the bookkeeping and even the time zone, stays in TypeScript. This post is about why the split sits there. ## The scheduler I already had The worker in Sumalos already ran periodic jobs before any of this: purge orphaned files every hour, index new messages every five seconds, prune old rows once a day. Those are system jobs, a fixed list in code, and the scheduler for them is twenty lines: ```typescript:apps/worker/src/scheduler.ts const tick = async () => { const taken = await redis.send("SET", [key("lock", job.name), String(process.pid), "NX", "EX", String(job.everySeconds - 1)]); if (taken !== "OK") return; const started = performance.now(); try { const result = await job.run(); ``` Every worker runs a `setInterval` per job, and the first one to take a Redis lock that expires just before the next period does the work. Several workers, one run per period. It works well for "every hour", and it's still what runs those system jobs. It doesn't stretch to "every Monday at 9 for this person". The interval is fixed in code, there's no calendar, and the jobs aren't data. I could have kept a `cron_jobs` table and had every worker scan it each minute, evaluate every expression and race for a lock per job and minute. That is a scheduler written from scratch, and it's exactly the part pg_cron already does well. ## What pg_cron does at a firing A scheduled job in Sumalos is two things with the same id: a row in `cron_jobs` (kind, owner, payload, schedule, enabled) and a pg_cron job. The pg_cron job's command never changes shape. It's built by one function: ```typescript:packages/server/src/cron/schedule.ts /** The SQL pg_cron runs at each firing: one pending run, and a wake-up for the worker. */ export function firingCommand(id: string): string { if (!UUID.test(id)) throw new Error(`not a job id: ${id}`); return `insert into cron_runs (job_id) values ('${id}'); notify ${RUNS_CHANNEL};`; } ``` That's all pg_cron ever executes for the app: insert a pending row in `cron_runs` and send a [`NOTIFY`](https://www.postgresql.org/docs/current/sql-notify.html) on the `cron_runs` channel. The id is checked against a uuid pattern before it goes into the SQL string, and the test for it tries the obvious: ```typescript:packages/server/src/cron/schedule.test.ts test("only takes a uuid, so nothing can be injected", () => { expect(() => firingCommand("x'); drop table users; --")).toThrow(); }); ``` ``` pg_cron (database postgres) │ at 0 7 * * 1, in database sumalos: │ insert into cron_runs (job_id) values (...); │ notify cron_runs; ▼ ┌──────────────────────────────────────┐ │ cron_runs │ │ job_id status scheduled_at │ │ 01a1... pending 2026-10-19 07:00 │ └──────────────────┬───────────────────┘ │ LISTEN cron_runs, and a poll every 30 s ▼ worker A worker B worker C ``` *pg_cron only marks the time; the row it inserts is the job's work order, and the NOTIFY wakes whichever workers are listening.* There's a reason the firing is a row and not the work itself. If pg_cron ran the job directly, the job would have to be SQL, its failures would be lines in `cron.job_run_details`, and retrying it would mean writing retry logic in PL/pgSQL. Sending an email, calling a model or talking to WhatsApp can't happen from inside Postgres anyway. As a row, a firing is a work order: it has a status, a number of attempts, a result and an error, and the app treats it like any other queue item. pg_cron lives in one database per cluster, `postgres` by default, and Sumalos's data lives in another. So `createCron` keeps a second, single connection to the `postgres` database just for scheduling, and asks pg_cron to run the command in the app's database with [`cron.schedule_in_database`](https://github.com/citusdata/pg_cron#creating-a-cron-job-in-a-different-database): ```typescript:packages/server/src/cron/index.ts /** Creates or replaces (same name) the pg_cron job of a row. */ async function schedule(job: Pick) { await cronSql()`select cron.schedule_in_database(${jobName(database, job.id)}, ${job.schedule}, ${firingCommand(job.id)}, ${database}, null, ${job.enabled})`; } ``` The app role isn't a superuser, and pg_cron revokes that function from `PUBLIC`, so the database's first-boot script grants exactly that one function to the app's role, plus `USAGE` on the `cron` schema. The firing runs as the role that scheduled it, so it can insert into `cron_runs` and nothing the app itself couldn't. The pg_cron job's name carries the database: `sumalos:cron:` in production, `sumalos_dev:cron:` in development. Both databases share one Postgres cluster, and so one `cron.job` table. With the database in the name, development can list and unschedule its own jobs by prefix and never touch production's. ## Several workers, one run Every worker in Sumalos runs the cron runner, and every one of them wakes on the same `NOTIFY`. Three workers, one pending row: the claim has to make sure one of them gets it. That's what [`FOR UPDATE SKIP LOCKED`](https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE) is for: ```typescript:packages/server/src/cron/runner.ts {8} export async function claimRuns(db: Db, limit = BATCH) { return db.transaction(async (tx) => { const picked = await tx .select({ id: cronRuns.id }) .from(cronRuns) .where(eq(cronRuns.status, CronRunStatus.Pending)) .orderBy(cronRuns.scheduledAt) .limit(limit) .for("update", { skipLocked: true }); if (!picked.length) return []; const runs = await tx .update(cronRuns) .set({ status: CronRunStatus.Running, startedAt: new Date(), attempts: sql`${cronRuns.attempts} + 1` }) .where(inArray(cronRuns.id, picked.map((p) => p.id))) ``` The first worker locks the row; the other two skip it instead of waiting, find nothing else, and go back to sleep. Inside the same transaction the row becomes `running` with one more attempt, so once it commits nobody will pick it again. A worker takes up to ten runs at a time and loops until none are left. ``` NOTIFY cron_runs │ ├──▶ worker A select ... for update SKIP LOCKED ──▶ run 01a1 (locks it) ├──▶ worker B select ... for update SKIP LOCKED ──▶ nothing └──▶ worker C select ... for update SKIP LOCKED ──▶ nothing ``` *Three workers wake on one NOTIFY; SKIP LOCKED gives the pending row to the first and makes the other two pass over it.* The runner also accounts for the two ways this can go wrong. A `NOTIFY` is delivered only to sessions listening at that moment; a worker that was restarting misses it. So each worker also polls every 30 seconds, and a missed wake-up costs at most half a minute, never a run. And a worker can die in the middle of a run, leaving it `running` forever. Before each drain, the runner puts back any run that has been `running` for more than 15 minutes, and after three attempts marks it failed with a message that says so. The handler is looked up by the job's kind. Whatever it returns is stored as the run's result, and a throw becomes the run's error. A job of a kind with no handler fails its runs with "No hay handler" instead of disappearing, and finished runs are kept for 30 days. ## "Monday at 9" is not a cron expression This is where keeping the work in the app paid off a second time, in a place I didn't expect. pg_cron reads every schedule in one time zone for the whole cluster, GMT unless `cron.timezone` is set in `postgresql.conf`. A person in Madrid who says "Mondays at 9" means 9:00 on the wall clock, which is 08:00 UTC in winter and 07:00 UTC in summer. Write `0 8 * * 1` and the reminder arrives at 10:00 from the end of March. Write `0 7 * * 1` and it arrives at 8:00 from the end of October. Setting `cron.timezone` to Madrid would fix one country and break the rest, for every database in the cluster. So the schedule a person asked for never goes to pg_cron. Suma's tasks store it as a local cron (five fields in the task's own time zone, `Europe/Madrid` by default) or a single instant, and pg_cron only ever holds the next firing, as a one-shot UTC expression: ```typescript:packages/server/src/ai/tasks/schedule.ts /** pg_cron's one-shot schedule for an instant: its UTC minute, hour, day and month. */ export function pgCronFor(at: Date): string { return `${at.getUTCMinutes()} ${at.getUTCHours()} ${at.getUTCDate()} ${at.getUTCMonth() + 1} *`; } ``` After each firing, the task's handler moves its pg_cron job to the occurrence after this one, computed in local time with nothing but `Intl`: ```typescript:packages/server/src/ai/tasks/store.ts /** Puts the task's pg_cron job on its next occurrence (or pauses it when there is none). */ async function placeFiring(task: AiTaskRow, after: Date): Promise { const next = task.status === "active" ? nextOccurrence(scheduleOf(task), after) : null; const status = task.status === "active" && !next ? "done" : task.status; if (task.cronJobId) await cron.update(task.cronJobId, { schedule: next ? pgCronFor(next) : pgCronFor(task.nextRunAt ?? after), enabled: !!next }); ``` ``` ai_tasks: cron "0 9 * * 1", timezone Europe/Madrid │ │ nextOccurrence (local, DST-aware) ▼ next: 2026-03-23 08:00 UTC ──▶ pg_cron one-shot "0 8 23 3 *" │ fires ▼ onCronRun: run it, then place the next ┌──────────────────────────────┘ ▼ next: 2026-03-30 07:00 UTC ──▶ pg_cron one-shot "0 7 30 3 *" ``` *The local schedule stays in the app; pg_cron holds one UTC firing at a time, and each firing places the next.* The test pins both changes of the clock in 2026. A Monday-at-9 task fires at 08:00 UTC on 23 March and 07:00 UTC on 30 March, and at 07:00 UTC on 19 October and 08:00 UTC on 26 October: ```typescript:packages/server/src/ai/tasks/schedule.test.ts test("Mondays at 9 keep 9:00 local across both DST changes", () => { const spring = nextOccurrences(cron("0 9 * * 1"), new Date("2026-03-20T00:00:00Z"), 2).map((d) => d.toISOString()); expect(spring).toEqual(["2026-03-23T08:00:00.000Z", "2026-03-30T07:00:00.000Z"]); const autumn = nextOccurrences(cron("0 9 * * 1"), new Date("2026-10-19T06:00:00Z"), 2).map((d) => d.toISOString()); expect(autumn).toEqual(["2026-10-19T07:00:00.000Z", "2026-10-26T08:00:00.000Z"]); }); ``` The two awkward hours have a rule each. A time that doesn't exist (2:30 on the night the clocks go forward) fires at the first valid minute after it, 3:00. A time that happens twice (2:30 on the night they go back) fires the first time. Both are in the same test file. A one-shot expression with no year has a catch: `0 8 23 3 *` matches 23 March of every year. It never gets there, because the firing that matches it is the one that moves it. When the move fails, the next section is what catches it. ## Fired twice, run once Moving the clock into the app means two systems have to agree, and sometimes they won't. pg_cron can miss a firing while Postgres restarts. A database restore can bring back `cron_jobs` rows whose pg_cron jobs are gone, or the other way round. A worker can claim a run, do the work and crash before marking it done, and the run gets tried again. The design answer is that firing twice must be harmless. Each task run is keyed by the task and the minute it was meant for, and that key is unique in two places. The run row has a unique index on `(task_id, scheduled_for)`, and the job it queues for core-api carries the idempotency key `ai_task_run::`, also unique: ```typescript:packages/server/src/ai/tasks/store.ts const [inserted] = await db .insert(aiTaskRuns) .values({ taskId: task.id, organizationId: task.organizationId, userId: task.userId, scheduledFor, trigger, cronRunId, status: "queued" }) .onConflictDoNothing({ target: [aiTaskRuns.taskId, aiTaskRuns.scheduledFor] }) .returning(); ``` A second firing for the same Monday finds the row already there and queues nothing. With that in place, the reconciliation jobs can be blunt, because a mistake on their side costs nothing: ``` heartbeat every 2 min a task whose next run is 3+ min late: fire it now (once), place the next repair every hour an active task whose cron_jobs row is gone: schedule it again sync by hand every cron_jobs row scheduled in pg_cron; pg_cron jobs of this database with no row: dropped ``` *Three loops repair what pg_cron and the app's tables can disagree on; idempotent runs make an extra firing harmless.* The heartbeat is the one that matters day to day. If pg_cron missed the 9:00 firing, the task's `next_run_at` is in the past, and two minutes later at most the heartbeat fires it with the same key the firing would have used. If the firing did happen and the heartbeat fires too, the unique index turns the second one into nothing. The third, `sync`, has no timer: it's a function on `createCron` for after a restore, and nothing calls it on its own yet. ## What pg_cron is trusted with What the app asks of pg_cron is small and testable. It has to fire about on time, in UTC, and run one fixed `INSERT` and `NOTIFY` as the app's own role, in the app's own database. Everything that could go wrong with a scheduled job, a failed email, a slow model, a person who lost access between Monday and Monday, a time zone, happens in code where it has a status, a log line and a test. The price is that there are now two places that hold a schedule, and they can disagree: a `cron_jobs` row and a pg_cron job, a task's `next_run_at` and the one-shot firing behind it. That is why the system has a heartbeat, a repair loop and a sync, and why every run carries a key that makes doing it twice safe. I'd pay it again. The alternative was to put the work inside the clock, and a clock is the one part of a scheduler I didn't want to debug.