NewSQL Databases: CockroachDB, TiDB, and Google Spanner

traditional_sql:
  pros: "ACID, mature, well-understood"
  cons: "Can't scale horizontally"

nosql:
  pros: "Horizontal scaling, high throughput"
  cons: "Weak consistency, no ACID"

newsql:
  goal: "Best of both worlds"
  features:
    - "Distributed architecture"
    - "ACID transactions"
    - "Horizontal scaling"
    - "SQL interface"

cockroachdb:
  architecture: "Distributed, multi-active"
  consensus: "Raft consensus"
  replication: "3-way minimum"
  sharding: "Range-based automatic"
  sql: "PostgreSQL-compatible"

# Connect to CockroachDB
import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=26257,
    database="defaultdb",
    user="root",
    password="password"
)

# Create distributed table
cur = conn.cursor()

# Geo-partitioned table
cur.execute("""
    CREATE TABLE users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email STRING,
        region STRING,
        created_at TIMESTAMP DEFAULT now()
    ) PARTITION BY LIST (region) (
        PARTITION us VALUES IN ('us-east', 'us-west'),
        PARTITION eu VALUES IN ('eu-west', 'eu-east'),
        PARTITION default VALUES IN (DEFAULT)
    )
""")

# Transaction with retry
def transfer_funds(conn, from_id, to_id, amount):
    with conn.cursor() as cur:
        while True:
            try:
                cur.execute("BEGIN")
                cur.execute("""
                    UPDATE accounts 
                    SET balance = balance - %s 
                    WHERE id = %s AND balance >= %s
                """, (amount, from_id, amount))
                
                if cur.rowcount == 0:
                    cur.execute("ROLLBACK")
                    return False, "Insufficient funds"
                
                cur.execute("""
                    UPDATE accounts 
                    SET balance = balance + %s 
                    WHERE id = %s
                """, (amount, to_id))
                
                cur.execute("COMMIT")
                return True, "Transfer complete"
                
            except psycopg2.errors.SerializationFailure:
                cur.execute("ROLLBACK")
                continue

tidb:
  components:
    - "TiDB Server (SQL layer)"
    - "TiKV (storage engine)"
    - "PD (placement driver)"
  consensus: "Raft"
  features:
    - "HTAP (Hybrid Transactional/Analytical)"
    - "Automatic sharding"
    - "Strong consistency"

# TiDB with Python

import pymysql

conn = pymysql.connect(
    host="localhost",
    port=4000,
    user="root",
    password="password",
    database="test"
)

cur = conn.cursor()

# Create table with AUTO_RANDOM for distributed IDs
cur.execute("""
    CREATE TABLE orders (
        id BIGINT PRIMARY KEY AUTO_RANDOM,
        customer_id BIGINT NOT NULL,
        total DECIMAL(10,2),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# Distributed transaction (using optimistic locking)
cur.execute("""
    SELECT id, balance, version 
    FROM accounts 
    WHERE id IN (%s, %s)
    FOR UPDATE
""", (from_id, to_id))

# Update with version check
cur.execute("""
    UPDATE accounts 
    SET balance = balance - %s, version = version + 1 
    WHERE id = %s AND version = %s
""", (amount, from_id, from_version))

if cur.rowcount == 0:
    print("Concurrent modification detected")

spanner:
  provider: "Google Cloud"
  consistency: "TrueTime (GPS + atomic clocks)"
  features:
    - "Globally distributed"
    - "Strong consistency"
    - "Automatic sharding"
    - "Schema changes online"

# Google Spanner with Python

from google.cloud import spanner

client = spanner.Client(instance_id, database_id)

# Transaction with read-your-writes consistency
def update_inventory(instance_id, product_id, quantity):
    transaction = client.transaction()
    
    try:
        snapshot = transaction.read(
            table='inventory',
            columns=['product_id', 'quantity'],
            keys=[product_id]
        )
        
        row = list(snapshot)[0]
        new_quantity = row[1] + quantity
        
        transaction.update(
            table='inventory',
            columns=['product_id', 'quantity'],
            values=[(product_id, new_quantity)]
        )
        
        transaction.commit()
        
    except Exception as e:
        transaction.rollback()
        raise e

newsql_use_cases:
  - "Global e-commerce platforms"
  - "Financial transactions needing ACID"
  - "Multi-region applications"
  - "Real-time analytics + transactions"
  - "Replace sharded MySQL/PostgreSQL"

# NewSQL best practices

design:
  - "Choose right partition key"
  - "Avoid cross-partition transactions"
  - "Use serializable isolation carefully"
  - "Monitor latencies across regions"

performance:
  - "Place replicas near users"
  - "Batch operations when possible"
  - "Use follower reads for analytics"