Thread: VACUUM ANALYZE -vs- ANALYZE on an insert-only table.
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 thetable instead of VACUUM ANALYZE? In other words, is running a VACUUM on a table useful if all that you're doing is INSERTinginto it? My understanding of VACUUM is that it cleans up stale tuples that are left after UPDATEs and DELETEs. The reason that I'm asking this has mainly to do with performance. I've got a lot of large tables (> 1 million records)in my database. Running a nightly VACUUM ANALYZE takes a while (> 1 hour) to run. I'm wondering, given the conditionabove, if I can skip the VACUUM part for these large tables and just run ANALYZE. If it matters, we're currently using Postgres 7.2.1. -Matt.
On Thu, Dec 11, 2003 at 15:26:56 -0800, Matt Gordon <m.gordon@f5.com> wrote: > If I have a table that I only use for INSERTs and queries (no UPDATEs or DELETEs), is it enough to just run ANALYZE onthe table instead of VACUUM ANALYZE? In other words, is running a VACUUM on a table useful if all that you're doing isINSERTing into it? My understanding of VACUUM is that it cleans up stale tuples that are left after UPDATEs and DELETEs. > You only need to vacuum if you do updates, deletes or when you need to handle wrap around of transaction IDs (about every 10^9 transactions).
"Matt Gordon" <m.gordon@f5.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
That's what I suspected. Thanks Bruno. -----Original Message----- From: Bruno Wolff III [mailto:bruno@wolff.to] Sent: Monday, December 15, 2003 1:38 PM To: Matt Gordon Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] VACUUM ANALYZE -vs- ANALYZE on an insert-only table. On Thu, Dec 11, 2003 at 15:26:56 -0800, Matt Gordon <m.gordon@f5.com> wrote: > If I have a table that I only use for INSERTs and queries (no UPDATEs or DELETEs), is it enough to just run ANALYZE onthe table instead of VACUUM ANALYZE? In other words, is running a VACUUM on a table useful if all that you're doing isINSERTing into it? My understanding of VACUUM is that it cleans up stale tuples that are left after UPDATEs and DELETEs. > You only need to vacuum if you do updates, deletes or when you need to handle wrap around of transaction IDs (about every 10^9 transactions).
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@sss.pgh.pa.us]
Sent: Monday, December 15, 2003 3:02 PM
To: Matt Gordon
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] VACUUM ANALYZE -vs- ANALYZE on an insert-only
table.
"Matt Gordon" <m.gordon@f5.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