Re: Architecting a database

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