Re: Schema per user?

On Thu, May 07, 2009 at 03:06:11PM -0600, Scott Marlowe wrote:
- On Thu, May 7, 2009 at 2:12 PM, Anders Steinlein <anders(at)steinlein(dot)no> wrote:
- >
- > On May 7, 2009, at 10:05 PM, Scott Marlowe wrote:
- >
- >> On Thu, May 7, 2009 at 1:45 PM, Anders Steinlein <anders(at)steinlein(dot)no>
- >> wrote:
- > Generally though, what made you consider such a solution? Same advantages as
- > I mentioned? One thing I'm a bit usure of how best to solve is where to
- > place the "users" or some such table for authentication and other "shared"
- > info -- simply in the "public" schema, perhaps?
-
- We're looking at a "schema per group" fit for a certain application
- and we have lot of groups (in the 100,000 to 1,000,000 range.) We're
- also looking at partitioning to multiple db servers if needs be. It's
- a compelling app, and schemas allow us to have one copy of the master
- user data etc and the app just has to have a different search path and
- viola, we're integrated.
-

Interesting, we were looking at something similar but dismissed it because it
seemed like a maintenance nightmare, instead we're planning on going with
partitioning.

From a programming aspect, we're using JPA, anyone know if you can set search_path
with JPA/JDBC?

Also, how do you plan to handle schema updates in that model, inheritence?

You don't have a concern with dealing with 100,000 * n tables?

My background is with oracle, and in general it would have cleanup issues with
tracking that many tables/segments. Does postgres just handle an insane amount
of tables better?

Thanks

David Kerr