Real-time PostgreSQL Monitoring with pg_activity: Kill Hung Queries in a Flash – ITFROMZERO

Table of Contents

Install and Run in 5 Minutes

If you’ve ever managed PostgreSQL, you’ve likely faced a server CPU hitting 100% without knowing why. Instead of manually typing SELECT queries into pg_stat_activity, you should use pg_activity. This tool works similarly to the htop command but is specifically designed for databases.

To get started, you can quickly install it via a package manager. However, I recommend using pip to ensure you get the latest update (currently version 3.x with many improvements).

# On Ubuntu/Debian
sudo apt update && sudo apt install pg-activity

# Install via pip (recommended for the latest version)
pip install pg_activity

After installation, connect to the database with superuser privileges (usually the postgres user). Only this privilege allows you to observe all system processes:

pg_activity -U postgres -d postgres -h localhost

Enter the password when prompted. An intuitive dashboard will appear, displaying connection metrics, CPU, RAM, and the list of currently executing queries.

Understanding “Golden” Metrics on the Dashboard

Don’t let the cluster of numbers confuse you. Focus on these key areas whenever the system shows signs of sluggishness:

Header: Observe the Load Average. If this number exceeds the number of CPU cores (e.g., a load of 10.0 on a 4-core machine), your server is severely overloaded.
PID: Process ID. You will need this number if you want to terminate a specific query.
STATE: Connection status. Be especially wary of idle in transaction. These are connections that have opened a transaction but haven’t committed or rolled back, often the culprits for holding locks too long.
TIME: Query execution time. A simple SELECT taking more than 10 seconds is usually a sign of a missing index.

pg_activity’s color-coding for different states helps me react extremely fast. For example, active queries are highlighted, making them easy to distinguish from hundreds of other idle connections.

Quickly Handling Hung SQL Statements (Slow Queries)

This is the most practical feature of this tool. In reality, applications often face deadlocks or run unoptimized queries that drain resources. Instead of entering psql and typing the long pg_terminate_backend(pid) command, you can act directly.

How to “Clean Up” a Faulty Query:

Use the arrow keys to navigate to the suspicious query line.
Press the K key (Kill).
Confirm the operation. pg_activity will send a terminate signal to the backend to release resources immediately.

Pro Tip: Press the Space key to pause the screen. This allows you to inspect long SQL statements carefully and select the correct PID, preventing the list from jumping around and causing a misselection.

Advanced Shortcuts for Optimal Monitoring

To master pg_activity like a pro, you should memorize these shortcuts:

Keys 1, 2, 3: Quickly switch display modes. Key 1 for running queries, 2 for waiting queries, and 3 for Idle in Transaction connections.
Keys c and m: Sort the list by CPU or Memory consumption.
Key t: Sort by execution time (Duration). This is the fastest way to find the “snails” slowing down your system.
Key r: Adjust the refresh rate (default is 2 seconds). If the database is overloaded, increase it to 5 or 10 seconds to reduce the overhead of the monitoring process itself.

# Run pg_activity with a 5-second refresh rate to save resources
pg_activity --refresh 5

Battle-Tested Experience: pg_activity or Log Files?

Many wonder why not use pg_stat_statements for a more professional approach. The answer lies in the timing.

Log files and analysis extensions are great for periodic optimization. They tell you which queries were slowest over the last 24 hours. However, when the system is “on fire,” customers are complaining, and your boss is standing right behind you, you need an immediate answer.

In those moments, pg_activity is a lifesaver. It provides a direct, real-time view. You can see exactly which query is hogging 99% of the CPU and kill it with just two keystrokes.

One technique I often use is combining it with an SSH tunnel for databases located within an internal network:

ssh -t user@jump-host "pg_activity -U postgres -h 10.0.1.5"

This method is both secure and allows you to monitor the server remotely without exposing the database port to the internet. pg_activity is truly a compact yet powerful tool that anyone working with PostgreSQL should have ready on their machine.