knowledge is power

0%

parallel commit in postgres fdw

Posted on Edited on In , , ,

Featured image

1. Overview

PostgreSQL is one of the greatest open source databases, not only because of the extensibility and SQL compliance but also the evolution of new features. For example, in postgres_fdw, there is a new feature parallel commit has been added into the main branch and will be released in PG15. This blog is for a quick taste of this new feature.

2. how parallel commit works

If you are a PostgreSQL database developer or if your internal database is built based on PostgreSQL database, and especially you have some applications which are related with the extension postgres_fdw, then you might wnat to take a look at this parallel commit feature. This new feature is committed just a month ago. It may help you on solving some performance issues, or as a reference if you are planning to build any parallel features on a postgres_fdw based solution. Here is the details about this parallel commit for postgres_fdw.

1
2
3
4
5
6
7
8
9
10
11
commit 04e706d4238f98a98e1c0b1a02db9d4280b96f04
Author: Etsuro Fujita <efujita@postgresql.org>
Date:   Thu Feb 24 14:30:00 2022 +0900

    postgres_fdw: Add support for parallel commit.
    
    postgres_fdw commits remote (sub)transactions opened on remote server(s)
    in a local (sub)transaction one by one when the local (sub)transaction
    commits.  This patch allows it to commit the remote (sub)transactions in
    parallel to improve performance.  This is enabled by the server option
    "parallel_commit".  The default is false.

By default, this parallel commit feature is turned off. If you want to try it you can simply turn it on by 

1
ALTER SERVER loopback OPTIONS (ADD parallel_commit 'true');

Once this parallel commit option is on for those foreign servers involved in a local transaction and when this local transaction commits, the opened remote transaction on those foreign servers will be committed in parallel. By providing this option, PostgreSQL community expects some performance improvement when multiple foreign servers involved in a transaction. This parallel commit feature can be useful for some applications on distributed PostgreSQL database clusters using postgre_fdw.

To verify the performance improvement, you can simply test it for before and after using below commands,

1
2
3
4
5
6
7
8
9
psql -d postgres -p 5432 -Atq <<EOT
\timing on
BEGIN;
SAVEPOINT s;
INSERT INTO ft1 VALUES (10, 10);
INSERT INTO ft2 VALUES (20, 20);
RELEASE SAVEPOINT s;
COMMIT;
EOT

According to the initial discussion for this parallel commit feature, below are some performance numbers for your reference.

1
2
3
4
5
6
7
* 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

To disable this feature, you can run a command like below,

1
ALTER SERVER loopback OPTIONS (DROP parallel_commit);

3. Summary

In this blog, we discussed the parallel commit feature recently added to postgres_fdw. When you apply this feature to your production servers you might need to be careful as it is mentioned in the document, this option might increase the remote server’s load when the local (sub)transaction commits.