Re: Partitioning Vs. Split Databases - performance?

On Thu, 2006-12-21 at 15:22 -0800, Benjamin Smith wrote:
> On Thursday 21 December 2006 14:41, Joshua D. Drake wrote:
> >You should read up on schemas and how they work. Plus the
> > addition of schemas and table spaces means you can infinite scaling
> > within the confines of your hardware itself.
>
> Ok, so I'd like you to correct me if I'm wrong:
>
> 1) Schemas operate within a database. A schema is analogized as a filesystem
> directory in the docs, except that you can't recurse schemas.

Kind of... think of a schema as a database without a new connection :).

catalog/cluster
->[n] databases
->[n] schemas (of which public is default)
->[n] objects (tables, functions, views etc..)

By default, your schema is public. You can create a new schema called
customer_one. Within public you could have table public.accounts and
within customer_one you could have customer_one.accounts. They are
isolated and contain seperate data sets.

You can set the schema based on the user connecting or as part of your
queries with set search_path. E.g;

set search_path = customer_one;

select * from accounts;

Will only grab customer_one.accounts.

Thus giving virtual multiple database access without multiple databases.

>
> 2) A database runs on one machine, with the following addenda:
> A) slony lets you copy that database to another system,

Replicate, not copy (being pedantic but it is for good reason).

> B) pgtool lets you duplicate the database if you're real careful around
> updating with unique IDs and aggregate functions,

You mean pgpool? Yeah, but don't use it for that, use Slony or
replicator instead.

> C) you can essentially do cross-machine RAID so that if your primary DB gets
> hosed, you can fire up the backup machine and continue working.

With Slony, Replicator or PITR yes.

> D) pg-cluster, a synchronous clustering solution appears to be new/unstable,
> doesn't appear to be current to 8.x, and takes a performance hit on writes.

Yes.
>
> 3) Thus, any service that splits up a database (EG: a schema) is subject to
> all the limitations outlined in #2.

Well and subject to your app understanding what to do, when.

Joshua D. Drake

>
> Did I miss anything?
>
--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate