Azure Database for PostgreSQL Flexible Server Architecture & Operations 🐘⚡

Azure Database for PostgreSQL Flexible Server is Microsoft’s next-generation managed PostgreSQL service built on Linux-based virtual machines and managed disk storage. Replacing the legacy Single Server architecture, Flexible Server delivers granular database configuration controls, Zone-Redundant High Availability (HA) with automated health detection and failover, built-in PgBouncer connection pooling, automated storage auto-grow, and predictable custom maintenance windows.

---

1. Architecture & Zone-Redundant High Availability

Flexible Server separates compute from storage using Azure Premium SSD / Ultra Disk managed storage. High Availability is implemented via synchronous replication to a hot standby VM in an adjacent Availability Zone.

                           CLIENT APPLICATION / MICROSERVICES
                                           │
                                           ▼ (Port 5432 or 6432)
       ┌────────────────────────────────────────────────────────────────────────┐
       │                   VIRTUAL NETWORK (VNET) INTEGRATION                   │
       │                   (Delegated Subnet: Microsoft.DBforPostgreSQL/flexibleServers)
       └───────────────────────────────────┬────────────────────────────────────┘
                                           │
                                           ▼
       ┌────────────────────────────────────────────────────────────────────────┐
       │                 BUILT-IN PGBOUNCER CONNECTION POOLER                   │
       │             (Port 6432: Transaction / Session / Statement)             │
       └───────────────────────────────────┬────────────────────────────────────┘
                                           │
                    AVAILABILITY ZONE 1    │      AVAILABILITY ZONE 2
       ┌───────────────────────────────────┴──┐ ┌──────────────────────────────┐
       │      PRIMARY COMPUTE NODE            │ │     HOT STANDBY NODE         │
       │  ┌────────────────────────────────┐  │ │ ┌──────────────────────────┐ │
       │  │ PostgreSQL Engine (v13-v16)    │  │ │ │ PostgreSQL Engine (Standby│ │
       │  └────────────────┬───────────────┘  │ │ └──────────▲───────────────┘ │
       │                   │ WAL Stream       │ │            │ WAL Apply       │
       │                   └──────────────────┼─┼────────────┘                 │
       │                                      │ │                              │
       │  ┌────────────────────────────────┐  │ │ ┌──────────────────────────┐ │
       │  │ Primary Managed Storage        ├──┼─┼─► Standby Managed Storage  │ │
       │  │ (Premium SSD v2)               │  │ │ │ (Synchronous Mirroring)  │ │
       │  └────────────────────────────────┘  │ │ └──────────────────────────┘ │
       └──────────────────────────────────────┘ └──────────────────────────────┘
                          ▲                                    ▲
                          └──────────────┬─────────────────────┘
                                         │
       ┌─────────────────────────────────┴──────────────────────────────────────┐
       │                     AZURE HIGH AVAILABILITY CONTROLLER                 │
       │   - Continuous heartbeat probing of primary compute and storage        │
       │   - Automatic DNS failover to standby node in 60-120 seconds           │
       │   - Zero data loss guarantee (RPO = 0)                                 │
       └────────────────────────────────────────────────────────────────────────┘

High Availability Mechanics

1. Synchronous Physical Replication: The primary instance streams Write-Ahead Log (WAL) records synchronously to the hot standby instance in Zone 2. A transaction is acknowledged as committed to the client only after the WAL record is written to both the primary and standby storage volumes.
2. Automated Health Detection & Failover: The Azure HA controller continuously monitors node health. If the primary VM, zone infrastructure, or network fails, the controller automatically updates DNS routing to point to the standby instance.
3. Failover Metrics:
   • Recovery Point Objective (RPO): 0 seconds (zero data loss due to synchronous WAL replication).
   • Recovery Time Objective (RTO): Typically 60 to 120 seconds, including DNS cache propagation and standby WAL recovery.

---

2. Core Concepts & Built-in PgBouncer Pooling

Built-in Connection Pooling with PgBouncer

PostgreSQL uses a process-per-connection concurrency model. Opening thousands of direct TCP connections consumes significant RAM and degrades CPU performance through OS context switching.

Flexible Server embeds PgBouncer directly on the compute node:

• Port 5432: Direct PostgreSQL connections (used for administrative tasks, DDL, and migrations).
• Port 6432: Pooled connections through PgBouncer.
• Pool Modes Supported:
  • transaction: (Recommended) Connection is returned to the pool as soon as a COMMIT or ROLLBACK completes. Allows thousands of client microservices to share a small number of physical database connections.
  • session: Connection is held for the entire client session duration.
  • statement: Connection is returned after every single SQL statement (does not support multi-statement transactions).

---

3. Production Deployment & CLI Operations (az)

1. Create a Dedicated Delegated Subnet for Flexible Server

az group create --name rg-database-prod --location eastus
 
# Create VNet and delegated subnet for PostgreSQL Flexible Server
az network vnet create \
    --resource-group rg-database-prod \
    --name vnet-db-prod \
    --address-prefixes 10.50.0.0/16 \
    --subnet-name snet-postgres-flexible \
    --subnet-prefixes 10.50.1.0/24 \
    --delegations Microsoft.DBforPostgreSQL/flexibleServers

2. Deploy Zone-Redundant PostgreSQL Flexible Server

Deploy a production-grade 4-vCPU database with multi-zone HA and automated storage auto-grow:

az postgres flexible-server create \
    --resource-group rg-database-prod \
    --name psql-core-production-eastus \
    --location eastus \
    --zone 1 \
    --standby-zone 2 \
    --tier GeneralPurpose \
    --sku-name Standard_D4ds_v5 \
    --version 16 \
    --storage-size 128 \
    --auto-grow Enabled \
    --vnet vnet-db-prod \
    --subnet snet-postgres-flexible \
    --backup-retention 35 \
    --geo-redundant-backup Enabled \
    --admin-user dbadmin \
    --admin-password "ComplexP@ssw0rd9988"

3. Enable and Configure Built-in PgBouncer

# Enable built-in PgBouncer parameter
az postgres flexible-server parameter set \
    --resource-group rg-database-prod \
    --server-name psql-core-production-eastus \
    --name pgbouncer.enabled \
    --value true
 
# Set PgBouncer pool mode to Transaction
az postgres flexible-server parameter set \
    --resource-group rg-database-prod \
    --server-name psql-core-production-eastus \
    --name pgbouncer.pool_mode \
    --value transaction
 
# Configure maximum client connections allowed through PgBouncer
az postgres flexible-server parameter set \
    --resource-group rg-database-prod \
    --server-name psql-core-production-eastus \
    --name pgbouncer.max_client_conn \
    --value 5000

4. Configure Maintenance Window and Server Parameters

# Set maintenance window to Sunday 03:00 AM UTC
az postgres flexible-server maintenance-window set \
    --resource-group rg-database-prod \
    --server-name psql-core-production-eastus \
    --day-of-week 0 \
    --start-hour 3 \
    --start-minute 0
 
# Optimize shared buffers and query timeout
az postgres flexible-server parameter set \
    --resource-group rg-database-prod \
    --server-name psql-core-production-eastus \
    --name statement_timeout \
    --value 30000

5. Create Cross-Region Read Replica for Disaster Recovery

az postgres flexible-server replica create \
    --resource-group rg-database-prod \
    --replica-name psql-core-read-replica-westus \
    --source-server psql-core-production-eastus \
    --location westus \
    --sku-name Standard_D4ds_v5

---

4. Quotas, Performance, and Configuration Limits

Parameter / Dimension	Default Quota	Maximum Supported
Max Compute Shape	Standard_D4ds_v5	Up to 64 vCPUs / 512 GiB RAM (Memory Optimized)
Max Storage Capacity	128 GiB default	Elastic scaling up to 32 TiB (32,768 GiB)
Storage IOPS	Tier-based	Up to 80,000 IOPS (32 TiB Premium SSD)
Backup Retention	7 days default	1 to 35 days (PITR down to the second)
Read Replicas	0 replicas	Up to 8 read replicas per primary server
Max Direct Connections	Scaled by vCPU	E.g., 4 vCPU = 200 conns; 64 vCPU = 5,000 conns
Max PgBouncer Connections	5,000 pooled	Supports up to 10,000 concurrent client sessions

---

5. Official References & Documentation

• Azure Database for PostgreSQL Flexible Server Overview
• High Availability Concepts (Zone-Redundant)
• PgBouncer Integration & Best Practices
• PostgreSQL Server Parameters Guide
• PostgreSQL Flexible Server Pricing

---

6. Realistic Pricing Scenarios

Pricing is based on:

1. Compute vCore + Memory: Standard D-series or Memory Optimized E-series billed per hour. Zone-redundant HA doubles compute cost (primary + standby VM).
2. Storage: $0.115 per GB-month (Premium SSD).
3. Backup Storage: First 100% of provisioned storage is free; extra incremental backup storage billed at $0.095/GB-month.

Scenario A: High-Availability Production Core Database (Tier-1 E-Commerce)

• Architecture:
  • Compute: Standard_D4ds_v5 (4 vCPUs, 16 GiB RAM).
  • Zone-Redundant High Availability enabled (2 nodes active-standby).
  • Storage: 500 GB Premium SSD with auto-grow enabled.
  • Geo-redundant backups retained for 30 days (~500 GB extra backup storage).
• Monthly Cost Calculation:
  • Compute (Zone Redundant = 2 × $0.228/hr):$0.456/hr × 730 hrs = $332.88
  • Storage: 500 GB × $0.115/GB=\ast \ast$57.50**
  • Extra Backup Storage: 500 GB × $0.095/GB=\ast \ast$47.50**
• Total Monthly Cost: $437.88 / month

Scenario B: High-Throughput Analytics & Reporting Database with Read Replicas

• Architecture:
  • Primary Compute: Standard_E16ds_v5 Memory-Optimized (16 vCPUs, 128 GiB RAM) Zone-Redundant ($1.824/hr).
  • Storage: 4,000 GB (4 TiB) Premium SSD ($460.00/month).
  • Read Replicas: 2 cross-region read replicas running Standard_D8ds_v5 ($0.456/hreach=$0.912/hr).
• Monthly Cost Calculation:
  • Primary HA Compute: $1.824/hr\times 730hrs=\ast \ast$1,331.52**
  • Read Replica Compute: 2 × $0.456/hr\times 730hrs=\ast \ast$665.76**
  • Primary Storage: 4,000 GB × $0.115/GB=\ast \ast$460.00**
  • Replica Storage (2 × 4,000 GB): 8,000 GB × $0.115/GB=\ast \ast$920.00**
• Total Monthly Cost: $3,377.28 / month

---

7. Battle-Tested Nuggets & Production Gotchas

1. Storage Can Never Be Downsized: In Azure PostgreSQL Flexible Server, storage auto-grows dynamically when disk usage crosses 80%. However, storage can only scale up; it can NEVER be scaled down. If a runaway query or bulk migration inserts 5 TiB of temporary logs, your database storage expands to 5 TiB permanently. To downsize storage, you must export the entire database with pg_dump, create a brand-new server with smaller storage, and restore.
2. Subnet Delegation Lock-In: Flexible Server VNet integration delegates the subnet exclusively to Microsoft.DBforPostgreSQL/flexibleServers. Once delegated, no other service, VM, or private endpoint can be created in that subnet. Furthermore, you cannot move an existing server to a different VNet or subnet without performing a point-in-time restore or dump-and-load. Allocate a dedicated /24 or /25 CIDR block specifically for your database tier.
3. Transaction Pooling vs Prepared Statements in PgBouncer: When pgbouncer.pool_mode is set to transaction, PostgreSQL client libraries that use server-side prepared statements (e.g., Spring Boot Hibernate reWriteBatchedInserts=true, Npgsql, Go pgx) will throw errors like ERROR: prepared statement "S_1" does not exist. This happens because statement preparations are tied to a physical backend connection that is returned to the pool. Configure your ORM/driver to use client-side prepared statements or set prepareThreshold=0.
4. Zone Failovers Trigger Temporary DNS Caching Delays: During a zone-redundant HA failover, the database FQDN’s DNS A-record is updated to point to the new primary VM in Zone 2. However, Java Virtual Machines (JVMs) cache DNS resolutions forever by default (networkaddress.cache.ttl = -1). If a failover occurs, Java services will attempt to connect to the dead Zone 1 IP indefinitely until the JVM is restarted. Always set networkaddress.cache.ttl=10 in your container java.security properties.
5. Autovacuum Wraparound Emergency Lockouts: PostgreSQL requires vacuuming to prevent transaction ID wraparound (autovacuum_freeze_max_age). On heavily loaded databases where long-running analytical queries block autovacuum workers, the database may reach transaction ID starvation. Flexible Server will forcefully place the database into read-only mode to protect against data corruption. Set up Cloud Monitoring alerts for maximum_used_transaction_id crossing 70% of 2 billion.
6. VNet Integration vs Public Access Toggle is One-Way at Creation: When creating an Azure PostgreSQL Flexible Server, you must choose between Private access (VNet Integration) or Public access (allowed IP addresses and Private Endpoints). You cannot change the networking method after the server is created. If you create a server with public access and later want native delegated VNet integration, you must provision a new server and migrate all data.

Across the wiki

• Amazon RDS — relational databases (AWS)
• GCP Cloud SQL — relational databases (GCP)
• PostgreSQL — relational databases (Architecture)
• Amazon Aurora — relational databases (AWS)