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

Thank you Tom & John.

In this case, there are no updates/deleted - only inserts. For now, I have
set per-table autovacuum rules in order to minimize the frequency of
vacuums but to ensure the statistics are updated frequently with analyze:

Table auto-vacuum VACUUM base threshold 500000000
Table auto-vacuum VACUUM scale factor 0.3
Table auto-vacuum ANALYZE base threshold 50000
Table auto-vacuum ANALYZE scale factor 0.02
Table auto-vacuum VACUUM cost delay 20
Table auto-vacuum VACUUM cost limit 200

On Wed, Nov 16, 2011 at 9:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

--
Jason L. Buberel
CTO, Altos Research
http://www.altosresearch.com/
650.603.0907