Monitoring the load on a PostgreSQL Server

To monitor load on a PostgreSQL with details like source, query, latency, count, CPU usage, memory usage, disk reads, and disk writes, you can use the following SQL script:

SELECT
COALESCE(application_name, ‘<unknown>’) AS source,
query,
(total_time / 1000) AS latency_seconds,
calls AS count,
(total_time / calls) AS avg_time_per_call,
(100 * (cpu_time / (total_time + 0.00001))) AS cpu_percent,
(100 * (blk_read_time / (total_time + 0.00001))) AS read_percent,
(100 * (blk_write_time / (total_time + 0.00001))) AS write_percent,
shared_blks_hit AS hits,
shared_blks_read AS reads,
shared_blks_dirtied AS dirtied,
shared_blks_written AS written,
temp_blks_read AS temp_reads,
temp_blks_written AS temp_written
FROM
pg_stat_statements
CROSS JOIN LATERAL pg_stat_activity
WHERE
query <> ‘<IDLE>’
ORDER BY
total_time DESC;

This script joins the pg_stat_statements view with the pg_stat_activity view to get information about the SQL statements being executed, as well as information about the active connections to the database.

The script calculates the following metrics:

  • source: The application name or connection string of the client issuing the query.
  • query: The text of the SQL statement being executed.
  • latency_seconds: The total time spent executing the query, in seconds.
  • count: The number of times the query has been executed.
  • avg_time_per_call: The average time spent executing the query per call.
  • cpu_percent: The percentage of total query time spent on CPU processing.
  • read_percent: The percentage of total query time spent on disk reads.
  • write_percent: The percentage of total query time spent on disk writes.
  • hits: The number of shared buffer hits for the query.
  • reads: The number of shared buffer reads for the query.
  • dirtied: The number of shared buffers dirtied by the query.
  • written: The number of shared buffers written by the query.
  • temp_reads: The number of temporary files read by the query.
  • temp_written: The number of temporary files written by the query.

By monitoring these metrics, you can gain insight into the performance and resource utilization of individual queries and connections to the database.