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

On Mon, Jul 25, 2011 at 5:21 PM, Chris Travers <chris.travers@gmail.com> wrote:
> 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 ;-) ).

Of course I pretty much agree on all points but you are preaching to
the choir.  You and I both code in a spartan, console heavy manner.
Of course we prefer plpgsql because over the course of years of
database application programming we've realized the advantages of
first class queries and having code running tightly coupled with the
data.  Unfortunately when I talk about these concepts to the majority
of programmers I've worked with over the years they look at me as if I
just got off my spacecraft from Neptune.  I think I've been cursed due
to have discovered the secret to fast, efficient programming while
continually being constrained from developing that way.   Then again,
most programmers probably feel like that :-).

merlin