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

It's not you, it's SQL

stack.convex.dev

Post reply on HN
Print view View original

71–80 of 84 posts

Re: It's not you, it's SQL

#71

Post by 015a »

brightball wrote: post #50
015a wrote: post #30

I mean... yeah MongoDB got a lot of hate, but I think the broader point is that it was one of the first technologies to popularize the domain of NoSQL. No one knew how to use it properly; so we adapted SQL-like schema design, and when it became obvious that didn't work well the hate started spilling over to the first technology to arrive at the party. The elephant in the room is, I suppose, that the modern internet l…

> 2. Making a document store operate more like SQL, for the things SQL is good at (joins, validation, etc) is a lot easier than making a SQL database operate like a document store for the things document stores are good at (Planetscale? there's a couple players in this game). For a lot of NoSQL document stores, the simple acting of having multiple indexes was a bridge too far. Couchbase comes to mind here. I can't th…

But you just asserted the same point I was: No one knows it anymore! That makes the skill valuable on the short-term, but on the long-term you've got the next generation of programmers entering jobs, being given the opportunity to build and change systems, and what are they going to pick? Maybe not SQL.

Its the same argument, just at a grander scale, for the generation before us and the COBOL mainframe programmers of old; database technologies just tend to move slower because data is very sticky.

You might not see the use-cases; but Apple does. Their usage of Cassandra sustains in the millions of QPS across over 300,000 instances. That's a scale SQL can't even begin to dream of. Discord does; they moved to Scylla [2]. Amazon does [3]. Blackrock Aladdin depends on Cassandra, and manages over $21T in assets [4].

Which kind of circles around to the fact that, while you say "a lack of SQL knowledge is what causes unwarranted griping about SQL", sure I can accept that, but I'd conversely argue that a lack of Solving Real Problems is what causes unwarranted griping about NoSQL. Schematization isn't a real problem; we have a dozen ways of solving that at different layers. Imperfect ACID compliance isn't a real problem; its a pseudo-academic expression of the guarantees that SQL offers, which then gets unfairly transplanted onto NoSQL databases as if those issues can't or wouldn't be addressed elsewhere. Even original article statements like data being inherently relational; data isn't anything, its just data, it just appears relational because its how we've been trained to think about data after 60 years of SQL-monoculture. If you start thinking about data end-to-end, how its displayed to users, on pages, components, screens, feeds, it really doesn't look relational at all; life isn't a graph, the graph is an expression of human pattern-matching on things that could reasonably be related, but in reality aren't; life is a series of snapshots. This moment, this page, this feed, this component, in this moment, for this request, for this session; that is the end-to-end view of data; documents, not a web.

But SQL is fine at that! In fact, its a best option at medium scales; the middle of the bell curve where 80% of people live. It may not always be, but its there today. Its that bottom 10% and top 10% where the solutions that work for most don't work as well; and I think NoSQL gets a lot of Majority Hate despite being an extremely strong solution at those scales. Know your tools, and be willing to replace them when the circumstances change.

[1] https://twitter.com/erickramirezau/status/157806381149547724...

[2] https://www.scylladb.com/press-release/discord-chooses-scyll...

[3] https://aws.amazon.com/blogs/aws/amazon-prime-day-2022-aws-f...

[4] https://www.youtube.com/watch?v=322GytEo_fE

Re: It's not you, it's SQL

#72

Post by wvenable »

jamwt wrote: post #18
wvenable wrote: post #17

Earlier quoted context omitted.

I'm forgiving on the authors point here. If you have JOINs and subqueries, you're just doing a SELECT to get data that can only be UPDATEd/INSERTed on a single table. You can't do an INSERT across 5 tables in one statement.

Article author here, yep, that was the intended point. Agree the wording was unclear, and there's a footnote now clarifying that. Thanks!

One could argue that one statement vs. many statements isn't particularly important and as long as you have transactions.

Re: It's not you, it's SQL

#73

Post by WkndTriathlete »

Tell us you don't understand the theory or use cases for SQL databases without telling us you don't understand the theory or use cases for SQL databases.

There are indeed times when a simple key-value store is appropriate, but the encoding of hierarchy and the relational calculus are useful for so many more use cases in business applications. SQL as a language is certainly flawed - it would be nice to be able to algebraically/programmatically join select clauses and conditions, for instance, or to perform multi-inserts - but the semantics of the storage and engine are based on some pretty mathematically sound foundations.

You may find the seminal paper by Codd as illuminating as I did if you read it.

Re: It's not you, it's SQL

#74

Post by brightball »

015a wrote: post #71
brightball wrote: post #50

Earlier quoted context omitted.

> 2. Making a document store operate more like SQL, for the things SQL is good at (joins, validation, etc) is a lot easier than making a SQL database operate like a document store for the things document stores are good at (Planetscale? there's a couple players in this game). For a lot of NoSQL document stores, the simple acting of having multiple indexes was a bridge too far. Couchbase comes to mind here. I can't th…

But you just asserted the same point I was: No one knows it anymore! That makes the skill valuable on the short-term, but on the long-term you've got the next generation of programmers entering jobs, being given the opportunity to build and change systems, and what are they going to pick? Maybe not SQL. Its the same argument, just at a grander scale, for the generation before us and the COBOL mainframe programmers of…

Developers not knowing something they need to know isn't a case for getting rid of the thing, it's a case for insisting they learn it. That it's been abstracted away by ORMs is a testament to those ORM layers and the consistency of the standard...not an indictment of SQL itself.

Let's talk real use cases for a second.

All of the performance benefits of NoSQL come from key-value retrieval and the ability to shard data due to built in lack of joins.

There's nothing stopping anyone from doing that in a relational database. It's a common pattern. CitusDB even made a PostgreSQL extension that makes this model even easier to use with joins and all the other relational goodies. If you want to setup PostgreSQL with an id field and a JSONB field, you've got the entire NoSQL scaling use case covered without all of the limitations of using a NoSQL only database.

The schema at different layers thing doesn't _really_ work either. If you put the schema in your application code, then you mandate an API layer in addition to your database layer. With schema enforced in the database, multiple code bases can connect to the same database. With the schema in the application layer, now everything has to connect to that application. So not only are you working with the NoSQL database AND defining the schema in the application but now you also have to define an API if you have a reason to split out some portion of application logic to a more specialized languages.

The layers of problems introduced because NoSQL is used on a project are excessive. There's no question that storing document data makes a lot of sense in some circumstances, but you're always better off just storing the portion you need in an appropriate column type (JSONB) rather than forcing your entire system into the issues that come with NoSQL only.

Given the JSON capabilities of modern SQL databases there's almost no reason to start any project with a NoSQL only option.

In the prime day article for example, they're singing the praises of their own SQL offerings too.

> Amazon Aurora – On Prime Day, 5,326 database instances running the PostgreSQL-compatible and MySQL-compatible editions of Amazon Aurora processed 288 billion transactions, stored 1,849 terabytes of data, and transferred 749 terabytes of data.

> Amazon DynamoDB – DynamoDB powers multiple high-traffic Amazon properties and systems including Alexa, the Amazon.com sites, and all Amazon fulfillment centers. Over the course of Prime Day, these sources made trillions of calls to the DynamoDB API. DynamoDB maintained high availability while delivering single-digit millisecond responses and peaking at 105.2 million requests per second.

They don't given an apples to apples numbers comparison here. We don't have a cost comparison of infrastructure for DynamoDB vs Aurora. We don't get to see if "288 billion transactions" are database writes while we only see requests here. We don't get to see whether there have to be so many more requests to Dynamo because you can't get the data as easily in a single query. We don't get to see if it's being used as a cache layer or for session management, which are both great use cases for it.

I mean, Instagram scaled just fine with only PostgreSQL. SQL scales just fine. Some of the bevy of options and tools that you get to work with the data don't scale as well as others, but that's not a case for getting rid of those tools...it's just a case for caching when needed.

Re: It's not you, it's SQL

#75

Post by Justsignedup »

dragonwriter wrote: post #63
sgk284 wrote: post #46

Earlier quoted context omitted.

SQL already covers this use case with the `using` keyword. But you need to specify the shared column name. If you didn't need to specify the column then adding a new foreign key between the tables would make existing queries ambiguous and break backwards compatibility. See: https://www.postgresql.org/docs/current/queries-table-expres... .

> SQL already covers this use case with the `using` keyword. No, “using” doesn’t automatically join by foreign key, it joins by explicitly-provided column name which must be identical between the two tables. > If you didn’t need to specify the column then adding a new foreign key between the tables would make existing queries ambiguous and break backwards compatibility. Not if you specified with constraint name rathe…

wow i absolutely did not know this. amazing.

Re: It's not you, it's SQL

#76

Post by Justsignedup »

dragonwriter wrote: post #69
Justsignedup wrote: post #64

Earlier quoted context omitted.

This is an accurate description of how I think of this problem. And it would be tough to describe all this: - table a joins b yay a view - table b joins c yay a view - table c join d yay a view - table a joins to d. well technically it can, but are you really gonna write all the permutations of views for every possible join? ORMs encode that nicely so I can easily walk the relationships and get to the query I need.

Essentially all of the actual information is encoded in the foreign key constraints (in combination with uniqueness constraints.) What ORMs provide that SQL doesn’t isn’t much encoding of information (they usually have facilities to distinguish data tables from pure join tables, and to distinguish 1:1 and 1:M, (M>0) relations from 1:(0-1) and 1:M, (M>=0) ones, so they do encode some additional information), but provi…

To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc.

Just a new language to replace sql, that is still sql but just better designed with the benefit of 30+ years of language design improvements.

Re: It's not you, it's SQL

#77

Post by ntonozzi »

Justsignedup wrote: post #76
dragonwriter wrote: post #69

Earlier quoted context omitted.

Essentially all of the actual information is encoded in the foreign key constraints (in combination with uniqueness constraints.) What ORMs provide that SQL doesn’t isn’t much encoding of information (they usually have facilities to distinguish data tables from pure join tables, and to distinguish 1:1 and 1:M, (M>0) relations from 1:(0-1) and 1:M, (M>=0) ones, so they do encode some additional information), but provi…

To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc. Just a new language to replace sql, that is st…

Was it PRQL? That’s what all of these comments remind me of.

https://prql-lang.org/

Re: It's not you, it's SQL

#78

Post by zX41ZdbW »

Justsignedup wrote: post #76
dragonwriter wrote: post #69

Earlier quoted context omitted.

Essentially all of the actual information is encoded in the foreign key constraints (in combination with uniqueness constraints.) What ORMs provide that SQL doesn’t isn’t much encoding of information (they usually have facilities to distinguish data tables from pure join tables, and to distinguish 1:1 and 1:M, (M>0) relations from 1:(0-1) and 1:M, (M>=0) ones, so they do encode some additional information), but provi…

To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc. Just a new language to replace sql, that is st…

There were countless attempts to extend or replace SQL:

OQL: https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-langua...

More modern:

PRQL: https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 (it is so obscure that Google replaces it to "Malay language")

Another example: ClickHouse supports standard SQL with many features such as window functions, SQL/JSON, and extends it to make it convenient for data analysts by adding: - higher-order functions; nested data structures, arrays, tuples, and maps; aggregate function states as first-class citizens, unrestricted usage of aliases in any place of expression, etc.

https://github.com/ClickHouse/ClickHouse/

I'm an everyday user of ClickHouse, and I'm finding its SQL implementation the most pleasant to use! Although it's unsurprising, because I'm also one of its authors... I'm also welcoming innovation and improvement of SQL without the introduction of a completely different language.

Re: It's not you, it's SQL

#79

Post by jrumbut »

zX41ZdbW wrote: post #78
Justsignedup wrote: post #76

Earlier quoted context omitted.

To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc. Just a new language to replace sql, that is st…

There were countless attempts to extend or replace SQL: OQL: https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-langua... More modern: PRQL: https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 (it is so obscure that Google replaces it to "Malay language") Another example: ClickHouse supports standard SQL with many features such as w…

ClickHouse is one of those technologies I've had half an eye on for a while.

In your completely unbiased opinion (I kid), do you think it's a good choice for the following problem?

I have multiple sensors that read different types of data about the same subject at (annoyingly) slightly different intervals, usually a few dozen times a second. This needs to be combined with other event data that happens on the order of a few times per day.

Currently I analyze this data in Python, R, and sometimes SAS (a weird proprietary language). Some coworkers use Matlab.

Is that a ClickHouse problem? If I tried it out would the ClickHouse community be interested in hearing how it goes?

Re: It's not you, it's SQL

#80

Post by Justsignedup »

zX41ZdbW wrote: post #78
Justsignedup wrote: post #76

Earlier quoted context omitted.

To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc. Just a new language to replace sql, that is st…

There were countless attempts to extend or replace SQL: OQL: https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-langua... More modern: PRQL: https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 (it is so obscure that Google replaces it to "Malay language") Another example: ClickHouse supports standard SQL with many features such as w…

PRQL was what I was thinking of. Problem is integrating PRQL into say rails.
Post reply on HN