pg_activity: a top for PostgreSQL that also reads the host

Why psql alone leaves you guessing

pg_activity is described as a top like application for PostgreSQL server activity monitoring, and the analogy does more work than it first appears. `top` became indispensable not because it shows processes, which `ps` does, but because it shows process plus resource in the same row. pg_activity makes the same move for a database.

The base data is familiar. It comes from the server's activity view, the same underlying statistics you would query by hand to see active sessions or the list of running queries. What pg_activity adds is context: CPU percentage, memory percentage, reads per second, writes per second, duration and wait events for each backend, alongside the process table for the host.

That combination is the whole point. A query sitting in a wait state for four seconds means something different when the disk is saturated by a neighbouring workload than it does on an idle machine, and a curses display that refits in place lets you watch both develop. The refresh flag accepts half a second through five seconds, defaulting to two, which is fast enough to watch a batch job start and stop.

Installing from PGDG, PyPI, pipx or source

The README recommends distribution packages first, with a warning worth taking seriously. On Debian-based systems the command is a single apt install, and PGDG maintains packages for RPM and Debian derivatives. But the README states plainly that distribution packages may not be up to date with the latest releases, and it asks bug reporters to check the package version against the changelog first, and to go to the package maintainer rather than upstream if the issue is about packaging.

That caveat matters more than it sounds for a tool people run during incidents. The PyPI route installs the driver alongside the tool:

python3 -m pip install "pg_activity[psycopg]"

or, if you would rather not touch the system Python, pipx in an isolated environment, where the binary lands at `~/.local/bin/pg_activity` if that directory is not already on your path. The bracketed extra is what pulls in psycopg, and the pyproject also offers a psycopg2 variant for codebases that are not ready to move to the third generation driver.

For development there is a full source path with a virtual environment, and `tox.ini`, `pytest.ini` and `.pre-commit-config.yaml` in the tree show a project with real test and lint discipline behind it rather than a script someone maintains alone.

The privilege question decides what you actually see

The Usage section is the part to read before you install, because it explains why the tool sometimes looks worse than expected. To display system information, the local user running pg_activity must be the same user running the PostgreSQL server, which is `postgres` by default, or have more rights such as root. The PostgreSQL user must additionally be a superuser to get as much data as possible.

When those conditions are not met, pg_activity does not fail. It falls back to a degraded mode where some data, such as system information or temp file data, are not displayed. That is the trap. An operator connecting as a restricted monitoring role over a network will get a working display with gaps that look like a quiet database rather than a permission wall.

The local invocation in the README is therefore not a casual example but a requirement:

sudo -u postgres pg_activity -U postgres

Remote use is supported, with the connection given as a connection string such as `host=HOSTNAME port=PORT user=USER dbname=DBNAME`, plus the usual `-h`, `-p`, `-U` and `-d` flags. But the system metrics belong to the machine you run on, not the machine you query, which is worth remembering before reading a remote view as a picture of the server.

Filters and thresholds that change the answer

The options list is long, but a handful of flags do most of the practical work. `--min-duration SECONDS` hides queries shorter than the given threshold, which is the single most useful option on a busy server, because without it a screen full of fast queries hides the one slow query you were asked about.

`--duration-mode` is the subtler one. It takes three values, 1 for query which is the default, 2 for transaction, and 3 for backend, and the difference between them is whether an activity is timed from when its query started, when its transaction opened, or when the backend itself was started. A query inside a transaction that has been open for an hour can look instant under query mode and correctly alarming under transaction mode. Choosing wrong is a common source of confusion when comparing pg_activity against a slow query log.

Then there is `--filter FIELD:REGEX`, which applies a case insensitive expression to known fields such as dbname, `--rds` for AWS RDS which implies no tempfiles and excludes the rdsadmin database from space calculations, `--strip-comments` for cleaning SQL text, `-w` to wrap long queries instead of truncating them, and `--output FILEPATH` to store running queries as CSV. A CSV of what was running at the moment of an incident is often more useful than a screenshot of the curses screen.

Hiding columns and turning the display down

There is a full set of paired flags for the process table columns, and the negative form is the interesting one. Options like `--pid` and `--no-pid`, `--database` and `--no-database`, `--cpu`, `--mem`, `--read`, `--write`, `--time`, `--wait` and `--app-name` let you remove columns that do not apply to your workload. Release 3.6.0 added non-negative counterparts of many of these, so a configuration file can disable a column and the command line can still turn it back on, which is a small fix that removes a real annoyance in a tool used over SSH on a narrow terminal.

The header has its own switches: `--no-inst-info`, `--no-sys-info` and `--no-proc-info` drop the instance, system and worker process summaries from the top of the screen. On a small terminal that is often the difference between a readable display and one where the process table starts halfway down the screen.

One entry is worth reading for what it implies. `--hide-queries-in-logs` disables `log_min_duration_statements` and `log_min_duration_sample` for pg_activity. Monitoring tools that quietly change your server configuration are a nuisance, and this tool makes it an explicit opt-in flag instead. Release 3.6.1 had to fix that flag to also disable `log_statements`, so the behaviour has needed care, but at least it is visible on the command line.

Configuration files, profiles and the dependency set

Settings live in an INI format file read from `${XDG_CONFIG_HOME:~/.config}/pg_activity.conf` or `/etc/pg_activity.conf`, in that order, and command line options override whatever the file says. The `-P` and `--profile` option adds a second layer, matching a PROFILE.conf file in the user's config directory or `/etc/pg_activity/`, or a built-in profile. That is enough to keep a display tuned for a laptop and a different one tuned for a wall screen.

Release 3.6.0 added colour customisation for cells in the process table through the configuration file, and also fixed the colour configuration of the appname column. Colour is where a display tool like this earns or loses trust, since the point of the view is that a blocked query looks blocked.

The dependency list from `pyproject.toml` is short and tells you what the tool is made of: attrs, blessed for the terminal handling, humanize for readable durations and sizes, psutil for the system metrics, and sqlparse for formatting SQL. There is no database framework and no ORM, which is the right choice for a diagnostic tool that needs to show you a malformed query exactly as the server received it. The project requires Python 3.9 or newer, and 3.6.0 dropped Python 3.8 support.

Editorial conclusion

pg_activity earns its place next to psql because the question you have when a database feels slow is rarely only about SQL. It puts the query, the wait event, the client address and the CPU and IO cost of the backend on one screen, which turns a vague report of slowness into a specific question you can answer with EXPLAIN. Two things to know before you rely on it. First, the richest view requires a superuser connection and a local process that can see system information, so running it over a remote host or as an unprivileged user quietly loses columns. Second, it is a point-in-time monitor, not a history store, and the recent release history shows maintenance rather than new ground: 3.6.2 in June 2026 fixed comment stripping in the CSV output and removed a metric that turned out to mislead. Install from PGDG rather than your distribution package, and set --min-duration before you conclude a database is idle.

Frequently asked questions

Official sources

1. dalibo/pg_activity on GitHub
2. Issues
3. License: PostgreSQL
4. README
5. Releases