Database Design • ACID vs BASEHard⏱️ ~3 min
Production Implementation Patterns
Real systems blend ACID and BASE by choosing consistency per boundary. Amazon uses ACID within a shard or service (Aurora for orders, payments) and BASE across services (DynamoDB for catalog, session state). Change Data Capture (CDC) streams from Aurora to Kinesis feed DynamoDB projections, accepting 100 milliseconds to seconds staleness for derived views. This pattern isolates transactional complexity to source of truth writes while scaling reads horizontally.
Cross entity workflows in BASE systems replace multi entity transactions with sagas. A checkout saga has steps: reserve inventory (conditional DynamoDB write), charge payment (Stripe API call), confirm order (Aurora write), release inventory on failure. Each step is idempotent with a unique operation ID stored in DynamoDB. A persistent log (Step Functions or SWF) drives retries and compensation. Expect temporary inconsistencies during saga execution (seconds to minutes) and design user experience to handle them: show "processing" states, send confirmation emails after saga completes.
Quorum tuning in BASE systems trades consistency for latency and availability. With N equals 3 replicas, R equals 2, W equals 2, reads and writes both touch a majority, guaranteeing reads see latest writes at cost of higher latency (both reads and writes wait for 2 replicas) and lower availability (any 2 replicas must be up). DynamoDB does not expose R and W knobs directly but offers eventual (R equals 1) versus strongly consistent (R equals quorum) reads. Cassandra exposes full tuning: LOCAL_QUORUM for low latency within a datacenter, QUORUM for cross datacenter consistency.
Observability is critical. Track per consistency level latencies separately: eventual reads at P99, strong reads at P99, write latencies at P50 and P99.9. Monitor replication lag via log sequence numbers or vector clock distances. For ACID, track lock wait time, deadlock rate, and transaction abort rate. Set user facing Service Level Objectives (SLOs) tied to consistency: profile updates must read your writes within 100 ms P99, catalog reads can be eventual within 2 seconds.
💡 Key Takeaways
•Change Data Capture from ACID source of truth (Aurora orders) to BASE projections (DynamoDB search index, ElastiCache) accepts 100 ms to seconds staleness, isolating transactional writes while scaling reads to millions of queries per second.
•Sagas decompose cross entity workflows into idempotent steps with compensation. Checkout saga: reserve inventory (conditional write with operation ID), charge payment, confirm order, release on failure. Persistent log (Step Functions) drives retries.
•Quorum tuning with N equals 3, R equals 2, W equals 2 ensures reads intersect writes but doubles read latency and requires 2 of 3 replicas up (66 percent availability threshold versus 33 percent with R equals 1).
•Cassandra LOCAL_QUORUM provides strong consistency within a datacenter (single digit ms) while QUORUM spans datacenters (adds WAN RTT of 60 to 150 ms), demonstrating geographic consistency tradeoff.
•Per consistency level observability: track eventual read P99, strong read P99, replication lag (log sequence distance), conflict rates, and deadlock rates separately to detect regressions and correlate with user facing SLOs.
📌 Examples
Amazon order flow: Client calls Orders API (Aurora ACID write, 15ms P99), which emits event to Kinesis. Lambda updates DynamoDB order status projection (eventual, 200ms typical lag). Client polls status from DynamoDB (5ms reads). Catalog browsing reads from DynamoDB replicas (eventual, 3ms P50).
Idempotent saga step: PutItem with ConditionExpression 'attribute_not_exists(operationId)', operationId from client request header. Retries on timeout return ConditionalCheckFailedException (already processed), preventing duplicate charges. Log stores saga state: PENDING, INVENTORY_RESERVED, PAYMENT_CHARGED, COMPLETED.
Cosmos DB multi region write: Application in West US writes with session consistency (8ms local write), session token returned. Subsequent read from East US with token waits for replication if needed (adds 50 to 100 ms if not yet replicated), guaranteeing read your writes globally.