Scaling PostgreSQL in the Cloud: Deep Dive into Amazon Aurora and Cloud SQL Read Replicas

2 days ago
4 min read

As web applications and enterprise analytical platforms grow, database read traffic often scales far faster than write traffic. Complex analytical queries, reporting dashboards, and high-concurrency read operations can quickly saturate the CPU and memory of a single primary PostgreSQL instance.

To overcome these compute bottlenecks, cloud providers offer horizontal read scaling using Read Replicas. However, the underlying storage and replication architecture varies dramatically depending on whether you deploy a traditional managed DBaaS like Google Cloud SQL for PostgreSQL or a cloud-native storage engine like Amazon Aurora PostgreSQL.

This guide explores the internal replication mechanics, latency behaviors, auto-scaling patterns, and failover topologies of read replicas across Amazon Aurora and Google Cloud SQL.

1. Architectural Foundations: Block Storage vs. Decoupled Cloud Storage

Understanding how read replicas scale requires examining how storage is decoupled from compute in each cloud environment.

+-----------------------------------------------------------------------+
|                TRADITIONAL DBaaS (Google Cloud SQL)                   |
|                                                                       |
|   [ Primary Instance ]  --(WAL Stream over Network)--> [ Read Replica ] |
|   [ Attached Disk    ]                                 [ Attached Disk]|
|   (Duplicate Storage & Write Costs Per Replica)                       |
+-----------------------------------------------------------------------+

+-----------------------------------------------------------------------+
|                 CLOUD-NATIVE DBaaS (Amazon Aurora)                    |
|                                                                       |
|   [ Primary Instance ]   [ Read Replica 1 ]   [ Read Replica 2 ]      |
|             \                   |                   /                 |
|              +------------------+------------------+                  |
|                                 v                                     |
|            [ Shared Distributed Storage Volume (6-Way) ]             |
|   (Zero Disk Duplication / Sub-10ms Replication Lag)                  |
+-----------------------------------------------------------------------+

Google Cloud SQL (Physical Asynchronous Streaming Replication)

Google Cloud SQL follows traditional PostgreSQL replication architecture. The primary instance streams Write-Ahead Log (WAL) records over the cloud network to one or more independent read replicas.

Storage Duplication: Each read replica requires its own provisioned Persistent Disk volume.
I/O Overhead: Read replicas independently apply WAL records to update their local data blocks, creating local disk write activity on the replica instance.

Amazon Aurora (Distributed Log-Structured Storage)

Amazon Aurora completely decouples storage from compute. Instead of replicating WAL logs to separate storage volumes, all Aurora instances (primary and read replicas) share a single, distributed storage volume spanning 3 Availability Zones (AZs).

Shared Storage Engine: Read replicas do not maintain separate storage copies. They read directly from the shared storage layer.
Zero Storage Amplification: Adding an Aurora read replica incurs zero additional storage cost, as no data disk duplication takes place.

2. Replication Lag & Performance Comparison

Replication lag—the time delta between a write transaction committing on the primary and becoming visible on a read replica—dictates whether an application can safely read from replicas.

Feature / Metric	Amazon Aurora PostgreSQL	Google Cloud SQL for PostgreSQL
Replication Engine	Shared Storage Memory Updates	PostgreSQL Physical Streaming Replication
Average Replication Lag	Sub-10 to 20 milliseconds	100ms to several seconds (under heavy load)
Storage Cost Factor	1x Storage Cost (Shared across replicas)	Nx Storage Cost (Per replica volume)
Max Read Replicas	Up to 15 Aurora Replicas	Up to 20 Read Replicas
Replica CPU Impact	Minimal (No WAL replay on disk)	Higher (Continuous WAL replay on disk)

3. Connection Endpoints & Load Balancing Strategies

Routing client traffic between read and write endpoints is crucial for effective scaling.

Amazon Aurora Endpoint Topology

Aurora natively provides managed cluster endpoints that abstract individual instance IP addresses:

Cluster Endpoint (Writer): Points directly to the current primary instance for INSERT, UPDATE, and DELETE operations.
Reader Endpoint: Automatically load-balances incoming read connections (SELECT) across all active Aurora Read Replicas using round-robin DNS.
Custom Endpoints: Allows grouping specific replicas (e.g., dedicated high-memory instances for heavy analytical queries) into specialized endpoints.

Google Cloud SQL Load Balancing Topology

Google Cloud SQL provides individual IP addresses for each read replica.

Proxy-Based Routing: Applications typically connect through Cloud SQL Auth Proxy or an external connection proxy (like ProxySQL or PgBouncer) to distribute read queries across replica IP pools.

4. Auto-Scaling Read Replicas in Production

Both cloud platforms support dynamic auto-scaling to handle fluctuating read query traffic.

Auto-Scaling Aurora Replicas

Aurora Auto Scaling dynamically adds or removes read replicas based on customized CloudWatch metrics (such as average CPU utilization or target connection counts).

# AWS CLI Auto Scaling Configuration Concept
ScalingPolicy:
  TargetMetric: CPUUtilization
  TargetValue: 70.0
  CoolDownPeriods:
    ScaleOut: 300 seconds
    ScaleIn: 600 seconds
  MinCapacity: 2
  MaxCapacity: 8

Auto-Scaling Considerations

Provisioning Speed: Adding a new Aurora Read Replica takes 2 to 5 minutes because it attaches directly to the pre-existing shared storage volume without needing data disk snapshots or historical WAL replaying.
Cloud SQL Replica Provisioning: Provisioning a new Cloud SQL read replica requires taking a storage snapshot of the primary and streaming missing WAL records, which can take 15 to 45+ minutes depending on total dataset size.

5. Architectural Best Practices for DBAs

Handle Stale Reads in Application Code: Even with sub-10ms lag in Aurora, replication is inherently asynchronous. Implement "Read-Your-Own-Writes" logic in your application framework by routing post-mutation queries to the primary writer for a short window.
Monitor ReplicaLag Metrics: Set strict alerting thresholds on replica lag metrics (AuroraReplicaLag in AWS CloudWatch or replication_lag in GCP Cloud Monitoring) to prevent stale reporting data.
Isolate Analytical Heavy Lifters: Prevent massive analytical GROUP BY queries from locking out fast OLTP reads by isolating dedicated read replicas via Custom Endpoints or dedicated instance classes.

Summary

Choose Amazon Aurora PostgreSQL if you require sub-10ms replication lag, instant replica auto-scaling, cost-effective storage (shared volume), and automated DNS-based read load balancing.
Choose Google Cloud SQL Read Replicas for standard PostgreSQL compatibility, multi-cloud operational parity, or regional compliance frameworks where traditional streaming replication model matches your existing operational runbooks.