[pgpool-general: 8328] Re: Read write split with cache enable question
avi.raboah at gmail.com
Mon Jul 11 19:20:04 JST 2022
Thanks, I'll check it out.
On Mon, 11 Jul 2022 at 13:18 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > 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,
> > can I be sure that in case I am trying to read from the same table and
> > read replica not synced yet that I will not cached a stale data. Because
> > 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
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the pgpool-general