5 ms·

As a data platform manager in a data-rich company I agree: programmers tend to prefer their favourite hammers to sql, even if a modern relational database can d

As a data platform manager in a data-rich company I agree: programmers tend to prefer their favourite hammers to sql, even if a modern relational database can do the thing much better. But! As somebody with a relatively good understanding of a history of sql, relational dbs and related concepts I have to add: SQL is often to blame. All the the amazing engineering that goes into database engines, clean and coherent ideas of relational algebra, optimisability of a declarative approach to computaion - all of that gets bad rep because of the nightmare of sql-the-language. The standard, the syntax, every little bit that could go wrong is just wrong from the point of view of a language designer. Composability, modularity, predictability, even core null-related defaults. But it's everywhere. We just have to accept it.

jonhohle 4y ago
It’s not just the language, it’s schema evolution, data distribution, and exposed APIs. I don’t want to give other teams direct access to a DB and have them Not only take a dependency on the schema, but have the ability to run arbitrary queries that may exhaust resources in ways that impact normal operations. If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. If other teams need a replica to perform their arbitrary queries, I’d much rather have them using a richer data model that they can normalize into whatever form suits their needs than have to conflate that into a source of truth data store. If you have a single business unit and can get away with commingling concerns within a small team, great, throw it all in a single DB. If it makes sense to split, however, do it quick and early to avoid a decoupling hell that is more expensive then having split in the first place.
- andylynch 4y ago
  That’s not a new problem though? The classic way to solve this in database is to expose the data API as stored procedures/views and restrict query access on the actual tables to just the DBAs - I think even MySQL which was late to the party here has had this ability for some time now.
- marcosdumay 4y ago
  > If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. And then they will depend on your API data schema... Yes, schema evolution is hard, but databases have many tools to help here that you will either have to recreate on your APIs or live without and have a harder time. Either way, all the trouble comes from data evolution, and any schema-only change is trivial to deal with. Data distribution is something that varies from one DB to another, they usually have very good performance that is hard to replicate on your application layer, but are very hard to setup and keep running. But the point about control of resource usage is a good one.
  - yunohn 4y ago
    > And then they will depend on your API data schema... +100 to this
    - jonhohle 4y ago
      But that API schema is unrelated to the underlying DB schema. I’ve been able to run services with different backends (eventually consistent & low latency vs transactional) exposing the same API. That would not have been possible by just giving consumers access to a DB. The DB can do everything can’t seem to understand this, for some reason.
      - marcosdumay 4y ago
        Moving the goalposts, I can see. Of course, merging different backends can not be done on the backend. Either you add a layer or you do it on the client.
        h_r 4y ago
        That seems to be an odd way of looking at it, IMO. Most of the time, the entire point of building an API is to present consistent functionality to any consumer, not only ones under your control. Also, a well-behaved API is versioned so as to allow evolution of the API without breaking existing clients who can upgrade to new versions as they are able.
- zvrba 4y ago
  > I don’t want to give other teams direct access to a DB [...] You don't have to. Package up necessary queries into views and/or stored procedures and grant permissions only on those. Views can also shield from schema changes.
  - jonhohle 4y ago
    That also requires all data to be in the same DB. Often that’s not practical or possible.
    - Tostino 4y ago
      Postgres FDW's do wonders to centralize this type of access.
    - zvrba 4y ago
      In SQLServer you can define external tables that access data on another server, or even parses a file.
klysm 4y ago
is there something fundamental from us making a new frontend to something like postgres? I think all the solutions that compile to SQL kinda work but it would be nice to have a new native interface that sucks less.
- vkazanov 4y ago
  Nope. Nothing but industry-wide inertia is so massive by now that it just doesn't make sense to switch. Nosqls tried hard and went nowhere. What i find funny is that most dbs translate sql into an internal representation that is remarkably similar to a proper relational algebra and optimise on that. I'd really just prefer the alebraic language as described in the original ages old paper. There were also a few dbs trying to push sql-but-better languages... haven't heard about them for a while.
- jstrong 4y ago
  I have experimented with a different query model from sql for time series data. A query took the form of a Rhai script. (Rhai is a scripting language that has great interop with rust, so it was similar to how lua would be used to script parts of a game.) Each query script would act on a few objects in global scope: `db` (handle to database), `start`, and `end` (time range of grafana dashboard that query was for). I found being able to write imperative (rather than declarative) code to build a query to be extremely powerful, especially for storing variables and looping over things. e.g. query script - just to get a feel for it: let dalmp = db.ts("pjm-da-lmp/western-hub") // ie retrieve the timeseries named 'pjm..' .with_time_range(start, end); let rtlmp = db.ts("pjm-5min-lmp-rt-lmp/western-hub") .with_time_range(start, end) .resample("1h", "mean"); let da_err = rtlmp.diff(dalmp); #{ dalmp: dalmp, rtlmp: rtlmp, da_err: da_err, } A query script would be expected to return a dictionary-like object. the keys would be used as labels and the values would each be a time series object. This is not the perfect solution for every problem but though it might be interesting to see an example of a very different approach to querying compared to sql.
darksaints 4y ago
As someone who absolutely loves the power of SQL, I abhor the footguns involved. Especially with null-based ternary logic that is incomprehensible to most people.
- SoftTalker 4y ago
  You have to learn how your database handles NULLs, different databases do it slightly differently. Once you do that, you're fine. One big thing is that NULL should never "mean" anything in a business sense. It is the absence of a value, hence it cannot mean anything. By the same token you need to understand how your database handles concurrency. Different databases do it differently.
  - winrid 4y ago
    Many businesses are absent of value :)
  - branko_d 4y ago
    > One big thing is that NULL should never "mean" anything in a business sense. It is the absence of a value, hence it cannot mean anything I always had a problem with that notion. I mean, it has a memory representation, it has a set of operators you can apply to it, defined behavior in UNIQUE and FOREIGN KEY constraints etc. All this is well documented (though can behave slightly differently between databases, as you mentioned). So, it has a set of valid values (just one: NULL) and a set of valid operations, so it's a type! And now you have a type that looks somewhat similar to a null in "normal" programing languages, and SQL generally lacks the mechanisms for inventing your own types, so why wouldn't you use it in your business logic where it makes sense? The design of NULL seems like a historical accident anyway. The best I can discern, there was a need for something to behave as "excluded" in the context of FOREIGN KEYs and outer joins, and so that semantic was just passed along to other areas where it made less sense. I think a better type system and a better separation between comparison logic and the core type would have obviated most of the NULL's weirdness and made it far less foot-gunny in the process...
  - darksaints 4y ago

jonhohle 4y ago

It’s not just the language, it’s schema evolution, data distribution, and exposed APIs. I don’t want to give other teams direct access to a DB and have them Not only take a dependency on the schema, but have the ability to run arbitrary queries that may exhaust resources in ways that impact normal operations. If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. If other teams need a replica to perform their arbitrary queries, I’d much rather have them using a richer data model that they can normalize into whatever form suits their needs than have to conflate that into a source of truth data store. If you have a single business unit and can get away with commingling concerns within a small team, great, throw it all in a single DB. If it makes sense to split, however, do it quick and early to avoid a decoupling hell that is more expensive then having split in the first place.

andylynch 4y ago
That’s not a new problem though? The classic way to solve this in database is to expose the data API as stored procedures/views and restrict query access on the actual tables to just the DBAs - I think even MySQL which was late to the party here has had this ability for some time now.
marcosdumay 4y ago
> If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. And then they will depend on your API data schema... Yes, schema evolution is hard, but databases have many tools to help here that you will either have to recreate on your APIs or live without and have a harder time. Either way, all the trouble comes from data evolution, and any schema-only change is trivial to deal with. Data distribution is something that varies from one DB to another, they usually have very good performance that is hard to replicate on your application layer, but are very hard to setup and keep running. But the point about control of resource usage is a good one.
- yunohn 4y ago
  > And then they will depend on your API data schema... +100 to this
  - jonhohle 4y ago
    But that API schema is unrelated to the underlying DB schema. I’ve been able to run services with different backends (eventually consistent & low latency vs transactional) exposing the same API. That would not have been possible by just giving consumers access to a DB. The DB can do everything can’t seem to understand this, for some reason.
    - marcosdumay 4y ago
      Moving the goalposts, I can see. Of course, merging different backends can not be done on the backend. Either you add a layer or you do it on the client.
      - h_r 4y ago
        That seems to be an odd way of looking at it, IMO. Most of the time, the entire point of building an API is to present consistent functionality to any consumer, not only ones under your control. Also, a well-behaved API is versioned so as to allow evolution of the API without breaking existing clients who can upgrade to new versions as they are able.
zvrba 4y ago
> I don’t want to give other teams direct access to a DB [...] You don't have to. Package up necessary queries into views and/or stored procedures and grant permissions only on those. Views can also shield from schema changes.
- jonhohle 4y ago
  That also requires all data to be in the same DB. Often that’s not practical or possible.
  - Tostino 4y ago
    Postgres FDW's do wonders to centralize this type of access.
  - zvrba 4y ago
    In SQLServer you can define external tables that access data on another server, or even parses a file.

klysm 4y ago

is there something fundamental from us making a new frontend to something like postgres? I think all the solutions that compile to SQL kinda work but it would be nice to have a new native interface that sucks less.

vkazanov 4y ago
Nope. Nothing but industry-wide inertia is so massive by now that it just doesn't make sense to switch. Nosqls tried hard and went nowhere. What i find funny is that most dbs translate sql into an internal representation that is remarkably similar to a proper relational algebra and optimise on that. I'd really just prefer the alebraic language as described in the original ages old paper. There were also a few dbs trying to push sql-but-better languages... haven't heard about them for a while.
jstrong 4y ago
I have experimented with a different query model from sql for time series data. A query took the form of a Rhai script. (Rhai is a scripting language that has great interop with rust, so it was similar to how lua would be used to script parts of a game.) Each query script would act on a few objects in global scope: `db` (handle to database), `start`, and `end` (time range of grafana dashboard that query was for). I found being able to write imperative (rather than declarative) code to build a query to be extremely powerful, especially for storing variables and looping over things. e.g. query script - just to get a feel for it: let dalmp = db.ts("pjm-da-lmp/western-hub") // ie retrieve the timeseries named 'pjm..' .with_time_range(start, end); let rtlmp = db.ts("pjm-5min-lmp-rt-lmp/western-hub") .with_time_range(start, end) .resample("1h", "mean"); let da_err = rtlmp.diff(dalmp); #{ dalmp: dalmp, rtlmp: rtlmp, da_err: da_err, } A query script would be expected to return a dictionary-like object. the keys would be used as labels and the values would each be a time series object. This is not the perfect solution for every problem but though it might be interesting to see an example of a very different approach to querying compared to sql.

darksaints 4y ago

As someone who absolutely loves the power of SQL, I abhor the footguns involved. Especially with null-based ternary logic that is incomprehensible to most people.

SoftTalker 4y ago
You have to learn how your database handles NULLs, different databases do it slightly differently. Once you do that, you're fine. One big thing is that NULL should never "mean" anything in a business sense. It is the absence of a value, hence it cannot mean anything. By the same token you need to understand how your database handles concurrency. Different databases do it differently.
- winrid 4y ago
  Many businesses are absent of value :)
- branko_d 4y ago
  > One big thing is that NULL should never "mean" anything in a business sense. It is the absence of a value, hence it cannot mean anything I always had a problem with that notion. I mean, it has a memory representation, it has a set of operators you can apply to it, defined behavior in UNIQUE and FOREIGN KEY constraints etc. All this is well documented (though can behave slightly differently between databases, as you mentioned). So, it has a set of valid values (just one: NULL) and a set of valid operations, so it's a type! And now you have a type that looks somewhat similar to a null in "normal" programing languages, and SQL generally lacks the mechanisms for inventing your own types, so why wouldn't you use it in your business logic where it makes sense? The design of NULL seems like a historical accident anyway. The best I can discern, there was a need for something to behave as "excluded" in the context of FOREIGN KEYs and outer joins, and so that semantic was just passed along to other areas where it made less sense. I think a better type system and a better separation between comparison logic and the core type would have obviated most of the NULL's weirdness and made it far less foot-gunny in the process...
- darksaints 4y ago