0

Frequently we restore a Postgres DB from a RDS snapshot to an entirely new RDS instance. We notice that queries on certain tables perform really slowly but improve by a huge magnitude after running VACUUM ANALYZE on those tables. Queries on those tables are performing well on the original instance. This makes me think that a restored snapshot or db doesn't bring along table statistics that are helpful to queries but thats just a guess.

Any insights on what happens to ANALYZEd table stats when a db is restored? Are those stats not relevant because the file layout is now different for the new db and hence its always a good idea to ANALYZE after a restore

1
  • This seems unrelated to PostgreSQL. Jun 8 at 6:16
0

Whenever you restore a snapshot from your system(automated) snapshots those are stale as the snapshots might have taken at the time of snapshot creation, not the time you are restoring. However, you can check and validate the pg-statistic and pg_stat_all_tables this would give you a better idea of the stats of the restored cluster.

I am not sure why AWS hasn't mentioned this in the documentation but as a best practice, we used to follow VACUUM ANALYZE as the standard process post snapshot restoration. Although AWS recommends running the ANALYZE operation to refresh the pg_statistic table after you complete a major version upgrade,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.