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: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 9 Sep 2025 21:11:33 -0400
Message-ID: <CANzqJaBvw-CAFyE+v2c91PBQazO0Y7sTP8gxqQh6XKDVv=8y+Q@mail.gmail.com>
Subject: Re: MVCC and all that...
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009bbd57063e681a61"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBvw-CAFyE%2Bv2c91PBQazO0Y7sTP8gxqQh6XKDVv%3D8y%2BQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, Sep 9, 2025 at 8:41=E2=80=AFPM Justin <zzzzz.graf@gmail.com> wrote:

> 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.
>
> 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
> production database with a high TPS rate.
>
> 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.
>
> Number of open connections.  so firebird can do 1000  open sessions with =
a
> smaller memory footprint,  still can not have 1000 simultaneous running
> sessions unless we have 1000 CPU's. Where is the win here??  We should be
> managing resources better on the application side, not opening thousands =
of
> connections that sit idle doing nothing.
>
> On autonomous transactions we have procedures now that allow transactions
> inside of transactions that can be committed and rollbacked.  that has be=
en
> around for several years now.
>
> 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.
>
> "PostgreSQL has a relatively simple, but fast query planning algorithm"
> Compared to what....  What feature is PG missing these days...  the only
> thing 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 to itself  I am taking too long maybe go back to the plannin=
g
> stage...  Query Hints that have been discussed endlessly.  Adding hints
> adds its own problems and has become a big mess for databases that suppor=
t
> it.
>
> Multiple transactions per connection.  I am asking WHY is that a feature.
> when one can have multiple sessions, what is the difference?  running
> multiple transactions in single or multiple sessions means moving  part o=
f
> transaction logic into the application space. What do we gain here.....
>
> No application packaging.  This Oracle thing that  firebird has duplicate=
d
> at some level.  we can simulate this with namespace/schemas.
>
>
> I can keep going on here.
>
> There are litigmate points here
> Compression,
> not being able to return partials result sets from functions
> XID being 32 bit
>

Would converting them to 64 bits require changing the on-disk structure of
database files?


> anonymous functions in PG have several limitation not just input
> arguments (not sure i see the need for that)
>

Aren't transience and "ad hockery" the whole point of anonymous
procedures?  Thus, I don't see the point of passing them parameters, either=
.

(When I *do* need something similar, I build the DO block as a bash string
variable with environment variables as "parameters", and then execute it
via psql -c "$sql"  More like a template, TBH.  It's great for purging old
data from tables, since I can bypass records who's DELETE statements fail
due to a FK constraint.


> Temporary tables are a pain and cause issues for big databases
>

I'd like to see GLOBAL TEMPORARY tables.  Each connection gets its own
private copy of the table, so that applications don't need to carry around
CREATE TEMPORARY TABLE code with them.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000009bbd57063e681a61
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 8:41=E2=80=AFPM Ju=
stin &lt;<a href=3D"mailto:zzzzz.graf@gmail.com">zzzzz.graf@gmail.com</a>&g=
t; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">I read through the =
article its=C2=A0click bait/flame war just waiting to happen.<br><br>Articl=
e is a list of cherry picked=C2=A0PG drawbacks that can be mitigated or wor=
ked around.=C2=A0 =C2=A0 <br><br>On the bulk updating.=C2=A0 I&#39;m shakin=
g my finger at any one that locks up 25% of a table with an update or delet=
e. That is asking for problems in a production database with a high TPS rat=
e.<br><br>The author brings up threaded vs multi-process. That&#39;s an old=
 old old old old conversation that has been shown there is no clear better =
way.=C2=A0<div><br></div><div>Number of open connections.=C2=A0 so firebird=
 can do 1000=C2=A0 open sessions with a smaller memory footprint,=C2=A0 sti=
ll can not have 1000 simultaneous running sessions unless we have 1000 CPU&=
#39;s. Where is the win here??=C2=A0 We should be managing resources better=
 on the application=C2=A0side, not opening=C2=A0thousands of connections th=
at sit idle doing nothing.=C2=A0=C2=A0<br>=C2=A0=C2=A0<br>On=C2=A0autonomou=
s transactions we have procedures now that allow transactions inside of tra=
nsactions that can be committed and rollbacked.=C2=A0 that has been around =
for several years now.<br><br>Backup argument is=C2=A0cherry=C2=A0picking a=
nd not discussing pgBackrest and other solutions=C2=A0 or the use of tables=
paces to isolate databases in a cluster at the disk layer=C2=A0 or disk sna=
pshots.=C2=A0 =C2=A0</div><div><br></div><div>&quot;PostgreSQL has a relati=
vely simple, but fast query planning algorithm&quot;=C2=A0 Compared to what=
....=C2=A0 What feature is PG missing these days...=C2=A0 the only thing I =
know it can&#39;t do is change the=C2=A0 plan=C2=A0 in the middle of the ex=
ecution stage.=C2=A0 Which is not a query planner thing but the execution l=
ayer saying to itself=C2=A0 I am taking too long maybe go back to the plann=
ing stage...=C2=A0 Query Hints that have been discussed endlessly.=C2=A0 Ad=
ding=C2=A0hints adds its own problems and has become a big mess for databas=
es that support it.=C2=A0=C2=A0<br><br>Multiple transactions per connection=
.=C2=A0 I am asking WHY is that a feature.=C2=A0 when one can have multiple=
 sessions, what=C2=A0is the difference?=C2=A0 running multiple transactions=
 in single=C2=A0or multiple sessions means moving=C2=A0 part of transaction=
 logic into the application space. What do we gain here.....</div><div><br>=
</div><div>No application packaging.=C2=A0 This Oracle thing that=C2=A0 fir=
ebird has=C2=A0duplicated at some level.=C2=A0 we can simulate this with na=
mespace/schemas.<br><br>=C2=A0=C2=A0<br>I can keep going on here.=C2=A0=C2=
=A0<br>=C2=A0<br>There are litigmate points here <br>Compression, <br>not b=
eing able to return partials result sets from functions<br>XID being 32 bit=
=C2=A0<br></div></div></blockquote><div><br></div><div>Would converting the=
m to 64 bits require changing the on-disk structure of database files?</div=
><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div di=
r=3D"ltr"><div>anonymous functions in PG have several limitation not just i=
nput arguments=C2=A0(not sure i see the need for that)=C2=A0<br></div></div=
></blockquote><div><br></div><div>Aren&#39;t transience and &quot;ad hocker=
y&quot; the whole point of anonymous procedures?=C2=A0 Thus, I don&#39;t se=
e the point of passing them parameters, either.</div><div><br></div><div>(W=
hen I <i>do</i>=C2=A0need something similar, I build the DO block as a bash=
 string variable with environment variables as &quot;parameters&quot;, and =
then execute it via psql -c &quot;$sql&quot;=C2=A0 More like a template, TB=
H.=C2=A0 It&#39;s great for purging old data from tables, since I can bypas=
s records who&#39;s DELETE statements fail due to a FK constraint.</div><di=
v>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"><div>Temporary tables are a pain and cause issues for big databases<b=
r></div></div></blockquote><div><br></div><div>I&#39;d like to see GLOBAL T=
EMPORARY tables.=C2=A0 Each connection gets its own private copy of the tab=
le, so that applications don&#39;t need to carry around CREATE TEMPORARY TA=
BLE code with them.</div><div><br></div></div><span class=3D"gmail_signatur=
e_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></=
div></div></div></div>

--0000000000009bbd57063e681a61--