Re: [SQL] (Ab)Using schemas and inheritance

Em Terça 23 Maio 2006 20:26, Jim C. Nasby escreveu:
> Moving to -general, where it's more likely that others will have input.

Thanks Jim. This wasn't clear to me when I subscribed to the other mailing
list.

> One issue is that you'll probably be breaking new ground here a bit; I
> suspect there's very few people that are using more than a handful of
> schemas. Shouldn't pose any issues, but you never know; although any
> issues you do run into should only be performance problems.

If these come when I have, e.g., the 8k schemas Tony mentioned, then I could,
definitely, partition my database or use some other technic to divide the
amount of schemas I have on each server. (I hope we have that kind of
problem because it will mean a very successful application :-))

> Another consideration is that the free space map doesn't care too much
> for tracking space info on tons of small tables.

Hmmmm...

> Perhaps the biggest issue is: what happens when you need to do DDL? If
> you have 1000 schemas that should be identical, you'll need to perform
> any DDL 1000 times.

Not really. As I said, I'm planning on using inheritance also. We have
tested that modifications to the parent table reflects on child tables that
inherited from it. So, if I add some new column then I'll have it added to
all 1k schemas.

Anyway, if we have to make some change that isn't inherited by child tables we
can write some script or function to help looping through all tables and
schemas.

> But as you point out, there's some interesting advantages to using
> schemas like this.

I see it as a very interesting possibility, specially when combined with
inheritance.

--
Jorge Godoy <jgodoy(at)gmail(dot)com>