Re: PostgreSQL users on webhosting - Mailing list pgsql-general

That's an interesting idea. First, you can't (as far as I know) do it
with just schemas to seperate the users. There is no default tablespace
for an object created inside a given schema.

However, there is a default tablespace for a given database. You can (as
superuser) create a tablespace and permit only a specific user to use
it, and then create a database within that tablespace (so that objects
created in that database use only a specific tablespace). Users can't
create their own tablespace, so they can't create objects out of that
tablespace unless the superuser creates a new tablespace and gives them
permission.

That seems like it would work quite effectively, except that you need a
bunch of size-limited areas to point the tablespaces at. It would
probably be inconvenient to have many partitions. Although you could,
like you said, put all the "cheap" accounts on one partition, and the
expensive guys on their own disk. Then again, if you're going to single
out accounts, why not just give the special hosting account their own
instance?

There's no really easy answer. It would be nice if postgres had a "max
size" parameter for tablespaces, and then you could achieve reasoanble
seperation between databases quite easily (while still sharing the
buffers). I'm not sure what the overhead on a feature like that would
be.

Regards,
    Jeff Davis

On Fri, 2005-01-07 at 10:38 +0100, Csaba Nagy wrote:
> On Wed, 2005-01-05 at 21:34, Jeff Davis wrote:
> > Benefits of multiple instances:
> > (1) Let's say you're using the one-instance method and one of your web
> > users is a less-than-talented developer, and makes an infinite loop that
> > fills the database with garbage. Not only will that hurt performance,
> > but if it fills the disk than no other users can even commit a
> > transaction! If you seperate the instances, you can run each as its own
> > uid and control each with quotas, etc.
>
> I wonder if this could not be achieved at least partially by using
> schemas and set each user's schema to different tablespaces with
> different space available on them ? Say, the bulk of the low paying
> customers on a bulk partition, and the important customer on it's own
> partition ? I actually would like to know if this is feasable...
>
> Cheers,
> Csaba.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match