Re: Restricting schema sizes

On Sun, Oct 17, 2010 at 5:02 AM, Kieren Scott <kierenscott(at)hotmail(dot)com> wrote:
> Hi,
>
> What is the best way to restrict/limit the size that a schema can grow too
> in Postgresql?
>
> If I want to have a scratchpad area within my database to allow users to
> create tables,
> how can I control the size of the objects they can create?
>
> Would I have to create a dedicated filesystem and then create a tablespace
> on that
> filesystem. Users could then create objects in that tablespace until they
> fill the filesystem.

Yes.

> Is there a setting in Postgresql whereby I can allocate a quota of space
> within a
> tablespace for each schema?

Currently, no. I don't know if someone is working on that type of thing or not.

> The other option I can think of is writing a script that monitors the size
> of the objects
> within a schema. The danger here is that a user could potentially create a
> huge table
> as a result of a bad query (cartesian join etc) and fill the application
> tablespace / filesystem.

Yeah, you'd always be checking after the fact. I guess it depends on
your needs. It might be good enough to keep track of usage and warn
users when they go over if you've got storage to spare, and just want
to keep users from creating multi-terabyte databases all over the
place.