Compose Notes for PostgreSQL 9.5


PostgreSQL 9.5 is now on the Compose platform and we've got some notes about the new version of the database covering autoscaling and versions.

Updating the PostgreSQL Autoscaling Algorithm

With the arrival of PostgreSQL 9.5, we're updating how we dedicate system resources to PostgreSQL deployments. It's a change that comes from our experience with PostgreSQL 9.4. The change itself is in how we count how much storage your deployments are using. Although it only affects a small number of users, we like to be transparent about these things. To understand what's changed, you'll need to know a little about the inner workings of your PostgreSQL deployment.

All Compose PostgreSQL deployments are based around a highly available cluster which uses the Write-Ahead-Log (WAL) archive from the primary database to keep the secondary up to date. The WAL gets added to when every a write operation occurs in the primary database and we take that log and archive it.

The WAL archive holds around the last 24 hours of writes to the database in total but, also, gets backed up with the database. This enables users to do a point in time restoration using two backups, a process we'll go into in a future Compose article.

With PostgreSQL 9.4, we had not included the space used by the WAL archive in the calculation of how much storage a deployment was using, and in turn how much you'd be billed. For most deployments this was fine as the write load was not so high. Some users though do a lot of writing to their database, and we mean a lot, which means WAL archives of tremendous size and typically those users were actually updating a relatively small data set and getting asked to pay far less relative to what their database actually consumed.

For example, we've seen 100GB of WAL log files generated in an hour on one of our multi-tenant environments for a user with a 60GB database. That customer obviously needed a lot more resources than 6GB of RAM and it wasn't being handled by our autoscaling algorithms.

With PostgreSQL 9.5, we've decided to include the space used by the WAL archive so that we reflect the actual space used by a PostgreSQL deployment. Most users won't notice the difference but, as we said, we thought you should know about this change. We feel the change will better match all the PostgreSQL use cases, from the warehouses to the web applications, by matching them with the proper amount of system resources for best performance.

Why 9.5.2?

We were actually ready to bring PostgreSQL 9.5.1 to customers when we found out about the abbreviated indexes problem. We have been testing a number of enhancements in the reliability of our high availability support. Because the bug in 9.5 and 9.5.1 meant users might have to reindex their databases, we decided to go ahead with deploying PostgreSQL 9.5.2 to ensure no users were exposed to the problem.

Dj Walker-Morgan
Dj Walker-Morgan is Compose's resident Content Curator, and has been both a developer and writer since Apples came in II flavors and Commodores had Pets. Love this article? Head over to Dj Walker-Morgan’s author page to keep reading.

Conquer the Data Layer

Spend your time developing apps, not managing databases.