Introduction
PostgreSQL exposes a rich set of internal statistics through system views such as pg_stat_activity, pg_stat_database, and pg_stat_bgwriter. These views are excellent for understanding what happens inside the database engine. However, real-world production problems often originate outside PostgreSQL, at the operating system level.
High CPU usage, memory pressure, disk I/O saturation, and unexpected background processes can severely impact PostgreSQL performance, especially in large systems like Odoo databases, migration workloads, or analytical environments.
Traditionally, database administrators rely on operating system tools such as top, htop, vmstat, iostat, and files under /proc. The problem is that these tools live outside PostgreSQL, while most troubleshooting starts inside the database.
The pg_proctab extension solves this gap by exposing operating system process and resource information directly inside PostgreSQL as SQL-callable functions.
This blog explains what pg_proctab is, how to install it, how each function works, and why it is valuable for database administrators.
What is pg_proctab?
pg_proctab is a PostgreSQL extension that reads data from the Linux /proc filesystem and exposes it through SQL functions. It allows PostgreSQL to query operating system–level statistics such as CPU usage, memory usage, disk I/O, system load, and per-process information.
Instead of switching between PostgreSQL and the operating system shell, a DBA can run SQL queries to inspect how the operating system behaves and how PostgreSQL backend processes interact with system resources.
This extension is particularly useful in environments where access to system tools is limited or where diagnostics need to be automated using SQL.
Why pg_proctab Matters for DBAs
Modern PostgreSQL deployments often run in containers, cloud virtual machines, or managed environments. In these setups, DBAs may not always have direct or persistent access to operating system monitoring tools.
pg_proctab enables DBAs to:
- Observe CPU, memory, and disk usage without leaving PostgreSQL
- Correlate PostgreSQL backend processes with OS-level behavior
- Diagnose performance issues during migrations, vacuum, or heavy write workloads
- Perform lightweight diagnostics without installing external monitoring agents
Installing pg_proctab
Step 1: Clone the Source Code
git clone https://github.com/markwkm/pg_proctab
cd pg_proctab
Step 2: Build the Extension
Ensure PostgreSQL development headers are installed, then run:
make
Step 3: Install the Extension
sudo make install
This installs the shared library and SQL control files into PostgreSQL’s extension directories.
Checking Extension Availability
After installation, connect to PostgreSQL and verify that the extension is available:
SELECT *
FROM pg_available_extensions
WHERE name = 'pg_proctab';
If it is installed, you get result like this
-[ RECORD 1 ]-----+--------------------------------------
name | pg_proctab
default_version | 0.0.12
installed_version | 0.0.12
comment | Access operating system process table
If the extension appears but is not yet installed in the database, the installed_version column will be empty.
Creating the Extension
Enable the extension in the current database:
CREATE EXTENSION pg_proctab;
Verify the installed objects:
\dx+ pg_proctab
You should see five functions created by the extension like this
Example:
Objects in extension "pg_proctab"
Object description
-------------------------
function pg_cputime()
function pg_diskusage()
function pg_loadavg()
function pg_memusage()
function pg_proctab()
(5 rows)
Exploring pg_cputime()
The pg_cputime() function exposes CPU usage statistics collected from the operating system.
SELECT * FROM pg_cputime();
You get result like this
user | nice | system | idle | iowait
---------+---------+----------+------------+--------
8645728 | 6147512 | 10180174 | 2772417712 | 534207
(1 row)
This function reports how much time the CPU has spent executing user-space code, kernel code, low-priority processes, waiting for I/O, or remaining idle. The values are cumulative and reflect overall system CPU behavior.
From a DBA perspective, this function helps distinguish whether PostgreSQL performance problems are caused by CPU saturation or by other factors. If idle time is very low and user or system time is continuously increasing, the server may be CPU-bound. If I/O wait is high, disk latency is likely the real bottleneck.
Exploring pg_diskusage()
The pg_diskusage() function exposes disk I/O statistics from the operating system.
SELECT * FROM pg_diskusage();
You get result like this
-[ RECORD 1 ]------+-----------
major |
minor |
devname | rdstemp
reads_completed | 100169
reads_merged | 0
sectors_read | 832338
readtime | 60620
writes_completed | 1588825
writes_merged | 0
sectors_written | 1441076832
writetime | 12866030
current_io | 0
iotime | 3859770
totaliotime | 12926650
discards_completed | 0
discards_merged | 0
sectors_discarded | 0
discardtime | 0
flushes_completed | 0
flushtime | 0
This function reports completed read and write operations, the number of sectors read and written, and the total time spent performing I/O. It reflects how heavily the storage subsystem is being used.
For a DBA, this information is critical during bulk inserts, index creation, vacuum operations, or WAL-heavy workloads. A high write count combined with increasing I/O time can indicate that the database is limited by disk throughput rather than query execution logic.
This function is especially useful when diagnosing slow queries that appear simple but are actually blocked by disk pressure.
Exploring pg_loadavg()
The pg_loadavg() function provides the system load average.
SELECT * FROM pg_loadavg();
You get result like this
load1 | load5 | load15 | last_pid
-------+-------+--------+----------
0.86 | 0.32 | 0.22 | 6699
(1 row)
The load average represents the number of processes waiting for CPU or I/O over different time intervals. It gives a high-level view of system stress.
From a DBA’s point of view, load average is useful to quickly detect whether the system is overloaded. A sustained high load average during database migrations, parallel queries, or batch jobs often explains latency spikes inside PostgreSQL.
Because this information is available via SQL, it can be captured during incident analysis or stored for later comparison.
Exploring pg_memusage()
The pg_memusage() function exposes memory and swap usage statistics.
SELECT * FROM pg_memusage();
Example:
memused | memfree | memshared | membuffers | memcached | swapused | swapfree | swapcached
----------+----------+-----------+------------+-----------+----------+----------+------------
98270788 | 31544172 | 0 | 473288 | 6158364 | 0 | 8388604 | 0
(1 row)
This function shows how much physical memory is used, how much is free, how much is cached by the kernel, and whether swap space is being consumed.
For PostgreSQL administrators, memory usage is critical. PostgreSQL performance degrades significantly if the system starts swapping. By checking this function, a DBA can verify whether memory pressure exists and whether PostgreSQL configuration parameters such as shared_buffers, work_mem, and maintenance_work_mem are appropriate.
This function is also useful for detecting memory leaks or unexpected memory consumption by other processes on the server.
Exploring pg_proctab()
The pg_proctab() function is the most powerful feature of the extension.
SELECT * FROM pg_proctab() limit 1 ;
Example:
-[ RECORD 1 ]---------+-----------------------------------------
pid | 656
comm |
fullcomm | postgres: rdsadmin rdsadmin [local] idle
state | S
ppid | 581
pgrp |
session | 656
tty_nr | 0
tpgid | -1
flags |
minflt | 1293
cminflt | 0
majflt | 4
cmajflt | 0
utime | 146
stime | 0
cutime | 0
cstime | 0
priority | 37
nice | 17
num_threads | 1
itrealvalue | 0
starttime | 172660052
vsize | 190610501632
rss | 38364
exit_signal | 17
processor |
rt_priority | 0
policy | 0
delayacct_blkio_ticks | 0
uid |
username |
rchar | 78029
wchar | 191411
syscr | 33
syscw | 53
reads | 0
writes | 200704
cwrites | 0
This function returns one row per operating system process, similar to the output of tools like ps or top. It includes PostgreSQL backend processes, background workers, and other system processes.
Each row provides details such as process ID, command name, full command line, process state, CPU time consumed, memory usage, and I/O counters.
For DBAs, this function enables deep diagnostics. By matching the process ID with pg_stat_activity.pid, it becomes possible to identify exactly which PostgreSQL backend process is consuming CPU, memory, or disk I/O.
This is extremely useful for finding runaway queries, stuck transactions, idle sessions holding resources, or backend processes causing excessive disk writes.
Practical DBA Benefits of pg_proctab
One of the biggest advantages of pg_proctab is that it enables SQL-only diagnostics. DBAs can investigate system behavior directly from a PostgreSQL session without switching to the operating system shell.
It also allows correlation between database internals and OS-level metrics. For example, a DBA can observe a slow query, identify its backend process, and immediately inspect that process’s CPU time, memory usage, and I/O statistics.
Because the extension is lightweight and does not rely on external agents, it is well suited for cloud and container-based deployments where installing monitoring software may not be possible.
Limitations and Considerations
pg_proctab relies on the Linux /proc filesystem, so it works only on Linux systems. Access to certain information may depend on system permissions.
This extension is not a replacement for full-featured monitoring systems such as Prometheus, Grafana, or enterprise monitoring tools. Instead, it is best used as a diagnostic and troubleshooting aid.
Conclusion
The pg_proctab extension brings operating system visibility directly into PostgreSQL. By exposing CPU usage, memory statistics, disk I/O, system load, and per-process information through SQL, it allows database administrators to analyze performance problems more efficiently and accurately.
For PostgreSQL environments running complex workloads such as Odoo, large migrations, or write-heavy systems, pg_proctab provides critical insights that traditional PostgreSQL statistics alone cannot offer.
By combining PostgreSQL internal views with pg_proctab, DBAs gain a unified, SQL-driven approach to understanding both database and operating system behavior.