Metrics with pg_proctab

pg_proctab is a set of stored functions installed as a Postgres extension that will let you access operating system statistics from the underlying server, such as I/O, processor load, and memory usage.

You can enable pg_proctab for Crunchy Bridge by running:


Once you've enable the extension you have access to new functions to monitor system metrics.

System load

You can query load using pg_proctab by running:

SELECT * FROM pg_loadavg()

pg_proctab will provide you with:

  • load1 — load average of last minute
  • load5 — load average of last 5 minutes
  • load15 — load average of last 15 minutes
  • last_pid — last PID running

Load is going to give you a number relative to the number of CPUs you have, so you’ll need to know the total number of cores/virtual CPUs available. As the load numbers get closer to the number of CPUs you have, you are running on the high end of load. On a single-core machine, you want the number below 1.0. Or for example, if you have 4 cores, you want the number to be below 4.0.

The below query will give you your percentage of load if you have a 4-core setup.

SELECT load15/4*100
FROM pg_loadavg();

Memory usage

To query memory usage you can run:

FROM pg_memusage();

The above will return:

  • memused
  • memfree
  • memshared
  • membuffers
  • memcached
  • swapused
  • swapfree
  • swapcached

Memory usage will show memory used, free memory, memory shared, swap used and free. If you start regularly seeing swap used, it's time to look at getting a provision with additional memory.

Here’s an easy one to check the free memory

SELECT pg_size_pretty(memfree*1024)
FROM pg_memusage();

CPU time and I/O

To monitor CPU and I/O you can run:

FROM pg_cputime();

This is going to get you:

  • user nice
  • system idle
  • iowait

User will give you all the non-system programs using cpu, system is the other system/kernel cpu process, and idle is the idle/free processes. So you can get pretty close to getting CPU usage by totaling idle, user, and system and diving by idle. The below example gives a sample of that:

SELECT idle/(idle + "user" + system)*100
FROM pg_cputime();