
Simplify: move code into database functions (2015) - nsomaru
https://sivers.org/pg
======
maxk42
Don't do this.

Don't FUCKING do this.

I've spent the last 15 months untangling a mess of an e-commerce system that
was built exactly like this. The problem is SQL is not a programming language
and the only way to accomplish some things directly in the database is by
creating solutions with O(n^2) or worse running-time. Our system has queries
with O(n^4) time complexity. Less than two weeks ago, I had to untangle a
query that was over 3,100 lines long with 18 sub-queries and was able to
refactor it into about 100 lines of SQL and another 100 lines of code. Making
matters worse, changes to stored procedures don't show up in git, and can
break some regression-testing tools, making it very difficult to track changes
and hunt down bugs.

Use the right tool for the job: Don't treat every problem like a nail once you
have a hammer. Use SQL for data and your language of choice for logic. Don't
create a monstrosity that someone else will have to spend weeks fixing just
because you can.

~~~
JohnBooty
Every horror you've just described can happen up in the app layer as well.

    
    
        The problem is SQL is not a programming language and the only way to 
        accomplish some things directly in the database is by creating solutions 
        with O(n^2) or worse running-time. Our system has queries with O(n^4) 
        time complexity.
    

Two observations here.

1\. The approach being espoused is not, "Move everything down into the
database layer at all costs, no exceptions." It's more like, "Many things are
simpler and faster at the database layer."

2\. These O(n^2) (and n^3, n^4, whatever) horrors are at least as common at
the app layer. I have dealt with my fair share at every possible level of the
stack.

    
    
        I had to untangle a query that was over 3,100 lines long with 18 sub-queries
    

I just want to say: _ouch._ I am sorry!

~~~
yonixw
You say "could happen" he says "must happen". SQL won't be able to lower the
complexity needed. But in the app level it is just a matter of being good
programmer or under good review process.

------
YarickR2
I sincerely hope this will be downvoted out of sight so we won't be dealing
with more dog slow sites. Moving business logic to essentially non-scaleable
layer is a terrible idea, benefits are so small compared to drawbacks

~~~
eloff
It's not that as simple as all that. Often the most expensive part of a
database transaction is all the round trips back and forth while holding locks
with an open transaction in the database. Move that logic into a stored
procedure and suddenly your business logic can run in less than the time of a
couple round trips to the database. Lock contention drops, number of
concurrent transactions drops, and performance could actually increase. You
scale the system vertically as long as you can, and then start partitioning
and scaling it horizontally. You can go pretty far like that.

There are lots of other reasons this is a questionable idea at best, but
performance is not necessarily one of them.

~~~
JohnBooty

        Move that logic into a stored procedure and suddenly 
        your business logic can run in less than the time of 
        a couple round trips to the database. Lock contention 
        drops, number of concurrent transactions drops, and
        performance could actually increase
    

Yes! A thousand times yes. This is especially important for write-heavy
workloads. Releasing those locks and finishing those transactions ASAP is
crucial.

    
    
        There are lots of other reasons this is a questionable 
        idea at best, but performance is not necessarily one of 
        them. 
    

When it comes to scalability.... yeah I think the 90/10 rule applies here.
Probably the bulk of one's code can comfortably live up at the app layer. And
the 10% of your code that's causing 90% of the bottlenecks is a candidate for
solutions such as moving it down into the data layer, when feasible.

------
bigdubs
One danger with overuse of stored procs is unit tests; they're hard to write
in sql, and also hard to keep in sync with tests in code. There are also
dangers with ninja patching stored procs without corresponding code deploys.

It does make query development much easier to use a database specific IDE for
writing the queries versus munging big strings in code.

~~~
samus
> There are also dangers with ninja patching stored procs without
> corresponding code deploys.

This just means that modifying sprocs has to be treated with the same caution
as deploments. Unit testing is truly difficult though. It doesn't help that
many DBMS products (looking at you, Postgres) don't offer an embedded mode.

------
ambicapter
As someone currently untangling a monster sproc at work--Just no.

~~~
claytongulick
Plsql, T-SQL, and it's ilk are poorly suited to complex logic.

One of the best features of pg is it's ability to write db logic in your
language of choice.

The performance characteristics of these languages meets or exceeds the
execution time of plsql in many cases.

------
simonhamp
It does feel like this whole concept is predicated on chasing the new hotness
in languages or frameworks.

In practice, if you’re thinking of changing at that level, the problem
probably lies elsewhere. Unless your app is some special snowflake (note:
99.99% aren’t), any of the popular languages/frameworks will do just fine for
a very long time.

The only move Sivers arguably needed to make here was away from Perl. To Ruby,
PHP or JavaScript, but not all 3.

If those extra moves hadn’t happened, this wouldn’t even be a thing.

------
kabdib
If you do this, you will regret it and it will cost you money.

------
claytongulick
There are a lot of advantages to the approach the author is discussing, but
some of the drawbacks are (as he mentions) the poor syntax of plsql,
difficulty with SCC and versioning, and some others.

I wrote pgproxy[1] in an attempt to overcome some of those issues. So far,
it's worked pretty well for me.

[1]
[https://github.com/claytongulick/pgproxy](https://github.com/claytongulick/pgproxy)

------
natmaka
A way to develop along those lines without much of the potential hassle is
exposed in Dimitri Fontaine's books.

[https://tapoueh.org/](https://tapoueh.org/)

------
dean177
The motivation for this idea was to make it easier to rewrite everything else.
> In 1997, I started in Perl. In 1998, I switched to PHP. In 2004, a rewrite
in Rails. In 2007, back to PHP. In 2009, minimalist Ruby. In 2012, client-side
JavaScript.

~~~
BubRoss
Rewriting something that works is a terrible idea in the first place.

------
cpach
Discussion is already ongoing in this thread:
[https://news.ycombinator.com/item?id=21362190](https://news.ycombinator.com/item?id=21362190)

------
k__
After looking into solutions like Hasura and Postgraphile, this doesn't seem
like such a bad idea.

------
spiderfarmer
This is making a simple thing complex in order to make complex things simple.

------
kissgyorgy
Usually the recommendation and the simpler approach is quite the opposite: You
can't version control, deploy and generally handle database function as easily
as a piece of code. My experience is that nowadays more and more code is
pushed to the ORM or database handling code level because it greatly
simplifies everything.

~~~
claytongulick
ORM's have improved considerably over the years, but I've always been a bit
hesitant to use them.

I've seen a lot of developers begin to believe in the abstraction, and forget
the underlying access.

This has caused performance problems with increased round trips to the server,
non-obvious indexing strategy issues, and code tightly coupled to the ORM
rather than the data.

I get especially suspicious with framework-type ORMs that can cause a massive
rewrite between major versions. It's costly, time consuming, and doesn't
provide any application benefit, it's only new forms of abstraction.

ORMs are seductive, because a lot of these issues aren't obvious when you
first start using them. The marketing is usually pretty compelling.
Unfortunately, by the time you start to run into issues I've described, it's
normally too late to change paths.

