Would be nice, but bazillions of lines of SQL at the core of almost every business system make this as likely as “We can do better than five fingers.”
The article does nicely illustrate many of the well-known shortcomings of SQL. Chris Date and Hugh Darwen unsuccessfully tried to fix SQL with Tutorial D. Never heard of it? Exactly.
> Chris Date and Hugh Darwen unsuccessfully tried to fix SQL with Tutorial D.
Well, just the D class of languages; Tutorial D is (as the name suggests) a pedagogy-focusses implementation of the D requirements, the intent was that there would be one or more Industrial Ds.
(Dataphor is a D—the first implemented, IIRC—and is successful enough that it's a still-living commercial product.)
Date and Darwen’s D class of languages unifying the OO and relational models are different from Walter Bright’s “what C++ should have been” D language, yes.
Probably more than 30 minutes, SQL may look easy but using it correctly assumes a fairly good understanding of the relational model.
I agree that programmers too often seem to wave database design and integrity aside and create RDBMS/SQL worst-cases with ORMs and terrible queries, and then blame the tools.
I think the problem is that developers love to abstract everything. And with the database often becoming a god object it's often targeted for abstraction.
The problem is, querying data is a complex problem, complicated enough to require a query language.
So the developer goes off to abstract the database for their needs, a helper table object here, a helper row object there and eventually you start to see an Orm appear.
Developers need to be told to stop this behavior.
The abstraction is the thing you are representing in the database, not the database.
Your models should use plain SQL queries to execute behavior.
If you must, setup some helpers to do very basic crud operations, but as soon as you need to really alter a where or a select, drop to SQL, use the language as intended and watch your code suddenly become modular, readable and maintainable.
Yes, this means you will need to write SQL, yes this means you will write a lot of select and where statements, no this is not code duplication, stop freaking out about non problems.
I swear to god DRY is the source of and solution to all developer problems.
COBOL and SQL are for different purposes, apples and oranges. One can’t be worse than the other. Programmers who have never seen or written COBOL should stop trotting it out as an example of bad. You’d be surprised how many important systems are written in COBOL, and how well-suited it is for the problem domains it was designed for.
It's 2020 not 1995. There are already lots of good, production-ready SQL alternatives out there. There's full-fledged businesses that have zero lines of SQL.
Technically ActiveRecord uses Arel, which in turn compiles, if you will, to SQL. Arel (a strict subset of) could theoretically be an alternative to SQL, if databases supported it as a native language. They don't, so the SQL step is a practical requirement.
This is similar to the browser (especially in the pre-WASM days). No matter what language you actually wrote your software in, eventually you were going to end up converting it to Javascript out of necessity. Would you still say that your program written in C, compiled with emscripten, was written in Javascript?
What are good production ready alternatives for SQL? (That are still relational and doesn't make you rewrite half of the database logic again in each new backend you make -- not talking about key/value stores)
Misses the point. Sure, we have alternatives, and always have had alternatives to SQL. But those don't address the bazillions of lines of SQL already running almost every important business system. SQL won't just go away because we can point out it's flaws or come up with something we think works better.
Started reading believing the article would be utter nonsense but by the end was convinced they might be onto something. My hat goes off to them if they pull this off. It will take a lot to push SQL out of its stronghold.
People have a love/hate relation with SQL. I think most software engineers are OK with it, some even find it a curious case to tumble down the rabbit hole of multiple JOINs or inner SELECTs.
But for most people out there who simply want to poke at their business data, SQL, or this article's stated solution - EdgeQL are all pains in the wrong places. If you are making the life of engineers a bit easier, then you have to think of the millions of My/Pg SQL installations.
If you want to make the larger audiences' life easier (the business people who need insights), then you need to think outside of SQL altogether.
SQL is definitely not perfect but it's supported everywhere and integrates with everything, so you can augment it with other languages. That fact makes a lot of the critiques sort of moot.
Yup. The fact that it has been around for 46 years and dominant in its problem space for most of that time is pretty compelling too. In the 90s it was going to be replaced by object databases, said the hype. In the aughts it was up against Mongo and Couch and the like. The pretenders to throne keep coming and going.
Area of curiousity at the moment as I too agree that SQL is a poor fit, even if the better DSL inputs eventually get reduced to SQL command text and parameter arrays.
This was written on my mobile, so haven't had a chance to test it, but here's my first pass at modelling it in SQL:
select sum( case when prev_cust.cust_id is null then 1 else 0 end) / sum( april_cust_count ) as pc_new_cust
from (
/* get unique customers in April */
select distinct cust_id,
1 as april_cust_count
from orders
where order_date between date '2020-04-01' and date '2020-04-30'
) as april_cust
left join
(
/* get customers with a transaction prior to April */
select distinct cust_id
from orders
where order_date < date '2020-04-01'
) as prev_cust
on april_cust.cust_id =
prev_cust.cust_id
Apologies for the lack of code formatting... I find that when SQL is written with a nice formatting (e.g. Nested sub queries with tabs) it reads a whole lot better.
I was looking around yesterday to find alternatives to SQL and found nothing. Everything is in this ridiculous table-based model, the queries of which are complicated and error-prone.
It’s heresy to criticize SQL these days or even suggest that DBs could be easier and more robust. I envision a future DB language that offers perfect ease and safety with queries the way Rust has shown us that the memory unsafeness of C was voluntary all along.
> The NoSQL movement was born, in part, out of the frustration with the perceived stagnation and inadequacy of SQL databases.
I would dispute this. The antecedents of NoSQL were the parallel programming models of HPC. They weren’t specifically excluding SQL, and NoSQL was a term that was invented after the fact.
> As Oskarsson had described, the meeting was about open source, distributed, non-relational databases, for anyone who had "… run into limitations with traditional relational databases…," with the aim of "… figuring out why these newfangled Dynamo clones and BigTables have become so popular lately."
I was using MongoDB at the time (we became one of their first paying customers -- they didn't even want to take money for support at first!) and HPC wasn't in the air. So please elaborate.
The functional style that MapReduce derives from had been used in parallel computing models, e.g. the scatter/compute/gather model of MPI, and in turn this was adopted by Hadoop, CouchDB, MongoDB and others.
Agreed. The name NoSQL succeeded because it was concise and attention grabbing but the main goal wasn’t about eliminating SQL.
A big part was document oriented databases like CouchDB and MongoDB made more sense for a lot of web based use cases, where in the end you’re serving a page of content. Building a relational model often little sense for the web and makes managing the content harder; that a lot of websites can be built with a static site generator highlights that.
>The NoSQL movement was born, in part, out of the frustration with the perceived stagnation and inadequacy of SQL databases. Unfortunately, in the pursuit of ditching SQL, the NoSQL approaches also abandoned the relational model and other good parts of RDBMSes.
Yeah that's what I was thinking, they really don't fix the issues listed, just have chosen to solve other problems, but not in a "going to fix SQL" kind of way.
> The NoSQL movement was born, in part, out of the frustration with the perceived stagnation and inadequacy of SQL databases
I'm a little bit young, but isn't this a bit of a revisionist take, by the author?
I thought that Amazon, Google, FB et al moved away from relational databases because the sharding logic they needed to build on top of these databases was approaching the complexity of a RDBMS. They didn't need strong consistency or support for complex queries, on the kind of data they were storing at scale, and so made compromises in those areas while engineering their purpose-built alternatives (Dynamo, BigTable, Cassandra).
It's not that SQL didn't work, but that the persistence layer was too strong and therefore too slow for their very particular needs. It's like comparing a minivan/suv (mysql/postgres) to a drag racer (nosql databases). You don't want to drive your kids to soccer practice in a two-seater with no airbags, and a 5* crash safety rating isn't as important to the pink-slippers as horsepower and 0-60.
> I thought that Amazon, Google, FB et al moved away from relational databases because the sharding logic they needed to build
Note that these companies did not move away from relational DBs until long after the "NoSQL is Web Scale" video. Yes, Google invented Big Table to help power search (and others), but their revenue system, AdWords, didn't move off MySQL until like 2015. And last I checked, Facebook is still a heavy user of MySQL with sharding.
The original NoSQL software had two major value adds: you didn't have to learn a new language, and were faster (typically via disabling fsync -- the DBA equivalent of running with scissors). If you knew SQL or an ORM already, you were really just hoping mongoDB was faster magically.
These days you can even just tune pgsql to support kv store formats: https://www.postgresql.org/docs/9.1/hstore.html. Yes, you'll have to pay someone to know how to DBA pgsql, or pay AWS to pay someone, but I'm comfortable paying that price.
>These days you can even just tune pgsql to support kv store formats
And you can turn off fsync! Though if you do, disable synchronous_commit instead for most of the performance but none of the potential data corruption (you're still risking data loss, of course, just not corruption).
SQL RDBMSes solve a lot of problems / provide a lot of services: locking & concurrency control, computation, consistency, replication, transactions, etc.
Not all of those services can scale horizontally. Concurrency control most especially, but other things which assert global invariants are often too expensive. Most NoSQL systems remove some of these features in order to scale horizontally. But the problems they solved remain, and need new solutions. This has two effects: it forces clients to do more which hopefully means doing less complex stuff (you can write mega expensive computations in SQL where the nested loops might offend you in handwritten code); and it means higher risk of bugs and more engineering effort for correctness (e.g. transactions in application, eventual consistency, reimplementation of transaction log in queuing systems, etc.).
In transport analogies, RDBMS is like a lift helicopter, NoSQL is like a fleet of container ships. Or RDBMS is like a car, and NoSQL is like a train network. NoSQL is inflexible and needs lots of extra attention at the edges, while RDBMS needs a careful operator and doesn't work well beyond a certain scale unless you give everyone (or subgroups) their own instance (which could be sharding, it can work).
And if you don't have a really big problem, NoSQL is probably the wrong choice, not because it's fast with few safety checks, it's because most of them do very little for you, they can just do a lot of that scaled out.
If you're not scaling out, stuffing JSON into Postgres will give you a better experience even if you hate relational algebra.
That is probably true. But, I'd also suspect that in 99% of cases some form of that data (lots of post processing) ends back in data store where all kinds of people use SQL to analyze it. Two main reasons: 1) join it with other data 2) SQL is so widely understood across functions / roles
I've grown pretty fond of the way Spark SQL queries can be represented with DataFrame operations. There is a more or less 1:1 relationship with SQL, except the commands can be programatically generated and composed. It sure beats stitching together a SQL string when you have a bunch of query clauses that might be optional, or need a generalized way to take 30 result columns, and rename them with a prefix or something.
e.g.
result = dframe.select(*[f.col(colname).alias(f"{colname}_old") for colname in dframe.columns]).join(other_df, 'joincolumn', type='outer')
I used to use SAS macros to conditionally generate monstrous SQL queries all the time. It was a bit hacky, but man I could make a thousand lines of SAS do just about whatever I wanted however I wanted. It really feels like a powerful way to tackle messy real-life business logic.
I've always wondered if a newer language could be designed with ANSI SQL as a transpilation target, or each of the vendor SQLs as targets. Optimization of queries would be a huge problem, but it always seemed like the only way it would be possible to break out of the SQL hegemony, i.e. first transpile, then start developing native support in the open source databases, then pressure the proprietary databases to adopt native support.
Well, that's an object oriented design, and it's not really a full language in that it has its own syntax. I'm talking about a relational language with its own syntax.
I feel like a lot of these concerns are resolved in tools like R's dplyr. You use mostly the same R code, whether your data is a data.frame, or living in a SQL source. dplyr generates the SQL query for you.
By reading the queries it generates it's quick to pick up how the SQL works! Another big advantage is that you can always pull the data into R and have a ton of general purpose tools available.
dplyr is aimed at data analysis though, so may be other use-cases for edge db?
I acknowledge that these are real issues, and commend the authors for attempting to address them. However, these issues rarely cause any real friction for me - I generally find SQL among the most ergonomic languages I use (regardless of dialect).
SQL has a lot of incidental, accidental complexity, no doubt.
Though when I reason about SQL, I think mostly in terms of functional operators over streams of data: projection, filtering, flat-map, join, fold/reduce. Obviously optimization means looking through streams and seeing tables to find indexes etc., but once you get to the execution plan, you're firmly in a concrete world of data flow and streams of tuples.
I didn't get on well with the example syntax in this write-up. It didn't mesh better with my mental model of relational algebra either at the logical or physical execution level - and the truth is you need a foot in both worlds to write good scalable SQL today.
Aside from the complexities of dynamic construction, my biggest problem with SQL is modal changes in query plans, owing to how declarative it is. It's a two-edged sword: the smart planner is great, up until it's stupid. And it usually turns stupid based on index statistics in production at random times.
QUEL ( https://en.wikipedia.org/wiki/QUEL_query_languages ), the original query-language for Ingres, was more orthogonal and consistent than SQL. But IBM decided SQL was more business friendly. Who can argue with that.
"Ted [Codd] also saw the potential of using predicate logic as a foundation for a database language. He discussed this possibility briefly in his 1969 and 1970 papers, and then, using the predicate logic idea as a basis, went on to describe in detail what was probably the very first relational language to be defined, Data Sublanguage ALPHA, in “A Data Base Sublanguage Founded on the Relational Calculus,” Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access and Control, San Diego, Calif. (November 1971). ALPHA as such was never implemented, but it was extremely influential on certain other languages that were, including in particular the Ingres language QUEL and (to a lesser extent) SQL as well."
Ted Codd designed the Relational Calculus as a clean relational-query language. It looks mathematical (scary?) and a little like a set-comprehension. But I think the big mistake is its use of non-ascii chars like ∃ ∈ ∀.
I only had a quick look, is it a computer-friendly offshoot of relational algebra[1], the actual mathematical model for relational databases, as an actual query language?
Relational calculus is another mathematical model that is dual to relational algebra: if you have one representation you can always get the other. Relational algebra describes a fairly direct set of manipulations of database rows that can be implemented efficiently. Relational calculus operations are more abstract, but have useful identity transformations you can use to optimize query plans.
So, when you ask a database system to perform a query, you ask it in relational algebra terms because they’re easy to understand. It then transforms your query into a relational calculus-like form to shuffle things around, and then back to a different, but equivalent, algebraic form to actually execute.
Relational algebra is functional programming: you map-reduce your way to a solution set.
Relational calculus is logical programming: you specify what the solution looks like and the query engine figures out the sequence of operations to find a solution.
It goes a bit deeper than an analogy, but regardless I’m glad it helped!
EDIT: To explain, the relational algebra IS a map-reduce functional programming language. The relational calculus IS a logical programming language. They just use tuples and sets instead of the more familiar lists and strings of LISP, Forth, Prolog, etc.
And the sibling comment further up is right to point out that there are mechanical transformations to turn one into the other, just like there are automatic ways to compile Prolog into LISP (and vice-versa, though that is rarely done).
It's a mistake if your goal is to have the language implemented and become popular. Otherwise I like the notation.
Computer languages would be different if Ascii had provided code points for ∧ ∨ ∩ ∪ ≡ ↑ etc. It's too bad so much real estate is wasted in the control chars.
You don't even need to be a math grad for it to be natural. It's just a natural way to perform set operations, pretty much everybody who knows a little bit of math is familiar with it. End even if you are not, you need like 5-10 minutes to learn it, and then maybe a couple of weeks of using it to become really fluent.
And I feel like "is not ASCII" is pretty silly complaint in 2020 anyways. First of, if these characters would be widely used in 1980, they would be on every keyboard today, same as it was on keyboards for APL. Second, you don't really have them to be literally ∀ and ∃, it could have been &A and &E for example, and then every other IDE, including some fancy product of JetBrains, vim & emacs and most likely even your mysql-cli (at least some wrapper around it written in Python) would turn them into pretty ∀ and ∃ on your screen, same as some editors do for λ.
And third, which is a personal pet peeve of mine (so I probably should keep my mouth shut about that, but I cannot): I really, really wish we'd stop with all this ASCII bullshit already. There's nothing special about ASCII except it's 7-bit. And that's outdated.
It may come as a shock to your average American, but most of the texts in the world are not ASCII, just deal with it. If your product doesn't support that, it probably means it's broken, outdated and will eventually lose to a competition, so you don't do yourself a favour forgetting about that.
And with regards to input, there's nothing complicated about typing ∀ (or anything else like that) on an ordinary US-layout keyboard. For me ∀ is 3 very fluent keystrokes. I use XCompose, which is a blessing, but other operating systems have similar software with similar capabilities as well. I understand that this is not something most people are familiar with, but that's just bad and shouldn't be respected: using a mouse also wasn't familiar to every single PC-user out there. With demand comes the supply.
So I really dream about the day when typing nice, clear syntax like ∀ instead of awkward Perl-like character sequences (to read which you need mentally traumatizing professional deformation and/or an IDE post-processor) becomes the norm of programming.
1. Anyone striving to build a better SQL should make a comprehensive list of common (but difficult!) database tasks for OLTP and OLAP workloads. This will expose the weakness of their language. SQL has had 50 years and myriads of improvements to cover all these common cases. This is not a fair fight, so come prepared.
2. It's not enough to be just "better than SQL" to replace it. SQL has such a huge momentum that a new language needs to be absolutely better _and_ it should have many features that SQL cannot possibly have. My nice-to-have list would contain predictable performance, lock ordering, ownership relations (for easy data cleanup), and a standard low level language which the query optimizer would output.
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:
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.
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)
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.
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.
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.
> 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.)
Alternatively you can make a compiler from sql to your alternative, which would make it relatively easy to get the "I just want a familiar thing that works" people on board.
I don't know if it's necessary for a better language to do both OLAP and OLTP; it seems that they are used by different classes of users with relatively low overlap in language features. But I definitely agree that such a test as (1) would be a critical prerequisite to an improvement over SQL.
Honestly I think SQL is pretty easy. I love it. I can teach the basics to a new person in minutes.
You know what we could do better at? Crappy explains from database engines. Crappy rate limiting capabilities. Poor feedback on keep cache pipelines fed during scans. Poor feedback on column size effects on reading stripes from disk and size alignments between the filesystem and database.
An excellent point: there are so many other problems that deserve our attention regarding databases that talking about something that's worked really well for 50 years is kind of silly. We have bigger fish to fry.
Not only this, but resources GALORE since its been around longer than the internet and has been used by just about every developer. Java, Python, C#, Node, PHP etc... they all know SQL.
Your brain must work different to mine. SQL is by far the hardest tool I use. I’ve used all the main languages from asm up to js for real work and nothing breaks my brain like SQL.
I use it daily in a business that is heavy on SPs and while I get by and am improving the jump from inner joins and selects to CTEs and the other wizardry is massive.
I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php”
> I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php”
The thing about SQL is that it's the fastest way to read&write data in a relational database. Maybe writing the code is faster in js/swift/php, but the code will run faster in SQL. If you need to do something to 100M pieces of data you can do a lot worse than SQL.
You cannot compare SQL to either of those languages, since they are not built solely for data for data manipulation, but also a lot of other things, which SQL is not. SQL can do a lot too, but just because you e.g can do formatting in SQL doesn't Mean it's the correct place or choice.
That's circular reasoning though. Why do you want your data to be in a relational database? Particularly if you're not actually using its features (I don't think I've ever seen a web application that actually got any value out of database-level transactions, for example). A different kind of datastore could offer you better performance and easier querying.
I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in this field.
I struggle to understand this viewpoint. In my experience business rules are harder to express in SQL than in practically any first-class programming language. "Is this value one of this list of values" - whether that list is hardcoded or dynamically obtained - is completely trivial.
(Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)
With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint.
Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
> With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint.
Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite.
> Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
We use exclusively stored procedures, so if we want to add a FK constraint while the application doesn't adhere to it, we do one of two things:
Either add it to the application and push out a new version.
Or handle it within the stored procedure.
Additionally, there would be the option of updating the database when pushing a new software update out.
> If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code.
In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
You're arguing that business rules are easier to express in other programming language, but the point of the parent post (and SQL) is that business rules are easier to enforce in SQL.
It's trivial to write some code verifying "Is this value one of this list of values", but writing such code not ensure that this constraint will actually be met in 100% of your past and future data.
It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint.
> It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint.
If you have business logic in your datastore then that puts you in much the same position though. If you have a trigger you might have data written before it was introduced, or queries that ran with it disabled. If you're gradually rolling out a new version then you might have some data that follows a constraint and some that doesn't. And so on.
For triggers, you can run a procedure that executes it for all past data. Or write a procedure that updates the data to a valid state.
When gradually rolling out, you can have adjusted stored procedures that deal with the different versions, and turn off the old one when it is no longer in use.
And with good practices (mainly a decent type system that lets you distinguish between checked and unchecked values) you'll have no problems with constraints in the application layer either.
I don't think we'll come to an agreement in regards to that, so, I am gonna voice my disagreement and move on.
It was informational that I saw your viewpoint, even though I disagree. In the future I will definitely try and reevaluate my viewpoints to see if maybe I am wrong after all.
As long as your application layer is a single homogeneous application running the same code.
That is approximately never the case. Current widespread practices are moving away from it, with multi-services, and old fashioned practices of mixing customized and off the shelf tools basically forbid it.
So where are you proposing the consensus be placed? A lot (I'd argue most) applications need consensus for them to be stable and reliable in the long term, so either you place it in the datastore or in the logic processing. Where else would you place it?
I prefer to see the whole system as a succession of stream transformations (https://www.confluent.io/blog/turning-the-database-inside-ou...). If you view the sequence of input events as first-class and the "current state of the world" as derived, then a lot of problems go away. You need a datastore that can give you a consistent answer as to what order events occurred in, but it's a lot easier to make appending to an append-only list atomic than to make arbitrary state computations ACID.
For downstream derived computations, basically you either make the causal relationship explicit, or accept that you have eventual consistency. The only case where you can have inconsistency is where you have a "diamond" in your computations (i.e. you compute B that's derived from A and C that's also derived from A, and you compute D that's derived from B and C). So you figure out the business implications and either accept it or eliminate the diamond (by computing (A, B) from A and (B,C) from (A, B) instead of computing B and C separately from A). You will also get inconsistency if you do some totally ad-hoc query that's not part of your existing pipelines, but usually that's the kind of reporting query that doesn't need to be 100% consistent; if you do need a consistent version of that then the best approach is to take a regular snapshot, which can be consistent.
Basically you have a lot more precise control, you have causal relationships where you have explicit dependencies, so you have the level of consistency that you need for all your operational stuff, but you don't have a globally consistent realtime view of everything. It may take more work up front, but IME the notion of that kind of global consistency is a lie in a distributed world; even if you just have a basic webapp then you can't actually achieve the kind of consistency that that model pretends you have, because what the user is viewing in their browser (and then potentially making changes based on) at any given time is not necessarily the same as what's in the central database.
To get back to the question, this means that if inconsistency is due to an actual bug, it's pretty easy to solve: fix the bug and then regenerate from the original events. If you don't understand why data is inconsistent then you can always look back to the source events and figure out what it should be.
It makes some things simple and insanely fast at runtime, at the cost of making other things impossible or if possible horribly awkward and just as slow as in any other programming language.
In those rare cases, you can do post processing on the data in code if need be. I personally don't see it fully as either/or but as mostly SQL with enhancing the data in rare cases via "traditional" code.
> (I don't think I've ever seen a web application that actually got any value out of database-level transactions, for example)
You only ever have one user in your web applications at a time?
> A different kind of datastore could offer you better performance and easier querying.
Citation needed.
The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
> You only ever have one user in your web applications at a time?
No, but database-level transactions don't help you deal with concurrent users. You don't keep a transaction open between showing the user an edit page and applying their changes (at least I hope you don't), your transactions can last at most through the web request-response cycle. So if you want actual transactional behaviour (e.g. a wiki with "another user is editing this page") you have to reimplement it yourself "in userspace".
> Citation needed.
SQL datastores are very open about the tradeoffs that they make for the sake of ACID isolation - the fact that the transaction isolation level is tuneable shows that, as does the fact that all serious SQL databases use MVCC.
Also MySQL had benchmarks showing that 75% of the time for a query by primary key was spent on parsing the SQL.
> The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
Not my experience, because the overwhelming majority of the time the code doesn't make any use of the complicated SQL functionality. I've literally never seen a cross join in use. I can count the number of times I've seen nontrivial aggregations in live code on one hand. The few times I've written recursive queries I found poor driver support, incompatibilities between different databases. So you pay the cost of flattening your data into the SQL table model, but most of the time you get no benefit from it.
There are pretty much three different kinds of queries: indexed lookups into raw data, indexed lookups into derived data (secondary indexes being a special case of this) and aggregations over a full table (table scans being a special case of this). A query model that represents those cases separately is easier to understand and work with, especially when it comes to understanding the performance. If your datastore supports server-side map-reduce style aggregations then you can literally execute arbitrary code in a "query", but you'll have a clear understanding that you're doing something different (with performance implications) from looking up a value by its indexed key.
The problem for SQL is (apart from horrible syntax that makes any query re-factoring tedious) that databases are very strict structures, that through their rigidity make some operations very fast, but some operations impossible (to perform in this fast manner).
When you are crafting good SQL query (crafting is the right word as each non-trivial SQL query is a little puzzle that may take you few days to solve because of the constraints) you need to stay within the bounds of the this fast db world.
Whenever you are forced to open a cursor or use CTE for recursion or even have a full table scan you already left the fast land and landed in the world that all general purpose language inhibit, where you have to iterate and recurse and everything takes ages. And in that land any other language beats SQL because any other language has the syntax designed to make things easier in this world while SQL has the syntax that's just good enough for the fast world where operations are highly restricted and when it ventures into the slow world it's just a horrible mess.
You’re not thinking in sets if you’re constantly inclined to move to js/php/swift. Spend some time trying to break this mental bearier and you’ll find that SQL becomes easier and more sensible.
I had three college classes that really sealed this up for me: linear algebra, a set theory 'light' class, and a class on database mechanics itself. After than I was able to reason about mathematical sets, and scalar functions and what the database engine is doing.
If you "just learn SQL" without understanding the abstraction below it, it'll be difficult to be successful, much the same with anything else.
The last technology that attempted this task was NoSQL, and we all know how that ended.
First, people realized that schemas (just like static types) are extraordinarily important for robust software.
Second, NoSQL lost to SQL over the long run in pretty much all dimensions: query language, performance, scalability, concision, etc...
As a result, not only is NoSQL on the way out, but SQL databases have actually become better at supporting NoSQL features than any NoSQL database.
SQL didn't just win, it absorbed its opponent and became even better as a result. Never underestimate the versatility and adaptiveness of a technology.
This is trouble for the example for calculating the average number of reviews across movies:
SELECT math::mean(
Movie {
description,
number_of_reviews := count(.reviews)
}.number_of_reviews
);
Never mind, they are not sets:
> Strictly speaking, EdgeQL sets are multisets, as they do not require the elements to be unique.
The relational model is firmly based on the idea of a relation as a "set of tuples", and a major criticism of SQL has been that it views data as an ordered sequence of tuples.
So I'm skeptical of the claim that EdgeQL is really based on the relational model.
(Not clear whether multisets are ordered - wondering about window functions etc...)
468 comments
[ 2.8 ms ] story [ 333 ms ] threadThe article does nicely illustrate many of the well-known shortcomings of SQL. Chris Date and Hugh Darwen unsuccessfully tried to fix SQL with Tutorial D. Never heard of it? Exactly.
Well, just the D class of languages; Tutorial D is (as the name suggests) a pedagogy-focusses implementation of the D requirements, the intent was that there would be one or more Industrial Ds.
(Dataphor is a D—the first implemented, IIRC—and is successful enough that it's a still-living commercial product.)
I agree that programmers too often seem to wave database design and integrity aside and create RDBMS/SQL worst-cases with ORMs and terrible queries, and then blame the tools.
I often joke that SQL is the COBOL of the 21st century. HHOS. There's worse things...e.g. COBOL.
Actually it's very difficult to deny the empirically discernible utility of relational databases and SQL. SQLite, for example.
I think the problem is that developers love to abstract everything. And with the database often becoming a god object it's often targeted for abstraction.
The problem is, querying data is a complex problem, complicated enough to require a query language.
So the developer goes off to abstract the database for their needs, a helper table object here, a helper row object there and eventually you start to see an Orm appear.
Developers need to be told to stop this behavior.
The abstraction is the thing you are representing in the database, not the database.
Your models should use plain SQL queries to execute behavior.
If you must, setup some helpers to do very basic crud operations, but as soon as you need to really alter a where or a select, drop to SQL, use the language as intended and watch your code suddenly become modular, readable and maintainable.
Yes, this means you will need to write SQL, yes this means you will write a lot of select and where statements, no this is not code duplication, stop freaking out about non problems.
I swear to god DRY is the source of and solution to all developer problems.
This is similar to the browser (especially in the pre-WASM days). No matter what language you actually wrote your software in, eventually you were going to end up converting it to Javascript out of necessity. Would you still say that your program written in C, compiled with emscripten, was written in Javascript?
"Accrington Stanley"
https://www.youtube.com/watch?v=zPFrTBppRfw
But for most people out there who simply want to poke at their business data, SQL, or this article's stated solution - EdgeQL are all pains in the wrong places. If you are making the life of engineers a bit easier, then you have to think of the millions of My/Pg SQL installations.
If you want to make the larger audiences' life easier (the business people who need insights), then you need to think outside of SQL altogether.
Area of curiousity at the moment as I too agree that SQL is a poor fit, even if the better DSL inputs eventually get reduced to SQL command text and parameter arrays.
Absolutely!
This assumes the following schema:It’s heresy to criticize SQL these days or even suggest that DBs could be easier and more robust. I envision a future DB language that offers perfect ease and safety with queries the way Rust has shown us that the memory unsafeness of C was voluntary all along.
I would dispute this. The antecedents of NoSQL were the parallel programming models of HPC. They weren’t specifically excluding SQL, and NoSQL was a term that was invented after the fact.
Can you elaborate on what you are thinking of? As a refresh, here's when and how the (current usage at least) of NoSQL was introduced: https://subscription.packtpub.com/book/big_data_and_business... in 2009.
> As Oskarsson had described, the meeting was about open source, distributed, non-relational databases, for anyone who had "… run into limitations with traditional relational databases…," with the aim of "… figuring out why these newfangled Dynamo clones and BigTables have become so popular lately."
I was using MongoDB at the time (we became one of their first paying customers -- they didn't even want to take money for support at first!) and HPC wasn't in the air. So please elaborate.
http://2009.drupalcamp.at/sessions/chx-session.html as far as I can remember this was my first MongoDB talk. It's been a long time ago.
The functional style that MapReduce derives from had been used in parallel computing models, e.g. the scatter/compute/gather model of MPI, and in turn this was adopted by Hadoop, CouchDB, MongoDB and others.
A big part was document oriented databases like CouchDB and MongoDB made more sense for a lot of web based use cases, where in the end you’re serving a page of content. Building a relational model often little sense for the web and makes managing the content harder; that a lot of websites can be built with a static site generator highlights that.
"Has anyone fixed these problems elsewhere?"
Then:
>The NoSQL movement was born, in part, out of the frustration with the perceived stagnation and inadequacy of SQL databases. Unfortunately, in the pursuit of ditching SQL, the NoSQL approaches also abandoned the relational model and other good parts of RDBMSes.
Yeah that's what I was thinking, they really don't fix the issues listed, just have chosen to solve other problems, but not in a "going to fix SQL" kind of way.
I'm a little bit young, but isn't this a bit of a revisionist take, by the author?
I thought that Amazon, Google, FB et al moved away from relational databases because the sharding logic they needed to build on top of these databases was approaching the complexity of a RDBMS. They didn't need strong consistency or support for complex queries, on the kind of data they were storing at scale, and so made compromises in those areas while engineering their purpose-built alternatives (Dynamo, BigTable, Cassandra).
It's not that SQL didn't work, but that the persistence layer was too strong and therefore too slow for their very particular needs. It's like comparing a minivan/suv (mysql/postgres) to a drag racer (nosql databases). You don't want to drive your kids to soccer practice in a two-seater with no airbags, and a 5* crash safety rating isn't as important to the pink-slippers as horsepower and 0-60.
Or am I missing something?
Note that these companies did not move away from relational DBs until long after the "NoSQL is Web Scale" video. Yes, Google invented Big Table to help power search (and others), but their revenue system, AdWords, didn't move off MySQL until like 2015. And last I checked, Facebook is still a heavy user of MySQL with sharding.
The original NoSQL software had two major value adds: you didn't have to learn a new language, and were faster (typically via disabling fsync -- the DBA equivalent of running with scissors). If you knew SQL or an ORM already, you were really just hoping mongoDB was faster magically.
These days you can even just tune pgsql to support kv store formats: https://www.postgresql.org/docs/9.1/hstore.html. Yes, you'll have to pay someone to know how to DBA pgsql, or pay AWS to pay someone, but I'm comfortable paying that price.
And you can turn off fsync! Though if you do, disable synchronous_commit instead for most of the performance but none of the potential data corruption (you're still risking data loss, of course, just not corruption).
Not all of those services can scale horizontally. Concurrency control most especially, but other things which assert global invariants are often too expensive. Most NoSQL systems remove some of these features in order to scale horizontally. But the problems they solved remain, and need new solutions. This has two effects: it forces clients to do more which hopefully means doing less complex stuff (you can write mega expensive computations in SQL where the nested loops might offend you in handwritten code); and it means higher risk of bugs and more engineering effort for correctness (e.g. transactions in application, eventual consistency, reimplementation of transaction log in queuing systems, etc.).
In transport analogies, RDBMS is like a lift helicopter, NoSQL is like a fleet of container ships. Or RDBMS is like a car, and NoSQL is like a train network. NoSQL is inflexible and needs lots of extra attention at the edges, while RDBMS needs a careful operator and doesn't work well beyond a certain scale unless you give everyone (or subgroups) their own instance (which could be sharding, it can work).
And if you don't have a really big problem, NoSQL is probably the wrong choice, not because it's fast with few safety checks, it's because most of them do very little for you, they can just do a lot of that scaled out.
If you're not scaling out, stuffing JSON into Postgres will give you a better experience even if you hate relational algebra.
Surprisingly, Spanner has tables with columns and you can run SQL on top of it.
e.g. result = dframe.select(*[f.col(colname).alias(f"{colname}_old") for colname in dframe.columns]).join(other_df, 'joincolumn', type='outer')
and so forth.
But (as I haven't read of their blog posts) I am a bit more reluctant about the whole thing when they describe it as an ORM.
Can we leave the ORM and take the query language and implement this as a Postgres extension?
But if you can define a new query language that can be implemented by existing relational DBs, you might actually have a shot at displacing SQL.
Both DQL and HQL are complete query languages.
By reading the queries it generates it's quick to pick up how the SQL works! Another big advantage is that you can always pull the data into R and have a ton of general purpose tools available.
dplyr is aimed at data analysis though, so may be other use-cases for edge db?
I acknowledge that these are real issues, and commend the authors for attempting to address them. However, these issues rarely cause any real friction for me - I generally find SQL among the most ergonomic languages I use (regardless of dialect).
Though when I reason about SQL, I think mostly in terms of functional operators over streams of data: projection, filtering, flat-map, join, fold/reduce. Obviously optimization means looking through streams and seeing tables to find indexes etc., but once you get to the execution plan, you're firmly in a concrete world of data flow and streams of tuples.
I didn't get on well with the example syntax in this write-up. It didn't mesh better with my mental model of relational algebra either at the logical or physical execution level - and the truth is you need a foot in both worlds to write good scalable SQL today.
Aside from the complexities of dynamic construction, my biggest problem with SQL is modal changes in query plans, owing to how declarative it is. It's a two-edged sword: the smart planner is great, up until it's stupid. And it usually turns stupid based on index statistics in production at random times.
And before that there was ALPHA. From https://www.labouseur.com/courses/db/s2-Remembering-Codd-2.p... :
"Ted [Codd] also saw the potential of using predicate logic as a foundation for a database language. He discussed this possibility briefly in his 1969 and 1970 papers, and then, using the predicate logic idea as a basis, went on to describe in detail what was probably the very first relational language to be defined, Data Sublanguage ALPHA, in “A Data Base Sublanguage Founded on the Relational Calculus,” Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access and Control, San Diego, Calif. (November 1971). ALPHA as such was never implemented, but it was extremely influential on certain other languages that were, including in particular the Ingres language QUEL and (to a lesser extent) SQL as well."
Here's an example from http://arwan.lecture.ub.ac.id/files/2013/10/4.-relationalcal... :
SQL:
Relational Calculus:[1]https://en.wikipedia.org/wiki/Relational_algebra
So, when you ask a database system to perform a query, you ask it in relational algebra terms because they’re easy to understand. It then transforms your query into a relational calculus-like form to shuffle things around, and then back to a different, but equivalent, algebraic form to actually execute.
Relational calculus is logical programming: you specify what the solution looks like and the query engine figures out the sequence of operations to find a solution.
EDIT: To explain, the relational algebra IS a map-reduce functional programming language. The relational calculus IS a logical programming language. They just use tuples and sets instead of the more familiar lists and strings of LISP, Forth, Prolog, etc.
And the sibling comment further up is right to point out that there are mechanical transformations to turn one into the other, just like there are automatic ways to compile Prolog into LISP (and vice-versa, though that is rarely done).
there exists, in, for all
Computer languages would be different if Ascii had provided code points for ∧ ∨ ∩ ∪ ≡ ↑ etc. It's too bad so much real estate is wasted in the control chars.
Original ASCII (1963) had ↑, but it was converted to ^ in 1967 so that it could double as a circumflex.
(Also ←, which was a good assignment operator; it's too bad _ didn't replace \ instead.)
And I feel like "is not ASCII" is pretty silly complaint in 2020 anyways. First of, if these characters would be widely used in 1980, they would be on every keyboard today, same as it was on keyboards for APL. Second, you don't really have them to be literally ∀ and ∃, it could have been &A and &E for example, and then every other IDE, including some fancy product of JetBrains, vim & emacs and most likely even your mysql-cli (at least some wrapper around it written in Python) would turn them into pretty ∀ and ∃ on your screen, same as some editors do for λ.
And third, which is a personal pet peeve of mine (so I probably should keep my mouth shut about that, but I cannot): I really, really wish we'd stop with all this ASCII bullshit already. There's nothing special about ASCII except it's 7-bit. And that's outdated.
It may come as a shock to your average American, but most of the texts in the world are not ASCII, just deal with it. If your product doesn't support that, it probably means it's broken, outdated and will eventually lose to a competition, so you don't do yourself a favour forgetting about that.
And with regards to input, there's nothing complicated about typing ∀ (or anything else like that) on an ordinary US-layout keyboard. For me ∀ is 3 very fluent keystrokes. I use XCompose, which is a blessing, but other operating systems have similar software with similar capabilities as well. I understand that this is not something most people are familiar with, but that's just bad and shouldn't be respected: using a mouse also wasn't familiar to every single PC-user out there. With demand comes the supply.
So I really dream about the day when typing nice, clear syntax like ∀ instead of awkward Perl-like character sequences (to read which you need mentally traumatizing professional deformation and/or an IDE post-processor) becomes the norm of programming.
1. Anyone striving to build a better SQL should make a comprehensive list of common (but difficult!) database tasks for OLTP and OLAP workloads. This will expose the weakness of their language. SQL has had 50 years and myriads of improvements to cover all these common cases. This is not a fair fight, so come prepared.
2. It's not enough to be just "better than SQL" to replace it. SQL has such a huge momentum that a new language needs to be absolutely better _and_ it should have many features that SQL cannot possibly have. My nice-to-have list would contain predictable performance, lock ordering, ownership relations (for easy data cleanup), and a standard low level language which the query optimizer would output.
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.
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.
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!
Edit: don't do it
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)
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
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.
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.
[1]http://duhallowgreygeek.com/polymorphic-association-bad-sql-...
Because of the endless articles and comments saying basic computer science knowledge "isn't really needed" for the majority of programming jobs.
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 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.)
You know what we could do better at? Crappy explains from database engines. Crappy rate limiting capabilities. Poor feedback on keep cache pipelines fed during scans. Poor feedback on column size effects on reading stripes from disk and size alignments between the filesystem and database.
I use it daily in a business that is heavy on SPs and while I get by and am improving the jump from inner joins and selects to CTEs and the other wizardry is massive.
I want to be better at SQL but so many problems I hit up against and think “well that’s a 2 minute job in js/swift/php”
The thing about SQL is that it's the fastest way to read&write data in a relational database. Maybe writing the code is faster in js/swift/php, but the code will run faster in SQL. If you need to do something to 100M pieces of data you can do a lot worse than SQL.
(Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "database" may be done at will in production then you'll find SQL logic easier to adjust, but that's a reflection of your policies rather than any fundamental reality.)
Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
Which is to say that you have little control over deployment. Adding a new foreign key constraint might block queries for an indeterminate amount of time while a new index is built, but you have no way to introspect or predict that at the SQL level (if you're lucky there might be a specific way to find out via the internals of your particular datastore implementation). PHP fans used to tout being able to just edit the code on the production server as an advantage, but most of us recognise it as the opposite.
> Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.
If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
Either add it to the application and push out a new version. Or handle it within the stored procedure.
Additionally, there would be the option of updating the database when pushing a new software update out.
> If your data is well architected so that you only have a single representation of each piece of data, sure. Equally if your application is well architected you can have a single API where any given thing happens.
I think I formulated it badly before. In this case I meant that we have one stored procedure for each thing we do. So that, if the underlying data changes for example, we only need to change the procedures directly acessing it. We can then call those procedures from multiple different parts of the code. And the procedures are all managed within one application, mssms in our case, where you can have a much cleaner structure than within code.
In my experience, in PHP for example, you tend to write VERY similar/same queries in multiple locations, which can cause you to find it harder to all instances.
It's trivial to write some code verifying "Is this value one of this list of values", but writing such code not ensure that this constraint will actually be met in 100% of your past and future data.
It's very difficult to guarantee that a business constraint expressed in your client app is actually enforced - there can be different versions of your app applying the constraint differently, there can be multiple apps accessing the datastore, there could be manual interventions in various ways to the datastore, the app could have code paths that may cause data insertion or alterations without running that verification in certain conditions, etc; so for all intents and purposes you can't really rely on that constraint.
If you have business logic in your datastore then that puts you in much the same position though. If you have a trigger you might have data written before it was introduced, or queries that ran with it disabled. If you're gradually rolling out a new version then you might have some data that follows a constraint and some that doesn't. And so on.
When gradually rolling out, you can have adjusted stored procedures that deal with the different versions, and turn off the old one when it is no longer in use.
So, I fail to see the problems you mentioned.
It was informational that I saw your viewpoint, even though I disagree. In the future I will definitely try and reevaluate my viewpoints to see if maybe I am wrong after all.
That is approximately never the case. Current widespread practices are moving away from it, with multi-services, and old fashioned practices of mixing customized and off the shelf tools basically forbid it.
For downstream derived computations, basically you either make the causal relationship explicit, or accept that you have eventual consistency. The only case where you can have inconsistency is where you have a "diamond" in your computations (i.e. you compute B that's derived from A and C that's also derived from A, and you compute D that's derived from B and C). So you figure out the business implications and either accept it or eliminate the diamond (by computing (A, B) from A and (B,C) from (A, B) instead of computing B and C separately from A). You will also get inconsistency if you do some totally ad-hoc query that's not part of your existing pipelines, but usually that's the kind of reporting query that doesn't need to be 100% consistent; if you do need a consistent version of that then the best approach is to take a regular snapshot, which can be consistent.
Basically you have a lot more precise control, you have causal relationships where you have explicit dependencies, so you have the level of consistency that you need for all your operational stuff, but you don't have a globally consistent realtime view of everything. It may take more work up front, but IME the notion of that kind of global consistency is a lie in a distributed world; even if you just have a basic webapp then you can't actually achieve the kind of consistency that that model pretends you have, because what the user is viewing in their browser (and then potentially making changes based on) at any given time is not necessarily the same as what's in the central database.
To get back to the question, this means that if inconsistency is due to an actual bug, it's pretty easy to solve: fix the bug and then regenerate from the original events. If you don't understand why data is inconsistent then you can always look back to the source events and figure out what it should be.
You only ever have one user in your web applications at a time?
> A different kind of datastore could offer you better performance and easier querying.
Citation needed.
The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
No, but database-level transactions don't help you deal with concurrent users. You don't keep a transaction open between showing the user an edit page and applying their changes (at least I hope you don't), your transactions can last at most through the web request-response cycle. So if you want actual transactional behaviour (e.g. a wiki with "another user is editing this page") you have to reimplement it yourself "in userspace".
> Citation needed.
SQL datastores are very open about the tradeoffs that they make for the sake of ACID isolation - the fact that the transaction isolation level is tuneable shows that, as does the fact that all serious SQL databases use MVCC.
Also MySQL had benchmarks showing that 75% of the time for a query by primary key was spent on parsing the SQL.
> The so called "NoSQL" systems manage to scale better because they have very constrained query models. So they are only "easier", perhaps, in the sense of supporting less functionality, but in most cases that leads to a big increase in complexity in the rest of the application code.
Not my experience, because the overwhelming majority of the time the code doesn't make any use of the complicated SQL functionality. I've literally never seen a cross join in use. I can count the number of times I've seen nontrivial aggregations in live code on one hand. The few times I've written recursive queries I found poor driver support, incompatibilities between different databases. So you pay the cost of flattening your data into the SQL table model, but most of the time you get no benefit from it.
There are pretty much three different kinds of queries: indexed lookups into raw data, indexed lookups into derived data (secondary indexes being a special case of this) and aggregations over a full table (table scans being a special case of this). A query model that represents those cases separately is easier to understand and work with, especially when it comes to understanding the performance. If your datastore supports server-side map-reduce style aggregations then you can literally execute arbitrary code in a "query", but you'll have a clear understanding that you're doing something different (with performance implications) from looking up a value by its indexed key.
When you are crafting good SQL query (crafting is the right word as each non-trivial SQL query is a little puzzle that may take you few days to solve because of the constraints) you need to stay within the bounds of the this fast db world.
Whenever you are forced to open a cursor or use CTE for recursion or even have a full table scan you already left the fast land and landed in the world that all general purpose language inhibit, where you have to iterate and recurse and everything takes ages. And in that land any other language beats SQL because any other language has the syntax designed to make things easier in this world while SQL has the syntax that's just good enough for the fast world where operations are highly restricted and when it ventures into the slow world it's just a horrible mess.
If you "just learn SQL" without understanding the abstraction below it, it'll be difficult to be successful, much the same with anything else.
First, people realized that schemas (just like static types) are extraordinarily important for robust software.
Second, NoSQL lost to SQL over the long run in pretty much all dimensions: query language, performance, scalability, concision, etc...
As a result, not only is NoSQL on the way out, but SQL databases have actually become better at supporting NoSQL features than any NoSQL database.
SQL didn't just win, it absorbed its opponent and became even better as a result. Never underestimate the versatility and adaptiveness of a technology.
Do you mean billion dollar companies?
Don't get me wrong, wouldn't go near the popular NoSQL databases I've used in the past again, but I sure wish I invented them.
maybe EdgeQL can have the same success by demonstrating improvements that can be added to SQL databases.
This is trouble for the example for calculating the average number of reviews across movies:
Never mind, they are not sets:> Strictly speaking, EdgeQL sets are multisets, as they do not require the elements to be unique.
The relational model is firmly based on the idea of a relation as a "set of tuples", and a major criticism of SQL has been that it views data as an ordered sequence of tuples.
So I'm skeptical of the claim that EdgeQL is really based on the relational model.
(Not clear whether multisets are ordered - wondering about window functions etc...)
1. Elimination of duplicates from every projection is prohibitively expensive.
2. Sometimes you actually _want_ duplicates to show up without injecting a synthetic key into every projection.
3. There's DISTINCT.