Alright, let’s talk about pg_activity. You’ve probably used top or htop on a Linux system to see what’s chewing up all your CPU and memory. pg_activity is that, but for your PostgreSQL database. It’s a real-time, terminal-based dashboard that lets you see exactly what your server is doing, who’s connected, and which queries are currently trying to set your disk on fire. It’s not some bloated GUI; it’s a fast, information-dense tool you can SSH into and get an immediate pulse check. Forget digging through a dozen pg_stat views manually when things are on fire—this is your first line of defense.

Why pg_activity Beats Manually Querying pg_stat_views

Sure, you could write a novel of a SQL query joining pg_stat_activity, pg_locks, and pg_stat_statements to get a similar picture. I’ve done it. You’ve done it. It’s a rite of passage. But it’s also static. You run it, you get a snapshot, and a millisecond later that snapshot is obsolete. pg_activity gives you a live, updating view. More importantly, it does the heavy lifting of formatting that data into something instantly readable. The key columns are right there: PID, user, database, state (is it active? idle? idle in transaction?), the wait event (what is it waiting on?), the backend type, and of course, the query itself. Seeing this data in motion is infinitely more useful for diagnostics than a static slice.

Installation and Basic Invocation

First, you need to get it. It’s a Python tool, so pip is your friend. I highly recommend installing it in a virtual environment or using your system’s package manager to avoid the general chaos of global Python installations.

# On Debian/Ubuntu
sudo apt-get install pg-activity

# Or with pip (probably in a virtualenv)
pip install pg_activity

Running it is straightforward. It connects just like psql does, reading your standard PG* environment variables (PGHOST, PGUSER, etc.) or taking command-line arguments.

# Simple. Uses your $PGUSER, $PGHOST, etc.
pg_activity

# Explicit, because sometimes you need to be explicit.
pg_activity -h my-db-host.example.com -p 5432 -U monitoring_user -d postgres

Fire it up, and you’re immediately greeted with a refreshingly organized screen of database activity.

Interpreting the Key Metrics and Columns

The default view is packed with info. Let’s break down the crucial bits:

  • PID: The process ID. You’ll need this if you have to cancel a query.
  • Database: Which database is this connection hooked to? Crucial for multi-tenant or complex systems.
  • User: Who’s connected? Is it your app? A random analyst? Yourself from three hours ago?
  • Client: The IP address of the client. Is that request coming from where it’s supposed to?
  • CPU% / Mem%: Pretty self-explanatory. A sudden spike here is a great clue.
  • Read / Write: The rate of data being read from or written to disk (per second). A query with a high write rate and a long duration is probably your problem child.
  • Time: How long has the query been running? This is either “perfectly normal” or “oh god oh no”.
  • Wait Event: This is the gold. Is it waiting on a Lock? A BufferPin? IO? This column instantly tells you if the problem is a slow disk, a query holding a lock everyone else needs, or something else entirely. If it says ClientRead, congratulations, the database is waiting on your application to send its next command or consume the results. Go yell at the app developers.

Taking Action: Cancelling Queries and More

pg_activity isn’t just for watching the Titanic sink; it gives you lifeboats. Navigate with the arrow keys to highlight a process. Now press k.

Select a process to kill.
Proceed? [y/N]:

Say y, and it sends a SIGINT—the equivalent of pg_cancel_backend(pid). It’s a polite request for the query to stop. Most well-behaved queries will abort. If that doesn’t work (or if you’re sure you need the nuclear option), press F9. This toggles the “kill” mode from SIGINT to SIGTERM. Now when you press k, it’s pg_terminate_backend(pid). This is the equivalent of pulling the plug on the connection. It will stop the query, but it might also cause your application to throw an error. Use this one with more care.

Customizing the View and Refreshing Data

The default view is great, but you can tailor it. Press f to bring up a field display menu. You can toggle columns on and off. Don’t care about the client address? Turn it off. Want to see the application name? Turn it on. You can also sort by any column by pressing < or >.

By default, it refreshes every 2 seconds. This is usually fine. If you need a different interval, you can start it with --duration 5 for a 5-second refresh, for instance. Be warned: too frequent and it adds load; too infrequent and you might miss short-lived spikes.

The One Quirk You Must Remember

Here’s the one thing the designers did that’s, frankly, a bit questionable. The default view filters out idle connections. You know, the ones just sitting there waiting for a command. This is usually what you want when diagnosing a performance fire—you want to see the active stuff. But sometimes, you need to see all connections, especially if you’re hunting for a connection leak or too many open idle transactions. Toggle this by pressing F2. This is a classic case of a sensible default that can trip you up if you forget it’s there. Always be conscious of whether you’re looking at “active” or “all” mode. It’s the difference between looking for a needle in a haystack and looking at the entire hayfield.