Performance Tuning

-- Inefficient: Multiple single-row inserts
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');
INSERT INTO users (id, name) VALUES (3, 'Charlie');

-- Efficient: Single multi-row insert
INSERT INTO users (id, name) VALUES 
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

-- Tables with no secondary indexes: Use UPSERT
UPSERT INTO users (id, name, email) 
VALUES (1, 'Alice', '[email protected]');

-- Tables with secondary indexes: Use INSERT ON CONFLICT
INSERT INTO users (id, name, email) 
VALUES (1, 'Alice', '[email protected]')
ON CONFLICT (id) DO UPDATE SET 
  name = excluded.name,
  email = excluded.email;

-- Batch inserts in groups (experiment with batch size)
INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 100, 50.00),
  (2, 101, 75.50),
  (3, 102, 125.00)
  -- ... up to 100-1000 rows per batch
;

-- Alternative: Use IMPORT INTO for CSV data
IMPORT INTO orders (id, customer_id, amount)
  CSV DATA ('s3://bucket/orders.csv')
  WITH delimiter = ',';

-- Use TRUNCATE to delete all rows (fastest)
TRUNCATE orders;

-- For partial deletes, use batch deletions
DELETE FROM orders 
WHERE order_date < '2023-01-01' 
LIMIT 1000;
-- Repeat until no more rows to delete

-- Use Row-Level TTL for automatic deletion of expired data
ALTER TABLE sessions ADD COLUMN crdb_internal_expiration TIMESTAMPTZ;
ALTER TABLE sessions SET (
  ttl_expiration_expression = 'crdb_internal_expiration',
  ttl_job_cron = '@daily'
);

CREATE TABLE posts (
  username STRING,
  post_timestamp TIMESTAMP,
  post_id INT,
  post_content STRING,
  PRIMARY KEY (username, post_timestamp)
);

SELECT * FROM posts
WHERE username = 'alice'
ORDER BY post_timestamp DESC
LIMIT 10;

-- Use gen_random_uuid() for UUID primary keys
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id INT,
  amount DECIMAL
);

-- Use hash-sharded indexes for sequential IDs
CREATE TABLE events (
  id INT PRIMARY KEY USING HASH,
  event_type STRING,
  created_at TIMESTAMP
);

-- Avoid: Creates hotspot on single range
CREATE TABLE bad_example (
  id SERIAL PRIMARY KEY,  -- or INT DEFAULT unique_rowid()
  data STRING
);

-- Better: Use UUID or composite key
CREATE TABLE better_example (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data STRING
);

-- Query that needs optimization
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 AND status = 'pending';

-- Create covering index
CREATE INDEX idx_customer_status ON orders (customer_id, status);

-- Even better: Include all selected columns
CREATE INDEX idx_customer_status_covering ON orders (customer_id, status) 
STORING (order_date, amount);

-- Inefficient: Retrieves all columns
SELECT * FROM orders WHERE customer_id = 100;

-- Efficient: Retrieves only needed columns
SELECT order_id, order_date, amount 
FROM orders 
WHERE customer_id = 100;

CREATE TABLE users (
  id UUID PRIMARY KEY,
  username STRING,
  email STRING,
  -- Hot: Updated frequently
  login_count INT,
  last_login TIMESTAMP,
  -- Cold: Rarely updated  
  bio TEXT,
  avatar_url STRING,
  FAMILY hot (id, username, email, login_count, last_login),
  FAMILY cold (bio, avatar_url)
);

-- Basic query plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

-- Detailed execution statistics
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

-- With options for more detail
EXPLAIN (VERBOSE) SELECT * FROM orders WHERE customer_id = 100;

-- Historical read that won't conflict with writes
SELECT COUNT(*), AVG(amount) 
FROM orders
AS OF SYSTEM TIME '-10s';

-- Run reports without impacting production
SELECT customer_id, SUM(amount) as total
FROM orders
AS OF SYSTEM TIME '-1m'
GROUP BY customer_id;

# Application connection pool configuration
max_connections = (num_cpu_cores * 4)
min_connections = (num_cpu_cores)

# Recommended memory settings on node startup
cockroach start \
  --cache=25% \          # RocksDB cache (default 25%)
  --max-sql-memory=25% \ # SQL query memory (default 25%)
  ...

-- Monitor SQL memory usage
SHOW CLUSTER SETTING sql.mem.max_sql_memory;

-- Monitor active query memory
SELECT 
  query,
  txn_id,
  start_time,
  mem_usage
FROM crdb_internal.node_queries
WHERE mem_usage > 1073741824  -- 1 GB
ORDER BY mem_usage DESC;

-- Check LSM health
SELECT 
  store_id,
  l0_sublevels,
  l0_num_files
FROM crdb_internal.kv_store_status
WHERE l0_sublevels > 20;  -- Warning threshold

-- Check read amplification
SHOW CLUSTER SETTING storage.read_amplification;

-- View current replication settings
SHOW ZONE CONFIGURATION FOR RANGE default;

-- Modify replication factor
ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3;

-- Database-specific replication
ALTER DATABASE mydb CONFIGURE ZONE USING num_replicas = 5;

-- Pin data to specific regions
ALTER TABLE users PARTITION BY LIST (region) (
  PARTITION us_west VALUES IN ('us-west'),
  PARTITION us_east VALUES IN ('us-east'),
  PARTITION europe VALUES IN ('europe')
);

-- Configure partition placement
ALTER PARTITION us_west OF TABLE users 
  CONFIGURE ZONE USING constraints = '[+region=us-west]';

ALTER PARTITION us_east OF TABLE users 
  CONFIGURE ZONE USING constraints = '[+region=us-east]';

-- Check slow queries
SELECT query, count, avg_latency, p99_latency
FROM crdb_internal.statement_statistics
WHERE avg_latency > interval '100ms'
ORDER BY avg_latency DESC
LIMIT 10;

-- Check contention
SELECT * FROM crdb_internal.cluster_contention_events
ORDER BY contention_duration DESC
LIMIT 10;

-- Check for range hotspots
SELECT 
  range_id,
  writes_per_second,
  lease_holder
FROM crdb_internal.ranges
WHERE writes_per_second > 1000
ORDER BY writes_per_second DESC;

# Check system logs for OOM
sudo dmesg | grep -i "out of memory"

# Check CockroachDB memory usage
grep "memory budget" /var/log/cockroach/cockroach.log