[pgpool-general: 8327] Re: Read write split with cache enable question

Tatsuo Ishii ishii at sraoss.co.jp
Mon Jul 11 19:18:40 JST 2022

> Hi,
> I am trying to check how the real write split working with caching, and I
> think I found an issue which return stale data.
> The scenario is the following:
> 1. I have a primary for write operation and read replica for reads.
> 2. I am using streaming replication mode.
> 3. When I am doing a write which cause an invalidation on the primary, how
> can I be sure that in case I am trying to read from the same table and the
> read replica not synced yet that I will not cached a stale data. Because in
> case the read replica not synced yet what will happened is pgpool will
> cache what he got from the read replica and the user will get stale data
> until the next invalidation.

Currently there's no way to completely avoid that. Probably you could
specify memqcache_expire so that the stale cache does not remain for
long time.

> Any thoughts on this? Do you encounter with this issue before?

The fundamental problem is, PostgreSQL's asynchronous replication does
not guarantee that any committed transaction data is also committed at
the same time on standby servers. The only way to guarantee it is,
using synchronous replication with "synchronous_commit = remote_apply"

Or you could use backend_clustering_mode = 'snapshot_isolation' in
pgpool. This is superior than streaming replication in that it allows
to have cross server consistent visibility. See manual for more
details to know why you need that.

However there are some down side in the mode including the only
allowed transaction isolation mode is REPEATABLE READ.  Also the mode
inherits same limitaions from native replication mode.

Best reagards,
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php

More information about the pgpool-general mailing list