Cloud / Amazon Aurora Interview questions

Amazon Aurora is a fully managed relational database from AWS that is compatible with MySQL and PostgreSQL. Your existing drivers, tools, and SQL keep working, but the storage layer underneath is a distributed, log-structured design built for the cloud.

Compute and storage are separated. Database instances run queries, while a shared cluster volume spans three Availability Zones and keeps six copies of your data. AWS cites up to 5x the throughput of standard MySQL and 3x of standard PostgreSQL on comparable hardware.

• Storage grows automatically up to 128 TiB (check the limit for your engine version)
• Up to 15 low-lag read replicas
• Continuous backup to Amazon S3
• Automated failover, usually within about 30 seconds

Aurora offers two engine editions: Aurora MySQL-compatible and Aurora PostgreSQL-compatible. Aurora MySQL version 3 is compatible with MySQL 8.0, and version 2 with MySQL 5.7, which is now in extended support.

Both editions share the same storage architecture, but some features exist for only one of them:

An Aurora DB cluster is the basic unit of deployment. It consists of one or more DB instances plus a single cluster volume that all of those instances share.

A standard cluster has exactly one writer (primary) instance that handles reads and writes, and up to 15 Aurora Replicas that serve reads and act as failover targets. Instances hold no permanent data of their own, which is why adding or replacing one is fast.

graph TD
  W["Writer instance"] --> V[(Cluster volume: 6 copies, 3 AZs)]
  R1["Aurora Replica 1"] --> V
  R2["Aurora Replica 2"] --> V

The cluster volume is the virtual, SSD-backed storage layer shared by all instances in an Aurora cluster. It is split into 10 GB segments, and each segment is stored as six copies, two in each of three Availability Zones.

You never provision its size. It grows in 10 GB increments as data is added, and since dynamic resizing was introduced it also shrinks when you drop tables or databases. You pay only for what is actually used.

• Maximum size is 128 TiB on most versions
• Replication is done by the storage layer, not by the database engine
• Backups are taken continuously from it into S3

Aurora gives you four kinds of endpoints so applications never have to track individual instances:

The cluster endpoint is updated automatically when a failover happens, so it should be your default for writes.

An Aurora Replica is a read-only instance in the same cluster that reads from the same shared cluster volume as the writer. It does not keep its own copy of the data and does not replay the full change stream onto a separate disk.

Because of that, replica lag is usually measured in milliseconds. A cluster can hold up to 15 replicas across Availability Zones, and each one is also a candidate to be promoted if the writer fails. Replicas can have different instance sizes from the writer.

The writer sends redo records to replicas only so they can refresh pages in their buffer cache.

Aurora Serverless v2 is a deployment mode where each instance scales its compute and memory up and down automatically, based on load, in increments of 0.5 ACU. It scales in place within seconds and without dropping connections.

You set a minimum and maximum capacity for the cluster. Recent versions can go down to 0 ACU and pause automatically when idle, so you pay only for storage during quiet periods (resume takes a few seconds).

• Supports readers, Multi-AZ failover, and Global Database
• Can be mixed with provisioned instances in one cluster
• Billed per ACU-second

An ACU is the unit Serverless v2 uses to measure capacity. One ACU provides roughly 2 GiB of memory with matching CPU and networking. Billing is per ACU-hour, calculated by the second.

You define the scaling range per cluster, and every Serverless v2 instance moves within it:

aws rds modify-db-cluster \
  --db-cluster-identifier mycluster \
  --serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=16

Set the minimum high enough to keep your buffer cache warm, and the maximum low enough to cap runaway cost.

Aurora Global Database spans multiple AWS Regions. One primary Region accepts writes, and up to five secondary Regions hold read-only clusters that are kept in sync by storage-level replication.

Replication runs on dedicated infrastructure, so it does not load the primary's instances, and lag is typically under one second. It serves two purposes: low-latency local reads for global users, and disaster recovery with a cross-Region failover option.

Write forwarding can let a secondary cluster send writes to the primary Region.

Backtrack lets you rewind an Aurora MySQL cluster to an earlier point in time without restoring from a backup. It is handy for undoing a bad DELETE or a faulty deployment in minutes.

You enable it when creating or restoring the cluster and choose a target window of up to 72 hours. Aurora keeps change records for that window, which are billed as storage. You can rewind backward or forward within the window, and the operation affects the whole cluster, not single tables.

It is not available for Aurora PostgreSQL.

Fast cloning creates a new Aurora cluster from an existing one in minutes, regardless of data size. It uses a copy-on-write protocol: the clone initially points to the same storage pages as the source, and a page is copied only when either side modifies it.

That means a fresh clone uses almost no extra storage. It suits dev and test copies, running heavy reports without touching production, and rehearsing a schema change or version upgrade.

aws rds restore-db-cluster-to-point-in-time \
  --source-db-cluster-identifier prod \
  --db-cluster-identifier prod-clone \
  --restore-type copy-on-write --use-latest-restorable-time

Parallel Query is an Aurora MySQL feature that pushes filtering, projection, and some aggregation work for large scans down to the storage layer. Thousands of storage nodes process their own data in parallel and return only the matching rows.

This cuts network traffic and CPU on the instance and avoids flooding the buffer pool with scanned pages. It speeds up analytical queries on fresh, large tables without needing a separate data warehouse.

It does not help short OLTP lookups, which are already served from the buffer cache or by index.

Aurora I/O-Optimized is a cluster configuration with no charges for read and write I/O. In exchange you pay a higher instance price (about 30% more) and a higher per-GB storage price than Aurora Standard.

Standard bills per million I/O requests, which can swing wildly for busy workloads. I/O-Optimized makes the bill predictable. A common rule of thumb is to consider it once I/O charges exceed roughly 25% of your total Aurora spend.

You can switch from Standard to I/O-Optimized at any time, and switch back once every 30 days.

Limitless Database is an Aurora PostgreSQL-compatible option that scales writes and storage horizontally by sharding across many instances, while the application sees one logical database.

It is organised into a shard group made of transaction routers and shards. Table types decide data placement:

Choose the shard key carefully, because joins and transactions that stay on one shard are far cheaper.

Babelfish lets an Aurora PostgreSQL cluster understand the SQL Server TDS wire protocol and T-SQL. Applications written for SQL Server can connect with their existing drivers (on port 1433) with few or no code changes.

It is mainly a migration tool: you move off SQL Server licensing while keeping most of the app intact, and later start using native PostgreSQL features on the same data. The Babelfish Compass tool scans your SQL scripts and reports unsupported constructs before you start.

Aurora backs up the cluster volume continuously and incrementally to Amazon S3, with no performance impact on the instances and no backup window. You choose a retention period of 1 to 35 days.

Within that period you can do point-in-time restore to any second, up to the latest restorable time (typically within the last five minutes). Manual snapshots are separate: they stay until you delete them and can be copied across Regions or shared with other accounts.

Every restore creates a new cluster with a new endpoint. It never overwrites the original.

The Data API lets you run SQL over an HTTPS endpoint using the AWS SDK or CLI, with no JDBC/ODBC driver and no persistent connection. Credentials come from AWS Secrets Manager, and access is controlled with IAM.

aws rds-data execute-statement \
  --resource-arn arn:aws:rds:us-east-1:111122223333:cluster:mycluster \
  --secret-arn arn:aws:secretsmanager:us-east-1:111122223333:secret:dbcreds \
  --database mydb --sql "select now()"

It fits Lambda functions and other short-lived clients well, because there is no connection pool to manage.

In the console, the flow is the same as the CLI flow below, just with forms:

1. Pick the engine edition and version.
2. Choose a template (production or dev/test) and set credentials, ideally managed by Secrets Manager.
3. Select provisioned instance class or Serverless v2 capacity.
4. Choose a VPC, DB subnet group, and security group.
5. Decide on Multi-AZ replica, encryption, and backup retention.

aws rds create-db-cluster --db-cluster-identifier demo --engine aurora-postgresql \
  --master-username admin --manage-master-user-password --storage-encrypted

aws rds create-db-instance --db-instance-identifier demo-1 \
  --db-cluster-identifier demo --engine aurora-postgresql --db-instance-class db.r6g.large

Note that create-db-cluster alone creates only storage. You still need at least one instance to run queries.

Zero-ETL is a managed integration that continuously replicates data from Aurora to Amazon Redshift (and to SageMaker lakehouse targets) so you can run analytics almost in real time, typically within seconds of a commit.

You create an integration between the source cluster and the target, and AWS handles change capture, transfer, and schema mapping. There are no pipelines, jobs, or custom code to maintain, and analytic queries stay off your production instances.

Every Aurora Replica has a priority tier from 0 (highest) to 15 (lowest). When the writer fails, Aurora promotes the replica with the lowest tier number.

If several replicas share a tier, Aurora picks the largest instance, and if sizes also match it chooses arbitrarily. Give your biggest replica in a healthy AZ tier 0 so the new writer can carry the full load, and put small analytics replicas on a high tier number.

Encryption at rest is chosen when the cluster is created by selecting an AWS KMS key (the default aws/rds key or your own). It covers the cluster volume, replicas, automated backups, snapshots, and logs.

aws rds create-db-cluster ... --storage-encrypted --kms-key-id alias/aurora-key

You cannot switch encryption on for an existing cluster. Instead, snapshot it, copy the snapshot with a KMS key, and restore. For data in transit, use TLS and enforce it with rds.force_ssl (PostgreSQL) or require_secure_transport (MySQL).

Enable IAM authentication on the cluster, create a database user tied to IAM, and connect with a short-lived token instead of a password. The token is valid for 15 minutes and TLS is required.

-- MySQL
CREATE USER app_user IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
-- PostgreSQL
GRANT rds_iam TO app_user;

TOKEN=$(aws rds generate-db-auth-token --hostname $HOST --port 5432 \
  --region us-east-1 --username app_user)

The caller's IAM policy must allow rds-db:connect for that user. Tokens only matter at connect time, so open connections keep working after expiry.

These are the metrics worth alarming on first:

Pair these with Performance Insights to see which queries and wait events cause the load.

The cluster volume is cut into 10 GB segments, and every segment is kept as six copies, two per AZ across three AZs. The database instance sends each redo record to all six storage nodes in parallel, and the storage layer handles replication itself.

Because replication happens below the engine, replicas do not need their own copy, and the loss of an entire AZ removes only two of six copies. Writes still succeed with the four that remain.

Aurora is part of the RDS family, but its architecture differs where it matters: the storage layer is distributed and shared, instead of an EBS volume attached to one instance.

For a small, steady workload RDS is often cheaper. Aurora pays off when you need fast failover, many replicas, or heavy write throughput.

Aurora uses a 4/6 write quorum and a 3/6 read quorum across the six copies of each segment. A write is durable once four storage nodes acknowledge it. These numbers satisfy the quorum rules: read plus write exceeds six, and write exceeds half of six.

In practice the engine rarely runs a read quorum. It tracks which segments are current and reads from one of them. The 3/6 read quorum is what matters during recovery, when the engine must rebuild the latest consistent state.

A traditional engine writes redo logs, binlogs, full data pages, and a double-write buffer, and every one of those goes through mirrored EBS, which multiplies network and disk I/O. Aurora removes that amplification by treating the log as the database.

The writer ships only redo records. Storage nodes apply them to pages in the background and materialise pages when needed, so the engine does no checkpoints and no full-page writes. Crash recovery is quick because storage nodes replay redo continuously and in parallel, instead of one long replay at startup.

A write travels from the client through the writer to the storage fleet, and commits as soon as a quorum is durable.

sequenceDiagram
  participant C as Client
  participant W as Writer
  participant S as 6 Storage nodes
  participant R as Replicas
  C->>W: INSERT / COMMIT
  W->>S: Redo records (parallel)
  S-->>W: ACK from 4 of 6
  W-->>C: Commit OK
  W-->>R: Redo stream (cache refresh)
  S->>S: Apply redo to pages, back up to S3

1. The writer generates redo records and sends them to all six copies.
2. Four acknowledgements make the commit durable.
3. The client gets its OK without waiting for replicas.
4. Replicas update cached pages from the redo stream.
5. Storage applies redo to pages and streams backups to S3.

When the writer becomes unavailable, Aurora detects it, promotes the replica with the best priority tier, and repoints the cluster endpoint DNS (5-second TTL) to the new writer. Failover usually finishes in about 30 seconds or less.

flowchart LR
  A["Writer fails"] --> B["Detect failure"]
  B --> C["Pick replica by tier and size"]
  C --> D["Promote to writer"]
  D --> E["Cluster endpoint DNS updated"]
  E --> F["Clients reconnect"]

It is quick because the new writer already shares the same storage, so there is no data to copy and little log to replay. Other replicas stay online and keep serving reads.

Replicas do not replicate data. Writer and replicas read the same cluster volume, so a committed change is already visible in storage the moment quorum is reached.

The only work left for a replica is to update pages that are already in its buffer cache, using the redo stream the writer sends. That is why AuroraReplicaLag typically sits in the tens of milliseconds. Compare that with MySQL binlog replication, where a replica replays every transaction, often on a single applier thread.

Lag can still rise if a replica is much smaller than the writer or the write rate spikes.

Both run the same engine and storage. The difference is how compute is sized and billed.

A flat 24x7 workload is usually cheaper on provisioned instances with reservations. Variable traffic favours Serverless v2, and the two can live in the same cluster.

Serverless v1 has reached end of life (March 31, 2025), so new designs should use v2. The differences explain why v2 replaced it.

Choose I/O-Optimized when I/O charges are a large, unpredictable part of the bill, typically above 25% of total Aurora spend. Check VolumeReadIOPs and VolumeWriteIOPs plus Cost Explorer to see your real split. AWS cites savings of up to 40% for I/O-intensive workloads.

Stay on Standard for idle or small databases. Re-evaluate quarterly, since you can switch back once every 30 days.

Replication happens at the storage layer. A replication server in the primary Region reads redo records and ships them to a replication agent in each secondary Region, which writes them to the local cluster volume.

flowchart LR
  W["Primary writer"] --> PV[(Primary volume)]
  PV --> RS["Replication server"]
  RS --> RA["Replication agent, Region 2"]
  RA --> SV[(Secondary volume)]
  SV --> SR["Secondary readers"]

Because the database instances are not involved, the primary's performance is unaffected and lag usually stays under a second. With write forwarding, a secondary can accept a write statement and pass it to the primary.

Switchover is a planned role swap, used for maintenance or DR drills. Failover is the unplanned response to a Regional outage.

aws rds switchover-global-cluster --global-cluster-identifier g1 --target-db-cluster-identifier arn:...
aws rds failover-global-cluster --global-cluster-identifier g1 --target-db-cluster-identifier arn:... --allow-data-loss

After either, update the application to the new writer endpoint, or use the global writer endpoint to avoid this.

Both give you a new, independent cluster. They differ in speed, storage, and reach.

Use a clone for a short-lived dev or test copy of a big production database. Use a snapshot when you need an off-site, archival, or cross-Region copy.

Backtrack rewinds the existing cluster. Point-in-time restore (PITR) builds a new one.

Backtrack is great for a quick undo, but it does not replace backups, because it only covers a short window.

The cluster endpoint always points to the writer and supports reads and writes. The reader endpoint spreads connections across available replicas and accepts reads only.

The balancing is done at connection time through DNS, not per query. A long-lived pooled connection stays on one replica, so large pools can still be uneven. If the cluster has no replica, the reader endpoint points to the writer.

Aurora uses Application Auto Scaling with a target-tracking policy. You register the cluster's replica count as a scalable target, then pick a metric and a target value. The two predefined metrics are average reader CPU and average reader connections.

aws application-autoscaling register-scalable-target --service-namespace rds \
  --resource-id cluster:mycluster --scalable-dimension rds:cluster:ReadReplicaCount \
  --min-capacity 1 --max-capacity 8

When the metric stays above target, a replica is added and joins the reader endpoint. When it falls, replicas are removed after the scale-in cooldown. Set a sensible minimum so failover always has a target.

Send writes to the cluster endpoint and reads to the reader endpoint. Use two connection pools, or a driver such as the AWS Advanced JDBC Wrapper with its read/write splitting plugin, or RDS Proxy reader endpoints.

const writer = new Pool({ host: 'mycluster.cluster-xxxx.us-east-1.rds.amazonaws.com' });
const reader = new Pool({ host: 'mycluster.cluster-ro-xxxx.us-east-1.rds.amazonaws.com' });

Remember replica lag. After a write, a read that must see it should go to the writer, or you accept tiny staleness. Transactions that write must stay entirely on the writer.

Use RDS Proxy when many clients open lots of short-lived connections, which is typical with AWS Lambda, or when connection storms exhaust max_connections. The proxy pools and multiplexes connections, and it can cut failover time because it keeps client connections open while the writer changes.

• Supports IAM authentication and Secrets Manager
• Provides a read-only endpoint for replicas
• Adds cost and a small latency hop

Beware of connection pinning: session state such as temporary tables or SET variables locks a client to one backend connection and reduces the benefit. You may not need it for a few long-lived pooled connections.

Choose it on Aurora MySQL when you run large analytical scans over fresh data and want to avoid maintaining a separate warehouse for them. It works best on queries that read most of a big table with selective filters on non-indexed columns, or large aggregations.

Enable it with the aurora_parallel_query setting, then confirm with EXPLAIN: the plan shows Using parallel query when it is used.

Start by finding out whether the writer, the replica, or one query is responsible.

1. Check AuroraReplicaLag alongside replica CPU and writer VolumeWriteIOPs in CloudWatch.
2. Compare instance sizes. A replica much smaller than the writer cannot apply redo fast enough.
3. Look in Performance Insights for long queries on the replica that block page updates or cause conflicts.
4. Look for bursts of bulk writes, big transactions, or heavy DDL on the writer, and spread them out.
5. Add replicas or move heavy reports to a custom endpoint to reduce read pressure.

If staleness is not acceptable for some reads, send those to the writer.

Aurora's own promotion usually takes under 30 seconds, so slowness is often on the client side or in the topology.

1. DNS caching. A JVM with an infinite DNS cache keeps using the old writer IP. Set a low TTL (about 5 seconds).
2. Connection pools that do not discard broken connections or re-resolve the cluster endpoint.
3. No replica in another AZ, so Aurora has to create a new instance.
4. Use the AWS Advanced JDBC Wrapper failover plugin or RDS Proxy to skip the DNS wait.

Rehearse regularly:

aws rds failover-db-cluster --db-cluster-identifier mycluster

Look at the biggest bill lines first, usually instances, then I/O, then storage.

Also trim idle replicas, and watch cross-Region data transfer for Global Database.

Your data is safe, because it lives in the six-copy cluster volume, not on the instance. But there is no ready target to promote, so Aurora tries to create a replacement instance on a best-effort basis, starting in the original AZ.

That takes much longer than promoting a replica, often several minutes, and it can fail if capacity is short. The endpoint stays the same and applications recover once the new instance is up.

Always keep at least one replica in a different AZ for production.

The method depends on source, size, and acceptable downtime.

For a heterogeneous source, convert the schema first with AWS SCT or DMS Schema Conversion. Before cutover, compare row counts, test queries, and lower DNS TTLs so clients switch quickly.

Use Blue/Green Deployments. Aurora clones your production (blue) cluster into a green copy kept in sync by replication, so you can upgrade and test it safely.

1. Create the blue/green deployment with the target engine version and parameter group.
2. Let green catch up and run tests and pre-checks against it.
3. Switch over. Aurora blocks writes, waits for sync, and swaps endpoints, usually in about a minute or less.
4. Keep the old blue environment for rollback, then delete it.

aws rds create-blue-green-deployment --blue-green-deployment-name upgrade \
  --source arn:aws:rds:us-east-1:111122223333:cluster:prod --target-engine-version 16.4
aws rds switchover-blue-green-deployment --blue-green-deployment-identifier bgd-xxxx

An in-place upgrade is simpler but means downtime, so reserve it for non-critical systems.

The storage fleet is made of nodes with local SSDs. Each 10 GB segment belongs to a protection group of six copies across three AZs. Log records carry a log sequence number (LSN), which lets every node know which records it has.

flowchart TD
  W["Writer sends redo"] --> N1["Node A"]
  W --> N2["Node B"]
  W --> N3["Node C"]
  N1 <-->|gossip missing records| N2
  N2 <-->|gossip| N3
  N1 --> S3[(Backup to S3)]
  N3 -->|failed copy rebuilt from peers| N4["New node"]

If a node missed records, it gossips with its peers to fill the gaps. If a copy is lost or corrupt, a new 10 GB copy is rebuilt from the healthy ones, often in seconds, which is why small segments matter. Background scrubbing also checks for corruption, and all of this happens without the database instance doing anything.

Aurora is an instance-based relational database with a single writer per cluster (scaled out with Global Database or Limitless). Aurora DSQL is a separate, serverless, distributed SQL service with PostgreSQL compatibility, designed for active-active multi-Region writes with strong consistency.

Check DSQL's unsupported features (such as foreign keys and triggers) before migrating. Because of optimistic concurrency, your code should retry on commit conflicts.