5 ms·

Simplify: move code into database functions (2015)

32 points by nsomaru 7y ago

bigdubs 7y ago
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 7y ago
  > 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.
YarickR2 7y ago
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 7y ago
  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 7y ago
    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.
- weberc2 7y ago
  It’s not a bad idea because it’s not well supported by current tooling and practices, not because of scalability. The scalability problem is tractable.
- claytongulick 7y ago
  Performance is the primary benefit for adopting the author's approach, actually. Normally the bottleneck of scaling an application is data access and storage, there aren't typically a lot of problems that are CPU bound. Adding additional layers doesn't increase performance, it increases latency and processing time. The closer you can keep your processing logic to the actual data, the better your performance is going to be. That being said, there are reasons why you may not want to follow that architecture, but generally performance isn't going to be one of them.
dean177 7y ago
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 7y ago
  Rewriting something that works is a terrible idea in the first place.
claytongulick 7y ago
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
ambicapter 7y ago
As someone currently untangling a monster sproc at work--Just no.
- claytongulick 7y ago
  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.
cpach 7y ago
Discussion is already ongoing in this thread: https://news.ycombinator.com/item?id=21362190 https://news.ycombinator.com/item?id=21362190
spiderfarmer 7y ago
This is making a simple thing complex in order to make complex things simple.
maxk42 7y ago
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.
- dastx 7y ago
  > Making matters worse, changes to stored procedures don't show up in git This is not necessarily true. It depends on your environment. You can track changes to db with the right tools. Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.
  - OJFord 7y ago
    I don't like migrations, it's like doing version control with dated copies of files. I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match. Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.
    - samus 7y ago
      - Liquibase expects the scemea to be modeled in its schema language. This makes it possible to compute changes between the database and the new schema and apply those changes, or to just create old-fashioned migration scripts. - Th. Doctrine and Hibernate ORMs can do similar things. - Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.
deleted 7y ago
[deleted]
kissgyorgy 7y ago
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.
- JohnBooty 7y ago
  The author's not advocating anything as drastic as abandoning the application layer entirely, of course. These aren't mutually exclusive approaches. You can use an ORM like ActiveRecord to manage migrations and move functionality down to the database layer when reasonable to do so.
- claytongulick 7y ago
  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.
kabdib 7y ago
If you do this, you will regret it and it will cost you money.
k__ 7y ago
After looking into solutions like Hasura and Postgraphile, this doesn't seem like such a bad idea.
simonhamp 7y ago
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.
natmaka 7y ago
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/

32 points by nsomaru 7y ago

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 7y ago
> 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.

YarickR2 7y ago

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 7y ago
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 7y ago
  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.
weberc2 7y ago
It’s not a bad idea because it’s not well supported by current tooling and practices, not because of scalability. The scalability problem is tractable.
claytongulick 7y ago
Performance is the primary benefit for adopting the author's approach, actually. Normally the bottleneck of scaling an application is data access and storage, there aren't typically a lot of problems that are CPU bound. Adding additional layers doesn't increase performance, it increases latency and processing time. The closer you can keep your processing logic to the actual data, the better your performance is going to be. That being said, there are reasons why you may not want to follow that architecture, but generally performance isn't going to be one of them.

dean177 7y ago

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 7y ago
Rewriting something that works is a terrible idea in the first place.

claytongulick 7y ago

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

ambicapter 7y ago

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

claytongulick 7y ago
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.

cpach 7y ago

Discussion is already ongoing in this thread: https://news.ycombinator.com/item?id=21362190 https://news.ycombinator.com/item?id=21362190

spiderfarmer 7y ago

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

maxk42 7y ago

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.

dastx 7y ago
> Making matters worse, changes to stored procedures don't show up in git This is not necessarily true. It depends on your environment. You can track changes to db with the right tools. Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.
- OJFord 7y ago
  I don't like migrations, it's like doing version control with dated copies of files. I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match. Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.
  - samus 7y ago
    - Liquibase expects the scemea to be modeled in its schema language. This makes it possible to compute changes between the database and the new schema and apply those changes, or to just create old-fashioned migration scripts. - Th. Doctrine and Hibernate ORMs can do similar things. - Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.

deleted 7y ago

[deleted]

kissgyorgy 7y ago

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.

JohnBooty 7y ago
The author's not advocating anything as drastic as abandoning the application layer entirely, of course. These aren't mutually exclusive approaches. You can use an ORM like ActiveRecord to manage migrations and move functionality down to the database layer when reasonable to do so.
claytongulick 7y ago
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.

kabdib 7y ago

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

k__ 7y ago

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

simonhamp 7y ago

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.

natmaka 7y ago

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/