1

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

Improve this question
2
  • This seems unrelated to PostgreSQL.
    – Laurenz Albe
    Commented Jun 8, 2021 at 6:16
  • FWIW, it is has been very helpful and may I say, quite related to the combination "PostgreSQL RDS and restoration of snapshots". With Amazon pushing EOL on some 9.x versions, more people may run into this soon.
    – LMSingh
    Commented Nov 30, 2021 at 18:17

1 Answer 1

Reset to default
1

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,

Improve this answer
1
  • Thanks Nikhil. After a similar upgrade, as in the original question of this thread, PostgreSQL was stuck in a loop doing repeated entries with autovacuum but in each vacuum operation doing nothing because all tuples were frozen. VACUUM FULL helped break the loop. I had to do other tuning as well, but the problem seemed to come from a corrupted visibility map.
    – LMSingh
    Commented Nov 30, 2021 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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