Scheduled Purge — Keep Only the Last 3 Log Entries

This chapter demonstrates pg_relay's deferred event feature: scheduling work to run at a future time, entirely from within the database — no external scheduler required.

Create the purge function

The function calls pgrelay.purge() to trim the log. It is SECURITY DEFINER, so it runs as app_user, who was granted EXECUTE on pgrelay.purge() back in One-Time Superuser Setup. The pgrelay role itself does not need that grant.

CREATE FUNCTION purge_logs(p_payload text)
RETURNS void LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    PERFORM pgrelay.purge(p_keep_quantity := 3);
END;
$$;

GRANT EXECUTE ON FUNCTION purge_logs(text) TO pgrelay;

Register the channel

SELECT pgrelay.register(
    'purge_log',
    'SELECT public.purge_logs($1)',
    'Purges pgrelay.log, keeping the 3 most recent entries'
);

Schedule the purge to run in 2 minutes

SELECT pgrelay.notify('purge_log', '', p_run_at := now() + interval '2 minutes');

Check the queue — confirm the event is deferred

SELECT id, channel, run_at, dispatched_at
FROM pgrelay.queue
WHERE dispatched_at IS NULL;

You will see one pending row with run_at roughly 2 minutes in the future and dispatched_at NULL. The Processor's probe ignores rows where run_at > now(), so nothing happens yet.

 id  |  channel  |            run_at             | dispatched_at
-----+-----------+-------------------------------+---------------
 ... | purge_log | 2026-04-22 12:05:00.000+00    |

Count current log entries

SELECT count(*) FROM pgrelay.log;

You should see around 39 entries from the earlier chapters' work. Make a note of this — it will drop to 4 once the purge runs. The reason it lands on 4, not 3, is the 3 you asked to keep plus 1 new log entry for the purge action itself.

Wait and re-query until the purge fires

After 2 minutes, the Processor picks up the purge_log event and runs purge_logs(''). Re-run this until the count drops:

SELECT count(*) FROM pgrelay.log;

When it reaches 4, the purge has run. Query the remaining entries:

SELECT id, channel, status, actioned_at
FROM pgrelay.log
ORDER BY actioned_at DESC;

 id  |  channel  | status |         actioned_at
-----+-----------+--------+----------------------------
 ... | purge_log | ok     | 2026-04-22 12:05:01.123+00
 ... | ...       | ...    | ...
 ... | ...       | ...    | ...
 ... | ...       | ...    | ...

Four rows remain. The purge kept the 3 most recent entries that existed when it ran, then pg_relay wrote a 4th row — the ok entry for the purge_log event itself (written after the action completes). Everything older has been removed.

---

Continue to How the Events Flow for a diagram summarising everything you have just seen.