The Watermark Pattern

This is the pattern to actually use pgrelay.schedule for something — a table that remembers when each job last ran and when it's next due, so a long outage results in one catch-up run instead of either a lost run or a backlog stampede.

This table belongs to your application, in your own schema (shown here as myapp) — pgrelay.schedule is a shared type, not a table pg_relay ships:

CREATE TABLE myapp.job (
    job_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text NOT NULL UNIQUE,
    s           pgrelay.schedule NOT NULL,
    enabled     boolean NOT NULL DEFAULT true,
    last_run_at timestamptz,
    next_run_at timestamptz NOT NULL
);
CREATE INDEX job_due ON myapp.job (next_run_at) WHERE enabled;

INSERT INTO myapp.job (name, s, next_run_at) VALUES
  ('refresh_sales_mv',   pgrelay.weekdays('4:30,1700'),      now()),
  ('rebuild_dim_cust',   pgrelay.fortnightly('sun', '0130'), now()),
  ('month_end_rollup',   pgrelay.month_end('2330'),          now()),
  ('board_pack_extract', pgrelay.nth_dow('mon', '0600', 1),  now());
UPDATE myapp.job SET next_run_at = pgrelay.next_run(s, now());

Somewhere on a timer (a small worker loop, a pg_cron job, whatever already polls in your application), claim due work — FOR UPDATE SKIP LOCKED lets several workers safely share the same table:

SELECT * FROM myapp.job
 WHERE enabled AND next_run_at <= now()
 ORDER BY next_run_at
   FOR UPDATE SKIP LOCKED;

Then advance the watermark once the work is handed off:

UPDATE myapp.job
   SET last_run_at = now(),
       next_run_at = pgrelay.next_run(s, now())
 WHERE job_id = $1;

Because next_run() is computed from now(), not from the previous next_run_at, a job that was due three times during an outage catches up with exactly one run when the outage ends — not three queued runs firing back to back.

Connecting this to pg_relay's actual queue

Everything above only decides when. If you want pg_relay's own Processor to be the thing that actually does the work — rather than your worker loop doing it directly — have the watermark advance also call pgrelay.notify():

UPDATE myapp.job
   SET last_run_at = now(),
       next_run_at = pgrelay.next_run(s, now())
 WHERE job_id = $1
RETURNING pgrelay.notify('myapp_job_due', job_id::text);

(or call pgrelay.notify() from a trigger on myapp.job, if you'd rather keep the notify logic out of every call site that touches the table). Either way, the due job becomes an ordinary queued event, and everything the User Guide and Technical Guide already tell you about pg_relay — retries, audit logging, concurrency control — applies to it exactly as it would to any other event.

A performance note for bulk rescheduling

next_run() re-parses a schedule's dow/times text and walks up to a 400-day window on every call — advancing one job's watermark right after it runs is unremarkable (a couple of milliseconds), but recomputing next_run() for a large fleet of schedules in one bulk statement measurably is not instant. Testing found roughly 2.5 ms per row for 10,000 rows in a single UPDATE. If you're doing something like a full fleet reschedule, budget for that rather than expecting it inline in a request path.

Testing

The schedule/ package ships its own test suite — schedule/run_tests.sh installs into a scratch database, runs the full SQL regression suite plus a multi-million-evaluation differential test against an independent Python reference implementation, and exits non-zero on any failure. See schedule/README.md in the repository for how to run it, and schedule/VERIFICATION.md for the full test report (every PostgreSQL version and session timezone it was verified against, and why).

---

This is the last chapter in this book. Head back to the User Guide or the Technical Guide for everything else pg_relay does.