Azure Database for PostgreSQL & MySQL
Azure Database for PostgreSQL
Deployment Options
| Option | Description |
|---|---|
| Flexible Server | Recommended. Zone-redundant HA, burstable compute, stop/start. |
| Deprecated — migrate to Flexible Server |
Key Features
- Versions: PostgreSQL 13, 14, 15, 16
- Compute tiers: Burstable, General Purpose, Memory Optimized
- Storage: Up to 16 TB, auto-grow supported
- HA: Zone-redundant with automatic failover
- Read replicas: Up to 5 replicas, cross-region supported
- Extensions:
pg_stat_statements,pgcrypto,PostGIS, many more
Connection Example
-- Connect via psql
psql "host=myserver.postgres.database.azure.com port=5432 dbname=mydb user=myadmin password=<YOUR_PASSWORD> sslmode=require"
-- Check version
SELECT version();
-- Enable useful extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
🎯 Exam Focus
For DP-300, know that Flexible Server is the recommended deployment for PostgreSQL. Single Server is deprecated. Key differentiator: Flexible Server supports stop/start capability for cost savings.
Azure Database for MySQL
Deployment Options
| Option | Description |
|---|---|
| Flexible Server | Recommended. Same-zone and zone-redundant HA. |
| Deprecated — migrate to Flexible Server |
Key Features
- Versions: MySQL 5.7, 8.0
- Compute tiers: Burstable (B-series), General Purpose, Business Critical
- Storage: Up to 16 TB, auto-grow
- HA: Same-zone and zone-redundant options
- Read replicas: Up to 5, cross-region supported
- Data-in replication: Replicate from external MySQL to Azure
MySQL Performance Tuning Parameters
-- Key server parameters to tune
-- slow_query_log = ON
-- long_query_time = 2
-- innodb_buffer_pool_size = ~70-80% of available memory
-- max_connections = based on tier (default varies)
-- Check slow query status
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
🏢 Real-World DBA Note
When migrating from on-prem MySQL, use Azure Database Migration Service (DMS) for minimal downtime. For PostgreSQL, you can also use pg_dump/pg_restore for smaller databases.
Comparison: PostgreSQL vs MySQL on Azure
| Feature | PostgreSQL Flexible | MySQL Flexible |
|---|---|---|
| HA | Zone-redundant | Same-zone + Zone-redundant |
| Max Storage | 16 TB | 16 TB |
| Read Replicas | Up to 5 | Up to 5 |
| Stop/Start | ✅ | ✅ |
| Backup Retention | 7-35 days | 7-35 days |
| VNET Integration | ✅ | ✅ |
| Private Link | ✅ | ✅ |
Anti-Patterns
- "Use Single Server because it's cheaper." PostgreSQL Single Server is deprecated — retirement on the roadmap. New deployments must be Flexible Server. MySQL Single Server is also retired. Picking Single Server today = a forced migration in months.
- "Burstable tier for production." Burstable banks credits when idle and burns them on spikes. Steady production load drains credits within hours and the DB throttles to baseline (~5–10 % of vCore). Use General Purpose for any sustained workload.
- "Same-zone HA is good enough." Same-zone protects against host failures, not zone outages. For 99.99 % SLA you must pick zone-redundant HA (paid only for GP+ tier, separate zone for standby).
- "Read replicas count as DR." They're async, in any region, and not promoted automatically. They protect read scale, not RTO. For DR, combine with backups and a documented promotion runbook.
- "Just allow 0.0.0.0/0 in firewall to test." That setting replaces IP-rule validation with "allow any Azure service in any tenant." It's the single most common Defender for Cloud finding on Flexible Server.
- "PostgreSQL extensions — install whatever I need." Only allow-listed extensions load (
azure.extensionsserver parameter).pg_repack,pgvector,postgisare common ones, butpg_cronrequires Flexible Server and a parameter restart.
⚠️ Watch Out
Storage auto-grow on Flexible Server is one-way. It expands but never shrinks. Over-provisioning costs you forever. Right-size at create or rebuild via dump/restore.
Migration Between Options
| From → To | Path | Cost |
|---|---|---|
| Single Server (PG/MySQL) → Flexible Server | Azure Database Migration Service (online or offline) | Plan for it now — Single Server retiring; minimal downtime via online mode |
| On-prem PostgreSQL → Flexible Server | DMS (online), pg_dump/pg_restore (offline), or logical replication | Logical replication = lowest downtime |
| Burstable → General Purpose | Online tier scale | Brief reconnect; gain non-burst CPU |
| GP → Memory Optimized | Online tier scale | Brief reconnect; for cache-heavy / OLAP workloads |
| Same-zone HA → Zone-redundant HA | Recreate — cannot change in place | Backup + restore; downtime window required |
| Public access → Private endpoint / VNet-injected | Recreate (PG Flexible) or transition (MySQL) | Plan IP space; private DNS zone; firewall update on app side |
| Flexible Server → Cosmos DB for PostgreSQL | Citus extension; reshape schema for distributed | Significant rewrite; only for horizontal-scale needs |
| Single-region → Geo-Redundant Backup | Toggle backup option (only at create for some tiers) | Doubles backup storage cost; gain cross-region restore |
Most-expensive moves: same-zone HA → zone-redundant (forces recreate) and public → private (network rework).
Real Scenarios
- Greenfield SaaS, multi-tenant Postgres, 99.99 % SLA → PG Flexible Server, GP tier, zone-redundant HA, private endpoint, geo-redundant backup. Driver: SLA + tenant isolation. Trade-off: 2× compute cost for HA standby.
- Migrating WordPress + 50 plugins from on-prem MySQL 5.7 → MySQL Flexible Server, GP tier, same-zone HA, public access with FW rules. Driver: simple LAMP stack. Trade-off: same-zone HA = lower SLA but half the cost.
- Analytical workload, 8 TB Postgres, weekly batch → PG Flexible Server, Memory Optimized tier, no HA, geo-restore for DR. Driver: cache-bound queries. Trade-off accepted: no HA — batch can rerun.
- Dev/test, 5 small DBs → PG Flexible Server, Burstable B1ms, auto-stop after 7 days idle. Driver: pennies/month. Trade-off: cold-start delay on first connection.
- Cross-region read scale for global app → PG Flexible Server primary in West Europe + 2 read replicas (East US, Southeast Asia). Driver: read latency at the edge. Trade-off: writes still cross-region; replica lag observable.
Flashcards
What is the recommended PostgreSQL deployment on Azure?
Click to reveal answer
Flexible Server. Single Server is deprecated.
1 / 6
Quiz
Which PostgreSQL deployment option is recommended for new workloads on Azure?