RDS versus Aurora PostgreSQL
Why you care
Every Python ETL job you write eventually writes to a database. In a product company that database is almost always managed — RDS or Aurora. Picking the wrong one is not a one-time decision: you will pay for it in failover downtime, read-replica costs, or a storage ceiling hit at the worst possible moment. Interviewers ask "RDS or Aurora, and why?" because the answer reveals whether you understand the failure modes or whether you just picked the console default. Half the "job ran but data is missing" incidents in managed Postgres come from not understanding what happens to an open connection during a failover.
Explain like I'm 5
RDS is a managed delivery truck: one driver makes deliveries, a spare driver waits on the side doing nothing. If the first driver has a heart attack, the spare takes fifteen minutes to learn the route, start the truck and get going. Aurora is a shared warehouse: the shelves exist in three buildings at once, every forklift can read from any building, and if the head forklift breaks down, another one is already parked in front of the same shelves and takes over in seconds because the shelves never moved.
Core concept
The fundamental split is storage architecture.
- RDS PostgreSQL uses an EBS volume attached to the instance. In Multi-AZ mode, AWS keeps a synchronous hot standby in a second AZ with its own EBS copy. The standby does nothing until failover — it cannot serve reads. Failover is DNS-based: the standby is promoted, the DNS record is updated, and application connections must reconnect. This typically takes 60–120 seconds.
- Aurora PostgreSQL separates compute from storage. The cluster volume stores six copies of every write across three AZs automatically, regardless of how many DB instances you run. There is no separate EBS per instance; every writer and reader accesses the same distributed volume. Read replicas are endpoints, not independent data copies. Failover promotes an existing replica to writer; because the storage never moves, this typically takes under 30 seconds (or under 10 minutes if there are no replicas — always provision at least one).
Key numbers (checked against AWS docs, September 2026; AWS has raised this ceiling before, so confirm the current limit for your engine version before publishing or relying on it):
- Aurora max cluster volume: 128 TiB on older engine versions; up to 256 TiB on newer ones (Aurora PostgreSQL 15.13+/16.9+/17.5+, Aurora MySQL 3.10+). Auto-grows; no pre-provisioning. Check your cluster's engine version against the current size-limits page before treating either number as a hard ceiling.
- Aurora max read replicas: 15 per cluster.
- RDS read replicas: 15 per source (quota page; adjustable).
- Aurora failover with replica: typically < 30 seconds. Without replica: typically < 10 minutes.
- RDS Multi-AZ failover: typically 60–120 seconds.
Endpoints matter. Aurora gives you two stable endpoints: the cluster endpoint (always the current writer) and the reader endpoint (round-robins across all read replicas). Always point ETL writers at the cluster endpoint. Never hardcode a replica's instance endpoint — it does not survive a promotion.
Use RDS when cost is the constraint, one-minute failover is acceptable, and you need at most a handful of read replicas. Use Aurora when you need sub-30-second failover, more than five readable replicas, or you are approaching storage limits.
Under the hood
Aurora's quorum write: the writer node sends each 10 GB chunk (a "protection group segment") to six storage nodes. The write is acknowledged after four of the six nodes confirm. This means Aurora can lose an entire AZ (two storage nodes) and still write without pausing. There is no "replica lag" at the storage layer — the data is durable before the writer acknowledges. Read replicas do experience a brief metadata propagation delay (buffer-cache invalidation messages) that can reach low single-digit seconds on very busy writers. That is why a read-after-write check sent to the reader endpoint can return stale data — see Replicas, failover and recovery for how to manage this.
Minimal example RDS vs Aurora decision tree
def recommend_engine(failover_budget_s: int, read_replicas: int, storage_tib: float) -> tuple:
reasons = []
if failover_budget_s < 60:
reasons.append("sub-60-s failover -> Aurora")
if read_replicas > 5:
reasons.append(f"{read_replicas} read replicas -> Aurora (max 15)")
if storage_tib > 64:
reasons.append(f"{storage_tib} TiB storage -> Aurora (128-256 TiB depending on engine version)")
if reasons:
return "Aurora", reasons
return "RDS", ["no hard constraint; lower cost wins"]
for args in [
(30, 2, 10),
(120, 12, 20),
(120, 3, 70),
(120, 3, 10),
]:
engine, why = recommend_engine(*args)
print(f"failover={args[0]}s replicas={args[1]} storage={args[2]}TiB => {engine}: {why[0]}")failover=30s replicas=2 storage=10TiB => Aurora: sub-60-s failover -> Aurora
failover=120s replicas=12 storage=20TiB => Aurora: 12 read replicas -> Aurora (max 15)
failover=120s replicas=3 storage=70TiB => Aurora: 70 TiB storage -> Aurora (128-256 TiB depending on engine version)
failover=120s replicas=3 storage=10TiB => RDS: no hard constraint; lower cost winsIn production Endpoint routing for ETL and reporting
import urllib.parse
def build_dsn(host: str, port: int, db: str, user: str, pw: str) -> str:
pw_enc = urllib.parse.quote(pw, safe="")
return f"postgresql://{user}:{pw_enc}@{host}:{port}/{db}?sslmode=require"
def endpoint_role(host: str) -> str:
if ".cluster-ro-" in host:
return "aurora-reader"
if ".cluster-" in host:
return "aurora-writer"
return "rds-instance"
# Illustrative hostnames; replace with your cluster endpoints
ENDPOINTS = {
"etl_write": "mydb.cluster-abc123.ap-south-1.rds.amazonaws.com",
"report_read": "mydb.cluster-ro-abc123.ap-south-1.rds.amazonaws.com",
"rds_primary": "mydb.abc123.ap-south-1.rds.amazonaws.com",
}
for label, host in ENDPOINTS.items():
dsn = build_dsn(host, 5432, "finance", "etl", "s3cr3t")
role = endpoint_role(host)
print(f"{label:12s}: role={role}")etl_write : role=aurora-writer
report_read : role=aurora-reader
rds_primary : role=rds-instanceSee it happen Aurora storage vs RDS Multi-AZ on failover
- Aurora writer commits by getting a quorum from the distributed storage layer; replicas get metadata updates after.
- Both replicas can serve SELECT queries; the reader endpoint round-robins across them.
- Writer fails. Aurora promotes Reader A (highest priority tier). Storage never moves; failover takes under 30 seconds.
- Compare RDS Multi-AZ: standby has its own EBS copy, is idle, and becomes available after DNS propagation (60-120 s).
In your world
The situation
Your Jenkins ETL job connects to an RDS PostgreSQL Multi-AZ instance, truncates the staging table, and loads 4 million rows. The DBA schedules a maintenance window that triggers a failover test at 02:00, while the job runs.
What actually breaks
Failover takes 90 seconds. Your connection pool has a connect timeout of 30 seconds and no retry logic. The truncate commits; the load dies halfway through. Staging is empty. The downstream finance report runs at 06:00 and returns zeros, not an error.
What you'd do on Monday
Add exponential-backoff retry around the load loop. For the long term, switch to Aurora with one replica: the failover finishes in under 30 seconds, before most timeouts expire. Set JDBC connectTimeout=60 and socketTimeout=300.
import time
import psycopg2
def connect_with_retry(dsn: str, max_attempts: int = 5) -> psycopg2.extensions.connection:
delay = 2.0
for attempt in range(1, max_attempts + 1):
try:
return psycopg2.connect(dsn, connect_timeout=60)
except psycopg2.OperationalError as e:
if attempt == max_attempts:
raise
print(f"attempt {attempt} failed: {e}. Retry in {delay:.0f}s")
time.sleep(delay)
delay = min(delay * 2, 30.0)Imagine you work at Paytm
The numbers
Illustrative: 2M rows × ~500 bytes ≈ 1 GB nightly load per pipeline, plus two reconciliation queries running concurrently from a reporting team. A single RDS Multi-AZ writer handles all three workloads; a long query from reconciliation during the ETL load saturates CPU.
What the Scenario-A answer does here
The retry fix protects the ETL job against a failover, but the reporting traffic stays on the primary writer. Promoting the Multi-AZ standby after a failover gives back one writer — no read offload.
What flips at this scale
At this scale Aurora's reader endpoint pays for itself. Reconciliation queries move to replicas; the ETL writer gets the full CPU. RDS Proxy in front of the cluster endpoint absorbs connection spikes and hides DNS propagation delays from the application.
The trade-off you'd defend
I would choose Aurora over RDS Multi-AZ for any payment pipeline running concurrent analytics against the same DB, accepting the higher per-instance-hour cost in exchange for read-endpoint isolation and sub-30-second failover. The retry logic is identical either way.
Illustrative figures chosen for the arithmetic; not Paytm-published numbers.
Gotchas
Hardcoding the replica's instance endpoint
After a failover, writes start failing with "cannot write to a read-only instance."
The job connects to a specific replica's instance endpoint, which became the writer after promotion. The code is pointed at the wrong role.
Always use the Aurora cluster endpoint for writes. The cluster endpoint follows the current writer automatically.
RDS Multi-AZ standby treated as a read replica
Attempt to read from the standby endpoint returns a connection error or nothing.
RDS Multi-AZ standby is a failover target only. It does not accept connections for reads. Read offload on RDS requires a separately created read replica.
Create a read replica explicitly and point reporting traffic at its endpoint, or migrate to Aurora to get the reader endpoint.
Aurora storage growing unexpectedly
Storage costs spike even though the data size should be stable.
Aurora auto-grows the cluster volume but does not auto-shrink below the high-water mark in older engine versions. Large deletes or DROP TABLE free logical space but not billing space until the cluster is optimised or rebuilt.
Use Aurora I/O-Optimised storage if write-heavy, or run an Aurora clone and switch to reduce the billing volume size. Check the current engine version's shrink behaviour before planning.
Tricky questions
No, the reader endpoint always works because replicas are available.
During the promotion of a replica to writer, that specific replica is temporarily unavailable as a reader. The reader endpoint stops routing to it briefly. If your cluster has only one replica, the reader endpoint has no target during the promotion window. Provision at least two replicas to ensure the reader endpoint stays available.
Yes, the standby is always in sync so failover is immediate.
The DNS record must propagate, and application connection pools must reconnect. Even with an already-synced standby, the application sees a 60–120 second window where new connections fail. Retry logic is mandatory.
Scenario question
You're handed a report saying a Python ETL job failed three times last quarter due to database failovers. The job runs 45 minutes. The RDS instance is Multi-AZ. You cannot change the job code. How do you fix the failure rate?
Model answer
I would migrate the endpoint to Aurora PostgreSQL and provision at least one read replica. The failover time drops from 60-120 seconds to under 30 seconds, and the Aurora cluster endpoint follows the promotion automatically. Even without touching the job code, the shorter failover window means a standard 60-second JDBC connect timeout survives. I would also add an RDS Proxy in front of the cluster endpoint, because the proxy maintains a pooled connection on behalf of the application and shields it from DNS propagation during failover. The trade-off is cost: Aurora and the proxy both bill per instance-hour. If the budget disallows it, the minimum fix is to increase the JDBC socketTimeout to 90 seconds, which buys survival for the current 60-120s window at the risk of slower error detection on genuine query problems.
Interview corner
CommonWhat is the key architectural difference between RDS PostgreSQL and Aurora?
TrickYou point your ETL job at an Aurora replica's instance endpoint. After a failover it starts reporting "read-only instance." Why?
Experience checkA failover happened during your ETL job. How long was the data gap?
DesignWhen would you choose RDS over Aurora for PostgreSQL?
Memory hook
The RDS spare driver has to learn the route before it can drive; the Aurora forklift is already parked at the same shelves. That is the whole difference in failover time.
Use the Aurora cluster endpoint for writes. A replica's instance endpoint does not survive promotion.
Self-check
How many storage copies does Aurora maintain, and how are they distributed?
Six copies across three Availability Zones, stored in the cluster volume regardless of how many instances you run.
What is the typical Aurora failover time with at least one replica versus without any replica?
Under 30 seconds with a replica; up to 10 minutes without one, because Aurora must recreate the primary instance.
Can the RDS Multi-AZ standby serve read queries?
No. It is a failover target only. To offload reads from RDS, you need a separately created read replica.
Using the AWS pricing calculator (https://calculator.aws), find the hourly price for a db.r7g.large Aurora PostgreSQL instance versus a db.r7g.large RDS PostgreSQL Multi-AZ instance in ap-south-1. Calculate the annual cost difference for one writer plus one read replica (Aurora) versus one Multi-AZ pair (RDS). Report both totals.