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.