Date: Wed, 10 Sep 2025 16:28:09 -0500
From: Nico Williams <nico@cryptonector.com>
To: Justin <zzzzz.graf@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>,
	Ellen Allhatatlan <ellenallhatatlan@gmail.com>,
	pgsql-general <pgsql-general@postgresql.org>
Subject: Re: MVCC and all that...
Message-ID: <aMHtafqqx6UmC5T9@ubby>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CALL-XeM=0oH=2ECawmyCOv2umKF1nyqr97CuQXiPfO39uhCucQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aMHtafqqx6UmC5T9%40ubby>
Precedence: bulk

On Tue, Sep 09, 2025 at 08:41:02PM -0400, Justin wrote:
> 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 relevant to the next part:

> 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.

When a service is written in such a way as to minimize the memory
footprint of each request/client then it can process more of them
assuming it's only memory-bound.  Why?  Because less memory per thing ==
less bandwidth use, and also less thrashing of caches and higher cache
hit ratios.

Minimizing request/client state means not spreading any of it on the
stack, thus not requiring a stack per-client.  This means not
thread-per-client (green or otherwise) or process-per-client.  It means
essentially some flavor of continuation passing style (CPS).  For a
query plan executor that's really: the query plan, all the in-flight I/O
requests, all cached data still needed to continue processing the plan.
If you have a Duff's device style / CPS style implementation, then
nothing on the stack needs to be preserved while waiting for I/Os, and
the state of the query plan is effectively minimized.

But for a database with storage I/O costs the memory footprint doesn't
matter quite so much because most likely it will be I/O bound rather
than CPU- or memory-bound.

> "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 planning
> stage...  Query Hints that have been discussed endlessly.  Adding hints
> adds its own problems and has become a big mess for databases that support
> it.

I would really like out-of-band hints.  These would be hints not
specified in the SQL itself but to be sent separately and which address
table sources or joins by name, like this:

psql> SELECT .. FROM x x1 JOIN y y1 ON .. JOIN y y2 ON .. WHERE ..;
...> \hint y1 indexed by ..
...> \hint y2 indexed by ..
...> ;

> 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 of
> transaction logic into the application space. What do we gain here.....

I agree it's not really important.  Moreover interleaving multiple
queries over one TCP connection will lead to having to manage how much
bandwidth each query consumes so as not to drown out the others.

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

And extensions.

> XID being 32 bit

This is a huge problem.

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

Yes.  PG badly needs GLOBAL TEMP.

Another thing that would be nice is if PG could have tables that are not
heaps.

Nico
--