Thread: VACUUM and transaction ID wraparound
The documentation says (Admin Guide, 8.2.3), "... each database-wide VACUUM automatically delivers a warning" if individualtables need VACUUMing to prevent transaction ID wraparound. But I thought a database-wide VACUUM automatically VACUUMed each table in the database (Ref Manual, 'VACUUM'), making (atleast to my way of thinking) VACUUMing an individual table unnecessary, and thus making the warning superfluous. Is there something here I don't understand? -- Dean
On Fri, May 16, 2003 at 09:11:19PM -0700, Dean Gibson (DB Administrator) wrote: > The documentation says (Admin Guide, 8.2.3), "... each database-wide VACUUM automatically delivers a warning" if individualtables need VACUUMing to prevent transaction ID wraparound. > > But I thought a database-wide VACUUM automatically VACUUMed each table in the database (Ref Manual, 'VACUUM'), making (atleast to my way of thinking) VACUUMing an individual table unnecessary, and thus making the warning superfluous. > > Is there something here I don't understand? I beleive they are referring to the difference between VACUUM and VACUUM FULL. The former is faster and doesn't lock tables, but the latter is required to solve transaction wraparound. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
"Dean Gibson (DB Administrator)" <dba-sql@ultimeth.net> writes:
> The documentation says (Admin Guide, 8.2.3), "... each database-wide
> VACUUM automatically delivers a warning" if individual tables need
> VACUUMing to prevent transaction ID wraparound.
Not individual tables, but whole databases (databases other than the one
you just vacuumed, obviously).
> Is there something here I don't understand?
The warning is intended to alert you in case you have entire databases
you've forgotten to vacuum for a good long while. It's a compromise
between being too much in-your-face and letting you go down to
disaster...
regards, tom lane
On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote:
> I beleive they are referring to the difference between VACUUM and VACUUM
> FULL. The former is faster and doesn't lock tables, but the latter is
> required to solve transaction wraparound.
It is? I didn't think VACUUM FULL was ever required.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote:
>> I beleive they are referring to the difference between VACUUM and VACUUM
>> FULL. The former is faster and doesn't lock tables, but the latter is
>> required to solve transaction wraparound.
> It is? I didn't think VACUUM FULL was ever required.
Either one will get the job done as far as avoiding wraparound goes.
If there's someplace in the docs that seems to you to give a different
impression, please tell me where.
regards, tom lane
On Wed, 21 May 2003, Andrew Sullivan wrote: > On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote: > > > I beleive they are referring to the difference between VACUUM and VACUUM > > FULL. The former is faster and doesn't lock tables, but the latter is > > required to solve transaction wraparound. > > It is? I didn't think VACUUM FULL was ever required. I'm not sure if it's vacuum full or vacuum, but I do know you have to vacuum ALL the tables in ALL your databases to prevent wrap around. Full or not, I'm not really sure. But I run fulls every night anyway.
On Thu, May 22, 2003 at 10:11:17AM -0400, Tom Lane wrote: > Andrew Sullivan <andrew@libertyrms.info> writes: > > On Sat, May 17, 2003 at 02:34:19PM +1000, Martijn van Oosterhout wrote: > >> I beleive they are referring to the difference between VACUUM and VACUUM > >> FULL. The former is faster and doesn't lock tables, but the latter is > >> required to solve transaction wraparound. > > > It is? I didn't think VACUUM FULL was ever required. > > Either one will get the job done as far as avoiding wraparound goes. > If there's someplace in the docs that seems to you to give a different > impression, please tell me where. Sorry, must be my memory. I remember something about requiring a VACUUM FULL at least once every two billion transactions. I must have misremembered, sorry. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington