Re: Controlling maximal on-disk size of a table

What you're looking for sounds like and extended (destructive) version
of what autovacuum does.

So you might try to look at the statistics tables like autovacuum does.
I don't know how it does that, but it seems that that way you'd be able
to incrementally have approximate information about what happens with
the table.

Good luck,

d.
--
David Helgason,
Business Development et al.,
Over the Edge I/S (http://otee.dk)
Direct line +45 2620 0663
Main line +45 3264 5049

On 16. nov 2004, at 13:21, Nils Rennebarth wrote:

> I have a table that is essentially a log where new entries are
> streaming in continually and from time to time I throw old entries
> 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, its toast table and
> toast index)  grow until it is about to reach the maximum size. Then a
> daemon continually deletes old entries 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 quiet and 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 accounted for 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 range of 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>