CockroachDB: Distributed SQL untuk Aplikasi Skala Global

# Download CockroachDB binary terbaru
wget -qO- https://binaries.cockroachdb.com/cockroach-v24.1.0.linux-amd64.tgz | tar xvz

# Pindahkan ke PATH
sudo cp -i cockroach-v24.1.0.linux-amd64/cockroach /usr/local/bin/

# Verifikasi instalasi
cockroach version

# Instal client library (opsional, untuk bahasa tertentu)
# Python:
pip install psycopg2-binary sqlalchemy

# Node.js:
npm install pg

# Buat direktori untuk data
mkdir -p ~/cockroach-data

# Mulai single-node cluster (insecure mode untuk development)
cockroach start-single-node \
  --insecure \
  --store=~/cockroach-data \
  --listen-addr=localhost:26257 \
  --http-addr=localhost:8080 \
  --background

# Buka SQL shell
cockroach sql --insecure --host=localhost:26257

# Di dalam SQL shell, buat database dan tabel:
CREATE DATABASE mydb;
USE mydb;

CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name STRING NOT NULL,
    email STRING UNIQUE NOT NULL,
    region STRING DEFAULT 'us-east1',
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO users (name, email, region) VALUES
    ('Budi', 'budi@example.com', 'ap-southeast-1'),
    ('Andi', 'andi@example.com', 'eu-west-1'),
    ('Sari', 'sari@example.com', 'us-east1');

SELECT * FROM users;

# Node 1
cockroach start \
  --insecure \
  --store=node1 \
  --listen-addr=localhost:26257 \
  --http-addr=localhost:8080 \
  --join=localhost:26257,localhost:26258,localhost:26259 \
  --background

# Node 2
cockroach start \
  --insecure \
  --store=node2 \
  --listen-addr=localhost:26258 \
  --http-addr=localhost:8081 \
  --join=localhost:26257,localhost:26258,localhost:26259 \
  --background

# Node 3
cockroach start \
  --insecure \
  --store=node3 \
  --listen-addr=localhost:26259 \
  --http-addr=localhost:8082 \
  --join=localhost:26257,localhost:26258,localhost:26259 \
  --background

# Initialize cluster (hanya sekali)
cockroach init --insecure --host=localhost:26257

# Cek status cluster
cockroach node status --insecure --host=localhost:26257

-- CockroachDB SELALU dalam serializable isolation
-- Tidak perlu SET TRANSACTION ISOLATION LEVEL

-- Contoh: Transfer saldo dengan jaminan konsistensi
-- Sesi 1:
BEGIN;
  SELECT balance FROM accounts WHERE id = 'acc-1'; -- 1000
  SELECT balance FROM accounts WHERE id = 'acc-2'; -- 500
  UPDATE accounts SET balance = balance - 100 WHERE id = 'acc-1';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'acc-2';
COMMIT;

-- Jika ada Sesi 2 yang mencoba update data yang sama
-- di saat bersamaan, salah satu akan mengalami
-- serialization conflict dan otomatis di-retry

-- Mengecek isolation level (selalu SERIALIZABLE):
SHOW TRANSACTION ISOLATION LEVEL;

-- Retry logic di aplikasi (penting!)
-- CockroachDB mengembalikan error 40001 (serialization failure)
-- ketika transaksi perlu di-retry

import psycopg2
from psycopg2 import errors
import time

def execute_with_retry(conn, func, max_retries=5):
    """Execute function dalam transaction dengan retry logic."""
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    result = func(cur)
            return result
        except errors.SerializationFailure:
            conn.rollback()
            wait = 0.1 * (2 ** attempt)  # exponential backoff
            print(f"Retry attempt {attempt + 1}, waiting {wait}s...")
            time.sleep(wait)
    raise Exception("Transaction failed after max retries")

# Contoh penggunaan
conn = psycopg2.connect("postgresql://root@localhost:26257/mydb")

def transfer_balance(cur):
    cur.execute("""
        UPDATE accounts SET balance = balance - 100
        WHERE id = 'acc-1'
    """)
    cur.execute("""
        UPDATE accounts SET balance = balance + 100
        WHERE id = 'acc-2'
    """)
    return True

execute_with_retry(conn, transfer_balance)

-- Melihat informasi ranges
SHOW RANGES FROM TABLE users;

-- Melihat distribusi ranges di seluruh node
SELECT
    range_id,
    start_key,
    end_key,
    lease_holder,
    replicas
FROM crdb_internal.ranges
ORDER BY range_id;

-- Melihat statistik ranges
SELECT
    range_id,
    key_bytes,
    val_bytes,
    live_bytes,
    lease_holder
FROM crdb_internal.range_stats
LIMIT 20;

-- Mengubah ukuran range (untuk testing/scenario tertentu)
ALTER TABLE users CONFIGURE ZONE USING
    range_min_bytes = 134217728,  -- 128 MiB
    range_max_bytes = 536870912;  -- 512 MiB

-- Manual split (biasanya tidak perlu karena otomatis)
ALTER TABLE users SPLIT AT VALUES ('M');

-- Manual merge (menggabungkan range kecil)
ALTER TABLE users UNSPLIT AT VALUES ('M');

-- Melihat hot ranges
SELECT
    range_id,
    lease_holder,
    reads_per_second,
    writes_per_second
FROM crdb_internal.ranges_no_leases r
JOIN crdb_internal.node_metrics m ON r.lease_holder = m.node_id
ORDER BY reads_per_second DESC
LIMIT 10;

-- HINDARI: Auto-increment ID bisa menyebabkan hot spot
-- Semua write masuk ke range terakhir
CREATE TABLE bad_example (
    id SERIAL PRIMARY KEY,
    data STRING
);

-- LEBIH BAIK: Hash-sharded primary key
CREATE TABLE orders (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- ATAU: Gunakan hash-sharded index untuk kolom sequential
CREATE INDEX idx_orders_created ON orders (created_at)
    USING HASH;

-- Untuk tabel besar, pertimbangkan composite key dengan locality
CREATE TABLE events (
    region STRING,
    created_at TIMESTAMPTZ,
    event_id UUID DEFAULT gen_random_uuid(),
    payload JSONB,
    PRIMARY KEY (region, created_at, event_id)
);

# Node di US East
cockroach start \
  --insecure \
  --store=node-us-east \
  --listen-addr=us-east.cockroach.internal:26257 \
  --join=node-us-east:26257,node-eu-west:26257,node-ap-south:26257 \
  --locality=region=us-east,zone=us-east-1a \
  --background

# Node di EU West
cockroach start \
  --insecure \
  --store=node-eu-west \
  --listen-addr=eu-west.cockroach.internal:26257 \
  --join=node-us-east:26257,node-eu-west:26257,node-ap-south:26257 \
  --locality=region=eu-west,zone=eu-west-1a \
  --background

# Node di AP South
cockroach start \
  --insecure \
  --store=node-ap-south \
  --listen-addr=ap-south.cockroach.internal:26257 \
  --join=node-us-east:26257,node-eu-west:26257,node-ap-south:26257 \
  --locality=region=ap-south,zone=ap-south-1a \
  --background

# Initialize cluster
cockroach init --insecure --host=node-us-east:26257

-- Konfigurasi zona untuk tabel: replika di setiap region
ALTER TABLE users CONFIGURE ZONE USING
    num_replicas = 3,
    constraints = '{+region=us-east: 1, +region=eu-west: 1, +region=ap-south: 1}',
    lease_preferences = '[[+region=us-east]]';

-- Konfigurasi database-level
ALTER DATABASE mydb CONFIGURE ZONE USING
    num_replicas = 3,
    constraints = '{+region=us-east: 1, +region=eu-west: 1}';

-- Melihat zone configuration
SHOW ZONE CONFIGURATION FOR TABLE users;
SHOW ZONE CONFIGURATION FOR DATABASE mydb;

-- Partitioning berdasarkan region
ALTER TABLE users PARTITION BY LIST (region) (
    PARTITION us_east VALUES IN ('us-east'),
    PARTITION eu_west VALUES IN ('eu-west'),
    PARTITION ap_south VALUES IN ('ap-south')
);

-- Setelah partition, atur placement setiap partisi
ALTER PARTITION us_east OF TABLE users CONFIGURE ZONE USING
    constraints = '{+region=us-east: 2}',
    lease_preferences = '[[+region=us-east]]';

ALTER PARTITION eu_west OF TABLE users CONFIGURE ZONE USING
    constraints = '{+region=eu-west: 2}',
    lease_preferences = '[[+region=eu-west]]';

ALTER PARTITION ap_south OF TABLE users CONFIGURE ZONE USING
    constraints = '{+region=ap-south: 2}',
    lease_preferences = '[[+region=ap-south]]';

-- Aktifkan rangefeed di cluster
SET CLUSTER SETTING kv.rangefeed.enabled = true;

-- Changefeed ke Kafka
CREATE CHANGEFEED FOR TABLE users, orders
INTO 'kafka://kafka-broker:9092?topic_prefix=cdc_'
WITH updated, resolved, envelope = 'wrapped';

-- Changefeed ke cloud storage (S3, GCS, Azure)
CREATE CHANGEFEED FOR TABLE users
INTO 's3://my-bucket/cdc/users?AWS_ACCESS_KEY_ID={key}&AWS_SECRET_ACCESS_KEY={secret}'
WITH format = 'parquet', compression = 'gzip';

-- Changefeed ke webhook
CREATE CHANGEFEED FOR TABLE orders
INTO 'webhook-https://my-api.com/cdc-webhook?insecure_tls_skip_verify=true'
WITH webhook_auth_header = 'Bearer my-secret-token',
     webhook_client_timeout = '5s',
     envelope = 'wrapped';

-- Changefeed dengan filter dan transformasi
CREATE CHANGEFEED FOR TABLE orders
INTO 'kafka://kafka-broker:9092'
WITH updated, envelope = 'wrapped',
     format = 'json',
     key_in_value;

-- Changefeed hanya untuk kolom tertentu (cdc_prev berguna untuk audit)
CREATE CHANGEFEED FOR TABLE orders (id, status, total)
INTO 'kafka://kafka-broker:9022?topic_prefix=order_changes'
WITH updated, resolved;

-- Melihat semua changefeed jobs
SHOW JOBS WHEN COMPLETE SELECT * FROM [SHOW CHANGEFEED JOBS];

-- Melihat status changefeed
SELECT job_id, status, created, description
FROM [SHOW CHANGEFEED JOBS];

-- Pause changefeed
PAUSE JOB 12345;

-- Resume changefeed
RESUME JOB 12345;

-- Cancel changefeed
CANCEL JOB 12345;

-- Melihat changefeed yang sedang aktif
SELECT
    job_id,
    job_type,
    status,
    fraction_completed,
    high_water_timestamp
FROM crdb_internal.jobs
WHERE job_type = 'CHANGEFEED';

-- Mengubah sink URI changefeed
ALTER CHANGEFEED 12345 SET sink_uri = 'kafka://new-broker:9092';

-- Menambah tabel ke changefeed
ALTER CHANGEFEED 12345 ADD TABLE new_table;

-- Pastikan node sudah diberi locality saat startup
-- Set database sebagai multi-region
ALTER DATABASE mydb SET PRIMARY REGION "us-east1";
ALTER DATABASE mydb ADD REGION "eu-west1";
ALTER DATABASE mydb ADD REGION "ap-southeast-1";

-- Set survival goal: survive region failure
ALTER DATABASE mydb SURVIVE REGION FAILURE;

-- Melihat region yang terdaftar
SHOW REGIONS FROM DATABASE mydb;

-- Tabel global: dibaca cepat dari semua region, ditulis dari region utama
CREATE TABLE products (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name STRING NOT NULL,
    price DECIMAL(10,2)
) LOCALITY GLOBAL;

-- Tabel regional by row: setiap baris dikaitkan dengan region tertentu
CREATE TABLE user_accounts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    email STRING NOT NULL,
    crdb_region crdb_internal_region NOT NULL DEFAULT gateway_region(),
    name STRING NOT NULL
) LOCALITY REGIONAL BY ROW;

-- Baris akan otomatis disimpan di region yang sesuai
INSERT INTO user_accounts (email, name) VALUES ('budi@id.com', 'Budi');
-- Data ini akan disimpan di region AP (jika koneksi dari sana)

-- Membaca dari region spesifik
SELECT * FROM user_accounts
WHERE crdb_region = 'ap-southeast-1';

-- Tabel regional by table: semua data di satu region
CREATE TABLE local_configs (
    key STRING PRIMARY KEY,
    value STRING
) LOCALITY REGIONAL BY TABLE IN REGION "us-east1";

-- Gunakan gateway_region() untuk otomatis mengaitkan baris
-- dengan region dari koneksi
INSERT INTO user_accounts (email, name, crdb_region)
VALUES ('user@local.com', 'Local User', gateway_region());

-- Cross-region join tetap bisa dilakukan
-- tapi coba hindari untuk latensi rendah
SELECT
    u.name,
    u.crdb_region,
    o.total
FROM user_accounts u
JOIN orders o ON u.id = o.user_id
WHERE u.crdb_region = gateway_region();

-- Gunakan GLOBAL table untuk data yang jarang berubah
-- dan dibutuhkan di semua region (lookup table)
CREATE TABLE countries (
    code STRING PRIMARY KEY,
    name STRING NOT NULL
) LOCALITY GLOBAL;

-- Statistik untuk monitoring cross-region
SELECT
    u.crdb_region,
    COUNT(*) as user_count,
    AVG(o.total) as avg_order
FROM user_accounts u
JOIN orders o ON u.id = o.user_id
GROUP BY u.crdb_region;

-- Analisis query plan
EXPLAIN SELECT * FROM users WHERE region = 'us-east';

-- EXPLAIN dengan detail
EXPLAIN (VERBOSE, TYPES)
    SELECT u.name, COUNT(o.id)
    FROM users u JOIN orders o ON u.id = o.user_id
    GROUP BY u.name;

-- EXPLAIN ANALYZE (mengeksekusi query dan menampilkan statistik)
EXPLAIN ANALYZE
    SELECT * FROM orders WHERE total > 100 ORDER BY created_at DESC;

-- Melihat statistik tabel
SHOW STATISTICS FOR TABLE users;

-- Mengumpulkan statistik manual
ANALYZE users;

-- Melihat index usage
SELECT
    ti.index_name,
    ti.index_type,
    ts.total_reads,
    ts.last_read
FROM crdb_internal.table_indexes ti
JOIN crdb_internal.index_usage_statistics ts
    ON ti.index_id = ts.index_id AND ti.table_id = ts.table_id
WHERE ti.descriptor_name = 'users';

-- Node liveness
SELECT node_id, address, is_live
FROM crdb_internal.gossip_nodes;

-- Replication status
SELECT
    store_id,
    node_id,
    range_count,
    lease_count,
    total_bytes
FROM crdb_internal.kv_store_status;

-- Cluster settings yang berguna
SET CLUSTER SETTING server.time_until_store_dead = '10m';
SET CLUSTER SETTING kv.snapshot_rebalance.max_rate = '64 MiB';
SET CLUSTER SETTING kv.rangefeed.enabled = true;

-- SQL activity monitoring
SELECT
    query,
    count,
    service_lat_avg,
    service_lat_p99
FROM crdb_internal.node_statement_statistics
ORDER BY count DESC
LIMIT 20;