How do you check the top IOPs intensive queries in PostgreSQL DB?

IOPS stands for Input/Output Operations Per Second. It is a performance measurement used to benchmark computer storage devices like hard disk drives (HDDs), solid-state drives (SSDs), and storage area networks (SANs).

IOPS measures how many read or write operations a device can perform in a second. This metric is crucial for understanding the performance characteristics of storage systems, especially in environments where data must be accessed quickly and efficiently, such as in database servers, high-transactional systems, or any application that requires high-speed data access.

Checking IOPS for a PostgreSQL database instance is important for several reasons, as it directly impacts the performance, scalability, and overall efficiency of database operations. Like any database system, PostgreSQL relies heavily on disk I/O operations for reading from and writing to the database.

In this post, I’d like to demonstrate how to check the top IOPs intensive queries in the PostgreSQL DB Server.

Set PostgreSQL configuration parameter

track_io_timing = 1;

Query #1:

SELECT 
 (select datname from pg_database where oid=dbid) datname,
 query,
 blk_read_time + blk_write_time AS io_time
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 20;

Query #2:

with
a as (select dbid, queryid, query, blk_read_time r, blk_write_time w from pg_stat_statements),
b as (select dbid, queryid, query, blk_read_time r, blk_write_time w from pg_stat_statements, pg_sleep(1))
select
 pd.datname as db_name, 
 substr(a.query, 1, 2000) as the_query, 
 round(sum(b.r-a.r)) as blk_reads_per_sec,
 round(sum(b.w-a.w)) as blk_writes_per_sec,
 round(sum(b.r-a.r) + sum(b.w-a.w)) as iops
from a, b, pg_database pd
where 
 a.dbid= b.dbid 
and 
 a.queryid = b.queryid 
and 
 pd.oid=a.dbid
group by 1, 2
having sum(b.r-a.r) + sum(b.w-a.w) > 0
order by 5 desc
limit 20;