Re: How to avoid transaction ID wrap

Clinging to sanity, hannu(at)skype(dot)net (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.