Message-ID: <4C258553.4010108@postnewspapers.com.au>
Date: Sat, 26 Jun 2010 12:42:59 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB;
	rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5
MIME-Version: 1.0
To: tony@exquisiteimages.com
CC: pgsql-performance@postgresql.org
Subject: Re: Architecting a database
References: 
 <4f262829ce5efd2bc0296ef8a081cc41.squirrel@www.exquisiteimages.com>
In-Reply-To: 
 <4f262829ce5efd2bc0296ef8a081cc41.squirrel@www.exquisiteimages.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 26/06/2010 3:36 AM, tony@exquisiteimages.com wrote:
> I am in the process of moving a system that has been built around FoxPro
> tables for the last 18 years into a PostgreSQL based system.
>
> Over time I came up with decent strategies for making the FoxPro tables
> work well with the workload that was placed on them, but we are getting to
> the point that the locking mechanisms are causing problems when some of
> the more used tables are being written to.
>
> With the FoxPro tables I had one directory that contained the tables that
> had global data that was common to all clients. Things like documents that
> had been received and logged, checks that had been cut, etc. Then each
> client had his own directory which housed tables that had information
> relating to that specific client.

> I am wondering how I should architect this in PostgreSQL. Should I follow
> a similar strategy and have a separate database for each client and one
> database that contains the global data?

No - use separate schema within a single database.

You can't do inter-database queries in PostgreSQL, and most things 
you're used to using different "databases" for are best done with 
separate schema (namespaces) within one database.

A schema is almost a logical directory, really.

> With the dBase and ISAM tables I
> have a good idea of how to handle them since I have been working with them
> since dBASE originally came out. With the PostgreSQL type tables I am not
> so certain how the data is arranged within the one file. Does having the
> data all in one database allow PostgreSQL to better utilize indexes and
> caches or does having a number of smaller databases provide performance
> increases?

It doesn't really make much difference, and for easier management a 
single database for a single app is very much the way to go.

> In case it is important, there are 2000 clients involved, so
> that would be 2000 databases if I followed my current FoxPro related
> structure.

Nonono! Definitely use different schema if you need to separate things 
this way.

--
Craig Ringer