Relational Databases at Scale

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

# Primary cluster in us-east-1
aws rds create-db-cluster \
  --db-cluster-identifier primary-cluster \
  --engine aurora-postgresql \
  --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 \
  --global-cluster-identifier global-app-cluster \
  --region eu-west-1

# Application writes to the primary, reads from its local region.
# AWS documents replication to secondary regions with "latency typically
# under a second". Two different operations exist and they are not the same:
#   switchover - planned, on a healthy global database, with NO data loss
#   failover   - unplanned, after losing the primary region; this is where
#                an RPO greater than zero is possible, because replication
#                is asynchronous
# AWS markets an RPO of 1 second and RTO under 1 minute for the managed
# path. Treat those as targets to validate in a game day, not guarantees.

# Backtrack must be enabled when the cluster is CREATED, and only
# works on Aurora MySQL. There is no way to add it to an existing
# cluster and no equivalent for Aurora PostgreSQL.
aws rds create-db-cluster \
  --db-cluster-identifier my-cluster \
  --engine aurora-mysql \
  --backtrack-window 259200          # 72 hours, the maximum

# Rewind the cluster to two hours ago
aws rds backtrack-db-cluster \
  --db-cluster-identifier my-cluster \
  --backtrack-to "2026-01-30T12:00:00Z"

# Aurora pauses the database, closes open connections and drops
# uncommitted work while it rewinds, so quiesce the application first.
# Check where it actually landed (Aurora snaps to the nearest
# consistent point, not necessarily the exact timestamp you asked for):
aws rds describe-db-cluster-backtracks \
  --db-cluster-identifier my-cluster

# Create Aurora Serverless v2 cluster
aws rds create-db-cluster \
  --db-cluster-identifier serverless-cluster \
  --engine aurora-postgresql \
  --serverless-v2-scaling-configuration \
    MinCapacity=0,MaxCapacity=16,SecondsUntilAutoPause=3600
# MinCapacity=0 enables auto-pause and needs a recent engine version
# (Aurora PostgreSQL 13.15+/14.12+/15.7+/16.3+, Aurora MySQL 3.08+).
# Older versions floor at 0.5. The full range today is 0-256 ACUs.

# 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 between the min and max you set. AWS documents the
# behaviour, not a fixed latency: capacity moves in increments as small as
# 0.5 ACU, and the increment grows with current capacity, so a large
# instance scales faster than a small one. Aurora does not wait for a quiet
# point: scaling happens with connections open, transactions in flight and
# tables locked, and does not disrupt work underway.
#
# Do not promise a number you have not measured. Load-test your own
# workload and watch the ServerlessDatabaseCapacity and ACUUtilization
# CloudWatch metrics to see how your cluster actually behaves.

# 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

# The clone is available quickly because no data is copied up front: it
# shares the source's storage pages and only allocates new ones as either
# side writes (copy-on-write). So initial storage cost is near zero and
# grows with divergence, not with the size of the source database.
# Test schema changes safely on the clone; drop it when done, with no
# impact on production. AWS doesn't publish a fixed creation time, so
# measure it on your own cluster rather than planning around a number.

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

# Read endpoint. It balances each *connection request* across the replicas,
# not each query: every statement on one connection lands on whichever
# replica that connection opened against. A pooled engine like the one below
# therefore pins each pooled connection to one replica for its lifetime.
# Caveat: if the cluster has no replicas at all, the reader endpoint connects
# to the primary, and writes through it will silently succeed.
reader_endpoint = "my-cluster.cluster-ro-xyz.us-east-1.rds.amazonaws.com"

# Python example with SQLAlchemy
from sqlalchemy import create_engine, text

# 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(text("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')"))
    conn.commit()

# Query users (read operation, served by whichever replica this connection
# was balanced onto). Aurora Replicas are asynchronous, so a read issued
# immediately after the commit above may not see Alice yet. Read your own
# writes from write_engine, or wait/retry, rather than assuming replicas are
# current.
with read_engine.connect() as conn:
    result = conn.execute(text("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;

# One transaction moving stock between two warehouse rows.
BEGIN TRANSACTION;

# Warehouse 1001
UPDATE Inventory
SET Quantity = Quantity - 100,
    LastUpdated = PENDING_COMMIT_TIMESTAMP()
WHERE WarehouseId = 1001 AND ProductId = 5000;

# Warehouse 2001
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
#
# A common misreading: rows do NOT live in a region. In a multi-region
# instance every replica holds the whole database, so "the US warehouse row"
# is not stored in nam any more than in eur. What the instance config
# decides is where the *replicas* are, and specifically which regions are
# read-write. Commit latency is set by how far apart those read-write
# regions are, because a commit needs a Paxos quorum among them, and by
# where the leader currently sits, not by which warehouse ids you touched.
# In nam-eur-asia3 the read-write regions are us-central1 and us-east1;
# europe-west1 and asia-east1 are read-only replicas that serve local reads
# without voting on writes. Measure commit latency for your own config
# rather than assuming a figure.

-- 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;
-- A strong read is served at a timestamp at least as recent as every
-- committed transaction, so it may have to check with the Paxos leader
-- before answering. That round trip is the cost you are paying.

-- Stale read (lower latency). Staleness is a session/transaction setting,
-- not a per-query one. In the GoogleSQL dialect used by this schema the
-- statement has no SPANNER. prefix, and it is understood by the Spanner JDBC
-- driver rather than by the client libraries or gcloud:
SET READ_ONLY_STALENESS = 'MAX_STALENESS 10s';

--   MAX_STALENESS 10s   -> Spanner picks a timestamp at most 10s old, and is
--                          free to pick a fresher one. This is what you want
--                          for "recent enough" reads.
--   EXACT_STALENESS 10s -> read as of exactly now-10s, no fresher.
-- (The PostgreSQL dialect spells the same setting SPANNER.READ_ONLY_STALENESS.)

SELECT ProductId, Quantity FROM Inventory
WHERE WarehouseId = 1001 AND ProductId = 5000;
-- A sufficiently stale read can be served by the nearest replica out of its
-- own state, with no leader round trip. That is where the saving comes from.

-- How stale can you go? Not 15 seconds: the real ceiling is the database's
-- version_retention_period, which defaults to 1 hour and is configurable up
-- to 1 week. Older versions are garbage-collected and become unreadable.
-- The number that matters at the short end is a floor, not a ceiling: Google
-- advises at least 10 seconds of staleness before a stale read is fast
-- enough to be worth it, because below that Spanner often still has to
-- consult the leader.

-- 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

-- T-SQL, run against the master database of the logical server.
-- Create Hyperscale database
CREATE DATABASE ProductionDB
  (EDITION = 'Hyperscale', SERVICE_OBJECTIVE = 'HS_Gen5_8');

-- Create a named replica for read scale-out. A named replica is its own
-- Azure SQL database resource: own name, own service objective, optionally
-- on a different logical server in the same region. Up to 30 of them.
-- (Separately, you can run 0-4 HA replicas, which are invisible hot
-- standbys for failover, not addressable databases.)
ALTER DATABASE ProductionDB
ADD SECONDARY ON SERVER 'replica-eastus2'
WITH (
  SERVICE_OBJECTIVE = 'HS_Gen5_4',
  SECONDARY_TYPE = 'Named'
);


/* Application connection strings (not T-SQL)

   Primary (writes):
     Server=primary.database.windows.net;
     Database=ProductionDB;
     ApplicationIntent=ReadWrite;

   Named replica (reads). You connect to the replica by name, so
   ApplicationIntent is not what routes you there. ApplicationIntent=ReadOnly
   is how you reach an *HA* replica on the primary's own connection string,
   and if no secondary exists it silently falls back to the read-write primary:
     Server=replica-eastus2.database.windows.net;
     Database=ProductionDB;
*/


-- Point-in-time restore, via Azure CLI (bash, not T-SQL).
-- Hyperscale backups are storage snapshots, so a same-region restore
-- "finishes in minutes instead of hours or days, even for multi-terabyte
-- databases" (Microsoft's wording): it is not a size-of-data operation.
-- Two exceptions that are size-of-data, and therefore slow:
--   * geo-restore to another region
--   * changing storage/zone redundancy as part of the restore
az sql db restore \
  --dest-name ProductionDB_Recovered \
  --resource-group myResourceGroup \
  --server myServer \
  --name ProductionDB \
  --time "2026-01-30T14:30:00"