Architecture Overview

How an event flows

Producer (a trigger, a function, or your application code):
    SELECT pgrelay.notify('channel_name', payload)
        │
        │  Validated (channel exists and is active), then written to
        │  pgrelay.queue — inside the producer's own transaction.
        │  If the producer rolls back, the event is discarded.
        ▼
pgrelay.queue — a durable row, committed with the producer's data.
        ⋮
        ⋮  (up to ~1 second passes)
        ⋮
Processor tick (every 1 second):
    1. SELECT pgrelay.queue_has_work()       ← cheap probe: is there anything to do?
    2. SELECT id, action_type FROM pgrelay.queue_pending_ids()   ← list of eligible (id, action_type) pairs
    3. For each id in parallel (one per worker connection):
           SELECT * FROM pgrelay.process_one(id)
               ← claims the row, runs the action, writes the audit row, marks done
        ▼
pgrelay.log — one audit row per processed event.

There is no LISTEN/NOTIFY (PostgreSQL's own built-in mechanism for one session to wake another instantly), no signalling, and no wake-up mechanism of any kind. The Processor finds work by polling — checking the queue once per second and processing everything it finds. This keeps it simple to reason about, simple to operate, and robust across every high-availability configuration.

Note

When the Processor finishes a batch of work, it immediately polls the queue again — if there is more to do, it starts straight away rather than waiting for the next 1-second tick. In a busy environment, the Processor can end up working continuously for some time.

The durable queue

When pgrelay.notify() is called, it inserts a row into pgrelay.queue inside the caller's current transaction. This is the key property: the event row rises and falls with everything else the caller does. If the caller's transaction rolls back, the event never existed. If the caller commits, the event is guaranteed to be there for the Processor to find.

The poll loop (drain loop)

Every second, the Processor runs one poll:

1. Cheap probe — pgrelay.queue_has_work() returns true or false. This is a single existence check using a partial index (a smaller, more targeted index that only covers the rows that matter for this check). When the queue is empty, it takes about 40 microseconds and adds essentially no load to the database.
2. If there is work — pgrelay.queue_pending_ids() returns (id, action_type) pairs for every eligible row still pending. Rows are ordered by action_types.run_order (NULLs last), then run_at, then queued_at — so lower-priority action types are processed after higher-priority ones within the same batch. It uses FOR UPDATE SKIP LOCKED (a PostgreSQL locking technique explained in the Glossary) — if more than one Processor is running, each gets a disjoint set of rows. The row locks from this call are released the instant the statement finishes; the Processor only holds a plain list of (id, action_type) values after this step.
3. Dispatch — each ID is processed by calling pgrelay.process_one(id) on a worker connection.
4. Drain — if any worker actually processed an event, the Processor does not wait for the next tick. It immediately repeats step 1. It keeps doing this until the probe comes back false, or every event is skipped. Only then does it wait for the next 1-second tick.

This drain behaviour means a burst of 1,000 events is processed as fast as the database can handle them — not at a fixed rate of one batch per second.

Why there is no LISTEN/NOTIFY

While Pebble IT was developing pg_relay, earlier designs used PostgreSQL's built-in pg_notify() to wake the Processor immediately when an event arrived, instead of waiting for the next poll. This was removed because:

• It added infrastructure that could fail silently — a notification lost during a reconnect meant a delayed event with no sign anything was wrong.
• The 1-second polling overhead is negligible (a 40-microsecond probe once a second).
• The drain loop already makes burst processing fast — the delay is only one poll interval for the first event in a burst; everything after that is limited only by processing speed.
• Polling works the same way under every high-availability configuration, including load balancers that are aware of which node is the standby.

---

Continue to The Processor for the command-line flags, connection settings, and startup behaviour.