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

I don't need your query language

antonz.org

Post reply on HN
Print view View original

91–100 of 304 posts

Re: I don't need your query language

#91

Post by OliverJones »

As an experienced (===old) developer, I have learned that data long outlasts the programs that access it. The lifetime of data is measured in decades, but programs last for years. Most SQL-based RDBMS teams have figured out workable version migration paths allowing old data to run on newer servers. Because this kind of migration is a very common and economically valuable operation, the vendors make sure it works correctly.

Sometimes a project, especially a greenfield project, looks like it will benefit from more recently invented data storage and query tech than your grandmother's SQL. That's always possible. And as developers we hope for, and work for, continued progress. But consider what may happen when the project succeeds.

If you're still on the project, you'll wake up one day and realize your oldest data is 20 years old. What happens if your storage and query engines are also 20 years old, because they didn't succeed to the extent needed to pay for maintenance and upgrades? You'll be in the software equivalent of the century-old subway system where you have to make all your replacement parts yourself, or get gouged by vendors that can't spread their costs among many customers.

Build for the ages, not for the moment!

Re: I don't need your query language

#93

Post by mattmanser »

tomnipotent wrote: post #75
masklinn wrote: post #59

Earlier quoted context omitted.

That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.

> further increase the complexity of the queries Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.

I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth.

1. The data has to be serialised into JSON on the DB server, which costs CPU.

2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem is that you're using javascript instead of a better, compiled language)

3. The network bandwidth is actually larger as you've got all those extra {} in your result set, compared to the raw data in column format

It might "look" bigger to a human as there's more columns, but the data is exactly the same. So by definition, youre doing extra CPU work of serialising/deserliazing JSON and adding all the object markers of extra characters like {} and "" and : means the payload is bigger too.

Re: I don't need your query language

#94

Post by jmull »

DaiPlusPlus wrote: post #83
nicoburns wrote: post #43

Earlier quoted context omitted.

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

> This is no longer true in database that have JSON support Doing that means losing foreign-key referential integrity...

The comment alludes to this, but to clarify:

You can have your data stored in tables with all the constraints you might want, but then use json in queries to return the results in whatever form you want.

Re: I don't need your query language

#95

Post by tomnipotent »

mattmanser wrote: post #93
tomnipotent wrote: post #75

Earlier quoted context omitted.

> further increase the complexity of the queries Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.

I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth. 1. The data has to be serialised into JSON on the DB server, which costs CPU. 2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem i…

> which costs CPU

Which costs very little CPU in 2023.

> deserialised on the application server

This is true regardless. The low-level libraries are still parsing the stream into meaningful in-memory structures. With JSON, the low-level library only has to parse a variable length string, then JSON decode. I'm unfamiliar with any language in 2023 that doesn't have incredibly fast and efficient JSON parsers.

> bandwidth is actually larger as you've got all those extra

This is true, but generally negligible. I run into very few scenarios where network saturation is more of a problem than CPU or memory issues. If you have network-constrained problems, obviously optimize accordingly.

Re: I don't need your query language

#96

Post by rak1507 »

iLoveOncall wrote: post #48
RyanHamilton wrote: post #29

Show me an elegant SQL version for the queries in this article: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ Particularly when you are trying to run queries where order matters, e.g. top 3 posters by topic on HN. You will find it much more annoying. Fundamentally SQL is based on the concept of tuples/sets which have no order so there's no way to avoid it being messy. What you want is a database based on the co…

It's easy to cherry pick. I guarantee you there are a lot more queries that are easier to write in SQL than in your favorite FancySQL (or even worse, NoSQL) variation.

There aren't. Feel free to try to come up with something. 'SQL' is pretty feature light (without specific extensions). Qsql is really great, it's a shame it's locked behind a proprietary language.

Re: I don't need your query language

#97

Post by n_e »

masklinn wrote: post #59
nicoburns wrote: post #43

Earlier quoted context omitted.

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.

    select array_agg(e)  from jsonb_array_elements_text('["a","b"]'::jsonb) e;

?

Re: I don't need your query language

#98

Post by zX41ZdbW »

mcdonje wrote: post #79
ayhanfuat wrote: post #41

Earlier quoted context omitted.

That's valid DuckDB syntax

DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases. I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language. https://duckdb.org/2022/05/04/friendlier-sql.html

Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.

Re: I don't need your query language

#99

Post by [deleted] »

mabbo wrote: post #74
LunicLynx wrote: post #4

The main issue with sql is, that it is the wrong way around, which eliminates all tooling support. You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that. So switching this, moving from and joins in front of select, might be everything needed to fix sql.

I agree with you and think this backwards model leads to developers having a poor mental model of what they are doing. Step 1: build the dataset you want (FROM and JOIN) with all columns. Step 2: filter out the rows you don't want (WHERE). Step 3: choose which columns/values you want (SELECT). Maybe it's just me but this model makes so much more sense to me! I'm sure not every developer has the same way of thinking,…

[deleted]
Post reply on HN