Skip site navigation (1) Skip section navigation (2)

Re: When to choose putting logic into PL/pgSQL and when to use an app server

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
Cc: Frank Lanitz <frank(at)frank(dot)uvena(dot)de>, pgsql-novice(at)postgresql(dot)org
Subject: Re: When to choose putting logic into PL/pgSQL and when to use an app server
Date: 2012-03-10 19:38:24
Message-ID: 4F5BADB0.8080005@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On 11/03/12 05:45, Rory Campbell-Lange wrote:
> On 10/03/12, Frank Lanitz (frank(at)frank(dot)uvena(dot)de) wrote:
>> I started to get into PL/pgSQL a bit as I'm started to work at an
>> application where I might can make usage of it. But I'm not sure even
>> those I read a bunch of documentation where is it useful to put logic
>> into database server and when I shall do the computing inside an
>> application layer... Does anyone of you have some kind of rule of a
>> thumb about that?
> It might depend on what sort of logic you are referring to. Some web
> frameworks provide very simple ways of persisting objects to the
> database and then querying these which are fine for many applications.
>
> If your data model requires good quality data and you are making heavy
> use of the relational aspects of the database, plpgsql can be an
> excellent choice.
>
> Wherever you do the bulk of the data "logic" you will no doubt be doing
> some work in SQL even if you are using an Object-Relational Mapper
> (ORM), or in the middleware language even if you are using plpgsql.
> Things like web forms are best validated in the middleware.
>
>  From our experience we have altered our web frontend and middleware
> several times over the last 7 years while running much the same plpgsql
> functions. We are very happy with this arrangement.
>
> Rory
>

Before I read Rory's answer, my first thought was "It depends..." - one 
of the most annoying and irritating answers an expert can give, but 
unfortunately all too true in practice! :-)

I think for 'simple' situations it is better and faster to put the logic 
in the database. However, I deliberately leave the definition of 
'simple' undefined! My notion of 'simple' depends on my mindset at the 
time -- have I been just been heavily immersed I database thinking, or 
in considering aspects of middleware. So the answers you will get, and 
I've seen I previous threads, depends a lot on where people's experience 
mostly is.

I think whichever way you decide to jump, some use of stored procedures 
may be beneficial -- a lot depends on your situation: skill sets, size 
of team, business case, use cases, performance requirements, and 
complexity of logic required -- both now (time to develop and 
reliability) and in the medium to longer term (maintenance 
considerations and chances of major changes in functionality).

If the application life is likely to be long term, beware of committing 
to a database only approach - as you may suddenly realize a year or so 
down the track, that the complexity has grown beyond what can be safely 
down solely in the database (in terms of ease of development and 
maintenance), and that you should have used middleware.

So in some situations, it may be best to start with using middleware, 
even though it is not really required initially and may delay the 
implementation of the first phase. However, by doing the middleware 
initially you will gain experience when the system is less complicated 
to understand, and the system can more easily scale into handling 
greater complexity.

Note that middleware, like JBoss, can provide a lot of useful features 
almost for free -- that otherwise you would have to investigate, design 
, implement, and have to maintain yourself. For serious enterprise 
stuff, nothing beats Java. I started working life with FORTRAN on 
minicomputers, have had many years experience with COBOL systems on a 
mainframe , and have taught C to experienced programmers -- yes, I am 
ancient! :-)

It may be, that unless you have investigated what features and 
facilities middleware can offer, that you have vastly underestimated 
what is required to implement a robust and secure production system.

So I suggest that you have a look at middleware, even if you decide not 
to use it.


Cheers,
Gavin

In response to

Responses

pgsql-novice by date

Next:From: Nicholas WilsonDate: 2012-03-12 20:31:42
Subject: converting 1 dimensional array to 2 dimensional array
Previous:From: Rory Campbell-LangeDate: 2012-03-10 16:45:01
Subject: Re: When to choose putting logic into PL/pgSQL and when to use an app server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group