How to receive postgres trigger notification in python using pg_notify quickly or real time with bulk records

Question

I have trigger of after update / after insert and have python listener using pg_notify. Trigger:

DECLARE
    channel text :='test_channel';
BEGIN
    RAISE NOTICE 'channel %  % ',id;
    PERFORM pg_notify(channel,json_build_object('id',new.id,'col1',col1,'col2',col2);

    RETURN NEW;
END;

Python code to listen :

while True:
    conn_psycopg.poll()
    while conn_psycopg.notifies:
        notify = conn_psycopg.notifies.pop(0)
        json_payload = json.loads(notify.payload)
        id = json_payload.get('id')
        prepare_payload_and_make_api_call(json_payload, id)

It is working fine with less no of records but there is bulk update (10k) happens taking 1 hour to process all records. Currently if bulk update happens in data receives one by one, so one by one processing is taking time.

{id:1, name: abc} {id:2, name: xyz} {id:3, name: pqr}

Because of this behavior we are also processing these records one by one, but we receive bulk records in one go like this:

[{id:1, name: abc}, {id:2, name: xyz}, {id:3, name: pqr}]

then processing will improve.

Please suggest if there are other approaches also which will help to improve speed.

Answer 1

You could use a statement trigger rather than row trigger, and then use the 'transition table' feature to compose a larger payload. But pg_notify has a rather small limit on the size of a payload, so there is no way you can condense 10,000 updated rows into a single pg_notify call.

Better to accumulate the messages on the python side, and then dispatch them to the api call once enough have accumulated (or enough time has elapsed that you don't want to wait longer for more). Or maybe just fix the api call so it isn't so inefficient to do one at a time.