I have a table that is essentially a log where new entries are streaming in continually and from time to time I throw
oldentries away to
keep the table from growing.
I understand that in addition to issue a
DELETE FROM log WHERE date < xxx
I also need to issue a
VACUUM log
so that new entries will use the space of deleted entries.
Now I want to reserve a certain amount of disk storage to hold the log table. So I first let the table (plus its index,
itstoast table and toast index) grow until it is about to reach the maximum size. Then a daemon continually deletes
oldentries and vacuums the table so the on-disk usage stays more or less constant from now on, at least this is the
idea.
Of course I would like to keep as much history as possible, given the available space. Also the log may sometimes be
quietand sometimes quite busy, also the size of the text entries may vary quite a bit.
Now to make a good guess about when to issue the next delete, I need to estimate how much of the on-disk usage is
accountedfor by deleted entries.
I can of course count the number of entries, estimate the bytes needed for storage by averaging the length of the text
column,adding the size of the fixed columns and compare that to the on-disk size to conclude how much space is still
available.As for the index I assume it is has a fixed size per row.
But these queries are expensive because the log may easily contain millions of entries with an on disk size in the
rangeof a few GB, and must be repeated quite often to prevent sudden bursts of new entries from overflowing the log.
Is there a better way to get at the current "free space" inside of a table/index?
__________________________________________________________
Mit WEB.DE FreePhone mit hoechster Qualitaet ab 0 Ct./Min.
weltweit telefonieren! http://freephone.web.de/?mc=021201