On 26/06/2010 3:36 AM, tony@xxxxxxxxxxxxxxxxxxx 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
