Re: SOC & user quotas

On Wed, 28 Feb 2007, Tom Lane wrote:

> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Martijn van Oosterhout wrote:
>>> Generally, rolling back a transaction doesn't reduce the amount of disk
>>> used. Only VACUUM FULL actually shrinks relations.
>
>> Right, but what I mean was -- if we rollback because we hit quota we
>> could potentially cause even more maintenance to have to happen (vacuum).
>
> It's worse than that, because VACUUM FULL will actually bloat the
> indexes on the way to being able to reduce the table size (since it has
> to make new index entries for rows it moves).  If the limit is strictly
> enforced then a user who has reached his quota is really totally
> screwed: the only easy way to get back under quota will be to completely
> drop tables, ie, discard data.  VACUUM probably won't reduce the
> physical table size much, and VACUUM FULL will fail, and other
> approaches such as CLUSTER won't work either.
>
> [ thinks for a bit... ]  Possibly you could drop all your indexes,
> VACUUM FULL, reconstruct indexes.  But it would be painful and would
> certainly prevent you from working normally until you finish that
> maintenance.  If the quota limit includes temp files you might find that
> rebuilding the indexes fails, too, because of the transient space needed
> to rebuild.
>
> Plus, all that forced maintenance activity will be degrading response
> for other users while it happens.
>
> On the whole I'm not convinced that a quota is a good idea.

On database level it's possible to have soft user quote, just measure
disk usage and warn user if database size is over. This could be realized
using external tools. But Sergey wanted finer granulation. As a workaround,
we could have function which return size of db objects owned by user and
let administrator run cron job to realize soft quota. This will not provide
foundation for enterprize level of QoS, but we certainly don't want to
introduce too much overhead. It's interesting and challenging task though.
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83