PostgreSQL pg_stat_activity and Session Control

Monitoring and Operations / Guide 38 of 47 / Intermediate

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Host2 core / 4 GB, 400,000-row test table
• TimeAbout 17 min

io worker appears in backend_type from PostgreSQL 18, which introduced asynchronous I/O. On 17 and earlier you will see three fewer background processes here.

This guide includes

Use this when a server is busy and you need to know what it is doing. This matters because pg_stat_activity quietly hides most of it from a non-superuser - and cancelling a query is not the same thing as terminating a session.

• looking at every session on the server, one row per backend
• finding out why most of it came back blank
• watching a query while it runs, then cancelling it
• spotting a transaction still holding on after its work is finished
• terminating it, and seeing exactly how that differs from a cancel

Before you start

• A running cluster with the appdb database.
• Shell access as a user who can sudo -u postgres.

1. 1

   Look at every session on the server

2. 2

   Find out why most of it was blank

3. 3

   Watch a query while it runs

4. 4

   Cancel it

5. 5

   Spot a transaction holding on

6. 6

   Terminate it, and see the difference

Official sources

• PostgreSQL 18 Documentation - The Statistics Collector
• PostgreSQL 18 Documentation - Viewing Locks
• PostgreSQL 18 Documentation - Explicit Locking
• PostgreSQL 18 Documentation - Error Reporting and Logging
• PostgreSQL 18 Documentation - Connections and Authentication
• PgBouncer Documentation