We have a system that archives data to a postgres database. The raw
data is confined to one table. Each record in the table is generally
1500 bytes. Each record is also associated with a volume name. During
normal operations, many millions of rows are written to this table.
After sometime the disk gets close to being full.
We have a program that allows the user to offload rows from a table
based upon volume name to a CD. This is done by doing a select on the
table and then creating a memento record for each row and persisting it
to the CD. Once the rows are written to the CD a delete is performed on
the table for the off-loaded rows.
select * from packets where volume = 'abc';
... process to write to CD ...
delete from packets where volume = 'abc';
We perform a vacuum full after each mass delete. This cycle can happen
many times during over a couple of weeks. We are in a test lab
environment and are generating a lot of data.
One of the problems we have is that the vacuum on the table can take up
to 10 hours. We also expect to see the physical disk space go down, but
this does not happen. If we accidently fill up the disk, then all bets
are off and we are unable to recover. A vacuum never seems to finish
(several days).
How can we physically recover "real" disk space for the rows that were
deleted from the table? I've heard about free space buffers, but am not
really sure how they work. Are there configuration items that can be
tweaked to help with vacuuming large tables?
Thanks!!!!