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