vacuum

"Dr R.Adscheid" <adscheid@rosin.com> wrote in message
news:94rb3d$f5v$1@news.tht.net...

We are using PostgreSQL 6.3/Digital Unix 4.0B in an environment with 7x24
availiability. There

You might want to consider upgrading when possible - I think there have been
fairly substantial changes since 6.3

is one table, which has about 9000 new records per day and about 10% being
updated. With an index over several columns the select on this table is

quit

short, but removing old entries and vacuuming is an very time
consuming operation (about 1 hour for the whole database!) and because of

One hour to vacuum 9000 records seems to be a *very* long time. Almost e
faster to do it by hand. You aren't short of RAM? Actually - you say that's
the whole database, so it might be reasonable - depends on what's in the
rest.

the 7x24 production not acceptable. On the other hand, no index improves

the

removing and vacuuming, but now the select is very time consuming, which

is

also not acceptable. Even with the best solution (some index, which

improves

the select but slows down the cleaning) our customer complaints .

The best way to solve this, would be to remove the feature of keeping
deleted/updated records in the databasefiles and therefor no need to

vacuum.

Is there any way to configure this when compiling? Or are there other
possibilities?

Try dropping the index, vaccuming, recreate the index. Might well be a lot
quicker.

You might also find an index on 2 or 3 columns gives you selects that are
almost as fast, but speeds inserts/updates.

- Richard Huxton

"Dr R.Adscheid" <adscheid@rosin.com> writes:

We are using PostgreSQL 6.3/Digital Unix 4.0B in an environment with 7x24
availiability. There
is one table, which has about 9000 new records per day and about 10% being
updated. With an index over several columns the select on this table is quit
short, but removing old entries and vacuuming is an very time
consuming operation (about 1 hour for the whole database!) and because of
the 7x24 production not acceptable.

A number of people have found that removing the index, vacuuming, and
recreating the index is faster than letting vacuum try to fix the
index for itself.

Another possibility is to try the "lazy vacuum" patches available from
http://people.freebsd.org/~alfred/vacfix/, though I don't fully trust
those yet. (That would require updating to 7.0.3 ... but I tell you
in all honesty that you're a fool to still be using 6.3 for anything
critical. The number of bugs fixed between 6.3 and 7.0.3 is enormous.)

regards, tom lane

The best way to solve this, would be to remove the feature of keeping
deleted/updated records in the databasefiles and therefor no
need to vacuum.
Is there any way to configure this when compiling? Or are there other
possibilities?

^^^^^^^^^^^^^
There will be in, hopefully, 7.2, only -:(

Vadim