A never vacuumed PostgreSQL 7.0.3 database getting sluggish
Posted on 2006-05-08
I have a ~5 year old PostgreSQL 7.0.3 installation, which I don't believe has ever had the tender lover care of a database administrator, and yet it is reasonably complex. Performance has been reported to have deteriorated over the years, partly no doubt because if the increase in size of the data set.
Before I get into looking how the schema might be rationalised by (say) spooling data off non-current data into archives, I want to look into ways to improve performance, while leaving things intact. The database can afford to have some downtime over night say.
I looked briefly at taking a dump from the database and importing it into PostgreSQL 8, but there were oodles of errors thrown up, which I'd need to pick through, so I wanted to see if I could improve matters with the existing 7.0.3.
Here are my questions:
(1) Am I reasonably safe vacuuming it now, or would I be wiser to dump and regenerate it from scratch?
(2) Am I reasonably safe setting up a daily vacuum on cron thereafter?
(3) Is there anything else I ought to consider?