Re: Is it ever necessary to vacuum a table that only gets inserts/updates?

John R Pierce <pierce(at)hogranch(dot)com> writes:
> On 11/16/11 4:24 PM, Jason Buberel wrote:
>> Just wondering if there is ever a reason to vacuum a very large table
>> (> 1B rows) containing rows that never has rows deleted.

> no updates either?

To clarify: in Postgres, an "update" means an insert and a delete.
So unless you mean that this table is insert-only, you certainly
still need vacuum.

> you still want to do a vacuum analyze every so often to update the
> statistics used by the planner.

If it's purely an insert-only table, such as a logging table, then in
principle you only need periodic ANALYZEs and not any VACUUMs.

VACUUM could still be worthwhile though, because (a) it will set commit
hint bits on all pages and (b) it will set visibility-map bits on all
pages. An ANALYZE would only do those things for the random sample of
pages that it visits. While neither of those things are critical, they
do offload work from future queries that would otherwise have to do that
work in-line. So if you've got a maintenance window where the database
isn't answering queries anyway, it could be worthwhile to run a VACUUM
just to get those bits set.

regards, tom lane