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

181–190 of 304 posts

Re: I don't need your query language

#181

Post by bob1029 »

SQL is only ever as good as the schema relative to the business or problem domain. The focus on the syntax of the language was always a mystery to me. It's a domain-specific language. It's up to you to make it not suck.

If you are forced to work with a schema that is poorly-aligned with the logical reality it intends to represent, you would definitely walk away with a bad taste in your mouth. Hacking around bad normalization is 99% of what makes SQL suck for me.

If you ever get a chance to design the whole thing yourself from zero, you should almost always insist on one big database/schema and routinely review the table structure with the business owners before you actually go to prod.

The moment you start doing things like putting data for service A into database A and service B into database B, you lose a lot of power. Sometimes this is required, but most of the time it's an org-chart alignment meme. There are ways to join these separate databases, but it starts to fall down pretty quickly. The true magic of SQL is having all of those dimensions in one place at one moment in time so you can put a pin in anything without complex distributed transactions.

Re: I don't need your query language

#182

Post by dbanon9 »

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…

1. Read the source. It's very efficient: https://github.com/postgres/postgres/blob/a14e75eb0b6a73821e...

2. You don't have to do deserialization in the application layer. If all you're using JSON for is to convert to OOP objects, just deserialize in the db -- which again, trivial.

3a. This is wrong on many counts. If you want efficient passing of JSON, use JSONB which is the binary encode of the JSON, as a tree structure. It will not include the structural characters.

3b. Bandwidth is also cheap.

4. Don't optimize without profiling. A few extra CPU cycles is not going to make-or-break your scaling journey, you'll most likely run into larger problems before that happens.

5. You can get "non-uniform" tuples by using UNIONs and a smart flagging system that points to tuple schemas -- rather than using JSON; the difference is entirely ergonomic.

6. If you're in a low-latency environment and the CPU cycles are absolutely critical, write your own extensions to handle what you're trying to do, instead of twisting Postgres into doing your bidding.

Re: I don't need your query language

#183

Post by fbdab103 »

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

I would love if base SQL were improved. On the other hand, we are just slapping lipstick on a pig. It is already a gargantuan mess which needs a path to replacement.

Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.

Re: I don't need your query language

#184

Post by jmartrican »

tester756 wrote: post #9
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.

C#'s LINQ (query syntax, not methods) got it right var result = from s in stringList where s.Contains("Tutorials") select s;

I'm so jealous that Java does not have its own LINQ.

Re: I don't need your query language

#185

Post by masklinn »

marcosdumay wrote: post #173
masklinn wrote: post #69

Earlier quoted context omitted.

That only fixes trivial selects. But you still have issues with e.g. GROUP BY, especially since the dependency is circular: - barring extensions you can only select grouping expressions or aggregates - but instead of repeating grouping expressions you can refer to a select expression (by index, some databases also allow the alias) The "spec" order of evaluation for queries is WITH, FROM, WHERE, GROUP BY, HAVING, SELE…

You can easily change it to make `group by` behave much better, and `having` redundant with `where`, like it should always have been if you just evaluate from start to end, without any hidden reordering.

> You can easily change it to make `group by` behave much better

Change what? Make "group by" behave better how?

> `having` redundant with `where`

They filter different things, how do you make `where` perform both jobs?

> like it should always have been if you just evaluate from start to end, without any hidden reordering.

The only "hidden reordering" is an optimisation.

Re: I don't need your query language

#187

Post by tomnipotent »

littlestymaar wrote: post #180
tomnipotent wrote: post #153

Earlier quoted context omitted.

The added cost of JSON serialization is easily offset by reducing the total number of overall queries and implicit (or explicit) transactions. The additional parallelization the RDBMS can achieve is generally greater than the added JSON serialization and extra network bandwidth.

Maybe, but this isn't the same as saying CPU cost doesn't matter because it's cheap.

> same as saying CPU cost doesn't matter because it's cheap

A straw man argument you've pulled out of thin air.

Re: I don't need your query language

#188

Post by Jenk »

bob1029 wrote: post #181

SQL is only ever as good as the schema relative to the business or problem domain. The focus on the syntax of the language was always a mystery to me. It's a domain- specific language. It's up to you to make it not suck. If you are forced to work with a schema that is poorly-aligned with the logical reality it intends to represent, you would definitely walk away with a bad taste in your mouth. Hacking around bad norm…

The "domain" in the DSL of SQL is "relational data" not "your business domain"

Re: I don't need your query language

#189

Post by yencabulator »

nicoburns wrote: post #43
DaiPlusPlus wrote: post #18

SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results…

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)

The crap mini-language of Postgres functions to manipulate JSON makes otherwise reasonable queries unreadable, though.

Re: I don't need your query language

#190

Post by yencabulator »

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.

Since you express that opinion, I trust you're already aware of this, but just in case: https://prql-lang.org/
Post reply on HN