pg_stat_activity is system view that shows what is currently happening in the DB.
With newer pgs it's rather wide (22 columns as I write this). And, it's not always obvious what all this means. So let's try to figure it out.
Connected to database depesz with user test and got:
$ SELECT * FROM pg_stat_activity ; datid │ datname │ pid │ leader_pid │ usesysid │ usename │ application_name │ client_addr │ client_hostname │ client_port │ backend_start │ xact_start │ query_start │ state_change │ wait_event_type │ wait_event │ state │ backend_xid │ backend_xmin │ query_id │ query │ backend_type ────────┼──────────┼────────┼────────────┼──────────┼─────────┼──────────────────┼─────────────┼─────────────────┼─────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼─────────────────────────────┼─────────────────┼────────────┼────────┼─────────────┼──────────────┼─────────────────────┼──────────────────────────────────┼──────────────── [NULL] │ [NULL] │ 87714 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] [NULL] │ [NULL] │ 87716 │ [NULL] │ 10 │ pgdba │ │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] 325115 │ depesz │ 849586 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] 325115 │ depesz │ 848113 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ 16596 │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] 325115 │ depesz │ 848261 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ 16593 │ [NULL] │ <insufficient privilege> │ [NULL] 5 │ postgres │ 848535 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ 16593 │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] 5 │ postgres │ 848405 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ 16593 │ [NULL] │ <insufficient privilege> │ [NULL] 325115 │ depesz │ 848805 │ [NULL] │ 16390 │ test │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:43:41.338899+02 │ 2022-07-04 16:47:16.559599+02 │ 2022-07-04 16:47:16.559599+02 │ 2022-07-04 16:47:16.5596+02 │ [NULL] │ [NULL] │ active │ [NULL] │ 16593 │ -907669364431849768 │ SELECT * FROM pg_stat_activity ; │ client backend [NULL] │ [NULL] │ 87711 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] [NULL] │ [NULL] │ 87715 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] [NULL] │ [NULL] │ 87710 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] [NULL] │ [NULL] │ 87713 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ <insufficient privilege> │ [NULL] (12 ROWS)
First thing that I noticed are the values in query column: <insufficient privilege>. This is because user test is not superuser, and as such can't see what other users are doing. It can, however, see its own queries, which we can see in the data for connection with pid 848805:
$ SELECT * FROM pg_stat_activity WHERE pid = 848805 \gx ─[ RECORD 1 ]────┬─────────────────────────────────────────────────── datid │ 325115 datname │ depesz pid │ 848805 leader_pid │ [NULL] usesysid │ 16390 usename │ test application_name │ psql client_addr │ [NULL] client_hostname │ [NULL] client_port │ -1 backend_start │ 2022-07-04 16:43:41.338899+02 xact_start │ 2022-07-04 16:49:18.495575+02 query_start │ 2022-07-04 16:49:18.495575+02 state_change │ 2022-07-04 16:49:18.495577+02 wait_event_type │ [NULL] wait_event │ [NULL] state │ active backend_xid │ [NULL] backend_xmin │ 16593 query_id │ 8210364456846404087 query │ SELECT * FROM pg_stat_activity WHERE pid = 848805 backend_type │ client backend
After switching to superuser I can see:
$ SELECT * FROM pg_stat_activity; datid │ datname │ pid │ leader_pid │ usesysid │ usename │ application_name │ client_addr │ client_hostname │ client_port │ backend_start │ xact_start │ query_start │ state_change │ wait_event_type │ wait_event │ state │ backend_xid │ backend_xmin │ query_id │ query │ backend_type ────────┼──────────┼────────┼────────────┼──────────┼─────────┼──────────────────┼─────────────┼─────────────────┼─────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼─────────────────┼─────────────────────┼─────────────────────┼─────────────┼──────────────┼─────────────────────┼───────────────────────────────────────────────────────────────────────┼────────────────────────────── [NULL] │ [NULL] │ 87714 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.737082+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ AutoVacuumMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ autovacuum launcher [NULL] │ [NULL] │ 87716 │ [NULL] │ 10 │ pgdba │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.737313+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ LogicalLauncherMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ logical replication launcher 325115 │ depesz │ 849586 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:45:03.983256+02 │ [NULL] │ 2022-07-04 16:45:54.774817+02 │ 2022-07-04 16:45:54.775426+02 │ Client │ ClientRead │ idle │ [NULL] │ [NULL] │ 328096568509443406 │ SELECT n.nspname AS "Name", ↵│ client backend │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner", ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description" ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ FROM pg_catalog.pg_namespace n ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ ORDER BY 1; │ 325115 │ depesz │ 848113 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:42:47.613258+02 │ 2022-07-04 16:43:32.90074+02 │ 2022-07-04 16:43:35.176834+02 │ 2022-07-04 16:43:35.177334+02 │ Client │ ClientRead │ idle IN TRANSACTION │ 16596 │ [NULL] │ -58199854079439753 │ DROP TABLE q; │ client backend 325115 │ depesz │ 848261 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:42:54.562032+02 │ 2022-07-04 16:43:37.768588+02 │ 2022-07-04 16:43:37.768588+02 │ 2022-07-04 16:43:37.76859+02 │ LOCK │ relation │ active │ [NULL] │ 16593 │ [NULL] │ SELECT * FROM q; │ client backend 5 │ postgres │ 848535 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:43:03.427086+02 │ 2022-07-04 16:43:10.942546+02 │ 2022-07-04 16:43:12.494412+02 │ 2022-07-04 16:43:12.497506+02 │ Client │ ClientRead │ idle IN TRANSACTION │ 16593 │ [NULL] │ 389562163604003053 │ DROP TABLE z; │ client backend 5 │ postgres │ 848405 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:42:59.671318+02 │ 2022-07-04 16:43:15.250683+02 │ 2022-07-04 16:43:15.250683+02 │ 2022-07-04 16:43:15.250685+02 │ LOCK │ relation │ active │ [NULL] │ 16593 │ [NULL] │ SELECT * FROM z; │ client backend 325115 │ depesz │ 852004 │ [NULL] │ 10 │ pgdba │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:50:25.426408+02 │ 2022-07-04 16:50:45.614384+02 │ 2022-07-04 16:50:45.614384+02 │ 2022-07-04 16:50:45.614385+02 │ [NULL] │ [NULL] │ active │ [NULL] │ 16593 │ -907669364431849768 │ SELECT * FROM pg_stat_activity │ client backend [NULL] │ [NULL] │ 87711 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.731409+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ BgWriterHibernate │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ background writer [NULL] │ [NULL] │ 87715 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.737179+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ ArchiverMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ archiver [NULL] │ [NULL] │ 87710 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.731313+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ CheckpointerMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ checkpointer [NULL] │ [NULL] │ 87713 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.736982+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ WalWriterMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ walwriter (12 ROWS)
Nice. All the columns. But to they really mean?
First of all – this view shows one row for every backend process. Most of these, in normal systems (this is just test db on my desktop) will be connections from applications. But not all.
Each process has it's PID (process number), and you can always check your own PID from within DB connection using:
$ SELECT pg_backend_pid(); pg_backend_pid ──────────────── 852004 (1 ROW)
This is the same PID that is visible to the system, for example, when using ps command:
=$ ps uw -p 852004 USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND pgdba 852004 0.0 0.0 216984 19516 ? Ss 16:50 0:00 postgres: pgdba depesz [LOCAL] idle
So, let's dig deeper. We'll start … from the end. Last column is backend_type:
$ 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 (12 ROWS)
“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.
Knowing this, let's look back at our data, another subset of it:
$ SELECT * FROM pg_stat_activity WHERE backend_type <> 'client backend'; datid │ datname │ pid │ leader_pid │ usesysid │ usename │ application_name │ client_addr │ client_hostname │ client_port │ backend_start │ xact_start │ query_start │ state_change │ wait_event_type │ wait_event │ state │ backend_xid │ backend_xmin │ query_id │ query │ backend_type ────────┼─────────┼───────┼────────────┼──────────┼─────────┼──────────────────┼─────────────┼─────────────────┼─────────────┼───────────────────────────────┼────────────┼─────────────┼──────────────┼─────────────────┼─────────────────────┼────────┼─────────────┼──────────────┼──────────┼───────┼────────────────────────────── [NULL] │ [NULL] │ 87714 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.737082+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ AutoVacuumMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ autovacuum launcher [NULL] │ [NULL] │ 87716 │ [NULL] │ 10 │ pgdba │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.737313+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ LogicalLauncherMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ logical replication launcher [NULL] │ [NULL] │ 87711 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.731409+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ BgWriterHibernate │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ background writer [NULL] │ [NULL] │ 87715 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.737179+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ ArchiverMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ archiver [NULL] │ [NULL] │ 87710 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.731313+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ CheckpointerMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ checkpointer [NULL] │ [NULL] │ 87713 │ [NULL] │ [NULL] │ [NULL] │ │ [NULL] │ [NULL] │ [NULL] │ 2022-07-01 10:55:08.736982+02 │ [NULL] │ [NULL] │ [NULL] │ Activity │ WalWriterMain │ [NULL] │ [NULL] │ [NULL] │ [NULL] │ │ walwriter (6 ROWS)
Please note that most of the columns are null, because – well, these are processes that are not connected to any specific database, nor they use any specific user (aside from logical replication launcher). Usually you don't really care about them.
For other backends (in my case, only normal client backend):
$ SELECT * FROM pg_stat_activity WHERE backend_type = 'client backend'; datid │ datname │ pid │ leader_pid │ usesysid │ usename │ application_name │ client_addr │ client_hostname │ client_port │ backend_start │ xact_start │ query_start │ state_change │ wait_event_type │ wait_event │ state │ backend_xid │ backend_xmin │ query_id │ query │ backend_type ────────┼──────────┼────────┼────────────┼──────────┼─────────┼──────────────────┼─────────────┼─────────────────┼─────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼─────────────────┼────────────┼─────────────────────┼─────────────┼──────────────┼──────────────────────┼───────────────────────────────────────────────────────────────────────┼──────────────── 5 │ postgres │ 848405 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:42:59.671318+02 │ 2022-07-04 16:43:15.250683+02 │ 2022-07-04 16:43:15.250683+02 │ 2022-07-04 16:43:15.250685+02 │ LOCK │ relation │ active │ [NULL] │ 16593 │ [NULL] │ SELECT * FROM z; │ client backend 5 │ postgres │ 848535 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:43:03.427086+02 │ 2022-07-04 16:43:10.942546+02 │ 2022-07-04 16:43:12.494412+02 │ 2022-07-04 16:43:12.497506+02 │ Client │ ClientRead │ idle IN TRANSACTION │ 16593 │ [NULL] │ 389562163604003053 │ DROP TABLE z; │ client backend 325115 │ depesz │ 848261 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:42:54.562032+02 │ 2022-07-04 16:53:24.693972+02 │ 2022-07-04 16:53:24.693972+02 │ 2022-07-04 16:53:24.693974+02 │ LOCK │ relation │ active │ [NULL] │ 16593 │ [NULL] │ SELECT * FROM q; │ client backend 325115 │ depesz │ 853274 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:53:19.46231+02 │ 2022-07-04 16:53:21.765739+02 │ 2022-07-04 16:53:23.106893+02 │ 2022-07-04 16:53:23.107714+02 │ Client │ ClientRead │ idle IN TRANSACTION │ 16597 │ [NULL] │ -58199854079439753 │ DROP TABLE q; │ client backend 325115 │ depesz │ 864116 │ [NULL] │ 16384 │ depesz │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 17:10:43.001571+02 │ [NULL] │ 2022-07-04 17:10:44.682067+02 │ 2022-07-04 17:10:44.682396+02 │ Client │ ClientRead │ idle │ [NULL] │ [NULL] │ -2698492627503961632 │ SELECT 1; │ client backend 325115 │ depesz │ 852004 │ [NULL] │ 10 │ pgdba │ psql │ [NULL] │ [NULL] │ -1 │ 2022-07-04 16:50:25.426408+02 │ 2022-07-04 17:10:54.757781+02 │ 2022-07-04 17:10:54.757781+02 │ 2022-07-04 17:10:54.757783+02 │ [NULL] │ [NULL] │ active │ [NULL] │ 16593 │ -2404663320170207359 │ SELECT * FROM pg_stat_activity WHERE backend_type = 'client backend' │ client backend (6 ROWS)
datid and datname are basically just information which database this backend is connected to. You can see that these values are visible as oid and datname in pg_database table:
$ SELECT oid, datname FROM pg_database WHERE datname IN ('postgres', 'depesz'); oid │ datname ────────┼────────── 5 │ postgres 325115 │ depesz (2 ROWS)
This shows us one important thing – pg_stat_activity contains information about all databases. Not only the one that you're connected to. This can be sometimes problematic, if you're looking for source of problem in some DB, and you keep seeing connections to other databases clouding the information.
Because of this you might want to add where datname = current_database() to your query.
Next is pid, which I explained, and then there is leader_pid. Which will be usually NULL. It is not null if you have parallelized query, in which case parallel worker backends will have, in their leader_pid column pid of backend that started them (the real, original db connection), like in here:
pid │ leader_pid │ backend_type │ query ────────┼────────────┼─────────────────┼─────────────────────────────────────────── 867808 │ [NULL] │ client backend │ SELECT SUM(LENGTH(p::text)) FROM plans p; 868383 │ 867808 │ parallel worker │ SELECT SUM(LENGTH(p::text)) FROM plans p; 868384 │ 867808 │ parallel worker │ SELECT SUM(LENGTH(p::text)) FROM plans p; (3 ROWS)
This shows that connection with pid 867808 started a query, and Pg decided to start two additional workers to calculate it faster. As soon as this query will end, these worker backend will disappear.
Next in pg_stat_activity we have user information: 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.
Next thing is application_name. This is name provided by application, and you can always change it:
$ SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid(); application_name ────────────────── psql (1 ROW) $ SET application_name = 'magic'; SET $ SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid(); application_name ────────────────── magic (1 ROW)
In most languages, I would assume, you can set application name using database connection parameters, or, if your application uses standard libpq, you can use environment variable PGAPPNAME:
=$ psql -c 'select application_name from pg_stat_activity where pid = pg_backend_pid()' application_name ------------------ psql (1 ROW) =$ PGAPPNAME=testing psql -c 'select application_name from pg_stat_activity where pid = pg_backend_pid()' application_name ------------------ testing (1 ROW)
This thing (application_name) is absolutely amazing when it comes to debugging problems. If you're not using it – start.
Next thing in pg_stat_activity are 3 columns which tell us where the user connected from:
- client_addr – ip of client machine
- client_hostname – hostname of the client machine
- client_port – source port for the connection
The thing is that PostgreSQL can be connected using two ways:
- TCP/IP connection – basicaly over network, from another server, or, sometimes, from the same machine using localhost as server
- UNIX sockets – special magical “files" that can be used to connect, only locally. Default when running psql or other CLI apps.
In case of UNIX sockets – client_addr and client_hostname are NULL, and port is set to -1, but if I'd connect using TCP/IP:
=$ psql -h localhost -c 'select client_addr, client_hostname, client_port from pg_stat_activity where pid = pg_backend_pid()' client_addr │ client_hostname │ client_port ─────────────┼─────────────────┼───────────── 127.0.0.1 │ [NULL] │ 43574 (1 ROW)
client_hostname is null because to have this visible, i'd have to turn log_hostname config variable, which I don't do, as it's not necessary (for me).
Next in line are 4 columns, one of which is the single most commonly misunderstood:
- 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.
The problem with query_start is that people seem to assume that now() – query_start means: how long does the query ran (so far). And it kinda does, if the backend is currently working on something (state = ‘active'). But it coule be idle, and then this calculation has no meaning, because it's not working on anything right now. Let's see example:
$ SELECT pid, now() - query_start AS duration, query_start, state_change, state, query FROM pg_stat_activity WHERE backend_type = 'client backend'; pid │ duration │ query_start │ state_change │ state │ query ────────┼─────────────────┼───────────────────────────────┼───────────────────────────────┼─────────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── 864116 │ 00:26:46.690661 │ 2022-07-04 17:10:44.682067+02 │ 2022-07-04 17:10:44.682396+02 │ idle │ SELECT 1; 853274 │ 00:44:08.265835 │ 2022-07-04 16:53:23.106893+02 │ 2022-07-04 16:53:23.107714+02 │ idle IN TRANSACTION │ DROP TABLE q; 848261 │ 00:44:06.678756 │ 2022-07-04 16:53:24.693972+02 │ 2022-07-04 16:53:24.693974+02 │ active │ SELECT * FROM q; 848535 │ 00:54:18.878316 │ 2022-07-04 16:43:12.494412+02 │ 2022-07-04 16:43:12.497506+02 │ idle IN TRANSACTION │ DROP TABLE z; 848405 │ 00:54:16.122045 │ 2022-07-04 16:43:15.250683+02 │ 2022-07-04 16:43:15.250685+02 │ active │ SELECT * FROM z; 868019 │ 00:00:00.709748 │ 2022-07-04 17:37:30.66298+02 │ 2022-07-04 17:37:30.663767+02 │ idle │ SELECT pid, leader_pid, backend_type, query FROM pg_stat_activity WHERE datname = 'depesz_explain' AND pid <> pg_backend_pid() 852004 │ 00:00:00 │ 2022-07-04 17:37:31.372728+02 │ 2022-07-04 17:37:31.372729+02 │ active │ SELECT pid, now() - query_start AS duration, query_start, state_change, state, query FROM pg_stat_activity WHERE backend_type = 'client backend'; 867808 │ 00:18:16.976686 │ 2022-07-04 17:19:14.396042+02 │ 2022-07-04 17:19:37.187507+02 │ idle │ SELECT SUM(LENGTH(p::text)) FROM plans p; (8 ROWS)
please note that there are only 3 backends that are doing something (pids 848261, 848405, 852004). For them, the duration column makes sense. For all other – it doesn't.
So why is there thing in ‘query' column? Answer is simple – this is the last processed query in this backend. It could be current (in case of state = ‘active'), but for idle/idle-in-transaction – this is simply the last query that started at query_start and finished at state_change.
This gotcha is reported as “long running trivial query, like ‘select 1' or ‘commit', so often, that it's the base reason why I wrote this blogpost.
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
This leaves us with the middle five columns left to explain:
pid │ wait_event_type │ wait_event │ backend_xid │ backend_xmin │ query_id │ query ────────┼─────────────────┼────────────┼─────────────┼──────────────┼──────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── 864116 │ Client │ ClientRead │ [NULL] │ [NULL] │ -2698492627503961632 │ SELECT 1; 853274 │ Client │ ClientRead │ 16597 │ [NULL] │ -58199854079439753 │ DROP TABLE q; 848261 │ LOCK │ relation │ [NULL] │ 16593 │ [NULL] │ SELECT * FROM q; 848535 │ Client │ ClientRead │ 16593 │ [NULL] │ 389562163604003053 │ DROP TABLE z; 848405 │ LOCK │ relation │ [NULL] │ 16593 │ [NULL] │ SELECT * FROM z; 868019 │ Client │ ClientRead │ [NULL] │ [NULL] │ 1114137780797701598 │ SELECT pid, leader_pid, backend_type, query FROM pg_stat_activity WHERE datname = 'depesz_explain' AND pid <> pg_backend_pid() 852004 │ [NULL] │ [NULL] │ [NULL] │ 16593 │ 7635051274713358230 │ SELECT pid, wait_event_type, wait_event, backend_xid, backend_xmin, query_id, query FROM pg_stat_activity WHERE backend_type = 'client backend'; 867808 │ Client │ ClientRead │ [NULL] │ [NULL] │ -2698492627503961632 │ SELECT 2; (8 ROWS)
The meaning is:
- 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'
I hope that this will help clear the confusion about reading pg_stat_activity and understanding query_start as unconditional start of currently processed query.
Thanks for this helpful post. From my testing, it seems that ‘now() – query_start’ for a process in the ‘idle in transaction’ state shows the duration of time that the beginning of the transaction began. The query_start time matches the state_change time for ‘idle in transaction’ processes. Is that correct a correct conclusion?
It’s incorrect.
Consider case: start backend. start transaction, and issue, in it: create table depesz_test(); don’t close transaction. wait a minute. issue: select pg_backend_pid(); and leave the transaction open.
In the mean time start new connection, and check data about the backend with pid you just saw.
query_start will be start of processing of “select pg_backend_pid()”. State_change will be – when select pg_backend_pid() has ended. and start of transaction will be in xact_start.
state_change and query_start will be similar if the last query ran in transaction was short (like select pg_backend_pid()) – but if the query was long, it will be significantly different. You can see it by running select pg_sleep(10); and checking pg_stat_activity ~ 15 seconds later.
“archiver – runs archive_command (or it’s equivalent in newer pgs)”
Has archive_command been replaced by something? I don’t see that mentioned in current docs.
@Don Seiler:
https://www.depesz.com/2022/02/06/waiting-for-postgresql-15-allow-archiving-via-loadable-modules/