MIME-Version: 1.0
References: <Z-NLelZdH3GaLRNK@paquier.xyz> <70BA294B-5A33-4F47-A637-8011C1F279CB@gmail.com>
 <Z-OdsRQUmwtSa_4l@paquier.xyz> <CAFh8B==vog16UGpigF5jukg0yZW+MHsvvKG0QBs7gV6cHgqanA@mail.gmail.com>
 <aHbmsdI1k12yIFB2@paquier.xyz> <CAFh8B=m5BPeo7vTYDSw2KV=CmjeATWGw-CHCZmJ+3ZKm7kVM1w@mail.gmail.com>
In-Reply-To: <CAFh8B=m5BPeo7vTYDSw2KV=CmjeATWGw-CHCZmJ+3ZKm7kVM1w@mail.gmail.com>
From: Lukas Fittl <lukas@fittl.com>
Date: Wed, 16 Jul 2025 22:06:04 -0700
Message-ID: <CAP53PkyHVm6boP5=PahN1Xq7+D+aUZy+zg4QhXXpTCecsJnm6w@mail.gmail.com>
Subject: Re: query_id: jumble names of temp tables for better
 pg_stat_statement UX
To: Alexander Kukushkin <cyberdemn@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Sami Imseih <samimseih@gmail.com>, 
	Tom Lane <tgl@sss.pgh.pa.us>, Christoph Berg <myon@debian.org>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, ma lz <ma100@hotmail.com>
Content-Type: multipart/alternative; boundary="000000000000884590063a18f909"
Archived-At: <https://www.postgresql.org/message-id/CAP53PkyHVm6boP5%3DPahN1Xq7%2BD%2BaUZy%2Bzg4QhXXpTCecsJnm6w%40mail.gmail.com>
Precedence: bulk

--000000000000884590063a18f909
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Jul 15, 2025 at 11:20=E2=80=AFPM Alexander Kukushkin <cyberdemn@gma=
il.com>
wrote:

> However, we regularly hear from many different customers that they *don't
> control queries* sent by application or *can't modify these queries*.
> Such kinds of workloads are also not that uncommon and this change makes
> it impossible to monitor them.
>

For the workloads you are thinking of, are these "one customer per schema"
multi-tenant workloads, or something else?

I mentioned this earlier in the discussion (when supporting the change that
was done), but the main challenge I've seen is that for "one customer per
schema" workloads, pg_stat_statements just doesn't work today, unless you
have only a handful of customers on a server.

Once you have anything close to 100 or more customer schemas on a server,
the churn on the entries makes pg_stat_statements unusable (even with a
high max), especially with the current way the query text file works, since
you can't reliably read from pg_stat_statements anymore without incurring a
read from a 100MB+ query text file.

So I agree this change reduces the visibility into which of the schemas had
a slow query, but it at least allows reliably using pg_stat_statements to
narrow down which query / part of an application is problematic. To get
specifics on the schema, one could then use other means (e.g.
log_min_duration_statement, auto_explain, etc) to get exact details,
grepping the logfile for the query ID retrieved from pg_stat_statements.

Thanks,
Lukas

--=20
Lukas Fittl

--000000000000884590063a18f909
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jul 15, 2025 at =
11:20=E2=80=AFPM Alexander Kukushkin &lt;<a href=3D"mailto:cyberdemn@gmail.=
com" target=3D"_blank">cyberdemn@gmail.com</a>&gt; wrote:</div><div class=
=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div class=3D"gmail_quote"><div>However, we regularly hear from ma=
ny different=C2=A0customers that they *don&#39;t control queries* sent by a=
pplication or *can&#39;t modify these queries*.</div></div><div><span class=
=3D"gmail_signature_prefix">Such kinds of workloads are also not that uncom=
mon and this change makes it impossible to monitor them.</span></div></div>=
</blockquote><div><br></div><div>For the workloads you are thinking of, are=
 these &quot;one customer per schema&quot; multi-tenant workloads, or somet=
hing else?</div><div><br></div><div>I mentioned this earlier in the discuss=
ion (when supporting the change that was done), but the main challenge I=
9;ve seen is that for &quot;one customer per schema&quot; workloads, pg_sta=
t_statements just doesn&#39;t work today, unless you have only a handful of=
 customers on a server.</div><div><br></div><div>Once you have anything clo=
se to 100 or more customer schemas on a server, the churn on the entries ma=
kes pg_stat_statements unusable (even with a high max), especially with the=
 current way the query text file works, since you can&#39;t reliably read f=
rom pg_stat_statements anymore without incurring a read from a 100MB+ query=
 text file.</div><div><br></div><div>So I agree this change reduces the vis=
ibility into which of the schemas had a slow query, but it at least allows =
reliably using pg_stat_statements to narrow down which query / part of an a=
pplication is problematic. To get specifics on the schema, one could then u=
se other means (e.g. log_min_duration_statement, auto_explain, etc) to get =
exact details, grepping the logfile for the query ID retrieved from pg_stat=
_statements.</div><div><br></div><div>Thanks,</div><div>Lukas</div><div>=C2=
=A0</div></div><span class=3D"gmail_signature_prefix">-- </span><br><div di=
r=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr">=
<div><div dir=3D"ltr">Lukas Fittl</div></div></div></div></div></div></div>
</div>

--000000000000884590063a18f909--