Better performance with MariaDB 10.5 and PostgreSQL 13

Two of the leading free software databases on the market had new releases this year, and we are happy to report they are now available on Platform.sh. Neither one is an earth-shattering upgrade, but both offer worthwhile performance improvements, especially on larger datasets.

MariaDB 10.5

MariaDB 10.5 has a huge number of changes, most of which are either bug fixes or performance related. Overall the new version should be faster and more stable than its predecessors, which is what you always want to hear about new software releases.

The biggest news is the addition of the ColumnStore engine. MariaDB, like its sister project MySQL, has long supported pluggable storage engines for different tables to allow developers to change what trade-offs they want for a given situation. The default—and most popular—engine, InnoDB, is well optimized for most standard SQL use cases, balancing read speed, write speed, and atomicity.

The new ColumnStore engine, by contrast, is optimized for highly complex reads on large datasets. The details of how it differs from InnoDB are a bit complex, but the short version is that it stores every column in the table separately. That makes writes and reads that span many columns slower, but aggregate reads on a few columns vastly faster. For example, if you have a column that contains the price for millions of sales, you can compute the average or median price far more efficiently with ColumnStore, and you likely won't even need a dedicated index, either. More complex statistical analysis queries would see an even bigger performance boost.

On the flipside, a high-write task like logging would be a bad idea with ColumnStore. Please don't do that. If you want to do analytics on high-transaction-count data, snapshot it into a ColumnStore table (an offline task that doesn't have to be particularly fast) and then run your data analytics on that snapshot.

The DB Sys Upgrade blog has much deeper technical details if you're interested.

Upgrading MariaDB

Upgrading to 10.5 is a snap, as always. First, create a new branch:

platform branch update-mariadb

Then edit your .platform/services.yaml file to change the MariaDB version to use:

db:
  type: mariadb:10.5
  disk: 2048

Commit that change to Git and push. Once you're happy with the result and have verified there are no minor issues with the upgrade (and you've taken a backup), merge the update-mariadb branch to master, push, and your production site will be rebuilt with the new DB version.

PostgreSQL 13

The latest release of PostgreSQL is also all about performance. There aren't many changes that should affect day to day usage, and there isn't any significant new syntax. What is there is a lot of engine improvements around performance, disk usage, and security. We'll call out two in particular.

The first is incremental sorting. When sorting by two columns, especially indexed columns, PostgreSQL can now segment its sorts. That is, there's no point in sorting the entire second column. Now it first sorts by the first column, and then only if there are duplicates in the first column it sorts just those rows by the second column. Not only does that mean the second column will often not need to be sorted at all, but even if it does the dataset to be sorted will almost certainly fit entirely in memory, making it substantially faster. There is no new syntax for this improvement; some sort queries will just get faster.

The second feature to call out is B-tree index deduplication. Again, that sounds a bit esoteric, and for most developers it is. It's largely an internal change to how indexes are structured that makes them more efficient when indexing columns that have duplicate data. PostgreSQL is now smarter about structuring the index to avoid duplication, which saves on disk space and lookup time.

Upgrading PostgreSQL

Upgrading between PostgreSQL versions is often a bit trickier than for MariaDB, but Platform.sh runs the update routine for you automatically as long as you're upgrading from PostgreSQL 12. (If not, upgrade one release at a time to the latest version, in stages.)

In a branch, update your .platform/services.yaml file to specify the new version.

db:
  type: postgresql:13
  disk: 2048

Test in that branch to make sure the upgrade went smoothly. When you're satisfied with it, click the "Backup" button on your master branch to be extra safe and merge your update branch. Congratulations, you're now on the latest PostgreSQL.

As always, Platform.sh aims to take the ops out of your devops and let you focus on what matters most: your application's business goals.