Re: System username in pg_stat_activity

On Fri, Feb 16, 2024 at 9:45 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2024-02-16 15:22:16 -0500, Tom Lane wrote:
> > Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > > I mean, we could split it into more than one view. But adding a new
> > > view for every new thing we want to show is also not very good from
> > > either a usability or performance perspective. So where would we put
> > > it?
> >
> > It'd have to be a new view with a row per session, showing static
> > (or at least mostly static?) properties of the session.
>
> Yep.
>
>
> > Could we move some existing fields of pg_stat_activity into such a
> > view?
>
> I'd suspect that at least some of
> - leader_pid
> - datid
> - datname
> - usesysid
> - usename
> - backend_start
> - client_addr
> - client_hostname
> - client_port
> - backend_type
>
> could be moved. Whether's worth breaking existing queries, I don't quite know.

I think that's the big question. I think if we move all of those we
will break every single monitoring tool out there for postgres...
That's a pretty hefty price.

> One option would be to not return (some) of them from pg_stat_get_activity(),
> but add them to the view in a way that the planner can elide the reference.

Without having any numbers, I would think that the join to pg_authid
for exapmle is likely more costly than returning all the other fields.
But that one does get eliminated as long as one doesn't query that
column. But if we make more things "joined in from the view", isn't
that likely to just make it more expensive in most cases?

> > I'm not sure that this is worth the trouble TBH. If it can be shown
> > that pulling a few fields out of pg_stat_activity actually does make
> > for a useful speedup, then maybe OK ... but Andres hasn't provided
> > any evidence that there's a measurable issue.
>
> If I thought that the two columns proposed here were all that we wanted to
> add, I'd not be worried. But there have been quite a few other fields
> proposed, e.g. tracking idle/active time on a per-connection granularity.
>
> We even already have a patch to add pg_stat_session
> https://commitfest.postgresql.org/47/3405/

In a way, that's yet another different type of values though -- it
contains accumulated stats. So we really have 3 types -- "info" that's
not really stats (username, etc), "current state" (query, wait events,
state) and "accumulated stats" (counters since start).If we don't want
to combine them all, we should perhaps not combine any and actually
have 3 views?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/