Обсуждение: Architecting a database

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. Setting things up like this kept me from
having any tables that were too terribly large so record addition and
index creation were not very time consuming.

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? 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? In case it is important, there are 2000 clients involved, so
that would be 2000 databases if I followed my current FoxPro related
structure. Of course, I suppose it is always possible to combine a number
of groups into a database if the number of databases is an issue.

Tables within the client specific databases are generally name and address
information as well as tables for 10 different types of accounts which
require different structures and those tables hold anywhere from 10,000
transactions a piece for some smaller groups and 1 million for larger
groups. I believe we have read to write ratio of about 1 to 15.

Thanks for any input.

<tony@exquisiteimages.com> wrote:

> 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.

Ah, someone with whom I can reminisce about CP/M and WordStar?  :-)

> 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? In case it is important, there are 2000 clients
> involved, so that would be 2000 databases if I followed my current
> FoxPro related structure.

Well, there are many options here.  You could have:
 - one PostgreSQL cluster for each client,
 - one database for each client (all in one cluster),
 - one schema for each client (all in one database), or
 - a client_id column in each table to segregate data.

The first would probably be a maintenance nightmare; it's just
listed for completeness.  The cluster is the level at which you
start and stop the database engine, do real-time backups through the
database transaction logging, etc.  You probably don't want to do
that individually for each of 2,000 clients, I'm assuming.  Besides
that, each cluster has its own memory cache, which would probably be
a problem for you.  (The caching issues go away for all the
following options.)

The database is the level at which you can get a connection.  You
can see some cluster-level resources within all databases, like the
list of databases and the list of users, but for the most part, each
database is independent, even though they're running in the same
executable engine.  It would be relatively easy to keep the whole
cluster (all databases) backed up (especially after 9.0 is release
this summer), and you could have a cluster on another machine for
standby, if desired.  You are able to do dumps of individual
databases, but only as snapshots of a moment in time or through
external tools.  It's hard to efficiently join data from a table in
one database to a table in another.

A schema is a logical separation within a database.  Table
client1.account is a different table from client2.account.  While a
user can be limited to tables within a single schema, a user with
rights to all the tables can join between them as needed.  You could
put common reference data in a public schema which all users could
access in addition to their private schemas.

The implications of putting multiple clients in a table, with a
client's rows identified by a client_id column, are probably fairly
obvious. If many of those 2,000 clients have tables with millions of
rows, performance could suffer without very careful indexing,
managing tables with billions of rows can become challenging, and
there could be concerns about how to ensure that data from one
client isn't accidentally shown to another.

Hopefully that's enough to allow you to make a good choice.  If any
of that wasn't clear, please ask.

-Kevin

On 6/25/10 3:28 PM, Kevin Grittner wrote:
> <tony@exquisiteimages.com>  wrote:
>> 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? In case it is important, there are 2000 clients
>> involved, so that would be 2000 databases if I followed my current
>> FoxPro related structure.
>
> The implications of putting multiple clients in a table, with a
> client's rows identified by a client_id column, are probably fairly
> obvious. If many of those 2,000 clients have tables with millions of
> rows, performance could suffer without very careful indexing,
> managing tables with billions of rows can become challenging, and
> there could be concerns about how to ensure that data from one
> client isn't accidentally shown to another.

You should also ask whether there are social (that is, nontechncal) reasons to avoid multiple clients per table.

When a customer asks about security and you tell them, "You get your own database, nobody else can log in," they tend
tolike that.  If you tell them that their data is mixed with everyone else's, but "we've done a really good job with
ourapp software and we're pretty sure there are no bugs that would let anyone see your data," that may not fly. 

People will trust Postgres security (assuming you actually do it right) because it's an open source, trusted product
usedby some really big companies.  But your own app?  Do you even trust it? 

Even if your application IS secure, it may not matter.  It's what the customer believes or worries about that can sell
yourproduct. 

We've also found another really good reason for separate databases.  It lets you experiment without any impact on
anythingelse.  We have scripts that can create a database in just a few minutes, load it up, and have it ready to demo
injust a few minutes.  If we don't end up using it, we just blow it off and its gone.  No other database is impacted at
all.

Craig

Kevin Grittner wrote:
> A schema is a logical separation within a database.  Table
> client1.account is a different table from client2.account.  While a
> user can be limited to tables within a single schema, a user with
> rights to all the tables can join between them as needed.  You could
> put common reference data in a public schema which all users could
> access in addition to their private schemas

My guess would be that this app will end up being best split by schema.
I wonder whether it *also* needs to be split by database, too.  2000
clusters is clearly a nightmare, and putting all the client data into
one big table has both performance and security issues; that leaves
database and schema as possible splits.  However, having 2000 databases
in a cluster is probably too many; having 2000 schemas in a database
might also be too many.  There are downsides to expanding either of
those to such a high quantity.

In order to keep both those in the domain where they perform well and
are managable, it may be that what's needed is, say, 50 databases with
40 schemas each, rather than 2000 of either.  Hard to say the ideal
ratio.  However, I think that at the application design level, it would
be wise to consider each client as having a database+schema pair unique
to them, and with the assumption some shared data may need to be
replicated to all the databases in the cluster.  Then it's possible to
shift the trade-off around as needed once the app is built.  Building
that level of flexibility in shouldn't be too hard if it's in the design
from day one, but it would be painful bit of refactoring to do later.
Once there's a prototype, then some benchmark work running that app
could be done to figure out the correct ratio between the two.  It might
even make sense to consider full scalability from day one and make the
unique client connection info host:port:database:schema.

P.S. Very refreshing to get asked about this before rather than after a
giant app that doesn't perform well is deployed.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

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

On Fri, Jun 25, 2010 at 3:36 PM,  <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. Setting things up like this kept me from
> having any tables that were too terribly large so record addition and
> index creation were not very time consuming.
>
> 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? 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? In case it is important, there are 2000 clients involved, so
> that would be 2000 databases if I followed my current FoxPro related
> structure. Of course, I suppose it is always possible to combine a number
> of groups into a database if the number of databases is an issue.
>
> Tables within the client specific databases are generally name and address
> information as well as tables for 10 different types of accounts which
> require different structures and those tables hold anywhere from 10,000
> transactions a piece for some smaller groups and 1 million for larger
> groups. I believe we have read to write ratio of about 1 to 15.
>
> Thanks for any input.

congratulations.  I developed on foxpro for years and I can tell you
you've come to the right place: your porting process should be
relatively pain free.  foxpro had a couple of nice features that
aren't found in too many other places: expression indexes (which we
have) and first class queries (we have, if you count pl/pgsql).
foxpro was also an enormous headache on so many levels which is why I
assume you are here.  I've long harbored suspicion that Microsoft
enjoyed adding to those headaches rather than subtracting from them.

Others have answered the data organization question.  You definitely
want to use schemas to logically separate private application data
inside your database...this is the purpose of schemas basically.

Data in SQL tables is considered unordered (we have no concept of
recno) unless an explicit ordering criteria is given.  Direct access
to the tables (BROWSE) has no analog in SQL.  A query is sent to the
database, results are gathered, buffered, and sent back.  This is the
#1 thing you will have to get used to coming from dbase style coding.

Locking model in postgres is completely different (better).  Records
are implicitly locked by writing to them and the locks are released at
transaction end (optimistic locking plus). As a bonus, data doesn't
get corrupted when you break the rules =).

For backend data processing tasks I advise you to use pl/pgsql.
Coming from foxpro you should have no problems.  You are going to have
to replace your GUI and report generator.  First question is whether
or not go web...and following that which technologies to use.  You may
have already figured all this out but perhaps you haven't.    Foxpro
does have odbc connectivity so you may have entertained ideas of
simply moving your application w/o porting the code.   This may or may
not work (just a heads up) -- the performance of foxpro odbc
translation is not so great and some of your code won't translate
well.  If you didn't use foxpro for the front end, it's going to
depend on what you're using.

Once you get used to postgres and how it reads and writes data, don't
worry so much about performance.  As long as you avoid certain
paradigms postgres doesn't write, the performance of your new database
should absolutely nuke what you're used to, especially in the multi
user case.  You will have no problems on the backend -- it's the front
end where your main concerns should be.  good luck.

merlin

tony@exquisiteimages.com writes:
> 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?

As others said already, there's more problems to foresee doing so that
there are advantages. If you must separate data for security concerns,
your situation would be much more comfortable using schema.

If it's all about performances, see about partitioning the data, and
maybe not even on the client id but monthly, e.g., depending on the
queries you run in your application.

Regards,
--
dim

Thanks for all of the input everyone.

I believe I am going to put together a test case using schemas and
partitioning and then doubling the amount of data currently in the system
to give me an idea of how things will be performing a couple of years down
the road.

I was looking at a server using the new Opteron 6100 series for the new
server and it would have 32 cores, but the speed is 2ghz. I read a post
earlier today that mentioned in passing that it was better to have a
faster processor than more cores. I was wondering whether or not this
would be a good selection since there are CPUs in the Intel branch that
are quad core up to 3.3ghz.

On Jun 30, 2010, at 11:12 AM, tony@exquisiteimages.com wrote:

>  I read a post
> earlier today that mentioned in passing that it was better to have a
> faster processor than more cores.

This really depends on your workload and how much you value latency vs. throughput. If you tend to have a lot of very
simplequeries, more cores => more throughput, and it may not matter much if your queries take 20ms or 30ms if you can
bedoing a dozen or two more of them concurrently in an AMD system than in an Intel one. On the other hand, if you have
lessclients, or more latency-sensitive clients, then fewer-but-faster cores is usually a win. 

Either way, the amount of power you can get for your money is pretty impressive.