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

From: Sam Kidman <[email-masked]>
Date: Mon, 3 Jun 2024 18:06:18 +1000

We keep the staging environment of our application up to date with
respect to production data by creating a new RDS instance for the
staging environment and restoring the most recent production snapshot
into it.

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.

The staging RDS instance is a smaller size than production (it has
32GB ram and 8 vCPU vs production's 128GB ram and 32 vCPU) but the
performance seems to much worse than this decrease in resources would
account for.

I have seen some advice that vacuum analyze should be run after the
snapshot restore but I thought this was supposed to happen
automatically. If we did run it manually how would that help?

Are there any other tools in postgres we can use to figure out why it
might be so much slower?

Best

--

Sam Kidman

Web Developer

Melbourne

---

From: Shammat <[email-masked]>
Date: Wed, 5 Jun 2024 10:31:04 +0200

> automatically. If we did run it manually how would that help?

autovacuum will kick in eventually - but only after some time (which
is what you are seeing).

In general after a bulk load (e.g. restore of a backup or importing
data in any other way) running vacuum to udpate statistics is highly
recommended