Re: vacuum

"Dr R.Adscheid" <adscheid(at)rosin(dot)com> wrote in message
news:94rb3d$f5v$1(at)news(dot)tht(dot)net(dot)(dot)(dot)
> 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