Re: LYDB: What advice about stored procedures and other server side code?

|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 logic within the procs without causing any app code breakage…the app tier is completely insulated from those changes – that’s worth a lot. Yes, for deploying the schema change there must be an outage, but that’s true regardless of where the data 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 limiting factor. The only time CPU consumption spikes is during admin activities - reindexing, vacuuming, bulk data loads...that sort of thing. Even the boxplot calculations barely cause a ripple. To me that speaks to the efficiency of language and engine working together. You are right of course on the scaling - if we do run out of CPU horsepower and cannot scale up any more, we'd have to scale out, and there are ways to do that too. IOWs, the model doesn't have to change, 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, correct indexes 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)