Purging PostgreSQL Databases

Do they pose any problem?

[windspinner]

We have a relatively huge PostgreSQL database for a web application. It has around 27 Million records more or less. Just last Friday, I purged old unnecessary data from our PostgreSQL database the 27 Million shrank to 3 Million. Thing is, I got performance problems then on.

The Web-app runs on a Linux box. The database lies on the /var directory, which only has 8% (1.5 GB out of 19 GB) available space even after the purging. The /var directory has other historical files in it, however, and just recently the system got into some trouble. I purged the files and the system got better.

Question: is it normal for postgresql (postmaster) to have a CPU utilization of 56% at a given time (this is the highest CPU utilization of postgresql I encountered)? Did my purging affect PostgreSQL's storage and indeces? Why didn't my database size decrease after the purging?

Thanks for any help anyone can give.

[zCat]

You probably need to issue a "VACUUM ANALYZE" and afterwards a "VACUUM FULL" to update the index statistics and reclaim unused space.

[bcc1234]

First run full, then analyze.

But keep in mind, when you run full, the db will be locked and your site won't function.

Also, I'm pretty sure you'll hit the vacuum limit and will need to change some parameters.