Re: User Quota Implementation

Stephen Frost wrote:
> * Rod Taylor (pg(at)rbt(dot)ca) wrote:
>
>>>>Simply setup a tablespace for a given user with permissions to allow
>>>>only that user to create new objects within it and make it the default
>>>>location) -- tie their schema to their tablespace? -- then set a kernel
>>>>level quota on their tablespace.
>>>
>>>Since the user accessing/writing to the tablespaces would be the
>>>postgres user I don't really think this 'solution' works in reality.
>>
>>I had assumed it would be a directory based quota rather than a user
>>based one.
>
>
> It's been a while since I played with quotas but I don't recall this
> option being available.
>
>
>>>>Or do we expect a PostgreSQL implementation to do more than that, to
>>>>only count active data by ignoring data pending a vacuum?
>>>
>>>Certainly, it should.
>>
>>Okay. But just so we all know that this means the user with a 5MB quota
>>could still (potentially) fill 1TB of physical diskspace.
>
>
> Hmm, interesting point. What are the options? Make sure the user
> understands they have to vacuum their tables in order to regain the
> space? Have two seperate values (similar to soft vs. hard limits) that
> the admin sets? Either (or both) of those seem reasonable to me.
>
> Stephen

So I'm clear, the soft limit being actual data and the hard limit being
unvacuumed space? How many people don't have auto vacuum set up?

While Oracle doesn't have the vacuum problem, its quota implementation
simply limits the collective amount of space a user can consume in a
tablespace... do we want to deviate from this somewhat standard approach?

I don't see the value in letting a user with a 5M quota take up as much
space as they want. Otherwise, how are they really saving any space at
all? IMO, I think that if a pgsql admin wants to implement quotas they
should understand the auto vacuum requirement. Also, it would add a
great deal of complexity and computation time to calculate the soft
limit on every commit rather than requiring a vacuum analyze.

--
Jonah H. Harris, UNIX Administrator | phone: 505.224.4814
Albuquerque TVI | fax: 505.224.3014
525 Buena Vista SE | jharris(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
-- George Bernard Shaw