Databases &amp; Architecture

Data warehouse vs data lake

Warehouses store structured, schema-on-write data for BI; lakes store raw multi-format data with schema-on-read for exploration and ML.

Multi-region active-passive DR with Aurora

Async global replication to a passive region, promote and repoint traffic on failover, and fence the old primary to prevent split-brain.

Aurora vs Spanner architecture contrast

Aurora is single-writer with a shared distributed log-based storage and quorum, scaling reads; Spanner shards data with Paxos and TrueTime for global writes.

How Spanner achieves global external consistency

TrueTime gives bounded-uncertainty clocks via GPS and atomic sources, Spanner commit-waits out that uncertainty, and Paxos replicates each shard.

Cache-aside pattern with Redis and RDS

App checks cache, on miss reads DB and populates, writes invalidate the key, and consistency is eventual.

Near-zero-downtime database migration to cloud

Assess and provision, do a full load then continuous CDC replication with a tool like DMS, validate, then cut over with a rollback plan.

Aurora Serverless v2 for spiky traffic

Serverless v2 autoscales capacity in fine-grained ACU steps near-instantly, you pay per-ACU-second, and provisioned is fixed cost regardless of load.

Replica lag and read-your-writes consistency

Stale reads come from async replica lag, the guarantee a user expects is read-your-writes, and you route that user's reads to the primary after a write.

Read replica vs Multi-AZ in RDS

Multi-AZ is synchronous standby for failover, read replicas are async copies for read throughput, and the two solve different problems.

Managed RDS vs self-managed DB on EC2

Managed RDS offloads patching, backups, failover, and replication, freeing the team to build product; self-managed EC2 means you own all that toil.

Fixing an ORM's inefficient aggregation query

Drop to raw SQL or a view for the heavy report, or restructure the ORM query and add indexes. Raw SQL is fast but couples to the schema; tuning keeps portability.

Unit of Work / Session pattern in ORMs

The Unit of Work tracks new, dirty, and deleted objects, then flushes them as one batched transaction at commit.

Transaction isolation levels and their tradeoffs

Isolation levels control which concurrency anomalies (dirty/non-repeatable reads, phantoms) are allowed; higher levels mean stronger consistency but more blocking and less concurrency.

Eager vs lazy loading in an ORM

Eager fetches related data up front (joins/extra query); lazy defers until accessed. Lazy in a loop causes the N+1 query problem.

Pooled connection lifecycle and close() semantics

Borrow from pool, use, then close() returns it to the pool rather than tearing down the socket.

Connection pools and the problem they solve

A pool reuses pre-opened connections so requests skip the expensive connect handshake; without one, every request pays setup latency and may overwhelm the database.

Purpose of database drivers (JDBC/ODBC)

A driver translates a standard API into each database's wire protocol, so app code stays portable across vendors.

Defense-in-depth against SQL injection

Beyond parameterization, apply least-privilege accounts, stored procedures, input allowlisting, and monitoring to shrink blast radius.

Diagnosing degradation with normal CPU and memory

When CPU and memory look fine, sessions are waiting, not computing; examine wait statistics, lock and latch contention, I/O waits, and buffer pool hit ratio.