MIME-Version: 1.0
References: <CAMLfE0MAVH5YORs0OKmD=fFWH9EtuNsnhViti=+hkp3Kbz-Gfw@mail.gmail.com>
 <CAHyXU0yrwoEYia0mZhpjQLMdd-WXpWFM+Au1jXQpk3MtBvdogw@mail.gmail.com>
 <CAMLfE0MWqALMJ99dpFniZ1xknCgHj4ETPWro+Ry9awPUG1OKMw@mail.gmail.com>
 <CAHyXU0y2gZfH_ZomazQB7H0KkuyV3QofvuvZcSYXEhpotG=hMA@mail.gmail.com> <CALL-XeM=0oH=2ECawmyCOv2umKF1nyqr97CuQXiPfO39uhCucQ@mail.gmail.com>
In-Reply-To: <CALL-XeM=0oH=2ECawmyCOv2umKF1nyqr97CuQXiPfO39uhCucQ@mail.gmail.com>
From: Ellen Allhatatlan <ellenallhatatlan@gmail.com>
Date: Wed, 10 Sep 2025 08:41:14 +0100
Message-ID: <CAMLfE0O5YPhrXOWP1pLTLHy-rKDut5cmiGyuxo3d43LCGEkJYg@mail.gmail.com>
Subject: Re: MVCC and all that...
To: Justin <zzzzz.graf@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>, pgsql-general <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMLfE0O5YPhrXOWP1pLTLHy-rKDut5cmiGyuxo3d43LCGEkJYg%40mail.gmail.com>
Precedence: bulk

Hi, and thanks for your input,

Just before I reply - if you (at least here in Ireland - Google's
answers vary per location, unlike Duckduckgo's) search for "firebird
mvcc mechanism" the "AI assistant" tells me twice that FB's MVCC
implementation is "like PostgreSQL's"... I'll investigate further and
report back. Igor Rogov's book looks like a good place to start!

> I read through the article its click bait/flame war just waiting to happe=
n.
> Article is a list of cherry picked PG drawbacks that can be mitigated or =
worked around.

Pity - I took the guy at his word when he said that PostgreSQL was
just different, not better or worse.

> On the bulk updating.  I'm shaking my finger at any one that locks up 25%=
 of a table with an update or delete. That is asking for problems in a prod=
uction database with a high TPS rate.

OK - I'm going to run the benchmarks myself and see what happens - but
I"m sure he didn't pick that test for nothing - come to think of it,
the table stable structure is bizarre!

> The author brings up threaded vs multi-process. That's an old old old old=
 old conversation that has been shown there is no clear better way.

This is where things become interesting. Firebird actually has 3
process/threading models - and they manage to maintain these with a
team that is *_much_* smaller than the PostgreSQL one - FB is a minnow
compared to PG!

AIUI, Michael Stonebraker suggested that the process model
would/should be "upgraded" to a threaded one at some point in the
system's developement?


> Number of open connections.  so firebird can do 1000  open sessions with =
a smaller memory footprint,  still can not have 1000 simultaneous running s=
essions unless we have 1000 CPU's. Where is the win here??  We should be ma=
naging resources better on the application side, not opening thousands of c=
onnections that sit idle doing nothing.

Agreed on that point.

> On autonomous transactions we have procedures now that allow transactions=
 inside of transactions that can be committed and rollbacked.  that has bee=
n around for several years now.

OK.

> Backup argument is cherry picking and not discussing pgBackrest and other=
 solutions  or the use of tablespaces to isolate databases in a cluster at =
the disk layer  or disk snapshots.

OK again. I'm just wondering if the single file per database isn't a
fundamental architectural flaw in itself? AIUI, you could have
mulitple files (back in 32-bit land) "chained" - but (again AIUI) the
same table could be spread over x files - all "intermingled"... weird.

> "PostgreSQL has a relatively simple, but fast query planning algorithm"  =
Compared to what....  What feature is PG missing these days...  the only th=
ing I know it can't do is change the  plan  in the middle of the execution =
stage.  Which is not a query planner thing but the execution layer saying t=
o itself  I am taking too long maybe go back to the planning stage...  Quer=
y Hints that have been discussed endlessly.  Adding hints adds its own prob=
lems and has become a big mess for databases that support it.

I know - personally, I'm in favour of the PostgreSQL approach - rather
than improve the hints, improve the planner!

Plus, if you really want to, you can go here:
https://www.postgresql.org/docs/current/runtime-config-query.html and,
for example

SET enable_seqscan =3D OFF;

Plus, there is/are extension(s) which allow one to provide hints - I
did think this was a bit of a whopper alright!


> Multiple transactions per connection.  I am asking WHY is that a feature.=
  when one can have multiple sessions, what is the difference?  running mul=
tiple transactions in single or multiple sessions means moving  part of tra=
nsaction logic into the application space. What do we gain here.....

No idea - I'll take your word for it!

> No application packaging.  This Oracle thing that  firebird has duplicate=
d at some level.  we can simulate this with namespace/schemas.

Again, I'm not too sure of my ground here - but I do know that Oracle
(and SQL Server) are ahead in this domain.


> There are litigmate points here
> Compression,
> not being able to return partials result sets from functions
> XID being 32 bit

There's a lot of talk about 64 bit ones - FB has 48 bit ones AIUI -
that could kick the can down the road for PostgreSQL at the price of 2
bytes per record - is it worth it to alleviate the difficulties
associated with VACUUM-ing?

> anonymous functions in PG have several limitation not just input argument=
s (not sure i see the need for that)
> Temporary tables are a pain and cause issues for big databases

> The article is unfair in many places..

Accepted now - thanks for your input.


--=20

El!