Big News In Databases — Fall 2017

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

SQL on The Rise

NoSQL pioneer Google writes that their Spanner database is becoming a SQL system (summary). Salesforce uses Artificial Intelligence to translate natural language to SQL. Even some ORM0 vendors say it is better to use SQL. Seems like SQL has a positive vibe.

The highly popular article “Why SQL is beating NoSQL, and what this means for the future of data” is a very nice write-up of why SQL had become frowned upon, and how it is now gaining new popularity right.

The Cloud War Continues

In the previous edition, I reported about the sudden spike in license costs for the Oracle database in Amazon’s AWS and Microsoft’s Azure cloud environments.

In July the opposite happened for users of Microsoft SQL Server: the cost of running the Standard Edition in Amazon’s AWS cloud was reduced by between 29 and 52 percent.

IBM’s Renaming Insanity

There is a saying that there are only two hard things in computer science: (0) cache invalidation, (1) naming things, (2) and off-by-one errors. In June, IBM renamed the products in the Db2 family and thereby demonstrated how to cause great harm by choosing poor names.

Note the subtle but groundbreaking innovation to write the “b” in Db2 as a lower case letter.

All sarcasm aside: Previously, DB2 was a common element in the names of different products of the same family. The “for” addendum made a distinction between each product. As these products offer different features,1 the distinction is quite important.

The new name Db2 doesn’t allow this distinction anymore because it represents the whole family as well as one specific product. This lack of differentiation becomes a real problem when searching the internet: webpages about the former product DB2 for LUW might not contain “LUW” anymore. Reducing the iSeries addendum to i doesn’t improve searching either.

Wikipedia says “A name is a term used for identification.” I doubt the new names fulfill this purpose sufficiently.

One thing is for sure: the new naming is an upgrade for the LUW version. The reverse of this conclusion shines an interesting light on the other variants. IBM also looses grounds in the just published Gartner Magic Quadrant for Operational Databases 2017—it’s now far behind SAP and Amazon Web Services (AWS).

Other Vendors Rethink Release Numbering

I have already mentioned that the next MySQL major release after 5.7 will be MySQL 8.0. In the meantime, a release candidate is available.

The next major release of the Oracle Database will be 18c (instead of 12.2.0.2). New releases will be annual and the version will be the last two digits of the release year (see also: release roadmap).

Starting with the just released PostgreSQL 10, there is no dot in the major versions of PostgreSQL anymore.

New Database Releases

In the past six month there were three major releases among the most popular SQL databases.

SQL Server 2017 (October 2017)

My personal picks from the “new features” list:

• It’s available on Linux. Free for developing, including Docker images. Licenses are “platform agnostic, so customers can run the software on either Windows or Linux.”

• Batch mode adaptive joins: decide to use nested loops or hash join during execution (like Oracle’s adaptive joins, but more limited2).

• Graph processing capabilities: a match clause that reminds one of the Cypher Query Language.

• The string_agg function: similar to the standard function listagg, but without on overflow clause.

PostgreSQL 10 (October 2017)

My favorite new features:

• Parallel query improvements: Merge Join, Bitmap Heap Scan, Index [Only] Scan, and more.

• Multi-column optimizer statistics, so the optimizer understands cross column correlations.

• ICU collations: operating system independent rules for comparing character strings.

MariaDB 10.2 (May 2017)

MariaDB3 10.2 introduces two very important features that will also appear in MySQL 8.0:

• Window functions: the over clause.

• The with clause: both, recursive and non-recursive variants.

News on my Sites

Articles I published or updated:

• I updated the PDF edition of “SQL Performance Explained” (all languages). If you bought it in the past, now is a good time to re-download it. (If not, you can buy it here ;)

• I’ve updated the 3 minute SQL indexing quiz: Can you spot the five most common SQL indexing mistakes?

• New article: extract — Get a Field from a Date or Time Value

• New article: What’s New in SQL:2016

From Twitter, in Great Brevity (follow me on Twitter)

• The next Oracle Database Express Edition will be 18c XE. Limits: 12GB storage, 2GB RAM

  For comparison, IBM Db2 Express-C has no storage limit, 16GB RAM, and 2 cores.

  Addendum for the trolls: Yes, the free open source databases such as PostgreSQL and MySQL have no limits for the same price.

• What do SQL statements really mean, and how can we prove RDBMS query rewrites are safe? Until now, we couldn’t…

• 10 Cool SQL Optimisations That do not Depend on the Cost Model

• TIL: Although SQLite’s default collation is case-sensitive, it doesn’t affect LIKE. LIKE’s case-sensitivity is controlled by a PRAGMA instead

• SQL Island — text adventure game to learn SQL

• Explaining isolation levels based on the states that are observable by a client application

• How Postgres Makes Transactions Atomic

• “ACIDRain: concurrency-related attacks on database-backed web applications” Warszawski & Bailis ’17

• Practical Guide to SQL Transaction Isolation … a really good one.