Re: example database setup - Mailing list pgsql-general

Hello William,

Our application does this.  After looking at the various alternatives when we
started this project, we decided to add a column to all relevant tables with
an id that marks it as owned by a particular agency.  Then our app makes sure
this value gets passed in on every query that needs it (the value is loaded
into a session variable when the user logs in).  So we went with the very
large database, subsetting the data on a column on all relevant tables. So a
user sees the slice of the table that is relvent to them. It has worked
relatively well for us.

Pros:
1. It allows us to have system tables that everyone shares without having to
reproduce them within various dbs.  Updates to this system level data is done
in one place.  This could be done in a "system" db, but I don't believe there
is any way to do cross db joins.  Someone else may be able to speak to this
better then I.
2. No special coding for ZSQL statements to work.  There doesn't appear to be
an elegant method of having the same code in zope connect to various dbs
since zsql statements are bound to 1 connection/db.  Probably some minor
coding could fix this.  But the app would still have to pass it in on every
request just as we do now.
3. Database structure changes are done one time, not having to be replicated
over many dbs.  This is a huge timesaver for a system that is quickly
evolving.  You never have an issue where a db gets out of sync with your
code.  May not be as big a deal if the db structure is relatively static.
(Good or bad depending on your perspective).
4. db connections can be re-used using zope's standard connection pooling
instead of opened and closed after every web request.

Cons:
1. It places the burdon on the application and programmer to always limit on
this field where appropriate.  We have found this to actually be less of an
onerous issue then we first thought since generally in testing it's pretty
obvious if you are getting the correct set of data.
2. Depending on the size of the database(s) and the complexity of your table
structure, as your db grows, you can have volume issues.  We are at that
point now.  Complex queries that work well for 100 names don't work nearly so
well for 10,000 names.

#2 has been the biggest con for us recently.  For hard coded queries we can
optimize and continue to do so to make them more efficient.  But we have an
adhoc query and report area which has become quite a bear performance wise.
The larger the volume set postgres has to deal with, the slower any query
will get especially with any complexity.  This is true for any db.  The only
solutions we have found so far are indexing where that makes sense,
materialized views where that makes sense, and continueing to tweak our tools
to try and optimize the sql.

Hope that helps.

-Chris

On Wednesday 05 May 2004 8:49 am, William Herring wrote:
> I would like to set up a zope/database interface (using Postgres 7.4) with
> the following properties:
> - multiple users each with their own id and password - accessed via zope
> - they will enter and access their own data via zope to Postgresql
> - any specific user will not be able to see another user's data
> - the user will not manage his/her web-page
>
> I have Zope 2.6 and Postgres 7.4 set up and working with each other.  Also,
> have a few ideas on how to do this, but thought it would be easiest if
> there were some similar examples out there to look at.   Any suggestions
> from anyone, on anything that might be similar to the above task?  I'm sure
> this has been done many times.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org