This is incredible to see all in one place. I’m preaching SQL to my Org. and it’s only getting preachier over the years, as I see new technologies come and go (OLAP, NoSQL and it’s many variations including Hadoop, Azure Cosmos DB).
For an Org. of our size, I’m not sure if these new fads make it any easier. Even if they helped with data streaming, we still are having to move it to a SQL warehouse where we can combine it with other Org. data to answer business questions. Not sure if anyone has built out a fully operational reporting architecture without using SQL on HN. Long live SQL!
I've only had contact with DBs during my university years so please bear with me, but at least to me (mostly a systems programmer who played around with functional and logic programming languages) SQL seems, I dunno.. very crude? For instance it seems to me that PROLOG is a lot better at querying/defining relational facts.
Also every time I looked into SQL DBs I felt uncomfortable having to patch together SQL queries as strings and compile them at runtime. Why can't I define a DB schema in my compiled programming language and have it produce a typesafe query that can execute immediately? I know there's wrappers that help you define queries in a typesafe manner, but afaik these still generate query strings in the background.
Few people, when they say "SQL is amazing!", mean the language.
The language is ok minus, it's usable and isn't a problem center. String parsing isn't expensive enough for anyone to replace, and there are many benefits to the 100% language decoupling it ensures.
What we mean is "RDBMSs are amazing!", and they are. Humanity has spent a lot of resources on their design and evolution, making them into systems that efficiently solve your hard problems years before you first find out you have them.
You are absolutely right! They seam easier to reason about. But relational databases actually are simpler to reason about when you compare equivalent feature sets. I've seen people argue it is just a key value store, so it is dead simple, right? But then they didn't just use key value store features. They used or implemented themselves joins, transactions, consistency constraints under parallel modifications, etc. But this is not the system for which they determined that it is simple.
But even for simple use cases durability might be a requirement. And that is not so simple to get right.
You're right too, it's hard to know from the start what you will need, and your needs will often grow to the point that you'll need a full relational database.
AIUI, you can have a key-value store is an SQL RDBMS: Just make a big bunch of two-column -- key and value -- tables.
Also, again AIUI, you can build an RDBMS on top of a key-value store: "Just" build a whole lot of key-value pairs where the "value" is a reference to another key more often than not. (Perhaps not just for actual references to other related tables -- or what would be another table in a real RDBMS -- but for each separate key-value store that would just be another column in a real RDBMS. Then I suppose your primary ("key column") key-value pair would have a lot of rows for each key; one for each "column"?)
I suspect most real-world usage of key-value stores in one fashion or another approaches the above... And then the vaunted "simplicity" has gone out the window (and made SPLAT! on the ground far below).
But by then you're so [invested in | married to | sunk cost-fallacied with] your key-value store that changing to a real RDBMS is nigh impossible.
So better start with a real RDBMS from the beginning.
I'd love to have the ability to write a direct query plan, for the rare occasion where the query planner does something stupid. But, ya SQL is great for the majority of situations.
OLAP isn't a technology and it didn't go away. That's an approach your and many other orgs have been using for decades to do data warehousing. NoSQLs definitely didn't go away.
The need for denormalized data and data warehousing will never go away.
> I’m preaching SQL to my Org. and it’s only getting preachier over the years
We are all-in on using SQL (SQLite) for our business logic these days. It's wonderful being able to watch the business build most of our customer experiences for us. No more lost-in-translation bullshit exercises between the biz and the tech. Our developers are now mostly tending to the SQL matrix that everyone else works inside of every day. Most of my support issues are along the axis of "Why isnt customer property X showing up in table Y under circumstances Z". We have built a lot of custom tooling so we can quickly answer this question with confidence. 9/10 times the resolution is 1 line in a mapper that needs to be updated somewhere.
For me, SQL only works if the schema is clean and the business can understand why it is constructed in the way that it is. If you were to dump your SQL schema to excel sheets and email it to your project manager, would they have a clue how to piece these things back together or why things are represented the way they are? A well-normalized schema should be intuitive to join together by even non-domain experts. Simply being consistent with naming throughout is 80% of this battle in my mind. When someone says the word "Customer" in context of your SQL schema, everyone on the team should implicitly be on the same page regarding properties and relations around this type.
Unlike other such maps this map is a bit form over function. There are various crossings up or down that are there only to make it more visually interesting (more like a city metro map) but harder to follow.
At a workshop at Microsoft a few years ago they proudly told us that they at that point only maintained 5 internal forks of SQL server, recently down from 8.
They make it look like Interbase ended with the open-sourcing of Firebird. In reality that turned out to be a fork; Interbase itself is still available from Embarcadero.
Makes one a bit suspicious about how accurate it is in other corners I know less about.
24 comments
[ 9.5 ms ] story [ 541 ms ] threadFor an Org. of our size, I’m not sure if these new fads make it any easier. Even if they helped with data streaming, we still are having to move it to a SQL warehouse where we can combine it with other Org. data to answer business questions. Not sure if anyone has built out a fully operational reporting architecture without using SQL on HN. Long live SQL!
What would you use INSTEAD of SQL?
Also every time I looked into SQL DBs I felt uncomfortable having to patch together SQL queries as strings and compile them at runtime. Why can't I define a DB schema in my compiled programming language and have it produce a typesafe query that can execute immediately? I know there's wrappers that help you define queries in a typesafe manner, but afaik these still generate query strings in the background.
How would you CRUD data DDL, DQL, DML, DCL, TCL against tables (not facts or variables) without SQL? Magic?
And why are you cobbling queries together manually and not using an ORM or library? SMH.
The language is ok minus, it's usable and isn't a problem center. String parsing isn't expensive enough for anyone to replace, and there are many benefits to the 100% language decoupling it ensures.
What we mean is "RDBMSs are amazing!", and they are. Humanity has spent a lot of resources on their design and evolution, making them into systems that efficiently solve your hard problems years before you first find out you have them.
ACID, locking, referential integrity, SQL (language flavors and RE DBMSes) aren't appreciated, probably due to a lack of CS education.
Dunno Mongo, but perhaps even that is better than having it shredded.
But even for simple use cases durability might be a requirement. And that is not so simple to get right.
Also, again AIUI, you can build an RDBMS on top of a key-value store: "Just" build a whole lot of key-value pairs where the "value" is a reference to another key more often than not. (Perhaps not just for actual references to other related tables -- or what would be another table in a real RDBMS -- but for each separate key-value store that would just be another column in a real RDBMS. Then I suppose your primary ("key column") key-value pair would have a lot of rows for each key; one for each "column"?)
I suspect most real-world usage of key-value stores in one fashion or another approaches the above... And then the vaunted "simplicity" has gone out the window (and made SPLAT! on the ground far below).
But by then you're so [invested in | married to | sunk cost-fallacied with] your key-value store that changing to a real RDBMS is nigh impossible.
So better start with a real RDBMS from the beginning.
The need for denormalized data and data warehousing will never go away.
We are all-in on using SQL (SQLite) for our business logic these days. It's wonderful being able to watch the business build most of our customer experiences for us. No more lost-in-translation bullshit exercises between the biz and the tech. Our developers are now mostly tending to the SQL matrix that everyone else works inside of every day. Most of my support issues are along the axis of "Why isnt customer property X showing up in table Y under circumstances Z". We have built a lot of custom tooling so we can quickly answer this question with confidence. 9/10 times the resolution is 1 line in a mapper that needs to be updated somewhere.
For me, SQL only works if the schema is clean and the business can understand why it is constructed in the way that it is. If you were to dump your SQL schema to excel sheets and email it to your project manager, would they have a clue how to piece these things back together or why things are represented the way they are? A well-normalized schema should be intuitive to join together by even non-domain experts. Simply being consistent with naming throughout is 80% of this battle in my mind. When someone says the word "Customer" in context of your SQL schema, everyone on the team should implicitly be on the same page regarding properties and relations around this type.
Postgres / Enterprise DB converges towards compatibility with Oracle DBMS.
Unlike other such maps this map is a bit form over function. There are various crossings up or down that are there only to make it more visually interesting (more like a city metro map) but harder to follow.
Happy days...
Makes one a bit suspicious about how accurate it is in other corners I know less about.