Clinging to sanity, [EMAIL PROTECTED] (Hannu Krosing) mumbled into her beard: > Ühel kenal päeval, T, 2006-06-06 kell 08:42, kirjutas Mark Woodward: >> OK, here's my problem, I have a nature study where we have about 10 video >> cameras taking 15 frames per second. >> For each frame we make a few transactions on a PostgreSQL database. >> We want to keep about a years worth of data at any specific time. > > partition by month, then you have better chances of removing old data > without causing overload/data loss;
It's certainly worth something to be able to TRUNCATE an elderly partition; that cleans things out very nicely... >> We have triggers that fire is something interesting is found on insert. >> We want this thing to run for a log time. >> From the numbers, you can see the PostgreSQL database is VERY loaded. >> Running VACUUM may not always be possible without losing data. > > why ? just run it with very friendly delay settings. "Friendly delay settings" can have adverse effects; it is likely to make vacuum run on the order of 3x as long, which means that if you have a very large table that takes 12h to VACUUM, "vacuum delay" will increase that to 36h, which means you'll have a transaction open for 36h. That'll be very evil, to be sure... >> The numbers I have amount to 466,560,000 transactions per month, lasting a >> maximum of about 9 months until XID wrap. > > actually 4.5 months as you will start having problems at 2G xacts. Right. >> I am thinking about a few work arounds, BEGIN/COMMIT to reduce the number >> of transactions, COPY, etc. so I'm not dead in the water, but I would be >> interested in any observations yo may have. Grouping work together to diminish numbers of transactions is almost always something of a win... -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/rdbms.html Roses are red, Violets are blue, I'm schizophrenic... And I am too. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster