pg_top is a real-time monitoring utility for PostgreSQL, similar to the Linux top command. It allows DBAs and database engineers to monitor PostgreSQL backend processes, active SQL statements, resource consumption, and database activity in real time.
Table of Contents
Installing pg_top
RHEL / CentOS
sudo yum install pg_top
or
sudo dnf install pg_top
Ubuntu
sudo apt install pgtop
Starting pg_top
Connect to the default local database:
pg_top
Connect to a specific database:
pg_top mydb
Specify a user:
pg_top -U postgres mydb
Specify host and port:
pg_top -h localhost -p 5432 -U postgres mydb
Understanding the Main Screen
A typical pg_top display looks like this:
last pid: 32567; load avg: 0.42, 0.35, 0.29
PID USER PRI NI SIZE RES STATE TIME CPU COMMAND
32411 postgres 20 0 35M 9M idle 0:00 0.0 postgres
32412 postgres 20 0 48M 15M active 0:12 8.1 SELECT ...
32413 postgres 20 0 40M 11M idle 0:00 0.0 postgres
Key Columns
| Column | Description |
|---|---|
| PID | PostgreSQL backend process ID |
| USER | Database user |
| STATE | Current session state (active, idle, etc.) |
| CPU | CPU utilization |
| TIME | Accumulated CPU time |
| COMMAND | Current SQL statement |
Useful Keyboard Shortcuts
Display Help
Press:
?
Sort Processes
Sort by CPU usage:
P
Sort by memory usage:
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
M
Sort by execution time:
T
Display Full SQL Text
Press:
c
This toggles the display of the full SQL query associated with each backend process.
View Lock Information
Press:
l
Useful for identifying lock waits and blocking sessions.
View Database Statistics
Press:
d
Displays database-level statistics.
View Replication Information
Press:
r
Displays replication status (depending on PostgreSQL and pg_top versions).
Common DBA Use Cases
Identify High-CPU Queries
Press:
P
to sort sessions by CPU usage and quickly identify resource-intensive queries.
Find Long-Running Queries
Press:
T
to sort by execution time.
Look for sessions with unusually large values in the TIME column.
Investigate Stuck or Hung Sessions
Check the STATE column.
For example:
active
If a session remains active for an extended period, it may indicate a long-running query, lock wait, or performance issue.
Record the PID for further investigation.
Terminating Problematic Sessions
After identifying a problematic PID, you can cancel or terminate it from PostgreSQL.
Cancel the current query:
SELECT pg_cancel_backend(pid);
Terminate the entire session:
SELECT pg_terminate_backend(pid);
Example:
SELECT pg_terminate_backend(32412);
Combining pg_top with Linux Tools
Once you identify a PostgreSQL backend PID, you can examine it from the operating system.
View process details:
ps -fp 32412
Monitor thread-level activity:
top -Hp 32412
This is useful when investigating CPU spikes or memory consumption.
Where Does pg_top Get Its Data?
Most information displayed by pg_top comes from PostgreSQL system views, including:
pg_stat_activitypg_stat_databasepg_stat_replicationpg_locks
For example, active sessions can be queried directly:
SELECT
pid,
usename,
application_name,
state,
wait_event_type,
wait_event,
query
FROM pg_stat_activity;
Many managed PostgreSQL environments, such as Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL, do not have pg_top installed. In those environments, DBAs typically rely directly on these system views.
Checking the Installed Version
pg_top -v
Most Useful Shortcuts at a Glance
| Key | Function |
|---|---|
P | Sort by CPU usage |
M | Sort by memory usage |
T | Sort by execution time |
c | Display full SQL text |
l | Show lock information |
d | Display database statistics |
q | Quit pg_top |
Summary
For day-to-day PostgreSQL performance troubleshooting, the typical workflow is:
pg_top
Then use:
P → Find high-CPU queries
T → Find long-running queries
c → View full SQL statements
These three actions alone cover the majority of PostgreSQL performance investigations and troubleshooting scenarios.
