MIME-Version: 1.0
From: Jacob Biesinger <jake.biesinger@gmail.com>
Date: Tue, 15 Oct 2024 16:27:49 -0700
Message-ID: <CAHYXj6eE0jMyeM-c-szjzDLVB6WSD=bYH3KiCgZ2s8f3Z_HV9Q@mail.gmail.com>
Subject: serializable master and non-serializable hot standby: feasible set up?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dc2bbf06248c4d36"
Archived-At: <https://www.postgresql.org/message-id/CAHYXj6eE0jMyeM-c-szjzDLVB6WSD%3DbYH3KiCgZ2s8f3Z_HV9Q%40mail.gmail.com>
Precedence: bulk

--000000000000dc2bbf06248c4d36
Content-Type: text/plain; charset="UTF-8"

Howdy!

I've been going back and forth with the GCP CloudSQL engineering team about
the feasibility of a particular setup, and I'm pinging the list here
hoping for a sanity check. They assure me that it's impossible and I think
they must be mistaken, but I have limited experience administrating my own
postgres instances. So I'm appealing to a higher authority :)

The docs outline[1
<https://www.postgresql.org/docs/current/hot-standby.html>][2
<https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY>]
that a hot standby / replica with the flag
`default_transaction_isolation='serializable'` is going to cause issues --
while you can connect to such an instance, basically every query against it
will fail.

But! If you can somehow manage to get the replica's flags to instead
use `repeatable
read` or  `read committed` isolation, everything seems to work well, even
if the master uses `serializable` isolation. In GCP, we are having to
temporarily swap the master to a lower isolation level, then stand up the
replica and pin the lower isolation level flag, and then finally revert the
flag change on the master. If the replica goes down, we have to repeat this
process and it's a pain (not to mention data issues since our app relies on
this isolation level instead of doing explicit locking in most cases).


So I know this is an awkward question to post here, but as postgres admin
professionals, *would you* expect to be able to stand up a `repeatable
read` replica against a `serializable` master? My expectation is that you'd
simply change the setting in a .conf file on the replica and be good to go;
is there something that would make this process really difficult /
impossible?

Thanks so much!

[1]: https://www.postgresql.org/docs/current/hot-standby.html
[2]:
https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY
--
Jake Biesinger

--000000000000dc2bbf06248c4d36
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Howdy!<div><br></div><div>I&#39;ve been going back and for=
th with the GCP CloudSQL engineering team about the feasibility of a partic=
ular setup, and I&#39;m pinging the list here hoping=C2=A0for a sanity chec=
k. They assure me that it&#39;s impossible and I think they must be mistake=
n,=C2=A0but I have limited=C2=A0experience administrating my own postgres i=
nstances. So I&#39;m appealing to a higher authority :)</div><div><br></div=
><div><div>The docs outline[<a href=3D"https://www.postgresql.org/docs/curr=
ent/hot-standby.html">1</a>][<a href=3D"https://www.postgresql.org/docs/cur=
rent/applevel-consistency.html#SERIALIZABLE-CONSISTENCY">2</a>] that a hot =
standby / replica with the flag=C2=A0 <font face=3D"monospace">`default_tra=
nsaction_isolation=3D&#39;serializable&#39;`</font> is going to cause issue=
s -- while you can connect to such an instance, basically every query again=
st it will fail.</div><div><br></div><div>But! If you can somehow manage to=
 get the replica&#39;s flags to instead use `<font face=3D"monospace">repea=
table read</font>` or=C2=A0 `<font face=3D"monospace">read committed</font>=
` isolation, everything seems to work well, even if the master uses `serial=
izable` isolation. In GCP, we are having to temporarily swap the master to =
a lower isolation level, then stand up the replica and pin the lower isolat=
ion level flag, and then finally revert the flag change on the master. If t=
he replica goes down, we have to=C2=A0repeat this process and it&#39;s a pa=
in (not to mention data issues since our app relies on this isolation level=
 instead of doing explicit locking in most cases).</div></div><div><br></di=
v><div><br></div><div>So I know this is an awkward question to post here, b=
ut as postgres admin professionals, *would you* expect to be able to stand =
up a `repeatable read` replica against a `serializable` master? My expectat=
ion is that you&#39;d simply change the setting in a .conf file on the repl=
ica and be good to go; is there something that would make this process real=
ly difficult / impossible?</div><div><br></div><div>Thanks so much!</div><d=
iv><br></div><div>[1]: <a href=3D"https://www.postgresql.org/docs/current/h=
ot-standby.html">https://www.postgresql.org/docs/current/hot-standby.html</=
a></div><div>[2]: <a href=3D"https://www.postgresql.org/docs/current/applev=
el-consistency.html#SERIALIZABLE-CONSISTENCY">https://www.postgresql.org/do=
cs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY</a><br clear=
=3D"all"><div><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"=
gmail_signature"><div dir=3D"ltr"><div>--<br>Jake Biesinger<br></div></div>=
</div></div></div></div>

--000000000000dc2bbf06248c4d36--