On Fri, 2010-02-12 at 18:09 +0100, Marcin Krol wrote:
> Hello,
>
> The db in the application I maintain but didn't write (it obviously
> makes use of PG, v 8.3), has been systematically growing in size from
> about 600M to 1.6G.
>
> At the same time, the performance of the app has degraded significantly
> (several times).
This is usually an indication that you are not vacuuming frequently
enough.
> So I've done VACUUM ANALYZE on entire db. Nothing. The db did not
> decrease in size, the performance stayed the same.
Regular vacuum will not decrease the size of the database. It will just
mark the dead tuples available for re-use.
VACUUM FULL will remove the dead tuples from the database, and make it
smaller. It's a bad idea to get in the habit of doing this. It's
better to tune autovacuum and avoid the issue altogether.
> So I backed it up using pg_dump, deleted database, and recreated it from
> backup.
>
> The size of db on disk went down to 600M, performance recovered to the
> original level.
Reloading the database eliminates the dead tuples, which is why the
database is smaller.
> Why that is so? I thought that VACUUM ANALYZE does everything that is
> needed to optimize disk usage?
It may not do everything, but the idea is to VACUUM your tables often
enough that they don't grow out of control.
Are you running autovacuum? It should take care of this for you. You
may need to make it more aggressive than the default though.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.