|From: Christoph Moench-Tegeder
|Initially, running code in your database can make life easier for the developers
|(ise pgTap for testing, pl/profiler and pl/debugger, etc.). But once you have to
|change your schema, the hurt begins:
|you'll need downtime for that, or you'll have to deal with the possibility of
|changing the API of your "database side" code, and matching code and tables on
|the database.
I look at this from the opposite direction: with a stable database API (via stored procs), I can change the schema and
logicwithin the procs without causing any app code breakage…the app tier is completely insulated from those changes –
that’sworth a lot. Yes, for deploying the schema change there must be an outage, but that’s true regardless of where
thedata is being manipulated – the data is still in a db…and there are ways to mitigate/eliminate the duration of the
outage.
|The next pain point is scalability: running code on the database server puts your
|code on the most expensive and hardest to scale CPUs. You can (almost) always
|add another appserver to your setup (just spin up a VM with a tomcat or
|whatever-you-use). But if the bottleneck is your database CPUs, you'd have to
|move to a larger server
Our dedicated db servers have not yet shown any real CPU consumption during app use - memory, for us, is the only real
limitingfactor. The only time CPU consumption spikes is during admin activities - reindexing, vacuuming, bulk data
loads...thatsort of thing. Even the boxplot calculations barely cause a ripple. To me that speaks to the efficiency
oflanguage and engine working together. You are right of course on the scaling - if we do run out of CPU horsepower
andcannot scale up any more, we'd have to scale out, and there are ways to do that too. IOWs, the model doesn't have
tochange, just the solution to solve the scaling (solve the problem, not the symptom).
|TL;DR: database side code can be a great thing in a small application, but once
|the application and traffic grows, "code in the database"
|requires specialist attention and may become a burden.
|Unfortunately, most large applications started small...
In my opinion, having a database specialist work on database stuff is a GOOD thing. Tables get designed properly,
correctindexes are built, efficient query plans are created, etc. ORMs are a shortcut to getting an app talking to
data,but aren't a substitute for a proper, scalable data tier. IMO...being a data specialist... :-)
Mike Sofen (Synthetic Genomics)