PostgreSQL focuses mainly on database-level metrics, but sometimes you need visibility into the underlying operating system directly from SQL—CPU details, memory usage, disk activity, network stats, and running processes.
That’s exactly what the system_stats extension provides.
In this blog, we will walk through installing the system_stats extension from source and show how each function can be used to inspect real system information from inside PostgreSQL.
All examples below are tested on:
- PostgreSQL 18
- Ubuntu 22.04
- AMD Ryzen 7 system
What is system_stats?
system_stats is an open-source PostgreSQL extension maintained by EnterpriseDB. It exposes operating system statistics as SQL functions.
Once installed, you can query:
- CPU information and usage
- Per-process CPU and memory
- Disk space and I/O
- Load averages
- RAM and swap
- Network interfaces
- OS details
- Process counts
Everything is available using simple SELECT statements.
Step 1 – Clone the Repository
Start by cloning the source code:
git clone https://github.com/EnterpriseDB/system_stats.git
cd system_stats
Step 2 – Build the Extension
Since this is a source build, we compile it using PGXS and point to PostgreSQL 18:
Before this step, you can check the version of pg_config like this
cybrosys@cybrosys:~/system_stats$ pg_config --version
PostgreSQL 17.6
Build the extension by this command
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
If the build succeeds, install it:
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install
At this point, the extension binaries are copied into PostgreSQL’s extension directories.
Step 3 – Enable the Extension in PostgreSQL
Switch to the postgres user and connect to your cluster:
sudo su postgres
psql -p 5433
Create the extension:
CREATE EXTENSION system_stats;
Verify installation:
\dx+ system_stats
You should see the following functions:
\dx+ system_stats
Result :
Objects in extension "system_stats"
Object description
-----------------------------------------
function pg_sys_cpu_info()
function pg_sys_cpu_memory_by_process()
function pg_sys_cpu_usage_info()
function pg_sys_disk_info()
function pg_sys_io_analysis_info()
function pg_sys_load_avg_info()
function pg_sys_memory_info()
function pg_sys_network_info()
function pg_sys_os_info()
function pg_sys_process_info()
(10 rows)
CPU Hardware Information
To view CPU model, cores, cache sizes, and architecture:
SELECT * FROM pg_sys_cpu_info();
Example output includes:
-[ RECORD 1 ]------+----------------------------------------
vendor | AuthenticAMD
description | AuthenticAMD model 68 family 25
model_name | AMD Ryzen 7 7735HS with Radeon Graphics
processor_type |
logical_processor | 0
physical_processor | 16
no_of_cores | 8
architecture | x86_64
clock_speed_hz | 400000800
cpu_type |
cpu_family |
byte_order |
l1dcache_size | 32
l1icache_size | 32
l2cache_size | 512
l3cache_size | 16384
This gives hardware-level visibility directly from SQL.
CPU Usage Breakdown
To see how CPU time is distributed:
SELECT * FROM pg_sys_cpu_usage_info();
This shows percentages for user mode, kernel mode,idle time,IRQ handling and soft IRQ
What is IRQ handling?
IRQ means Interrupt Request.
It’s a signal sent to the CPU by hardware devices when they need attention.
Result :
-[ RECORD 1 ]-------------------+------
usermode_normal_process_percent | 1.27
usermode_niced_process_percent | 0
kernelmode_process_percent | 6.33
idle_mode_percent | 91.98
io_completion_percent | 0
servicing_irq_percent | 0
servicing_softirq_percent | 0.42
user_time_percent |
processor_time_percent |
privileged_time_percent |
interrupt_time_percent |
Per-Process CPU and Memory
To inspect running processes and their resource usage:
SELECT * FROM pg_sys_cpu_memory_by_process() LIMIT 10;
Result :
pid | name | running_since_seconds | cpu_usage | memory_usage | memory_bytes
-----+-------------------------------+-----------------------+-----------+--------------+--------------
1 | (systemd) | 37239 | 0 | 0.04 | 13500416
2 | (kthreadd) | 37239 | 0 | 0 | 0
3 | (pool_workqueue_release) | 37239 | 0 | 0 | 0
4 | (kworker/R-rcu_g) | 37239 | 0 | 0 | 0
5 | (kworker/R-rcu_p) | 37239 | 0 | 0 | 0
6 | (kworker/R-slub_) | 37239 | 0 | 0 | 0
7 | (kworker/R-netns) | 37239 | 0 | 0 | 0
10 | (kworker/0:0H-events_highpri) | 37239 | 0 | 0 | 0
12 | (kworker/R-mm_pe) | 37239 | 0 | 0 | 0
13 | (rcu_tasks_kthread) | 37239 | 0 | 0 | 0
(10 rows)
This is useful when diagnosing system load or identifying heavy processes without leaving PostgreSQL.
Disk Space Information
To view mounted filesystems:
Returned fields include:
- Mount point
- Filesystem
- Total space
- Used space
- Free space
- Inode counts
Result :
mount_point | file_system | drive_letter | drive_type | file_system_type | total_space | used_space | free_space | total_inodes | used_inodes | free_inodes
-------------+----------------+--------------+------------+------------------+--------------+--------------+------------+--------------+-------------+-------------
/ | /dev/nvme0n1p3 | | | ext4 | 470919749632 | 438938546176 | 7984541696 | 29278208 | 3359229 | 25918979
/boot/efi | /dev/nvme0n1p1 | | | vfat | 509640704 | 6381568 | 503259136 | 0 | 0 | 0
(2 rows)
This helps track disk pressure that could affect PostgreSQL performance.
Disk I/O Statistics
For block device activity:
SELECT * FROM pg_sys_io_analysis_info() LIMIT 10;
You’ll see per-device data such as:
- Total reads/writes
- Bytes read/written
- Read/write time in milliseconds
For example, nvme0n1 clearly shows cumulative read/write activity on the main drive.
This is valuable when analyzing slow queries caused by storage bottlenecks.
Result :
device_name | total_reads | total_writes | read_bytes | write_bytes | read_time_ms | write_time_ms
-------------+-------------+--------------+------------+-------------+--------------+---------------
loop0 | 14 | 0 | 17408 | 0 | 0 | 0
loop1 | 52 | 0 | 1110016 | 0 | 6 | 0
loop2 | 43 | 0 | 355328 | 0 | 5 | 0
loop3 | 73 | 0 | 1161216 | 0 | 11 | 0
loop4 | 48 | 0 | 367616 | 0 | 6 | 0
loop5 | 55 | 0 | 1110016 | 0 | 13 | 0
loop6 | 342 | 0 | 4825088 | 0 | 34 | 0
loop7 | 60 | 0 | 1111040 | 0 | 13 | 0
nvme0n1 | 227562 | 932298 | 8179008512 | 13652943872 | 37021 | 1331675
nvme0n1p1 | 383 | 2 | 6520832 | 1024 | 177 | 0
(10 rows)
Load Average
To check system load:
SELECT * FROM pg_sys_load_avg_info();
It returns:
- 5-minute load
- 10-minute load
- 15-minute load
This mirrors Linux load averages and helps correlate database latency with overall system pressure.
Result :
load_avg_one_minute | load_avg_five_minutes | load_avg_ten_minutes | load_avg_fifteen_minutes
---------------------+-----------------------+----------------------+--------------------------
1.65 | 1.48 | 1.18 |
(1 row)
Memory and Swap
To inspect RAM usage:
SELECT * FROM pg_sys_memory_info();
You’ll get:
- Total memory
- Used memory
- Free memory
- Swap total / used / free
- Cache size
Result :
total_memory | used_memory | free_memory | swap_total | swap_used | swap_free | cache_total | kernel_total | kernel_paged | kernel_non_paged | total_page_file | avail_page_file
--------------+-------------+-------------+-------------+-----------+-------------+-------------+--------------+--------------+------------------+-----------------+-----------------
32342069248 | 26225377280 | 6116691968 | 32000438272 | 0 | 32000438272 | 12570255360 | | | | |
(1 row)
This is essential for tuning PostgreSQL parameters like shared_buffers and work_mem.
Network Interface Statistics
To view network activity:
SELECT * FROM pg_sys_network_info();
Each interface shows:
- IP address
- TX/RX bytes
- Packet counts
- Dropped packets
- Link speed
This is useful in distributed setups or when debugging replication and backup traffic.
Result:
interface_name | ip_address | tx_bytes | tx_packets | tx_errors | tx_dropped | rx_bytes | rx_packets | rx_errors | rx_dropped | link_speed_mbps
-----------------+---------------+-----------+------------+-----------+------------+------------+------------+-----------+------------+-----------------
lo | 127.0.0.1 | 346994955 | 647421 | 0 | 0 | 346994955 | 647421 | 0 | 0 | 0
wlp2s0 | 10.0.21.188 | 868296583 | 1343188 | 0 | 0 | 1412729542 | 2475042 | 0 | 10 | 0
tailscale0 | 100.105.98.56 | 361225 | 2974 | 0 | 0 | 46928 | 530 | 0 | 0 | -1
br-ca2ffca7cc2e | 172.19.0.1 | 724806 | 2813 | 0 | 0 | 177922 | 1225 | 0 | 0 | 10000
br-66f2d4a9a4e1 | 172.20.0.1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1
br-8941f0656708 | 172.18.0.1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1
docker0 | 172.17.0.1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1
(7 rows)
Here
- TX = Transmit - data going out from your machine
- RX = Receive - data coming into your machine
Operating System Details
To fetch OS-level information:
SELECT * FROM pg_sys_os_info();
It provides:
- OS name and version
- Hostname
- Architecture
- Process count
- Thread count
- System uptime
Result :
name | version | host_name | domain_name | handle_count | process_count | thread_count | architecture | last_bootup_time | os_up_since_seconds
--------------------+------------------------+-----------+-------------+--------------+---------------+--------------+--------------+------------------+---------------------
Ubuntu 22.04.5 LTS | Linux 6.8.0-90-generic | cybrosys | lan | 17952 | 494 | 2480 | x86_64 | | 37604
(1 row)
This gives context about the environment PostgreSQL is running in.
Process Summary
To get a quick overview of system processes:
SELECT * FROM pg_sys_process_info();
Returns:
- Total processes
- Running processes
- Sleeping processes
- Zombie processes
Result :
total_processes | running_processes | sleeping_processes | stopped_processes | zombie_processes
-----------------+-------------------+--------------------+-------------------+------------------
494 | 1 | 373 | 0 | 0
(1 row)
A fast way to detect abnormal system states.
For DBAs and developers working deeply with PostgreSQL, especially source-level tuning like things, this extension removes the need to constantly switch between SQL and Linux tools like top, free, df, or iostat.
We can easily:
- Correlate query behavior with CPU usage
- Track memory pressure during heavy workloads
- Monitor disk I/O while testing indexes
- Observe network usage during replication or backups
All from inside PostgreSQL.
The system_stats extension turns PostgreSQL into a lightweight system monitoring console.
If you’re experimenting with PostgreSQL internals, building custom distributions, or optimizing databases for better performance, this extension is worth adding to your toolkit.
It’s simple to build, easy to query, and provides real operational insight with plain SQL.