Using PostgreSQL on PostgreSQL 15_ FDW performs parallel commit of transactions

Using data from a remote external database into our local database like using another local table is a great feature , It adds excellent flexibility to the design of database solutions .

This option exists in many database products .Oracle (DBLINK)、MySQL (FEDERATED Engine), obviously PostgreSQL (dblink, postgres_fdw) All support it .PostgreSQL Support multiple external data wrappers . But in this blog , We will review in the new version 15 Specific functions added in .

postgres_fdw How it works

Suppose you have a local and a remote PostgreSQL The server . The second one has a table , Your application using the local server needs to access this table . You may think of any of the following solutions ：

1. Configure your application to be able to connect to two servers .
2. Set up some background jobs to perform replication from time to time .
3. Configure logical replication between two servers , So that the local table can be synchronized with the remote table .

Any of the above has its own advantages and disadvantages . For the first , You may need to configure the network and grant access to both database servers . All this may add complexity and open the door to some security issues . The second option will add additional load on both servers . Data will not be updated in real time , Therefore, some read inconsistencies may occur . The third may actually be a good choice . however , If you plan to modify the synchronization data , Then you may encounter some conflicts .

Then the solution may be Foreign Data Wrapper Medium relay . In the official documentation , From remote PostgreSQL The following steps are required for the server to access data ：

1. Use CREATE EXTENSION Install the extension .postgres_fdw
2. Use CREATE SERVER Create an external server object , To represent each remote database you want to connect . Specify connection information , In addition to users and passwords , Options as server objects .
3. Use CREATE USER MAPPING Create a user mapping for each database user you want to allow access to each external server . Specify the remote user name and password to use as the user and password options for user mapping .
4. Use CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA Create an external table for each remote table you want to access . The columns of the external table must match the referenced remote table . however , If you specify the correct remote name as the option of the external table object , You can use tables and... That are different from remote tables / Or column name .

You'll end up like this ：

So now , With appropriate permissions , Your application can access the data in the remote table as it accesses another local table , You can choose or even execute DML sentence .

PG15 What has changed ？

The functions described are PostgreSQL Version has been in existence for some time , And will be in version 15 The new enhancement provided in is to use . When your solution design depends on having multiple remote servers （ For example, distributed PostgreSQL database ） when , This is especially useful . postgres_fdw

From the transaction management document , We know “ During queries that reference any remote tables on the external server , If the transaction on the remote server corresponding to the current local transaction has not been opened ”, In our local case, transactions refer to multiple remote tables from different external servers , Then open one on each remote server （ Son ） Business .postgres_fdw

Until the version 14, When there is more than one （ Son ） When a transaction is bound to a local transaction and the transaction is committed locally , One by one （ According to the order ） Submit each remote （ Son ） Business . This behavior is still a version 15 Default behavior of , But now we can choose to enable this option for each external server bound to local transactions , under these circumstances , When a local transaction is committed , All remote （ Son ） The transaction will be committed once （ parallel ）. postgres_fdwparallel_commit

Laboratory cases

I set up a lab to test sequential and parallel remote （ Son ） Transactions commit and capture differences .

I decided to use pgbench Tools for benchmarking and options for creating partitions for tables , I adjust these partitions to external tables , just as Percona About PostgreSQL Upper Sharding As described in my very interesting blog , It's worth seeing the last one . Last , I created a partition table , Among them is 70 Zones , Distribution in 7 On remote servers , Each is connected to an external server . pgbench_accountspostgres_fdw

The following figure gives some insights .

Pgbench perform

To test new features , I tried two different loads , Disable options （ Default ）, Then enable ：parallel_commit

I run “ normal ” Of pgbench The workload . Each transaction accesses one row , This means that only one remote is opened for each local transaction （ Son ） Business .
I ran a pgbench“ Customize ” Script to retrieve each transaction 100 That's ok . under these circumstances , Each local transaction opens multiple remote （ Son ） Business .
in the light of PG15 The server runs normally pgbench The load does not exceed ：

pgbench --progress-timestamp -c 16 -j 4 -T 3600 -P 2 \  -h pg15 -U pgbench pgbench

After obtaining the results , I enabled this option on each external server in the following way ：parallel_commit

LTER SERVER remote_server<N> OPTIONS (ADD parallel_commit 'true');

And repeat pgbench load . give the result as follows .

We found that the parallelism option is set to OFF or ON There is not much difference between the loads . This is to be expected , Because in routine pgbench Loading , Only one remote transaction is opened for each local transaction .

For the second test scenario , I use custom pgbench The script executes on each transaction 100 That's ok , As shown below ：

$ cat pgbench_custom.sql\set v1 random(1, 100000 * :scale)\set v2 :v1 + 100BEGIN;  SELECT abalance FROM pgbench_accounts  WHERE aid BETWEEN :v1 AND :v2;END;

Before running the load , I disabled the option of executing the next external server for each ：parallel_commit

ALTER SERVER remote_server<N> OPTIONS (DROP parallel_commit);

Then I run the custom load without parallel submission in the following way ：

pgbench --progress-timestamp -c 8 -j 4 -T 3600 -P 2 \  -h pg15 -U pgbench -f pgbench_custom.sql pgbench

Then I use the command I used before to enable it on each external server and run the custom load again . Next is the result .

Now we see a significant improvement when using the enabled option . When this option is enabled , Testing period TPS The number of is always high , Shows how to improve performance .parallel_commit

in general , And Convention pgbench Compared with the load ,TPS Declined . Details are beyond the scope of this blog post , The goal here is to understand how to use new options to improve performance under similar conditions . parallel_commit

Conclusion

PostgreSQL The functions of database have long been known , And many system designs make use of these functions . postgres_fdw

stay PostgreSQL 15 The new options added when using in bring a good opportunity to improve the performance of our database , If they are designed as distributed systems and our local transactions will be with multiple remote （ Son ） The affairs are related . Each solution and system design requires its own analysis . This blog post is only intended to show new options , And how we can provide higher throughput under the same conditions by enabling new options . parallel_commitpostgres_fdwparallel_commit

Original title ：Parallel Commits for Transactions Using postgres_fdw on PostgreSQL 15
Original author ：David Gonzalez
Original address ：https://www.percona.com/blog/parallel-commits-for-transactions-using-postgres_fdw-on-postgresql-15/