Hi,
As I said before [1], I’m working on $SUBJECT. Attached is a WIP
patch for that. The patch is pretty simple: if a server option added
by the patch “parallel_commit” is enabled, 1) asynchronously send
COMMIT TRANSACTION (RELEASE SAVEPOINT) to all remote servers involved
in a local (sub)transaction, then 2) wait for the results from the
remote servers in the order that the command was sent to the remote
servers, when called from pgfdw_xact_callback (pgfdw_subxact_callback)
during pre-commit. The patch also parallelizes clearing prepared
statements the same way during pre-commit. (The option is false by
default.)
I evaluated the effectiveness of the patch using a simple
multi-statement transaction:
BEGIN;
SAVEPOINT s;
INSERT INTO ft1 VALUES (10, 10);
INSERT INTO ft2 VALUES (20, 20);
RELEASE SAVEPOINT s;
COMMIT;
where ft1 and ft2 are foreign tables created on different foreign
servers hosted on different machines. I ran the transaction five
times using the patch with the option enabled/disabled, and measured
the latencies for the RELEASE and COMMIT commands in each run. The
average latencies for these commands over the five runs are:
* RELEASE
parallel_commit=0: 0.385 ms
parallel_commit=1: 0.221 ms
* COMMIT
parallel_commit=0: 1.660 ms
parallel_commit=1: 0.861 ms
With the option enabled, the average latencies for both commands are
reduced significantly!
I think we could extend this to abort cleanup of remote
(sub)transactions during post-abort. Anyway, I think this is useful,
so I’ll add this to the upcoming commitfest.
Best regards,
Etsuro Fujita
[1] https://www.postgresql.org/message-id/CAPmGK177E6HPcCQB4-s%2Bm9AcCZDHCC2drZy%2BFKnnvEXw9kXoXQ%40mail.gmail.com
