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


Fri, 9 Jul 2004 15:30:09 -0700
Josh Berkus <josh@agliodbs.com>
Re: User Quota Implementation