I have listen/notify on most changes in my database. Not sure I've experienced any performance issue though but I can't say I've been putting things through their paces. IMHO listen/notify's simplicity outweighed the perf gains by WAL.
I'm only sharing this should it be helpful:
def up do
whitelist = Enum.join(@user_columns ++ ["tick"], "', '")
execute """
CREATE OR REPLACE FUNCTION notify_phrasing() RETURNS trigger AS $$
DECLARE
notif jsonb;
col_name text;
col_value text;
uuids jsonb := '{}'::jsonb;
user_columns text[] := ARRAY['#{whitelist}'];
BEGIN
-- First, add all UUID columns
FOR col_name IN
SELECT column_name
FROM information_schema.columns
WHERE table_name = TG_TABLE_NAME AND data_type = 'uuid'
LOOP
EXECUTE format('SELECT ($1).%I::text', col_name)
INTO col_value
USING CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
IF col_value IS NOT NULL THEN
uuids := uuids || jsonb_build_object(col_name, col_value);
END IF;
END LOOP;
-- Then, add user columns if they exist in the table
FOREACH col_name IN ARRAY user_columns
LOOP
IF EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_name = TG_TABLE_NAME AND column_name = col_name
) THEN
EXECUTE format('SELECT ($1).%I::text', col_name)
INTO col_value
USING CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
IF col_value IS NOT NULL THEN
uuids := uuids || jsonb_build_object(col_name, col_value);
END IF;
END IF;
END LOOP;
notif = jsonb_build_object(
'table', TG_TABLE_NAME,
'event', TG_OP,
'uuids', uuids
);
PERFORM pg_notify('phrasing', notif::text);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
"""
# Create trigger for each table
Enum.each(@tables, fn table ->
execute """
CREATE TRIGGER notify_phrasing__#{table}
AFTER INSERT OR UPDATE OR DELETE ON #{table}
FOR EACH ROW EXECUTE FUNCTION notify_phrasing();
"""
end)
end
I do react to most (70%) of my database changes in some way shape or form, and post them to a PubSub topic with the uuids. All of my dispatching can be done off of uuids.