Take the 2-minute tour ×

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.

  1. 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.

  2. 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.

share|improve this question

migrated from stackoverflow.com Feb 1 '11 at 11:07

This question came from our site for professional and enthusiast programmers.

Can you show us the results from EXPLAIN ANALYZE for a slow query on these tables? And what about configuration settings? work_mem, shared_buffers, maintenance_work_mem and some others, need some tweaking. 130MB is a small database, the whole database should be in RAM. –  Frank Heikens Feb 1 '11 at 7:24

1 Answer 1

When you're vacuuming, are you ANALYZE-ing?

Reindex shouldn't help: when you pg_restore all those indexes are being created from scratch anyway.

VACUUM by itself won't do anything: a newly restored database doesn't contain any dead tuples to get rid of.

Run ANALYZE across the entire database after a restore, this updates the statistics, allowing the query optimiser to generate efficient query plans.

share|improve this answer

Your Answer


