Comment by JoelJacobson

Here is the Commitfest entry if you want to help with reviewing/development/testing of the patch: https://commitfest.postgresql.org/patch/6078/

We use it like this:

    CREATE TRIGGER notify_events_trg AFTER INSERT ON xxx.events FOR EACH ROW EXECUTE PROCEDURE public.notify_events();

    CREATE FUNCTION public.notify_events() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
      PERFORM pg_notify('events', row_to_json(NEW)::text);
      RETURN NEW;
    END;
    $$;

And then we have a bunch of triggers like this on many tables:

    CREATE TRIGGER create_category_event_trg AFTER INSERT OR DELETE OR UPDATE ON public.categories FOR EACH ROW EXECUTE PROCEDURE public.create_category_event();

    CREATE FUNCTION public.create_category_event() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
    DECLARE
      category RECORD;
      payload JSONB;
    BEGIN
      category := COALESCE(NEW, OLD);
      payload := jsonb_build_object('id', category.id);
      IF NEW IS NULL OR NEW.deleted_at IS NOT NULL THEN
        payload := jsonb_set(payload, '{deleted}', 'true');
      END IF;
      INSERT INTO xxx.events (channel, inserted_at, payload)
        VALUES ('category', NOW() AT TIME ZONE 'utc', payload);
      RETURN NULL;
    END;
    $$;

We found no notable performance issues. We have a single LISTEN in another application. We did some stress testing and found that it performs way better than we would ever need

We tried to use LISTEN/NOTIFY for notification purposes, or rather, a queue in which the order mattered (well, kind of, we were aiming for the best scenario where a customer would not perform a specific task in a matter of milliseconds, because we could afford this expectation), in a .NET application using Npgsql.

The listeners were scattered between replicas, so we took advantage of Advisory Locks (https://news.ycombinator.com/item?id=44490510, and my first point felt validated;

but to this day I'm still unsure. Anyway, since it was a complementary system, it didn't hurt to leave it out, we had another background job that would process the outbox table regardless, but I felt it could/would give something closer to "real time" in our system.

The post seems to say that NOTIFY is generally not a good idea, then comments here say that NOTIFY can actually work, but it depends on some particular things (which are not easy to know for newcomers to Postgres), makes it a bit complicated to know what is the way to go for a new database

In my case I have an IoT setting, where my devices can change their "DesiredState", and I want to listen on this to push some message to MQTT... but then there might be also other cases where I want to listen to some messages elsewhere (eg do something when there is an alert on a device, or listen to some unrelated object, eg users, etc)

I'm not clear right now what would be the best setting to do this, the tradeoffs, etc

Imagine I have eg 100k to 10M range of devices, that sometimes these are updated in bulks and change their DesiredState 10k at a time, would NOTIFY work in that case? Should I use the WAL/Debezium/etc?

Can you try to "dumb down" in which cases we can use NOTIFY/LISTEN and in which case it's best not to? you're saying something about single-channel/multi-channel/etc but to a newcomer I'm not clear on what all these are

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.