PostgreSQL 19 重磅推出 “WAIT FOR LSN” patch, 读写分离场景支持逻辑一致性读了
P
digoal约 5 分钟
digoal约 5 分钟本期播客
PostgreSQL 19 重磅推出 “WAIT FOR LSN” patch, 读写分离场景支持逻辑一致性读了
试想一下, 在读写分离场景, 如果你的主从是异步留复制, 你在主库写了一笔存款记录, 马上去从库查询, 可能查不到这笔存款, 因为数据可能还没有同步过来.
现在有解决方案了, PostgreSQL 19支持WAIT FOR LSN [TIMEOUT ...]语法了, 如果在主库写入完成后, 查一下当时的LSN, 然后在从库进行查询之前, 等到LSN已replay, 再查一定能查到主库的变更.
但是我个人建议如果主库的DML能直接支持返回LSN就更好了, 而不用多查一遍.
不玩虚的, 我们一起来看看example:
<title>Examples</title>
<para>
You can use <command>WAIT FOR</command> command to waitfor
the <type>pg_lsn</type> value. For example, an application could update
the <literal>movie</literal> table and get the <acronym>lsn</acronym> after
changes just made. This example uses <function>pg_current_wal_insert_lsn</function>
on primary server to get the <acronym>lsn</acronym> given that
<varname>synchronous_commit</varname> could be set to
<literal>off</literal>.
<programlisting>
postgres=# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
UPDATE 100
postgres=# SELECT pg_current_wal_insert_lsn();
pg_current_wal_insert_lsn
--------------------
0/306EE20
(1 row)
</programlisting>
Then an application could run <command>WAIT FOR</command>
with the <parameter>lsn</parameter> obtained from primary. After that the
changes made on primary should be guaranteed to be visible on replica.
<programlisting>
postgres=# WAIT FOR LSN '0/306EE20';
status
--------
success
(1 row)
postgres=# SELECT * FROM movie WHERE genre = 'Drama';
genre
-------
(0 rows)
</programlisting>
</para>
<para>
If the target LSN is not reached before the timeout, the error is thrown.
<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '0.1s');
ERROR: timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60
</programlisting>
</para>
<para>
The same example uses <command>WAIT FOR</command> with
<parameter>NO_THROW</parameter> option.
<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '100ms', NO_THROW);
status
--------
timeout
(1 row)
</programlisting>
</para>
</refsect1>
</refentry>
patch如下:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3b4e53a075ea5671b075f8fd873241179f8e64af
Add infrastructure for efficient LSN waiting
author Alexander Korotkov <[email protected]>
Mon, 3 Nov 2025 11:31:13 +0000 (13:31 +0200)
committer Alexander Korotkov <[email protected]>
Wed, 5 Nov 2025 09:44:13 +0000 (11:44 +0200)
commit 3b4e53a075ea5671b075f8fd873241179f8e64af
tree eaa18a033d0ee4dfc8d2447346a4a7fbfac79919 tree
parent 8af3ae0d4b36f4cbd6c72b12357ba928d02b3ebd commit | diff
Add infrastructure for efficient LSN waiting
Implement a new facility that allows processes to waitfor WAL to reach
specific LSNs, both on primary (waiting for flush) and standby (waiting
for replay) servers.
The implementation uses shared memory with per-backend information
organized into pairing heaps, allowing O(1) access to the minimum
waited LSN. This enables fast-path checks: after replaying or flushing
WAL, the startup process or WAL writer can quickly determine if any
waiters need to be awakened.
Key components:
- New xlogwait.c/h module with WaitForLSNReplay() and WaitForLSNFlush()
- Separate pairing heaps for replay and flush waiters
- WaitLSN lightweight lock for coordinating shared state
- Wait events WAIT_FOR_WAL_REPLAY and WAIT_FOR_WAL_FLUSH for monitoring
This infrastructure can be used by features that need to waitfor WAL
operations to complete.
Discussion: https://www.postgresql.org/message-id/flat/CAPpHfdsjtZLVzxjGT8rJHCYbM0D5dwkO+BBjcirozJ6nYbOW8Q@mail.gmail.com
Discussion: https://www.postgresql.org/message-id/flat/CABPTF7UNft368x-RgOXkfj475OwEbp%2BVVO-wEXz7StgjD_%3D6sw%40mail.gmail.com
Author: Kartyshov Ivan <[email protected]>
Author: Alexander Korotkov <[email protected]>
Author: Xuneng Zhou <[email protected]>
Reviewed-by: Michael Paquier <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>
Reviewed-by: Amit Kapila <[email protected]>
Reviewed-by: Alexander Lakhin <[email protected]>
Reviewed-by: Bharath Rupireddy <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Heikki Linnakangas <[email protected]>
Reviewed-by: Kyotaro Horiguchi <[email protected]>
Reviewed-by: Xuneng Zhou <[email protected]>
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=447aae13b0305780e87cac7b0dd669db6fab3d9d
Implement WAIT FOR command
author Alexander Korotkov <[email protected]>
Wed, 5 Nov 2025 09:43:55 +0000 (11:43 +0200)
committer Alexander Korotkov <[email protected]>
Wed, 5 Nov 2025 09:44:13 +0000 (11:44 +0200)
commit 447aae13b0305780e87cac7b0dd669db6fab3d9d
tree 2ba9e956343d3f85d9d91011220047b8e80d6171 tree
parent 3b4e53a075ea5671b075f8fd873241179f8e64af commit | diff
Implement WAIT FOR command
WAIT FOR is to be used on standby and specifies waiting for
the specific WAL location to be replayed. This option is useful when
the user makes some data changes on primary and needs a guarantee to see
these changes are on standby.
WAIT FOR needs to wait without any snapshot held. Otherwise, the snapshot
could prevent the replay of WAL records, implying a kind of self-deadlock.
This is why separate utility command seems appears to be the most robust
way to implement this functionality. It's not possible to implement this as
a function. Previous experience shows that stored procedures also have
limitation in this aspect.
Discussion: https://www.postgresql.org/message-id/flat/CAPpHfdsjtZLVzxjGT8rJHCYbM0D5dwkO+BBjcirozJ6nYbOW8Q@mail.gmail.com
Discussion: https://www.postgresql.org/message-id/flat/CABPTF7UNft368x-RgOXkfj475OwEbp%2BVVO-wEXz7StgjD_%3D6sw%40mail.gmail.com
Author: Kartyshov Ivan <[email protected]>
Author: Alexander Korotkov <[email protected]>
Author: Xuneng Zhou <[email protected]>
Reviewed-by: Michael Paquier <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>
Reviewed-by: Amit Kapila <[email protected]>
Reviewed-by: Alexander Lakhin <[email protected]>
Reviewed-by: Bharath Rupireddy <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Heikki Linnakangas <[email protected]>
Reviewed-by: Kyotaro Horiguchi <[email protected]>
Reviewed-by: jian he <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Xuneng Zhou <[email protected]>