Re: MVCC and all that...

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