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
