Skip to main content

SQL databases

Relational databases remain the foundational storage technology for transactional business applications. Structured schemas, foreign-key relationships, ACID (Atomicity, Consistency, Isolation, Durability) guarantees, and expressive SQL queries provide robust safeguards for data integrity. While the core database engines—such as PostgreSQL, MySQL, and Microsoft SQL Server—operate with the same query mechanics in the cloud as they do on dedicated physical hardware, cloud platforms have transformed how they are administered.

A abstracts away routine operational administration. The cloud provider handles physical hardware provisioning, operating system maintenance, engine patching, continuous backup pipelines, and automated multi-zone failover. In return, your engineering team receives a secure connection string and administrative access to the database engine, retaining full control over schema design, indexing strategies, query optimization, and user permissions without the overhead of managing underlying database infrastructure.

What managed operational services provide​

Operating high-availability database clusters in-house requires deep operational expertise and continuous vigilance. Managed database platforms offload several critical failure-prone responsibilities:

  • Automated engine patching: The provider applies operating system security updates and minor database version patches during configurable weekly maintenance windows. Major version upgrades, which can introduce breaking syntactic or behavioral changes, are initiated deliberately under team control.
  • Continuous backups and point-in-time recovery: Rather than relying exclusively on daily batch dumps that can lock tables and lose changes between runs, managed databases combine automated periodic storage snapshots with continuous transaction log (WAL or binary log) streaming into multi-zone object storage. This enables (PITR), allowing operators to restore a brand-new database instance to any specific second within a configurable retention window (typically up to 35 days). If an erroneous schema migration or accidental table deletion occurs at 14:07:22, the system can be restored precisely to 14:07:21.
  • Multi-zone automated failover: By configuring a in an independent availability zone, the provider establishes synchronous replication of all database writes. If the primary instance suffers a hardware fault, kernel panic, or data center loss, the managed platform automatically detects the failure, promotes the standby replica to primary status, and updates the database DNS endpoint. This automated failover typically completes within 60 to 120 seconds, resulting in brief transient connection drops rather than prolonged downtime or data loss.
  • Built-in operational telemetry: CPU utilization, memory pressure, active connection counts, slow query logs, and disk capacity metrics are tracked automatically and piped into centralized monitoring dashboards without requiring third-party monitoring agents.

Replicas and scaling​

A high-availability standby replica exists exclusively as a passive failover target; on most managed platforms, it cannot serve client read traffic. To scale read throughput, organizations provision one or more instances.

Read replicas synchronize asynchronously with the primary instance via streaming replication. Read-heavy analytical workloads, business intelligence dashboards, and search queries can be offloaded to read replicas, preserving compute and memory capacity on the primary for write transactions. Because replication is asynchronous, replicas experience slight replication lag—typically milliseconds under normal conditions, but potentially seconds during massive write bursts. Read-after-write operations requiring strict freshness (such as displaying an updated user balance immediately after checkout) must query the primary directly.

While read replicas allow horizontal scaling of read operations, traditional relational engines cannot scale write throughput horizontally across multiple instances without significant architectural changes or specialized distributed engines.

Connection management​

Every active database connection consumes memory and process overhead on the database server. In modern cloud architectures—where containerized microservices scale dynamically and serverless functions spin up rapidly—uncoordinated connection creation can quickly exhaust database connection pools and trigger widespread connection rejections.

A (such as PgBouncer or a cloud provider's managed database proxy) resolves this challenge. Positioned between application tiers and the database, the pooler maintains a compact, long-lived pool of established backend database connections while multiplexing thousands of incoming client requests over them. Deploying a connection pooler early in application architecture protects the database from connection storms during sudden traffic spikes.

Provisioned sizing and serverless tiers​

Standard managed databases are sized similarly to virtual machines: by allocated CPU cores, RAM, and attached persistent block storage with defined IOPS baselines. While storage volumes can be configured to automatically expand as data grows, changing CPU or memory allocations requires an instance restart, which should be scheduled during maintenance windows.

Cloud providers also offer serverless database tiers that automatically adjust CPU and memory capacity in response to real-time query demand, scaling compute down or pausing completely during periods of inactivity. Serverless tiers offer compelling cost efficiencies for development clusters, testing environments, and intermittent batch workloads. However, for predictable, steady-state production systems, provisioned instances provide lower hourly unit costs and avoid the latency delays associated with scaling up from an idle state.

Before a schema migration

Protect transactional data by verifying point-in-time recovery points before running schema migrations.

  1. Verify that automated backups and continuous logging are active and record the exact timestamp before starting.
  2. Test large schema transformations, index creations, and data backfills on an isolated restored database clone first.
  3. Execute production migrations during a designated maintenance window or low-traffic operational period.
  4. If a migration corrupts data or locks tables unexpectedly, restore to the recorded timestamp rather than attempting ad-hoc manual rollbacks.

When SQL is the right answer​

Relational databases remain the default recommendation for transactional business systems: they provide unmatched data integrity, ACID consistency, complex relational joins, and decades of mature tooling and operational knowledge.

Relational databases reach their natural limits in three specific scenarios: when write volume fundamentally exceeds the throughput of a single vertically scaled primary node, when data structures are entirely unstructured and evolve unpredictably across records, or when access patterns consist strictly of massive point-lookups requiring sub-10-millisecond latency at global scale. For those specialized access patterns, distributed NoSQL architectures become appropriate.

Terms introduced​

  • Managed database: a database engine installed, patched, backed up, and failed over by the provider.
  • Point-in-time recovery: restoring a database to any moment inside the retention window, from snapshots plus the change log.
  • Standby replica: a copy in another zone that the provider fails over to, usually not readable.
  • Read replica: a readable copy that lags the primary slightly, used to move heavy reads off it.
  • Connection pooler: a proxy that holds a fixed number of database connections and lends them to callers.

How providers do it

Managed relational database platforms provide automated maintenance, point-in-time recovery, and multi-zone high availability for standard open-source and commercial database engines. Each cloud provider also offers proprietary database engines engineered around specialized distributed storage architectures.

ConceptAWSAzureGoogle Cloud
Managed PostgreSQL and MySQLAmazon RDSAzure Database for PostgreSQL / MySQL Flexible ServerGoogle Cloud SQL
Managed Microsoft SQL ServerAmazon RDS for SQL ServerAzure SQL Database, Azure SQL Managed InstanceCloud SQL for SQL Server
Proprietary cloud-native engineAmazon AuroraAzure SQL Database HyperscaleGoogle Cloud AlloyDB
Multi-zone standby replicaMulti-AZ deploymentZone-redundant high availabilityHigh availability (HA) configuration
Asynchronous read scalingRead replicaRead replica, geo-replicaRead replica
Point-in-time recovery (PITR)Continuous automated backups with transaction logsAutomated backups with log replayAutomated backups with binary logging / WAL streaming
Managed connection poolingAmazon RDS ProxyBuilt-in PgBouncer (Flexible Server)Managed connection pooling (Cloud SQL)
Autoscaling serverless tierAmazon Aurora Serverless v2Azure SQL serverless compute tierNo serverless compute tier for Cloud SQL
Horizontally scalable distributed SQLAmazon Aurora DSQLNo native distributed SQL engineGoogle Cloud Spanner

Every product name and technical mapping above is confirmed against provider documentation. Specific regional availability for Aurora DSQL and configuration parameters for managed connection poolers are marked unconfirmed in the provider tabs below.

Horizontal write scaling remains a fundamental architectural dividing line:

  • Traditional relational engines—such as standard RDS, Cloud SQL, and Azure Database Flexible Server—rely on a single primary writer instance, scaling horizontally only for read operations via asynchronous replicas.
  • Google Cloud Spanner and AWS Aurora DSQL overcome this physical limit by decoupling compute from distributed, Paxos-coordinated storage layers, delivering horizontal write scaling with multi-region transactional consistency. Azure addresses global multi-region write workloads primarily through Azure Cosmos DB, which provides NoSQL interfaces rather than a native distributed relational engine.

What this maps to: Amazon RDS (Relational Database Service) runs PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, and Db2 as managed instances. Amazon Aurora is AWS's own storage engine, compatible with PostgreSQL and MySQL. Its replication and pricing model is different from RDS's.

ConceptOn AWSStatus
Managed databaseAn RDS DB instance, sized by instance class and storageconfirmed
Standby replicaMulti-AZ deployment. RDS keeps a synchronous standby in another AZ and moves the endpoint on failureconfirmed
Read replicaRead replica, in the same or another region. Multi-AZ DB cluster deployments give two readable standbysconfirmed
Point-in-time recoveryAutomated backups with a retention period; restore to any second inside itconfirmed
Connection poolerRDS Proxy, sitting in front of RDS or Aurora, with IAM authenticationconfirmed
Serverless tierAurora Serverless v2, scaling capacity units up and down with loadconfirmed
Aurora's differenceStorage is a shared cluster volume replicated across three AZs; replicas read the same storage rather than a copy, so they lag by millisecondsconfirmed
Distributed SQLAurora DSQL, a serverless distributed PostgreSQL-compatible databaseunconfirmed; check regional availability
MaintenanceA weekly maintenance window you set; minor version upgrades can be automaticconfirmed

Their vocabulary

Standard termTheir term
StandbyMulti-AZ
SnapshotDB snapshot
FailoverFailover, done by moving the DNS endpoint

Where to look

RDS Performance Insights shows load by query. For failovers and maintenance, look at the Events tab on the instance.

Last verified: never.

Check your understanding

0 of 4 answered

  1. A migration at 14:07 dropped the wrong column. What does point-in-time recovery let you do?
  2. Which of these can a relational database not solve by adding read replicas?
  3. What is the difference between a standby replica and a read replica?
  4. Twenty copies of a service each hold a pool of twenty database connections. What is the problem, and what fixes it?