How to Debug Slow Queries and Database Bottlenecks in the Terminal Using pg_activity

The Invisible Challenge of Relational Database Slowness

When a web application begins to respond with latency, the first suspect is usually the API code or the network infrastructure. However, in the vast majority of cases, the real culprit is hidden deep within the relational database. Poorly constructed SQL queries, known as slow queries, silently consume precious processing and memory resources. For software engineers and systems administrators, discovering exactly which instruction is stalling the system requires agile, straight-to-the-point tools, far away from sluggish graphical interfaces or expensive monitoring dashboards.

In Linux-based production environments, accessing servers via Secure Shell (SSH) is standard practice. In these scenarios, opening a heavy web browser to check PostgreSQL performance is impractical or impossible. This is precisely where command-line utilities come into play. Tools built to run in the terminal offer instant feedback, low resource consumption, and the ability to be executed rapidly on any remote instance, even when network bandwidth is severely constrained.

The PostgreSQL ecosystem provides several internal system views, such as the pg_stat_activity system table, which lists all active connections and what they are currently executing. However, reading this table manually requires writing repetitive SQL queries and interpreting raw columns without any visual formatting. It is to fill this operational gap that interactive real-time monitoring utilities become indispensable for any technical team focused on reliability.

Understanding pg_activity and Its Value Proposition

pg_activity is an open-source command-line tool written in Python, designed specifically to monitor PostgreSQL instances in real time. If you have ever needed to check CPU and memory usage on a Linux server using the classic top or htop commands, you already possess the mental model needed to understand pg_activity. It translates complex database data into a colorful, dynamic, and extremely readable text-based interface right inside your terminal.

In practice, the tool connects to your PostgreSQL database and continuously queries the internal statistics views, refreshing the screen every few seconds. It groups information by processes, displaying CPU consumption, memory usage per connection, execution time of the current query, and the state of each session. This means that instead of guessing what is bringing down the application, you can literally see the exact SQL query consuming one hundred percent of the processor at that very instant.

Beyond displaying raw resource consumption, pg_activity allows you to interact directly with running processes. If you identify a query that entered an infinite loop and is locking up the entire user table, you can cancel that specific instruction or even terminate the problematic connection directly through the tool's interactive interface, without needing to open a separate session of the PostgreSQL interactive console, known as psql.

Practical Installation and Initial Setup

Installing pg_activity is a straightforward process that can be accomplished in different ways depending on your operating system's package manager. Because the tool is packaged in Python, the most universal and recommended way to ensure the latest version is to use the official Python ecosystem installer, pip, or modern managers like pipx, which isolate the application in its own virtual environment to avoid dependency conflicts on the host system.

To install the tool using pipx on an Ubuntu or Debian server, execute the following commands in your terminal:

sudo apt update && sudo apt install pipx -y
pipx ensurepath
pipx install pg_activity

Once installation is complete, you must ensure you have appropriate access credentials to the PostgreSQL database. Pg_activity needs to connect to an instance to collect metrics. If you are running the command directly on the database server itself, standard environment variables such as PGUSER, PGHOST, and PGPORT are usually sufficient, or you can pass parameters directly via the command line.

To start basic monitoring, the most direct command involves specifying the database user name and, optionally, the database name:

pg_activity -U postgres -h localhost

If PostgreSQL is configured for password authentication or uses a pg_hba.conf restrictions file, the tool will securely prompt for your access password before rendering the main interface in the terminal.

Interpreting the Interface and Real-Time Metrics

As soon as pg_activity launches successfully, your terminal screen transforms into a dynamic dashboard divided into clear sections. At the top, you find a general summary of the PostgreSQL instance state, including the total number of active connections, global CPU and memory usage by the database engine, alongside consolidated disk read and write (I/O) statistics.

Below this summary header sits the main table displaying all ongoing sessions. Each row represents a connection or execution thread. The columns display vital information: the operating system process identifier (PID), connected user name, accessed database, duration of the current query (dur), connection state (active, idle, waiting for lock), and finally, the truncated text of the SQL query currently being executed.

Comprehending connection states is vital for diagnosing bottlenecks. The active state means PostgreSQL is actively processing an instruction for that session. The idle in transaction state indicates that a transaction was opened and performed operations, but the developer forgot to send the commit or rollback command, keeping locks active on entire tables and preventing other write operations from proceeding.

Filtering Information and Diagnosing Lock Bottlenecks

In production environments handling hundreds of requests per second, the sheer volume of rows displayed in pg_activity can be overwhelming. To isolate problems quickly, the tool offers interactive keyboard shortcuts that act as dynamic filters. Pressing the letter s, for example, lets you sort processes by CPU consumption, placing the server's heaviest resource hogs at the top of the list.

Another powerful feature is the ability to filter processes by specific users or states. If you want to view only queries taking longer than acceptable thresholds, execution time filters can be applied. Furthermore, the tool lets you toggle between different query display modes, making it easier to read lengthy SQL instructions that would otherwise appear truncated on the standard screen.

Concurrency locking, technically known as lock contention, occurs when two or more transactions attempt to modify the same record or table simultaneously. Pg_activity visually highlights these situations, allowing you to identify which transaction holds the lock (the blocker) and which sessions are parked waiting for release (the blocked). This immediate visibility eliminates hours of blind investigation through unstructured log files.

## Best Practices for Resolution and Corrective Actions in the Terminal

Identifying a slow query is only half the battle in software engineering; the other half consists of making the right corrective decision without crashing the application. When pg_activity points out an inefficient SQL query exhausting the CPU, the immediate temptation is to simply kill the process. However, abruptly terminating a large transaction can trigger a heavy automatic rollback process, which will continue consuming disk and CPU resources for several minutes.

A professional approach requires evaluating the impact before taking action. If the query is merely a heavy listing operation without proper pagination or an appropriate table index, the ideal path is noting the SQL code displayed in the terminal, exiting the monitoring tool, and planning the creation of an index using the CREATE INDEX CONCURRENTLY command, which avoids locking table write operations while the index builds in the background.

If the situation is critical and the server is on the verge of total unavailability due to connection exhaustion, using pg_activity's quick action keys to cancel the specific query (typically pressing c to cancel or k to terminate the process) becomes unavoidable. In these operational crises, the agility provided by a well-mastered terminal tool makes all the difference between prolonged downtime and rapid, controlled system recovery.

Conclusion

Proactive monitoring of relational databases is an essential skill for any engineer seeking stability and high performance in their systems. pg_activity perfectly bridges the gap between the complexity of internal PostgreSQL data and the need for rapid responses in Linux-based production environments, combining the visual simplicity of a text-mode dashboard with the analytical depth required to diagnose complex failures.

By mastering this terminal tool, development and operations teams gain autonomy to inspect CPU bottlenecks, identify stalled transactions, and resolve locking disputes in real time, without relying on sluggish graphical interfaces. Integrating this quick inspection routine into the daily engineering cycle ensures performance issues are resolved before impacting end-user application experiences.