Date: Wed, 10 Sep 2025 18:07:51 -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: <aMIEx7Y4BPXYp+0t@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>
 <aMHtafqqx6UmC5T9@ubby>
 <CALL-XeOVhXeGTxQ-m=a__YqKrhWROTdv6Ea-zfN2-rFtRQyfSg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CALL-XeOVhXeGTxQ-m=a__YqKrhWROTdv6Ea-zfN2-rFtRQyfSg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aMIEx7Y4BPXYp%2B0t%40ubby>
Precedence: bulk

On Wed, Sep 10, 2025 at 06:20:18PM -0400, Justin wrote:
> I am not following you here,   Databases are going to be bound somewhere at
> some point, Disk,IO, Network IO, Memory, or CPU bound.  Which one is
> causing the bottle neck just depends on the workload and size of the
> database.
> 
> The number of idle sessions does not really matter  it is just
> wasting resources across the entire application stack.

Idle sessions do cost something, but I agree it's not much.

The point is that if you've got a thread- or process-per-client/request
architecture then you can easily make the per-client memory footprint
(and cache pressure) of the service much higher than if per-client/req
state was minimized (by not spreading it over the stack).  If you do
that you might go from I/O-bound to memory-bound, though frankly I doubt
it.  Instead the likely cost of PG's architecture is just that you need
more memory per (active) client, which is probably a fine price to pay
considering how featureful PG is and how active the PG community is.

> > 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 ..
> > ...> ;
> 
> I humbly disagree, the point of SQL being a 4th  generation language is,  I
> tell it what I want, not how to go get what I want.

This debate will go on and on.  And query planners will continue to plan
some queries very poorly.  And telling users to deal with it sucks for
them.

Nico
--