Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Against SQL

scattered-thoughts.net

Post reply on HN
Print view View original

311–320 of 354 posts

Re: Against SQL

#311

Post by zug_zug »

I guess I don't get it. It uses a bunch of big sounding technical terms ("inexpressive" "non-pourous") to criticize sql, but when I actually read it this seems to be mostly miniscule details that could be added trivially to an SQL engine if there was demand. For example, joining natively on foreign key seems like a trivial convenience, I'm not sure it proves any larger point to me, many people prefer code that is more verbose and clear about what it does than magical/implicit.

Another example complaint hidden behind a ominous-sounding word boils down to "Using a table expression inside a scalar expression is generally not possible, unless the table expression returns only 1 column and either a) the table expression is guaranteed to return at most 1 row or b) your usage fits into one of the hard-coded patterns such as exists."

Uh, great I've never needed to do that in my career, and so if you care so much make a PR, but suggesting that SQL itself is somehow the problem is laughable. It would be orders of magnitude more effort to try to standardize the industry on a new query language than to patch table expressions. I can scarcely imagine what a productivity loss it would be to the industry of SQL standardization were dropped, it would be much worse than python 2/3 debacle.

Also "incompressible" - Sounds like the author doesn't use views/materialized-views.

Finally the "fragile" example is just the author writing a bad query. The example here is performant and less fragile: https://stackoverflow.com/questions/612231/how-can-i-select-...

etc.

Re: Against SQL

#312

Post by glogla »

quietbritishjim wrote: post #162
ngrilly wrote: post #143

A more pragmatic view in that article: https://blog.nelhage.com/post/some-opinionated-sql-takes/

Thanks, that's a great article. It has just the right balance of some interesting things I didn't know with enough things I agree with that I believe it! I agree with the desire for a data-based language, rather than text-based one as SQL is. A classic example of this is MongoDB: you can add a new filter by just adding a new entry to a dict in Python or object in JS etc. I think 99% of the reason MongoDB was successf…

SQL is not a great API but its a great human interface. JSON based query language is much better API but terrible human interface. So depending on your objectives, it might make things better or worse.

MySQL is strange choice but think I understand why the author picked it - from his other critique he seems to look at databases as a building block of hyper-scaleable applications, not as a tool for humans to do often-ad hoc things with data.

I would never recommend MySQL for "business data" - it had and possibly still has way too many footguns with regards to number behavior, character encodings and Unicode, date and timestamp handling, and so on - hell, it doesn't have a proper MERGE and it only got CTEs in the latest version.

But if you're using it as persistence store that barely more than key-value store, why not? I have no problem believing the author that that kind of use is more common.

Re: Against SQL

#313

Post by chubot »

Amazing critique! It has a wealth of examples -- I liked the "N+1 query bugs" and "feral concurrency" links (stuff I've experienced but didn't have a name for).

----

The comparison of SQL vs. flink windowing ("kernel space" vs "user space") reminds me of the this 2013 call to change the design of browsers feaetures:

https://extensiblewebmanifesto.org/

Basically there's a lot of stuff implemented stuff in the C++ layer of the browser that's impossible to emulate in JavaScript, and that's a bad design.

It is indeed alarming how much syntax SQL has. It reminds me of shell, where every string manipulation function like stripping a prefix has custom syntax like ${x//pat/replace} or ${x%%prefix}. Oil (https://www.oilshell.org/) will simply have functions for this, like x.sub('pat', 'replace').

----

I also wonder if the author has worked with dplyr and the tidyverse at all? He mentions Pandas, but IMO it's a clunkier imitation of those ideas (and I'm saying that as a Python programmer).

Tidy data was my intro to the design of dplyr: http://vita.had.co.nz/papers/tidy-data.html

It's very inspired by the relational model, but it has a few more operations like "gather" and "spread" which turn "long" format into "wide" format and vice versa.

It has a clean and expressive API: https://www.rstudio.com/wp-content/uploads/2015/02/data-wran...

It composes like regular code, so you can write stuff like:

    bin_sizes %>%
      select(c(host_label, path, num_bytes)) %>%
      left_join(bytecode_size, by = c('host_label')) %>%
      mutate(native_code_size = num_bytes - bytecode_size) ->
      sizes
Good comparison of the relational model and data frames: Is a Dataframe Just a Table? https://plateau-workshop.org/assets/papers-2019/10.pdf

I link all of these in What is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html

Re: Against SQL

#314

Post by kinjba11 »

MetaWhirledPeas wrote: post #246
thrusong wrote: post #240

Earlier quoted context omitted.

I completely agree with you. Is SQL perfect? No. Have I accepted and embraced it? Yes, because it'll get the job done. I also happen to really dislike how the author hasn't capitalized the syntax like SELECT FROM WHERE or CREATE TABLE which, to me, poorly affects the legibility and therefore makes me less interested in reading the argument overall.

Among the various languages I use, why is SQL the only one that favors ALL CAPS EVERYTHING? When writing ad hoc queries I ignore that convention just to be ornery.

You're not alone. Myself and many coworkers don't feel the need to capitalize everything, preferring `select * from Table` to the shouting version `SELECT * FROM TABLE`. It ain't the 80s anymore, we can use lowercase.

Re: Against SQL

#315

Post by yen223 »

kinjba11 wrote: post #314
MetaWhirledPeas wrote: post #246

Earlier quoted context omitted.

Among the various languages I use, why is SQL the only one that favors ALL CAPS EVERYTHING? When writing ad hoc queries I ignore that convention just to be ornery.

You're not alone. Myself and many coworkers don't feel the need to capitalize everything, preferring `select * from Table` to the shouting version `SELECT * FROM TABLE`. It ain't the 80s anymore, we can use lowercase.

This reminds me, case sensitivity of identifiers (table names, column names etc) in SQL is a whole mess that can fill another blog post.

Re: Against SQL

#316

Post by [deleted] »

pc86 wrote: post #295
balfirevic wrote: post #273

Earlier quoted context omitted.

> I feel like most frustrations with SQL boil down to fighting against a shitty schema. Which one of the frustrations from the article boils down to fighting against shitty schema?

The vast majority of recursion in SQL is probably the result of a bad schema.

[deleted]

Re: Against SQL

#317

Post by AllegedAlec »

tremon wrote: post #199
spicybright wrote: post #182

Earlier quoted context omitted.

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

.Net's LINQ comes close, at least for querying. Update statements using LINQ usually translate to a select query followed by an in-language loop, but that's probably more an Entity Framework limitation than of LINQ itself. The problem with most abstractions on top of SQL is that the first thing they abstract away is the relational model; you end up with an opaque result set, a mapped object, or an untyped collection.…

> .Net's LINQ comes close, at least for querying

It breaks down in really stupid and unintuitive ways. Now you're not just learning LINQ, but also the ideosyncracies of how LINQ translates queries to SQL and why sometimes your performance plummets even though you expressed yourself in the same way, but if you put some query in a variable first and then continue grouping it does a query that takes milliseconds. It's the best example of a leaky abstraction I've ever come across.

Re: Against SQL

#318

Post by jiggawatts »

barrkel wrote: post #243
jiggawatts wrote: post #220

Earlier quoted context omitted.

> On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use. That's precisely because of some the flaws of SQL outlined in the article. Generating SQL is complex . Generating portable SQL is impossible. Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene…

There's a lot more wrong with DDL than the inability of expressing it as DML. Some SQL doesn't scale well, but DDL fundamentally scales really badly. It needs either an async or resumable execution model, for one thing, which doesn't map well to connection-oriented transactions. Big migrations take days to complete. That's too long to be very reliable in a distributed system when operating on a synchronous basis.

Big migrations take days to complete because -- like the author alluded to -- SQL merges very low level data layout concerns with very high level abstract data representations. So to make a change to one you have to make a change to the other.

Decoupling the materialisation of a schema from its theoretical representation would make all migrations "instant". Many column-oriented databases can do this, or nearly so, but most row-oriented databases can't or won't.

I.e.: ideally database engines should be able to keep track of multiple "versioned" physical schemas and transparently map in-flight queries to them. Big data migrations often implement this kind of thing manually with "writeable views" or similar techniques. This shouldn't be manual, the database engine should be able to do it behind the scenes with minimal human involvement.

Re: Against SQL

#319

Post by hobs »

bob1029 wrote: post #300
pc86 wrote: post #295

Earlier quoted context omitted.

The vast majority of recursion in SQL is probably the result of a bad schema.

I agree with this. If you are doing recursion in SQL, its probably because you have a bad schema. If the schema is clean, then you should probably be doing the recursion in your application logic. There are still cases where it makes sense but its super rare in my experience.

Storing trees for instance, but you can always rewrite your recursive CTE to use a while loop and a stack.

Re: Against SQL

#320

Post by mcv »

jandrewrogers wrote: post #297
mcv wrote: post #277

Earlier quoted context omitted.

Do graph databases predate relational databases? Do you have a source for that? I thought graph databases were a fairly new thing. I only recently started working with a graph database (a bit over two years now), and it struck me just how terrible relational databases are at relationships, compared to graph databases. I know nothing about the history of databases, but my impression is that relational DBs are basicall…

Graph databases have been around nearly as long as databases generally, at least since the 1970s. The sole feature that makes a database a "graph database" is support for a minimal amount of recursion in queries, which was a feature before SQL even existed. There are good technical reasons graph databases have never been commercially successful. Most databases support some form of recursion and have for decades. The…

Interesting. But are those old graph databases similar to modern ones like Neo4j? Neo4j has pretty amazing performance compared to doing the same thing in SQL. Although it's certainly possible that SQL database was poorly designed. Even so, a handful of developers new to graph DBs managed to easily beat its performance.

I've also heard that some modern graph databases are not true native graph databases below the surface, and therefore perform worse at graph-specific queries.

Edit: is it possible you're talking about Network Model DBs[0]? Wikipedia mentions them being around since the late 1960s, and that they could model graphs, but suggests it's more of a predecessor to graph DBs, which saw a lot of improvements until the arrival of modern commercial graph DBs in the 2000s[1].

Again, I'm not an expert on this at all, but it sounds like there have been significant improvements in graph databases since the 1970s. Much more so than in relational databases.

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

[1] https://en.wikipedia.org/wiki/Graph_database

Post reply on HN