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

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.