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

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