How to Monitor PostgreSQL Effectively Using pg_top

Monitoring PostgreSQL databases in real-time is essential for maintaining performance, diagnosing issues, and ensuring stability in production environments. One of the most effective command-line tools for this purpose is pg_top. This tool provides a live, interactive view of your PostgreSQL database activity, making it invaluable for DBAs and developers alike.

In this blog, we’ll cover installation, usage, understanding the output, and why pg_top is a must-have tool for DBAs.

What is pg_top?

pg_top is a command-line interface (CLI) tool for monitoring PostgreSQL. It is similar to the Unix top command but specifically designed for PostgreSQL database clusters.

With pg_top, you can:

• See active queries and sessions in real-time.
• Monitor CPU, memory, and I/O usage of PostgreSQL processes.
• Identify idle vs. active sessions.
• Check locks and replication statistics.
• Terminate problematic queries directly from the CLI.

Its interactive interface allows filtering, sorting, and adjusting display options, making it highly flexible for DBAs managing busy production servers.

Installing pg_top

Follow these steps to install pg_top on Ubuntu or Debian-based systems:

• Update the package list:

sudo apt-get update

• Install pgtop:

sudo apt-get -y install pgtop

• Check the installed version:

pg_top --version

Example output:

pg_top 4.1.3

Running pg_top

Switch to the PostgreSQL user

sudo su postgres

Launch pg_top

pg_top

To connect to a specific database:

pg_top -d mydatabase -U postgres

Help and Options

pg_top --help

Some useful options:

• -d DBNAME - Database name
• -U USERNAME - PostgreSQL user
• -I - Hide idle sessions
• -o FIELD - Sort by a specific column
• -R - Show replication stats
• -X - Show I/O statistics
• -s SECONDS - Update interval

Understanding pg_top Output

When you run pg_top, you will see a detailed display similar to this:

last pid: 200581;  load avg  0.60,  1.67,  1.86; up 0+02:06:57
8 processes: 5 other background task(s), 2 idle, 1 active
CPU states:  0.6% user, 0.0% nice, 1.2% system, 98.3% idle, 0.0% iowait
Memory: 14G used, 432M free, 0K shared, 75M buffers, 5833M cached
DB activity: 4 tps, 0 rollbs/s, 0 buffer r/s, 100 hit%, 504 row r/s, 0 row w/s
DB I/O: 0 reads/s, 0 KB/s, 0 writes/s, 0 KB/s
Swap: 2484M used, 27G free, 354M cached, 0K in, 0K out
PID USERNAME    SIZE   RES STATE   %CPU LOCKS COMMAND
200582 postgres    238M   24M active   0.0     8 postgres: 17/main: postgres postgres [local] idle
152059 postgres    238M   23M idle     0.0     0 postgres: 17/main: postgres postgres [local] idle
148161             227M   11M          0.0     0 postgres: 17/main: walwriter

Breaking Down the Output

Header Section

• last pid - The last process ID created.
• load avg - System load averages over 1, 5, and 15 minutes.
• process summary - Number of active, idle, and background PostgreSQL processes.
• CPU states - Percentages of user, system, and idle CPU time.
• Memory - Total memory usage, free memory, buffers, and cache.
• DB activity - Database-specific metrics:
  • tps - Transactions per second
  • rollbacks/s - Rollbacks per second
  • buffer r/s - Buffer reads per second
  • hit% - Cache hit percentage
  • row r/s / w/s - Rows read and written per second
  • DB I/O - Reads and writes in bytes and operations per second.
  • Swap - Swap usage details.

Process Table

• PID - Process ID of the PostgreSQL session.
• USERNAME - PostgreSQL user executing the process.
• SIZE / RES - Virtual and resident memory usage.
• STATE - Status of the session (active, idle, etc.).
• %CPU - CPU usage for this process.
• LOCKS - Number of locks held by this process.
• COMMAND - Query or operation being executed.

How pg_top Helps DBAs

1. Real-Time Performance Monitoring

• Quickly see which queries are consuming CPU or memory.
• Identify idle vs. active connections.

2. Troubleshoot Slow Queries

• Spot long-running queries and decide whether to terminate them.

3. Analyze System Resource Usage

• Observe CPU, memory, and I/O stats for the database.
• Identify potential bottlenecks in production environments.

4. Monitor Locks and Contention

• Detect which sessions are holding locks that block other queries.

5. Track Transactions and Row Activity

• Monitor TPS (transactions per second), buffer hits, and row reads/writes.

6. Support Replication Monitoring

• Use -R to view replication-related stats in a master-slave setup.

In short, pg_top allows DBAs to get a real-time operational view of PostgreSQL without needing a GUI, making it perfect for servers, remote sessions, or production troubleshooting.

Conclusion

pg_top is a must-have command-line tool for PostgreSQL DBAs. Its real-time monitoring, interactive interface, and comprehensive metrics allow database administrators to:

• Identify slow or problematic queries
• Track CPU, memory, and I/O usage
• Monitor active and idle sessions
• Manage locks and replication status

For any DBA managing PostgreSQL in production or development, pg_top provides instant insight into the health and performance of the database, helping prevent issues before they escalate.