Re: SOC & user quotas

"Joshua D. Drake" <jd(at)commandprompt(dot)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.

regards, tom lane