Vacuum and inserts

From: Mark Wilden <mark(dot)wilden(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Vacuum and inserts
Date: 2008-06-18 19:29:46
Message-ID: 329d4944-864e-4995-a4b2-6ede8eeb20f8@u36g2000prf.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

My coworker and I are having an argument about whether it's necessary
to VACUUM an insert-only table.

My theory is that since there are no outdated nor deleted rows, VACUUM
doesn't do anything. I just loaded a TRUNCATEd table with no indexes
with 4 million records, indexed it, then ran VACUUM. There was nothing
to do. So I don't see VACUUM as necessary with an insert-only table
(not to be confused with ANALYZE, which clearly is necessary).

My coworker is mainly going from the article at
http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT
. In particular, the author says

"Consider this scenario: a row is inserted into a table that has a
couple indexes, and that transaction commits. Several updates happen
on that row. Each update will create a new row in all indexes, even if
the index key didn't change. And each update will also leave an old
version of the row in the base table, one that has been updated to
point to the location of the new version of the row that replaces it.
"

I don't get this at all. He starts by talking about inserts, but then
talks only about updates (and VACUUM is clearly needed when rows are
updated or deleted).

Can someone disambiguate?

adthanksvance

///ark

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shane Ambler 2008-06-18 19:30:18 Re: Database design: Storing app defaults
Previous Message Shane Ambler 2008-06-18 19:15:38 Re: Database design: Backwards-compatible field addition