postgres pg_stat_activity - ghdrako/doc_snipets GitHub Wiki

pg_stat_activity is system view that shows what is currently happening in the DB. pg_stat_activity contains information about all databases. Not only the one that you're connected to.

To see all data you need superuser

This view shows one row for every backend process.

Your session pid:

$ SELECT pg_backend_pid();

$ SELECT pid, backend_type FROM pg_stat_activity ORDER BY backend_type, pid;
  pid   β”‚         backend_type         
────────┼──────────────────────────────
  87715 β”‚ archiver
  87714 β”‚ autovacuum launcher
  87711 β”‚ background writer
  87710 β”‚ checkpointer
 848261 β”‚ client backend
 848405 β”‚ client backend
 848535 β”‚ client backend
 849586 β”‚ client backend
 852004 β”‚ client backend
 853274 β”‚ client backend
  87716 β”‚ logical replication launcher
  87713 β”‚ walwriter

β€œClient backend" is basically your normal connection from application. All other connections are special processes that β€œdo stuff":

  • archiver – runs archive_command (or it's equivalent in newer pgs)
  • autovacuum launcher – starts autovacuum workers when it's necessary to vacuum/analyze something
  • background writer – writes data to tables/indexes in the background
  • checkpointer handles checkpoints – basically every now and then writing all modified data to table/index files
  • logical replication launcher – manages processes related to logical replication
  • walwriter – writes changes in data to WAL

Aside from these you can also see:

  • autovacuum worker – actually does some vacuum/analyze work for autovacuum
  • logical replication worker – works on logical replication
  • parallel worker – special backend started by client backend where query is being processes in parallel
  • startup – applies WAL from somewhere, this is the process that is responsible for recovery and streaming/wal replication
  • walreceiver – receives wal stream from source in streaming replication setups
  • walsender – sends wal via stream to some replica

You can also see some other types if you use specialized extensions.

SELECT * FROM pg_stat_activity WHERE backend_type <> 'client backend';
SELECT * FROM pg_stat_activity WHERE backend_type = 'client backend';

Info

  • usesysid and usename. Just like with database name/id these are the same information about user, which you can see in select usesysid, usename from pg_user.
  • application_name. This is name provided by application
  • client_addr – ip of client machine
  • client_hostname – hostname of the client machine
  • client_port – source port for the connection
  • backend_start – when this backend has started – basically, in case of client backends – when client connected.
  • xact_start – when did the currently active transaction started. It should be understood that even if you didn't do BEGIN – each query on its own is in transaction, so it will be null only if you're not in transaction, and you're currently not working on anything.
  • query_start – when did the last started query start. This query could have already ended. But the query_start is still there – this is the one thing that is often misunderstood.
  • state_change – when was the last time that state of backend changed. This could be, in case of idle backends – when did the last query end – because then state got changed from active to idle.

So, while there are other states, the 3 most commonly seen are:

  • active – backend is actuvely working now on a query (it could be locked out, but it's trying to do it)

  • idle – backend finished all previously given work, and is not doing anything. It's not in transaction, it doesn't hold any locks, it just exists.

  • idle in transaction – bane of my existence since, at least, 2008. This means that backend has started transaction, perhaps there was some queries running in it, but currently it's not doing anything. Idling. But in transaction, which means that all the locks that were acquired during all queries in this transaction are still held and can lock other backend from doing productive work

  • wait_event_type – basically category of events that the backend is currently waiting on. It can be be one of many things, but the most interesting/common ones are:

    • Client – backend is waiting for client to receive data, or send new query, it's not locked out – client has to do something
    • Lock – backend is actually locked on something (by another backend most likely)
    • NULL – backend is not waiting – it's working on something (current query)
    • IO – backend is waiting for disks to do whatever needs to be done (read/write stuff)

  • wait_event – more detailed information about what it could be. For example, in case of wait_event_type = β€˜Lock', you can have 11 distinct wait_events, each of them are different kind of lock.

  • backend_xid – transaction ID of the transaction currently processed by the backend, if it's inside transaction

  • backend_xmin – internal data related to β€œwhich data changes can this backend see"

  • query_id – numerical hash of query, with all parameters removed. Please note that query_id for pids 864116 and 867808 is the same, despite the fact that queries are different – this is because parameters don't matter, so β€˜select 1' can be treated just like β€˜select 2'

The pg_stat_activity is a view in the pg_catalog schema showing information related to the current activity of a process, such as the state, the current query, the start time of the query amongst others.

The pg_stat_activity can be linked with other tables and views such as pg_class for table names, and pg_namespace for schema names.

To get the current running queries exceeding one minute from pg_stat_activity. Your query will look like:

select * from pg_stat_activity p where (now()-p.query_start) > interval '1 minute'