NewSQL Databases: The Best of SQL and NoSQL

NewSQL Databases: The Best of SQL and NoSQL

NewSQL databases combine the ACID guarantees and familiar SQL interface of traditional relational databases with the horizontal scalability of NoSQL systems. They emerged to address the false dichotomy between SQL and NoSQL — you no longer have to choose between consistency and scalability. This guide covers the leading NewSQL databases, how they work, and when to use them.

Why NewSQL Emerged

Traditional SQL databases (PostgreSQL, MySQL) scale vertically — you buy a bigger server. When that is not enough, you resort to manual sharding, which sacrifices cross-shard transactions, complicates application logic, and makes operations painful.

NoSQL databases (Cassandra, DynamoDB) scale horizontally but sacrifice SQL support, complex queries, and strong consistency. Many applications need both: ACID transactions AND horizontal scalability.

NewSQL databases solve this by implementing distributed consensus protocols that allow ACID transactions across multiple nodes. They offer the scalability of NoSQL with the guarantees and query capabilities of SQL.

CockroachDB

CockroachDB is an open-source, distributed SQL database designed to survive disasters. Named after the resilient insect, it is built to survive disk failures, node failures, data center failures, and even region failures without losing data or availability.

Architecture

CockroachDB distributes data across nodes using ranges (chunks of approximately 512MB). Each range is replicated across 3+ nodes using the Raft consensus protocol. The SQL layer sits on top, parsing queries and routing them to the correct ranges. The key insight is that ranges can be split, merged, and rebalanced automatically as data grows.

-- CockroachDB: PostgreSQL-compatible SQL
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner STRING NOT NULL,
    balance DECIMAL NOT NULL CHECK (balance >= 0),
    region STRING NOT NULL DEFAULT 'us-east',
    created_at TIMESTAMP DEFAULT now()
);

-- ACID transaction across distributed nodes
BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE id = 'account-a';
UPDATE accounts SET balance = balance + 100.00 WHERE id = 'account-b';
COMMIT;
-- Both accounts may be on different nodes — transaction is still atomic

-- Multi-region configuration
ALTER DATABASE bank PRIMARY REGION "us-east1";
ALTER DATABASE bank ADD REGION "us-west1";
ALTER DATABASE bank ADD REGION "eu-west1";

-- Table-level locality for latency optimization
ALTER TABLE accounts SET LOCALITY REGIONAL BY ROW;
-- Each row lives in its region's closest nodes

Key Features

• Serializable isolation — the strongest isolation level, by default
• Automatic sharding and rebalancing — no manual shard management
• Multi-region survival — survive entire region outages
• PostgreSQL wire compatibility — use existing PostgreSQL drivers and tools
• Online schema changes — alter tables without downtime

TiDB

TiDB (Titanium Database) is an open-source NewSQL database that is MySQL-compatible. Developed by PingCAP, it separates the SQL processing layer (TiDB) from the distributed key-value storage layer (TiKV), allowing them to scale independently.

Architecture

TiDB has three core components: TiDB Server (stateless SQL layer, handles parsing, optimization, and execution), TiKV (distributed transactional key-value storage using Raft), and PD (Placement Driver) (cluster manager that handles metadata and data scheduling).

-- TiDB: MySQL-compatible syntax
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Standard SQL works as expected
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name
HAVING total_spent > 1000
ORDER BY total_spent DESC;

-- TiDB-specific: TiFlash for real-time analytics (columnar engine)
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- Analytical queries automatically use TiFlash (HTAP)
SELECT DATE(created_at) as day, SUM(total) as daily_revenue
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY day;

HTAP Capability

TiDB's unique feature is HTAP (Hybrid Transactional and Analytical Processing). TiFlash provides a columnar storage engine that replicates data from TiKV in real time. The optimizer automatically routes analytical queries to TiFlash and transactional queries to TiKV, eliminating the need for a separate OLAP data warehouse for many workloads.

Google Spanner

Google Spanner is the pioneer of NewSQL — the first system to achieve external consistency (linearizability) at global scale. It powers critical Google services and is available as Cloud Spanner on Google Cloud Platform.

Spanner uses TrueTime (GPS + atomic clocks) for global clock synchronization, enabling it to order transactions across data centers without communication. This is architecturally unique — no other database has this capability. See our distributed databases guide for the full technical details.

-- Google Spanner: Globally distributed relational database
CREATE TABLE Accounts (
    AccountId INT64 NOT NULL,
    OwnerId INT64 NOT NULL,
    Balance NUMERIC NOT NULL,
    Currency STRING(3) NOT NULL,
    LastModified TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
) PRIMARY KEY (AccountId);

-- Cross-region ACID transaction (Spanner handles distribution)
BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 500, LastModified = PENDING_COMMIT_TIMESTAMP()
  WHERE AccountId = 1001;
  UPDATE Accounts SET Balance = Balance + 500, LastModified = PENDING_COMMIT_TIMESTAMP()
  WHERE AccountId = 2002;
COMMIT;

VoltDB

VoltDB is an in-memory NewSQL database designed for extreme throughput. It partitions data across CPU cores and uses single-threaded execution per partition to eliminate locking overhead. VoltDB targets real-time applications like telecom, financial trading, and ad tech where microsecond latency matters.

Comparison: NewSQL vs Traditional SQL vs NoSQL

When to Choose NewSQL

• You need ACID transactions at scale: Financial systems, inventory management, or any application where data correctness across distributed nodes is critical
• You are outgrowing a single PostgreSQL/MySQL server: Instead of implementing manual sharding, migrate to a NewSQL database that handles distribution transparently
• Multi-region active-active deployments: NewSQL databases support writes in multiple regions with strong consistency
• You want SQL compatibility: Your team knows SQL, your tooling expects SQL, and you do not want to rewrite application logic for a NoSQL API

When NOT to Choose NewSQL

• Simple key-value access patterns: DynamoDB or Redis will be faster and simpler
• A single PostgreSQL server handles your load: Do not add distributed complexity prematurely
• Extreme write throughput with relaxed consistency: Cassandra may be a better fit
• Tight budget: NewSQL databases require more nodes (minimum 3 for consensus) and more expertise to operate

Migration Considerations

Migrating from PostgreSQL to CockroachDB or from MySQL to TiDB is relatively straightforward due to wire compatibility. However, watch for these differences:

• Query performance characteristics: Distributed consensus adds latency to every write. Single-row reads may be slower than a local PostgreSQL.
• Unsupported features: Stored procedures, triggers, and some extensions may not be available.
• Schema design: Primary key selection matters more — it determines data distribution. Avoid monotonically increasing keys (use UUIDs or hash prefixes).
• Connection pooling: You may need more connections since queries can be routed to multiple nodes.

For related topics, see our guides on ACID vs BASE, distributed databases, and sharding strategies.

Frequently Asked Questions

Is NewSQL replacing traditional SQL databases?

No. Traditional SQL databases like PostgreSQL remain the best choice for most applications. NewSQL is for the subset of applications that have outgrown single-server capacity and need distributed ACID transactions. The majority of applications never reach this scale.

How does CockroachDB performance compare to PostgreSQL?

For single-node workloads, PostgreSQL is faster — it does not have consensus overhead. CockroachDB shines when you need horizontal scalability: it can handle workloads that would require complex sharding with PostgreSQL. Expect 2-5x higher latency per query in exchange for linear scalability.

Can I use my existing ORM with NewSQL databases?

Generally yes. CockroachDB supports PostgreSQL ORMs (SQLAlchemy, ActiveRecord, Hibernate with the CockroachDB dialect). TiDB supports MySQL ORMs. However, some ORM features that rely on database-specific behavior (sequences, advisory locks) may need adjustments.

What is the minimum cluster size for NewSQL?

Most NewSQL databases require at least 3 nodes for consensus (a majority must agree for writes). For production with regional survival, 5-9 nodes across 3 availability zones is typical. This is significantly more infrastructure than a single PostgreSQL instance.

Should I choose CockroachDB or TiDB?

Choose CockroachDB if you are in a PostgreSQL ecosystem, need multi-region deployments, or prioritize strong consistency. Choose TiDB if you are in a MySQL ecosystem, need HTAP (combined transactional and analytical) workloads, or want to use the TiFlash columnar engine for real-time analytics.