Jesse Norell wrote:
Hello,We've been dealing with this issue quite recently (even with some assistance from Dan in #dbmail{,-dev} channels). The below recommendations are right on par with what I've found for pgsql maintenance (ie. sufficiently high fsm settings, and frequent vacuuming), but there also seems to be a bug in postgres that continues to grow the disk usage without bounds (well, the to the limits of your hardware :). It supposedly may be fixed in 7.4, but we haven't gotten to upgrade yet to test that. We're running 7.3.2, and apparently it's been an issue that's been around quite a while.
I assume the "fix" you are talking about is that starting with PostgreSQL 7.4 Vacuum reclaims index space. That resolves the major remaining file bloat problem with PostgreSQL. As of 7.4 as long as your FSM setting is high enough, and you vacuum often enough, you shouldn't have any problems with unbounded file growth. If you do, please let the people on the postgresql mailing lists know about it, they would like to look at it.
As for working around this in 7.3, to reclaim space in youn indexes, you have to periodically re-index your big tables. If you add that to your maintenace routines it should help alot. But better yet would be to upgrade to 7.4, there are lots o'improvements.
We only have about 5.5-6GB of actual data in our dbmail database, but usage keeps growing over time, eventually filling the 18GB available disk space. We ended up running periodic vacuum fulls from cronjobs (twice a month lately), and that does drop the used disk space down some, but it does continue to grow. As of this weekend, after running a vacuum full, still had > 16GB taken up for postgres. Last night we did a dump of all databases, dropped and recreated them, and are now inserting back in... not done yet, so I can't give the total end result, but the dump file itsself is only 4.8GB.
Again, in 7.3 even VACUUM FULL does not reclaim index space, so next time instead of doing a complete reload you might try doing a VACUUM FULL and a reindex on all the tables. Also, since it sounds like the VACUUM FULL only reclaims some of the space it sounds like either bloat or perhaps if you did this while the system was live, VACUUM might not have been able to get the exclusive lock it needs, so it may have skipped some tables, you can verify this by running VACUUM VERBOSE. Also, regular VACUUM doesn't require an exclusive lock.
Also, pg_autovacuum that is in 7.4 contrib should work against a 7.3 PostgreSQL server. If you really can't upgrace to 7.4 you might want to try running pg_autovacuum against your 7.3 database, again you will still need to reindex periodically but it should help.
Hope some of that helps. Matthew
