What is Azure PostgreSQL?
Azure Database for PostgreSQL is an Azure database service based on the PostgreSQL Community Edition database engine. Azure Database provides the regular PostgreSQL features, plus extended capabilities that are offered on-demand. The service provides automatic backups, enterprise-grade security, automated maintenance for the underlying infrastructure, elastic scaling, automated monitoring, and more.
In this post, we’ll review Azure PostgreSQL managed service, and also present a second option—running PostgreSQL in an Azure virtual machine, with storage leveraging Azure managed disks or third-party solutions like NetApp Cloud Volumes ONTAP. Self-managing PostgreSQL in a VM is a bit more challenging, but lets you achieve the maximum level of control and optimization, as you would have in your local data center.
A note about migration: The Azure Database Migration Service (DMS) supports migrations both to Azure Database for PostgreSQL and Azure VMs. So you can easily automate migration for both fully managed and self-managed options.
In this article, you will learn:
PostgreSQL on Azure: Managed Server Options
When you opt to host your PostgreSQL database on Azure in a platform as a service (PaaS) model, there are three main deployment options: Single Server, Flexible Server, and Hyperscale.
Azure Database for PostgreSQL Single Server
Single Server provides a fully-managed database platform for PostgreSQL on Azure. The platform provides database management capabilities that require minimal configuration, including availability, security, backups, and patching.
The architecture of Single Server is designed for 99.99% availability in a single availability zone, and the platform supports PostgreSQL community versions 9.5, 9.6, 10, and 11. The platform is ideal for cloud-native applications that can handle automated patches and do not require granular control or customization.
Azure Database for PostgreSQL Flexible Server
Flexible Server provides fully-managed database services that provide granular control over database management configurations. The architecture of Flexible Server lets you collocate your database engine with the client tier. You can also configure high availability across multiple zones.
You can control when your server starts and stops, and use a compute tier that supports bursting, for workloads that do not require continuous, full compute capacity. Bursting also provides improved cost optimization, because resources are provisioned flexibly according to actual loads. However, the service is currently in preview and supports only community versions 11 and 12 of PostgreSQL.
Azure Database for PostgreSQL Hyperscale (Citus)
Hyperscale (Citus) provides a fully-managed database platform that lets you deploy PostgreSQL using horizontal scaling across multiple machines. To achieve this, the system uses a technique called sharding, which separates the database into different partitions. The system can then perform query parallelization across multiple servers and deliver a faster response to incoming SQL queries.
Hyperscale helps deliver faster response times when applications query large databases. The service provides greater scalability and performance for workloads that either approach or exceed 100 GB of data. It can support multi-tenant applications, high throughput transactional workloads, and real-time operational analytics.
Pros and Cons of Fully Managed PostgreSQL in Azure
Pros of fully-managed PostgreSQL in Azure
- Fully managed high availability, backup, patching and updates. Most ongoing maintenance efforts are taken care of as part of the managed service.
- Automated scalability:
- With Single Server and Flexible Server, you can scale server resources up and down, and scale up storage, in the Azure Portal or using the Azure CLI.
- With PostgreSQL Hyperscale (Citrus), you can automatically shard your database and dynamically scale workloads across multiple machines.
- In Azure Database for PostgreSQL, storage is an integrated part of the service, and scales automatically based on usage up to 4TB. Note that you cannot scale down storage.
Cons of fully-managed PostgreSQL in Azure
Though many DB administration activities are offloaded in Azure Database for MySQL, customers could find them to be restrictive in certain circumstances. For example:
- Azure SQL Database for PostgreSQL only supports PostgreSQL 9.5, 9.6, 10, and 11. The Flexible Server solution has more limited version support - only PostgreSQL versions 11 and 12. If you run workloads on versions earlier than 9.5, you will need to upgrade your database.
- There is also no guarantee of the exact DB maintenance time - planned maintenance events run for 15 hours, while individual servers are updated and restarted for 60 seconds, at some point during this period. Microsoft advises not to performing long running transactions during the planned maintenance window.
- The DBaaS services are deployed in Azure and can be connected to local data centers in a hybrid configuration using read replicas. However, hybrid deployment is not supported for Flexible Server or Hyperscale (Citrus).
- Azure SQL Database backup is automated, but the schedule and frequency may not always align with your organization’s data protection DR requirements. This means additional efforts will be needed to perform backups manually or using custom scripts and copying data to additional Azure regions for redundancy.
- Managed services use built-in Azure storage, which offers limited opportunity for customization outside the fixed parameters of the Azure SKUs. However, in enterprise deployments, the storage layer needs additional flexibility and data management features compared to what is supported by native cloud storage.
- For PostgreSQL administrators, there will be a learning curve before they can take up the configuration and management of Azure Database for PostgreSQL.
- Azure Database for PostgreSQL only supports Gen 4 and Gen 5 instances with up to 32 vCores, 10 GB of RAM, and 16 TB local storage. If you need a more powerful instance, you will not be able to use the service.
- Limit on the number of IOPS supported - the limit is 15 MB/sec for Burstable instances, and between 48-750MB/sec for General Purpose instances depending on instance type.
- Migration between major versions of PostgreSQL is not supported - you will need to take a dump of the database and restore it to an instance with the new version.
- The maximum number of connections to an Azure PostgreSQL Database instance is limited to 55 for Burstable instances, between 145-1895 for General Purpose instances, and between 295-1982 for Memory Optimized instances, depending on instance type.
PostgreSQL on Azure: The Self-Managed Option
The above PaaS options for deploying PostgreSQL on Azure are suitable for cloud-native applications, or applications rebuilt for the cloud. For enterprise workloads currently running in an on-premises data center, or for enterprise workloads that require greater flexibility a more suitable model is infrastructure as a service (IaaS) - deploying your database in an Azure VM.
IaaS deployments provide you with full control over your database. This means you can fine tune database and server configurations in the cloud just like you would do in on-premises deployments.
Azure offers marketplace images which help to deploy VMs with PostgreSQL in a matter of a few clicks. Marketplace images use Azure VMs fine-tuned for databases. In order to manage storage for the PostgreSQL database, you will need to use a managed storage service provided by Azure or a third party.
You can choose Azure managed disks, with a Premium or Ultra disk option for superior performance in production deployments.
Compute performance can be adjusted by scaling up VMs to high performance SKUs (instance types) if required.
Pros and Cons of Self-Managed PostgreSQL in Azure
Pros of self-managed PostgreSQL in an Azure VM
- Provides full end-to-end control, same as you would have in an on-premises deployment.
- Less expensive than managed options, because you are only paying for compute and storage resources (as well as for the Marketplace image, if you selected a paid option).
- Supports all PostgreSQL versions, including versions earlier than 9.5
- You can perform maintenance at any time - no risk of interruption during maintenance windows outside your control.
- Easy to connect your cloud-based database to an on-premises data center.
- Full flexibility to configure schedule and frequency of backups according to your organization’s disaster recovery requirements.
- Ability to use high performance storage services, including all tiers of managed disks, and high-performance third-party storage services like NetApp Cloud Volumes ONTAP.
- No learning curve for using the database, because it can be deployed with exactly the same configuration as in the local data center.
- You can deploy the database on any instance size, with the ability to use large instances for heavy-duty production databases.
- No hard limit on the number of IOPS or connections - you are free to configure your database with the resources needed to meet your throughput, and tune performance to meet your loads.
Cons of self-managed PostgreSQL in an Azure VM
- When running in an Azure VM, you own the configuration of high availability, backup management, patching, etc. This requires additional effort.
- It is up to you to devise a scaling strategy for compute resources, and configure it using auto scaling features available in Azure VMs. This can be challenging compared to Azure Database for PostgreSQL where scalability for read replicas is built in.
- More difficult to manage storage, which relies on Azure managed disks. In order to scale up storage, you will need to attach a large managed disk or storage service to your instance and copy your data.
These cons could also be advantages, when you need more control and options to customize your deployment, or when the backup schedule needs to be aligned with organizational strategy.
What this deployment option boils down to is more control over the database, its optimization, how you can use that data, and how much you can save by avoiding the costs of premium services.
PostgreSQL on Azure with Cloud Volumes ONTAP: The Best of Both Worlds
For organizations that need to customize their PostgreSQL deployments, and need to overcome the limitations of the Azure Database for PostgreSQL managed service, the self-managed Azure VM option is the most appropriate option. However, this option comes with greater complexity and difficulty of managing storage.
NetApp Cloud Volumes ONTAP can help customers meet the performance, availability, and agility requirements of PostgreSQL in a self-managed environment. It augments the Azure cloud storage layer with a set of storage management features and capabilities, uniquely suited to enterprise deployments, including:
- Hybrid deployments, supporting consistent deployment of PostgreSQL workloads irrespective of whether they are deployed on-premises or in any cloud.
- Storage efficiency through data deduplication, compression, and thin provisioning, which reduce storage footprint and monthly Azure storage costs.
- NetApp Snapshot™ copies for point-in-time, application-consistent database snapshots to recover the database in case of disaster or data corruption.
- Easy migration across hybrid environments through NetApp Cloud Sync service or SnapMirror® data replication.
- The Azure high availability architecture configuration assures zero data loss (RPO=0) and minimal recovery times (RTO< less than 60 seconds).
- High performance through intelligent caching technology that reduces data access latencies.
- FlexClone® data cloning technology creates storage-efficient, writable clones of database volumes that can be used in testing and development scenarios.
Learn more from our customers in our Database Case Studies with Cloud Volumes ONTAP blog, and visit our Cloud Volumes ONTAP for PostgreSQL solution page.