Re: Performance (was: The New Slashdot Setup (includes MySql server))

Chris <chris(at)bitmead(dot)com> writes:
>> not helpful at all WRT this benchmark, because the user who caused the
>> problem ("test", in my case) isn't permitted to run VACUUM on the
>> pg_index table.

> Speaking of which, why can't any user who can change meta-data, also
> Vacuum meta-data ? It's not a threat to security is it?

No, but it is a potential route to a denial-of-service attack, because
VACUUM has to acquire an exclusive lock on the target table. An
unprivileged user can't vacuum pg_index for the same reason he can't
lock it: he could effectively shut down all other users of that
database, at least for a while (and VACUUMs issued in a tight loop
might manage to make things pretty unusable).

The design assumption here is that VACUUMs will be run periodically by a
cron job executing as user postgres; typically once a day at a low-load
time of day is a good plan.

There has been some talk of switching away from the no-overwrite storage
manager to a more conventional overwriting manager. That'd reduce or
eliminate the need for periodic VACUUMs. But currently, you can't
really run a Postgres installation without 'em.

regards, tom lane