From: Craig Ringer <craig@postnewspapers.com.au>
To: tony@exquisiteimages.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Architecting a database
Date: Sat, 26 Jun 2010 12:42:59 +0800
Message-ID: <4C258553.4010108@postnewspapers.com.au> (raw)
In-Reply-To: <4f262829ce5efd2bc0296ef8a081cc41.squirrel@www.exquisiteimages.com>
References: <4f262829ce5efd2bc0296ef8a081cc41.squirrel@www.exquisiteimages.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, tony@exquisiteimages.com
Subject: Re: Architecting a database
In-Reply-To: <4C258553.4010108@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox