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