It would be interesting to see a "new and noteworthy" section in these types of news recaps. There's always a lot of hype around new databases, and it'd be nice to see an unbiased perspective from someone with such deep knowledge of database systems, similar to Aphyr's deep dives into consistency and durability.
This seems focused on only SQL systems, and only the largest ones, which is okay, but I think there's a lot more to the story than that :)
If you are into global consistency but also want NoSQL flexibility, my employer FaunaDB has ACID transactions, joins, indexes, and object level access control.
Thanks for pointing that out! They appear to only support one of the two time models proposed by the research community. They support transaction time (when you write it), but not the interesting and useful 'valid time'. The difference can be explained as you are going to be paid a salary for working say from July 1, 2018 to June 30, 2019 (this is valid time), but we write the information into the db at a different time, say we write it on June 15 (this is transaction time), then we realize a mistake and update your salary record on June 20th. But your employment starts July 1.
Is this the final nail in the coffin for the NoSQL hype?
Although I'm not sure the road back to sanity is to add SQL interfaces to non-relational database systems. SQL and the relational model are often conflated, both in the NoSQL hype and apparently also in the backlash. I like things like LINQ in .net which allows you to work with a relational model and use relational algebra without having to use the SQL language
NoSQL is not going to die, there are many places where specialized non-relational databases are appropriate. But the hype wave may be coming to an end.
I am personally playing a bit with Apache Calcite - SQL without the data storage - and it sounds very interesting. For most reporting jobs, a couple of SQL queries are all that s needed and are easy to read and easy to write, even if you are actually querying some in-memory structures.
I recently bought the author’s book to brush some cobwebs off parts of my SQLing. Over the years I’ve seen Markus’s blog posts and guides posted here on HN. I’m always impressed when I see an individual focus on a topic and build an identity / business like this. Thanks for all the blog posts, Markus!
SQL is by no means perfect. For one, t̶h̶e̶r̶e̶'̶s̶ ̶n̶o̶ ̶o̶f̶f̶i̶c̶i̶a̶l̶ ̶s̶p̶e̶c̶i̶f̶i̶c̶a̶t̶i̶o̶n̶: some dialects are meaningfully different than others, and even similar ones are often full of implementation details about the underlying database. It has a ton of quirks, and isn't as powerful as I'd always want it to be. And sometimes it can be really hard to read.
But I still haven't found a better universal "language" to talk about extracting and manipulating data. The core set of functionality of SQL (selects, joins, aggregations) is usually adequate to express powerful ideas. Most importantly, a lot of people who work with data know it, and I've found semi-technical people (including non-programmers) can learn it quickly. Anyone who has been paying attention to trends in the data world in the last 5 years knows SQL is here to stay for the long-term.
> and isn't as powerful as I'd always want it to be
Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.
>I'd take ISO/IEC 9075 as an official specification
Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift.
>Do you have examples?
For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.
Declarative languages are almost always more verbose than imperative languages. Not sure why you'd use LOC to compare across them when they have readily available execution engine planners with stats and everything.
As for myself, I wish SQL's SELECT were more expressive. It's currently organized almost exactly like a mirror of the underlying relational algebra operators that a query engine has to execute internally.
But relational operators isn't what developers like to work with. SQL is about "projecting" relations, putting them through some basic transforms, none of which compose that well because of SQL's statement-oriented syntax. For example, you can't group a group-by. A group-by takes a list of terms, but you can't funnel the output through another one without wrapping the whole query in a "SELECT FROM (...) AS ...".
I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.
> I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.
I do not think this can be easily fixed in SQL because the problems are not in the syntax but rather lie deeper at the level of the underlying paradigm and principles.
Whew. Those years of enduring "SQL is dead" posts were not easy. Now if we can just rebrand "cloud" to "someone else's computer", much will be right with the world.
The conflation of SQL with consistency in this blog post is dumb. Yes Google offers SQL on Spanner but the query execution is built atop lower level Spanner primitives that are also available to developers and are similar to Bigtable concepts. Also Bigtable is still much, much larger and more important within Google than is Spanner/F1, so the death of Bigtable is much overhyped.
Point is there are strong and weak consistency models with and without SQL execution engines, and they’re all useful.
The SQL standard specifies explicit consistency semantics for statements and transactions, though. So something claiming to adhere to e.g. SQL:2011, is claiming that executing certain commands will have certain consistency properties (and if you've set up your DBMS to have weaker consistency, then it should be blaring a loud alarm that you've effectively put it in a non-SQL-standard-conformant mode.)
Other generic DBMS query protocols could specify their own consistency semantics, but I'm not aware of any that do. (I guess, if you consider REST a "DBMS query protocol", then CouchDB is an example of a DBMS "conforming" to the consistency semantics specified in the REST protocol.)
If that's your definition then it's even more dumb to hold up Google Cloud Spanner as an example of how SQL gives you consistency. There's NO WAY to mutate Spanner tables via SQL. All writes are performed via the lower-level RPC interfaces. The only thing you can do with Spanner from SQL is query it. The consistency provided by Spanner is clearly not provided by SQL.
Even if a DBMS only exposes read-only SQL support, that still has consistency-model implications—namely that, if you can execute several SQL statements in a transaction with a long delay between those statements, then all the statements in the transaction should execute against the same MVCC "version" of the affected tables.
So a write on that lower-level RPC interface shouldn't, by the SQL spec, be able to cause a sequence of SQL wire messages like this:
BEGIN
SELECT COUNT(*) FROM foo;
-- 100s later
SELECT COUNT(*) FROM foo;
COMMIT;
...to return separate numbers for those two SELECTs.
If it could, under Spanner, then Spanner wouldn't be conformant to [the read-only subset] of whatever SQL standard it claims to obey. But, according to the Cloud Spanner docs (https://cloud.google.com/spanner/docs/transactions#read-only...), the above code will always return the same values for the two queries.
And, if you're feeling argumentative, you could interpret the reason for the particular consistency semantics, as the fact that Cloud Spanner claims to support SQL, and SQL mandates that consistency. You could suppose that, without that claim, Cloud Spanner would never have bothered building a transactional MVCC layer into their architecture.
Unfortunately, no. Spanner may garbage collect the snapshot timestamp to which your example query was bound, if the garbage collection policy was triggered by additional writes at greater timestamps, or by the simple passage of time. The result of the second query may be a permanent error indicating that your data has disappeared.
Spanner had a transactional MVCC system in their original implementation described in the 2012 paper which predates SQL support described in the 2017 paper. Therefore it is probably wrong to credit SQL with Spanner's MVCC system.
> The result of the second query may be a permanent error indicating that your data has disappeared.
That's actually okay, per SQL. Any SQL transaction may fail, for any reason, at the server's discretion. Because the user's permission to operate on the table was revoked in the middle of their transaction, for example. Or because the server is going down and wants to drop connections.
SQL clients have to be written to assume that any transaction could enter a failed state "before" the execution of a statement, even if the previous statement returned a success.
Helpfully, though, they know that 1. by the SQL standard, that failure means that no part of the transaction applied; and 2. that this failure-state is a failure of the transaction context to be valid, rather than a failure of their statement to be valid. (They don't know that their statement is valid, but they know that they haven't been told that it isn't.)
I also will never understand why people have so much trouble decoupling SQL, the query language, from classic RDBMS systems. Being able to use a SQL-like dialect in systems like Hive, Spark SQL, and now even Elasticsearch makes those systems infinitely more useful.
Now if only I didn't have to keep looking up the pandas "Comparison with SQL" page every time I have to do some manipulations to a data frame...
Also some services really get held back by their lack of SQL support. We use splunk and the lack of SQL expression comprehension hurts (especially with their lack of `IN`)
As much as I hate NoSQL (which is considerable), I think it was destined to happen.
Sometimes the incumbent needs a kick in the ass. It feels like the pace of improvement in boring old databases has had an uptick for the last five or so years. That feeling of slowness is probably what opened the door in the first place. If you put enough beers in me I would have admitted as much at the time. Database progress got boring.
But the other thing is that people forget the past when it's been too long since the Dark Times. I have my fingers crossed, and perhaps I will regret that naivete some day, but I have my fingers crossed anyway that there's a whole generation of young developers that will know, viscerally, why we use Relational Databases. Why concurrent access to data is hard and we should leave it to the professionals instead of rolling our own every time we're feeling bored at work. The same way we generally let other people do billing, or data viz.
[Edit to add]
One of the biggest things I'm champing at the bit for an opportunity to use is the newer event streaming stuff that builds on exporting the WAL data from ACID databases. My OLTP data set gets to be the system of record as God and Man intended, but there's a perfectly sane mechanism to support the occasionally very valid OLAP-oriented business concerns. Any time a tool can take away something that makes my life difficult or unpredictable is a good tool.
I just haven't been at the right sized company that it's an unsolved problem that we need to solve, and I have higher priorities for my own stuff.
Slowness of progress in traditional databases has absolutely nothing to do with it. Primary reason NoSQL even exists is inability of traditional databases to run in distributed environments with decent availability, scalability, latency/performance and low maintenance requirements. It was never about SQL the language or relational model (which, by the way, is not inherently better than other models).
Nothing has changed since. As there exist fundamental limitations preventing all those traditional databases from ever satisfying requirements. And new distributed relational databases designed and built from the ground up so far don't seem to be able to meet neither SQL crowd's expectations nor win people who are facing actual distributed problems. It's like progress in databases doesn't favor familiarity over other things at all, effectively resetting familiarity with relational model altogether and forcing even models to compete with each other for fresh minds.
"According to the “shoot the messenger” principle, PostgreSQL has been heavily criticized for fsyncgate. Indeed, PostgreSQL suffers from this problem more than other databases as it doesn't offer direct IO."
Not a problem on any illumos based operating system, as fsync(2) and ZFS do what they're supposed to do and do not lie about data being on stable storage when it isn't. PostgreSQL is a good database, and if you want it to be bullet proof, run it on an illumos based operating system.
This comment is currently fading into downvotitude. Can someone explain why? Is it factually incorrect? Are people upset at the implication that other systems do lie about data being on stable storage when it isn’t?
I haven’t spent much time with Illumos, but if Postgres is not impacted on Illumos, then why the downvotes?
Ultimately the Operating System doesn't (and can't) know whether the storage is actually going to retain data you've written to it.
Even if your OS goes out of its way to use all six kinds of double secret synchronization primitive, the cheap eBay clone product didn't bother implementing any of them and replies "Yup, definitely 100% synchronised" while doing nothing, then the user is disappointed because your OS promised that the data was safe and it isn't.
This isn't really just about hard disks either.
Over the years lots of manufacturers have sold "smart" network cards that corrupt data if they're left to their own devices to "speed up" networking by cutting corners, miscalculating checksums, copying things wrongly from RAM, and so on. But it's even possible to build a card where the most trivial scenario - where the OS lays out the entire packet and then explicitly does IO operations to write it to the card - still sometimes randomly corrupts data. Your OS can promise a "guarantee" that the network data is exactly what you sent... and then you find 0.1% of packets are corrupted before they even reach the switch, oops.
Ultimately all bets are off when running on real hardware. Many years ago a friend of mine had a PC where randomly sometimes basic Unix features like "cat" would segfault or misbehave. Did disk checks, re-installed the entire OS, loads of hair-pulling. Eventually he discovered that the CPU fan failed, and overheating had damaged the on-die L2 cache...
To be fair, no OS has control over the hardware, only how it interacts with it. The cheap eBay clone and random PC fan failure are vanishingly rare for a datacenter database environment.
Still, the claim that merely selecting a an OS that may not suffer from this one particular issue results in a "bulletproof" setup is rather extraordinary. There may be other issues, some as a result of that OS choice (even if hardware issues are not).
Additionally, the choice of a much less popular OS can, in and of itself, be a significant "bullet"-susceptibility factor, not just in terms of general supportability but, getting back to your point about hardware, how well one might expect it to behave with what's out there on the market.
“Ultimately the Operating System doesn't (and can't) know whether the storage is actually going to retain data you've written to it.”
illumos and ZFS do know about it, especially since ZFS will checksum every block and will perform an explicit flush for every write. Should the drive lie about it, the checksum won’t match.
To make the claims you made just because what I wrote doesn’t involve Linux is preposterous. Sun engineers spent 25 years making the OS robust and able to correctly cope with failing, flaky or otherwise unreliable hardware. Ever heard of fault management architecuture in Solaris? If you had, you wouldn’t have written what you wrote.
They literally can't, as I explained previously. Everything seems fine with such hardware, up until you find that some of the data you were absolutely sure you'd written isn't there after all. The fact you can't conceive of how this goes wrong is actually _illustrative_ of how Sun got screwed. The foolish man certainly does build his house on sand.
Now, twenty five years ago when those Sun engineers apparently started making the OS robust (maybe this explains how it got the reputation for being a flaky piece of crap, they should have started sooner...) I was working for a company that I'm quite _sure_ a person who says they really care about robust systems has heard of. Tandem Computers.
Tandem had a demo that gets to the heart of the thing you think you really care about in Illumos. Do a transaction, it's committed, and then we... pull out the CPU cards from the computer. It's dead.
... and our transaction is safe and indeed other users can continue to use the system, with the committed transaction, because it was replicated to other nodes far away and those weren't destroyed.
Tandem Computers doesn't exist any more because it turns out that doing this correctly is expensive and nobody wants to pay extra for correctness. After all, Sun engineers said their OS is "able to cope with failing, flaky or otherwise unreliable hardware" and with the lower price tag a customer really wants to believe that's true, shame about their data when it isn't...
“The fact you can't conceive of how this goes wrong is actually _illustrative_ of how Sun got screwed.“
I can very well concieve of it, as I’ve been preaching this to the Linux crowd for decades and they just don’t get it. By the way I know about Tandem because yes, I’m that old.
Please, before you go preaching to the choir (me), read how ZFS works; if the drive totally completely lies about everything, if it silently corrupts data, first read from the drive will uncover it because the checksums won’t match. ZFS will still deliver good data if it has redundant copies and will attempt to overwrite the corrupt data automatically.
50% of the two memory chips in my Solaris storage server no longer function correctly; Solaris has automatically migrated the affected pages out of them. When I get new memory chips, I will off-line them and replace them. Tandem might have been even more robust, but its engineers didn’t continue working on it after the company’s demise like those Sun engineers did on illumos.
I would dispute the claim that PostgreSQL has been so heavily criticized. Instead, the project was invited to send a representative to the Linux Storage, Filesystem, and Memory-Management Summit (https://lwn.net/Articles/lsfmm2018/) to discuss solutions with the kernel development community; some of the resulting kernel changes are finding their way toward the mainline now.
What is there to discuss? 20 years into development and there is still no working fsync(2) which does what fsync(2) in a real UNIX has been reliably doing since 1985. One cannot build a robust infrastructure on an operating system where core functionality isn’t working reliably.
> The marketing term NoSQL, which was the hippest buzzword just a few years back, is slowly becoming a synonym for a defect. Without SQL, there is something missing.
I guess RDBMS companies are too afraid of NoSQL to attack it with such silly propagandist statements. You can't win the market this way.
To me it increasingly looks like SQL is losing in distributed systems. And NoSQL may never really gain SQL beyond niche usage after all.
To me it increasingly looks like SQL is losing in distributed systems.
I’m seeing the reverse trend. Google went from nosql to sql with cloud spanner. The various flavors of SQL on hadoop are on the uptick. Cockroachdb is looking really interesting.
They also exist as features within an (otherwise) relational DBMS. Perhaps the trouble is that it's mostly been in the realm of the commercial ones and only relatively recently has Postgres started adopting enough of them to make the notion popular.
From a developer’s point of view, I absolutely hate SQL. Converting my data from and to row format is a pain. Transactions don’t really tie well with the rest of my application logic. And of course embedding multiline SQL statements in my code looks like monstrosity.
On the database subject, I'm working on a new project called DBacked, basically a simple and encrypted database backups as a service. I'm not ready yet for a Show HN (more things to improve on the presentation website) but happy to get your advices ;)
Anyone know a really good SQL book or tutorial? Every book I've ever bought is usually way too simple. I'll flip to the "advanced" section thinking I'll finally learn some tricks only to see the "advance" section just introduces the join syntax...
I'd like a good book that goes over queries common in reporting, you know a 3 page sql query that joins to sub queries that themselves are joining to sub queries. You know, the good stuff!
Lots of receipts. The first are quite simple but the later, more advanced ones should take you way beyond simple joins and would also have variations for the most common databases.
From the table of contents:
...
Chapter 5 Metadata Queries
Listing Tables in a Schema
Listing a Table's Columns
Listing Indexed Columns for a Table
Listing Constraints on a Table
Listing Foreign Keys Without Corresponding Indexes
Using SQL to Generate SQL
Describing the Data Dictionary Views in an Oracle Database
Chapter 6 Working with Strings
Walking a String
Embedding Quotes Within String Literals
Counting the Occurrences of a Character in a String
Removing Unwanted Characters from a String
Separating Numeric and Character Data
Determining Whether a String Is Alphanumeric
Extracting Initials from a Name
Ordering by Parts of a String
Ordering by a Number in a String
Creating a Delimited List from Table Rows
Converting Delimited Data into a Multi-Valued IN-List
Alphabetizing a String
Identifying Strings That Can Be Treated as Numbers
Extracting the nth Delimited Substring
Parsing an IP Address
Chapter 7 Working with Numbers
Computing an Average
Finding the Min/Max Value in a Column
Summing the Values in a Column
Counting Rows in a Table
Counting Values in a Column
Generating a Running Total
Generating a Running Product
Calculating a Running Difference
Calculating a Mode
Calculating a Median
Determining the Percentage of a Total
Aggregating Nullable Columns
Computing Averages Without High and Low Values
Converting Alphanumeric Strings into Numbers
Changing Values in a Running Total
Chapter 8 Date Arithmetic
Adding and Subtracting Days, Months, and Years
Determining the Number of Days Between Two Dates
Determining the Number of Business Days Between Two Dates
Determining the Number of Months or Years Between Two Dates
Determining the Number of Seconds, Minutes, or Hours Between Two Dates
Counting the Occurrences of Weekdays in a Year
Determining the Date Difference Between the Current Record and the Next Record
Chapter 9 Date Manipulation
Determining if a Year Is a Leap Year
Determining the Number of Days in a Year
Extracting Units of Time from a Date
Determining the First and Last Day of a Month
Determining All Dates for a Particular Weekday Throughout a Year
Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
Creating a Calendar
Listing Quarter Start and End Dates for the Year
Determining Quarter Start and End Dates for a Given Quarter
Filling in Missing Dates
Searching on Specific Units of Time
Comparing Records Using Specific Parts of a Date
Identifying Overlapping Date Ranges
Chapter 10 Working with Ranges
Locating a Range of Consecutive Values
Finding Differences Between Rows in the Same Group or Partition
Locating the Beginning and End of a Range of Consecutive Values
Filling in Missing Values in a Range of Values
Generating Consecutive Numeric Values
Chapter 11 Advanced Searching
Paginating Through a Result Set
Skipping n Rows from a Table
Incorporating OR Logic when Using Outer Joins
Determining Which Rows Are Reciprocals
Selecting the Top n Records
Finding Records with the Highest and Lowest Values
Investigating Future Rows
Shifting Row Values
Ranking Results
Suppressing Duplicates
Finding Knight Values
Generating Simple Forecasts
Chapter 12 Reporting and Warehousing
Pivoting a Res...
Joe Celko's Puzzles and Answers is a nice complement to the Smarties book.
Neither of them cover analytic functions, though. And they can really make a difference (for performance and succinctness). So it's worth it to look into them after going through these books.
If you are looking for something advanced, you should check books written by C. J. Date. For example, his "An Introduction to Database Systems" considered as one of the best book on database theory.
However he insists on living in his relational purist fantasy land without nulls or outer joins or other pragmatic things that people who use databases to get real work done rely on every day.
I enjoyed "The Art of SQL". It's purposefully about higher-level strategies for relational DB management and administration, performance, tips in schema setup, SQL features to use/avoid...
"NoSQL" took the idea of a rejection of syntax (SQL) but also, in practice, burned in with it a rejection of vast swathes of database theory that resulted in the modern RDBMSes. It was a meme that was generally easy to ignore and identify the "bad ideas" within if you understood the history of databases. SQL obviously is just an incidental syntactic detail on top of a fairly sound, generalized model for databases -- relational algebra combined with a clear semantic model for transactions (ACID, etc.)
These two things I believe will stand the test of time. Everything else, I think, is ultimately incidental complexity to meet operational goals (like indexing, or caching methods, or sharding, or replication, or denormalization) or conceptually useful models and interfaces (like graphs, or literate APIs, or query langauges, or ORMs, etc) on top of these basic facilities that need to be provided by a database system.
The trend of NoSQL databases I think can be summarized as people building systems that tilted towards letting these bits of incidental complexity dictate the design of the system as a whole. The overriding concern seemed to be "scalability and performance" with "easy APIs" a close second and so that resulted in specific data structure and API needs or access patterns dictating everything about how the database system itself worked, and how data was modeled in that system.
For example, MongoDB was hearalded as finally letting you access a hierarchical data structure without SQL JOINs, but did so not by just providing a nice abstraction on top of a sound relational schema that allowed you to "re-project" your thinking and access patterns to a document-oriented one when it was the right model contextually, but instead by burning that denormalized, document-oriented data structure into the entire conceptual and technical stack for the whole system!
The cost, of course, is self-evident since choosing to reject the relational model also chooses to reject the benefits that were explicitly recognized as a reason for the relational model to be a good choice for database systems: normalized data allows a much more rich set of transformations and projections on that data to be unambiguously expressed and fulfilled by the system. In other words, it's much more future proofed since you now have the flexibility to mix-and-match and analyze it in arbitrary ways, decoupled from your choice of schema. So the effect of choosing MongoDB meant that you had minimal future-proofing if you got your document structure wrong and suddenly needed to re-project your data in a new way. The supported path, of course, was to "just run map reduce on it" -- ie, lets force every application developer to do the job the database is supposed to do :(
It's nice to see things are moving forward on this again, hopefully the trend doesn't just reverse now and we have a flood of "SQL access" APIs to poorly grounded database systems.
85 comments
[ 955 ms ] story [ 2854 ms ] threadThis seems focused on only SQL systems, and only the largest ones, which is okay, but I think there's a lot more to the story than that :)
I suspect you'll have a hard time competing against Google Spanner (or even Google Cloud Datastore).
Always wondered why this feature has been ignored for so long.
Read this paper if you'd like to learn more about it: http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeat...
Although I'm not sure the road back to sanity is to add SQL interfaces to non-relational database systems. SQL and the relational model are often conflated, both in the NoSQL hype and apparently also in the backlash. I like things like LINQ in .net which allows you to work with a relational model and use relational algebra without having to use the SQL language
But I still haven't found a better universal "language" to talk about extracting and manipulating data. The core set of functionality of SQL (selects, joins, aggregations) is usually adequate to express powerful ideas. Most importantly, a lot of people who work with data know it, and I've found semi-technical people (including non-programmers) can learn it quickly. Anyone who has been paying attention to trends in the data world in the last 5 years knows SQL is here to stay for the long-term.
I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685
> and isn't as powerful as I'd always want it to be
Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.
Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift.
>Do you have examples?
For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.
> > I'd take ISO/IEC 9075 as an official specification
> Fair enough, but I'd argue it's not a de facto standard...
It may be useful to understand the distinction between official (de jure) and common usage (de facto).
But relational operators isn't what developers like to work with. SQL is about "projecting" relations, putting them through some basic transforms, none of which compose that well because of SQL's statement-oriented syntax. For example, you can't group a group-by. A group-by takes a list of terms, but you can't funnel the output through another one without wrapping the whole query in a "SELECT FROM (...) AS ...".
I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.
https://code.kx.com/q/ref/qsql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/
With-clauses (aka Common Table Expressions) improves this a lot, but it would be better still if you could just write:
FROM <table> WHERE <filter> SELECT <projection> GROUP BY <groupings> WHERE <filter> SELECT <projection> and so on.
The difference is just the syntax, not the expressiveness.
I do not think this can be easily fixed in SQL because the problems are not in the syntax but rather lie deeper at the level of the underlying paradigm and principles.
Point is there are strong and weak consistency models with and without SQL execution engines, and they’re all useful.
Other generic DBMS query protocols could specify their own consistency semantics, but I'm not aware of any that do. (I guess, if you consider REST a "DBMS query protocol", then CouchDB is an example of a DBMS "conforming" to the consistency semantics specified in the REST protocol.)
So a write on that lower-level RPC interface shouldn't, by the SQL spec, be able to cause a sequence of SQL wire messages like this:
...to return separate numbers for those two SELECTs.If it could, under Spanner, then Spanner wouldn't be conformant to [the read-only subset] of whatever SQL standard it claims to obey. But, according to the Cloud Spanner docs (https://cloud.google.com/spanner/docs/transactions#read-only...), the above code will always return the same values for the two queries.
And, if you're feeling argumentative, you could interpret the reason for the particular consistency semantics, as the fact that Cloud Spanner claims to support SQL, and SQL mandates that consistency. You could suppose that, without that claim, Cloud Spanner would never have bothered building a transactional MVCC layer into their architecture.
Spanner had a transactional MVCC system in their original implementation described in the 2012 paper which predates SQL support described in the 2017 paper. Therefore it is probably wrong to credit SQL with Spanner's MVCC system.
That's actually okay, per SQL. Any SQL transaction may fail, for any reason, at the server's discretion. Because the user's permission to operate on the table was revoked in the middle of their transaction, for example. Or because the server is going down and wants to drop connections.
SQL clients have to be written to assume that any transaction could enter a failed state "before" the execution of a statement, even if the previous statement returned a success.
Helpfully, though, they know that 1. by the SQL standard, that failure means that no part of the transaction applied; and 2. that this failure-state is a failure of the transaction context to be valid, rather than a failure of their statement to be valid. (They don't know that their statement is valid, but they know that they haven't been told that it isn't.)
Now if only I didn't have to keep looking up the pandas "Comparison with SQL" page every time I have to do some manipulations to a data frame...
Doesn't change how much I love Elastic.
Sometimes the incumbent needs a kick in the ass. It feels like the pace of improvement in boring old databases has had an uptick for the last five or so years. That feeling of slowness is probably what opened the door in the first place. If you put enough beers in me I would have admitted as much at the time. Database progress got boring.
But the other thing is that people forget the past when it's been too long since the Dark Times. I have my fingers crossed, and perhaps I will regret that naivete some day, but I have my fingers crossed anyway that there's a whole generation of young developers that will know, viscerally, why we use Relational Databases. Why concurrent access to data is hard and we should leave it to the professionals instead of rolling our own every time we're feeling bored at work. The same way we generally let other people do billing, or data viz.
[Edit to add]
One of the biggest things I'm champing at the bit for an opportunity to use is the newer event streaming stuff that builds on exporting the WAL data from ACID databases. My OLTP data set gets to be the system of record as God and Man intended, but there's a perfectly sane mechanism to support the occasionally very valid OLAP-oriented business concerns. Any time a tool can take away something that makes my life difficult or unpredictable is a good tool.
I just haven't been at the right sized company that it's an unsolved problem that we need to solve, and I have higher priorities for my own stuff.
Nothing has changed since. As there exist fundamental limitations preventing all those traditional databases from ever satisfying requirements. And new distributed relational databases designed and built from the ground up so far don't seem to be able to meet neither SQL crowd's expectations nor win people who are facing actual distributed problems. It's like progress in databases doesn't favor familiarity over other things at all, effectively resetting familiarity with relational model altogether and forcing even models to compete with each other for fresh minds.
Not a problem on any illumos based operating system, as fsync(2) and ZFS do what they're supposed to do and do not lie about data being on stable storage when it isn't. PostgreSQL is a good database, and if you want it to be bullet proof, run it on an illumos based operating system.
I haven’t spent much time with Illumos, but if Postgres is not impacted on Illumos, then why the downvotes?
Even if your OS goes out of its way to use all six kinds of double secret synchronization primitive, the cheap eBay clone product didn't bother implementing any of them and replies "Yup, definitely 100% synchronised" while doing nothing, then the user is disappointed because your OS promised that the data was safe and it isn't.
This isn't really just about hard disks either.
Over the years lots of manufacturers have sold "smart" network cards that corrupt data if they're left to their own devices to "speed up" networking by cutting corners, miscalculating checksums, copying things wrongly from RAM, and so on. But it's even possible to build a card where the most trivial scenario - where the OS lays out the entire packet and then explicitly does IO operations to write it to the card - still sometimes randomly corrupts data. Your OS can promise a "guarantee" that the network data is exactly what you sent... and then you find 0.1% of packets are corrupted before they even reach the switch, oops.
Ultimately all bets are off when running on real hardware. Many years ago a friend of mine had a PC where randomly sometimes basic Unix features like "cat" would segfault or misbehave. Did disk checks, re-installed the entire OS, loads of hair-pulling. Eventually he discovered that the CPU fan failed, and overheating had damaged the on-die L2 cache...
Still, the claim that merely selecting a an OS that may not suffer from this one particular issue results in a "bulletproof" setup is rather extraordinary. There may be other issues, some as a result of that OS choice (even if hardware issues are not).
Additionally, the choice of a much less popular OS can, in and of itself, be a significant "bullet"-susceptibility factor, not just in terms of general supportability but, getting back to your point about hardware, how well one might expect it to behave with what's out there on the market.
illumos and ZFS do know about it, especially since ZFS will checksum every block and will perform an explicit flush for every write. Should the drive lie about it, the checksum won’t match.
To make the claims you made just because what I wrote doesn’t involve Linux is preposterous. Sun engineers spent 25 years making the OS robust and able to correctly cope with failing, flaky or otherwise unreliable hardware. Ever heard of fault management architecuture in Solaris? If you had, you wouldn’t have written what you wrote.
They literally can't, as I explained previously. Everything seems fine with such hardware, up until you find that some of the data you were absolutely sure you'd written isn't there after all. The fact you can't conceive of how this goes wrong is actually _illustrative_ of how Sun got screwed. The foolish man certainly does build his house on sand.
Now, twenty five years ago when those Sun engineers apparently started making the OS robust (maybe this explains how it got the reputation for being a flaky piece of crap, they should have started sooner...) I was working for a company that I'm quite _sure_ a person who says they really care about robust systems has heard of. Tandem Computers.
Tandem had a demo that gets to the heart of the thing you think you really care about in Illumos. Do a transaction, it's committed, and then we... pull out the CPU cards from the computer. It's dead.
... and our transaction is safe and indeed other users can continue to use the system, with the committed transaction, because it was replicated to other nodes far away and those weren't destroyed.
Tandem Computers doesn't exist any more because it turns out that doing this correctly is expensive and nobody wants to pay extra for correctness. After all, Sun engineers said their OS is "able to cope with failing, flaky or otherwise unreliable hardware" and with the lower price tag a customer really wants to believe that's true, shame about their data when it isn't...
I can very well concieve of it, as I’ve been preaching this to the Linux crowd for decades and they just don’t get it. By the way I know about Tandem because yes, I’m that old.
Please, before you go preaching to the choir (me), read how ZFS works; if the drive totally completely lies about everything, if it silently corrupts data, first read from the drive will uncover it because the checksums won’t match. ZFS will still deliver good data if it has redundant copies and will attempt to overwrite the corrupt data automatically.
50% of the two memory chips in my Solaris storage server no longer function correctly; Solaris has automatically migrated the affected pages out of them. When I get new memory chips, I will off-line them and replace them. Tandem might have been even more robust, but its engineers didn’t continue working on it after the company’s demise like those Sun engineers did on illumos.
working with the said technology and understanding it goes a long way.
I guess RDBMS companies are too afraid of NoSQL to attack it with such silly propagandist statements. You can't win the market this way.
To me it increasingly looks like SQL is losing in distributed systems. And NoSQL may never really gain SQL beyond niche usage after all.
I’m seeing the reverse trend. Google went from nosql to sql with cloud spanner. The various flavors of SQL on hadoop are on the uptick. Cockroachdb is looking really interesting.
People got all excited cause they discovered that these exist. They've been around for a long time.
Relational DBs are a good default, but there are plenty of cases in which you'll use another type of DB.
Engineers need to stop trying to find a silver bullet.
SQL and relational data is the only option, unless someone comes up with an equally powerful alternative with similar theoretical underpinnings.
I'd like a good book that goes over queries common in reporting, you know a 3 page sql query that joins to sub queries that themselves are joining to sub queries. You know, the good stuff!
https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.h...
I think that was the one I used to enjoy.
Lots of receipts. The first are quite simple but the later, more advanced ones should take you way beyond simple joins and would also have variations for the most common databases.
From the table of contents:
...
SQL Antipatterns
http://www.r-5.org/files/books/computers/languages/sql/style...
[0]https://www.safaribooksonline.com/library/view/joe-celkos-sq...
Neither of them cover analytic functions, though. And they can really make a difference (for performance and succinctness). So it's worth it to look into them after going through these books.
However he insists on living in his relational purist fantasy land without nulls or outer joins or other pragmatic things that people who use databases to get real work done rely on every day.
These two things I believe will stand the test of time. Everything else, I think, is ultimately incidental complexity to meet operational goals (like indexing, or caching methods, or sharding, or replication, or denormalization) or conceptually useful models and interfaces (like graphs, or literate APIs, or query langauges, or ORMs, etc) on top of these basic facilities that need to be provided by a database system.
The trend of NoSQL databases I think can be summarized as people building systems that tilted towards letting these bits of incidental complexity dictate the design of the system as a whole. The overriding concern seemed to be "scalability and performance" with "easy APIs" a close second and so that resulted in specific data structure and API needs or access patterns dictating everything about how the database system itself worked, and how data was modeled in that system.
For example, MongoDB was hearalded as finally letting you access a hierarchical data structure without SQL JOINs, but did so not by just providing a nice abstraction on top of a sound relational schema that allowed you to "re-project" your thinking and access patterns to a document-oriented one when it was the right model contextually, but instead by burning that denormalized, document-oriented data structure into the entire conceptual and technical stack for the whole system!
The cost, of course, is self-evident since choosing to reject the relational model also chooses to reject the benefits that were explicitly recognized as a reason for the relational model to be a good choice for database systems: normalized data allows a much more rich set of transformations and projections on that data to be unambiguously expressed and fulfilled by the system. In other words, it's much more future proofed since you now have the flexibility to mix-and-match and analyze it in arbitrary ways, decoupled from your choice of schema. So the effect of choosing MongoDB meant that you had minimal future-proofing if you got your document structure wrong and suddenly needed to re-project your data in a new way. The supported path, of course, was to "just run map reduce on it" -- ie, lets force every application developer to do the job the database is supposed to do :(
It's nice to see things are moving forward on this again, hopefully the trend doesn't just reverse now and we have a flood of "SQL access" APIs to poorly grounded database systems.