Microsoft Azure — Database for MySQL & PostgreSQL

Azure is Microsoft's cloud computing platform offering over 200 services. This article covers database for mysql & postgresql with production-ready configurations, best practices, and hands-on examples.

Choosing Between Azure Database for MySQL and PostgreSQL

Azure offers managed database services for both MySQL and PostgreSQL, but the choice between them should be driven by workload requirements, not familiarity. MySQL excels in read-heavy workloads, simple replication, and compatibility with legacy applications. PostgreSQL shines in complex queries, JSONB support, and advanced indexing like GIN and GiST. For production, consider your data integrity needs: PostgreSQL's MVCC and full ACID compliance make it better for financial systems, while MySQL's InnoDB engine is sufficient for most web apps. Both support high availability with zone-redundant deployment, but PostgreSQL's logical replication offers more flexibility for zero-downtime upgrades. Benchmark with your actual query patterns—don't assume one is faster. Use Azure's pricing calculator to compare vCore-based tiers; General Purpose is the default for production, but Memory Optimized may be needed for analytical workloads. Avoid the temptation to use the Basic tier in production—it lacks SLA and features like read replicas.

az mysql flexible-server create \
  --resource-group prod-rg \
  --name prod-mysql \
  --sku-name Standard_D4ds_v4 \
  --tier GeneralPurpose \
  --storage-size 128 \
  --backup-retention 7 \
  --high-availability ZoneRedundant \
  --admin-user dbadmin \
  --admin-password 'Str0ng!Pass'

Provisioning with Infrastructure as Code

Manual provisioning via portal is a recipe for drift. Use Terraform or Bicep to define your Azure Database for MySQL/PostgreSQL as code. This ensures repeatability, version control, and auditability. For Terraform, use the azurerm_mysql_flexible_server or azurerm_postgresql_flexible_server resources. Pin the provider version and use remote state with locking. Key parameters: sku_name (e.g., GP_Standard_D4ds_v4), storage_mb, backup_retention_days, and geo_redundant_backup_enabled. Enable delegated_subnet_id for private access—never expose to public internet. Use administrator_login and administrator_password from a key vault reference, not plain text. For PostgreSQL, set version to 14 or 15 (avoid 11, which is EOL). Add a azurerm_private_endpoint for secure connectivity. Store the connection string in Azure Key Vault and reference it in App Service settings via @Microsoft.KeyVault() syntax.

resource "azurerm_resource_group" "rg" {
  name     = "prod-db-rg"
  location = "eastus"
}

resource "azurerm_postgresql_flexible_server" "pg" {
  name                   = "prod-pg"
  resource_group_name    = azurerm_resource_group.rg.name
  location               = azurerm_resource_group.rg.location
  version                = "15"
  delegated_subnet_id    = azurerm_subnet.db.id
  private_dns_zone_id    = azurerm_private_dns_zone.pg.id
  administrator_login    = "dbadmin"
  administrator_password = data.azurerm_key_vault_secret.db_password.value
  storage_mb             = 262144
  sku_name               = "GP_Standard_D4ds_v4"
  backup_retention_days  = 7
  geo_redundant_backup_enabled = true

  high_availability {
    mode = "ZoneRedundant"
  }
}

Configuring High Availability and Disaster Recovery

Azure's flexible server offers zone-redundant HA with automatic failover. This replicates data synchronously to a standby in a different availability zone. Failover is automatic and typically completes within 60-120 seconds. For cross-region DR, enable geo-redundant backup (stored in paired region) and configure read replicas in another region. For PostgreSQL, use logical replication to create a cross-region replica for near-sync DR. For MySQL, use the built-in read replica feature (asynchronous). Test failover regularly—schedule a quarterly game day where you simulate a zone outage. Monitor replication lag using Azure Monitor metrics: Replication Lag for MySQL, pg_replication_slots for PostgreSQL. Set alerts for lag > 30 seconds. For zero data loss, consider Azure's Zone-Redundant HA with synchronous commit; but be aware of latency impact (typically 1-2ms). Never rely solely on backups for DR—restore times can be hours for large databases.

-- PostgreSQL: Check replication lag in seconds
SELECT
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
  EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds
FROM pg_stat_replication;

-- MySQL: Check replication lag
SHOW SLAVE STATUS\G
-- Look for Seconds_Behind_Master

Performance Tuning: Indexing and Query Optimization

Performance starts with the schema. Use EXPLAIN ANALYZE to identify full table scans. For PostgreSQL, leverage partial indexes, covering indexes, and BRIN indexes for large tables. For MySQL, use composite indexes with the most selective column first. Avoid over-indexing—each index slows writes. Use Azure's Query Performance Insight to find expensive queries. Set work_mem (PostgreSQL) or tmp_table_size (MySQL) appropriately for your workload. For read-heavy apps, add read replicas and route read traffic to them. Use connection pooling with PgBouncer or ProxySQL to reduce connection overhead. Monitor buffer cache hit ratio—if below 99%, increase memory. For PostgreSQL, enable pg_stat_statements to track query performance. For MySQL, use Performance Schema. Set innodb_buffer_pool_size to 70-80% of available memory for MySQL. Avoid using SELECT * in production—always specify columns.

-- PostgreSQL: Find missing indexes
SELECT
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / seq_scan AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_tup_read DESC
LIMIT 10;

-- MySQL: Find slow queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_TIMER_WAIT > 1000000000
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Security: Authentication, Authorization, and Encryption

Use Azure Active Directory (Azure AD) authentication for both MySQL and PostgreSQL. This eliminates password management and enables conditional access policies. For legacy apps, use password auth with strong rotation policies via Key Vault. Enable SSL/TLS enforcement—set require_secure_transport to ON for MySQL, and ssl to require for PostgreSQL. Use private endpoints and network security groups to restrict access. For data at rest, Azure Storage Service Encryption is enabled by default; use customer-managed keys (CMK) for compliance. For column-level encryption, use pgcrypto (PostgreSQL) or AES_ENCRYPT (MySQL). Implement row-level security (RLS) in PostgreSQL for multi-tenant apps. Audit all connections using Azure's audit logs. Set log_connections and log_disconnections to ON. Use Azure Policy to enforce TLS version 1.2 or higher. Never use the root user for application connections—create role-specific users with least privilege.

-- PostgreSQL: Create read-only user
CREATE ROLE app_readonly WITH LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE prod_db TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

-- MySQL: Create read-only user
CREATE USER 'app_readonly'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON prod_db.* TO 'app_readonly'@'%';
FLUSH PRIVILEGES;

Backup and Restore Strategies

Azure automatically takes full backups weekly, differential daily, and transaction log backups every 5 minutes. Retention is configurable up to 35 days. For longer retention, use Azure Backup or export to blob storage. Always test restores—backups are useless if they can't be restored. Use point-in-time restore (PITR) to recover to any second within the retention period. For cross-region restore, enable geo-redundant backup. For large databases (>1TB), restore times can be hours; consider using read replicas for faster recovery. Automate backup validation with a script that restores to a temporary server and runs integrity checks. For PostgreSQL, use pg_restore with --jobs for parallel restore. For MySQL, use mysqlpump or mydumper. Monitor backup status via Azure Monitor alerts. Never rely solely on Azure's automated backups—export periodic logical backups to a different storage account for defense in depth.

#!/bin/bash
# Test PITR restore to a temporary server
RESTORE_TIME="2026-07-10T03:00:00Z"
az postgres flexible-server restore \
  --resource-group prod-rg \
  --name prod-pg-restore-test \
  --source-server prod-pg \
  --restore-time $RESTORE_TIME

# Run integrity checks
PGPASSWORD='temp_pass' psql -h prod-pg-restore-test.postgres.database.azure.com \
  -U dbadmin -d prod_db -c "SELECT count(*) FROM orders;"

# Clean up
az postgres flexible-server delete --resource-group prod-rg --name prod-pg-restore-test --yes

Monitoring and Alerting with Azure Monitor

Use Azure Monitor to collect metrics and logs from your database. Key metrics: CPU percent, memory percent, storage percent, IOPS, connections, and replication lag. Set alerts for thresholds: CPU > 80% for 5 minutes, storage > 80%, connections > 90% of max. Enable diagnostic settings to stream logs to Log Analytics for query analysis. Use Azure Workbooks to create dashboards for at-a-glance health. For PostgreSQL, enable pg_stat_statements and log slow queries with log_min_duration_statement = 1000 (1 second). For MySQL, set slow_query_log = ON and long_query_time = 1. Use Azure's Intelligent Insights for proactive anomaly detection. Set up action groups to notify Slack, PagerDuty, or email. Monitor deadlocks—PostgreSQL logs them automatically; MySQL requires innodb_print_all_deadlocks = ON. Create a runbook for common alerts so on-call engineers know how to respond.

{
  "name": "High CPU Alert",
  "description": "Alert when CPU > 80% for 5 minutes",
  "severity": 2,
  "condition": {
    "allOf": [
      {
        "field": "Percentage CPU",
        "operator": "GreaterThan",
        "threshold": 80,
        "timeAggregation": "Average",
        "windowSize": "PT5M"
      }
    ]
  },
  "actions": [
    {
      "actionGroupId": "/subscriptions/.../actionGroups/on-call"
    }
  ]
}

Scaling: Vertical and Horizontal Strategies

Azure allows vertical scaling (changing vCores and memory) with minimal downtime (typically 60-120 seconds). Use this for predictable growth. For unpredictable spikes, enable autoscale via Azure's scaling API—but be aware it's not instantaneous (takes 5-10 minutes). For read scaling, add read replicas (up to 10 for MySQL, up to 5 for PostgreSQL). Replicas are asynchronous; monitor lag. For write scaling, consider sharding at the application layer. PostgreSQL supports partitioning natively; MySQL has partitioning but with limitations. Use Azure's Connection Pooling feature (via PgBouncer) to handle thousands of connections without scaling up. For storage, Azure automatically grows up to 16TB; set auto_grow_enabled to true. Plan for scale by testing with production-like load in a staging environment. Use Azure Load Testing to simulate traffic. Remember: scaling up is easier than scaling out; design your schema for horizontal scaling from day one if you anticipate massive growth.

# Scale up PostgreSQL from D4ds to D8ds
az postgres flexible-server update \
  --resource-group prod-rg \
  --name prod-pg \
  --sku-name Standard_D8ds_v4

# Add a read replica
az postgres flexible-server replica create \
  --resource-group prod-rg \
  --replica-name prod-pg-replica \
  --source-server prod-pg

Migration: On-Premises to Azure

Use Azure Database Migration Service (DMS) for online migrations with minimal downtime. For offline migrations, use pg_dump/pg_restore (PostgreSQL) or mysqldump (MySQL). For large databases (>100GB), use Azure's Data Factory or bulk copy with COPY (PostgreSQL) or LOAD DATA INFILE (MySQL). Pre-migration checklist: validate source version compatibility (Azure supports MySQL 5.7/8.0, PostgreSQL 11-15), check for unsupported features (e.g., MySQL's FEDERATED engine), and reduce downtime by using continuous sync. For PostgreSQL, use pglogical extension for real-time sync. For MySQL, use DMS with CDC. Test the migration in a staging environment first. After migration, run ANALYZE to update statistics. Monitor performance for a week before cutting over. Have a rollback plan: keep the source database running until you're confident.

# Offline migration using pg_dump/pg_restore
pg_dump --host=onprem-db --port=5432 --username=admin \
  --dbname=proddb --format=custom --file=proddb.dump

pg_restore --host=prod-pg.postgres.database.azure.com --port=5432 \
  --username=dbadmin --dbname=proddb --jobs=4 --verbose proddb.dump

# Run analyze
psql -h prod-pg.postgres.database.azure.com -U dbadmin -d proddb -c "ANALYZE;"

Cost Optimization and Governance

Azure Database for MySQL/PostgreSQL costs are driven by vCores, storage, and backup retention. Use reserved instances for 1 or 3 years to save up to 40% for steady-state workloads. For dev/test, use Burstable tier (B-series) which accumulates CPU credits. Right-size your tier: monitor CPU and memory usage; if average CPU < 20% for a week, consider scaling down. Use Azure Cost Management to set budgets and alerts. For backup storage, reduce retention to 7 days if not required by compliance. Use geo-redundant backup only if needed—it doubles backup storage costs. For read replicas, consider using lower tier than primary. Implement tagging for cost allocation (e.g., Environment: Production, Team: Payments). Use Azure Policy to enforce tags and restrict allowed SKUs. Schedule start/stop for non-production databases using automation (but not for production).

# Purchase reserved instance for 3 years
az reservations purchase \
  --reserved-resource-type VirtualMachines \
  --sku Standard_D4ds_v4 \
  --term P3Y \
  --quantity 1

# Scale down dev database
az mysql flexible-server update \
  --resource-group dev-rg \
  --name dev-mysql \
  --sku-name Standard_D2ds_v4

Automating Maintenance and Patching

Azure handles OS and database engine patching automatically, but you control the maintenance window. Set it to a low-traffic period (e.g., 2 AM Sunday). For critical patches, you can schedule a one-time maintenance. Use Azure's maintenance notifications to get advance notice. For zero-downtime patching, use read replicas: failover to replica, patch primary, then failback. For PostgreSQL, use pg_rewind to quickly sync after failback. For MySQL, use the built-in replica failover. Test patching in a staging environment first. Monitor patching history via Activity Log. For custom extensions (e.g., pg_cron, postgis), ensure they are compatible with the new version. Use Azure Policy to enforce maintenance window settings. Automate the failover process with Azure CLI scripts and test them quarterly.

# Failover to replica before patching
az postgres flexible-server replica stop-replication \
  --resource-group prod-rg \
  --name prod-pg-replica

# Promote replica to primary (manual failover)
az postgres flexible-server replica promote \
  --resource-group prod-rg \
  --name prod-pg-replica

# Update application connection string to point to replica
# After patching original primary, reattach as replica
az postgres flexible-server replica create \
  --resource-group prod-rg \
  --replica-name prod-pg-replica2 \
  --source-server prod-pg

Troubleshooting Common Production Issues

Common issues: connection timeouts, slow queries, replication lag, and storage full. For connection timeouts, check max_connections (default 100 for MySQL, 100 for PostgreSQL) and increase if needed. Use connection pooling. For slow queries, use EXPLAIN ANALYZE and add indexes. For replication lag, check network latency between regions and increase wal_keep_segments (PostgreSQL) or binlog_expire_logs_seconds (MySQL). For storage full, enable auto-grow and set up alerts at 80%. For deadlocks, review transaction ordering and use nowait or skip locked where possible. For authentication failures, check Azure AD integration and password expiry. For SSL errors, ensure client supports TLS 1.2. Use Azure's troubleshooting guides and support tickets for deeper issues. Keep a runbook of common fixes and share with the team.

-- PostgreSQL: Check active connections
SELECT count(*) FROM pg_stat_activity;

-- MySQL: Check active connections
SHOW STATUS LIKE 'Threads_connected';

-- PostgreSQL: Check for blocking locks
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;