Common Uses for pg_relay

These are real situations where pg_relay is a good fit, each with the SQL you would use.

Sending HTTP webhooks from the database

Together with PostgreSQL's pg_net extension, pg_relay can send HTTP requests directly from the database in response to any database event.

SELECT pgrelay.register(
    'order_placed',
    $$ SELECT net.http_post(
           url     := 'https://fulfilment.example.com/webhooks/order',
           body    := $1::jsonb,
           headers := '{"Content-Type": "application/json"}'::jsonb
       ) $$,
    'Notifies the fulfilment system when a new order is placed');
GRANT EXECUTE ON FUNCTION net.http_post TO pgrelay;

-- Trigger that fires for every new order:
CREATE FUNCTION orders.on_order_placed()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    PERFORM pgrelay.notify('order_placed', row_to_json(NEW)::text);
    RETURN NEW;
END;
$$;
CREATE TRIGGER orders_placed AFTER INSERT ON orders.orders
FOR EACH ROW EXECUTE FUNCTION orders.on_order_placed();

The webhook fires for every new order, no matter where it came from — application code, a data import, or an admin tool. Every attempt is logged.

Invalidating a cache

SELECT pgrelay.register('product_changed',
    'SELECT cache.invalidate_product($1)',
    'Clears the product cache when a product row changes');

Add a trigger on your products table that calls pgrelay.notify('product_changed', product_id::text). For high-frequency updates, add p_deduplicate := true so only one cache clear runs per product, even if many changes arrive at once.

Event-driven ETL

ETL stands for Extract, Transform, Load — the process of pulling data from one place, reshaping it, and loading it somewhere else (commonly a data warehouse).

SELECT pgrelay.register('staging_arrived',
    'SELECT etl.transform_and_load($1)',
    'Processes a staging batch into the warehouse');

-- After loading a batch into staging:
SELECT pgrelay.notify('staging_arrived', v_batch_id::text);

The transformation starts within about one second of the data arriving — there is no need to wait for the next scheduled run.

Multi-step workflows

Each step fires the next one when it finishes:

SELECT pgrelay.register('payment_captured',   'SELECT warehouse.reserve_inventory($1)');
SELECT pgrelay.register('inventory_reserved', 'SELECT despatch.schedule($1)');

-- Inside reserve_inventory, after it succeeds:
PERFORM pgrelay.notify('inventory_reserved', p_order_id::text);

Each step is logged independently. If one fails, the audit log shows exactly which step failed and why.

Keeping materialised views current

A materialised view is a table-like object that stores the result of a query, rather than recalculating it every time. Refreshing it updates that stored result.

SELECT pgrelay.register('sale_recorded',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.sales_summary',
    'Refreshes the sales summary after each sale');

For high-frequency refreshes that need throttling, Pebble IT's companion extension pg_auto_mv builds on pg_relay — see gitlab.com/pebble-it/pg_auto_mv.

Compliance and audit trails

SELECT pgrelay.register('patient_record_accessed',
    'SELECT compliance.log_access($1::jsonb)',
    'Writes an immutable access record to the compliance system');

A trigger fires on every access, no matter which application caused it. The audit record is written by pg_relay and cannot be skipped by application code.

---

Ready to install it? Continue to Installing the Extension.