Re: How to cope with low disk space

On 14/02/2008, Michiel Holtkamp <michiel(dot)holtkamp(at)soundintel(dot)com> wrote:
>
> Hello list,
>
> Just joined, read the archives, but couldn't find a solution to my
> problem. My question is: 'How can I best determine when to delete
> data?'. Hopefully this is the right place to ask, otherwise kindly
> redirect me to the proper list.
>
> The situation is as follows: we use PostgreSQL 8.1 to store large
> amounts of data (we are talking GB's). This data is stored as large
> objects and when we delete data, we don't forget to delete the
> corresponding large objects as well. The data stored is deleted after a
> while (usually a couple of weeks), so far so good.
>
> Due to the nature of the information (sound data, recording triggered on
> certain technical details) the amount of information is not very
> predictable. Sometimes a lot of data is stored over a period of a few
> days and the disk runs out of free space (this is not theoretical, in
> one case it happened already). For this situation we decided that we
> don't mind deleting some data earlier than normal, to ensure that we can
> store newly generated data (newer data is more important than older data).
>
> The problem:
> Somehow we have to decide when to delete data earlier than normal. We
> can't do this by checking disk-space, because postgres reserves
> disk-space. Freeing disk-space can be done by doing a full vacuum, but
> this locks tables and could cause data to be lost, besides I don't mind
> that postgres reserves tables, it's more efficient anyway.
>
> If anyone has ideas about this problem, it would be greatly appreciated,
> I'm sure this is a problem encountered by more persons. I've already
> looked at certain system tables (specifically pg_stat_user_tables) and
> at docs like: http://www.postgresql.org/docs/8.1/static/diskusage.html
> but so for no satisfying solution emerged.
>
> Thanks,
> Michiel Holtkamp
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

I think you need to know depending on a mix of free disk space and free
space map usage. If you do a standard Vacuum Verbose it will tell you how
full the fsm is. You need to ensure that you have enough free disk space and
or a (relativly) full fsm. When the fsm is empty the database has to use
disk space,

I probably not making any sence