# Mastering pg_stat_activity for real-time monitoring in PostgreSQL®

[Blog](/blog/)>[Technology](/blog/category/technical/)>Mastering pg\_stat\_activity for real-time monitoring in PostgreSQL® 

Mastering pg\_stat\_activity for real-time monitoring in PostgreSQL®
====================================================================

October 07, 2025 | By [ Vikas Kumar](https://www.instaclustr.com/blog/author/vkask1/)

 

 

 

 



   [ ](https://x.com/intent/tweet?text=Mastering%20pg_stat_activity%20for%20real-time%20monitoring%20in%20PostgreSQL%C2%AE&url=https://www.instaclustr.com/blog/mastering-pg-stat-activity-for-real-time-monitoring-in-postgresql/) [ ](https://www.linkedin.com/shareArticle?mini=true&url=https://www.instaclustr.com/blog/mastering-pg-stat-activity-for-real-time-monitoring-in-postgresql/&title=&summary=Mastering%20pg_stat_activity%20for%20real-time%20monitoring%20in%20PostgreSQL%C2%AE&source=) 

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:

**Column****Description**`datname`Name of database the session is connected to`pid`The process ID of the backend process. This is a unique identifier and very useful for correlating database activities with system-level metrics or for forcefully terminating a session using `pg_terminate_backend()``usename`Name of user who established the connection`client_addr`The IP address of the client connected to the database`application_name`Name of the application that initiated the connection`backend_start`Timestamp when the backend process started`state`Current state of the backend: active, idle, idle in transaction etc.`query`The text of the query that the session is currently executing`query_start`Timestamp when the current query began execution. Very helpful in investigating long running queries`wait_event_type`Type of event the backend is waiting on (e.g., Lock, IO, Client, IPC)`wait_event`Specific event name (e.g., LWLock, BufferPin, ClientRead)`backend_type`Type of backend process (e.g., autovacuum worker, client backend, background writer, walsender)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](https://www.instaclustr.com/platform/managed-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](https://www.instaclustr.com/platform/managed-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.

 

 [ Add Instaclustr as a preferred source on Google ](https://google.com/preferences/source?q=instaclustr.com)



 

 ![mail icon]()#### Get the latest articles for open sourceIn your inbox

 <a class="btn btn-primary btn-popup text-dark" href="">Sign up now</a> 

 

 

 

  ### Related content

 [ Complete guide to PostgreSQL: Features, use cases, and tutorial 

 

 PostgreSQL is a reliable open source relational database supporting SQL and JSON queries. Its adherence to standards and ... 

 

 

 

 

 

 

 ](https://www.instaclustr.com/education/postgresql/complete-guide-to-postgresql-features-use-cases-and-tutorial/) 

 [ Top 15 PostgreSQL® best practices for 2026 

 

 Postgres, or PostgreSQL, is an open-source relational database management system known for its scalability... 

 

 

 

 

 

 

 ](https://www.instaclustr.com/education/postgresql/top-10-postgresql-best-practices-for-2025/) 

 [ Lessons Learned in PostgreSQL® 

 

 Perry Clark recounts the story of a client’s PostgreSQL® database hijacked with ransomware in this thought leadership white paper... 

 

 

 

 

 

 

 ](https://www.instaclustr.com/resources/lessons-learned-in-postgresql/) 

 

  <a class="close-modal" href="">×</a>Sign upto ourNewsletter
-----------------------