Mastering pg_stat_activity for real-time monitoring in PostgreSQL®

PostgreSQL is widely adopted in the enterprise world due to its performance, reliability, and transparency. A crucial aspect of managing any production PostgreSQL database is having real-time insight into its ongoing operations. While PostgreSQL offers a comprehensive suite of monitoring views, pg_stat_activity stands out as an indispensable diagnostic tool for every database administrator seeking immediate visibility into database activity.

In this blog, we will delve into:

• What is pg_stat_activity?
• Importance of pg_stat_activity
• Mechanics behind pg_stat_activity
• What data it stores
• How DBAs can leverage it for daily operations

What is pg_stat_activity?

pg_stat_activity is a powerful dynamic system view within PostgreSQL that offers a real-time, granular snapshot of all active backends (i.e., connection/session) interacting with your database instance.

Every row in this view represents a connected process—be it an application connection, administrator session, autovacuum worker, or background replication process. This includes details such as:

• The SQL query or command currently running
• The database the session is connected to
• The database user who established the connection
• The time when the session first connected
• The current operational state (e.g., actively running, idle, waiting for input)
• Whether the session is waiting on something

This real-time visibility is crucial for database administrators to monitor and manage database activity effectively.

Importance of pg_stat_activity

Think of pg_stat_activity not just as a system view, but as your real-time control center for a PostgreSQL database. In production environments, where every second counts, having instant visibility into what your database is doing is critical for maintaining performance, stability, and uptime.

Let’s break down why this view is indispensable:

Performance bottlenecks troubleshooting

The primary use of pg_stat_activity is to quickly diagnose performance problems. If your application or database feels slow, a simple query to this view can reveal the root cause. You can identify:

• Long-running queries: These are the most common culprits behind the performance issues. By looking at the query and query_start columns, you can pinpoint specific queries that are taking too long to execute.
• Blocking sessions: When one session holds a lock that prevents other sessions from proceeding, it can create a cascading bottleneck. pg_stat_activity’s wait_event_type and wait_event columns tell you exactly what a session is waiting on, helping you detect and resolve locking conflicts.
• Idle in transaction sessions: These are sessions that have an open transaction but are not actively doing anything. They can hold locks and consume resources unnecessarily. pg_stat_activity helps you find these sessions by filtering for state = 'idle in transaction'.

Real-time monitoring

Beyond troubleshooting, pg_stat_activity is an invaluable tool for continuous monitoring. By periodically querying this view, you can:

• Analyze workload: Get a live snapshot of the kinds of queries being executed across the system. This helps you understand query patterns, workload peaks, and transaction volumes.
• Auditing and security visibility: By inspecting the usename, client_addr, and application_name, you can see who and what is connected to your database. This is valuable for security audits and for detecting unexpected access patterns.
• Validate maintenance operations: Internal operations like autovacuum, replication, or background workers also appear in pg_stat_activity. This helps confirm that critical background jobs are running as expected.

By providing a live, detailed snapshot of every database session, pg_stat_activity transforms guesswork into actionable insight, making it an indispensable tool for any PostgreSQL administrator.

The mechanics behind pg_stat_activity

Before diving into how to interpret the data from pg_stat_activity, it’s essential to understand how PostgreSQL collects and maintains this information. By exploring the mechanics behind pg_stat_activity, we uncover how PostgreSQL tracks backend processes, manages session states, and updates activity records in real time.

How PostgreSQL tracks activity

PostgreSQL operates as a multi-process architecture, where each client connection is managed by a dedicated backend process. When a client application connects to the PostgreSQL server, the server either forks a new backend process or assigns one from a connection pool. Each backend process is responsible for handling all communication, query execution, and transaction management for its associated client. To facilitate monitoring and diagnostics, PostgreSQL ensures that each backend maintains a detailed internal state, which is exposed through the pg_stat_activity system view. This internal state includes:

• The current SQL query being executed or in wait state
• Backend state (e.g., active, idle, or idle in transaction)
• Wait event, indicating whether the process is waiting for lock, I/O operation or other resources
• Timestamps for when transactions and queries started. Useful for identifying long-running operations

Role of shared memory in activity tracking

PostgreSQL uses shared memory as a high-speed communication hub for its processes. When you connect to the database, the server creates a dedicated backend process for your session. This process, along with every other backend, writes its status and activity metadata—such as the query being run, its current state, and any wait events—into a specific area of shared memory.

This shared memory is defined when the PostgreSQL server starts up and acts as a centralized “bulletin board” for all backend processes. System views, like pg_stat_activity, simply read this live data directly from memory. Because this process avoids disk I/O and complex locking mechanisms, querying pg_stat_activity is extremely fast and has very low overhead, even in a high-concurrency environment.

The architecture ensures that the statistics provided are near real-time, accurate, and safe to query as frequently as you need. This is why database administrators rely on it as a primary tool for live system introspection. In essence, when you run SELECT * FROM pg_stat_activity; you’re getting an instantaneous snapshot of what’s happening because PostgreSQL is simply reading from a shared, in-memory location.

What data does pg_stat_activity store?

The true power of pg_stat_activity lies in the wealth of information it provides for each session. While the view contains numerous columns, a handful are essential for day-to-day monitoring and troubleshooting. Here are the most critical ones you should know:

PostgreSQL provides two columns related to backend process IDs: pid and leader_pid. The pid column identifies the current backend process. The leader_pid column is typically NULL, except during parallel query execution. In such cases, each parallel worker process will have its own pid, and its leader_pid will reference the pid of the main backend process that initiated the parallel query. This allows you to distinguish between parallel workers and their parent query session.

By focusing above set of columns, you can swiftly diagnose common performance issues—such as long-running queries, idle-in-transaction sessions, or blocking conflicts—making you a more effective and proactive PostgreSQL administrator.

How DBAs can leverage pg_stat_activity for daily operations

For PostgreSQL DBAs, pg_stat_activity is more than a diagnostic tool—it’s a daily companion for maintaining database health, performance, and security. Here’s how it can be used effectively in routine operations, with practical examples to illustrate its value.

1. Monitor long-running queries
   Long-running queries can significantly degrade application and database performance. Being able to identify and, if necessary, terminate these queries is essential for maintaining system responsiveness. The following query helps you detect sessions executing queries that have been running for an extended period along with the wait events details:
   
   SELECT pid, usename, now() - pg_stat_activity.query_start AS duration, query , state, wait_event_type, wait_event FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes' ORDER BY duration DESC;

   1 2 3

   SELECT pid, usename, now() - pg_stat_activity.query_start AS duration, query , state, wait_event_type, wait_event FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes' ORDER BY duration DESC;

2. Investigating blocked processes and blocking queries
   The pg_stat_activity view provides visibility into all currently running queries in the database. To identify sessions that are blocking others, the pg_blocking_pids() function serves as a powerful shortcut—it returns the process IDs of the sessions causing the blockage, allowing DBAs to quickly trace and resolve locking conflicts.However, it’s important to note that pg_blocking_pids() can be resource-intensive, especially in environments with high concurrency or large numbers of active sessions. Executing it frequently or without filters in production may impact performance. DBAs should ensure it is used judiciously—ideally within targeted queries or monitoring scripts that minimize overhead.
   
   SELECT activity.pid, activity.usename, activity.query, blocking.pid AS blocking_id, blocking.query AS blocking_query FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid)) ORDER BY activity.query_start;

   1 2 3 4

   SELECT activity.pid, activity.usename, activity.query, blocking.pid AS blocking_id, blocking.query AS blocking_query FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid)) ORDER BY activity.query_start;

3. Track idle-in transaction sessions
   Idle-in-transaction sessions in PostgreSQL can lead to several operational challenges, including unnecessary resource consumption and lock contention. These conditions may degrade overall database performance and compromise system reliability if not addressed promptly.
   
   SELECT pid, usename, client_addr, xact_start, state, query, (now()-state_change) as total_query_time FROM pg_stat_activity WHERE state = 'idle in transaction'

   1 2 3 4

   SELECT pid, usename, client_addr, xact_start, state, query, (now()-state_change) as total_query_time FROM pg_stat_activity WHERE state = 'idle in transaction'

4. Track application behavior
   The pg_stat_activity view enables you to monitor which applications or services are actively connected to your PostgreSQL databases or how many concurrent queries each application is running.
   
   SELECT application_name, COUNT(*) AS active_sessions FROM pg_stat_activity WHERE state = 'active' GROUP BY application_name;

   1 2 3 4

   SELECT application_name, COUNT(*) AS active_sessions FROM pg_stat_activity WHERE state = 'active' GROUP BY application_name;

5. Track session volume and client IPs
   Monitoring session volume by client IP is essential for identifying potential connection overloads or misuse. The following query provides visibility into how many active sessions each client IP and application is using. This can help detect whether a particular IP is opening an unusually high number of connections:
   
   SELECT client_addr, application_name, COUNT(*) AS sessions FROM pg_stat_activity GROUP BY client_addr, application_name ORDER BY sessions DESC;

   1 2 3 4

   SELECT client_addr, application_name, COUNT(*) AS sessions FROM pg_stat_activity GROUP BY client_addr, application_name ORDER BY sessions DESC;

Final thoughts

The pg_stat_activity is far more than just another system view—it serves as a real-time command center for understanding the inner workings of your database. By offering a live snapshot of every active session, it empowers database administrators and developers to:

• Proactively monitor performance
• Detect and resolve query bottlenecks
• Track session behavior and client activity
• Maintain overall system health and stability

Its lightweight design, real-time accuracy, and rich session-level detail make it an indispensable tool for daily operations, troubleshooting, and long-term optimization.

Whether you’re managing a single instance or a high-concurrency production environment, pg_stat_activity should be part of your core observability toolkit.

At NetApp Instaclustr, through the Instaclustr Managed Platform for PostgreSQL, we’ve seamlessly integrated pg_stat_activity into our monitoring framework to collect detailed database-level metrics from customer clusters. This integration provides our users with a robust and transparent monitoring setup, enabling real-time visibility into session activity, query performance, and system health.

If you’re looking to harness the full power of PostgreSQL’s observability tools—without the complexity of managing infrastructure—Instaclustr for PostgreSQL offers a fully managed, production-grade experience. From deployment and scaling to maintenance and high availability, our managed platform handles the operational heavy lifting so you can focus on optimizing performance and building resilient applications.