By digoal
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.
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)
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.
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.
PostgreSQL: Performing Linear Regression Analysis to Predict Future Data (Part 1)
digoal - September 1, 2021
digoal - April 26, 2021
digoal - April 28, 2021
Alibaba Clouder - August 1, 2017
ApsaraDB - June 9, 2022
Alibaba Cloud Community - May 2, 2024
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreThis solution helps Internet Data Center (IDC) operators and telecommunication operators build a local public cloud from scratch.
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by digoal