MIME-Version: 1.0
References: <CAGqGHCNs_OWZjhNghiaENCLdRF3g7zc4cCegRcYwB1RFLKVf8g@mail.gmail.com>
 <fd556dbe18b9618b8eba03cf3703dac4c4d5c139.camel@cybertec.at>
In-Reply-To: <fd556dbe18b9618b8eba03cf3703dac4c4d5c139.camel@cybertec.at>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Fri, 24 Jan 2025 11:40:10 +0100
Message-ID: <CAKkG4_=a01yfmkjPgpU3h=QMcmo=q+9RcXvFV+p5Yv7kdyjkBw@mail.gmail.com>
Subject: Re: Is postgresql's json strong consistency or eventually consistency?
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: anlex N <an0291170@gmail.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008342dd062c715a35"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_%3Da01yfmkjPgpU3h%3DQMcmo%3Dq%2B9RcXvFV%2Bp5Yv7kdyjkBw%40mail.gmail.com>
Precedence: bulk

--0000000000008342dd062c715a35
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Jan 16, 2025 at 2:02=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Thu, 2025-01-16 at 15:25 +0300, anlex N wrote:
> > I have searched all archives, but have no results. Please help me.
>
> That's probably because the question doesn't make much sense.
> A single PostgreSQL instance (=3Dcluster) enforces consistency,
> and it doesn't matter which data type you are using.
>
> So I guess the answer should be "strong consistency".


I guess what the original author was trying to ask is how many databases
have to acknowledge a transaction before it is considered committed, stuff
that's called in mongodb write_concern and read_concern.

@anlex, PG at its heart is not a distributed system. When you talk about
consistency in PG you normally mean foreign key consistency or similar,
properties where data in one table is somehow related to data in another
table. A slightly different meaning of consistency comes when you talk
about transaction isolation levels.

All of this is different from Mongodb's read/write consistency. In Mongodb
a write can go to one machine while the subsequent read can fetch data from
another. So, you worry about linearizability, causal consistency and such
things. The only slightly similar question in PG is "Can I see data
somehow, on the master or a replica, that has been written to the database
but where the master has not yet acknowledged the commit to the client?"

The short answer is, yes. As soon as the commit record has been written to
the WAL, it is being transferred and replayed on streaming replicas. After
that the master might still have to do things like waiting for a
synchronous replica to acknowledge the transaction before the transaction
becomes visible on the master. On a fast enough replica, the transaction
can, hence, become visible before it's visible on the master.

https://foertsch.cc/en/postgres/commit-timing

You can control that behavior with the `synchronous_commit` setting in
combination with `synchronous_standby_names`. You can tell the DB, for
instance, a transaction should be considered as committed only when 3 out
of 5 replicas have acknowledged it.

If you set synchronous_commit=3Dremote_apply and configure N out of N
replicas in synchronous_standby_names, that would likely give you a
distributed database with strong consistency where you can direct writes to
the master and read-only load to the replicas. But I guess nobody in his
right mind would do that unless your database is rarely written to.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jan 16, 2025 at 2:02=E2=80=AFPM L=
aurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cy=
bertec.at</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, 2025-01-16 at=
 15:25 +0300, anlex N wrote:<br>
&gt; I have searched all archives, but have no results. Please help me.<br>
<br>
That&#39;s probably because the question doesn&#39;t make much sense.<br>
A single PostgreSQL instance (=3Dcluster) enforces consistency,<br>
and it doesn&#39;t matter which data type you are using.<br>
<br>
So I guess the answer should be &quot;strong consistency&quot;.</blockquote=
><div><br></div><div>I guess what the=C2=A0original author was trying to as=
k is how many databases have to acknowledge a transaction before it is cons=
idered committed, stuff that&#39;s called in mongodb write_concern and read=
_concern.</div><div><br></div><div>@anlex, PG at its heart is not a distrib=
uted system. When you talk about consistency in PG you normally mean foreig=
n key consistency or similar, properties where data in one table is somehow=
 related to data in another table. A slightly different meaning of consiste=
ncy comes when you talk about transaction isolation levels.</div><div><br><=
/div><div>All of this is different from Mongodb&#39;s read/write consistenc=
y. In Mongodb a write can go to one machine while the subsequent read can f=
etch data from another. So, you worry about linearizability, causal consist=
ency and such things. The only slightly similar question in PG is &quot;Can=
 I see data somehow, on the master or a replica, that has been written to t=
he database but where the master has not yet acknowledged the commit to the=
 client?&quot;</div><div><br></div><div>The short answer is, yes. As soon a=
s the commit record has been written to the WAL, it is being transferred an=
d replayed on streaming replicas. After that the master might still have to=
 do things like waiting for a synchronous replica to acknowledge the transa=
ction before the transaction becomes visible on the master. On a fast enoug=
h replica, the transaction can, hence, become visible before it&#39;s visib=
le on the master.</div><div><br></div><div><a href=3D"https://foertsch.cc/e=
n/postgres/commit-timing">https://foertsch.cc/en/postgres/commit-timing</a>=
</div><div><br></div><div>You can control that behavior with the `synchrono=
us_commit` setting in combination with `synchronous_standby_names`. You can=
 tell the DB, for instance, a transaction should be considered as committed=
 only when 3 out of 5 replicas have acknowledged it.</div><div><br></div><d=
iv>If you set synchronous_commit=3Dremote_apply and configure N out of N re=
plicas in synchronous_standby_names, that would likely give you a distribut=
ed database with strong consistency where you can direct writes to the mast=
er and read-only load to the replicas. But I guess nobody in his right mind=
 would do that unless your database is rarely written to.</div></div></div>

--0000000000008342dd062c715a35--