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(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110