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