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
