On 14/02/2008, Michiel Holtkamp <[EMAIL PROTECTED]> 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
