7 ms·

Cool demonstration of IMMUTABLE functions (TIL), but there's a reason people have moved away from stored procedures/functions in RDBMSs in favor of moving this

by gfodor 10y ago

Cool demonstration of IMMUTABLE functions (TIL), but there's a reason people have moved away from stored procedures/functions in RDBMSs in favor of moving this stuff into much slower application code -- you now have business logic living inside of the database. From there, you have to now think about how this affects: - Testing - Deployment/Migrations - Day-to-day tooling to work on it - Switching costs (mental overhead, using multiple languages) - Documentation - Probably more stuff The ruby code is just like all of your other ruby code and can be grokked and worked on by anyone who is up to speed on your ruby stack. The DB function meanwhile requires jumping through lots of hoops and will always feel like a second class citizen relative to the vast majority of your application code, unless you do a lot of work and maintenance (unlikely to happen without feeling distracting if this code is a relatively small slice of your code base.) In some cases, this can be worth it for massive optimization wins. But the article doesn't really touch on why this might be a bad idea. I do think it highlights the potential of systems like MADLIB [1] where you can push the data crunching into the database, while having that be abstracted away in your primary programming language (in this case, R.) One could imagine a world where you wrote the ruby code and it was somehow mapped in to be run on the database. But that's not the world we live in today unfortunately. It's really a shame, because people using a database as capable of PostgreSQL but with a fairly dumb ORM on top are under-utilizing its potential, for sure, but there's not really a great path afaik to leverage it. (I remember years ago MS introduced running C# inside of SQL Server, I am not sure if that ever ended up panning out into being something smarter than the dumb "upload code to database manually" model we are used it.) [1] http://madlib.incubator.apache.org/ http://madlib.incubator.apache.org/

howdopasswordsw 10y ago
While you're right of course, I think you also just described why people have been switching to SOA (cough, I mean microservices). If you encapsulate the database and related logic behind its own service interface, then as you get big enough to care about query performance, then only the team working on that microservice needs to care about how complex/tricky/whatever the optimizations become!
- gfodor 10y ago
  That certainly sounds right -- though I guess I still am very skeptical that the complexity tradeoff there is the right one (at least until you hit the scale that dictates it.)
- ako 10y ago
  And because you can't really report across microservices, everyone is now setting up datalakes, where they aggregate all the data of their services.
blahi 10y ago
You can run R code in SQL Server, Oracle, Teradata, Hana and Vertica. All of these databases kick the crap out of HN darlings like Redshift and BigQuery. Special mention goes to Vertica which is free up to three nodes and/or 1TB data + 1 TB in flex tables. It is, like the rest, analytical AND transactional. Has proper admin features, proper edge case coverage and a truck load of features. It also has a very sensible licensing.
pg314 10y ago
You can write your stored procedures in a number of scripting languages. Out of the box PostgreSQL supports Tcl [1], Perl [2] and Python [3]. There's also a general mechanism to register other languages [4]. On github I found [5], which is embeds Ruby in PostgreSQL. I haven't used it and don't know how mature it is. Using the same scripting language in your stored procedures and application code might lessen the impedance mismatch. It doesn't solve testing, deployment/migrations and tooling. [1] https://www.postgresql.org/docs/current/static/pltcl.html https://www.postgresql.org/docs/current/static/pltcl.html [2] https://www.postgresql.org/docs/current/static/plperl.html https://www.postgresql.org/docs/current/static/plperl.html [3] https://www.postgresql.org/docs/current/static/plpython.html https://www.postgresql.org/docs/current/static/plpython.html [4] https://www.postgresql.org/docs/9.6/static/plhandler.html https://www.postgresql.org/docs/9.6/static/plhandler.html [5] https://github.com/knu/postgresql-plruby https://github.com/knu/postgresql-plruby
- inopinatus 10y ago
  That pl/ruby embedding is unmaintained for years and doesn't currently build. To me the most interesting current extension language is javascript, via PL/v8: http://pgxn.org/dist/plv8/doc/plv8.html http://pgxn.org/dist/plv8/doc/plv8.html - now you can have isomorphic code at all three tiers of your web stack.
pjmlp 10y ago
Actually most of our enterprise customers prefer to let the database to the work. Then again we tend to work with the likes of SQL Server, Oracle and similar.
sbov 10y ago
I would be interested in seeing what your actual proposed solution to the example problem is here. You seem to be implying that we should pull back the full list of posts and order & limit in memory. This might make sense for some scales and workloads but it definitely wouldn't be my first or second solution for the given example.
- gfodor 10y ago
  today, lambda (speed layer + batch layer) or kappa architecture (kafka streams) -- ymmv. not sure if I would build that infra out just for this single problem, but generally once that infra is in place it can be applied to many problems including this one. edit: (assuming i can't get it working with a jury rigged cronjob and the usual dumb solutions that will still use the ruby code. i'd try a bunch of those types of solutions first obv.)
ianamartin 10y ago
I may be in the minority here, and I'll happily accept that if I am. But my rule of thumb is that anything that can be done inside the database should be done inside the database. But I'm a python/C#/.Net person. The stored procs/functions go into a repo just like everything else does. It's not that big a deal. Alembic for migrations is pretty solid. And if you're dealing with a large code base, you log changes inside the DB itself. Use the database for version control. It's really manageable. It requires that your dev team really knows SQL. But I don't think that's a bad thing. It's fast, gets the job done, and if you're running a language that doesn't make it easy to deal with concurrency, it's nice to just let the database handle transactions. For everyone who isn't facebook or google, this is the way to go, in my experience. Let the db do the hard work, let your preferred language be the middleman, and have a reasonable front end framework do the presentation. I can scale a web app to millions of users on a couple hundred bucks a month of hardware with this approach. You can too.
- josteink 10y ago
  > It's really manageable. It requires that your dev team really knows SQL I'd question the credentials of any developer who doesn't know SQL. It's an essential skill across the entire IT-indutry. Not knowing it means you're obviously not serious about your job. And here you're talking like programmers not knowing it is a normal thing... Are we working in the same industry?
  - detaro 10y ago
    There is a large span between "knows SQL" as in "can write and read basic SQL query" and "really knows SQL". And I bet there are tons of developers who never have had the need to touch SQL or even any RDBMS at all. It's an essential skill in parts of the industry, it has no practical relevance in others.
    - asah 10y ago
      Upwork.com is a great way to fill that gap: tons of great SQL guys, very reasonable cost.
lm2s 10y ago
I believe that many people don't use stored procedures/functions because of lack of knowledge/know-how, difficulty or needing something done yesterday (which in itself is related to the previously), unfortunately. I say this because I've seen it happen multiple times.
memracom 10y ago
We are using Liquibase to manage the SQL code in the database as a separate GIT repository from the several applications that use the database. We have a dev database, and are beginning to use pgTAP for SQL unit tests. Code review for the SQL comes from BitBucket. GitHub works similarly for code review. The SQL code can be versioned and deployed as changesets either ins synchronization with an app change if that is appropriate, or separately. If you also embrace the concept of "database refactoring" then there is less need for migrations in lockstep with an app deploy. So, putting some of the business logic into the database can be done using modern development best practices. Yes there are switching costs and it means that developers need to really learn SQL and PostgreSQL's advanced features but it pays off. And if you break the lockstep of db migrations and application deployments then you can easily have people specialize by language. In fact, I would say that the era of the DBA is fading and what we really need are devops developers with a strong understanding of PostgreSQL who can do db architecture, db engineering, db design, and SQL coding/refactoring. Probably my perspective is different from a lot of people because I worked many years in large ISPs where Network Engineering folks designed everything, built it in test labs, verified everything, documented how to deploy and then handed it over to an Operations team that actually set up the routers and installed the configuration code. I don't see coding as the top of the pyramid. Instead it is Engineering Design and Architecture of applications that we should all aspire to.

by gfodor 10y ago

howdopasswordsw 10y ago

While you're right of course, I think you also just described why people have been switching to SOA (cough, I mean microservices). If you encapsulate the database and related logic behind its own service interface, then as you get big enough to care about query performance, then only the team working on that microservice needs to care about how complex/tricky/whatever the optimizations become!

gfodor 10y ago
That certainly sounds right -- though I guess I still am very skeptical that the complexity tradeoff there is the right one (at least until you hit the scale that dictates it.)
ako 10y ago
And because you can't really report across microservices, everyone is now setting up datalakes, where they aggregate all the data of their services.

blahi 10y ago

You can run R code in SQL Server, Oracle, Teradata, Hana and Vertica. All of these databases kick the crap out of HN darlings like Redshift and BigQuery. Special mention goes to Vertica which is free up to three nodes and/or 1TB data + 1 TB in flex tables. It is, like the rest, analytical AND transactional. Has proper admin features, proper edge case coverage and a truck load of features. It also has a very sensible licensing.

pg314 10y ago

You can write your stored procedures in a number of scripting languages. Out of the box PostgreSQL supports Tcl [1], Perl [2] and Python [3]. There's also a general mechanism to register other languages [4]. On github I found [5], which is embeds Ruby in PostgreSQL. I haven't used it and don't know how mature it is. Using the same scripting language in your stored procedures and application code might lessen the impedance mismatch. It doesn't solve testing, deployment/migrations and tooling. [1] https://www.postgresql.org/docs/current/static/pltcl.html https://www.postgresql.org/docs/current/static/pltcl.html [2] https://www.postgresql.org/docs/current/static/plperl.html https://www.postgresql.org/docs/current/static/plperl.html [3] https://www.postgresql.org/docs/current/static/plpython.html https://www.postgresql.org/docs/current/static/plpython.html [4] https://www.postgresql.org/docs/9.6/static/plhandler.html https://www.postgresql.org/docs/9.6/static/plhandler.html [5] https://github.com/knu/postgresql-plruby https://github.com/knu/postgresql-plruby

inopinatus 10y ago
That pl/ruby embedding is unmaintained for years and doesn't currently build. To me the most interesting current extension language is javascript, via PL/v8: http://pgxn.org/dist/plv8/doc/plv8.html http://pgxn.org/dist/plv8/doc/plv8.html - now you can have isomorphic code at all three tiers of your web stack.

pjmlp 10y ago

Actually most of our enterprise customers prefer to let the database to the work. Then again we tend to work with the likes of SQL Server, Oracle and similar.

sbov 10y ago

I would be interested in seeing what your actual proposed solution to the example problem is here. You seem to be implying that we should pull back the full list of posts and order & limit in memory. This might make sense for some scales and workloads but it definitely wouldn't be my first or second solution for the given example.

gfodor 10y ago
today, lambda (speed layer + batch layer) or kappa architecture (kafka streams) -- ymmv. not sure if I would build that infra out just for this single problem, but generally once that infra is in place it can be applied to many problems including this one. edit: (assuming i can't get it working with a jury rigged cronjob and the usual dumb solutions that will still use the ruby code. i'd try a bunch of those types of solutions first obv.)

ianamartin 10y ago

I may be in the minority here, and I'll happily accept that if I am. But my rule of thumb is that anything that can be done inside the database should be done inside the database. But I'm a python/C#/.Net person. The stored procs/functions go into a repo just like everything else does. It's not that big a deal. Alembic for migrations is pretty solid. And if you're dealing with a large code base, you log changes inside the DB itself. Use the database for version control. It's really manageable. It requires that your dev team really knows SQL. But I don't think that's a bad thing. It's fast, gets the job done, and if you're running a language that doesn't make it easy to deal with concurrency, it's nice to just let the database handle transactions. For everyone who isn't facebook or google, this is the way to go, in my experience. Let the db do the hard work, let your preferred language be the middleman, and have a reasonable front end framework do the presentation. I can scale a web app to millions of users on a couple hundred bucks a month of hardware with this approach. You can too.

josteink 10y ago
> It's really manageable. It requires that your dev team really knows SQL I'd question the credentials of any developer who doesn't know SQL. It's an essential skill across the entire IT-indutry. Not knowing it means you're obviously not serious about your job. And here you're talking like programmers not knowing it is a normal thing... Are we working in the same industry?
- detaro 10y ago
  There is a large span between "knows SQL" as in "can write and read basic SQL query" and "really knows SQL". And I bet there are tons of developers who never have had the need to touch SQL or even any RDBMS at all. It's an essential skill in parts of the industry, it has no practical relevance in others.
  - asah 10y ago
    Upwork.com is a great way to fill that gap: tons of great SQL guys, very reasonable cost.

lm2s 10y ago

I believe that many people don't use stored procedures/functions because of lack of knowledge/know-how, difficulty or needing something done yesterday (which in itself is related to the previously), unfortunately. I say this because I've seen it happen multiple times.

memracom 10y ago

We are using Liquibase to manage the SQL code in the database as a separate GIT repository from the several applications that use the database. We have a dev database, and are beginning to use pgTAP for SQL unit tests. Code review for the SQL comes from BitBucket. GitHub works similarly for code review. The SQL code can be versioned and deployed as changesets either ins synchronization with an app change if that is appropriate, or separately. If you also embrace the concept of "database refactoring" then there is less need for migrations in lockstep with an app deploy. So, putting some of the business logic into the database can be done using modern development best practices. Yes there are switching costs and it means that developers need to really learn SQL and PostgreSQL's advanced features but it pays off. And if you break the lockstep of db migrations and application deployments then you can easily have people specialize by language. In fact, I would say that the era of the DBA is fading and what we really need are devops developers with a strong understanding of PostgreSQL who can do db architecture, db engineering, db design, and SQL coding/refactoring. Probably my perspective is different from a lot of people because I worked many years in large ISPs where Network Engineering folks designed everything, built it in test labs, verified everything, documented how to deploy and then handed it over to an Operations team that actually set up the routers and installed the configuration code. I don't see coding as the top of the pyramid. Instead it is Engineering Design and Architecture of applications that we should all aspire to.