Postgresql server causing a lot of IO activity

Question

I have a postgresql server running on a small linode partition. It is running a web app for a site which gets on average 0 users / minute (it has not launched yet). At one point, I loaded up the database with a lot of data, to do a stress test. I have since deleted the data and now have a few hundred megabyte DB just sitting there without anyone asking anything of it.

The linode server is reporting an average IO rate of 2.6k blocks a second. I ran pidstat -d 10 90 and figured out that the culprit was postgres:

Average:          PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
Average:        28836   1251.31      0.00      0.00  postgres

Further digging shows that approximately once every three minutes postgresql decides to do some heavy lifting (a short burst of reading at 21mb/s).

I know the bare minimum when it comes to DB administration and have an issue. My research unearthed things like VACUUMing and such. However, I am not sure what is happening, why, and how to stop it. There is absolutely no load on this machine. Can anyone point me in the right direction?

Answer 1

If you have added a lot of data and later deleted it, most likely Postgres starts auto-vacuuming process on your tables.

Auto-vacuum is designed to be rather gentle and not put heavy load on running server - otherwise it will significantly reduce system performance if it was serving live data.

Since your database is mostly empty and does not have any clients, you can fix this by performing full vacuum:

VACUUM FULL VERBOSE ANALYZE;

On a few hundred megabytes database it should not take more than a few minutes, and should stop any further activity on your database.

However, it is not recommended to perform full vacuum on production database because it locks tables as it goes - do simple VACUUM instead, or rely on auto-vacuum.