Subject: Re: Poor performance after restoring database from snapshot on AWS RDS

From: Jeremy Smith <[email-masked]>
Date: Wed, 5 Jun 2024 08:09:34 -0400

On Wed, Jun 5, 2024 at 4:23 AM Sam Kidman <[email-masked]> wrote:

> We get very poor performance in the staging environment after this
> restore takes place - after some usage it seems to get better perhaps
> because of caching.
>

This is due to the way that RDS restores snapshots.

>From the docs (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html):

You can use the restored DB instance as soon as its status is
available. The DB instance continues to load data in the background.
This is known as lazy loading.

If you access data that hasn't been loaded yet, the DB instance
immediately downloads the requested data from Amazon S3, and then
continues loading the rest of the data in the background.

  -Jeremy

---

From: Sam Kidman <[email-masked]>
Date: Thu, 6 Jun 2024 14:40:49 +1000

> This is due to the way that RDS restores snapshots.

Thanks, I never would have guessed. Would vacuum analyze be sufficient
to defeat the lazy loading or would we need to do something more
specific to our application? (for example. select(*) on some commonly
used tables)

I think vacuum full would certainly defeat the lazy loading since it
would copy all of the table data, but that may take a very long time
to run. I think vacuum analyze only scans a subset of rows but I might
be wrong about that.

Best, Sam