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. what is parallel commit postgres_fdw
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.
commit 04e706d4238f98a98e1c0b1a02db9d4280b96f04 Author: Etsuro Fujita <email@example.com> 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
ALTER SERVER loopback OPTIONS (ADD parallel_commit 'true');
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,
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.
* 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,
ALTER SERVER loopback OPTIONS (DROP parallel_commit);
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.
A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.