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.
synchronous_commit local, remote_write, remote_apply, on, off.
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.
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)
According to different requirements, you can set the modes at different levels.
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.
digoal - September 1, 2021
digoal - April 26, 2021
digoal - April 28, 2021
Alibaba Clouder - August 1, 2017
ApsaraDB - June 9, 2022
digoal - May 28, 2021
Fully managed and less trouble database servicesLearn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
This solution helps Internet Data Center (IDC) operators and telecommunication operators build a local public cloud from scratch.Learn More
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
More Posts by digoal