Re: How to avoid transaction ID wrap

Ü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;

> 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.

> 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.

> 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.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com