Re: How to cope with low disk space

In response to Michiel Holtkamp <michiel.holtkamp@soundintel.com>:

> 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.

Since you don't give any idea how much data is involved, let me iterate
through your choices, given the unknowns:

1) Buy more disk space.  It's cheap.  Get an external SCSI unit and a
   SCSI PCI card.
2) Work on your vacuum schedule.  You don't need to vacuum full all the
   time, and regular vacuum doesn't lock tables.  If you do regular
   vacuum often enough, you won't see significant bloat.
3) Reduce the data size.  You say this is audio data, can you reduce
   the bitrate or other storage factors so the data streams aren't so
   huge?

In general, if you're hitting the limits of your physical storage on
a regular basis, you either underestimated your storage requirements
(which means you should get more storage) or you're storing too much.

The _business_need_ needs to dictate how often you purge old data.
If you don't have enough hardware to meet the business need, you need
to add hardware.

If the business need is to store X gigabytes with no regard for how
old the data is, then you need to adjust your data storage methods
to work with that.  Create a table to store the size of each LO, and
run a regular maintenance job that purges old data when the used
size gets too big.

--
Bill Moran
http://www.potentialtech.com