Re: Vacuuming and re-indexing (was Re: Vacuum meaning)

No question vacuuming is ugly.  My hope is that some of the statistics
collection can be used to automatic vacuum for 7.4.  We have some folks
working on that.

---------------------------------------------------------------------------

David F. Skoll wrote:
> On Fri, 3 Jan 2003, Robert Treat wrote:
>
> > The purpose of a regular aka "lazy" vacuum is to mark dead tuples
> > generated from updates and deletions as reusable by future inserts and
> > updates. It doesn't recover any disk space, but allows currently wasted
> > space to be reused. "Full" vacuums, otoh, serve the purpose of
> > reclaiming unused space.
>
> The whole "vaccuum" concept is, IMHO, one of the weakest aspects of
> PostgreSQL.  It really makes it difficult to build products around
> PostgreSQL unless you have a good idea of the database churn, which
> isn't always possible.
>
> Is there any way to adaptively vacuum the database?  By this, I mean
> is it possible to run a query every few minutes and decide to vacuum
> based on the query results?
>
> > However, based on your 4:00 output, I'd say you need to start doing more
> > frequent lazy vacuums on user_logs_digest and user_traffic. Try and
> > determine how long it takes for 15% of those tables to be updated or
> > delete/insert and then set a cron task to do a lazy vacuum analyze at
> > that frequency. (This is in additional to regular vacuuming on the rest
> > of the database)
>
> Is there any query that can return that info?  A query that tells how
> many dead tuples there are?
>
> > It's possible your suffering index bloat as well and you might need to
> > do some regular reindexing,
>
> Again, this sounds ugly.  How do you determine this?  How do you get your
> database to be reindexed as often as needed, but not too often?
>
> --
> David.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073