Microsoft’s cloud comes with many different databases at as many different price points. You can go the open-source route with MySQL or MariaDB, use Microsoft’s own SQL Server in several different guises or build a NoSQL document database using Cosmos DB. However, one option spans many different use cases, and as it’s built on one of the most popular open source databases, is both efficient and capable.
SEE: Feature comparison: Time tracking software and systems (TechRepublic Premium)
PostgreSQL is a scalable, extensible SQL database, that uses familiar queries and interfaces to give you a database that’s able to handle most necessary operations. Microsoft’s acquisition of Citus Data made it a significant contributor to the project, and Citus continues to develop its open-source extension which adds scalable queries and distributed tables. Its scalability means Microsoft can offer three different classes of Azure-hosted PostgreSQL: Single Server, Flexible Server and Hyperscale.
PostgreSQL in Azure
Single Server is the familiar PostgreSQL found in most Linux distributions, while Hyperscale builds on Citus’ tooling to give you a global SQL database. However, it’s the Flexible Server option that’s perhaps the most interesting, as it gives you a serverless, scalable database that can add compute capability as needed and even drop down to zero when not needed. While it builds on the same foundation as the Hyperscale release, it’s a single Linux virtual machine running on its own server, with compute and storage in the same Azure availability zone.
The result is a fast and low latency server, with no need for additional gateways and with the database and logs stored across zones to increase reliability. Flexible Server uses a premium disk option too, so you get the IOPs your applications need. As it’s a single-tenant system you’re not sharing resources with other users, again reducing risk, and helping keep your data as secure as possible.
Microsoft describes the resulting service as a “developer-friendly managed Postgres service,” reducing the operations overhead and letting you get straight to building on and around your data. Simplifications include reducing the complexity of the setup process, reducing the fields you need to fill in and providing a simple setup process using the Azure CLI so you can automate building databases, allowing you to make it part of an Azure DevOps deployment pipeline.
Setting up Azure Database for PostgreSQL Flexible Server
There are plenty of options that let you tune an instance for your application. Start by choosing a pricing tier: Burstable, General Purpose and Memory Optimized. These allow you to tune the compute, memory and storage options. Once you’ve created a server you can add multiple databases, you’re not limited to a single database per instance.
If you use the Azure CLI tools to create an instance, the defaults set the instance to General Purpose running on an 8GB RAM 2 core virtual server with a private network connection. All you need is a name for the server and a resource group where it can be assigned.
Getting the right storage can be important. While it’s in the same availability group as your instance to keep latency to a minimum, you will need to choose storage types that support your application’s IO needs. Performance is limited by your VM type, so you may need to fine tune your choice of VM and storage to get a database that’s matched to your code.
The CLI tool has more options, allowing you to choose VM types, pricing tier, storage and more. For example, if you want a public access connection, you’ll need to explicitly declare it as part of the setup, as once defined it can’t be changed. It’s a good idea to have another virtual machine in the same network running Postgres’ psql command line tools so you can manage and test your database without needing access from outside Azure.
Understanding burstable instances
One important feature is the ability to use Flexible instances for burstable computes. These are instances built on B-series Azure VMs. They start with 1 or 2 cores, and 2 or 4GB of memory. How burstable VMs work is that they run at a relatively low power for most of the time, building up credits that can be used to boost performance when needed. For a B1 series VM, they have a 10% baseline that boosts to 100%, and for a B2 series VM they baseline at 40% and boost to 200%. This approach can save money, as you’re not paying for performance when you don’t need it.
Many databases have workloads that fit well with Azure’s burstable pattern, running at low CPU for most of the time and adding power at peak times. For example, you might have a database that stores time sheets for a mid-sized IT consultancy. Most of the time it will be offering reads to the HR and payroll team, but every week, it’ll need to burst to support writes from everyone updating their weekly reports. Microsoft provides an API to keep track of available credits, so you know how many are available and how your application consumes them when it runs in burst mode
SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)
If your application is even more time constrained, you can use a higher performance VM, while taking advantage of the stop/start capabilities in Flexible servers. With the database VM stopped, all you’re paying for is storage, and restarting an image doesn’t take very long, so you won’t be left waiting for a system to relaunch when your application is needed.
Why should you use Flexible Server for PostgreSQL in Azure?
A managed database like this is a useful tool for developers who want a reliable, fast database without the management overhead that comes from rolling out your own servers. Microsoft automates PostgreSQL updates with the option of controlling when those updates are applied. It will manage backups for you with up to 35 days of retention. It’s also simple to configure a reliable failover architecture, so you can keep services up even if you lose your primary Azure PostgreSQL instance.
Microsoft has said that Flexible Server isn’t a replacement for its Single Server PostgreSQL offering, but it’s not hard to see that it’s the long-term plan to make it the default choice for all PostgreSQL applications running in Azure. For one thing, Single Server only supports PostgreSQL 10 and 11, while Flexible Server supports 11, 12, and 13. If you want the latest features, you’ll only get them with Flexible Server.
With three different configurations and a lot of flexibility in how they’re deployed, Microsoft has made it very easy to add a managed Azure PostgreSQL database to your Azure infrastructure and to your applications. Using Azure Database for PostgreSQL Flexible Server, you should be able to manage an application from a small burstable development instance all the way to a memory optimized service running in-production applications.