Understanding ACID vs BASE Database Systems

Introduction

When choosing a database, one of the most consequential architectural decisions is whether to prioritize ACID guarantees (used by traditional relational databases like PostgreSQL and MySQL) or accept BASE properties (used by many NoSQL databases like Cassandra, DynamoDB, and Couchbase). These are not just buzzwords — they represent fundamentally different trade-offs between consistency and availability, rooted in the CAP theorem: distributed systems can provide at most two of Consistency, Availability, and Partition Tolerance simultaneously.

flowchart TD
    C["CAP theorem: pick 2 of Consistency, Availability, Partition tolerance"]
    C --> A["ACID (Postgres, MySQL): chooses Consistency"]
    C --> B["BASE (Cassandra, DynamoDB): chooses Availability"]
    A --> A1["Write blocks until every replica commits"]
    A --> A2["Every read sees the latest committed write"]
    B --> B1["Write acknowledged immediately, replicated async"]
    B --> B2["A read may briefly return stale data"]

ACID Properties Deep Dive

Atomicity

A transaction either fully completes or fully rolls back — no partial writes:

-- This transfer must succeed entirely or not at all
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 'alice';
  UPDATE accounts SET balance = balance + 500 WHERE id = 'bob';
  -- If server crashes here, both updates are rolled back
COMMIT;

Without atomicity, a crash after the first UPDATE but before the second would debit Alice without crediting Bob — money disappears.

Consistency

Database constraints are always satisfied — no transaction can leave the database in an invalid state:

-- CHECK constraint enforces non-negative balance
ALTER TABLE accounts ADD CONSTRAINT chk_positive_balance 
  CHECK (balance >= 0);

-- This transaction will fail and roll back
BEGIN;
  UPDATE accounts SET balance = balance - 10000 WHERE id = 'alice';
  -- Alice only has $500 → constraint violation → ROLLBACK
COMMIT;

Isolation

Concurrent transactions behave as if executed sequentially (at the strictest level — Serializable):

Read Committed:     Transactions see only committed data (no dirty reads)
Repeatable Read:    Re-reading rows gives same result within transaction (no non-repeatable reads)
Serializable:       Strictest — transactions produce same result as some sequential ordering

Durability

Committed transactions survive failures — data is persisted to disk (WAL — Write-Ahead Log) before acknowledging commit:

Write-Ahead Log sequence:
1. Transaction modifies in-memory buffer
2. Change written to WAL on disk
3. Commit acknowledged to client
4. In case of crash: WAL replayed to recover committed state

BASE Properties

BASE trades strict consistency for availability and partition tolerance:

Basically Available

The system responds to requests even during failures — possibly with stale data:

DynamoDB with eventual consistency:
  Read returns: might return data that is 100ms stale
  But: responds in <10ms even when some replicas are unreachable

Soft State

State may change over time even without new input (due to replication catching up):

Cassandra write (replication factor=3):
  Write acknowledged after 1 replica confirms (consistency level: ONE)
  Other 2 replicas receive update asynchronously
  During propagation: different replicas return different values

Eventual Consistency

Given no new updates, all replicas will eventually converge to the same value:

# DynamoDB eventually consistent read (default, cheaper)
response = table.get_item(
    Key={'user_id': '123'},
    ConsistentRead=False  # May return stale data
)

# Strongly consistent read (double the cost, guaranteed fresh)
response = table.get_item(
    Key={'user_id': '123'},
    ConsistentRead=True  # Always returns the latest committed write
)

When to Use Each

Requirement	ACID (PostgreSQL)	BASE (DynamoDB/Cassandra)
Financial transactions	✅ Required	❌ Dangerous
User sessions/caches	❌ Overkill	✅ Ideal
E-commerce orders	✅ Recommended	⚠️ With care
Social media feeds	❌ Overkill	✅ Perfect
Healthcare records	✅ Required (HIPAA)	❌ Risky
Global low-latency reads	⚠️ Hard to scale	✅ Native multi-region

Key Takeaways

• ACID provides atomicity, consistency, isolation, and durability — essential for financial and compliance workloads where data correctness is non-negotiable.
• BASE accepts eventual consistency in exchange for higher availability and horizontal scalability — appropriate for high-throughput, latency-sensitive workloads where stale data is acceptable.
• The CAP theorem limits distributed databases: PostgreSQL chooses CP (consistency + partition tolerance); Cassandra chooses AP (availability + partition tolerance).
• Many modern databases offer configurable consistency: DynamoDB supports strongly consistent reads; Cassandra offers tunable consistency levels (ONE/QUORUM/ALL).
• The best architectures often use both: ACID for the transactional core (orders, payments, inventory), BASE for read-heavy derivatives (product catalogs, user feeds, caches).