Databases & Storage
Study Guide

Atomicity (all-or-nothing), Consistency (valid state), Isolation (concurrent transactions), Durability (committed survives crash). OLTP (orders, payments) needs strong guarantees; analytics may relax.

PK uniquely identifies rows. FK enforces referential integrity between tables. Indexes speed lookups but slow writes and use storage - design for query patterns, avoid over-indexing.

Pools reuse DB connections to avoid TCP+auth overhead per request. Exhaustion causes timeouts under load - fix with pool sizing, shorter queries, read replicas, or app-level queuing. Watch max_connections on DB.

Dirty: read uncommitted data from another txn. Non-repeatable: same read twice returns different committed values. Phantom: new rows appear in range query. Isolation levels (Read Committed, Repeatable Read, Serializable) trade consistency vs performance.

Multi-Version Concurrency Control keeps row versions so readers do not block writers - snapshots for transactions. Used in Postgres, InnoDB. Trade-offs: vacuum/undo cleanup, long transactions holding old versions causing bloat.

Synchronous: primary waits for replica ack - stronger durability, higher latency, risk if replica slow. Asynchronous: faster commits, possible data loss if primary dies before log ships. Choose RPO requirements.

RPO: max acceptable data loss window. RTO: max acceptable downtime. Drive backup frequency, multi-AZ vs multi-region, and runbook automation.

Multi-AZ: synchronous standby, automatic failover for AZ outage. Cross-region replica promotion: manual or scripted - async lag means nonzero RPO. Test failover quarterly.

Two nodes both believe they are primary - divergent writes, data corruption. Quorum systems (majority votes), fencing (STONITH), and orchestrators (Patroni) mitigate. Kubernetes operators must handle carefully.

Measure p95 query latency, CPU, IOPS, storage growth, connection counts. Plan vertical limits, read replicas, sharding/partitioning strategy, caching (Redis), archive cold data, load test before cutover.

Document (MongoDB, DynamoDB document): flexible schema, nested objects, horizontal scale patterns. Relational: complex joins, strict integrity, mature tooling. Wrong choice causes join emulation in app or schema rigidity pain.

Partition key hashes to shard - hot keys throttle. Sort key orders items in partition for range queries. Design for access patterns; GSIs duplicate data for alternate queries - understand write amplification.

Cache, session store, rate limiting, pub/sub, leaderboards - in-memory speed. Plan persistence (AOF/RDB), eviction policy, HA (Redis Sentinel/Cluster), and never as sole source of truth for critical durable data unless architected for it.

Reads may not reflect latest write immediately across replicas - common in globally distributed systems. Application must tolerate stale reads or use strongly consistent APIs when available (higher latency/cost).

Many requests miss cache simultaneously and hit DB. Mitigate: probabilistic early expiration, request coalescing (single-flight), circuit breakers, warming, per-key locks. Critical at scale during cold start.

Object: flat namespace, HTTP API, great for blobs, static assets, data lake - not POSIX. Block: EBS volumes for databases/filesystems. NFS/EFS: shared POSIX files - different latency/consistency model.

Standard, IA, Glacier tiers - trade cost vs retrieval latency. Lifecycle transitions old objects, expire deletes, Intelligent-Tiering for unknown patterns. Reduces bill for archives and logs.

New objects readable immediately; overwrites/deletes have eventual consistency historically - know current AWS guarantees per operation. Matters for pipelines writing then reading same key.

Block public access settings, bucket policies denying s3:GetObject for Principal *, encryption at rest, logging to centralized account, IAM least privilege, pre-signed URLs for time-limited access, regular audits (S3 Block Public Access org-wide).

Split large objects into parts uploaded in parallel - required above 5 GB, recommended for large files for reliability/resume. Failed uploads leave orphan parts - lifecycle rule to abort incomplete multipart saves cost.

Replication mirrors live state - logical/physical corruption or deletes can replicate. Backups are point-in-time copies (snapshots, dumps) for restore to different timeline. Need both for resilience.

Retention balances cost vs compliance (7 years tax logs). Legal hold pauses deletion. Automate lifecycle; test restores, not just backup success jobs.

Periodic automated restore to isolated env, run integrity checks, measure RTO, verify app connectivity. Tabletop exercises for region loss. Document who approves production restore.

Restore to any second within retention window using transaction logs + periodic snapshots - minimizes data loss vs snapshot-only. Enabled on RDS/Aurora; understand storage cost of logs.

Logical (pg_dump): portable, slower restore, good cross-version. Physical (snapshot, xtrabackup): fast, closer to byte-copy, may be version/region specific. Choose per RTO/RPO and migration needs.

Schema design, query tuning, connection management, parameter group tuning, major version upgrades planning, security groups, monitoring slow queries - AWS patches OS, backups, failover mechanics.

Aurora: AWS storage engine, fast failover, read replicas share storage, auto-growing storage. Higher performance at cost; some MySQL/Postgres features differ - verify compatibility.

Replica on new version, replicate, switch traffic with controlled cutover - reduces downtime vs in-place upgrade. AWS offers managed blue/green for some engines; test app compatibility.

Initial dump/snapshot, ongoing replication (DMS, native logical replication), read-only cutover window, DNS/connection string swap, validate sequences and collations, rollback plan. Practice dry runs.

CPU, free storage, replication lag, connection count, deadlock rate, slow query log thresholds, failed backup jobs, disk queue depth, memory pressure. Tie alerts to runbooks.