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 BerkusAglio Database SolutionsSan Francisco
