Hacker News Reader

Comment by strbean | Hacker News Reader

> except for a brief period in 2010 where we thought we could kill it

Unfortunately, we never actually tried to kill SQL. We tried to kill relational databases, with the death of SQL as a side effect. This proved to be a stupid idea - non-relational data stores have value of course, but so do relational databases.

SQL, as you point out, is 48 years old. It shows. The ergonomics are awful. It uses natural-language inspired syntax that has fallen completely out of favor (with good reason imho). The ordering of fields and tables precludes the use of autocomplete. It uses orthogonal syntax for identical constructions, i.e.

    INSERT ... (col1, col2, ...) VALUES (val1, val2, ...)

vs.

    UPDATE ... col1=val1, col2=val2, ...

And worst of all, it comes with such a woefully inadequate standard that half of the universally supported features are expressed in a completely different fashion for each vendor.

It is very common to argue that its longevity is due to its excellence as a language. I think it's longevity is due to its broad target audience, which includes plenty of people in less technical roles who are high on the org chart. Tech workers are happy to jump on new technology that improves ergonomics (or shiny new toys that are strictly more interesting because they are new). Executives who's primary way of interacting with the tech stack is querying a read only replica are much less open to learning a new language.

View on HN

SQL expresses relational algebra [0] which is the language defining data relationships and manipulation. It hasn't changed in 50 years for the same reason why B-Trees and sorting algorithms haven't: it's foundational to computer science.

Database-specific "SQL extensions" are in my experience just administration commands, e.g. `VACUUM` or `CREATE EXTENSION` in Postgres. They help operate the DB, but have little to do with the actual data manipulation.

Killing SQL is like trying to kill computer science: you better come up with something better than a synonym.

[0] https://en.wikipedia.org/wiki/Relational_algebra

Nothing about relational algebra says you have to put your projection before you specify what relations are involved. SQL is a bad fit for modern tooling in ways that are a property of the language and are unrelated to it expressing relational algebra.

The notion that nothing has changed about B-Trees or sorting algorithms doesn't, I think, actually hold up... but this question is more like changing the API for a standard-ish B-Tree library than about modifying how the B-Tree itself works.

And that’s why Microsoft’s LINQ puts the entities (table names) first and then autocomplete just works. https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform...

As an aside: LINQ is the only “ORM” that is truly worth using. It is integrated into the language and you can use the same expressions and query expressions are first class types that can be passed around as expression trees and are then only translated either as code if you’re using in memory lists, database specific SQL, MongoQuery etc based on the provider you pass the query to.

> LINQ is the only “ORM” that is truly worth using

I've been thinking a lot about ORMs and they almost usually fall flat at some level since they are not nearly as expressive as a first order language is. I haven't had experience with LINQ but I know the Django ORM is this way.

I'm thinking the best approach if I had time/money would be to develop a better query language that is close enough to SQL for people to learn, but then also translates back down to SQL. LINQ looks really close to this, but I'd really want it to be cross language.

One of the aspects of QUEL that I really liked was that if you couldn't describe what you wanted, you could program it out if you needed to. Say you had some complex ranking algorithm that was hard to write in SQL directly.

LINQ is a full query language you can left join, right join, inner join limits, etc. It even maps C# functions to Sql functions. If you can express it with SQL, you can express it with LINQ.

Cross language will still be an issue. LINQ isn’t really an “ORM” it is a method to translate C# commands used to work over related collections to expression trees and those expression trees are interpreted/translated at runtime by a provider.

You need support from the language/runtime to treat expression trees as a first class type.

expression tree parser. It’s not for the faint of heart.

> If you can express it with SQL, you can express it with LINQ.

Not if it uses CTEs or other advanced SQL features. It can do basic select, join, group, sure. But that's just scratching the surface of how powerful SQL is. As for requiring language support: in Elixir, Ecto.Query essentially implements a LINQ-like DSL as a library (albeit with macros). See https://hexdocs.pm/ecto/Ecto.Query.html

From what I see a CTE is just an expression that you can reference.

In C#, if you did something like this

var seniorMales = from user in users where u.Age > 65 && u.Sex == “male” select u

seniorMales is just an IQueryable<User>

Later on you could say

var gaMales = seniorMales.Where(s => s.State == “GA”)

var floridaMale = seniorMales.Where(s => s.State ==“Florida”)

All three of those are just expressions that have not hit the database yet.

Then when you want to actually run the query and limit the number of rows, you can do

var result = floridaMales.Limit(20).ToList()

It would then create a query including a limit clause just as you would expect it to.

Now if you do

floridaMales.ToList().Limit(10)

It would return all of the rows in the table to the client and it would be limited on the client side (don’t do that).

Worse case, if LINQ can’t express in query syntax, a provider can add its own extensions in function syntax and the provider can still parse the expression tree.

Yes, I've been a professional C#/.NET developer before and understand LINQ and Entity Framework. The difference is that CTEs allow you to reference multiple tables at once and do inserts and updates on the intermediate result set. You can also use recursive CTEs to do hierarchical and graph-style queries, which you can't do with LINQ as far as I know.

LINQ also can't do window functions. Like I said, it covers the basics of SQL that most applications need. But as soon as you need to go beyond that you'll be writing custom SQL.

Never used C#, but in Java land we have a similar dsl jooq, which supports everything i’ve ever needed, including window functions https://www.jooq.org/doc/latest/manual/sql-building/column-e...

Yeah, there's nothing else quite like jOOQ as far as I know. I think SQLAlchemy in Python is the only other library that even comes close.

jooq is amazing and one of the things I really miss any time I leave Java land.

Then don't leave

> I'm thinking the best approach if I had time/money would be to develop a better query language that is close enough to SQL for people to learn, but then also translates back down to SQL

Have a look at EdgeQL, the query language that powers edgedb.

> LINQ puts the entities (table names) first and then autocomplete just works

I grew up with SQL so LINQ for a long time just reads so weird to me. I won't admit how long it was before I realized this fact you stated.

It still reads weird to me, but I get it.

SQL does express relational algebra, but OP is arguing it expresses it poorly, in a cumbersome way. There's nothing foundational about how exactly SQL chooses to express relational algebra, you could imagine many different syntaxes and specific semantics.

I concur, SQL is a liability in many ways.

The Third Manifesto[0] is 27 years old at this point. If it were possible to take seriously the proposition that SQL and relational algebra over databases were equivalent before its publication, it hasn't been since.

[0]: https://en.wikipedia.org/wiki/The_Third_Manifesto

I've been trying to get my hands on a corpus of Dataphor code, because Tutorial D is unsatisfactory for different reasons than SQL. What I've been able to glean of Dataphor is quite promising, but it isn't much.

>Killing SQL is like trying to kill computer science

SQL is a language that expresses relational algebra, killing it is like killing any other language i.e. nearly impossible because there is still code sitting around written in it, thus needing competent programmers to maintain, competent programmers who will be asked to write more code and will choose to do it in SQL if they can get away with it, rinse and repeat and so forth.

Once a language has a certain installed base it may be eternal - what size that installed base is I don't know.

The installed base is kind red herring, because most of the worlds largest companies use Oracle to hold their data. So you'd have to get Oracle on board.

Could there be a better alternative, oh yes, most certainly.

E.g. Every company I worked for needed to do SQL migrations. Unfortunately that isn't a part of SQL. Could it be? Yes! Is it? No! So everyone re-invents the wheel...

Db-specific extensions unfortunately aren't only admin commands. They include the whole PL/SQL thing, with concepts like triggers and stored procedures that should definitely be fundamental in SQL, but that nobody bothered to standardize, and eventually everybody implemented as they liked.

Stored procedures and even triggers are non-relational and have little to do with SQL proper. They are like Lua scripting inside nginx which are not directly related to anything HTTP.

On one hand, it might be nice to have completely portable triggers. On the other hand, the lack of standardization allowed to have approaches as differer as PL/SQL and T-SQL to emerge. It would be terrible to be stuck with some ancient COBOL-like SP / trigger syntax, required by the standard.

As someone who has worked across many DBs, I find the lack of a unified standard for procedures and triggers appalling and very time-consuming.

Granted, stored procedures and triggers aren't part of the relational model (strictly speaking). But they are the first layer built on top of the core - the one that packs multiple queries/statements together, introduces variables, and allows custom logic to be executed when the data changes.

The lack of standardization means that migrating from e.g. Oracle to Postgres/MySQL involves knowing the dialects and statements supported by each of them in order to migrate the PL/SQL or T-SQL logic - and, especially in the case of MySQL/MariaDB, some of those constructs may not be available at all.

That creates really a lot of friction when it comes to database migrations, especially for large databases. I've myself been working with this stuff for years, but I still have to regularly lookup how to write an IF or declare a variable for this or that DMBS, since we have such a proliferation of dialects and standards that one person can't keep all the variations in their mind.

> SQL expresses relational algebra

Actually, no. Relational algebra is based on sets. SQL is not. Perhaps you are confusing SQL with QUEL, which was used by Postgres in its younger days? QUEL evolved out of Codd's original Alpha language.

How is SQL not based on sets? One of the big issues I see when people first come to SQL is not thinking in sets. Instead they think in individual records and loops, which is the exact opposite on how to think about SQL.

Sets are collections of unique elements, and indeed relations are sets of tuples. But SQL only sticks to the tuples part, foregoing sets. It's quite possible, and quite likely if you're not careful, to end up with duplicate entires in your results to an unwanted effect.

SQL is only relational-like. With experience and care you can craft your queries in such a way that you do get true sets (e.g. using DISTINCT), but I see beginners – and sometimes even experts in a rush – overlook this quirk in a complex query resulting in what might seem okay in limited testing, but blows up when there is more data than the bare minimum needed to test. A language that truly follows Codd's relational model would produce sets always.

I expect SQL is considered difficult to learn exactly because of this deviation. It's quite unintuitive, especially if you go in thinking that it is actually relational. Nothing you can't work around with sufficient knowledge and experience, but that added knowledge and experience required is where the difficulty no doubt stems from and undoubtedly scares many away out of frustration when it doesn't work like you think it should in the interim.

Relational algebra is what makes SQL great: some actual strict math behind the design.

Ad-hoc syntax with hard-to-compose parts,is what makes SQL arbitrary and quirky. I would like a more algebraic syntax, with uniform, composable parts.

You mean it butchers relational algebra; composition is the name of the game, and yet that's somehow incredibly cumbersome in SQL.

> It uses orthogonal syntax for identical constructions, i.e.

Regarding your complaint ... those aren't identical constructions.

Insert has to have the syntax it does because it lets you insert multiple rows all with different values in a single statement:

    INSERT ... (C1, C2) VALUES (v1, v2), (v3, v4), (v5, v6)

Update doesn't allow that. The syntax for each of the above makes sense for the specific intention of each statement.

And further, insert … (select …)

They could have consolidated the syntax for both, though. No reason you couldn't have this instead:

    INSERT ... VALUES (C1=v1, C2=v2), (C1=v3, C2=v4), (C1=v5, C2=v6)

This would actually be more powerful because it would allow you to arbitrarily omit columns on a per-row basis, which you can't do with SQL today.

You can omit columns on a per-row basis with the DEFAULT keyword, it's just more cumbersome if your rows are very sparse. Your proposed syntax of:

  INSERT ... VALUES (C1=v1, C2=v2), (C3=v3, C4=v4), (C5=v5, C6=v6)

in standard SQL is

  INSERT ... (C1, C2, ... C6) VALUES (v1, v2, DEFAULT, DEFAULT, DEFAULT, DEFAULT), (DEFAULT, DEFAULT, v3, v4, DEFAULT, DEFAULT), ...

It would maybe be more powerful, but also less efficient. If you want to insert different sets of columns, you just use multiple INSERT statements. The point of the current syntax is to be able to efficiently bulk-insert many rows using the same set of columns. That set of columns needs to be validated and mapped to the internal offsets (or whatever) only once for inserting an arbitrarily large number of rows, instead of having to repeat that validation and preprocessing for each row.

What a bizarre use case. I highly doubt your scheme performs better than just using PREPARE AS INSERT to create a prepared statement and execution plan on the database side. Or are we still generating SQL using string interpolation in 2022?

Most people have an orm between them and the database.

Think bulk inserting into via the command why the format exists vs what you do in your language and/or framework.

If you call row.save() on one ORM object at a time, you'll get neither the performance benefits of prepared statements nor that of multi-row inserts. If you save many objects at a time, the ORM library will use prepared statements.

We're certainly still slurping up bulk SQL commands from raw text files in 2022. Kinda odd not to come across that use case.

I think it is pretty standard usage for ETL tools.

You are not inserting a few dozen records, you are inserting a several thousand records each query.

In fact, sometimes insert speed is measured in MB per second, not records per second.

And the point of gp is that SQL could do some work and sort these (a=b) values into groups that fit its inner requirements on its own. You can just omit a useful feature because it creates a minor inconvenience at a preparation phase, or you can include it. This is exactly these bad ergonomics some grand-commenter talked about.

They still aren't identical constructs, because the intended action is very different; multiple insertions result in multiple distinct records all with different values while multiple updates result in multiple records with the same new values

Having the syntax identical is inferior in every way except for the case where you want to build the string up programmatically, and that is a trivial problem to solve for the caller.

I think your proposal works better for an UPSERT keyword, as that is yet another different intended action.

> Unfortunately, we never actually tried to kill SQL. We tried to kill relational databases, with the death of SQL as a side effect.

I don’t know who “we” is, but, before NoSQL tried to replace RDBMS(well, starting before, these things overlapped it), there were efforts to provide alternatives to SQL for RDBMSs, some examples:

Query-by-Example. https://en.wikipedia.org/wiki/Query_by_Example

the D class of data languages. https://en.wikipedia.org/wiki/D_(data_language_specification...

No mention of PRQL yet, it's an effort to tackle some of these shortcomings

https://prql-lang.org/

Which is plain terrible and unnecessary. SQL works, it's easy to understand, doesn't need any kind of abstractions or stupefying measures like PRQL.

I think your last sentence gets at the crux of it. I think SQL is truly great for that use case of analytical queries over a read only database / data warehouse / parquet / whatever (ie. "OLAP" use cases). I think it's less great for the "OLTP" use cases. It still works fine, which is why it sticks around, but other approaches are more convenient for doing lots of smaller inserts, updates, and lookups and fewer table scans and aggregations.

You are selling SQL way short. SQL has stuck around because, compared to its complexity, it lets you do incredibly powerful things.

It's also great that much of the time if I come up with a crappy query... SQL knows how to optimize it for me. Easy to use, and fixes my stupids.

That's nothing to do with SQL, that's the database engine you're using (lexing, parsing, then) planning & optimising your query as best it can.

Much like C code say, it's not a feature of C the language that gcc, for example, can optimise your crappy code for whatever target platform.

Look at the analogy to general purpose programming languages.

Every single one, without exception, lets you do incredibly powerful things compared to its complexity.

This hasn't stopped the progression of general purpose programming languages, and I think the vast majority of developers would agree that there have been huge improvements in the last 50 years.

TIOBE [0] reckons that the top 10 programming languages are currently Python, C, Java, C++, C#, Visual Basic, JavaScript, "Assembly Language", SQL and PHP. The first five languages represent more than 50% of the users in TIOBE.

Apart from SQL, all of these languages are imperative, and most of them derive from C in some shape or form; K&R C was published in 1978, 44 years ago.

So, while I'd agree that there have certainly been huge incremental improvements in imperative programming in the last ~50 years, when we look at what people use on a daily basis, it's not really clear to me that the changes have been any greater in significance than the changes that SQL has gone through during the same period.

[0] https://www.tiobe.com/tiobe-index/

Yet every one of these “ancient” languages allows you to have human-readable common expressions, functions, sorts of classes, identifier scoping, heavy code reuse, flow parametrization, higher order operations (even C with some effort), libraries, frameworks, package management, interoperability, to name a few.

SQL is really good at its in-place one-time this-specific-case relational querying.

Things SQL is not even mediocre at: programming.

But aren't most people using ORM's? In which case the SQL code is more like a kind of byte code. SQL definitely is supper powerful, but it's also embedded deeply in all of the RDBMS's and tooling around them etc.

Lots of stuff untilizing SQL and the ability to do lots of cool stuff with SQL, doesn't actually mean that the Developer Experience or ergonomics around using SQL are good.

I also feel like part of the staying power of SQL is because it's the lingua franca of the database. I wonder if it's time to build and popularize some lower level language that could be targeted by ORMs and others to speak to DB engines. If we had a more flexible assembly language we might get more freedom at the programmer language level.

MySQL has something in that direction, where some operations can be expressed as expression trees by using Protobuf where a client can put some arbitrary query language on top: https://dev.mysql.com/doc/internals/en/x-protocol-messages-m... it is still a bit limited, though (like no join ...)

C# already does that, it converts LINQ to expression trees that are then converted by providers to their target query language. I’ve used it on one project to pass around the same query expressions to SQL server and Mongo - you pass a type of IQueryable<T> which represents an expression tree.

For instance

var seniors = query(from u in Users where u.Age > 65 && u.Sex == “male” select u)

IEnumerable<Users> query(IQueryable<Users>)

I've been using SQL for ~25 years and don't think the ergonomics are nearly as bad as people say. In your example the insert is done that way for good reason.

I know people hate listing the fields before the tables, but when I deal with data that's how I tend to think. I need X, Y, Z, now where do I get it from and do I need to filter.

From a vendor standpoint, it can take time getting new standards implemented, but it's definitely not half. One of the big issues here is that MySQL was and still is in some ways woefully inadequate. It's shortcomings are also often make people think they are relational database shortcomings in general.

> The ergonomics are awful. It uses natural-language inspired syntax that has fallen completely out of favor

I think SQL is so popular and has staying power for the same reason that our ridiculous gregorian calendar has staying power.

The reason we can't replace SQL is that it is the lingua franca between many client tools and data sources. From programming languages, to Analytics tools, to ETL tools to bulk data transfer tools, everything knows how to define SQL queries. On the datastorage side, almost everything is standardizing on SQL as its API: RDBMSes, but also NoSQL storage (etc, hadoop + hive, etc), data virtualization layers (denodo, aws athena, steam pipe).

You can't change this by providing a better query language in one of the datasource products, or by providing a better ORM. Because of the network effects it will be very hard to come up with something better that is universally adopted. Many have tried though but it's always limited to a small stack: MDX, DAX, Linq, Dplyr, Graphql, etc.

There may be opportunities to replace SQL as soon as we need something that moves away from relational algebra. Currently you see al lot of adoption on graph storage and graph query languages in the Data Fabric space, as users need to build queries reasoning about relationships between different datasets in the enterprise.

The other reason Data Fabrics could offer an opportunity here is that they're basically adding an layer of abstraction between all data sources and data consumers, and they the possibility to translate SQL into something else, e.g. graphql.

> SQL, as you point out, is 48 years old. It shows.

That's irrelevant and a fallacy. Plenty of amazing things are old and have stood the test of time. Vim and SQL have empowered me my entire career.

> It uses orthogonal syntax for identical constructions

There is an alternative INSERT syntax matching the UPDATE SET one:

  INSERT INTO table SET col1='val1', col2='val2';

The SELECT syntax is the one that's backwards IMHO (and that order hurts autocomplete too).

I disagree, the people high on the org chart are clamouring for cloud-based low structure stores on microsoft azure cloud maintained in India, while we warn them that we can migrate at a giant cost for a net loss in efficiency.

SQL and dbs are fine, they work, we could discuss beautifying sql for sure, but frankly we shouldnt solving data structuring problems that were already solved 50 years ago: we have real business to support instead.

The ergonomics issue of SQL can be workaround by transpiler.

I do like SQL the syntax but it's hard to discard the ecosystem around SQL.

For instance, I made better-sql [1] recently which generate SQL from a language similar to GraphQL and EdgeDB query language.

[1] https://better-sql.surge.sh

I hope that those two syntaxes could be unified in some future version.

Also, surely allowing FROM and SELECT clauses to be swapped in order shouldn't stress out any parser too much.

FROM and all subsequent JOINs, to be precise. They all generate a single namespace which SELECT uses. As far as I understand the grammar, FROM isn’t even a separate clause, but is itself a separator between a columnset and an expression of the form `t1 JOIN t2 ON cond12 JOIN t3 ON cond123 JOIN …`, where t<n> may be either a table name, or an arbitrarily nested subexpression of the same form.

When I was first learning to code, I once told someone that working with SQL felt like trying to wrestle with a mainframe, and I stand by that assertion.

To my primitive brain, NoSQL always sounded like "I am going to makeup data model and integrity on the fly and let the user figure out how to handle it". No Thanks. But may be I don't get it. GraphQL honestly gives me the same vibes. Happy to be corrected.

"Replacements" for SQL have been around as long as SQL. Remember the OODBMS rage of the eighties and nineties?

https://en.wikipedia.org/wiki/Object_database

I also wanted to kill old, obsolete, crummy SQL when I first learned it as a teenager. It reminded me of FORTRAN or COBAL or something.

It took quite a lot of theory and maturity to understand why RDBMS is the right paradigm. SQL is imperfect, but it's good enough to not be worth replacing.

Whenever I've seen benchmarks of postgresql managing JSON versus mongodb, postgresql won (at least comparing apples-to-apples; postgresql is much more conservative by default, in terms of reliability and data consistency, than mongodb). There's literally no reason to going with a lot of NoSQL databases over using a SQL database as a key-value store.

> I also wanted to kill old, obsolete, crummy SQL when I first learned it as a teenager. It reminded me of FORTRAN or COBAL or something.

> It took quite a lot of theory and maturity to understand why RDBMS is the right paradigm.

You and the guy you are replying to are doing it again, conflating SQL and relational databases. :(

No. I'm not. I said:

1) "RDBMS is the right paradigm." That's high praise. RDBMS is exactly the Right Thing, with a trademark and all.

2) "SQL is imperfect, but it's good enough to not be worth replacing." That's definitely not high praise. It means that this implementation has all sorts of warts and annoyance. However, that doesn't rise over the bar of replacing.

As a teenager, I definitely did conflate the two. Perhaps that's where your misread came from?

It probably comes from 90% of discussion on SQL revolves around de-conflating the two. It’s pretty much impossible to complain about SQL without clarifying the difference, and even then. Really I think we should just permanently assume the other party has never conceived of such a difference, no matter how much they insist otherwise.

SQL is here to stay, if only because few can be convinced that another language could query the RDBMS directly (without first compiling down to SQL). Peolle also keep persisting in this belief it’s standardized, for god knows what reason

In abstract, I don't see a lot of downside to compiling down to SQL as an intermediate format. Or vice-versa, for that matter.

In practice:

1) Most attempts to do this historically have missed the point of an RDBMS (e.g. most ORMs).

2) SQL is cumbersome, but it's not quite cumbersome enough I'd want to bother with something else.

As a footnote, I find SQL-grade standards to be incredibly helpful. In 2022, I wish there were a strict standard, but in new domains, standards like this mean:

1) I can read code for virtually any database, with maybe gentle use of search engines, unless I get into really hairy corners. Ultimately, those, I can look up too.

2) If I write code for a new database, I don't need to learn anything; I can understand syntax with a few web searches.

3) For 90+% of SQL, I can write automatic scripts quickly and easily to translate between databases (when migrating, the remaining few percent, someone can do by hand, and it's a very tractable chore).

That's not the case jumping into a graph database or other stuff.

At the same time, they don't over-constrain things. If you want your database to have a BCD type, JavaScript stored routines, or some wonky form of virtual tables, you can.

Now, in 2022, we know enough to standardize all of this stuff, but I'm not sure we did when these tools were coming out.

As a footnote: I'm working in a domain with no standards, and if everyone could just pick JSON (or XML, or just about any one thing), we'd already have 50% of the benefit of a full standard. If people standardized a few nouns and verbs (e.g. 'user' versus 'user_id' versus 'actor' versus 'agent' versus ...), we'd be another 50% of the way there. Systems do different things, and I don't think going 100% of the way makes sense until we understand the domain better, but partial standardization is a huge win. I've been pushing hard for having partial standards, and hard for not having full standards yet.

> SQL is imperfect, but it's good enough to not be worth replacing.

Great way to put it.

> There's literally no reason to going with a lot of NoSQL databases over using a SQL database as a key-value store.

To be devil's advocate, the arguments I hear range more on ease of ops/scalability for noSQL, apparently some do it better than SQL implementations, and some drivers can make trivial operations easier as well. That said, I'd pick PostgreSQL for pretty much any new project I get to work on.

It turns out that most SQL databases, when used as a key-value store, shard better than most NoSQL databasing.

You run into issues with things like JOINs across shards, but there's literally no upside even there to NoSQL versus using a SQL databased in a disciplined way. I've designed systems with the same type of infinitely horizontally scalable KVS-type storage for all the changing state in SQL.

SQL also means you can do things like:

- Do local joins (if e.g. all the data for a user is in the same shard)

- Keeping a small set of relational data (not the stuff which needs to scale) and have one technology

- Use read replicas to scale some of the stuff which doesn't fit in the "infinitely horizontally scalable KVS" model

.... and so on. You can't not think about it, but if you do think about it, it's all upside and no downside.

NoSQL is like simple assignment to storage and one layer of obvious indexing. Its lower level, and therefore much faster and predictable because there is no query planner guessing how to assemble things.

Tuning SQL on the other hand is a guessing game with the query planner. You can express a lot more but 1% of those expressions could down your DB.

I like both, but if you want performance NoSQL is the winner.

> if you want performance NoSQL is the winner.

Performance for which use cases?

It matters to clarify this because if you get rid of every use case SQL+RDBMS optimize for, NoSQL is obviously going to be the winner. A car with no safety measures and without most consumer-oriented features is probably going to be "faster" than one that has them.

Just to be pedantic you can use SQL to query NoSQL databases, just look at the PartiQL ExecuteStatement API for DynamoDB. I assume your question is about why anyone would use a NoSQL databases instead of a relational database. The short answer is scale. If you have well define access patterns that rely only on a primary index, you can effectively scale to billions of QPS. If you want to make relational queries, and/or can’t know your access patterns in advance, use a relational database.

Sometimes you just want to store data and worry about validation later. I'm in that situation now, with different tasks requiring different kinds of validation for the same data. Sometimes I want all the postal codes that users have entered, sometimes I want only the valid postal codes.

> GraphQL honestly gives me the same vibes.

Not that this is particularly relevant to the discussion, but GraphQL is actually much closer to SQL than most NoSQL solutions because it's statically typed and the schema is statically defined. The GraphQL schema acts as the contract of what the API can serve to the client, much like a schema in a relational database.

I have plenty of beef with SQL, but your insert versus update example doesn't make sense to me. That's a perfectly reasonable difference; insert is always a dense operation while update is usually a sparse operation.

It's also the simplest use case of INSERT imaginable and fails to be applicable with a slightly more complex one:

  INSERT INTO table1 (col_1, col_2, col3)
  SELECT COUNT(col_6), col_4, col_5 FROM table2 INNER JOIN...

I think in principle we could allow

    INSERT INTO table1
        col_1 = COUNT(table2.col_6),
        col_2 = table2.col_4,
        col3 = table2.col_5
    FROM table2 INNER JOIN ...

You can always do

    update t set (x, y) = (n, m);

At least you can in more recent versions of Postgres, and I think it’s standard SQL.

Hey man if SQL is so great why are people always trying to kill it??!?!?

Reply on news.ycombinator.com