Subject: RDS IO Read time

From: Eden Aharoni <[email-masked]>
Date: Mon, 31 Mar 2025 13:54:20 +0000

Hi all,
Hopefully, someone here could help us understand whats going on with our deployment..
We are running Postgres 17.4 on AWS RDS on an ec2 instance that has:

  *   32vCPU
  *   128GB RAM
  *   gp3 with 25K IOPS and 4000MiB/s throughput
  *   the instance supports up to 3125GB/s of throughput.
Whenever a query fetches data from the disk (index scan, bitmap scans, etc.) we’re reaching 23 – 30MB/s of IO Read time.
We changed our autovacuum settings to be more aggressive (0 scale factor and 10K threshold) so our index only scans will (hopefully) read data from the disk rarely. However, we can’t optimize all the queries to use index-only-scan since our users can dynamically select which columns to see and filtering.
Is this expected IO read rate? I can’t help but feel we’re missing something here..

Thanks a lot! 😊

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 31 Mar 2025 08:08:00 -0700

On 3/31/25 06:54, Eden Aharoni wrote:
> Whenever a query fetches data from the disk (index scan, bitmap scans, 
> etc.) we’re reaching 23 – 30MB/s of IO Read time.
> 
> We changed our autovacuum settings to be more aggressive (0 scale factor 
> and 10K threshold) so our index only scans will (hopefully) read data 
> from the disk rarely. However, we can’t optimize all the queries to use 
> index-only-scan since our users can dynamically select which columns to 
> see and filtering.
> 

RDS is a black box controlled by AWS, you are going to need to reach out 
to their tech support.

-- 
Adrian Klaver
[email-masked]

---

From: Christophe Pettus <[email-masked]>
Date: Mon, 31 Mar 2025 08:15:39 -0700

> On Mar 31, 2025, at 06:54, Eden Aharoni <[email-masked]> wrote:

Really, no particular I/O rate is "expected": if PostgreSQL needs that much data, it'll use that much I/O to get it.  From your description, it's likely that it's a case of the working set for the database just not fitting into the memory you have, so PostgreSQL needs to go out to secondary storage a lot to fetch the data.

The best first step is to use Performance Insights to see which queries are using I/O, and run sample ones with EXPLAIN (ANALYZE, BUFFERS) to see where the I/O is being used within the query.  Given that you allow users to assemble arbitrary queries, it's likely that PostgreSQL is having to use a wide variety of indexes (or sequential scans), so it can't successfully cache a particular set in memory.

---

From: Eden Aharoni <[email-masked]>
Date: Mon, 31 Mar 2025 17:32:15 +0000

First, thanks for the reply :)
So, I do know which part is taking a lot of IO time and it's to be honest any node that reads from the disk.. of course, we're running EXPLAIN on our queries (to be more specific we use auto_explain) but we can't seem to find what could cause an index scan that reads 34 MB to take more than a second (1.2 sec).. we do know that our dataset doesn't fit the memory and we were ok with that but with IO being so slow we just don't know what to do anymore. Any other suggestions other than contact AWS (which we did but it seems this path won't lead anywhere).

Thanks again

Get Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Christophe Pettus <[email-masked]>
Sent: Monday, March 31, 2025 6:15:39 PM
To: Eden Aharoni <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: [EXTERNAL] Re: RDS IO Read time

[You don't often get email from [email-masked]. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

---

From: Christophe Pettus <[email-masked]>
Date: Mon, 31 Mar 2025 10:43:19 -0700

> On Mar 31, 2025, at 10:32, Eden Aharoni <[email-masked]> wrote:
> 

The performance of EBS is definitely a question for AWS.  I can say that, in our experience, EBS mounts for RDS almost never approach the stated maximum throughput, although io2 tends to be closer than gp2 or gp3.