I have a backup routine that replicates the production database onto our staging server. It works without issues, but the staging database becomes unbearably slow after I restore from one of the snapshots. Slow to the point that my web server just times out and 502's.
I checked through to see what could be causing this, and I've isolated it to two large tables. We have a certain sessions table with several large TEXT fields in it. We're storing some unstructured data and are currently holding it in TEXT fields. Once I trim that table down (by deleting many of the rows), the staging environment accelerates and begins acting normally again.
I've gone through the routine of pg_reindexing the database, and have tried VACUUMing, but it's not making much of a difference. It's still really slow. Pages that don't need to access the two large tables run normally; pages that do don't load.
In short, I'm looking for help to answer two questions.
Is there a better way of doing database duplication from production to staging if my database dump is > 130 MB that will not cause this issue? I was under the impression that 130MB is not a very large database. I suspect it's all in the large session table with the TEXT fields that I spoke about earlier.
Is there any way I can optimize this table? Are there any tweaks that I can make after pg_restoring? I'm afraid that I'm going to have to restore from one of these backups on a rainy day and that it's going to affect the whole site's performance.
Thanks in advance.