Re: Using Postgresql as application server - Mailing list pgsql-general

Hi CK:

First, it depends on what you mean by an application server.  There
are people who in fact do very similar things with PostgreSQL,
essentially having it take on roles traditionally served by
middleware.

On Sat, Aug 13, 2011 at 8:57 AM, c k <shreeseva.learning@gmail.com> wrote:
> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used as
> application server?

Kinda.

> As postgresql supports many languages like pl/perl,
> pl/python etc, supports dblink like functions to connect to other postgresql
> servers and now features are in development to use external data. Postgresql
> works well on many operating systems and has a stable and good quality code.
> As many users are using plpython or plperl to work on many types of data and
> to implement logic that can be useful in web application management.
> So i am thinking if I can use postgresql as web application server.
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.

> 2. can connect to other postgresql servers or databases

Best used sparingly.

> 3. it is easy to manage stored procs or triggers than managing web
> application in other servers.

I agree, but....

> 4. data and logic/processing can be separated. One postgresql can be used as
> application server and another as database.

Bad idea.  You don't really gain anything except complexity and
headache by trying to separate like this.

> 5. stable, mature and open codebase.

Probably better than give some general feedback I will share how we do
this with LedgerSMB.  Some of our deployments using this approach are
decent-sized.

1:  Thus far all our stored procs are all in SQL and PLPGSQL.  We do
not currently use PL/Perl or PL/Python, or any other stored procedure
language.

2:  We pay close attention to semantics in order to ensure, to the
extent possible, that catalog data allows for discovery of stored
procedure interfaces.  We then do a lot of query building in the
"client" app (a web app) to discover these interfaces and call them
properly.  We do not include SQL code in most perl modules.  Instead
virtually all calls go through a generic discovery interface.

3:  All logic required to store, retrieve, or present (to the
application) the data goes through these stored procedures.

4:  The web app is a fairly thin Perl glue that binds HTML templates
written in Template Toolkit to these stored procedures.  It's rare to
find Perl functions more than about 5-10 lines long and when that
happens most of the logic is usually taking data and putting it into a
tabular structure for a report template.

This could be done with a desktop app as well.  The primary business
logic and security is thus reusable between applications, making
PostgreSQL essentially a middleware server.

Observations from my experience:  Design is critical and difficult.
There aren't a lot of people doing this sort of thing and so there is
a LOT of trial and error.  Also, it is quite possible to do a heck of
a lot in this area in SQL and PLPGSQL.  Focus on writing unified,
maintainable queries and semantically meaningful interfaces (argument
names, function names, etc).

Best Wishes,
Chris Travers