Postgres Pro
Downloads
Home   >   mailing lists

Thread: vacuum by non-owner

vacuum by non-owner

From
Richard Stover
Date:
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)
----------------------------------------------------------------------

Re: vacuum by non-owner

From
Doug McNaught
Date:
Richard Stover <richard@ucolick.org> writes:

> 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?

Why not run 'vacuumdb' periodically out of 'cron' as the postgres
user?

-Doug

Re: vacuum by non-owner

From
Richard Stover
Date:
This is what I was trying to avoid. I'd have to run the script
every five minutes, even if it isn't needed, to handle the
times when it is. That just seemed like sort of a clumsy way
to handle the problem. But perhaps this is the best
solution?



Richard



> Richard Stover <richard@ucolick.org> writes:
>
>
>> 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?
>
>
> Why not run 'vacuumdb' periodically out of 'cron' as the postgres
> user?
>
> -Doug
>
>


--
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)
----------------------------------------------------------------------

Re: vacuum by non-owner

From
Doug McNaught
Date:
Richard Stover <richard@ucolick.org> writes:

> This is what I was trying to avoid. I'd have to run the script
> every five minutes, even if it isn't needed, to handle the
> times when it is. That just seemed like sort of a clumsy way
> to handle the problem. But perhaps this is the best
> solution?

With Postgres 7.2 and later, regular VACUUM (not FULL) is very
lightweight.  The less work it has to do, the quicker it will run.
Vacuuming frequently in 7.2+ also reduces the pressure on FSM (free
space map) memory, so you can get away with less of it.

-Doug