Managed Databases and Data Services Questions

Covers choosing and operating managed database offerings and complementary cloud data services. Candidates should understand managed relational database services such as Amazon Relational Database Service for MySQL PostgreSQL MariaDB Microsoft SQL Server and Oracle, and NoSQL document and key value stores such as Amazon DynamoDB Azure Cosmos Database Google Cloud Firestore and Datastore. Expect to explain when to choose relational versus NoSQL based on data shape query complexity transactional guarantees including atomicity consistency isolation and durability read and write patterns latency and scalability requirements. Understand scaling techniques including vertical scaling read replicas for read scaling horizontal scaling via partitioning or sharding and multi region replication and failover strategies. Be familiar with backup and restore approaches including snapshots point in time recovery cross region replication and disaster recovery planning. Know consistency models and trade offs such as strong eventual and causal consistency, and understand provisioned capacity versus serverless autoscaling models and their cost and operational implications. Candidates should also be able to discuss performance tuning topics such as indexing query optimization caching connection pooling storage and input output optimization monitoring and alerting, as well as security and compliance considerations including encryption access control and network isolation. Finally be prepared to recommend a database solution given workload characteristics such as data size read to write ratio latency targets and operational constraints.

HardTechnical

97 practiced

Explain the following consistency/anomaly types with a short SQL or pseudo-code example: lost update, write skew, read skew, and phantom reads. For each, propose one or more mitigation strategies applicable in managed databases (serializable isolation, optimistic concurrency control, compare-and-swap, application-level compensation) and note operational trade-offs.

Sample Answer

Lost updateSituation & example:Transaction A reads balance=100; Transaction B reads balance=100.A sets balance = balance - 10 and commits (->90).B sets balance = balance - 20 and commits, overwriting A's update -> final 80 (lost A's change).Pseudo-SQL:T1: SELECT balance FROM accounts WHERE id=1; --100T2: SELECT balance FROM accounts WHERE id=1; --100T1: UPDATE accounts SET balance = 90 WHERE id=1; COMMITT2: UPDATE accounts SET balance = 80 WHERE id=1; COMMITMitigations:- Optimistic concurrency (version/check-and-swap): include version column and fail/ retry on conflict.- Serializable isolation or SELECT ... FOR UPDATE (pessimistic lock) to serialize updates.Trade-offs: Versioning adds retry complexity and latency; locks reduce concurrency and risk deadlocks; serializable increases aborts and throughput impact.

Write skewSituation & example:Two doctors check that at least one is on call.T1: SELECT count(on_call)=1?; set self off.T2: same read; both turn off -> invariant violated.Pseudo:T1/T2: SELECT SUM(on_call) FROM staff WHERE role='doctor' --1T1: UPDATE staff SET on_call=false WHERE id=A; COMMITT2: UPDATE staff SET on_call=false WHERE id=B; COMMITMitigations:- Serializable isolation prevents this by detecting non-serializable pattern.- Application-level compensation or explicit locking of the set (e.g., SELECT ... FOR UPDATE of all rows).Trade-offs: Serializable is safest but costly; locking whole set reduces parallelism; compensation is complex and error-prone.

Read skew (non-repeatable read)Situation & example:T reads row X, then another transaction updates X and commits; T reads again and sees different value.Pseudo:T: SELECT v FROM t WHERE id=1; --v1T2: UPDATE t SET v=v2 WHERE id=1; COMMITT: SELECT v FROM t WHERE id=1; --v2Mitigations:- Repeatable read or serializable isolation to provide stable reads.- Snapshot reads (MVCC) to give the transaction a consistent snapshot.Trade-offs: Snapshot isolation increases storage for versions and can allow write skew; stronger isolation raises aborts/latency.

Phantom readsSituation & example:T queries set of rows matching predicate, another transaction inserts a row matching predicate, T re-queries and sees extra row.Pseudo:T: SELECT COUNT(*) FROM orders WHERE status='open'; --5T2: INSERT INTO orders (...) status='open'; COMMITT: SELECT COUNT(*) FROM orders WHERE status='open'; --6 (phantom)Mitigations:- Serializable isolation solves phantoms.- Predicate locks or index-range locks (SELECT ... FOR UPDATE with ranges).- Application-level idempotency to tolerate duplicates.Trade-offs: Range locks and serializable reduce throughput and increase locking complexity; application workarounds add development cost.

Operational guidance for SREs- Prefer snapshot isolation + optimistic concurrency for high-throughput services; implement retries and robust idempotency.- Use serializable for safety-critical invariants (billing, permissions) accepting higher abort and latency rates.- Monitor aborts/retries, lock contention, and latency post-change; add metrics/alerts for serialization failures.- Test at scale (chaos/high-concurrency) to find hotspots; document where stronger isolation is required vs where eventual/optimistic approaches suffice.

MediumTechnical

82 practiced

Describe the step-by-step plan to migrate a production MySQL database from self-hosted VMs to Amazon RDS with minimal downtime. Include schema transfer, initial data load, continuous sync (binlog replication), migrating users and permissions, cutover strategy, smoke tests, and rollback plan.

Sample Answer

Situation: Migrate a production MySQL (self‑hosted VMs) to Amazon RDS with minimal downtime. Below is an SRE, step‑by‑step plan covering schema, initial data, continuous sync (binlog), users, cutover, smoke tests and rollback.

1. Preparation & validation- Inventory schema size, tables with heavy writes, GTID vs non‑GTID, current binlog format (ROW recommended), character sets.- Provision target RDS instance class, storage (GP3/io1), parameter group (binlog_format=ROW, server_id, log_slave_updates if using replica), security groups, subnet groups, monitoring/CloudWatch.

2. Schema transfer- Export schema only (no data) to avoid FK/order issues: mysqldump --no-data --routines --triggers --events --single-transaction -h source -u root -p db > schema.sql- Apply to RDS: mysql -h rds-endpoint -u admin -p < schema.sql- Validate with pt-table-checksum dry run or schema diff tool.

3. Initial data load (bulk)- For minimal locking use logical dump per table with --single-transaction for InnoDB or use Percona XtraBackup if very large: mysqldump --single-transaction --skip-lock-tables --no-create-info -h source -u root -p db | mysql -h rds-endpoint -u admin -p db- Alternatively use aws s3 import or AWS DMS full load for very large datasets.

4. Continuous sync (binlog replication)Option A — Native binlog replica:- Configure source to allow replication (binlog enabled, proper server_id, network access).- On RDS create external replica using stored procedure (RDS MySQL supports external source; commands differ by engine): call mysql.rds_set_external_master('<host>', <port>, '<user>','<password>','<binlog_file>',<position>,0);- Start replication on RDS: call mysql.rds_start_replication; monitor with SHOW SLAVE STATUS\G on RDS.Option B — AWS DMS (recommended for heterogeneous or firewall complexity):- Create DMS task: full load + ongoing replication (CDC). DMS handles schema conversion, LOBs, and continuous apply. Monitor latency.

5. Migrate users & permissions- RDS disallows direct mysql.user writes. Export users from source: SELECT CONCAT('CREATE USER \'', user, '\'@\'', host, '\' IDENTIFIED BY PASSWORD \'', authentication_string, '\'; GRANT ', GROUP_CONCAT(DISTINCT privilege_type), ' ON ', table_schema, '.', table_name, ' TO \'', user, '\'@\'', host, ';') ... ;- For simplicity, re-create application users with secure passwords on RDS and reapply grants. Use IAM authentication if desired. Verify roles and DEFINERs for routines/triggers.

6. Cutover strategy (minimal downtime)- Lower TTL of DB DNS (or use service discovery) well ahead.- Bring app into read-only mode or freeze writes (brief maintenance window) OR use load balancer to direct writes to old DB while reads to RDS (dual‑write avoided).- Record final binlog coordinates on source: SHOW MASTER STATUS;- Ensure replica/DMS has applied up to that position (replication_lag ~0).- Stop application writes, flush tables with read lock if using mysqldump approach: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; (capture position)- Wait replication catch‑up; then switch app DB endpoint to RDS endpoint or update DNS.- Remove read lock and resume application.

7. Smoke tests & validation- Run health checks: connect, simple read/write, transactions, stored procedures, complex queries.- Run pt-table-checksum or row counts on critical tables.- Monitor application error rates, latency, CloudWatch metrics, replica lag, slow queries.- Run load test on staging path if possible.

8. Rollback plan- Keep source DB writable until cutover completed and validated.- Have recent full backup snapshot and binary logs archived.- If issues discovered after cutover: revert app DNS/connection strings to original DB (low TTL), or restore from snapshot to new instance and point app back.- If replication method used native replica, you can stop writes on RDS and re‑point replication back to source if needed.- Document RTO/RPO and decision gates for rollback.

9. Post‑migration- Take final RDS snapshot, enable automated backups, set up Multi-AZ read replicas for HA, configure read replicas for scale.- Rotate passwords, update monitoring/alerts, remove old DB access, decommission VMs after retention period.

Key considerations- Test this entire flow in staging with production-sized data.- Prefer ROW binlog_format and GTID if supported (simplifies positioning).- Use low DNS TTL, scripted cutover steps, and runbooks.- Automate checks and have runbook owners and communication plan.

This plan minimizes downtime by using initial bulk load + ongoing CDC/binlog replication to keep RDS nearly in sync, then a short freeze for the final cutover with validated rollback paths.

HardSystem Design

67 practiced

Design a multi-region architecture to support a global service with OLTP requirements (p99 latency < 100ms for reads) and near-real-time analytics. Requirements: 200M users, peaks of 2k writes/sec, analytics updates within 30 seconds, GDPR constraints for EU-resident data. Propose managed products for OLTP and analytics, replication and CDC strategy, cross-region considerations, and how you'd ensure compliance for data residency.

Sample Answer

Requirements clarification:- 200M users, p99 read <100ms globally, writes peak 2k/sec (moderate), analytics freshness ≤30s, EU data must remain in EU (GDPR).- Availability SLOs: 99.95% for OLTP; analytics SLA best-effort near-real-time.

High-level design (Google Cloud example — equivalent AWS/Azure alternatives shown):- OLTP: Google Cloud Spanner (multi-region, strongly consistent, horizontal scale, multi-master semantics for writes with single or multi-leader config). Alternatives: CockroachDB Cloud or Amazon Aurora Global DB + regional read replicas (if you accept read-after-write tradeoffs).- Edge reads: Regional read replicas + global Anycast LB (Cloud CDN/Front Door) to route read traffic to nearest region; application-level caching (Redis/MemoryStore) for very hot keys to guarantee p99 <100ms.- Analytics: Pub/Sub streaming ingestion → Dataflow/Flink for transformations + BigQuery for OLAP and near-real-time dashboards. Alternative: Kafka (MSK/Confluent) → ksql/Stream processing → Snowflake.- CDC & replication: Use managed CDC (Datastream/Cloud Data Fusion) or Debezium connectors if using self-managed DB. Stream Spanner change streams or use Debezium for other DBs into Pub/Sub/Kafka. Dataflow jobs consume CDC, produce cleaned events to BigQuery and to materialized views/caches.- Cross-region strategy: - Deploy OLTP Spanner instances in multiple regions with database configured for multi-region read/write where supported. For Spanner, use multi-region instance topology with regional replicas close to users; choose leader placement to minimize cross-region commit latency. - Reads served from local read-only replicas; writes routed to region with leader or use multi-leader DB that supports conflict-free writes. - Asynchronous replication for analytics pipelines; use change streams so analytics freshness <=30s. - For stateful caches, use regional Redis clusters with async replication; avoid cross-region sync on hot paths.- GDPR / data residency: - Logical partitioning by geo: shard user data by residence (EU users stored in EU-region Spanner instance/instances). Use Cloud IAM + org policy to enforce region-locations for EU datasets. - Ensure backups, logs, and replicas for EU-resident data remain in EU; configure export sinks and Dataflow regional endpoints in EU. - Data processing pipeline must be region-aware: CDC connectors for EU data write to EU Pub/Sub topics and EU Dataflow workers; BigQuery datasets for EU-resident analytics stored in EU-only locations. - Encryption at rest and in transit with customer-managed keys (KMS) per-region; maintain DSAs, records of processing, consent flags, and automated data subject request pipelines (deletion, export).- Reliability & performance ops: - SRE-run runbooks for leader failover, regional failover, and cross-region latency spikes. - Autoscaling for DB nodes and Dataflow workers; capacity plan for 2k writes/sec + headroom. - Observability: distributed tracing (OpenTelemetry), p99 latency dashboards per region, alerts on replication lag (>10s), CDC lag (>5s), BigQuery ingestion lag (>30s). - Chaos testing: regional failover drills, network partition simulations.- Trade-offs: - Strong consistency multi-region (Spanner) simplifies correctness but increases commit latency vs eventual systems; mitigated via regional reads and caching. - If using Aurora/Cockroach multi-master, weigh operational complexity vs managed Spanner.

Why this meets requirements:- Spanner-like multi-region design provides consistent global reads/writes and predictable latency; local read replicas + caching ensure p99 <100ms.- CDC → Pub/Sub → Dataflow → BigQuery delivers sub-30s analytics freshness with managed scaling.- Geo-sharding + region-restricted processing and key management enforces GDPR residency.

HardSystem Design

77 practiced

Your primary relational DB is write-bound at 10k TPS on a single table with a hotspot on one key. Vertical scaling is exhausted. Propose strategies to scale writes horizontally: discuss sharding schemes, application-level changes (idempotency, batching), moving parts of workload to a NoSQL store, or using a write-queue/ingestion pipeline. For each strategy, describe operational trade-offs and migration complexity.

Sample Answer

Requirements & constraints:- 10k TPS write-bound single table, hotspot on one key, vertical scaling exhausted, need horizontal write scale while preserving availability and consistency SLAs.

High-level approaches (each with what, trade-offs, migration complexity):

1) Shard the table (application-level/DB-router)- What: Horizontal partitioning by key (range, hash, or lookup table). Avoid hotspot by choosing shard key that spreads writes (hash or time+id).- Trade-offs: More complex routing, joins across shards expensive, rebalancing shards heavy, need cross-shard transactions or eventual consistency.- Migration: Medium–high. Requires schema changes, routing layer (proxy or client lib), resharding plan (consistent hashing or splitter), extensive tests and rollout.

2) Hot-key mitigation (write-side)- What: Introduce sub-keys or time-bucketing for the hotspot (e.g., key:userid -> userid:shardId or userid:minute). Aggregate reads with fan-in.- Trade-offs: More complex read path (aggregation), eventual consistency for counters, lower risk than full sharding.- Migration: Low–medium. App changes to write and aggregated background job to merge; lower DB churn.

3) Move hot workload to NoSQL (e.g., Cassandra, DynamoDB, Bigtable)- What: Use a write-optimized, partitioned store for hotspot items (wide-row, time-series, or counter patterns).- Trade-offs: Different consistency model (usually eventual); different query capabilities; operational overhead of new DB.- Migration: Medium. Requires schema redesign, data sync strategy (dual writes or change-data-capture), client changes, and SLO adjustment.

4) Introduce write-queue / ingestion pipeline- What: Buffer writes in durable queue (Kafka, Kinesis) and consume with scalable workers to write to DB or to an aggregate store. Allows smoothing bursts and batching.- Trade-offs: Adds lag (acceptable for async workloads), must handle exactly-once/idempotency, increases system components to operate.- Migration: Low–medium. Implement producer changes, consumer workers, backpressure handling, and monitoring.

5) Application-level changes: idempotency, batching, backpressure- What: Ensure idempotent write operations (idempotency keys), batch small writes into larger transactions, apply client-side rate limiting/backpressure.- Trade-offs: Requires careful id design and conflict handling; batching increases latency for single events.- Migration: Low. Code changes and deploy strategy; immediate benefits.

Operational considerations (applies across strategies):- Observability: add SLOs, dashboards for queue lag, shard load, write latency, error rates.- Resilience: automated retries with backoff, poison message handling, circuit breakers.- Data integrity: adopt CDC for syncs, reconciliation jobs, and audits.- Rollout: Canary, feature flags, replayable migrations, capacity testing.

Recommendation path:1) Short term: implement idempotency + batching + hotspot mitigation (sub-keys) + monitoring.2) Mid term: add write-queue to smooth spikes and enable async migrations.3) Long term: shard or migrate hotspot items to NoSQL with CDC-based cutover and automated resharding tooling.

This staged approach reduces risk, spreads operational load, and provides iterative measurement points for SRE-run capacity planning.

HardTechnical

81 practiced

You observe a slow multi-join query in Postgres. Here is a simplified EXPLAIN ANALYZE output excerpt:

"Hash Join (cost=1000.00..2000.00 rows=10000) (actual time=800.123..820.456 rows=9500 loops=1) Hash Cond: (orders.user_id = users.id) -> Seq Scan on orders (cost=0.00..900.00) (actual time=0.123..120.456 rows=1000000 loops=1) -> Hash (cost=900.00..900.00) (actual time=600.000..600.000 rows=10000 loops=1)"

Analyze where time is spent, list likely root causes, and propose specific optimizations (indexes, query rewrite, materialized views, statistics/analyze) and how you would validate improvements.

Sample Answer

Brief diagnosis (where time is spent)- From the excerpt: Seq Scan on orders = actual 0.123..120.456 ms (scans 1,000,000 rows). Hash build = actual 600.000 ms (on users, 10,000 rows). Hash Join total ~800–820 ms. So most time is spent building the hash table (600 ms) and then probing/joining (remaining ~200 ms). The sequential scan of orders is non-trivial but not the dominant single component here.

Likely root causes- Missing/ineffective indexes for the join path (causing large seq scan or forcing hash strategy).- Hash build expensive because data must be read from disk (cold buffers) or hash spilled to disk due to low work_mem.- Poor or stale statistics leading planner to pick hash join instead of an indexed nested-loop.- Data skew or very large rows increasing build/probe cost.- Table bloat, TOASTed large columns read unnecessarily.- Parallelism disabled or misconfigured; I/O or CPU contention on the node.

Concrete optimizations (what to change)1. Ensure statistics up-to-date - Run: ANALYZE orders; ANALYZE users;2. Add appropriate indexes - If join is orders.user_id = users.id, ensure users.id is primary key (usually exists) and add index on orders.user_id: CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); - Also consider a covering index if predicates exist: idx_orders_user_id_status ON orders(user_id, status) INCLUDE (col1,...)3. Increase work_mem (session/test) so hash fit in RAM - SET work_mem = '64MB'; then re-run EXPLAIN ANALYZE to see if hash no longer spills.4. Force/use alternative plan if selective - If users is small and orders filter selective, try an indexed nested-loop by enabling nested loop planner or rewriting query to drive from small side.5. Reduce I/O / row width - Avoid SELECT *, remove large columns from the join (select only needed columns), or move large text to another table.6. Materialized view or pre-joined table - For repeated expensive aggregation/joins, create MATERIALIZED VIEW and refresh periodically: CREATE MATERIALIZED VIEW mv_user_orders AS SELECT ... FROM orders JOIN users ON ...;7. Vacuum / Reindex / Tune autovacuum - VACUUM (VERBOSE) and REINDEX if table/index bloat suspected.8. Enable parallelism and tune effective_cache_size, max_parallel_workers_per_gather.

How to validate improvements- Use EXPLAIN (ANALYZE, BUFFERS) before/after; compare actual times, buffers read, disk hits.- Measure end-to-end query latency and p99 for the service via pg_stat_statements and application metrics.- Check system metrics (iostat, vmstat, CPU) to ensure less IO or CPU.- Test different work_mem values and index presence in a staging dataset; measure plan changes and timings.- For materialized views, measure refresh cost vs query gain and check staleness tolerance.- Use pg_stat_user_indexes to see index usage and pg_buffercache to see cache hit improvements.

Example quick test commands- ANALYZE orders;- EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <your query>;- CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);- SET work_mem='64MB'; EXPLAIN (ANALYZE) <your query>;

Final noteStart with ANALYZE and EXPLAIN (ANALYZE, BUFFERS) to confirm where I/O is happening, then add index or tune work_mem as low-risk fixes. If frequent joins remain heavy, consider denormalization / materialized views with refresh strategy.

Unlock Full Question Bank

Get access to hundreds of Managed Databases and Data Services interview questions and detailed answers.

Join thousands of developers preparing for their dream job.