Comment by GeneralMayhem | Hacker News Reader

Comment by GeneralMayhem | Hacker News Reader

Parent Full thread

Not disagreeing with your point (if purist language nerds had their way over practicality, we'd all be writing Haskell and Prolog) but most of your nice-to-haves strike me as properties of the database engine, not SQL itself.

Predictable performance - this will always be not only implementation-dependent but data-dependent as well. In order to know whether a join will be efficient or not, you need to know things like relative sizes of the tables, which is not necessarily a language problem. I have worked with SQL implementations that had extensions to let the user annotate joins with relative sizes on each side, but I don't think that's quite what you mean.

Lock ordering - again, good databases should have defined semantics (Postgres, for instance, does take locks in order when using ORDER BY), but I'll grant that this one could be stronger. That said, I think this is pretty niche. How often are you doing large multi-row transactions where lock order is a serious problem? If I have enough volume that deadlock is likely, I probably have enough volume that I want to be breaking up the process into a sharded or two-phase commit anyway.

Ownership relations - I think this is a DDL problem rather than a SQL problem.

Low-level language - I don't think you'll get a portable low-level language here (at least not for any definition of "low level" that's much lower than the SQL AST) because, again, the basics are implementation-dependent. What kind of scan is the base atom of a query? Well, it depends - is your database distributed? sharded? row-store-based? column-store-based? I do wish more open source database drivers would let you play with the AST in memory (Postgres has ways to print it out, but I don't think there's a good API). That would tend to solve the most significant problem raised in the article (composability) - plugging together SQL clauses automatically is hard, but plugging together subtrees can be much easier.

> Predictable performance - this will always be not only implementation-dependent but data-dependent as well

This immediately jumped out at me from the parent comment. It would be entirely possible to implement a query language where you specify a plan for your query. But then you’d immediately lose the “better than SQL” competition, because your complexity and maintainability problems would skyrocket.

I’ve had to deal with this problem as an Oracle DBA, and it’s a complete nightmare. It starts with a statistics refresh ruining a couple of execution plans, so you start specifying them manually with the plan manager. Then it gets worse over time, because stats refreshes become a big risk and you don’t want to do them anymore. Eventually you get to the point where you pretty much only run verified plans. Then your verified plans slowly degrade overtime, because the underlying cardinality of every table is constantly changing. You’ve replaced the query optimiser with yourself, which is not only tedious work, but it’s simply not possible to do the job as well as any mainstream DB engine could.

I wish that I could upvote your comment more than once, because this rings so true.

There certainly are (rare) situations, where you need to provide hints in one form or another, but it's really a bloody nightmare to maintain and may completely bork, when you - say - upgrade to a new version of the database engine.

I work with relational databases since the early 90s and can give you a no-bullshit money back guarantee that you (not you personally, obviously) are not smarter than the optimizer.

Usually there are weird data patterns involved if you absolutely must provide hints. But basically:

Don't do it!

I think one of the challenges with sql is that beginner developers can create naive sql queries that "work" but are extremely complicated for the optimizer to "get right". So in some cases (talking from own experience) the developer can, with the use of hints, "be better" than the optimizer when the problem all along was the overall structure of the query.

Edit: don't do it

The relational model can _usually_ save the day here, without a huge amount of effort. SQL certainly has its share of anti-patterns and footguns. But most of the awful SQL I’ve seen over my career hasn’t come from poor mastery of SQL, it’s come from poorly normalized schemas. If you have a properly normalized schema, then you can do a huge amount with very simple SQL. When it’s poorly normalized, you end up with all sorts of strange and inefficient design patterns in your SQL.

This could come across as me saying “well it’s easy if you do it right”, but the thing is, normalizing a schema is incredibly simple. I would expect a relatively inexperienced software engineer to be able to pick it up literally just from reading the Wikipedia page. In my experience, the more common underlying problem is that inexperienced engineers (even if they’re only inexperienced in terms of SQL and RDBMS knowledge), don’t actually know what normal forms are, or why they’re useful.

Data structures and concurrency control is just fundamentally useful computer science, but for some reason it seems to be a topic a lot of people don’t pay enough attention too. Maybe it’s just my personal pet peeve, but I’ve seen too many projects start with “wow NoSQL is great”, and a few months later end up with giant nested loops in their lookups, and some poorly built custom implementation of MVCC in their business logic.

(NoSQL is great btw, just not for relational data)

>footguns

Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one.

This was fine, until we added a where clause to a, through multiple joins, base table for both options. This tanked the performance >1000x.[1] If we just returned the value it had basically no impact.

We tried solving it with using the result set as a base for a select where we did the filtering. This also resulted in the slow performance. In the end I solved it by wrapping the column in a function call, which solved it. And I still don't know why.

My guess is that somehow without the function call, it optimizes it into one query, which results in basically the original case, while a function forces the evaluation of the subquery first.

[1]Sub 1sec to over 15 minutes

Imo, polymorphic associations are one of the key areas that the relational model in general struggles. You can do them in most RDBMS, but they’re always a bit janky. Even when you’re just modelling your schema, you really have to think quite hard about it, and you’ll really struggle to preserve simplicity.

In this case it was a

left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid)

I guess that is such an uncommon case that it tripped up the optimizer completely.

Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it.

Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1]

[1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.

> Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it.

We might have had a similar experience with this. The first time I stumbled across this problem though I was specifically trying to figure out “what is the relational way to implement polymorphism”, so I pretty much lucked into the a rather productive series of google searches.

It wasn't strictly polymorphism, but the term you wrote led me to a article[1] where it mentioned "alternative parent". This alone instantly made the problem more understandable for me.

[1]http://duhallowgreygeek.com/polymorphic-association-bad-sql-...

> Data structures and concurrency control is just fundamentally useful computer science, but for some reason it seems to be a topic a lot of people don’t pay enough attention too.

Because of the endless articles and comments saying basic computer science knowledge "isn't really needed" for the majority of programming jobs.

>> But most of the awful SQL I’ve seen over my career hasn’t come from poor mastery of SQL, it’s come from poorly normalized schemas. If you have a properly normalized schema, then you can do a huge amount with very simple SQL. When it’s poorly normalized, you end up with all sorts of strange and inefficient design patterns in your SQL.

This is the crucial insight that has made tons of money for me over the last 3 decades. I have all these trite HHOS jokes about it, like telling people denormalizing from a schema not in a normal form is actually the process of "abnormalization". And then there's generic EAVil, where there's nothing that can't be stored, not that nothing really ever means something, heheh.

For every well designed and useful schema I've seen, there were 999 awful ones. For example a physical data model where the query writer has to use string manipulation for joins is going to result in all kinds of suckage. The developer will conclude NoSql is a perfectly reasonable alternative. Even though a modern RDBMS provides all sorts of nifty features to identify and correct such issues ex-post-facto.

For a relational model to work well there must be an a priori data design performed with significant discipline. This seems like too much like Big Design Up Front for the average developer or technical manager to stomach these days. It is true that a well-designed data collection system will have a simpler data design more amenable to a distributed NoSQL system and will support emergent schema and relations which may be divined via machine learning. It will also make Big Ball Of Mud more convenient to implement, but that's a posteriori observation, heheh, like that damned halting problem...

I've found with distributed postgreSQL databases that the optimizer needs more help than you might initially expect. After looking at how the optimizer inefficiently implemented a query, I found a different, but equivalent, way to rewrite the query that could then be efficiently optimized. It's more complicated in the distributed case, because the shard distribution rules have a strong impact on performance. The time needed to re-balance data across shards for a query can be significant.

I've had the opposite problem. In a system I've been working on a lot, I know exactly what indices I want used in what order to read the data. Admittedly the database often figures that out first time, but sometimes it doesn't, then I have to go through an infuriating process of changing parameters or tweaking the query into a mathematically equivalent form until it guesses what I want. In one case I was even forced to write a procedural loop in PL/pgSQL (yes, I'm fully aware this is an antipattern).

I don't want to give hints to the optimiser. I want to specify precisely how the data will be read and joined.

I appreciate this is this is the exception rather than the rule compared to how most developers would prefer to work. And this system is a bit unusual in that it's using a database for passing messages, which it isn't well suited for (especially not the way they're storing the messages.)

Reply on news.ycombinator.com