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

On 10/03/12, Frank Lanitz (frank@frank.uvena.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