Re: [GENERAL] Using Postgresql as application server - Mailing list pgsql-admin

On Tue, Aug 16, 2011 at 11:27 AM, Evan Rempel <erempel@uvic.ca> wrote:
> Technically it can be done, but just because we can do something does not
> mean we should do something. Having said that...
>
> We have been using a middleware product that shall remain nameless,
> that goes against a large commercial database that shall also remain
> nameless.
> The middleware has been migrating to a more and more database based code
> set, and as an administrator of such a system I can state that this is
> awful.
>
> Getting appropriate logging out of the application logic for both auditing
> purposes
> and trouble shooting is near impossible. Performance is nearly impossible to
> tune as
> everything runs inside the database. One giant process chewing up cores of
> CPU power.
>
>
> Security is near impossible to manage as well. Again, almost everything
> needs to run as
> the same user. The database is now making calls to generate pdf objects and
> make
> printing calls.
>
> None of the traditional tools can be used to integrate the application into
> the enterprise.
> The load balancer needs to add x-forwarded headers to http requests, but the
> custom http code can't handle that, so all web access appears to come from
> the load
> balancer. This violates regulatory requirements. Log file formats are not
> standard
> since none of the code is standard, this means that none of the event
> correlation
> tools can be used for intrusion detection etc.
>
> It is just a nightmare. The previous version that had real middleware and
> real database
> servers was much better. The workloads were different so each server could
> be tuned for
> what it was doing. We were able to purchase hardware appropriate to the
> task. Big RAM
> for database, big CPU for middleware. Overall it was cheaper.

very few of those complaints would apply to postgres -- the database
does not run in a single process (thank goodness for that!), logging
via the various pls is trivially done and effective, etc.  also if
you're serving http it does make sense to wrap postgres with a thin
http server (lighttpd, node.js, nginx are all good choices).   most of
the rest of your gripes seem to apply more to the specific middleware
implementation vs a general appraisal of the technique.  it's trivial
to implement database side security systems and many people do so.

one point that is getting lost in all this that if you are using a
database for an application server, this does not mean it's the same
database as your main database or even on the same machine -- you can
still keep traditional separation of roles and use something like
dblink to transfer data.  regarding the use of postgres functions for
things like reports and pdf generation, I see absolutely nothing wrong
with doing this...although plpgsql is not a very good
choice...pl/python or pl/java would be a better way to go.

merlin