Post

SQL vs NoSQL

The fundamental choice between ACID transactions with normalized schemas (SQL) versus BASE consistency with flexible schemas and horizontal scale (NoSQL) -- and when to use both.

Posted
By
13 min read
SQL vs NoSQL

ACID vs BASE: The Fundamental Trade-off

ACID Properties (SQL Default)

Atomicity: A transaction is “all or nothing” — either all operations complete and are visible to other transactions, or none are and the database rolls back. Example: transferring $100 between accounts updates both rows or fails completely; no intermediate state where one account is debited but the other not credited.

Consistency: The database enforces all declared rules (primary keys, foreign keys, check constraints, NOT NULL). If a transaction would violate any rule, it is rejected. The database is always in a valid state.

Isolation: Concurrent transactions do not interfere with each other. Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) define exactly what interference is prevented. Higher isolation guarantees more safety but lower concurrency.

Durability: Once a transaction is committed, the data survives any system failure (hardware crash, power loss) because it is written to persistent storage (disk) before the COMMIT returns to the client.

BASE Properties (NoSQL Default)

Basically Available: The system always responds to requests (no total unavailability), even if some replicas are down or network partitions exist. The trade-off is that responses may return stale data or conflicts.

Soft State: Data does not remain consistent all the time. A write to one replica may not immediately be visible on others. The system is in a temporary, intermediate state.

Eventually Consistent: Given enough time with no new writes, all replicas converge to the same data. Conflicts are resolved via last-write-wins, vector clocks, or application logic. There is no global point-in-time consistency.

Key Metrics Comparison

Metric SQL (ACID) NoSQL (BASE)
Write latency (p99) 5-50ms (durable to disk) <1ms (memory, async replication)
Consistency guarantee Strong (point-in-time) Eventual (minutes to seconds)
Max write throughput 10K-100K ops/sec per node 100K-1M+ ops/sec per node
Geographic distribution Difficult (replication lag) Native (multi-region)
Transaction scope Multi-row, multi-table Single row or eventual consistency
Schema changes Downtime or careful migration Hot deployment

How Real Systems Use ACID vs BASE

PostgreSQL (SQL, ACID)

PostgreSQL uses Multiversion Concurrency Control (MVCC) to achieve ACID without locking all readers. When a transaction modifies a row, PostgreSQL creates a new version with a new transaction ID; old versions remain visible to in-flight transactions. This allows readers to see consistent snapshots while writers proceed concurrently. Example: a financial reporting query can scan millions of rows at a consistent snapshot point, while other transactions commit writes without blocking the reader. PostgreSQL’s Write-Ahead Log (WAL) ensures durability by writing all changes to the log before modifying memory, so a crash-recovery process can replay committed transactions. For e-commerce, PostgreSQL enforces complex constraints (e.g., “order quantity ≤ inventory stock”) within transactions; a single COMMIT either succeeds fully or rolls back, preventing race conditions that would corrupt inventory counts.

MySQL InnoDB (SQL, ACID)

MySQL’s InnoDB engine implements ACID via locking and MVCC combined. For Repeatable Read isolation (MySQL’s default), each transaction gets a consistent view of data as of statement start, and row-level locks prevent phantom reads under typical workloads. InnoDB writes transactions to the redo log before committing (achieving durability), and uses a background thread to flush dirty pages to disk. At Netflix, MySQL powers booking systems (reservations, confirmations, payment captures) where atomicity is non-negotiable — a double-booking race condition is unacceptable, so the database enforces “one reservation per slot” via UNIQUE constraints and transactional semantics. InnoDB’s default 5MB undo log buffer handles typical transaction sizes; larger bulk operations require tuning or batching.

Cassandra (NoSQL, BASE)

Cassandra is a wide-column, eventually-consistent system optimized for write-heavy, time-series workloads. Twitter uses Cassandra to store billions of events (tweets, likes, retweets) with extremely high write throughput (100K+ writes/sec per node). Cassandra achieves this by accepting writes immediately to an in-memory structure (memtable), then asynchronously flushing to disk and replicating to other nodes. A read may return stale data if it hits a replica that hasn’t yet received a write from other replicas (replication lag of 100-500ms typical). Cassandra’s read_repair mechanism gradually makes replicas consistent, and its consistency_level parameter lets clients tune: setting consistency_level=QUORUM requires acknowledgment from a majority of replicas before returning (stronger consistency), but increases latency to 50-100ms. No multi-row transactions exist; instead, Cassandra’s Lightweight Transactions (LWTs) use Paxos to ensure single-row atomicity, but with 10-20x latency penalty.

DynamoDB (NoSQL, BASE with tunable consistency)

AWS DynamoDB is a fully managed key-value store with auto-scaling and single-digit millisecond latency. It offers eventual consistency by default (1-2 replicas per shard, async replication across data centers) but supports “strong consistency” reads that wait for all replicas (doubling read latency to 10-20ms). For Uber ride matching, DynamoDB stores driver location data with eventual consistency (a customer may see a driver location that’s 100-500ms stale), acceptable since a GPS update arrives every 2-5 seconds anyway. Write throughput scales linearly: provisioning 10,000 write units guarantees 10K writes/sec; each write is typically <5ms. DynamoDB’s transactional APIs (TransactWriteItems) support ACID across up to 25 items in a single AWS account/region, but cost 4x throughput; transactional operations have a 10ms latency floor. Items are limited to 400KB; larger data (user profiles, order history) must be split across multiple items.

CockroachDB (SQL, ACID with horizontal scale)

CockroachDB is a NewSQL system that mimics PostgreSQL’s SQL interface while distributing data horizontally across a cluster. It implements ACID transactions via distributed 2-phase commit (Raft consensus on each key range) and snapshot isolation (similar to PostgreSQL). When a client inserts a row, CockroachDB replicates the change to a quorum of nodes (typically 3) via Raft; write latency increases to 50-200ms depending on network round-trip times, because consensus requires waiting for a majority. CockroachDB can partition across regions: the US region writes to 3 US nodes (fast), while European users’ data writes to 3 EU nodes, minimizing latency. Uber migrated critical financial transactions (payments, trip charges, driver earnings) to CockroachDB to avoid PostgreSQL’s vertical scale limits — with 10 nodes across 2 data centers, CockroachDB handles 100K concurrent transactions with standard ACID guarantees. However, distributed consensus overhead means CockroachDB’s write latency is 10-50x higher than single-node PostgreSQL (200ms vs 10ms), making it unsuitable for ultra-low-latency caching.

Google Spanner (SQL, ACID with global distribution)

Spanner is a globally distributed SQL database that achieves strong consistency across continents via the TrueTime API — atomic clocks (GPS + cesium oscillators) in each data center provide absolute time bounds, accurate to within 7ms. When a transaction commits in Spanner, it assigns a commit timestamp and waits at least 7ms before releasing locks, ensuring that any subsequent transaction’s start time is guaranteed to be after the previous commit. This allows Spanner to provide true serializability without locking clients. Google Finance uses Spanner to maintain a single source of truth for account balances, trade prices, and positions across global data centers — a financial transfer that commits in the US is immediately visible to queries in Europe because all timestamps are globally ordered. A read query scans data with a consistent snapshot, taking ~100-200ms to hit replicas across continents. Writes go to a quorum (majority replication) and take 100-300ms. Spanner’s horizontal scale enables exabyte-scale databases (trillions of rows) split into logical shards (called “splits”) across hundreds of nodes.

Key Properties Comparison Table

Property SQL (ACID) NoSQL (BASE) NewSQL
Consistency model Strong (immediate visibility) Eventual (converges over time) Strong (across shards)
Transaction scope Multi-row, multi-table Single row or batch (eventually) Multi-row, multi-table (with overhead)
Write latency (p99) 5-50ms 1-5ms 50-300ms (distributed)
Max throughput 10K-100K ops/sec 100K-1M+ ops/sec 10K-100K ops/sec
Schema flexibility Rigid (migrations required) Flexible (no upfront schema) Rigid (SQL)
Geographic scale Regional (replication lag) Global (built-in) Global (with latency)
Query expressiveness SQL (joins, aggregations) Limited (key/range queries) SQL (full)

When to Use SQL (ACID)

✅ Complex multi-table transactions — Payments, inventory updates, booking systems where atomicity is non-negotiable ✅ Complex queries and joins — Reporting, analytics, ad-hoc queries across many tables ✅ Data integrity constraints — Foreign keys, uniqueness, check constraints that prevent invalid states ✅ Team expertise — SQL is widely understood; PostgreSQL has a mature ecosystem ✅ Moderate scale (single-region) — <10K QPS per database, <10TB data, single data center acceptable

❌ Avoid SQL for: Billions of writes/sec, globally distributed low-latency reads, rigid schema constraints blocking product iteration

When to Use NoSQL (BASE)

✅ Massive horizontal scale — Billions of records, 100K+ writes/sec, multiple data centers ✅ High write throughput — IoT sensors, logs, time-series, metrics (writes » reads) ✅ Flexible/evolving schema — Mobile apps, rapid feature iteration, new fields added weekly ✅ Simple access patterns — Read/write by key, time-range queries, no complex joins ✅ Geographic distribution — Multi-region low-latency reads (eventual consistency acceptable)

❌ Avoid NoSQL for: Complex transactions, data integrity enforcement, ad-hoc queries, strong consistency requirements

When to Use NewSQL (Spanner, CockroachDB, TiDB)

✅ Need ACID + horizontal scale — Financial systems, marketplaces needing both consistency and global distribution ✅ Multi-region with strong consistency — Banking regulations require immediate consistency worldwide ✅ Mature product with traffic burst — Migrate off monolithic SQL when traffic outgrows single database

❌ Avoid NewSQL if: Latency-sensitive applications (NewSQL has 10-50x higher latency than SQL), cost-constrained (consensus overhead increases compute), not yet at scale limits of SQL

Implementation: ACID Transaction vs Eventual Consistency Pattern

SQL: ACID Transaction Pattern

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE

def transfer_money_acid(source_account, dest_account, amount):
    """
    ACID transaction: atomicity ensures both rows update or both rollback.
    Isolation level SERIALIZABLE prevents phantom reads and dirty reads.
    Durability ensures the debit is permanent after COMMIT returns.
    """
    conn = psycopg2.connect("dbname=bank")
    try:
        conn.set_isolation_level(ISOLATION_LEVEL_SERIALIZABLE)
        cur = conn.cursor()

        # BEGIN is implicit
        # Debit from source account
        cur.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s",
            (amount, source_account)
        )

        # Credit to destination account
        cur.execute(
            "UPDATE accounts SET balance = balance + %s WHERE id = %s",
            (amount, dest_account)
        )

        # Log the transaction
        cur.execute(
            "INSERT INTO transactions (source, dest, amount) VALUES (%s, %s, %s)",
            (source_account, dest_account, amount)
        )

        # COMMIT: all or nothing
        conn.commit()
        return {"status": "success"}

    except psycopg2.Error as e:
        # On any error, ROLLBACK all changes
        conn.rollback()
        return {"status": "failed", "error": str(e)}

    finally:
        cur.close()
        conn.close()

# Example: transfer $100 from account 1 to account 2
# If network fails mid-transaction or database crashes, either both updates
# happen or neither does — no partial state possible.
result = transfer_money_acid(1, 2, 100)
print(result)

NoSQL: Eventual Consistency Pattern

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

import time
from dynamodb_mock import DynamoDBTable  # Pseudocode for clarity

def transfer_money_eventual(source_account, dest_account, amount):
    """
    Eventual consistency: write immediately to each account's row independently.
    Consistency converges over time (100-500ms), but the combined state may
    temporarily violate invariants (e.g., total money in system decreases briefly).
    """
    accounts = DynamoDBTable("accounts")

    try:
        # Write 1: Debit source (returns immediately, before replication completes)
        accounts.put_item({
            "account_id": source_account,
            "balance": accounts.get_item(source_account)["balance"] - amount,
            "updated_at": time.time()
        })

        # Write 2: Credit destination (independent operation)
        accounts.put_item({
            "account_id": dest_account,
            "balance": accounts.get_item(dest_account)["balance"] + amount,
            "updated_at": time.time()
        })

        # Log asynchronously (separate eventual-consistency operation)
        transactions_table = DynamoDBTable("transactions")
        transactions_table.put_item({
            "transaction_id": str(uuid.uuid4()),
            "source": source_account,
            "dest": dest_account,
            "amount": amount,
            "timestamp": time.time()
        })

        # Return immediately (writes not yet durably stored to all replicas)
        return {"status": "success"}

    except Exception as e:
        # On write failure, only one account may have been updated
        # Manual compensation required (debit the account we credited, etc.)
        return {"status": "failed", "requires_manual_compensation": True, "error": str(e)}

# Problem: After the function returns but before replication completes (within 100-500ms):
# - source_account balance is decremented
# - dest_account balance is NOT YET incremented on some replicas
# - A concurrent read of the total money across both accounts shows less than originally
# This temporary inconsistency is acceptable for non-critical use cases (shopping carts,
# recommendations) but NOT for financial transactions.

Hybrid Pattern: Using Both

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

def hybrid_inventory_update(order_id, items_ordered):
    """
    Use SQL (PostgreSQL) for critical consistency (inventory deduction),
    NoSQL (DynamoDB) for scalable read-heavy components (product catalog, cache).

    This is the polyglot persistence pattern: use the best tool for each component.
    """
    import psycopg2
    import boto3

    # 1. Deduct inventory from PostgreSQL (ACID, strongly consistent)
    pg_conn = psycopg2.connect("dbname=inventory")
    try:
        cur = pg_conn.cursor()

        # BEGIN transaction; if any item is out of stock, roll back all
        for item_id, qty in items_ordered.items():
            cur.execute(
                "UPDATE inventory SET stock = stock - %s WHERE id = %s AND stock >= %s",
                (qty, item_id, qty)
            )
            if cur.rowcount == 0:
                raise ValueError(f"Insufficient stock for item {item_id}")

        # Record order in PostgreSQL
        cur.execute(
            "INSERT INTO orders (id, status) VALUES (%s, %s)",
            (order_id, "confirmed")
        )

        pg_conn.commit()

    except Exception as e:
        pg_conn.rollback()
        raise e

    finally:
        cur.close()
        pg_conn.close()

    # 2. Update recommendation cache in DynamoDB (eventual consistency, fast)
    dynamodb = boto3.resource("dynamodb")
    recommendations_table = dynamodb.Table("recommendation_cache")

    # This write is asynchronous; if it fails, recommendations are slightly stale
    # but the order itself succeeded (decoupled concerns)
    for item_id in items_ordered.keys():
        recommendations_table.update_item(
            Key={"item_id": item_id},
            UpdateExpression="SET last_ordered_at = :now, order_count = order_count + 1",
            ExpressionAttributeValues={":now": int(time.time())}
        )

    return {"order_id": order_id, "status": "confirmed"}

Polyglot Persistence: Real-World Pattern

Most large-scale systems use multiple databases, each optimized for its access pattern:

  • PostgreSQL/MySQL: User accounts, orders, financial transactions (ACID critical)
  • Redis: Sessions, rate limits, leaderboards, caches (sub-millisecond GET/SET)
  • Cassandra/Bigtable: IoT telemetry, logs, metrics, events (write-heavy, time-series)
  • MongoDB/Firestore: Product catalogs, user profiles, content (document flexibility)
  • Elasticsearch: Full-text search, analytics, log aggregation (inverted index)
  • Neo4j: Social graphs, recommendation engines, fraud detection (relationship queries)

Example: Uber’s architecture uses Postgres for trip history (ACID), Cassandra for driver locations (base: eventual), Redis for ride matching cache (in-memory), and Elasticsearch for rider search (inverted index).

References

Software Architecture, Databases
distributed-systems
This post is licensed under CC BY 4.0 by the author.