PostgreSQL: Zero Data Loss and High-Performance Synchronous Switching and Transaction-Level Asynchronization

In this article, the author explains synchronous switching and transaction-level asynchronization in PostgreSQL with zero data loss and high performance.

By digoal

Background

Most databases are I/O-intensive, especially for TenProtect (TP) systems where the write operations are busy, such as account systems.

When committing a transaction, you must store the redo generated by the transaction in persistent storage to ensure data reliability.

You must optimize (reducing I/O frequency) the database software itself and the group submission to improve the response time. You can also reduce the response time (RT) by purchasing hardware with high Input/Output Operations Per Second (IOPS) capability.

Of course, if the hard disk’s I/O latency can be at the same level as the memory in the future, the redo is unnecessary.

In fact, another compromise is the asynchronous commit.

PostgreSQL has many asynchronous commit modes. The asynchronous commit of PostgreSQL does not cause data inconsistency and is written by a dedicated process during scheduling. Even if asynchronous, basically, the possibility of record loss in transactions is also minimal.

Example:

synchronous_commit  
  
local, remote_write, remote_apply, on, off.

Description:

local, 本地fsync  
  
remote_write, 本地fsync + 超阈值个数的 sync standby write   (quorum based sync standby)  
  
on, 本地fsync + 超阈值个数的 sync standby fsync  (quorum based sync standby)  
  
remote_write, 本地fsync + 超阈值个数的 sync standby apply   (quorum based sync standby)  
  
off, 本地write

PostgreSQL also supports the transaction-level correction of the synchronous_commit submission mode.

For example, you can set transactions requiring high reliability in the high-level modes. Transactions requiring low reliability can use the low-level modes to improve performance.

Example

Even in the same transaction, you can correct the synchronous_commit according to the amount involved.

For example, if the involved amount is less than 10 CNY, you can continue to use the asynchronous submission. Even if you lose money, you can use the compensation. Sometimes the hardware costs are much higher than the compensation amount.

1) Start a transaction.

postgres=# begin;  
BEGIN

2) Execute the Structured Query Language (SQL) in some transactions.

postgres=# select 2;  
 ? column?   
----------  
        2  
(1 row)

3) The default asynchronous mode is unchanged if the amount involved is less than 10 CNY.

postgres=# insert into tbl_charge values (3,1,now());  
INSERT 0 1  
postgres=# show synchronous_commit ;  
 synchronous_commit   
--------------------  
 off  
(1 row)

4) Actively set the current transaction mode to synchronous mode when the program finds that the amount involved is greater than 10 CNY. This ensures that the transaction is not lost after committing.

postgres=# set local synchronous_commit = on;  
SET  
postgres=# show synchronous_commit ;  
 synchronous_commit   
--------------------  
 on  
(1 row)  
  
postgres=# insert into tbl_charge values (4,10000000,now());  
INSERT 0 1

5) End the transaction. It is impossible to lose the transaction by using the synchronous mode.

postgres=# end;  
COMMIT

6) The current session returns to the default asynchronous mode.

postgres=# show synchronous_commit ;  
 synchronous_commit   
--------------------  
 off  
(1 row)

Settable Range for Modes

1) System-level
2) Database-level
3) User-level
4) Session-level
5) Transaction-level

According to different requirements, you can set the modes at different levels.

Summary

Through various submission mode combinations, you can achieve multiple reliability requirements.

By setting the mode at the transaction-level, PostgreSQL allows choosing between performance and reliability.

You can use lower-cost hardware to meet PostgreSQL’s mixed requirements of high reliability and high availability.

Community

PostgreSQL: Zero Data Loss and High-Performance Synchronous Switching and Transaction-Level Asynchronization

Background

Example

Settable Range for Modes

Summary

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

Local Public Cloud Solution

ApsaraDB RDS for PostgreSQL