We are running postgres 14.12 in rds and expirence very slow IO reads.. around 30MB/s on index scans. we can't figure out what might be the cause of it. any ideas to what we should / could check?
configuration
instance class: db.m6idn.8xlarge (which should support 3125MB/s throughput)
RAM: 128GB
vCPU: 32
storage type: gp3 with 25000 IOPS (we only reach 18K) and 4000MiB/s throughput.
most of our slow queries are due to slow IO read..
I am not familiar with AWS but,
IOPS * IO Size= Throughput
PostgreSQL by default using 8KB block size(not sure whether AWS using different size), which is optimised towards OLTP workloads to get higher IOPS than throughput.
With this lower block size, you can have your maximum IOPS at 200MB/s which is in the limit of your storage. However you cannot have your maximum throughput because the required IOPS is far beyond the limit of 25k. To get maximum throughput=3125MB/s(capped by instance) with current IOPS limit you have to change block size to 128KB. But I don't think its possible to do also such block sizes works better with OLAP.
Additionally if you having too many IO reads you might consider tuning indexes, check fragmentation and if necessary memory adjustments.
