Re: Vacuum full

On Wed, Nov 06, 2002 at 01:50:00PM +1100, Justin Clift wrote:
> Andrew Bartley wrote:
>
> > I am also aware there is an upper limit on the number of transactions
> > postgres can do before the DB needs vacuuming.  Somthing to do with tran ids
> > wrapping or something.  Do you know anything about this?
>
> Not my area.  One of the more-hard-core developers will be able to tell
> you about this though.  From memory though it happens around the 2 or 4
> billionth transaction mark, and something may have been done about it
> for the upcoming 7.3 release (we're in beta testing of this at present).

There has alwaus been a problem with transaction id wrap-around.
There is a maximum number of transactions the system can perform
before the transaction identifiers (which are required for MVCC to
work) wrap (the ids are int4, so the number is strictly speaking 4
billion).

Prior to 7.2.x, the only way to solve the problem was a complete
initdb-and-restore.

As of 7.2, there is a better solution; but the price of the solution
is that _every table_ in the database must be vacuumed at least once
every billion transactions.  See

<http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND>

(that's a long line.  Sorry.)

VACUUM without FULL doesn't block, however.  It will cost you almost
nothing to VACUUM nightly with cron.  Think Martha Stewart: It's a
Good Thing.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110