CockroachDB

**Essential Configuration:**

1. **Cluster Startup Flags:**
```bash
cockroach start --certs-dir=/etc/cockroach/certs --store=/var/lib/cockroach --listen-addr=0.0.0.0:26257 --http-addr=0.0.0.0:8080 --join=node1:26257,node2:26257,node3:26257 --cache=25% \ # Memory for cache
--max-sql-memory=25% \ # Memory for SQL queries
--locality=region=us-east,zone=a # Geo-locality
```

2. **Connection String Formats:**
```bash
# Basic connection
postgresql://user:password@host:26257/database

# Multi-node with load balancing
postgresql://user:password@host1:26257,host2:26257,host3:26257/database

# With SSL verification
postgresql://user:password@host:26257/database?sslmode=verify-full&sslrootcert=/path/to/ca.crt

# Application failover
postgresql://user:password@lb.example.com:26257/database?application_name=myapp
```

3. **Multi-Region Configuration:**
```sql
-- Set up regions
ALTER DATABASE mydb PRIMARY REGION "us-east-1";
ALTER DATABASE mydb ADD REGION "us-west-1";
ALTER DATABASE mydb ADD REGION "eu-west-1";

-- Geo-partitioned table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email STRING,
region STRING,
data JSONB,
INDEX (region)
) LOCALITY REGIONAL BY ROW AS region;

-- Regional table (single region)
CREATE TABLE eu_customers (
id UUID PRIMARY KEY,
name STRING,
address STRING
) LOCALITY REGIONAL IN "eu-west-1";
```

4. **Replication and Zones:**
```sql
-- Default replication factor
ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3;

-- Pin specific table to region
ALTER TABLE users CONFIGURE ZONE USING
num_replicas = 5,
constraints = '{"+region=us-east": 2, "+region=us-west": 2, "+region=eu-west": 1}';

-- Fast reads with follower reads (slightly stale data acceptable)
SET CLUSTER SETTING kv.closed_timestamp.target_duration = '1s';
```

5. **Performance Tuning:**
```sql
-- Increase SQL memory limit
SET CLUSTER SETTING sql.defaults.distsql.max_running_flows = 500;

-- Enable statement statistics
SET CLUSTER SETTING sql.metrics.statement_details.enabled = true;

-- Adjust range size for large datasets
ALTER RANGE default CONFIGURE ZONE USING range_max_bytes = 134217728; -- 128MB

-- Connection pool settings (application side)
-- Max connections: 4 × num_cores per node
-- Min connections: num_cores per node
```

6. **Backup and Restore:**
```sql
-- Full backup to S3
BACKUP DATABASE mydb TO 's3://bucket/backup?AWS_ACCESS_KEY_ID=xxx&AWS_SECRET_ACCESS_KEY=xxx';

-- Incremental backup
BACKUP DATABASE mydb TO 'latest' IN 's3://bucket/backups';

-- Restore
RESTORE DATABASE mydb FROM 'latest' IN 's3://bucket/backups';

-- Scheduled backups (Enterprise)
CREATE SCHEDULE backup_schedule
FOR BACKUP DATABASE mydb INTO 's3://bucket/backups'
RECURRING '@daily'
WITH SCHEDULE OPTIONS first_run = 'now';
```

7. **Change Data Capture (Enterprise):**
```sql
-- Stream changes to Kafka
CREATE CHANGEFEED FOR TABLE users, orders
INTO 'kafka://broker:9092?topic_prefix=myapp_';

-- Stream to webhook
CREATE CHANGEFEED FOR TABLE events
INTO 'webhook-https://myapp.com/webhook?insecure_tls_skip_verify=true';
```

8. **Monitoring Queries:**
```sql
-- Cluster health
SHOW CLUSTER SETTING version;

-- Node status
SELECT node_id, address, is_live, is_available FROM crdb_internal.gossip_liveness;

-- Active queries
SELECT query_id, user_name, query, start FROM [SHOW QUERIES];

-- Slow queries
SELECT query, count, avg_latency FROM crdb_internal.statement_statistics
ORDER BY avg_latency DESC
LIMIT 10;

-- Range distribution
SELECT range_id, replicas, lease_holder FROM crdb_internal.ranges;
```

**Best Practices:**
- Run at least 3 nodes per region for fault tolerance
- Use load balancers for connection distribution
- Enable follower reads for read-heavy workloads (1-48s staleness acceptable)
- Pin critical tables to specific regions with zone configurations
- Use UUID primary keys instead of sequential integers (better distribution)
- Monitor replication lag in multi-region deployments
- Configure automated backups to external storage
- Use connection pooling (PgBouncer, application-level pools)
- Set appropriate timeouts for long-running queries
- Regular vacuum and statistics updates (automatic in CockroachDB)
- Test failover scenarios regularly
- Keep cluster balanced (avoid hotspots with good key design)