Big News In Databases — Summer 2018

Don’t fall behind! Here’s the most important database news from the last few months.

SQL’s Rise Continues

Google’s shift towards SQL was already mentioned in my previous newsletter. This time a new article by Google further forwards an argument that Google initiated years ago: in the original Spanner paper (2012) Google said that strong consistency—like that offered by SQL databases—causes fewer problems than the eventual consistency model that is often employed by NoSQL systems. In the F1 paper (2013), Google described eventual consistency as an “unacceptable burden to place on developers”. In their recent article, Google has even put that message into the tile: Why you should pick strong consistency, whenever possible.

Moreover Amazon is not afraid to point out a few advantages when it comes to using SQL and the relational model. They tweeted this from their latest AWS re:Invent conference: SQL on the relational schema is orders of magnitude faster than JSON processing.

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. It is no surprise that more and more systems seek to distance themselves from this term by introducing an SQL interface. Recently, a particularly well-established system opted for this path: Elasticsearch.

The adoption of SQL doesn’t stop there. Even traditional SQL databases continue to embrace SQL by introducing more modern SQL features into their dialects. Triggered by the release of MySQL 8.0, I’ve written a comprehensive article about this development.

Cloud First

Oracle has completed a version number jump from 12.2 to 18 and released their new database, initially to the Oracle Cloud and to Engineered Systems only. In stark contrast to semantic versioning, this release is only at the level of a new patch. In the previous version numbering scheme, the release number would have been 12.2.0.2, i.e. only the last digit would have been incremented. From a developer’s perspective, the release notes are thus not very exciting.

Still, not everyone is so in love with the cloud. SAP demonstrated a particularly noteworthy failure. Two years after introducing their cloud solution SAP Anywhere, they cancelled this service—apparently, they only ended up with 30 customers in those two years.

Technology and Science

Of course the technology news of the past few month has been dominated by the Spectre and Meltdown security vulnerabilities. Those vulnerabilities cast a shadow on cloud solutions as they often share hardware among several customers. The newly discovered type of security vulnerabilities can break the separation so that a malicious client could spy on other clients that use the same hardware. As these weaknesses exploit a hardware bug, they are not limited to databases.

The so-called “fsyncgate” problem only affects systems that use buffered IO to store data persistently—such as some databases. At its core, the problem is that errors can go unnoticed when they occur while writing changed data back from the operating system cache to the disk. Consequently, these errors cannot be handled by the database. The problem was originally reported by Craig Ringer of 2ndQuadrant—a PostgreSQL consulting firm. The actual root cause might be an omission in the POSIX specification of the fsync() function: it does not explicitly specify the subsequent behavior after an error has been reported. Application and operating system developers therefore did not have common expectations for this scenario. Matthew Wilcox, a Linux kernel hacker working for Microsoft (!), explained this problem from the perspective of the Linux kernel at the PostgreSQL developer conference PgCon… and he apologized for the trouble.

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. However, other databases—and more generally all software that uses buffered IO—can be affected by this issue. MySQL with InnoDB in the default configuration on many Unix-like systems is one example.

Otherwise recent news was pretty good for PostgreSQL: DB-Engines.com named PostgreSQL the database of the year 2017. PostgreSQL also ranked first—ex aequo with Firebird—in a Code Quality Comparison of Firebird, MySQL, and PostgreSQL. With four times as many demerit points, MySQL ranks second and thus last in this comparison.

On the academic side, there was a new paper from Google about indexing—still my passion since I wrote SQL Performance Explained. The authors of this paper have put a bold claim in the abstract: “by using neural nets we are able to outperform cache-optimized B-trees by up to 70% in speed while saving an order-of-magnitude in memory”. Of course, that is only true once the network is trained. The training itself takes an extraordinary amount of resources so this idea is only applicable to mostly static data sets. It might be an interesting concept for the Google search engine, but it’s not applicable to everyday indexing.

New Major Releases

There have been some impressive releases in recent months:

MySQL 8.0 (April 2018)

After an unbearably long 19 months since the first “development milestone release (DMR)” in September of 2016, MySQL 8.0 has finally achieved general availability in April 2018. As this release has far-reaching impacts that go well beyond the MySQL user base, I’ll refer you once again to my article “One Giant Leap For SQL: MySQL 8.0 Released”.

The game changing news is clear:

• Window functions

• The with clause (also recursive)

Finally, MySQL has evolved beyond SQL-92 and makes some modern SQL features available to a huge user basis.

After such a long period in development, there are obviously many more features. Mysqlserverteam.com provides a nice overview.

MariaDB 10.3 (May 2018)

The introduction of system versioned tables as described by the SQL standard is particularly noteworthy here. It’s one of the functions everybody would have had a need for in the past, and it always sparks great interest in my “modern SQL” presentations. MariaDB is the first widely used free open-source database to introduce this feature, and thus it’s putting some pressure on its competitors. I’ll publish a more detailed review of MariaDB 10.3 on modern-sql.com soon.

The full release notes are available on mariadb.com.

New and Updated Content on Websites

• My book “SQL Performance Explained” was translated into Spanish.

• All of the content on use-the-index-luke.com and modern-SQL.com was updated for MySQL 8.0.

• New article: NULL in SQL: Indicating the Absence of Data

• New article: NULL-Aware Comparison: is [not] distinct from

• New article: The Three-Valued Logic of SQL

• New slides: Standard SQL Features Where PostgreSQL Beats its Competitors (presented at: FOSDEM and PgConf.de).

• New article: One Giant Leap For SQL: MySQL 8.0 Released

• New article and slides: PostgreSQL Standard SQL Gap Analysis (presented at PgCon in Ottawa).

From Twitter, Keeping it Brief (follow me on Twitter)

• SQL Server is on Linux, but the Management Studio requires Windows. Now there is the “SQL Operations Studio” which also runs on Linux & Mac.

• mssql-cli: a new interactive command line tool for SQL Server (cross-platform)

• New on my website: a compilation of stuff I have for developers. https://winand.at/developers Websites, slides, events, books, geek-ware, training.

• 3 Reasons I Hate Booleans In Databases • by Jeff Potter (@jpotts18)

• If there is one tip I give junior developers, it is to truly understand these two concepts: - instructions (most do!) - expressions (many don’t)

• KISS should have a third S: keep it simple, stupid and secure. #pgconfde

• Quiz on SQL Window Functions — test your knowledge now! http://www.windowfunctions.com

• Fully agree with this quote from the slides: “When you need to write data back to the database, entities are very useful. For read-only data, you should always use projections!”

• Hmmm...T495, “Combined data change and retrieval” <data change delta table> ::= <result option> TABLE <left paren> <data change statement> <right paren> That one hasn’t crossed my path yet.

• “We all know by heart that PostgreSQL is better than MySQL—we just have to write the code that makes it true.” — @robertmhaas at @PGCon yesterday. (quoted from memory—probably not the exact words).

• Bullshit bingo time. Which of the following buzzwords will make it into my @PGCon talk “Standard SQL Gap Analysis” tomorrow?

• The solution to the Bullshit Bingo is: GDPR because standard system-versioning does not allow deleting historical data (right of erasure?)

• usql — A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and other SQL databases inspired by PostgreSQL’s psql.

• Downvoting “poorly phrased” questions on StackOverflow pretty much defeats the rationale of a QA site. If people could phrase their questions better, they might not need to ask in the first place.

• A Primer on MySQL Online Schema Change

• We’re excited to share learnings from a deep-diving training on #PostgreSQL, our go-to database, directly from SQL guru @MarkusWinand

• “How *not* to structure your database-backed web applications: a study of performance bugs in the wild” Yang et al., ICSE’18. A look into some of the performance issues that can creep into codebases when using ORMs...

SQL Renaissance Ambassador

As the SQL Renaissance Ambassador, it’s my mission to make developers aware of the evolution of SQL in the 21st century. My book “SQL Performance Explained” has been published in five languages and can be read online free of charge at use-the-index-luke.com. My next book is currently in the works and can already be read online as it’s being written (modern-sql.com). I am available as a trainer, speaker and consultant for all companies and developers interested in SQL. You’ll find more info at winand.at.