Re: VACUUM ANALYZE -vs- ANALYZE on an insert-only table.

From: "Matt Gordon" <m(dot)gordon(at)f5(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: VACUUM ANALYZE -vs- ANALYZE on an insert-only table.
Date: 2003-12-16 20:26:43
Message-ID: 47BC08C8E02BA54EBD901AB9328BFBC09F10E5@exchtwo
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Our transactions rarely fail so I think we'll be okay on that front.

I'll look into moving up to at least v7.2.4 when I get some time.

Thanks Tom.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, December 15, 2003 3:02 PM
To: Matt Gordon
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] VACUUM ANALYZE -vs- ANALYZE on an insert-only
table.

"Matt Gordon" <m(dot)gordon(at)f5(dot)com> writes:
> If I have a table that I only use for INSERTs and queries (no UPDATEs
> or DELETEs), is it enough to just run ANALYZE on the table instead of
> VACUUM ANALYZE? In other words, is running a VACUUM on a table useful
> if all that you're doing is INSERTing into it?

It's of marginal value: it ensures that the commit status bits of the
table's rows are up-to-date, which can save work for subsequent SELECTs.

You *must* vacuum every table in your database at least once every
billion transactions to avoid transaction wraparound problems; and in
practice you probably want to do it more frequently than that to avoid
unreasonable growth of the pg_clog/ files. But most people don't need
daily VACUUMs to meet that goal...

One caveat: do any of your inserting transactions ever fail? If so, you
need VACUUM to clean up any dead tuples they may have inserted before
failing.

> If it matters, we're currently using Postgres 7.2.1.

You should get yourself to 7.2.4 posthaste, if not 7.3.5 or 7.4. There
were some really nasty bugs fixed between 7.2.1 and 7.2.4.

regards, tom lane

Browse pgsql-general by date

  From Date Subject
Next Message joseph speigle 2003-12-16 20:40:11 Re: selecting into a variable like @var=select ...
Previous Message Matt Gordon 2003-12-16 20:11:10 Re: VACUUM ANALYZE -vs- ANALYZE on an insert-only table.