Re: Postgresqlism & Vacuum?

On Fri, 14 Apr 2000, Thomas wrote:

> For large 24x7 installations, it's impossible to vacuum nightly because when
> postgresql is vacuuming the table is locked up, to the end-user the database
> has already hung.

That's right. I complained about this in a discussion with a Postgresql
developer, who assured me they were working towards a fix. I really don't
care whether the vacuuming is fixed so that it does not lock the table
completely, or that vacuuming becomes say, a once-a-month or less frequent
operation. For some reason everyone who is used to working with PostgreSQL
accepts the fact that you have to vacuum nightly - to outsiders it seems
like a major flaw with the system.

> There has been effort to speed up the vacuuming process, but this isn't the
> cure. I believe the fault lies on the optimizer.

Sure, the vacuum process speed is fine for small tables, but what about the
big ones where the table gets locked for 5 minutes? What a joke!

> Why save on micro-seconds to use sequential scan when the table is small and
> later 'forgets' that the table is now big because you didn't vacuum analyze?
> Why can't the optimizer just use indexes when they are there and not
> 'optimize' for special cases when the table is small to save micro-seconds?

Well its more than microseconds I presume, as opening indexes involves
opening files, which takes milliseconds rather than microseconds.

Andrew.