While working with a PostgreSQL database, it is often necessary to view the running queries on the server. This information can assist us in performance monitoring, troubleshooting, resource management, query analysis, and capacity planning. For this purpose, Postgres provides a “pg_stat_activity” view that enables users to gain valuable insights into the database's current activity and optimize its performance.

This post illustrates how to get the list of active/running queries in PostgreSQL.

How to Get a List of Active/Running Queries in Postgres?

PostgreSQL provides a system view named pg_stat_activity that retrieves information about the currently running queries and active sessions on the database server. A detailed description of the pg_stat_activity view is listed below:

• datid: It displays the OID of the database with which the session is associated.
• datname: This column shows the name of the database.
• pid: An integer-type column that shows the process ID of the session.
• usename: It represents the user name.
• client_addr: It retrieves the IP address information.
• application_name: This column retrieves the application/client name.
• query: this column shows the information about the currently executing query/command.
• state: Shows the session’s current state, like "active", "idle", etc.).
• wait_event_type and wait_event: these are Text-type columns that show the type of event a session is waiting for and the name of the event, respectively.

Now, head into SQL Shell, provide login privileges to connect to the Postgres database, and execute the following query to fetch the list of currently running/active queries:

SELECT pid AS process_id, 
query AS active_query
FROM pg_stat_activity
WHERE state = 'active';

The following snippet shows the currently active query:

To get a list of all queries, execute the following command:

SELECT pid AS process_id, 
query AS all_query
FROM pg_stat_activity;

The following snippet depicts the list of all queries:

That’s all about getting the list of active queries in Postgres.

Conclusion

PostgreSQL provides a system view named pg_stat_activity that retrieves information about the currently running queries and active sessions on the database server. It returns a list of currently active queries in Postgres, which help us monitor and analyze the workload and performance of a database. This write has illustrated a step-by-step method to fetch the list of running/active queries.