Re: Vacuum and inserts

In response to Mark Wilden <mark(dot)wilden(at)gmail(dot)com>:
>
> 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).

The only counter-argument to this is the case where transactions are
aborted/rolled back, which _does_ result in dead rows. If your
application doesn't usually do ROLLBACKs, this probably won't ever
be a problem. It's a great argument for autovacuum, however, which
won't bother to vacuum the table if it doesn't have any dead rows.

> 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?

I seems to me that it's your coworker that needs the disambiguation.
Based on the argument you describe, he doesn't seem to understand the
difference between UPDATE and INSERT.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023