Purging Historical Data

Why purging matters

pgrelay.log grows without limit if you never purge it. In a system processing 500 events per second, that generates 43 million rows a day. Schedule a regular purge to keep it manageable. pgrelay.queue also accumulates dispatched rows over time — purging it keeps the queue's index small and its probe fast.

Purge strategies

By age:

-- Delete log rows older than 7 days:
SELECT pgrelay.purge(p_hours := 168);

-- Delete dispatched queue rows older than 7 days (default):
SELECT pgrelay.purge_queue();

-- Shorter retention for the queue:
SELECT pgrelay.purge_queue(p_hours := 24);

By row count:

-- Keep only the most recent 100,000 log rows:
SELECT pgrelay.purge(p_keep_quantity := 100000);

Self-scheduling maintenance with pg_relay

You can use pg_relay itself to schedule its own purge — no external scheduler needed. Register a daily deferred event using a function that queues the next run when it finishes:

-- Function that purges and queues the next run:
CREATE FUNCTION maintenance.daily_purge(text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    PERFORM pgrelay.purge(p_hours := 168);
    PERFORM pgrelay.purge_queue(p_hours := 168);
    PERFORM pgrelay.notify('daily_maintenance', '',
        p_run_at := date_trunc('day', now() + interval '1 day') + interval '03:00:00');
END;
$$;
GRANT EXECUTE ON FUNCTION maintenance.daily_purge(text) TO pgrelay;

-- Register the channel:
SELECT pgrelay.register('daily_maintenance', 'SELECT maintenance.daily_purge($1)',
    'Daily log and queue purge, self-scheduling');

-- Fire the first one (runs tonight at 3 AM, then every night at 3 AM):
SELECT pgrelay.notify('daily_maintenance', '',
    p_run_at := date_trunc('day', now() + interval '1 day') + interval '03:00:00');

Every run logs its own completion in pgrelay.log and schedules the next one. If the Processor happens to be down during a maintenance window, the deferred event simply stays in the queue and fires the next time the Processor comes back up.

---

Continue to Performance Testing to measure your own configuration's throughput in detail.