Re: How to avoid transaction ID wrap

On Tue, 2006-06-06 at 13:53 -0400, Christopher Browne wrote:
> 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...

With one potential snafu -- it blocks new SELECTs against the parent
table while truncate runs on the child (happens with constraint
exclusion as well).

If your transactions are short then it won't be an issue. If you have
mixed length transactions (many short which the occasional long select)
then it becomes tricky since those short transactions will be blocked.

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

Sounds like this is almost strictly inserts and selects though. If there
is limited garbage collection (updates, deletes, rollbacks of inserts)
required then it isn't all that bad.

--