Against SQL • Hacker News Board

Re: Against SQL

#271

Post by batty_alex » Sat, Jul 10, 2021, 4:13 PM UTC

bob1029 wrote: post #270

I feel like most frustrations with SQL boil down to fighting against a shitty schema. When you are sitting in a properly normalized database, it is a lot easier to write joins and views such that you can compose higher order queries on top. If you are doing any sort of self-joins or other recursive/case madness, the SQL itself is typically not the problem. Whoever sat down with the business experts on day 1 in that c…

Yeah, this has been my experience, too. Sql and RDBs are a lot less frustrating when someone takes the time to actually do some design and planning.

Personal experience incoming: At startups, it's usually a mess because hiring someone who knows databases seems to always come so late in the game. At bigger corporations, well, hopefully the developers and database people get along and talk - otherwise, one of those teams is going to be a bottleneck.

> Model it correctly, then consider an optimization pass if performance cannot be reconciled with basic indexing or application-level batching/caching

So true. This also extends into general purpose languages, everything is so much easier when you take the time to model things correctly.

Re: Against SQL

#272

Post by mcv » Sat, Jul 10, 2021, 4:14 PM UTC

Having worked a lot with neo4j, a graph DB, over the past two years, I must say I'm surprised how rigid and inexpressive SQL is by comparison. We started our project with a SQL database, but some queries would be 10 or more lines with multiple joins. Very hard to read. Once we switched to neo4j, the same query was a single, easily readable line.

SQL is very well-established, but it's also old, and it shows its age. It's kinda weird how easily we jump from one programming language to another, and yet we can't seem to move on from our main relational query language.

Re: Against SQL

#273

Post by balfirevic » Sat, Jul 10, 2021, 4:16 PM UTC

bob1029 wrote: post #270

I feel like most frustrations with SQL boil down to fighting against a shitty schema. When you are sitting in a properly normalized database, it is a lot easier to write joins and views such that you can compose higher order queries on top. If you are doing any sort of self-joins or other recursive/case madness, the SQL itself is typically not the problem. Whoever sat down with the business experts on day 1 in that c…

> 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?

Re: Against SQL

#274

Post by taffer » Sat, Jul 10, 2021, 4:19 PM UTC

masklinn wrote: post #171

danbruc wrote: post #75

I wonder how much of the limitation are necessary in order for the query optimizer to have any chance at finding a good execution plan. As you add more and more abstractions and more and more general computations in the middle of your queries, it will probably become harder and harder for the query optimizer to understand what you are actually trying to do and figure out how to do it efficiently. Are you not running…

> I wonder how much of the limitation are necessary in order for the query optimizer to have any chance at finding a good execution plan. Probably in exactly the opposite way: the limitations of SQL put a lot of work on the back of the query optimiser without allowing for said optimiser to easily reason about the queries, or for the writer to easily feed the optimiser (short of dedicated extensions e.g. Oracle's opti…

Optimizability is one of the core ideas of the relational model. The others being data independence and a declarative query language. SQL is based on relational calculus and relational algebra, which in turn are based on first-order logic. The reason that every attribute in the relational model must be atomic is that first-order logic cannot be applied to nested sets. However, first-order logic is what makes query optimization possible in the first place. Thus, if one were to develop a query language that did not have these constraints, then one would lose optimizability.

Re: Against SQL

#275

Post by DangitBobby » Sat, Jul 10, 2021, 4:20 PM UTC

initplus wrote: post #98

kinjba11 wrote: post #28

Earlier quoted context omitted.

> maybe they are asking a bit much from SQL But this article is thought provoking to say the least. It follows the courtroom logic of holding the defendant SQL on trial for as much as possible. And SQL is guilty of a lot of crimes. I do hope GraphQL and similar query languages become more prevalent and standardized, as it seems SQL could really use some stiffer competition.

GraphQL is stiff competition, in the sense of "stiff as a corpse". Yes, it has the words "query language" in the name, but that's where the similarities end. In GraphQL, you can select fields of JSON objects, optionally a field may have parameters which affect the values it returns in unspecified ways. That's it. Because of this design, unlike in SQL where you are concerned with modeling the structure of your data, G…

You are focusing on the wrong aspects of the comparison here. The point is not that GraphQL is dreamy from a developer perspective (as opposed to the api end-user perspective). The popularity just demonstrates that there is a real end-user need for the query to be correctly structured out of the gate instead of being re-combined/nested/etc after it's left the database.

> The original idea of relational databases was that they would be queried directly from the client. With the rise of the web this idea died - SQL is too complex to be easily secured against adversarial input, cache invalidation for SQL queries is too hard, and there is no way to easily spawn background tasks (eg resizing images) or to communicate with the rest of the world (eg sending email). And the SQL language itself was not an appealing target for adding these capabilities.

> So instead we added the 'application layer' - a process written in a reasonable programming language that would live between the database and the client and manage their communication. And we invented ORM to patch over the weaknesses of SQL, especially the lack of compressibility.

> This move was necessary, but costly.

Your complaints are about GraphQL at the application layer, ie, the layer of concern that exists because SQL is inadequate for the burden it's been made to bear.

Imagine if GraphQL added aggregates and support for types besides what JSON limits you to, and it then just sat atop an RDMS like Postgres so that you no longer have to fuck around with it at the application layer to get it going. That would clearly be an improvement, right? Or really, just the ability to implement a language like GraphQL atop the RDMS so you can design your own QL that you don't hate.

Re: Against SQL

#276

Post by tonymet » Sat, Jul 10, 2021, 4:22 PM UTC

Relational Tables & SQL should be just one storage mechanism for your app.

What if someone told you: build an app, but only use b-trees? Then you start complaining about all the shortcomings of b-trees.

The point is that you have relational tables / SQL, along with many other persistence , storage & indexing mechanisms: distributed hashtables, queues, lists, etc.

All the apps I've worked on have mixed SQL with all of the other data structures with consistent or inconsistent replication among them depending on the use-case.

One way to manage this is a key-value online tier and a relational offline tier, with inconsistent replication online to offline.

SQL & RDMBS are very powerful, but like any tool, limited to the designated use case. Stop trying to make it do everything.

Re: Against SQL

#277

Post by mcv » Sat, Jul 10, 2021, 4:22 PM UTC

goto11 wrote: post #111

kinjba11 wrote: post #28

Earlier quoted context omitted.

> maybe they are asking a bit much from SQL But this article is thought provoking to say the least. It follows the courtroom logic of holding the defendant SQL on trial for as much as possible. And SQL is guilty of a lot of crimes. I do hope GraphQL and similar query languages become more prevalent and standardized, as it seems SQL could really use some stiffer competition.

SQL is often conflated with the relational model (hence the term NoSQL for non-relational databases), but the article is careful to explain that the relational model is great, but SQL is a clunky syntax/standard. Going to graph databases is certainly throwing the baby out with the bathwater. The relational model was invented to address shortcomings in the hierarchical and graph database models.

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 basically an expansion of simpler table-based databases that can also handle relations between different entries, if awkwardly. Graph databases are a much better fit and smoother to work with.

Re: Against SQL

#278

Post by dnautics » Sat, Jul 10, 2021, 4:25 PM UTC

trollied wrote: post #161

ako wrote: post #131

Earlier quoted context omitted.

There's a lot of talk about what is wrong with SQL, but i haven't seen something yet that is actually better than SQL for most use cases. Stop fighting SQL so much, and just focus on bringing a better solution. If potential users see it has significant benefits they'll start using it.

I agree. I’ve seen people bashing SQL ever since I started using it 25 years ago, but I’ve yet to see an attempt to replace or supersede it succeed. Many of these blog articles seem to be written by devs that have only really experienced the user-facing application side of things, and really don’t realise the sheer number of financial, analytic & business systems, that keep the world spinning round, that are all happ…

> A client should use appropriate joins to get the data from the database & then do the transformation

Please don't set up "object joins" against an object/key/value table. It's better to flatten the highly used bits of the json out into the table that hosts the "source json" as a blob in one column. There is a place in our codebase where someone did these "object joins", and it's a nightmare to work with. After about two months, there is a comment in one of the tests I found, "I really regret this pattern now". Now we have enough customer data in that object "kv table" that we can't migrate back to json + select flattened fields without extreme pain.

Re: Against SQL

#279

Post by BadInformatics » Sat, Jul 10, 2021, 4:31 PM UTC

taffer wrote: post #274

masklinn wrote: post #171

Earlier quoted context omitted.

> I wonder how much of the limitation are necessary in order for the query optimizer to have any chance at finding a good execution plan. Probably in exactly the opposite way: the limitations of SQL put a lot of work on the back of the query optimiser without allowing for said optimiser to easily reason about the queries, or for the writer to easily feed the optimiser (short of dedicated extensions e.g. Oracle's opti…

Optimizability is one of the core ideas of the relational model. The others being data independence and a declarative query language. SQL is based on relational calculus and relational algebra, which in turn are based on first-order logic. The reason that every attribute in the relational model must be atomic is that first-order logic cannot be applied to nested sets. However, first-order logic is what makes query op…

As has been mentioned in this article and many previous takedowns of SQL the language, it's a pretty poor approximation of both relational calculus and relational algebra. So in that sense, SQL barely has a leg to stand on. If anything, SQL not adhering more closely to those techniques is why query planning is so fraught in modern relational DBs. A language like the one described in TFA would be both closer to the relational model as theorized and probably easier/more consistent to optimize.

Re: Against SQL

#280

Post by DangitBobby » Sat, Jul 10, 2021, 4:32 PM UTC

pjmlp wrote: post #31

progre wrote: post #14

I do love SQL and at least where I live (MS SQL Server) it can be made to run amazingly fast if you take some care with your queries and indexes. It's not portable though: as far as I know not a single one of the big sql vendors follows the standards 100% and more importantly, spending some time with one vendor will give you some habits that are sure to not work as well with another (cursor constructs are generally a…

Yeah, however it is kind of strange to single out SQL, when we have examples like a very famous kernel that can only make use of specific compiler, or Web APIs that are only implemented by a specific browser.

I don't see it as strange. They probably spend lots of time mucking around in SQL trying to force it to things it's not good at, so now they have strong opinions on SQL. They specifically state that it's too late to turn back the clock on SQL, but it's not too late to expose RDMS access with a better language, or with a sufficiently flexible API to allow arbitrary QL implementations. They aren't shitting on anyone here either. They say that the assumptions made when SQL were designed ultimately didn't pan out:

> The original idea of relational databases was that they would be queried directly from the client. With the rise of the web this idea died - SQL is too complex to be easily secured against adversarial input, cache invalidation for SQL queries is too hard, and there is no way to easily spawn background tasks (eg resizing images) or to communicate with the rest of the world (eg sending email). And the SQL language itself was not an appealing target for adding these capabilities.

> So instead we added the 'application layer' - a process written in a reasonable programming language that would live between the database and the client and manage their communication. And we invented ORM to patch over the weaknesses of SQL, especially the lack of compressibility.