A Simplified Introduction to Azure Database for PostgreSQL Flexible Server

paul.randal over 1 year ago 9 minute read time

I find it amazing how much opportunity and flexibility cloud environments are creating for organizations of all sizes. I’m seeing more and more companies experimenting with open-source software (OSS) relational database systems, which years ago would’ve been too complicated for the customer to set up.

With Azure, you can spin up OSS systems like MySQL or PostgreSQL quickly to determine if the engine fits their needs. If it does, you can continue development on it. Otherwise, you can test other database engines quickly and efficiently through the Azure infrastructure. In my opinion, it’s one of the most exciting advancements to have happened to computing in my time.

Recently, I helped a customer who needed some performance tuning help. They had an on-premises PostgreSQL environment and were hitting constraints on their allocated hardware for their production environment. In addition, they were stressed on in-house resources for administering not only their PostgreSQL database but patching and maintaining their Linux operating system that hosted the PostgreSQL server. After careful analysis of their production environment, I determined a managed platform as a service offering like Azure Database for PostgreSQL Flexible Server would be great for them. The migration to this platform solved almost all the customer’s immediate problems, which is an amazing thing to be part of.

In this guide, I’ll dig deeper into what Azure PostgreSQL Flexible Server is, provide key differences between Flexible Server and Single Server, show you how to create a Flexible Server, and discuss when you may benefit the most from using PostgreSQL Flexible Server.

What is Azure Database for PostgreSQL Flexible Server?

Flexible Server is one of three deployment options for Azure PostgreSQL provided by Microsoft:

Single Server
Flexible Server
Hyperscale

Flexible Server is designed to provide more granular control of the PostgreSQL database engine configuration and some useful features not present in Single Server.

Single Server vs. Flexible Server

Single Server was the original offering and was immensely popular when first released. This database allowed new developers to uncouple themselves from traditional high-end relational database systems like Oracle and SQL Server and focus more on simple data storage needs for their applications. However, as demand increased, it became more and more apparent an offering needed to be available to curtail some of the inherent drawbacks of Single Server. From there, Azure Database for PostgreSQL Flexible Server was created.

It’s clear Single Server will eventually be discontinued in lieu of Azure Database for PostgreSQL Flexible Server due to the present limitations of the Single Server product. One big issue is connections coming to Single Server are established through a gateway designed to take care of the routing to the physical location of the database server. Because this gateway service is additional work in establishing the connection to the database service, it can slow down some connections. However, the biggest limitation I see for the Single Server service is the fact you can’t co-locate your Azure application tier to be the same physical location (availability zone) as the database tier. Ideally, your application and your database would be in the same data center to ensure the smallest possible latency for communication between the two.

Flexible Server Pros and Cons

One main benefit of choosing Flexible Server is the fact you can co-locate your virtual machines running your applications with the PostgreSQL database engine. This ability is probably the single most attractive feature of using Flexible Server, as it helps you avoid communication latency.

Another cool feature of Flexible Server is pgBouncer is built into the service. pgBouncer is a connection pooling service specific to PostgreSQL designed to provide a lightweight mechanism for the PostgreSQL engine to maintain a pool of connections for reuse. PostgreSQL is notorious for how expensive connections are to create resource-wise and using pgBouncer (or some other connection pooler) is critical for any application that establishes and maintains many connections to the database.

Flexible Server also has a configurable maintenance window. It applies regular patching and periodic maintenance for the PostgreSQL database, which you can configure to suit your application schedule. This lets the administrator choose a specific date and time window to apply these updates, generally during a time of low traffic to the database. This ensures minimal disruption to your applications when the maintenance occurs.

One of the main drawbacks I still see with Flexible Server is the storage size is restricted to 16TB. Though most applications aren’t going to approach this large size limit, there are some applications—especially those applications looking to migrate from Oracle to PostgreSQL—where the 16TB limit is a showstopper. This is where the final tier of PostgreSQL comes into play: Hyperscale.

Creating a PostgreSQL Flexible Server

Let’s create an Azure Database for PostgreSQL Flexible Server via the Azure portal. Once at the portal, I click on the “Create a resource” option, search for “Azure Database for PostgreSQL,” and click the “Create” button.

The next page is where I’ll choose a deployment option. I’m looking at Azure Database for PostgreSQL Flexible Server, so I’ll click the “Create” button on the “Flexible server” option.

Next, I’ll need to enter some information to configure my PostgreSQL server. I’ll first need to create a resource group inside of my Azure subscription. A resource group is a logical container in Azure where resources are contained. I’ll also need to give this PostgreSQL server a name, a region for where the server will be located, and the version of PostgreSQL I want installed on this server.

I’ll need to assign resources to this server based on my expected overall throughput. Here, I’ll choose the small/medium-sized prodution tier as the workload type. Under “Compute + storage,” I’ll click “Configure Server” so I can assign the necessary number of virutal cores, memory, and storage.

The tiers are differentiated by the amount of compute in vCores capable of being provisioned, memory per vCore, the storage technology used to store the data, and—of course—pricing. The first choice I’ll need to make when configuring compute and storage is which compute tier to use. The burstable tier is great for applications not constantly needing CPU. These are generally smaller applications or those only needed for certain times throughout the day. The general-purpose tier is applicable to most workloads needing dedicated CPU throughout the day and balanced compute and memory with scalable IO throughput. The memory optimized tier is for high-performance applications reuqiring a lot of memory, fast disks, and high concurrency needs, and high-end transaction processing production applications should be assigned to this tier.

As I mentioned before, one great feature of Azure Database for PostgreSQL Flexible Server is the ability to co-locate your applications and your PostgreSQL database. The way this is done is via availability zone placement. An availability zone is a physical location within an Azure region, which you can think of logically as a specific Azure data center. When your database and application are in the same physical Azure location, you can take advantage of the proximity between the two to minimize latency.

When high availability is configured, Flexible Server automatically provisions and manages a standby replica. “Same zone” places the primary and the standby server in the same availability zone. This option has low network latency and a service-level agreement (SLA) uptime of 99.95%. There’s no need to configure application redundancy with this option, as the application, primary, and standby server are all in the same Azure availability zone.

The “Zone redundant” option provides redundancy across availability zones within an Azure region. This option requires a bit more configuration, as you’ll need to make sure your application is also redundant across zones to ensure it’s highly available. This option gives you a guaranteed uptime of 99.99% vs. the 99.95% option the “Same zone” configuration gives you.

The last information I’ll need to enter on the initial configuration page is the admin username and password. Make sure to keep track of this information—you’ll need it later when logging in to the server.

Finally, I’ll need to choose how connections are going to be made to my Azure Database for PostgreSQL Flexible Server. If I’m going to have applications running from VMs or the Azure App Service, I can limit any public connections to my database. However, if applications or users from the public internet need to connect to my database, I’ll need to choose the “Public access” option. Here, I’m going to choose the public option and enter my public IP address into the firewall rules.

Once the deployment is complete, I should be able to connect to my new PostgreSQL database. To connect to the database, I’ll need the server’s full name. I can navigate to the “Connection Strings” blade in the Azure portal and grab the server name from there.

For integrated development environments (IDEs) for OSS systems, I really like using DBeaver. It’s easy to arrange scripts into projects, it’s relatively lightweight, and its Community Edition is free to use. I highly recommend testing it out if you find yourself administering various relational database platforms on a regular basis.

Let’s open DBeaver and add a new connection to PostgreSQL.

Under the connection settings, paste in the full name of the Azure PostgreSQL server you copied from the Connection Strings from the Azure Portal in the previous step. Then, enter your admin username and password. Once you’ve entered everything correctly, you should click the “Test Connection” button to ensure you can connect to your new PostgreSQL server.

When To Use PostgreSQL Flexible Server

The PostgreSQL Flexible Server offering for Azure Database is the best option for you if you’re looking to seamlessly migrate from on-premises PostgreSQL databases to Azure or from other cloud providers to Azure. This offering gives you a great deal of flexibility in terms of extended configuration options, cost savings, and resource assignment for resource-intensive workloads.

Optimizing the performance of your Azure PostgreSQL Flexible Server is crucial to realizing these benefits. For help tuning and improving overall database health, check out SolarWinds PostgreSQL solutions with on-premises and SaaS deployment options.

Do you have relational database workloads you’re considering moving to a cloud provider? Which relational database are you targeting for your migration? Which cloud provider are you favoring? Let me know in the comments below!