4 ms·

It seems to me that you're advocating changing implementation details (binary query protocol instead of SQL, map-reduce queries instead of sprocs) and claiming

It seems to me that you're advocating changing implementation details (binary query protocol instead of SQL, map-reduce queries instead of sprocs) and claiming that it is somehow fundamentally different. But I don't see how that would be the case. How is a binary query protocol fundamentally different than calling a sproc? How is a map-reduce job in Javascript any different on the attributes you mentioned than ad-hoc SQL or a sproc? Obviously map-reduce is a fundamentally different approach to data processing, but it seems to have similar traits in regards to source control, testability, etc.

lmm 10y ago
Binary query protocol is just a tweak, but it's a tweak that most RDBMSes are missing, and it matters for some workloads. Switching to a model where you supply your own map-reduce or pipeline is a real shift, I think, from the DB as a framework that manages querying for you to more of a library/toolkit you can use to write your own computations. The indexed tables model is an incredibly effective compromise, but it's still a compromise - if you know specifically what you need to do, you can do it better. Running a first-class programming language with full support for usual programming language tools is a major difference for developability, testability, libraries and so on. Deployment model depends on the datastore - plenty of non-SQL ones have room for improvement here - but I think the traditional RDBMS still has the worst of it.
- breischl 10y ago
  Huh, interesting. I wonder if this is down to us having used different RDBMSes? Most of my experience has been with SQL Server, which I think does use a (mostly) binary wire protocol, at least when calling sprocs. It also has a pretty good query optimizer - I've spent a lot of time trying to beat it with hand-tuned queries and only come up with something better about 50% of the time. And there are a lot of tools for source controlling the DB scripting and testing everything. It's not hard to do unit test style runs that set up and tear down tables and DBs. Has that not been your experience?
  - lmm 10y ago
    > which I think does use a (mostly) binary wire protocol, at least when calling sprocs Hmm. I'm used to invoking storedprocedures via "select mysproc(param1, param2, ...)" which still has to be formed into a string and then parsed on the DB side using the arbitrary-SQL parser (because there's no way for the DB to know a priori that it's not a "regular" query). Does SQL server have some special case binary protocol for invoking them directly? > It also has a pretty good query optimizer - I've spent a lot of time trying to beat it with hand-tuned queries and only come up with something better about 50% of the time. The query planner can usually run the best query possible with the indices that exist, sure. But you have to fit your calculation into this model of indices that are built on insert and everything else happening at query time. Or you go down the route of lazy materialized views that make use of other lazy materialized views, specifying indexing strategies... I mean I think you can ultimately express any data processing pipeline in a RDBMS if you try hard enough (though you have to use database-specific features that tend to be less-well supported by the ecosystem), but at some point it's easier to just have a first-class programming language that has access to the data, and write the code that you want to run. > And there are a lot of tools for source controlling the DB scripting and testing everything. It's not hard to do unit test style runs that set up and tear down tables and DBs. Up to a point. It's easy to end up with "unit" tests that take a second for each test, which mean it's not really practical to get good coverage of logic there. In terms of source controlling and so on I guess the big problem is that you now have a distributed system written in two quite different technologies. So you've got to figure out a release and deployment process that handles both, and a lot of shops don't seem to bother. If you're already running a multi-language microservice architecture then this is probably a lot less of an issue.
    - breischl 10y ago
      TBH I've never really looked at how TDS (the SQLServer wire protocol) works. From a code level, in ADO.NET you would create a query object, set the type to "sproc", set the name of the sproc, and attach parameters as objects. I bet the sproc name is going across as text, and maybe the param names, but that's probably it. >>So you've got to figure out a release and deployment process that handles both, and a lot of shops don't seem to bother. Yeah, that's true. Setting up something along the lines of Rails' migrations isn't really that hard, but many don't bother.

lmm 10y ago

Binary query protocol is just a tweak, but it's a tweak that most RDBMSes are missing, and it matters for some workloads. Switching to a model where you supply your own map-reduce or pipeline is a real shift, I think, from the DB as a framework that manages querying for you to more of a library/toolkit you can use to write your own computations. The indexed tables model is an incredibly effective compromise, but it's still a compromise - if you know specifically what you need to do, you can do it better. Running a first-class programming language with full support for usual programming language tools is a major difference for developability, testability, libraries and so on. Deployment model depends on the datastore - plenty of non-SQL ones have room for improvement here - but I think the traditional RDBMS still has the worst of it.

breischl 10y ago
Huh, interesting. I wonder if this is down to us having used different RDBMSes? Most of my experience has been with SQL Server, which I think does use a (mostly) binary wire protocol, at least when calling sprocs. It also has a pretty good query optimizer - I've spent a lot of time trying to beat it with hand-tuned queries and only come up with something better about 50% of the time. And there are a lot of tools for source controlling the DB scripting and testing everything. It's not hard to do unit test style runs that set up and tear down tables and DBs. Has that not been your experience?
- lmm 10y ago
  > which I think does use a (mostly) binary wire protocol, at least when calling sprocs Hmm. I'm used to invoking storedprocedures via "select mysproc(param1, param2, ...)" which still has to be formed into a string and then parsed on the DB side using the arbitrary-SQL parser (because there's no way for the DB to know a priori that it's not a "regular" query). Does SQL server have some special case binary protocol for invoking them directly? > It also has a pretty good query optimizer - I've spent a lot of time trying to beat it with hand-tuned queries and only come up with something better about 50% of the time. The query planner can usually run the best query possible with the indices that exist, sure. But you have to fit your calculation into this model of indices that are built on insert and everything else happening at query time. Or you go down the route of lazy materialized views that make use of other lazy materialized views, specifying indexing strategies... I mean I think you can ultimately express any data processing pipeline in a RDBMS if you try hard enough (though you have to use database-specific features that tend to be less-well supported by the ecosystem), but at some point it's easier to just have a first-class programming language that has access to the data, and write the code that you want to run. > And there are a lot of tools for source controlling the DB scripting and testing everything. It's not hard to do unit test style runs that set up and tear down tables and DBs. Up to a point. It's easy to end up with "unit" tests that take a second for each test, which mean it's not really practical to get good coverage of logic there. In terms of source controlling and so on I guess the big problem is that you now have a distributed system written in two quite different technologies. So you've got to figure out a release and deployment process that handles both, and a lot of shops don't seem to bother. If you're already running a multi-language microservice architecture then this is probably a lot less of an issue.
  - breischl 10y ago
    TBH I've never really looked at how TDS (the SQLServer wire protocol) works. From a code level, in ADO.NET you would create a query object, set the type to "sproc", set the name of the sproc, and attach parameters as objects. I bet the sproc name is going across as text, and maybe the param names, but that's probably it. >>So you've got to figure out a release and deployment process that handles both, and a lot of shops don't seem to bother. Yeah, that's true. Setting up something along the lines of Rails' migrations isn't really that hard, but many don't bother.