Service selection
For AWS choose Amazon Aurora (MySQL/Postgres compatible) for high performance and serverless options; use RDS (Managed MySQL/Postgres) if cost/compatibility matters. On GCP use Cloud SQL; on Azure use Azure Database for MySQL/Postgres. Choose the provider that matches existing stack, SLA and read/write patterns.
Sizing
- Pick instance class based on CPU/memory needs from load testing (e.g., db.m6i.large → scale up).
- Start with balanced vCPU/memory; prefer vertical scale-friendly families.
- Storage: use gp3 (AWS) or SSD provisioned IOPS for predictable IO; size with growth buffer and autoscaling if supported.
Backups & retention
- Enable automated daily snapshots; retention 7–35 days per compliance.
- Configure point-in-time recovery with binary/log shipping; test restores regularly (quarterly).
High availability & automated failover
- Use Multi-AZ / regional primary-replica configuration. For Aurora, use cluster endpoints and automatic failover <30s. Enable synchronous replication where possible.
Read scaling
- Add read replicas (Aurora readers or RDS read-replicas). Use load balancer or application-aware routing to distribute reads. Monitor replica lag and promote if needed.
Encryption
- Enable at-rest encryption using cloud KMS customer-managed keys. Enforce TLS for in-transit. Rotate keys per policy and restrict KMS IAM roles.
Maintenance windows & routine practices
- Set weekly maintenance window during low traffic. Apply minor patches regularly, major upgrades in planned maintenance with blue/green or snapshot rollback plan.
- Routine: monitor metrics (CPU, IOPS, connections, replica lag), run failure drills, automate backups validation, review slow query logs and index optimization, cost review and right-sizing monthly.
This approach balances reliability, performance, security and operational readiness for production workloads.
