Big News In Databases — Summer 2019

Twice a year I review the news articles from the past six months and report on the most interesting ones…it’s that time again! Subscribe the newsletter to receive future issues.

SQL is Growing

In previous issues, I repeatedly addressed the growing adoption of SQL among traditional databases such as MySQL and non-relational systems like Elasticsearch. In this issue, I focus on the growth of the SQL standard itself by shining a spotlight on three vendors of proprietary query languages who want to integrate their language concepts into the SQL standard.

Rasdaman and Multidimensional Arrays (SQL/MDA)

The first representative of the group has already made the grade: A new part of the SQL standard was just published in June: ISO/IEC 9075-15:2019 Multi dimensional arrays (SQL/MDA).

This functionality allows storing, accessing and processing massive multidimensional arrays such as n-channel satellite images. That means that SQL can now decode images and directly access and process image areas via pixel coordinates. You’ll find all the details in this Technical Report.

The driving force behind this extension was Peter Baumann, who initiated and co-edited the adoption of concepts from the Rasdaman-Database for SQL.

Neo4j, Graph Query Language (GQL) and SQL/PGQ

The next push for standardization—significantly driven by Neo4j—has just reached a critical milestone: The ballot for the creation of a new standard, GQL, is ongoing. If it passes, the normative documents that define a new database language will be written over the next years.

So GQL is not an SQL extension, rather it is a new language specifically designed to process graph structures. There are some cross references between SQL and GQL nevertheless. In the one way, the GQL will “inherit” some properties of SQL by referring to the SQL standard. In another way, SQL will get a new part 16 (SQL/PGQ) to make some GQL functionality directly available in SQL—for that, rumors mention the year 2020.

Apache and Streaming Data

The topic of streaming data is currently at the very beginning of the standardization process. A driving force is Apache, which offers several streaming platforms for which SQL-like languages are available (KSQL, Spark, Flink, etc.). A proposal published end of May (summary) claims that SQL can be very effective for processing streaming data with the addition of a few minor non-invasive extensions. In this way, the authors are initiating the standardization process.

Cloud Wars: Strange Allies Working Against Your Own Hardware

Starting with release 12.2 of the Oracle Database, there is an interesting pattern: New add-on features (options and management packs) are only available in the Oracle cloud and on engineered systems—i.e. only on Oracle hardware. If you want to run the Oracle Database on your own hardware, you cannot use these new add-on features, which simplify the management of large installations. This limitation affects end users who want to use their own hardware as well as third-party cloud providers like Microsoft and Amazon.

A recently announced alliance between Microsoft and Oracle has to be looked at in this context. The two cloud providers established a direct network link between two of their data centers and also integrated the identity and access management between them. The use cases envisioned in the press release always deploy the Oracle Database in the Oracle cloud, but the application software on Microsoft Azure. Apparently, the only reason to use the Oracle cloud is the Oracle Database—and that maybe only due to the limited availability of advanced management features on non-Oracle hardware. When it comes to running the application software, Oracle surrenders.

Technology and Science

In addition to the above mentioned “gentle pressure” to get clients into the cloud, a cloud deployment of course has some intrinsic benefits too. Besides the usage-based cost, it really comes down to just how simple it is to manage. In a cloud environment, it only takes a few clicks to install a database with redundancy across data centers.

However, this simplicity in terms of management introduces a new risk: bugs in the software that runs the cloud. A recent paper (summary) analyzed critical incidents in the Azure cloud during a six month period in 2018. One finding: 40% of these incidents were caused by bugs in the cloud software itself. One example of such an incident made it into the news in January 2019: An automatic process has inadvertently dropped client databases.

Another paper worth reading (summary) provides a current overview of the CALM Theorem. This theorem is about avoiding the coordination effort in distributed systems in order to improve performance.

Finally, a paper about efficient sliding window implementations in streaming context: Optimal and General Out-of-Order Sliding-Window Aggregation.

New Releases

Oracle Database 19c (February, April, second half of the year)

The successive release of the Oracle Database to various environments has become a bit of a tradition. The order in which 19c was released is a surprise nevertheless. The first release happened in February to LiveSQL—Oracle’s online SQL portal. The next release was the download version for Linux in April. In the Oracle cloud, release 19c is only available for Exadata—the other cloud offerings are announced for the second half of 2019.

Feature-wise, 19c was “focused on fixing known issues, rather than adding new functionality”.

See also: Oracle Database Release 19c New Features

MariaDB 10.4 (June)

MariaDB 10.4 delivers the first features for application versioning. Unfortunately, those features alone are pretty much useless if you want to reasonably implement application time versioned tables. The missing key feature—WITHOUT OVERLAPS—is currently planned for 10.5.

See also: Changes and Improvements in MariaDB 10.4

IBM Db2 11.5 (LUW) (June?)

Although the previous version of Db2 (11.1) was released in 2016, IBM has been regularly improving that version with mod packs. The last mod pack was released in December 2018 and added the JSON_TABLE function, for example. In June 2019 IBM released version 11.5, which is focused on AI—at least when you listen to IBM marketing. If you read the What’s new document, you won’t find a single mention of AI. You can draw your own conclusions.

See also: What’s New for Db2 Version 11.5 GA

MySQL 8.0.12-16 (July 2018 till April 2019)

We have been getting quarterly MySQL releases since last year, which also add new features. Notable SQL extensions since the first 8.0 GA release (8.0.11) are LATERAL and CHECK constraints.

See also: MySQL 8.0 Release Notes

A detailed analysis of the new SQL functionality of these releases will be published soon on modern-sql.com (Twitter, Email, RSS).

New Articles, Slides and Recordings

• New recording (15 min): The Mother of all Query Languages: SQL in Modern Times (YouTube, 15min)

• New recording (50 min): The Mother of all Query Languages: SQL in the 21st Century (YouTube, 15min)

• New article: SQLite in 2018: A state of the art SQL dialect

• New article: What’s new in PostgreSQL 11

• New article: A Close Look at the Index Include Clause

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

• @PostgreSQL is the @DBEngines database of the year for the second year in a row. https://db-engines.com/en/blog_post/79 #PostgreSQL

• SQL is No Excuse to Avoid DevOps Automation and a little discipline allow better testing, shorter release cycles, and reduced business risk. by @yesthattom

• Open source RDBMS uses spurred by lower costs, cloud options

• I am a fan of @MarkusWinand and also appreciated this video, so I hope he doesn’t interpret my shocking take (below) on some of the ideas he presents as a personal criticism.

• MySQL client allows MySQL server to request any local file by default

• Microsoft bought Citus Data. Credativ bought OmniIT. It’s PostgreSQL shopping time.

• Good news everybody! The Use The Index, Luke! mug is on stock again … More beautiful than ever.

• Read our speaker interview with Markus Winand (@MarkusWinand)! #pgDayParis #SpeakerInterview

• SQL has been one of the most valuable skills I’ve picked up in my career, here are a few thoughts on why the return on investment of learning it is well worthwhile:

• Firebird 4 is under way. Some picks from the release notes • FILTER • Window functions: - ROWS and RANGE frames - WINDOW clause - RECENT_RANK, CUME_DIST, NTILE • DECFLOAT

• Speaker interview - @MarkusWinand at @VoxxedBucharest 2019

• I’ve recently read the excellent book: SQL Performance Explained from @MarkusWinand and written a review about this must-read book for developers who want to understand SQL performance (once and for all):

• Two participants in a training... A: I remember that from when I read the book.... ... unfortunately, I read the book in a scattered fashion... ... I only read it when I was waiting for a query to finish. B: So you did actually read it in one go!

• This years update of “SQL Performance Explained” is live. If you ever bough the PDF version, just go here, enter your e-mail to get the update. https://sql-performance-explained.com/#request-download … This URL works for all languages. This years changes are about the INCLUDE clause (due to PostgreSQL 11).

• Using the right indexes helped me to speed up a stored procedure. It used to run 35-50 mins. Now it needs 3-5. I don’t think I could’ve done it if I haven’t read https://use-the-index-luke.com/ already. This is one of the best #SQL resources I’ve read.

• Compared to last year, PostgreSQL has overtaken SQL Server and SQLite has overtaken MongoDB [in the StackOverflow Developer Survay 2019]

• Washing my hands. I just used a self-join for a client. No alternative was appropriate. I stand my statement: 95% of all self-joins are wrong (as in “unnecessary”) 50% chances that one is wrong too ;)

• The impact of my LAST_EXPLAINED view in the Db2 community is really funny. I wrote the original view in one afternoon because I had to give a training on Db2 the next day—I was really p•ss•d by the tools IBM provides.

• Unless you are writing a trivial application, it’s unlikely that you can use #Hibernate without native SQL. #Hibernate is an alternative to the JDBC API, not to SQL. Unfortunately, many @Java developers have this misconception.

• PostgreSQL 12 beta1 released. My picks: • WITH is no longer an optimizer fence ;) • SQL/JSON PATH langauge • Geranted columns • Case insensitive ICU collations

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.