Re: User Quota Implementation

Jonah,

> I have seen some discussion about using OS-level quotas on a user or
> group level, however, like our Oracle system, not all database users
> have a system account.  This is why I needed to implement user-specific
> quota functionality within the database itself.

Agreed. Also, implementing quotas on a filesystem level is an administrative
pain.

> Also note, my quota implementation currently expects a well-vacuumed
> database.  I always use pg_autovacuum but don't know about most other
> people.

That's a reasonable limitation from my perspective, especially since we're
merging autovacuum into the back-end.

> - userquota is stored in units of kilobytes... is this adequate?  Would
> anyone ever use a quota < 1K other than for allowing a user no space?
> If the user has no space, why not just disable the account and/or make
> them read-only?

Yes, it's fine. I can't imagine needing a granularity < 1K. In fact, I
personally can't imagine using a granularity < 1mb, but that may be just me.

> - Would anyone want to use a group quota in PGSQL (rather than user-only)?

I can imagine this, yes.

> - I assume that, based on discussion, not everyone sets up
> auto-vacuuming and therefore I would need to change the way I perform
> calculations.

More that auto-vacuuming does not fix the issue for a high-activity database,
or at least that you have to set quotas at 50% of the available space.
Also, keep in mind that these quotas would not at all restrict the use of
xlog or swap space, so your users could still run you over.

> - Quota acts on any object owned by the user.  Is this adequate for
> everyone?

For me, yes.

> - Hard limits vs. soft limits... does anyone think it's a good idea to
> truncate someone's data?  I personally don't think it's a good idea.

No, it's not a good idea. If the user goes over quota, it should exception
and their transaction should abort.

> Is there any additional functionality you would like to see in a quota
> implementation?

A pg_catalog view that allows seeing the user quotas and what % full they are.

> Would you rather see ALTER USER SET for quota rather than ALTER USER?
> Likewise, I had originally used ALTER USER username QUOTA UNLIMITED (in
> Oracle style) but found that it didn't meet PostgreSQL's common
> syntax... which is why I changed it to ALTER USER username NOQUOTA...
> does everyone agree with NOQUOTA over QUOTA UNLIMITED?

No opinion.

--
-Josh Berkus
Aglio Database Solutions
San Francisco