On Thu, Apr 29, 2021 at 04:56:42PM +0200, Magnus Hagander wrote: > 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.
When it comes to development work, I also look at things different than backend connections, checkpointer and WAL writer included. > 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. Yes, I agree that it would be confusing to split the client_* fields across multiple views. > 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. Yes, you have an argument here about the removal of usesysid and datid. Now I find joins involving OIDs to be much more natural than the object names, because that's the base of what we use in the catalogs. Not sure if we would be able to agree on something here, but the barrier to what a session and a connection hold is thin when it comes to roles and application_name. Thinking more about that, I would be really tempted to get to do a more straight split with data that's associated to a session, to a transaction and to a connection, say: 1) pg_stat_session, data that may change. - PID - leader PID - the role name - role ID - application_name - wait_event_type - wait_event 2) pg_stat_connection, static data associated to a connection. - PID - database name - database OID - client_addr - client_hostname - client_port - backend_start - authn ID - backend_type 3) pg_stat_transaction, or pg_stat_activity, for the transactional activity. - PID - xact_start - query_start - backend_xid - state_change - query string - query ID - state Or I could just drop a new function that fetches the authn ID for a given PID, even if this makes things potentially less consistent when it comes to the lookup of PgBackendStatus, guarantee given now by pg_stat_get_activity(). -- Michael
signature.asc
Description: PGP signature
