4 ms·

Why would you prefer that? In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes. In the D

Why would you prefer that? In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes. In the Db you "define" how it should look/behave/ and who has access. As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude). I know your reaction comes form years of tutorials telling you not to do this, but this was way back when mysql didn't even have views, things are a lot different now, databases are not dumb stores. Give it 5 minutes and try a small project, you'll be surprised by the power

mlthoughts2018 7y ago
> “In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes.” No, it could be functional, declarative, OO, whatever, depending on the language used. Personally, after a lot of years of Haskell & Scala experience in large companies, I think functional & declarative programming are way overhyped, and these types of designs do not actually offer the benefits they are claimed to. Given this, I see zero reason to care if the abstraction layer is some OOP tool. That is absolutely fine. > “As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude).” This is comically wrong. I remember the ~100 line long MSSQL stored procedure just to compute (poorly) the median of a column. Expressing things in the languages supported with the database (even in Postgres, even with language extensions) requires waaaaay more code than using application tools in the application language. I remember how much easier our lives became at an old finance job when we finally ported a ton of stored procedures to instead use pandas for math operations in Python. The cost of passing the data through processing jobs that required serializing it to servers where the jobs could run, transforming it, then transporting it back, was absolutely worth it because we could remove thousands and thousands of lines of stored procedures that were incredibly bug prone, impossible to debug, and totally lacking the linear algebra and analytics functionality we needed to expand the app. Moving the logic out of the database was 100% motivated by making it safer, less buggy, simpler & easier to test code, and gaining expressive functionality totally impossible to express in the database.
- ruslan_talpa 7y ago
  I guess each person's oppinion is colored by personal past experiences, i too can come up with stories of how backend apis with 10s of thousands of lines got reduced to a handfull of views and stored procedures. But it's true this aproach does work when you have to do linear algebra on your data (my feeling is that this requirement does not comes up in so many projects). On the debuging part it's a bit true, the workflow is not as polished (debuging in db) as oposed to other envs. About the "fast" part, i would not say "comically wrong", if it were, there'd be no reason for sql beyond "select * from"
- felixyz 7y ago
  Let's not forget that Postgres has first-class support for Python [0] (unlike JavaScript etc which are maintained, or not, by third parties). I don't advocate going for Python for simple stuff but when you do use it, it has the added benefit of having plain ol' Python functions that you can unit test thoroughly (including with the wonderful Hypothesis [1] property testing framework). [0] https://www.postgresql.org/docs/12/plpython-funcs.html https://www.postgresql.org/docs/12/plpython-funcs.html [1] https://hypothesis.works/ https://hypothesis.works/
  - mlthoughts2018 7y ago
    The Postgres support of Python is usually way overhyped. I’ve done a lot of crazy things with it, like even compiling my own extension modules using Postgres C libraries to write Python native functions that vectorized business logic over columns like the way numpy works. It’s extremely hard to manage the Python environment itself with Postgres, as Postgres has to be compiled with it. If you’re working on scientific or analytics applications where you are spinning up a new conda environment all the time, adding & changing package dependencies, etc., and you need to keep your external-to-database Python app logic synchronized with the internal-to-database Python, it’s virtually unusable. Imagine needing to ship new versions of an in-house machine learning library into the database, test it, update that library’s own dependencies inside the database’s python, etc. It becomes a crazy packaging nightmare very fast. The plpython extension is basically just a cute toy. Occasionally useful or a small script or single transformation that needs Python standard library functions, but anything beyond that and it becomes very unscalable & unmaintainable very fast.
jablan 7y ago
> shorter Yes, until the very first time you'd want to reuse some of the code. From then on, any initial advantage is melting into unmaintainable mess of semi-imperative, semi-declarative copy-paste horror.
- ruslan_talpa 7y ago
  reuse code where? In the db, why couldn't you? Or do you mean to reuse the logic in the db in some other place? By this logic one can say "ruby is bad becasue i can't reuse logic i wrote for the backend in my frontend SPA". This is the type of (data logic) that you should not need to reuse in other parts. Maybe this comment does a better job of explaing the architecture https://news.ycombinator.com/item?id=21436425 https://news.ycombinator.com/item?id=21436425

mlthoughts2018 7y ago

> “In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes.” No, it could be functional, declarative, OO, whatever, depending on the language used. Personally, after a lot of years of Haskell & Scala experience in large companies, I think functional & declarative programming are way overhyped, and these types of designs do not actually offer the benefits they are claimed to. Given this, I see zero reason to care if the abstraction layer is some OOP tool. That is absolutely fine. > “As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude).” This is comically wrong. I remember the ~100 line long MSSQL stored procedure just to compute (poorly) the median of a column. Expressing things in the languages supported with the database (even in Postgres, even with language extensions) requires waaaaay more code than using application tools in the application language. I remember how much easier our lives became at an old finance job when we finally ported a ton of stored procedures to instead use pandas for math operations in Python. The cost of passing the data through processing jobs that required serializing it to servers where the jobs could run, transforming it, then transporting it back, was absolutely worth it because we could remove thousands and thousands of lines of stored procedures that were incredibly bug prone, impossible to debug, and totally lacking the linear algebra and analytics functionality we needed to expand the app. Moving the logic out of the database was 100% motivated by making it safer, less buggy, simpler & easier to test code, and gaining expressive functionality totally impossible to express in the database.

ruslan_talpa 7y ago
I guess each person's oppinion is colored by personal past experiences, i too can come up with stories of how backend apis with 10s of thousands of lines got reduced to a handfull of views and stored procedures. But it's true this aproach does work when you have to do linear algebra on your data (my feeling is that this requirement does not comes up in so many projects). On the debuging part it's a bit true, the workflow is not as polished (debuging in db) as oposed to other envs. About the "fast" part, i would not say "comically wrong", if it were, there'd be no reason for sql beyond "select * from"
felixyz 7y ago
Let's not forget that Postgres has first-class support for Python [0] (unlike JavaScript etc which are maintained, or not, by third parties). I don't advocate going for Python for simple stuff but when you do use it, it has the added benefit of having plain ol' Python functions that you can unit test thoroughly (including with the wonderful Hypothesis [1] property testing framework). [0] https://www.postgresql.org/docs/12/plpython-funcs.html https://www.postgresql.org/docs/12/plpython-funcs.html [1] https://hypothesis.works/ https://hypothesis.works/
- mlthoughts2018 7y ago
  The Postgres support of Python is usually way overhyped. I’ve done a lot of crazy things with it, like even compiling my own extension modules using Postgres C libraries to write Python native functions that vectorized business logic over columns like the way numpy works. It’s extremely hard to manage the Python environment itself with Postgres, as Postgres has to be compiled with it. If you’re working on scientific or analytics applications where you are spinning up a new conda environment all the time, adding & changing package dependencies, etc., and you need to keep your external-to-database Python app logic synchronized with the internal-to-database Python, it’s virtually unusable. Imagine needing to ship new versions of an in-house machine learning library into the database, test it, update that library’s own dependencies inside the database’s python, etc. It becomes a crazy packaging nightmare very fast. The plpython extension is basically just a cute toy. Occasionally useful or a small script or single transformation that needs Python standard library functions, but anything beyond that and it becomes very unscalable & unmaintainable very fast.

jablan 7y ago

> shorter Yes, until the very first time you'd want to reuse some of the code. From then on, any initial advantage is melting into unmaintainable mess of semi-imperative, semi-declarative copy-paste horror.

ruslan_talpa 7y ago
reuse code where? In the db, why couldn't you? Or do you mean to reuse the logic in the db in some other place? By this logic one can say "ruby is bad becasue i can't reuse logic i wrote for the backend in my frontend SPA". This is the type of (data logic) that you should not need to reuse in other parts. Maybe this comment does a better job of explaing the architecture https://news.ycombinator.com/item?id=21436425 https://news.ycombinator.com/item?id=21436425