Re: Implementing "thick"/"fat" databases - Mailing list pgsql-general

On Mon, Jul 25, 2011 at 12:33 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

> exactly. procedural middlewares written in languages like java tend to
> be bug factories:
> *) over-(mis-)use of threads
> *) performance wins moving logic outside the database to scale it are
> balanced out by the extra traffic

Well, typically you have other performance issues other than the
traffic.  A well written SQL query can do more, and more efficiently,
than most application programmers realize.  The tendency to move stuff
out of the database IMHO often occurs at the same time the SQL code
itself ends up being less efficient.  A lot of things (aggregates for
example) use of CPU cycles on the db server that could probably be
saved by moving things out of the db, but only at the cost of memory
usage.

I have seen CRAZY stuff coming out of middleware and I tend to suspect
that the "move it out of the db" crowd sometimes tend to be stuck
thinking in their languages and thus unable to take advantage of good,
well written SQL.

I have seen Perl code be used instead of HAVING clauses.  I have seen
loops through query results, firing other queries.....  Ok, to be
fair, I would call that program as something way below average in
quality but still.

> *) database concurrency issues
> *) OO representation of data is brittle and inflexible

And the DB ends up being designed around the ORM.......  Which sucks.......

> *) extremely (and IMNSHO unnecessarily) verbose
> *) framework all over the place
> *) libraries all over the place

generally agreed.

>
> On the plus side though, languages like java have huge pools of
> available talent and excellent tools.  These factors are *critical*
> for many IT companies.  plpgsql may be the bee's knee's (it is) but
> DIY tools and methodologies tends to translate directly to a high cost
> of labor, and application developers used to rich tool environments
> tend to really dislike code practices that pl/pgsql requires like
> debugging by logging and remembering where stuff is and what it does.

I dunno.  It depends on how you use plpgsql.

The approach we have taken in the LedgerSMB project is to use stored
procs basically as what has been called "named queries."  We try to
put as much as possible into single SQL statements as possible, and we
extensively use PLPGSQL's syntactic sugar to make things more
manageable (a very large number of our stored procs, probably a clear
majority, could be written in SQL).

I think that if you come up with all these excellent tools, the
quality of code will go down and you will end up with something that
approximates most of the Middleware problems today.  IMHO, the best
use for PLPGSQL is that of straight SQL queries with just a little
procedural logic around it.  There are exceptions due to the fact that
utility statements are unparameterized......  But for the most part,
that is what I have found to work best.

>
> plpgsql is a niche language that tends to attract the same really
> smart people who write code in a more functional style.  When done
> well, you can do a lot with a very small amount of code.  I would love
> to see stronger validation features (with a higher emphasis on
> warnings) and better tools/editors to help bring plpgsql to the
> mainstream.

What kinds of tools/editors do you think we need?  A heck of us really
like VIM or EMACS (just don't ask which is best ;-) ).

Best Wishes,
Chris Travers