After speaking at DevLille (▶️) about PostgreSQL troubleshooting, I realized many developers don't know about the powerful diagnostic tools hidden within PostgreSQL itself. Today, let's explore pg_stat_activity - your best friend when things go wrong.

What is pg_stat_activity?

pg_stat_activity is a system view that provides a real-time snapshot of all database processes. Think of it as your database's task manager - showing you exactly what's happening right now.

Why Should You Care?

When your application slows down or users complain about timeouts, pg_stat_activity helps you quickly identify:

  • Hanging queries that are stuck
  • Blocked processes waiting for locks
  • Suspicious connections from unknown sources
  • Long-running queries consuming resources
  • Idle transactions holding locks unnecessarily

Basic Usage

The simplest query to see what's happening:

SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
WHERE state <> 'idle';

This shows all active processes, excluding idle connections.

Advanced Troubleshooting Queries

1. Find Long-Running Queries

SELECT pid
     , usename
     , state
     , query
     , now() - query_start AS duration
FROM pg_stat_activity
WHERE state <> 'idle' AND query_start < now() - '5 minutes'::interval
ORDER BY duration DESC;

2. Identify Blocked Queries

SELECT pid
     , usename
     , query
     , pg_blocking_pids(pid) AS blocking_pids
     , now() - query_start   AS waiting_since
FROM pg_stat_activity
WHERE array_length(pg_blocking_pids(pid), 1) IS NOT NULL
ORDER BY waiting_since DESC;

3. Monitor Connection Count by Application

SELECT application_name
     , count(*)           AS connection_count
     , max(backend_start) AS newest_connection
     , min(backend_start) AS oldest_connection
FROM pg_stat_activity
GROUP BY application_name
ORDER BY connection_count DESC;

Make sure to have a safe margin from max_connections:

SELECT count(*)                                AS current_connections
     , current_setting('max_connections')::int AS max_connections
FROM pg_stat_activity;

4. Find Idle in Transaction Sessions

These are particularly dangerous as they can hold locks for extended periods:

SELECT pid
     , usename
     , application_name
     , client_addr
     , now() - xact_start AS transaction_duration
     , query
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND xact_start < now() - '10 minutes'::interval
ORDER BY transaction_duration DESC;

Key Columns to Watch

  • pid: Process ID (use this to kill queries)
  • usename: Database user
  • datname: Database name
  • application_name: Declarative name from the application
  • state: Current state (active, idle, idle in transaction)
  • query_start: When the current query started
  • xact_start: When the current transaction started
  • backend_start: When this connection was established
  • state_change: When the state last changed
  • wait_event_type/wait_event: What the process is waiting for

Pro Tips

  1. Create monitoring views: Save your favorite queries as views for quick access
  2. Set up alerts: Use these queries in your monitoring system
  3. Regular checks: Make it a habit to check during deployments, and even more during incidents
  4. Combine with pg_locks: For deeper lock analysis
  5. Use query_id: In PostgreSQL 14+, track specific query patterns

Common Pitfalls to Avoid

  • Don't just look at active queries, idle in transaction can be worse
  • Remember that pg_stat_activity shows a snapshot, run regularly for patterns
  • Check wait_events to understand what's blocking progress
  • Consider application_name to identify problematic services (set it correctly in your app!)

When to Escalate

If you see:

  • Queries running for hours
  • Many blocked processes
  • Connection count approaching max_connections
  • Suspicious queries from unknown sources

It's time to take action or call for help.

Next Steps

While pg_stat_activity is powerful for reactive troubleshooting, you shouldn't wait for problems to occur. In the next post, we'll explore how to safely terminate problematic queries using pg_cancel_backend and pg_terminate_backend.

For proactive monitoring, consider tools like Azimutt Inspector that continuously analyze these metrics and alert you before issues become incidents.

Need Help with Your Database?

Facing tricky PostgreSQL performance issues? Want to deep dive into your specific database challenges? I offer a free 30-minute consultation session where we can discuss your situation and explore solutions together.

📅 Book a free discussion slot - Let's tackle your database problems together!

Remember: knowing your tools before disaster strikes is the difference between a quick fix and a long night.