Re: One database vs. hundreds?

On Tuesday 28 August 2007 06:32:32 A. Kretschmer wrote:
> am Tue, dem 28.08.2007, um 14:23:00 +0200 mailte Kamil Srot folgendes:
> > Kynn Jones wrote:
> > >I'm hoping to get some advice on a design question I'm grappling with.
> > > I have a database now that in many respects may be regarded as an
> > >collection of a few hundred much smaller "parallel databases", all
> > >having the same schema. What I mean by this is that, as far as the
> > >intended use of this particular system there are no meaningful queries
> > >whose results would include information from more than one of these
> >
> > I don't have experience in this type of application, but we use pgsql
> > partitioning for other reasons
> > and it has some of the features you want (data separation, query
> > performance, ...).
> > It can be worth reading:
> > http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
>
> He don't need table partitioning, this is a different thing.
>
>
> Andreas

I find that creating multiple schema's is often better than creating multiple
db's since in the remote chance you might want to query across the schema's
it's a no-brainer where queries across db's are a bit more difficult.

That sais AFAIK db's vs. schema's outside of the query Issue I mentioned above
are merely an organizational mechanism. Unless you're talking about multiple
clusters it's kinda the old 6 vs. half a dozen arguement - meaning it doesn't
really matter per performance or functionality from strictly a postgres
perspective. The deciscion then becomes an application architecture
question(s) such as how important is it to isolate these db's/schema's from
each other and is there a forseeable need to query across them in the future,
etc...

Hope this helps.. My vote would be for schema's unless there's a requirement
on the table for isolation - it leaves your options open & more flexible for
the future.

/Kevin