I'm using postgres as the backend to a data-acquisistion
and instrument control system at Lick Observatory. It is
running under Redhat 7.X Linux.
I've noted that SELECT performance worsens significantly after
a few hundred updates on a table. This can be remedied with
a VACUUM. As far as I can tell only the owner of a table
can VACUUM it. The users of the data-acquisition system
are not the owners of the tables. So I've given them update and
insert permission on the relevant tables but they still can't
VACUUM. This leads to ever worsening database performance with
no simple way to recover. Sometimes there are hundreds of updates
in just a few minutes, so an automated backend script that runs
once per day, or even once per hour doesn't work too well.
Do I have to run a small program which is setuid to the owner
of the tables in order to make it possible for the users to
VACUUM the tables? Or is there a more elegant way to do this?
Thanks.
Richard
--
Richard Stover email: richard@ucolick.org
Detector Development Laboratory http://www.ccd.ucolick.org
UCO/Lick Observatory Voice: 831-459-2139
Natural Sciences Bldg. 2, Room 160
University of California FAX: 831-459-2298
Santa Cruz, CA 95064 USA FAX: 831-426-5244 (Alternate)
----------------------------------------------------------------------
