Chris <chris@bitmead.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