WAIT FOR LSN 4 大场景

本期播客

WAIT FOR LSN 4大场景

上次已发表《PostgreSQL 19 preview - 重磅推出WAIT FOR LSN patch, 读写分离场景支持逻辑一致性读了》

这次更猛, 支持4种堵塞选项了.

• 'standby_replay' (default): Wait for WAL to be replayed to the specified LSN,
• 'standby_write': Wait for WAL to be written (received) to the specified LSN,
• 'standby_flush': Wait for WAL to be flushed to disk at the specified LSN,
• 'primary_flush': Wait for WAL to be flushed to disk on the primary server.

第一种可用于读写分离的一致性读场景, 当请求发送给只读节点前, 先在该RO节点执行WAIT FOR LSN判断该RO节点是否已恢复到某个位点.

中间2种可用于提高某些高可靠性要求的事务的WAL可靠性. 例如在commit之前, 先到ro节点看看之前的WAL是不是都接收到了. 然后再发起commit.

最后一种, 感觉鸡肋? 实则牛逼, 用好了就是屠龙刀. 例如, 你在数据库中可放心大胆的开启异步提交, 但是当你的某些少量事务有可靠性要求时, commit 之前可使用WAIT FOR LSN先等待一下, 确保之前的WAL都成功 flush.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=49a181b5d634340fcfb7c762c387c03f6405367e

Add the MODE option to the WAIT FOR LSN command

This commit extends the WAIT FOR LSN command with an optional MODE option in
the WITH clause that specifies which LSN type to waitfor:  

  WAIT FOR LSN '<lsn>' [WITH (MODE '<mode>', ...)]  

where mode can be:  
 - 'standby_replay' (default): Wait for WAL to be replayed to the specified  
   LSN,  
 - 'standby_write': Wait for WAL to be written (received) to the specified  
   LSN,  
 - 'standby_flush': Wait for WAL to be flushed to disk at the specified LSN,  
 - 'primary_flush': Wait for WAL to be flushed to disk on the primary server.  

The default mode is 'standby_replay', matching the original behavior when MODE  
is not specified. This follows the pattern used by COPY and EXPLAIN  
commands, where options are specified as string values in the WITH clause.  

Modes are explicitly named to distinguish between primary and standby  
operations:  
- Standby modes ('standby_replay', 'standby_write', 'standby_flush') can only  
  be used during recovery (on a standby server),  
- Primary mode ('primary_flush') can only be used on a primary server.  

The 'standby_write' and 'standby_flush' modes are useful for scenarios where
applications need to ensure WAL has been received or persisted on the standby  
without necessarily waiting for replay to complete. The 'primary_flush' mode  
allows waiting for WAL to be flushed on the primary server.  

This commit also includes includes:  
- Documentation updates for the new syntax and mode descriptions,  
- Test coverage for all four modes, including error cases and concurrent  
  waiters,  
- Wakeup logic in walreceiver for standby write/flush waiters,  
- Wakeup logic in WAL writer for primary flush waiters.  

Discussion: https://postgr.es/m/CABPTF7UiArgW-sXj9CNwRzUhYOQrevLzkYcgBydmX5oDes1sjg%40mail.gmail.com  
Author: Xuneng Zhou <[email protected]>  
Reviewed-by: Alexander Korotkov <[email protected]>  
Reviewed-by: Chao Li <[email protected]>  
Reviewed-by: Alvaro Herrera <[email protected]>