To: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: why vacuum 
In-reply-to: <200510261015.17623.lawgon@thenilgiris.com> 
References: <200510261015.17623.lawgon@thenilgiris.com>
Comments: In-reply-to Kenneth Gonsalves <lawgon@thenilgiris.com>
	message dated "Wed, 26 Oct 2005 10:15:17 +0530"
Date: Wed, 26 Oct 2005 01:14:40 -0400
Message-ID: <2123.1130303680@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kenneth Gonsalves <lawgon@thenilgiris.com> writes:
> i was in a minor flame war with a mysql guy - his major grouse was that 
> 'I wouldnt commit mission critical data to a database that needs to be 
> vacuumed once a week'.

This guy is not worth arguing with.

> So why does pg need vacuum?

Every database needs maintenance operations.  PG is designed in a way
that exposes the maintenance operations to the control of the DBA a bit
more than most other DBMSes do: specifically, you get to decide when
some of the overhead work happens.  We think this is a feature, because
you can schedule the overhead for low-activity periods (nights,
weekends, whatever).  In other DBMSes the equivalent work happens as
part of foreground queries, no matter how time-critical they might be.

Now, there's no doubt that for a database run by a non-expert person
who can't even spell DBA, exposing this sort of knob isn't very helpful.
So there's work afoot to provide automatic maintenance tools (ie,
autovacuum).  Over time I think autovacuum will get smart enough that
even experts will usually use it.  But that point will only be reached
when autovacuum has some idea about doing more work during low-load
periods.

Unless MySQL invents some concept equivalent to VACUUM, they won't have
any prayer at all of being able to shift maintenance overhead to
low-load times.

			regards, tom lane