Re: How to avoid transaction ID wrap

On Tue, 6 Jun 2006, Mark Woodward wrote:

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

I would suggest doing all the inserts of one frame in one transaction.
Maybe even multiple frames in one transaction. That should bring down the
number of transactions significantly.

> We want to keep about a years worth of data at any specific time.
> 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 not?

> The numbers I have amount to 466,560,000 transactions per month, lasting a
> maximum of about 9 months until XID wrap.

If you can get that maximum up above one year (which was how long you want
to keep the data), you won't need to freeze the records to
avoid ID wraparound.

- Heikki