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

Not sure what overhead - but Oracle has this ;) Infact Oracle by
default puts each user in their own schema, and each user can be
assigned a default tablespace as a property of the user.  With the
advent of Tablespace in pg 8.0, is it possible to set a user's default
tablespace?

Alex Turner
NetEconomist


On Fri, 07 Jan 2005 13:03:25 -0800, Jeff Davis <jdavis-pgsql@empires.org> wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>