NewSQL & Distributed SQL — ACID at Global Scale

NewSQL's core insight: you don't have to choose between horizontal scale and ACID transactions. Replicate data across nodes using a consensus protocol, shard the keyspace into ranges, and serve SQL on top — the app sees a single database; the engine handles the rest.

Let's start with a situation that's painfully real for a lot of engineering teams. Picture a global SaaS company — say, a fintech platform handling payments for users in the US, Europe, and Japan. The early days were easy: one beefy Postgres instance in us-east-1, 50 K requests per second, everything humming. Then growth hit.

The Three Pain Points That Force a Decision

Pain 1 — Tokyo users get 200 ms read latency. Your Postgres is in Virginia. A user in Tokyo fires a query, packets cross the Pacific (roughly 150 ms one-way), the query runs, and the response travels back. That's 300+ ms for what should feel instant. You can add a read replica in Tokyo — but now you have replication lag and your app must decide which replica to read from.

Pain 2 — Write throughput ceiling. At 200 K writes per second, your single Postgres primary is at 95% CPU. You can't just add another primary — Postgres has no built-in multi-primary mode. You start considering sharding: split users by ID mod 4 across four Postgres instances. Suddenly your engineers are writing shard-routing logic, schema migrations become nightmares, and any transaction that touches two users on different shards requires custom two-phase commit code.

Pain 3 — You can't drop ACID. This is a payments platform. "Eventual consistency" means a user's balance might temporarily show the wrong value. A transfer could debit Account A but fail before crediting Account B. Cassandra or DynamoDB would scale horizontally but they'd force you to build your own transaction layer — and that's a distributed systems PhD project, not a feature sprint.

What Each Option Gives You

With NewSQL, you write to the nearest replica (low latency for local users), ACID is enforced across regions (no dirty reads), SQL and JOINs work exactly as you'd expect, and the database automatically re-balances ranges as data grows — no shard routing code in your app.

When you type a SQL query at a NewSQL database, you're talking to something that looks exactly like a normal SQL database. But underneath that familiar interface, there are several layers doing very different jobs. Understanding this stack is the key to predicting how NewSQL databases behave — when they're fast, when they're slow, and what can go wrong.

Think of it like a post office. You hand a letter to the counter clerk — that's the SQL parser. The clerk checks the address and picks a route — that's the query planner. The letter then enters a sorting system that tracks exactly which letters go in which order — that's the distributed transaction layer. The actual trucks that carry identical copies of the letter to multiple delivery depots to prevent loss — that's the consensus replication layer. And at the bottom, the physical shelves and bins where envelopes are actually stored — that's the key-value store and disk.

Two Architectural Families

Not all NewSQL databases are built the same way. There are two dominant approaches, and understanding which family a database belongs to helps you predict how it will behave in your environment.

Before going deeper, let's pin down the six terms that come up in every NewSQL conversation. You'll see these in documentation, job interviews, and architecture diagrams. Each one earns its place — none is jargon for jargon's sake.

Google Spanner is the database that proved you could run one big SQL database across the entire planet — with the same correctness guarantees you get from a single Postgres on one server. When the design was published at OSDI 2012, the distributed systems community was surprised: not just that Google had built a globally distributed SQL database, but that they'd done it with what appeared to be a fundamentally new approach to distributed time.

Before Spanner, the accepted wisdom was that you couldn't have both global distribution and strong consistency without either (a) giving up on consistency during network partitions, or (b) paying an enormous latency penalty. Google found a third option: make the clocks so accurate that the uncertainty window — the gap between "it definitely happened before now" and "it definitely happened after now" — becomes small enough to commit around. (We'll explain "external consistency" in plain English in a moment — it's the stronger-than-serializable guarantee Spanner achieves.)

TrueTime — The Key Innovation

In a normal datacenter, servers synchronize their clocks via NTP (Network Time Protocol), which keeps them within about 50–200 ms of each other on a good day — far too imprecise for database transaction ordering. Google solved this by installing atomic clocks and GPS receivers in every datacenter. These hardware clocks keep servers within about 7 ms of each other globally. The Spanner team then built the TrueTime API, which doesn't give you a single timestamp — it gives you an interval (earliest, latest) representing the range of possible true times, where the uncertainty is bounded by the hardware.

Why does bounded uncertainty matter? Because Spanner can commit a transaction by waiting out the uncertainty window. If TrueTime says "the current time is somewhere in [T - 4ms, T + 4ms]", Spanner waits 4 ms after writing and then says: any transaction that started before this commit is guaranteed to have an earlier timestamp, and any transaction that starts after this commit is guaranteed to have a later timestamp. This property is called external consistency — the strongest possible consistency guarantee — and it means even clients outside the database who compare timestamps will see a consistent ordering.

Four Key Innovations

Companies have been sharding Postgres and MySQL for decades. Instagram sharded Postgres. Stripe shards Postgres. If sharding works for them, why do NewSQL databases exist? The answer comes down to one gnarly problem: cross-shard transactions.

When data lives on a single Postgres instance, a transaction is a local operation — begin, write some rows, commit. The database engine handles locking, versioning, and rollback internally. When data is split across four Postgres instances, and a single business operation (say, transferring money between two users) needs to atomically update rows on Shard 1 AND Shard 3 — you need a protocol to coordinate the commit across two independent databases. That protocol is called Two-Phase Commit (2PC), and it has significant problems.

The 2PC Problem

In Two-Phase Commit, a coordinator (often your application or a proxy) sends a "prepare" message to all shards involved. Each shard votes yes or no. If all vote yes, the coordinator sends "commit" to everyone. Sounds reasonable — but here's where it breaks:

• The coordinator can crash between "prepare" and "commit". Each participating shard has locked the relevant rows and is waiting. They can't commit (no commit message) and they can't roll back (they voted yes). This is called the 2PC "in-doubt" state — a hung distributed transaction that blocks all readers and writers on those rows until a human operator intervenes. This is the reason experienced engineers say "2PC is correct but fragile."
• Latency is additive. Phase 1 (prepare) requires a round-trip to all shards. Phase 2 (commit) requires another round-trip. In a multi-region setup, two cross-continent round-trips at 100 ms each means 200+ ms just for coordination — before any actual work happens.
• Operational complexity explodes. You need to track which shards hold which data, route queries appropriately, manage schema migrations across all shards simultaneously, and handle the case where some shards are ahead of others after a partial migration. None of this is free.

Four Key Differences

Before any NewSQL database can guarantee ACID transactions across multiple nodes, all those nodes need a way to agree on what was written and in what order. That agreement process is called consensus. Think of it like a committee vote: a write only "counts" once a majority of voting members say "yes, I recorded that." The algorithm most NewSQL databases use today is called Raft.

Raft is not magic — it is a carefully engineered rulebook for how a small cluster of nodes elects a leader, replicates each write to followers, and decides when a write is safe to call "committed." Every NewSQL database carves its keyspace into ranges (sometimes called tablets or regions), and each range runs its own independent Raft group — typically 3 or 5 replicas. This means a 30-node cluster might have thousands of independent Raft groups all humming in parallel.

The Four Guarantees Raft Provides

Soft Latency Numbers

Within one datacenter, a Raft commit round-trip is roughly 1–3 ms — fast enough that most OLTP apps don't notice it. Cross-region (e.g., US-East to EU-West, ~80 ms RTT), a Raft commit adds around 80–200 ms per write because the AppendEntries call must physically travel to the other continent and back. This is why NewSQL databases offer follower-read options for reads — you can read from a local replica (possibly a few ms stale) instead of forcing every read through the leader.

A single write to a single range is straightforward — one Raft group, one commit. But real-world transactions usually touch multiple rows that may not live on the same machine. Picture a money transfer: subtract $100 from Alice's account, add $100 to Bob's. If Alice's row sits on one node and Bob's row on another, the database must still guarantee atomicity — either both rows change, or neither does. No middle ground where Alice loses money but Bob never receives it. In SQL that looks like UPDATE accounts SET balance = balance - 100 WHERE id = 42 followed by UPDATE accounts SET balance = balance + 100 WHERE id = 77, but the hard part isn't the syntax — it's making both updates land or neither, across independent Raft groups. That's the distributed transaction problem.

Three Approaches Used in Practice

Latency Cost of Distribution

A distributed transaction pays a tax compared to a local transaction. Within one datacenter, a distributed 2PC transaction might add roughly 5–50 ms over a single-shard transaction — most of that is the extra Raft round-trips on multiple ranges plus lock coordination. Cross-region, that tax balloons to 100–500 ms because the Prepare messages must physically traverse long-haul fiber. This is why you want to co-locate frequently-joined data in the same range wherever possible — a pattern called geo-partitioning in CockroachDB.

CockroachDB started as an open-source project on GitHub in 2014, and Cockroach Labs (the company) was founded in 2015 by Spencer Kimball, Peter Mattis, and Ben Darnell — all former Google engineers who had worked on Google's internal distributed systems (Kimball and Mattis on the GFS/Colossus team; Darnell on Google Reader). The name is deliberate: cockroaches survive anything. The goal was to build a database that kept running even when nodes, racks, or entire datacenters went offline.

The most important practical detail: CockroachDB speaks the PostgreSQL wire protocol. This means the psql CLI, every Postgres driver (Python psycopg2, Java JDBC, Node.js pg), and most Postgres-compatible ORMs (Django ORM, SQLAlchemy, Hibernate) connect to CockroachDB without modification. For teams already on Postgres, the migration story is much smoother than switching to a brand-new API.

Key Features

Trade-offs to Know

CockroachDB is not a drop-in replacement with zero downsides. Every write goes through Raft consensus — in a single-region cluster that costs roughly 2–10 ms per transaction. A single-node Postgres write returns in under 1 ms. If your application is write-latency sensitive and fits on one machine (or one primary + replicas), Postgres is faster and simpler. Additionally, CockroachDB has a smaller extension ecosystem than Postgres — if you depend on PostGIS for geospatial or TimescaleDB for time-series, you'd need a different approach.

On the positive side: a well-tuned CockroachDB cluster can handle roughly 10,000–100,000 writes per second and scales linearly — add nodes, get more throughput. Reads scale even more easily because any follower with a valid lease can serve them.

CockroachDB is not the only open-source distributed SQL database worth knowing. Two strong alternatives are YugabyteDB (founded 2016, open-sourced 2017) and TiDB (founded 2015 by PingCAP). Both follow the same core model — range-based sharding, Raft per range, ACID transactions — but they make different wire-protocol and architectural choices that make each a better fit for certain teams.

Google built Spanner internally around 2007–2012 to solve its own global-scale data challenges — AdWords billing, Photos storage, Drive metadata — where even eventual consistency was unacceptable. For years it was Google-only. In 2017, Cloud Spanner launched as a publicly available managed service on Google Cloud. It is arguably the most technically sophisticated production database in existence: globally distributed, externally consistent, fully managed, with a 99.999% multi-region SLA.

The key thing to understand about Cloud Spanner is that it doesn't give you a machine to SSH into — it's a fully managed service. You pay for processing capacity in "nodes" and storage separately. Google handles everything: replication, failover, backups, software updates. You interact with it via standard SQL (ANSI 2011 compatible) or through client libraries.

NewSQL solves a real problem — but it also adds real complexity and latency. The question isn't "is NewSQL impressive?" (it is) — the question is "does my application actually need what NewSQL provides?" More often than engineers expect, the honest answer is no.

Every distributed NewSQL write goes through consensus — that's the irreducible cost of safety across nodes. If your application can fit on a single primary plus a few read replicas, that cost buys you nothing. Before adopting NewSQL, ask: do I need writes to scale horizontally? Do I need multi-region active-active? Do I need the database itself to survive a datacenter loss with zero data loss? If all three answers are "no," NewSQL is likely overkill.

Five Anti-Patterns — When NewSQL Is the Wrong Tool

NewSQL's biggest pitch is global ACID — the ability to run strongly consistent transactions across data that lives in multiple countries at once. On paper that sounds magical. In practice it costs latency, because making nodes in different continents agree requires messages to physically cross the ocean.

Here is the core mechanic: data is split into ranges, each range has a leaseholder (the leader), and all writes must reach a quorum of that range's replicas before they commit. When replicas are in the same datacenter, quorum is a ~1-3 ms hop. When replicas span continents, quorum requires a ~50-200 ms round-trip — that is the speed-of-light tax, not a bug.

Soft numbers to keep in mind: a same-region commit is roughly 1–3 ms. A cross-region commit (US ↔ Europe) is roughly 80–120 ms. US ↔ Asia-Pacific can be 140–200 ms. These are physics-bounded — you cannot engineer your way below the speed of light.

4 Strategies to Soften Latency

From an application developer's perspective, writing NewSQL transactions feels almost identical to writing Postgres transactions — you use BEGIN, run some SQL, and call COMMIT. But a few important differences lurk under the surface, and ignoring them will cause subtle bugs and mysterious latency spikes.

The biggest difference is that every write in a NewSQL transaction must travel through consensus. A long-running transaction holds locks across multiple Raft rounds. The longer a transaction is open, the more likely another transaction will conflict with it — leading to an abort. Unlike Postgres where aborts are rare, in distributed SQL under load, abort-and-retry is expected and must be handled in application code.

4 Best Practices

Code Patterns

import psycopg2
from psycopg2 import OperationalError

MAX_RETRIES = 5

def run_with_retry(conn_str, txn_fn):
    """
    Execute txn_fn(cursor) inside a transaction.
    Retries up to MAX_RETRIES times on serialization failure (40001).
    """
    conn = psycopg2.connect(conn_str)
    conn.autocommit = False

    for attempt in range(MAX_RETRIES):
        try:
            with conn.cursor() as cur:
                txn_fn(cur)       # run all SQL statements
            conn.commit()         # success — exit the loop
            return
        except OperationalError as e:
            conn.rollback()
            if e.pgcode == "40001":
                # Serialization failure — retry is correct
                if attempt == MAX_RETRIES - 1:
                    raise RuntimeError("Max retries exceeded") from e
                continue          # back to top of loop
            else:
                raise             # different error — do not retry

# Usage
def transfer_funds(cur):
    cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,))
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = %s", (2,))

run_with_retry("postgresql://localhost:26257/bank", transfer_funds)

-- Read data that is at most 10 seconds old.
-- CockroachDB will route this to the nearest follower replica,
-- so even a us-east app reading eu-west data pays only local latency.
SELECT
    order_id,
    status,
    total_amount
FROM orders
AS OF SYSTEM TIME follower_read_timestamp()
WHERE created_at > now() - interval '24 hours';

-- follower_read_timestamp() returns (now - closed_timestamp_lag)
-- Typical lag: 4.8 seconds (configurable via kv.closed_timestamp.target_duration)

-- Step 1: read the current version
SELECT id, balance, version FROM accounts WHERE id = 42;
-- Returns: { id: 42, balance: 500, version: 7 }

-- Step 2: update only if version matches what we read
UPDATE accounts
SET
    balance  = 400,
    version  = version + 1
WHERE
    id      = 42
    AND version = 7;   -- if another writer bumped version, 0 rows updated

-- Step 3: check rows_affected
-- If 0 → someone else changed the row; re-read and decide whether to retry
-- If 1 → success, your update won the race

A freshly provisioned NewSQL cluster will handle your workload — but a tuned cluster can handle 5-10× more. The good news: unlike hand-sharded Postgres, you are tuning a system that understands its own topology. Most gains come from five practical levers.

5 Performance Levers

Running a distributed SQL database is meaningfully more complex than running a single Postgres server. A single Postgres goes down and you notice immediately. A NewSQL cluster has nodes, ranges, leaseholders, replication factors, and certificates — any of which can silently degrade without taking down the whole system.

The good news: NewSQL clusters are self-healing for most failure modes. A node dies, the cluster re-elects leaseholders, re-replicates affected ranges, and continues serving. You do not need a manual failover script. What you do need is visibility and a runbook for the things the cluster cannot fix itself.

Four systems dominate the NewSQL landscape today. They all solve the same core problem — distributed ACID at scale — but they differ in wire compatibility, deployment model, licensing, and heritage. Picking the right one usually comes down to two questions: what wire protocol does my app already speak? and where do I need to run this?

NewSQL is not the right answer for every database problem — in fact, for the majority of applications it is overkill. But for a specific shape of problem, it is the only good answer. Understanding that shape clearly is what makes a senior engineer: knowing not just what a tool does, but precisely when to reach for it.

5 Winning Use Cases

You do not need to learn an entirely new ecosystem to work with NewSQL databases. Most of the tools you already know — psql, JDBC, Python database drivers — work unchanged because NewSQL databases speak existing wire protocols (Postgres or MySQL). The handful of new tools listed below are mostly operational: they help you deploy clusters, manage nodes, and monitor cluster health. Learn these six and you are ready to run NewSQL in production.

Connection Code Examples

# CockroachDB — psql works because CockroachDB speaks Postgres wire protocol
# The only difference from a normal Postgres connection is the port (26257 default)
psql "postgresql://root@localhost:26257/defaultdb?sslmode=disable"

# YugabyteDB YSQL — identical to Postgres, default port 5433
psql "host=localhost port=5433 dbname=yugabyte user=yugabyte"

# Once connected, everything is standard SQL
# CockroachDB-specific: show which node owns each range
SHOW RANGES FROM TABLE orders;

// CockroachDB JDBC configuration
// Uses the standard PostgreSQL JDBC driver — no special CockroachDB driver needed
// Key: set reWriteBatchedInserts=true for 2-3x faster batch INSERT performance
// Key: set ApplicationName so the DB Console shows which app is causing load

import java.sql.*;
import java.util.Properties;

public class CockroachExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:26257/bank"
                   + "?sslmode=require"
                   + "&reWriteBatchedInserts=true"  // batches writes → faster
                   + "&ApplicationName=payment-service";

        Properties props = new Properties();
        props.setProperty("user", "myuser");
        props.setProperty("password", "secret");

        try (Connection conn = DriverManager.getConnection(url, props)) {
            // IMPORTANT: CockroachDB recommends wrapping transactions in a retry loop
            // because distributed serializable transactions can return "40001 restart"
            // errors when two transactions conflict. The retry is your responsibility.
            boolean success = false;
            while (!success) {
                try (var tx = conn.prepareStatement("BEGIN")) {
                    tx.execute();
                    // ... your SQL here ...
                    conn.createStatement().execute("COMMIT");
                    success = true;
                } catch (SQLException e) {
                    if ("40001".equals(e.getSQLState())) {
                        conn.createStatement().execute("ROLLBACK");
                        // retry the whole transaction
                    } else throw e;
                }
            }
        }
    }
}

# Google Cloud Spanner — Python client
# Spanner does NOT speak Postgres wire protocol — it has its own gRPC API
# The google-cloud-spanner library wraps that API cleanly

from google.cloud import spanner

# 1. Create a client pointing at your Spanner instance
client  = spanner.Client(project="my-gcp-project")
instance = client.instance("my-spanner-instance")
database = instance.database("my-database")

# 2. Read-only transaction (uses bounded staleness — cheap, no locks)
with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT user_id, balance FROM accounts WHERE user_id = @uid",
        params={"uid": "usr_123"},
        param_types={"uid": spanner.param_types.STRING},
    )
    for row in results:
        print(f"User {row[0]}: balance = {row[1]}")

# 3. Read-write transaction (serializable, uses TrueTime for external consistency)
def transfer(transaction, from_id, to_id, amount):
    # Spanner automatically retries this function on serialization conflicts
    transaction.execute_update(
        "UPDATE accounts SET balance = balance - @amt WHERE user_id = @uid",
        params={"amt": amount, "uid": from_id},
        param_types={"amt": spanner.param_types.INT64, "uid": spanner.param_types.STRING},
    )
    transaction.execute_update(
        "UPDATE accounts SET balance = balance + @amt WHERE user_id = @uid",
        params={"amt": amount, "uid": to_id},
        param_types={"amt": spanner.param_types.INT64, "uid": spanner.param_types.STRING},
    )

database.run_in_transaction(transfer, "usr_123", "usr_456", 5000)

NewSQL databases attract enthusiastic adoption — and with that comes a wave of misconceptions. Some come from the marketing copy ("infinite scale!"), some from half-remembering a conference talk, and some from assuming NewSQL is simply "Postgres but bigger." Clearing these up before you start a project saves you from architectural regrets that are very painful to undo.

The stories below reflect failure patterns that have been observed across multiple teams adopting distributed SQL. Names and specifics are composites, but the failure modes are real and well-documented in engineering post-mortems. The price of learning these the hard way is measured in weeks of incident recovery and often in architectural rewrites. Read them now instead.

Eight rules cover the vast majority of NewSQL production decisions. They are not advanced tuning tips — they are the baseline minimum. Running through this checklist before you design your schema and before you go to production catches most problems before they become incidents.

These are the questions that come up in every system design interview, every architecture review, and every engineering Slack thread that ends with "should we migrate to CockroachDB?" Each answer is written for someone who understands databases generally but is still learning the distributed SQL space.

NewSQL databases are not isolated products — they sit at the intersection of relational database theory, distributed systems research, and practical infrastructure engineering. Understanding the six companion topics below gives you the full mental model needed to design with NewSQL confidently and to evaluate it honestly against alternatives.