On 14/02/2008, Michiel Holtkamp <michiel.holtkamp@xxxxxxxxxxxxxx> 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
