Re: Architecting a database - Mailing list pgsql-performance

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