Articles

RDS Failover Interview Questions: Connections, DNS, and Transaction Outcomes

Practice RDS failover interview questions with connection and DNS recovery, lost COMMIT replies, durable idempotency, and transaction-safe retry exercises.

An application reserves an item, sends COMMIT, and loses its database connection before receiving the reply. RDS promotes a replacement writer. The application reconnects successfully. Should it reserve the item again?

That question makes RDS failover interviews more interesting than memorizing an outage duration. A working connection establishes that the application can reach a database. It does not establish whether its previous transaction committed. A strong answer separates endpoint routing, connection recovery, and business-operation recovery.

This guide uses an RDS for PostgreSQL Multi-AZ DB instance as the main example. Official facts come from AWS and PostgreSQL documentation. Design analysis and original exercises are labeled separately. There are no candidate-report claims about interview frequency or a particular employer's process, and the exercises below were not executed against an AWS deployment.

Which RDS Deployment and Endpoint Are We Discussing?

Start by asking: “Is this a Multi-AZ DB instance, a Multi-AZ DB cluster, or Aurora, and does the application connect directly or through RDS Proxy?” These names describe different architectures. An answer about one should not silently become a promise about all three.

Official fact: AWS describes automatic promotion to a standby for a Multi-AZ DB instance. It gives a typical failover duration of 60–120 seconds, with database activity and recovery affecting the duration. Treat that as contextual guidance, not an application availability guarantee. AWS Multi-AZ DB instance failover.

Official fact: A Multi-AZ DB cluster has a writer and two readers across three Availability Zones, using semisynchronous replication. AWS explicitly distinguishes this deployment from Aurora. Its architecture and endpoint behavior need their own discussion. AWS Multi-AZ DB clusters.

Interview analysis: Define “recovered” before estimating time. Database promotion, the first successful connection, the first successful write, and restoration of normal request latency are separate milestones. A pool saturated with retries can keep the application unhealthy after the writer is ready.

A useful opening answer is: “For this instance deployment, I expect routing and connections to recover, then I verify session initialization and reconcile interrupted operations. I measure application recovery independently of the RDS event.”

Why Can Connections Fail After the Writer Changes?

Official fact: For a Multi-AZ DB instance, AWS changes the DNS record to point to the standby during failover, and existing connections need to be re-established. Its JVM guidance recommends a DNS cache TTL no greater than 60 seconds. AWS failover and JVM DNS guidance.

Design analysis: DNS affects address resolution for new connections. Refreshing a record does not repair an already broken socket, and discarding a broken socket does not fix a resolver that still returns an obsolete address. Trace both paths before prescribing a restart.

Recovery layer	Evidence to collect	Application response
Endpoint and DNS	Configured hostname, resolved address, cache behavior in the deployed runtime	Use the intended endpoint; investigate stale resolution rather than hard-coding an IP
Connection pool	Failed connections, acquisition wait, active and queued requests	Discard unusable connections and bound concurrent reconnect attempts
Database session	Initialization failures and required settings	Establish a fresh session and restore the application's required settings
Business operation	Stable operation ID and authoritative stored result	Reconcile or retry under the same operation identity

Do not assume every Java deployment uses the same DNS defaults, or that an operating-system DNS observation proves what the JVM is using. Inspect the actual runtime configuration. The same diagnostic discipline applies to other drivers and resolvers, although the AWS JVM recommendation is specifically about Java.

A follow-up may ask why an aggressive retry loop makes recovery worse. Explain the amplification: many requests encounter a failure, each starts several connection attempts, and pool acquisition queues consume the request deadline. Backoff, jitter, admission limits, and a bounded overall deadline are design choices to control that load. They do not determine transaction outcome.

What Does a Lost COMMIT Reply Actually Tell You?

Official fact: PostgreSQL defines COMMIT as making the current transaction's changes permanent. Its libpq API can report PQTRANS_UNKNOWN when the connection is bad. PostgreSQL COMMIT, libpq transaction status.

Inference: The server's decision and the client's receipt of that decision are different events. A connection error after sending COMMIT can leave the client unable to distinguish a committed transaction from an aborted one. The error alone is insufficient evidence for either conclusion.

Consider this original inventory exercise. The request has operation ID r-42, item A, and quantity 1. Available stock starts at 10.

Begin a transaction and record r-42 under a unique key.
Reduce stock from 10 to 9 and store the reservation result.
Send COMMIT.
Lose the connection before reading its response.
Reconnect to the current writer and investigate r-42.

There are two plausible histories at step four: the database committed the changes, or it did not. Both can produce the same client-side symptom. Submitting a new operation ID would allow a second reservation in the committed history.

Observed evidence	Classification	Next step
A trustworthy commit acknowledgement or matching durable operation result	Known committed	Return the recorded result without repeating the stock change
A trustworthy transaction-abort result	Known aborted	Retry the complete operation if the error and deadline permit
Connection lost around commit; no authoritative result yet	Unknown	Reconcile and use a duplicate-safe retry protocol

A missing row in one lookup is not automatically proof of rollback. A lagging reader, an unsuitable snapshot, or a competing operation can invalidate that conclusion. Use the writer for this recovery path, and let durable uniqueness arbitrate repeated attempts.

How Would You Make the Reservation Safe to Retry?

Original design exercise: Store the operation identity and business mutation in the same database transaction. The operation key must cover the appropriate account or tenant scope. Preserve the original request identity across transport retries, and reject a reused key whose payload does not match.

A simplified PostgreSQL-oriented protocol, assuming Read Committed isolation, is:

begin transaction
insert operation(scope, id, payload_hash)
    values(scope, "r-42", hash(request))
    on conflict do nothing

if this attempt inserted the operation:
    update inventory
        set available = available - 1
        where item = "A" and available >= 1
    record success or sold-out result on the operation
    commit
    return recorded result
else:
    read operation using a fresh statement
    verify payload_hash matches
    return its recorded result
    finish the read transaction

This is explanatory pseudocode, not a production implementation. Define result states, request normalization, lock timeouts, isolation level, and error handling before translating it into code. A sold-out result can be recorded as a completed outcome too, so retrying does not unexpectedly change the original response.

Official fact: PostgreSQL unique constraints enforce uniqueness. At Read Committed isolation, INSERT ... ON CONFLICT DO NOTHING can decline an insertion because of another transaction whose row is not visible to that statement's snapshot. PostgreSQL constraints, PostgreSQL transaction isolation.

Design analysis: That is why the conflict path needs a fresh-statement lookup and an explicit policy if the expected result remains unavailable. A convenience query that tries to insert and return the competing result from one snapshot can surprise you. Do not convert “no returned row” into permission to mutate stock again.

The deduplication record must not commit separately before the reservation. Otherwise, a crash could leave a recorded operation without its business effect. Committing the effect first creates the opposite gap: a retry sees no record and repeats the effect.

Ask the interviewer about retention, too. Deleting operation records while clients can still retry old requests reopens the duplicate window. The design needs a documented retry horizon and retention policy. If the operation also sends an external notification, database uniqueness alone does not make that external delivery exactly once; give that side effect its own recovery protocol.

When Should You Retry the Whole Transaction?

Design analysis: Separate a known retryable database abort from an uncertain commit outcome. For a known abort, use the engine and driver's documented error classification, then replay the whole transaction on a usable session within a bounded deadline. For an unknown outcome, retain the operation ID and reconcile through the duplicate-safe protocol.

Replaying only the final SQL statement can lose earlier reads, locks, validation, and updates. It may produce a result that the original transaction would never have accepted. PostgreSQL's isolation documentation explains transaction-level retry requirements for serialization failures; that specific case should not be broadened into “retry every exception.” PostgreSQL transaction isolation.

A concise interview answer names four boundaries: the retryable error, the transaction unit, the deadline, and the operation identity. It also explains what happens when the deadline expires: return an explicit pending or uncertain outcome if the API contract supports it, rather than inventing success or failure.

Does RDS Proxy Remove the Recovery Problem?

Official fact: AWS says RDS Proxy can retain idle application connections during failover, while in-flight transactions or SQL statements are canceled. It routes to the new writer and can queue requests while that writer is unavailable. Session pinning can limit multiplexing when session state requires a particular backend connection. How RDS Proxy works.

Inference: Those capabilities improve connection handling. They do not give the business API a durable record of whether r-42 succeeded, nor do they make every driver exception safe to replay. Keep the operation-recovery design even when a proxy reduces disruption.

If asked whether to add a proxy, discuss observed connection churn, database connection limits, workload session behavior, and the operational trade-off. “Use RDS Proxy” is an incomplete answer to an ambiguous transaction result.

How Would You Test Failover and Prove Recovery?

Official fact: AWS supports rebooting a Multi-AZ DB instance with failover. Rebooting a DB instance.

Original rehearsal plan: Use a dedicated test deployment and repeat operations with stable IDs while recording application and database events. This plan has not been run against AWS for this article.

Failure window	Evidence that would demonstrate correctness
Before transaction execution	Retry creates one recorded result and one business effect
During the stock update	Aborted attempt leaves no committed partial reservation
After COMMIT is sent, before its reply	Reconciliation returns the same result; stock changes at most once
During simultaneous retries	Unique operation identity prevents duplicate business effects
After the writer is reachable	Pool wait and request latency recover without an unbounded retry queue

Measure the time of the failover event, connection restoration, first successful write, and normal service recovery separately. Verify operation counts and inventory invariants, not just HTTP success rates. Include payload-mismatch attempts and retry-horizon cases. A happy-path reconnect test cannot establish the correctness of uncertain-outcome recovery.