Re: Addition of authenticated ID to pg_stat_activity

On Tue, Apr 27, 2021 at 8:25 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2021-04-27 12:40:29 -0400, Stephen Frost wrote:
> > So, what fields are people really looking at when querying
> > pg_stat_activity interactively? User, database, pid, last query,
> > transaction start, query start, state, wait event info, maybe backend
> > xmin/xid? I doubt most people looking at pg_stat_activity interactively
> > actually care about the non-user backends (autovacuum, et al).
>
> Not representative, but I personally am about as often interested in one
> of the non-connection processes as the connection
> ones. E.g. investigating what is autovacuum's bottleneck, are
> checkpointer / wal writer / bgwriter io bound or keeping up, etc.

I definitely use it all the time to monitor autovacuum all the time.
The others as well regularly, but autovacuum continuously. I also see
a lot of people doing things like "from pg_stat_activity where query
like '%mytablename%'" where they'd want both any regular queries and
any autovacuums currently processing the table.

I'd say client address is also pretty common to identify which set of
app servers connections are coming in from -- but client port and
client hostname are a lot less interesting. But it'd be kind of weird
to split those out.

For *interactive use* I'd find pretty much all other columns
interesting and commonly used. Probably not that interested in the
oids of the database and user, but again they are the cheap ones. We
could get rid of the joints if we only showed the oids, but in
interactive use it's really the names that are interesting. But if
we're just trying to save column count, I'd say get rid of datid and
usesysid.

I'd hold everything else as interesting.

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