The pg_stat_activity view in PostgreSQL provides information about the current activity of each server process. The view returns one row for each server process, showing the current state of that process.
The following table describes the columns of pg_stat_activity view in PostgreSQL:
| Column Name | Description |
|---|---|
| datid | OID of the database that this backend is connected to |
| datname | Name of the database that this backend is connected to |
| pid | Process ID of this backend |
| usesysid | OID of the user that owns this backend |
| usename | Name of the user that owns this backend |
| application_name | Name of the application that is connected to this backend |
| client_addr | IP address of the client connected to this backend |
| client_hostname | Host name of the client connected to this backend |
| client_port | TCP port number that the client is using for communication with this backend |
| backend_start | Time when this process was started, i.e., when the client connected to the database |
| xact_start | Time when this process started the current transaction, if any |
| query_start | Time when the currently executing query was started, if any |
| state_change | Time when the state of this process last changed |
| waiting | Whether this process is currently waiting for a lock |
| state | Current state of this backend |
| backend_xid | Transaction ID of the current transaction, if any |
| backend_xmin | Minimal transaction ID that this backend will accept for queries |
| query | Text of this backend’s most recent query, if any |
The pg_stat_activity view can be used for troubleshooting PostgreSQL performance by identifying long-running queries, tracking connections and their associated states, and checking for blocked queries. For example, the view can be used to find queries that have been running for a long time, queries that are waiting for locks, and queries that are blocking other queries.