Why should you care?

PostgresVerse ops wall shows live order count without hammering SELECT COUNT(*) on orders every second.

See it live — copy this example

Run in pgAdmin or psql.

CREATE OR REPLACE FUNCTION notify_order_insert()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_notify('orders_channel', json_build_object(
    'order_id', NEW.order_id,
    'total', NEW.total_amount
  )::text);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_order_notify
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_insert();

What happened?

• After each order insert trigger fires pg_notify on orders_channel with JSON payload.
• Dashboard listener connection RECEIVE NOTIFY and updates UI count.

Practice next

1. Create trigger on orders.
2. In psql session one: LISTEN orders_channel;
3. Session two: INSERT order; watch NOTIFY in session one.
4. Maintain orders_today counter updated in same trigger.
5. REFRESH MATERIALIZED VIEW CONCURRENTLY every 30s for charts.

Remember

LISTEN/NOTIFY is lightweight pub/sub inside postgres. Triggers push events on row change. Combine with periodic MV for heavy aggregates.

PostgresVerse kitchen display

Restaurant chain dashboard updates new order count instantly via NOTIFY.

Outcome: Managers see live throughput without Redis duplicate state.