Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uwTB7-002n2f-6T for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 22:20:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uwTB4-003lTw-Jq for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 22:20:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uwTB4-003lTn-6H for pgsql-general@lists.postgresql.org; Wed, 10 Sep 2025 22:20:34 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uwTB0-00052r-1B for pgsql-general@postgresql.org; Wed, 10 Sep 2025 22:20:33 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-6188b5ad4f0so103951a12.0 for ; Wed, 10 Sep 2025 15:20:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757542830; x=1758147630; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=YgbXAAYrYrXfFDKRLDMdPFsS6tTLwpPziji1yHWSKh4=; b=lu65vjHMAnTo/OWCFkbnFxwDyIBZeZOHBZKgzRKIZXU6ti8Bpk19/8lGstnwC5VAqW XePwPVIW+8WqM7OpL5u9tEhuI/OqP5pySUnWtq5EGYfLdDjUw4DPzN237YSkpcV/fOM0 ACr/ibNP3mgUNEDDOSRc0oJCFJ7fv+AyGB5TBj6Nr8ngpfs9bhONa74gjQxogCFvKTo2 PMAgD1wHrpQO663BiSw+fiav7oS1u9YlmXkT8b3u0Hzb3OFvxf3cNhfpK9p+zUkS0l+D Oxjb2x3hP8SukDoClBHxpcdAGR6c2kwEfmpUoybEQMZAoQ3oKSGV+JVKT9x2dRI7JO3J cy5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757542830; x=1758147630; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=YgbXAAYrYrXfFDKRLDMdPFsS6tTLwpPziji1yHWSKh4=; b=kAiM2vNH/v/TSxc6VJCnrHDU+pcLO4n/6OIf+SUfdcHNoMD2xl88jpP30hjTs5TdpR U0r4Zo/sTJGmVK54sbkPVCCfn6mfHLhAOvGdcYlEjwTpxw1qkFcIi9OskBREww4GGFIc r/jobi8eH35vvK3CpqD40dMgTMg+kTil8BR3zTCccdsYEr2ON2Otox4+U0CMfknLHKaN F1NBbkwcp5YVVJN6zGm/f571ospGbuo4VFbdwGAeZ2+QsXouvP3HuFz/i43/fLdZW1hz 4UscEXU6WC1IIcL4DPsUv7pAD/aOKcKvEDikA6Y9jc/COHpVXg5ZEX98YiHUemS4NQZn noxg== X-Forwarded-Encrypted: i=1; AJvYcCXXNA+sXLHjAp47K/Jgv8iU0p8JHtM1rKNYYCymAO+vz6UBT/IzXY7cuk/44lBUuCmQlEUgEyqVIrHim5Ev@postgresql.org X-Gm-Message-State: AOJu0Yx15AimK7IPYqvKj8eRcAxiTvuSSQiz1SMpD7LW/9FnFwTdPcCL HyGd2pj8Cky2gtvDBDDqGWgTqmFpziS7RVuifXqkeTyayq4mEH6GbVGRjh7XFzFvbMPEOQibj35 snL1GttzFICzKM2720yyLTPv5fPajTIo= X-Gm-Gg: ASbGncsnIKtiygE4W8W2Q6+RB5lrFc0Qo/JWyuAB1gB3oR5evwWg9ijtBwhSdQeLt0n Tc6LPgIq4efUbanHxRCrDDANw7n2KplQrerx8Bv6Qu77wRU92QpaQ6MpK7I1Y7lLuk52Rau6/AJ IxEsZxBkQQMA3z/W/St9eb9S/eAwZUHa33rAOvU35HoxP+VMJi28SxUDjKg3Wz8WILARqpto0pD PtiETDvmg== X-Google-Smtp-Source: AGHT+IErNCrBQ49lCUbO8QRGRfeFtzVeniJOQQSOArZFVdJ3p1cPe5fYyrOyumuF6pTtaaWrcIshDzVXGPLDSe7WiRc= X-Received: by 2002:a05:6402:3492:b0:62b:bc39:b22f with SMTP id 4fb4d7f45d1cf-62bbc48fc92mr7406061a12.17.1757542830222; Wed, 10 Sep 2025 15:20:30 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Wed, 10 Sep 2025 18:20:18 -0400 X-Gm-Features: AS18NWAsQJFPXyaFfq1BpKOTYKprv49u4kYW45VRweuNUiMMxG44Me29qZZoZZ8 Message-ID: Subject: Re: MVCC and all that... To: Nico Williams Cc: Merlin Moncure , Ellen Allhatatlan , pgsql-general Content-Type: multipart/alternative; boundary="00000000000012b25e063e79d43a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000012b25e063e79d43a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Sep 10, 2025 at 5:28=E2=80=AFPM Nico Williams wrote: > 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 o= ld > > 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 wit= h > 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 thousand= s > 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 =3D= =3D > 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. > 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. > > "PostgreSQL has a relatively simple, but fast query planning algorithm" > > Compared to what.... What feature is PG missing these days... the onl= y > > 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 .. > ...> ; > > 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. Thank you, Justin --00000000000012b25e063e79d43a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, Sep 10, 2025 at 5:2= 8=E2=80=AFPM Nico Williams <nic= o@cryptonector.com> wrote:
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.=C2=A0 so firebird can do 1000=C2=A0 open s= essions with a
> smaller memory footprint,=C2=A0 still can not have 1000 simultaneous r= unning
> sessions unless we have 1000 CPU's. Where is the win here??=C2=A0 = We should be
> managing resources better on the application side, not opening thousan= ds 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.=C2=A0 Why?=C2=A0 Because less memory p= er thing =3D=3D
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.=C2=A0 This means not
thread-per-client (green or otherwise) or process-per-client.=C2=A0 It mean= s
essentially some flavor of continuation passing style (CPS).=C2=A0 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<= br> matter quite so much because most likely it will be I/O bound rather
than CPU- or memory-bound.


I am not following = you here,=C2=A0 =C2=A0Databases are going to be bound somewhere at some poi= nt, Disk,IO, Network IO, Memory, or CPU bound.=C2=A0 Which one is causing t= he bottle neck just depends on the workload and size of the database.=C2=A0=

The number of idle sessions does not really matter=C2=A0 it is just= wasting=C2=A0resources across the entire application=C2=A0stack.=C2=A0
=

> "PostgreSQL has a relatively simple, but fast query planning algo= rithm"
> Compared to what....=C2=A0 What feature is PG missing these days...=C2= =A0 the only
> thing I know it can't do is change the=C2=A0 plan=C2=A0 in the mid= dle of the
> execution stage.=C2=A0 Which is not a query planner thing but the exec= ution
> layer saying to itself=C2=A0 I am taking too long maybe go back to the= planning
> stage...=C2=A0 Query Hints that have been discussed endlessly.=C2=A0 A= dding hints
> adds its own problems and has become a big mess for databases that sup= port
> it.

I would really like out-of-band hints.=C2=A0 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=C2=A0a 4th=C2=A0 generation language is,=C2=A0 I tell it what I want,= not how to go get=C2=A0what I want.=C2=A0

Thank you,
Justin
--00000000000012b25e063e79d43a--