Relational Databases at Scale

-- Create Aurora Global Database cluster
aws rds create-global-cluster \
  --global-cluster-identifier global-app-cluster \
  --engine aurora-postgresql \
  --engine-version 15.4

-- Primary cluster in us-east-1
aws rds create-db-cluster \
  --db-cluster-identifier primary-cluster \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --global-cluster-identifier global-app-cluster \
  --region us-east-1

-- Secondary cluster in eu-west-1 (read-only)
aws rds create-db-cluster \
  --db-cluster-identifier secondary-cluster-eu \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --global-cluster-identifier global-app-cluster \
  --region eu-west-1

-- Application writes to primary, reads from local region
-- Replication lag typically < 1 second
-- RPO (Recovery Point Objective): < 1 second
-- RTO (Recovery Time Objective): < 1 minute for cross-region failover

-- Enable backtrack with 72-hour window
aws rds modify-db-cluster \
  --db-cluster-identifier my-cluster \
  --backtrack-window 259200 \
  --apply-immediately

-- Rewind database to 2 hours ago
aws rds backtrack-db-cluster \
  --db-cluster-identifier my-cluster \
  --backtrack-to "2026-01-30 12:00:00"

-- Database instantly reverts, no downtime
-- All data from 12:00 to 14:00 is "undone"
-- Perfect for recovering from accidental changes

-- Create Aurora Serverless v2 cluster
aws rds create-db-cluster \
  --db-cluster-identifier serverless-cluster \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --serverless-v2-scaling-configuration \
    MinCapacity=0.5,MaxCapacity=16

-- Create serverless instance
aws rds create-db-instance \
  --db-instance-identifier serverless-instance \
  --db-cluster-identifier serverless-cluster \
  --engine aurora-postgresql \
  --db-instance-class db.serverless

-- Scales automatically:
-- - Low traffic (night): 0.5-2 ACUs
-- - Medium traffic (day): 4-8 ACUs
-- - High traffic (Black Friday): 12-16 ACUs
-- Scaling happens in < 1 second without dropping connections

-- Clone production database for testing
aws rds restore-db-cluster-to-point-in-time \
  --source-db-cluster-identifier production-cluster \
  --db-cluster-identifier test-clone \
  --restore-type copy-on-write \
  --use-latest-restorable-time

-- Clone created in ~5 minutes
-- Initial storage cost: near zero (copy-on-write)
-- Test schema changes safely on clone
-- Drop clone when done - no impact on production

-- Application connection routing
# Write endpoint (primary instance)
writer_endpoint = "my-cluster.cluster-xyz.us-east-1.rds.amazonaws.com"

# Read endpoint (automatically load-balances across replicas)
reader_endpoint = "my-cluster.cluster-ro-xyz.us-east-1.rds.amazonaws.com"

# Python example with SQLAlchemy
from sqlalchemy import create_engine

# Writes go to primary
write_engine = create_engine(f"postgresql://user:pass@{writer_endpoint}/db")

# Reads use read replicas
read_engine = create_engine(f"postgresql://user:pass@{reader_endpoint}/db")

# Insert user (write operation)
with write_engine.connect() as conn:
    conn.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")

# Query users (read operation, load-balanced across replicas)
with read_engine.connect() as conn:
    result = conn.execute("SELECT * FROM users WHERE name = 'Alice'")
    print(result.fetchall())

-- Create multi-region Spanner instance
gcloud spanner instances create global-inventory \
  --config=nam-eur-asia3 \
  --description="Multi-region inventory system" \
  --processing-units=1000

-- Schema with interleaved tables (co-located for performance)
CREATE TABLE Warehouses (
  WarehouseId INT64 NOT NULL,
  Location STRING(100),
  Region STRING(50)
) PRIMARY KEY (WarehouseId);

CREATE TABLE Inventory (
  WarehouseId INT64 NOT NULL,
  ProductId INT64 NOT NULL,
  Quantity INT64,
  LastUpdated TIMESTAMP NOT NULL OPTIONS (
    allow_commit_timestamp=true
  )
) PRIMARY KEY (WarehouseId, ProductId),
  INTERLEAVE IN PARENT Warehouses ON DELETE CASCADE;

-- Global transaction: Deduct from US warehouse, add to EU warehouse
BEGIN TRANSACTION;

-- Deduct inventory in US (nam region)
UPDATE Inventory
SET Quantity = Quantity - 100,
    LastUpdated = PENDING_COMMIT_TIMESTAMP()
WHERE WarehouseId = 1001 AND ProductId = 5000;

-- Add inventory in EU (eur region)
UPDATE Inventory
SET Quantity = Quantity + 100,
    LastUpdated = PENDING_COMMIT_TIMESTAMP()
WHERE WarehouseId = 2001 AND ProductId = 5000;

COMMIT TRANSACTION;

-- Spanner ensures:
-- 1. Both updates succeed or both fail (atomicity)
-- 2. All observers see consistent state (no partial updates visible)
-- 3. Transaction commits in globally consistent order
-- 4. Typical latency: 50-100ms (synchronous cross-region replication)

-- Parent table: Customers
CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  Name STRING(100),
  Email STRING(255)
) PRIMARY KEY (CustomerId);

-- Child table: Orders (interleaved in Customers)
CREATE TABLE Orders (
  CustomerId INT64 NOT NULL,
  OrderId INT64 NOT NULL,
  TotalAmount NUMERIC,
  OrderDate TIMESTAMP
) PRIMARY KEY (CustomerId, OrderId),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;

-- Query customer with all orders (single-server operation)
SELECT c.Name, c.Email, o.OrderId, o.TotalAmount
FROM Customers c
JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE c.CustomerId = 12345;

-- Because Orders is interleaved, this JOIN executes on a single
-- Spanner server without network hops. Much faster than distributed JOIN.

-- Strong read (default): Globally consistent
SELECT ProductId, Quantity FROM Inventory
WHERE WarehouseId = 1001 AND ProductId = 5000;
-- Latency: 10-50ms (waits for latest committed data)

-- Stale read: Up to 15 seconds old (lower latency)
-- Staleness is set at the session/transaction level, not per query
SET SPANNER.READ_ONLY_STALENESS = 'EXACT_STALENESS 15s';

SELECT ProductId, Quantity FROM Inventory
WHERE WarehouseId = 1001 AND ProductId = 5000;
-- Latency: 5-20ms (reads potentially stale data)

-- Use strong reads for:
-- - Financial transactions
-- - Inventory that must be exact
-- - Critical consistency requirements

-- Use stale reads for:
-- - Analytics dashboards
-- - Product catalogs
-- - Reports where slight delay is acceptable

-- Create distributed table for multi-tenant SaaS
CREATE TABLE events (
  tenant_id bigint,
  event_id bigserial,
  event_type text,
  event_data jsonb,
  created_at timestamptz default now()
);

-- Distribute table across worker nodes by tenant_id
-- All data for a tenant is co-located on same shard
SELECT create_distributed_table('events', 'tenant_id');

-- Create reference table (replicated to all workers)
CREATE TABLE event_types (
  type_id serial primary key,
  type_name text,
  description text
);
SELECT create_reference_table('event_types');

-- Queries are automatically routed and parallelized
-- Query for single tenant (executes on 1 worker node)
SELECT COUNT(*) FROM events
WHERE tenant_id = 12345
  AND created_at > NOW() - INTERVAL '1 day';

-- Query across all tenants (executes in parallel on all workers)
SELECT tenant_id, COUNT(*) as event_count
FROM events
WHERE created_at > NOW() - INTERVAL '1 day'
GROUP BY tenant_id
ORDER BY event_count DESC
LIMIT 100;

-- Citus parallelizes query across workers, aggregates results

-- Create Hyperscale database
CREATE DATABASE ProductionDB
  (EDITION = 'Hyperscale', SERVICE_OBJECTIVE = 'HS_Gen5_8');

-- Create named replica for read scale-out
ALTER DATABASE ProductionDB
ADD SECONDARY ON SERVER 'replica-eastus2'
WITH (
  SERVICE_OBJECTIVE = 'HS_Gen5_4',
  SECONDARY_TYPE = 'Named'
);

-- Application connection strings
-- Primary (writes)
Server=primary.database.windows.net;
Database=ProductionDB;
ApplicationIntent=ReadWrite;

-- Named replica (reads)
Server=replica-eastus2.database.windows.net;
Database=ProductionDB;
ApplicationIntent=ReadOnly;

-- Fast database restore (point-in-time, via Azure CLI)
-- Traditional restore: Hours for 10 TB database
-- Hyperscale restore: Minutes (uses storage snapshots)
az sql db restore \
  --dest-name ProductionDB_Recovered \
  --resource-group myResourceGroup \
  --server myServer \
  --name ProductionDB \
  --time "2026-01-30T14:30:00"