Re: Adding extra custom reports

["Chris Travers" <..hidden..>]

Hi Ed;

On 11/7/06, Ed W <..hidden..> wrote:
> Hi
>
> I have done a few projects where we stuck a lot of code on the DB server
> and whilst it's nice in theory, it did lead to a DB performance
> bottleneck, and it is a bit limiting when later on you suddenly wish
> that you could support other databases after all (eg replication is
> stronger in MySql...)

Currently, we are looking at making this PostgreSQL only.  However,
the system is abstracted enough that porting the db backend to other
similarly capable databases would be possible (if the stored procs
were ported, etc).  Probably more work than necessary, and I actually
think that now (with Slony), replication is actually stronger on
PostgreSQL.

As for performance limiting, there are some other things to consider
too.  Right now (this could change) there are likely to be three
layers:

UI layer
Middleware/XML->data layer
Data layer

In general, where possible, data logic ought to be placed in the
database.  Data logic might be defined as logic that is involved in
storing, maintaining, or retrieving information.  A lot of other logic
(state handling, for example, and the like) will likely never be moved
there because it doesn't make sense to do this.  Of course, because of
input constraints, some data logic isn't going to be able to be
cleanly moved into the db, and so it will reside in the middle layer.
Also note that databases tend to be very good at handling data logic
in the back-end.

Performance bottlenecks happen because of an inattention to detail in
many of these areas.  Certainly moving the entire app into the
database would be silly (yes, you can run a lightweight httpd from
plperlu and reconnect to the db using dblink or even just dbi).  But
most of the db logic involves procedures that would be executed by the
database anyway.  We can, however, make the code more efficient and
maintainable by offering a better defined interface between them.

Of course there is a lot of logic outside of data logic too that
resides elsewhere.

Best Wishes,
Chris Travers