azure-postgresql-flexible-server

Azure Database for PostgreSQL - Flexible Server is a fully managed PostgreSQL database service that can handle mission-critical workloads with predictable performance and dynamic scalability. Designed to provide granular control and flexibility over database management functions and configuration settings, Azure PostgreSQL Flexible Server yada yada just add few sentences about how great this service is

Use Cases

Highly Scalable Data Store

PostgreSQL is an open-source object-relational database-management system. With decades of development behind it and as one of the most popular solutions in its class, PostgreSQL excels as a backend database. It is versatile and adaptable.

ACID Compliant

PostgreSQL supports the rigorous requirements of financial institutions and others needing exceptional reliability. It supports atomicity, consistency, isolation, and durability (ACID) and online transaction processing (OLTP).

Spatial Data

PostgreSQL is highly extensible. PostGIS, an extension for a geographical information system (GIS), provides hundreds of functions to process geometric data. In the decades since its initial release, PostGIS has become one of the de facto standards in the open-source GIS world.

JSON Support

PostgreSQL also supports JSON data in either json or jsonb format. For querying JSON data, PostgreSQL supports the jsonpath data type.

Guided Configs

Development

• Version: Set PostgreSQL version to 13, which is recommended version
• Tier: Using Burstable SKU for lowest price
• Size: B2s size is the smallest and cheapest available size for Burstable tier
• Provides 2 vCores, 4GiB memory, and 1280 max iops
• Storage GB: A preset size of 32GB for the user to use for their databases
• Backup retention: Set to 7 days since it’s a dev environment
• High availability: Set to false as this would be a dev environment

Production (Small / Medium-size)

• Version: Set PostgreSQL version to 13, which is recommended version
• Tier: Using General Purpose SKU for balanced CPU/RAM/IOPS
• Size: D16s is a mid-range level tier
• Provides 16 vCores, 64 GiB memory, 18000 max iops
• Storage GB: A preset size of 256GB for a moderate amount of production databases
• Backup retention: Set to 30 days since it’s a production environment
• High availability: Set to true as this would be a production environment

Production (Large-size)

• Version: Set PostgreSQL version to 13, which is recommended version
• Tier: Using Memory Optimized SKU for optimal RAM to CPU/IOPS ratio
• Size: E32s is a high-range level tier
• Provides 32 vCores, 256 GiB memory, 18000 max iops
• Storage GB: A preset size of 512GB for a large amount of production databases
• Backup retention: Set to 30 days since it’s a production environment
• High availability: Set to true as this would be a production environment

Design

Adjust performance and scale within seconds

The flexible server service is available in three compute tiers: Burstable, General Purpose, and Memory Optimized. The Burstable tier is best suited for low-cost development and low concurrency workloads that do not need full compute capacity continuously. The General Purpose and Memory Optimized are better suited for production workloads requiring high concurrency, scale, and predictable performance.

High-availability

The flexible server deployment model is designed to support high availability within a single availability zone and across multiple availability zones.The storage maintains three locally redundant synchronous copies of the database files ensuring data durability.

Virtual network integration

Several networking resources are provisioned in order for your flexible server to be available to other resources in your Azure virtual network. A subnet is created and delegated to your flexible server for exclusive use. Additionally a private DNS zone is created and associated with your virtual network so your workloads can use the internal DNS reference.

Features

Auto CIDR

The Masssdriver Auto CIDR feature takes the burden of selecting a CIDR range away from you by determining the next available CIDR range and automatically provisions it. You still have the option to manually set your CIDR range if you prefer.

Extension support

Azure PostgresQL Flexible Server bundle now supports the PgBouncer extension.

Best Practices

Dedicated Subnet and DNS

A dedicated virtual network subnet with its own private DNS zone is provisioned for VNet integration

Backups and Redundancy

The flexible server service automatically creates server backups and stores them on zone redundant storage (ZRS) within the region. Backups are maintained for 7 to 35 days and can be used to restore your server to any point-in-time within the backup retention period.

Automated Updates

The service performs automated patching of the underlying hardware, OS, and database engine. The patching includes security and software updates. For the PostgreSQL engine, minor version upgrades are also included as part of the planned maintenance release.

Security

Auto-generated password

A random 16 character password will be generated for your server.

Deployed into a Private Subnet

Only accessible from with the VNet and any peered networks

Encrypted Data in transit

Azure Database for PostgreSQL encrypts in-transit data with Secure Sockets Layer and Transport Layer Security (SSL/TLS). Encryption is enforced by default.

Encrypted Data at rest

For storage encryption, Azure Database for PostgreSQL uses the FIPS 140-2 validated cryptographic module. Data is encrypted on disk, including backups and the temporary files created while queries are running.

Observability

Massdriver gives you visibility into the health of your systems. By default, flexible servers will be created with alarms to monitor at key performance degradation or failure thresholds. You will be notified when storage, memory or CPU exceeds 90% usage.

Trade-offs

• Azure AD authentication is not yet supported for flexible server
• Read replicas are not currently available for flexible server