How to see active SQL queries and open connections in Postgres?

If you’re working with a PostgreSQL database, keeping tabs on active queries is a must to keep things running smoothly. Let it slide, and you could end up with sluggish performance—or even a database that stops working altogether. The upside? PostgreSQL has built-in features to help you stay on top of what’s happening. This guide will show you straightforward ways to track and manage active queries, ensuring your database stays quick and reliable.

Understanding pg_stat_activity

One of PostgreSQL’s handiest tools for monitoring is pg_stat_activity. It’s like a window into what’s happening in your database at any given moment. This built-in system view lets you see critical details like:

• datname: The database name.
• usename: Which user is running the query.
• pid: The process ID tied to the query.
• query: The actual query text.
• state: Whether the query is active, idle, or something else.
• query_start: When the query started.

By understanding these columns, you can interpret what’s happening with each session in real-time. For example, if you notice multiple active queries from a single user, it might be worth investigating further.

Listing All Active Queries

If you want a quick snapshot of all active queries, a simple SQL command can do the trick. Here’s an example:

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

When you run this, it shows you all the queries currently running in your database. Pretty useful, right? This information is great for spotting queries that might be hogging resources or causing bottlenecks.

Why This Matters

Real-time query monitoring is particularly helpful when debugging performance issues. For example, if your app suddenly slows down, you can use this query to identify if a specific command is the culprit.

Identifying Long-Running Queries

Not all active queries are equal. Some might run for an unusually long time and cause delays or lock up resources. To find these culprits, you can use this SQL query:

SELECT pid, now() - query_start AS duration, query  
FROM pg_stat_activity  
WHERE state = 'active'  
  AND now() - query_start > interval '5 minutes';  

This query checks for any processes that have been active for more than five minutes.

What to Do About Long-Running Queries

Once you’ve spotted a long-running query, it’s time to decide if you should step in. Maybe the query needs optimization, or perhaps it’s stuck due to a resource lock. Either way, identifying these queries early can save you a lot of headaches down the road.

Terminating Problematic Queries

Sometimes, you have to pull the plug. If a query is causing trouble and you can’t wait for it to finish, PostgreSQL offers two options:

1. pg_cancel_backend(pid): Politely asks the query to stop.
2. pg_terminate_backend(pid): Forcefully shuts it down.

Here’s how you’d use them:

SELECT pg_cancel_backend(pid);  
SELECT pg_terminate_backend(pid);  

Risks to Keep in Mind

Be cautious when terminating queries. Canceling a query might leave things incomplete, while terminating it could affect other processes. Use these commands as a last resort and make sure you know the impact.

Monitoring Tools and Extensions

While pg_stat_activity is powerful, it’s not the only tool in the toolbox. Tools like pgAdmin make query monitoring more visual, and extensions like pg_stat_statements give you deeper insights.

Why Use Extensions?

pg_stat_statements is particularly great because it tracks query performance over time. Instead of just looking at active queries, it helps you identify trends and optimize for the long haul.

Best Practices for Query Monitoring

To keep your database running smoothly, here are some simple habits to adopt:

• Monitor regularly: Don’t wait for issues to pile up. Make it a routine to check for active and long-running queries.
• Set up alerts: Use tools to notify you when a query runs longer than a set duration.
• Optimize your queries: Review slow queries for possible improvements, like adding indexes or rewriting them for better performance.

By following these steps, you’ll stay ahead of problems and keep your database humming along.

Conclusion

Monitoring active queries in PostgreSQL doesn’t have to be overwhelming. Tools like pg_stat_activity and extensions like pg_stat_statements give you everything you need to identify and manage issues quickly. By incorporating regular monitoring routines and best practices, you’ll ensure your database stays in top shape. Ready to dive in? Start with the commands we’ve covered here and watch your PostgreSQL performance soar!