Re: [PATCH] Add pg_current_vxact_id() function to expose virtual transaction IDs

Hello.

> I think the only thing I find slightly less than compelling about this
> reasoning is that it's all theoretical. If you said, look, we tried
> the pg_locks method in our environment and the performance was
> demonstrably bad, and then we wrote an extension that does the same
> thing as this function and then the performance was good, that would
> be a much stronger argument in my mind.

I made the simplest test possible.

/tmp/bench_pglocks.sql:
-- Get own VXID via pg_locks (the pre-patch approach)
SELECT virtualtransaction
FROM pg_locks
WHERE pid = pg_backend_pid()
AND locktype = 'virtualxid'
AND virtualtransaction IS NOT NULL
LIMIT 1;

/tmp/bench_vxact.sql
-- Get own VXID via pg_current_vxact_id() (the new function)
SELECT pg_current_vxact_id();

Then I ran pgbench with different numbers of connections:

for c in 1 8 16 32; do
echo -n "pg_locks c=$c: "
pgbench -p 5499 postgres -f /tmp/bench_pglocks.sql -T 10 -c $c -j $c 2>&1
| grep "tps ="
echo -n "pg_curr_vxid c=$c: "
pgbench -p 5499 postgres -f /tmp/bench_vxact.sql -T 10 -c $c -j $c 2>&1
| grep "tps ="
echo ""
done

The output:
pg_locks c=1: tps = 5885.343340 (without initial connection time)
pg_curr_vxid c=1: tps = 11841.412096 (without initial connection time)

pg_locks c=8: tps = 37010.065971 (without initial connection time)
pg_curr_vxid c=8: tps = 75025.838603 (without initial connection time)

pg_locks c=16: tps = 82045.746671 (without initial connection time)
pg_curr_vxid c=16: tps = 590842.022059 (without initial connection time)

pg_locks c=32: tps = 80797.877628 (without initial connection time)
pg_curr_vxid c=32: tps = 529788.677008 (without initial connection time)

It is 2x faster with 1 client and 6x faster with 32 clients. I believe this
is already enough to prove the improvement.

I will try to execute even more sophisticated tests with many parallel
sessions holding row locks.