MIME-Version: 1.0
References: <CAMLfE0MAVH5YORs0OKmD=fFWH9EtuNsnhViti=+hkp3Kbz-Gfw@mail.gmail.com>
 <CAHyXU0yrwoEYia0mZhpjQLMdd-WXpWFM+Au1jXQpk3MtBvdogw@mail.gmail.com> <CAMLfE0MWqALMJ99dpFniZ1xknCgHj4ETPWro+Ry9awPUG1OKMw@mail.gmail.com>
In-Reply-To: <CAMLfE0MWqALMJ99dpFniZ1xknCgHj4ETPWro+Ry9awPUG1OKMw@mail.gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Tue, 9 Sep 2025 16:55:09 -0600
Message-ID: <CAHyXU0y2gZfH_ZomazQB7H0KkuyV3QofvuvZcSYXEhpotG=hMA@mail.gmail.com>
Subject: Re: MVCC and all that...
To: Ellen Allhatatlan <ellenallhatatlan@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f3ce0f063e66329c"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0y2gZfH_ZomazQB7H0KkuyV3QofvuvZcSYXEhpotG%3DhMA%40mail.gmail.com>
Precedence: bulk

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

On Tue, Sep 9, 2025 at 11:57=E2=80=AFAM Ellen Allhatatlan <
ellenallhatatlan@gmail.com> wrote:

> > Note: your link is wrong, corrected here:
>
> Extra hyphen - sorry about and thanks for pointing it out!
>
> > What the article is driving at is that postgres does not use rollback
> logs to handle updated records in the MVCC implementation.  There are
> absolutely performance tradeoffs in that decision and, if you do a lot of
> development against postgresql, those tradeoffs should influence how you
> design databases.  The author then cherry picked the 'worst case' case,
> large unconstrained updates.
>
> Hmm... I was wondering about that - even though he stressed that there
> was (paraphrasing) no right or wrong - just different design
> decisions!
>
> > The article is a bit of a cheezy dig on postgres.  Another example is
> the complaint about autonomous transactions with another cherry picked
> example to make postgres look back. In the real world, these would not
> matter much, and can be worked around (if you want to see my take on how =
to
> deal with it, see here: https://github.com/leaselock/pgasync).
>
> OK - so, I was wrong in my original assumption that somehow (and it
> wasn't simply because of the phraseology - sweep vs vacuum) I thought
> that PG and FB had a similar MVCC implementation vs. Oracle and MySQL
> (InnoDB) (and OrioleDB). I'll do a deep dive into their docco and see
> what they actually do! I'm actually very interested in the
> benchmarking side of database technology - but I do know the old adage
> - there are lies, damned lies, statistics and *_then_* there are
> database benchmarks (as seen with the link I posted!).
>

Sure. I think you'll find that postgres approach to MVCC is somewhat
unusual relative to other players in this space, which is to write 'old'
records or changes in a rollback log; if the transaction commits it is
discarded but if it rolls back, the rollback log is written back to the
heap.  This makes rollbacks potentially very painful.  I suspect the
postgres approach can also do better in cases of highly contended records,
but that's just a guess.

For small (one or a small number of records) updates, the approach doesn't
make a whole lot of difference especially if you are aware of and exploit
HOT.  For very large updates however, it absolutely does, and one might try
to avoid them using various strategies.  I very much appreciate fast
rollbacks though.

merlin


merlin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Sep 9, 2025 at 11:57=E2=80=AFAM E=
llen Allhatatlan &lt;<a href=3D"mailto:ellenallhatatlan@gmail.com">ellenall=
hatatlan@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quot=
e_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">&gt; Note: y=
our link is wrong, corrected here:<br>
<br>
Extra hyphen - sorry about and thanks for pointing it out!<br>
<br>
&gt; What the article is driving at is that postgres does not use rollback =
logs to handle updated records in the MVCC implementation.=C2=A0 There are =
absolutely performance tradeoffs in that decision and, if you do a lot of d=
evelopment against postgresql, those tradeoffs should influence how you des=
ign databases.=C2=A0 The author then cherry picked the &#39;worst case&#39;=
 case, large unconstrained updates.<br>
<br>
Hmm... I was wondering about that - even though he stressed that there<br>
was (paraphrasing) no right or wrong - just different design<br>
decisions!<br>
<br>
&gt; The article is a bit of a cheezy dig on postgres.=C2=A0 Another exampl=
e is the complaint about autonomous transactions with another cherry picked=
 example to make postgres look back. In the real world, these would not mat=
ter much, and can be worked around (if you want to see my take on how to de=
al with it, see here: <a href=3D"https://github.com/leaselock/pgasync" rel=
=3D"noreferrer" target=3D"_blank">https://github.com/leaselock/pgasync</a>)=
.<br>
<br>
OK - so, I was wrong in my original assumption that somehow (and it<br>
wasn&#39;t simply because of the phraseology - sweep vs vacuum) I thought<b=
r>
that PG and FB had a similar MVCC implementation vs. Oracle and MySQL<br>
(InnoDB) (and OrioleDB). I&#39;ll do a deep dive into their docco and see<b=
r>
what they actually do! I&#39;m actually very interested in the<br>
benchmarking side of database technology - but I do know the old adage<br>
- there are lies, damned lies, statistics and *_then_* there are<br>
database benchmarks (as seen with the link I posted!).<br></blockquote><div=
><br></div><div>Sure. I think you&#39;ll find that postgres approach to MVC=
C is somewhat unusual relative to other players in this space, which is to =
write &#39;old&#39; records or changes in a rollback log; if the transactio=
n commits it is discarded but if it rolls back, the rollback log is written=
 back to the heap.=C2=A0 This makes rollbacks potentially very painful.=C2=
=A0 I suspect the postgres approach can also do better in cases of highly c=
ontended records, but that&#39;s just a guess.</div><div><br></div><div>For=
 small (one or a small number of records) updates, the approach doesn&#39;t=
 make a whole lot of difference especially if you are aware of and exploit =
HOT.=C2=A0 For very large updates however, it absolutely does, and one migh=
t try to avoid them using various strategies.=C2=A0 I very much appreciate =
fast rollbacks though.</div><div><br></div><div>merlin</div><div><br></div>=
<div><br></div><div><br></div><div><br></div><div><br></div><div>merlin=C2=
=A0</div></div></div>

--000000000000f3ce0f063e66329c--