pg_notify contention

[Dimitris Zenios <dimitris.zenios@xxxxxxxxx>]

Hi, 

I am measuring a very simple case of pg_notify in an after update trigger. The trigger is the following:

CREATE
    OR REPLACE FUNCTION audit_event() RETURNS TRIGGER AS
$$
BEGIN
    PERFORM pg_notify('user', 'hello world');
    RETURN NULL;
END;
$$
    LANGUAGE plpgsql;

and configured on the table with the following sql

CREATE CONSTRAINT TRIGGER trigger_test AFTER UPDATE ON pgbench_accounts FOR EACH ROW EXECUTE PROCEDURE audit_event()

I am running two benchmarks. One with the pg_notify on the trigger enabled and one with the notify commented out.

The command is the following:

pgbench -f /tmp/update_bench.sql  -c 10 -j 10 -t 100 benchdb

And the content of the update_bench.sql are the following

\set aid random(1, 100000 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
END;

Results are

- With pg_notify disabled

pgbench (16.1)
starting vacuum...end.
transaction type: /tmp/update_bench.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
number of failed transactions: 0 (0.000%)
latency average = 11.744 ms
initial connection time = 15.616 ms
tps = 851.531991 (without initial connection time)

- With pg_notify enabled

pgbench (16.1)
starting vacuum...end.
transaction type: /tmp/update_bench.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
number of failed transactions: 0 (0.000%)
latency average = 56.927 ms
initial connection time = 11.182 ms
tps = 175.664989 (without initial connection time)

There is a huge drop in TPS from 851 to 175.

I also noticed that if I run the test with a single connection -c 1 then the results are nearly identical which makes me assume that this is a contention that occurs between multiple connections.

Thanks