Re: Minimally avoiding Transaction Wraparound in VLDBs

Tom,

> If you're using autovacuum then the problem is already taken care of.
> It will be taken care of better in 8.2, if we add per-table tracking
> of XID wraparound risk, but it's handled now.  The only way that this
> proposal makes any sense is if you are trying not to vacuum at all, ever.

Hmmm ... the potential problem which Simon is mentioning is very real ... in
large DWs, there may be tables/partitions which are never, ever vacuumed.
Ever.

For example, at one client's site they load their data via ETL jobs that
insert about 25 million rows a day and update about 100,000. Given that
updates are < 5%, there is no reason from a data efficiency perspective to
ever vacuum. So that's the plan ... the main fact table will never, ever
be vacuumed. (in that particular case, since the ETL uses large transaction
batches, XID wraparound won't happen for about 20 years. But with a
different data load model, it could be a serious problem).

So, will per-table XID tracking allow us to avoid *ever* vacuuming some
tables? If not, what could?

--
Josh Berkus
Aglio Database Solutions
San Francisco