I had been using Postgres database version 6.5.3. Initially, I had the
problem of the data files growing very huge and thereby affecting the
performance of the database. However, on employing vacuum regularly, I
found that the size of files occupied on the disk would reduce except
for the INDEX FILES. It was rather funny to see that even though there
are no records in the table, the index file was occupying about 6-8MB.
The only solution I could find was to delete and recreate the index.
When I migrated to Postgres 7.x (7.0.3 and 7.1.1), I find that the
concept of a separate transaction log has been introduced (This was
located as pg_log in 7.0.3 in the data directory and as a numbered file
in the global directory under the data directory). I find that if there
are repeated periodic operations on the postgres database, the
transaction log size increases very rapidly (I was aghast to see that
after initializing a new database with only 2 tables and a program
polling one of the tables every 20 secs, the transaction log had grown
to 120MB overnight) and there appears to be no mechanism of reducing the
log size except for taking a dump, reinitializing the database and
reimporting the dump.
I tried vacuuming the database(s) and unfortunately did not succeed in
reducing the overall size of the log. I have tried truncating the log
file size by using some of the unix tools/programs but any tampering of
the log renders all the databases/tables useless.
Dumping the database and restoring it is too cumbersome to be done on a
day to day basis. My question is whether after vacuuming, the cleared
space in the log file reused and also whether the overall performance of
the DB is affected by a large log file on disk. Further, I would like
to know if there is any mechanism of releasing the unused space from the
log.
Thanks
Vikaas
