CockroachDB Cheat Sheet

cockroach start --insecure --store=node1 --listen-addr=localhost:26257 \  --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259 &cockroach start --insecure --store=node2 --listen-addr=localhost:26258 \  --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259 &cockroach start --insecure --store=node3 --listen-addr=localhost:26259 \  --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259 &cockroach init --insecure --host=localhost:26257cockroach sql --insecure --host=localhost:26257

BEGIN;SAVEPOINT cockroach_restart;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;RELEASE SAVEPOINT cockroach_restart;COMMIT;-- On a 40001 (serialization_failure) error, client should:-- ROLLBACK TO SAVEPOINT cockroach_restart; and retry the statements.-- Most drivers (pgx, node-postgres via cockroach adapters) do this automatically.

ALTER TABLE users ADD COLUMN region STRING NOT NULL DEFAULT 'us-east';ALTER TABLE users PARTITION BY LIST (region) (    PARTITION us (VALUES IN ('us-east', 'us-west')),    PARTITION eu (VALUES IN ('eu-west')));ALTER PARTITION us OF TABLE users  CONFIGURE ZONE USING constraints = '[+region=us-east1]';ALTER PARTITION eu OF TABLE users  CONFIGURE ZONE USING constraints = '[+region=eu-west1]';

CREATE CHANGEFEED FOR TABLE orders, payments  INTO 'kafka://broker:9092'  WITH updated, resolved = '10s', format = 'json';-- Sinkless changefeed streamed directly to the SQL client, useful for debuggingEXPERIMENTAL CHANGEFEED FOR orders WITH updated;SHOW CHANGEFEED JOBS;PAUSE JOB 123456789012345678;RESUME JOB 123456789012345678;

BACKUP DATABASE app INTO 's3://my-bucket/backups?AWS_ACCESS_KEY_ID=...&AWS_SECRET_ACCESS_KEY=...'  AS OF SYSTEM TIME '-10s';-- Subsequent backups append incrementally to the same collectionBACKUP DATABASE app INTO LATEST IN 's3://my-bucket/backups?...';SHOW BACKUPS IN 's3://my-bucket/backups?...';RESTORE DATABASE app FROM LATEST IN 's3://my-bucket/backups?...'  WITH new_db_name = 'app_restored';

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;-- Ranges receiving disproportionate QPS, a common cause of latency spikesSELECT range_id, lease_holder, qpsFROM crdb_internal.rangesORDER BY qps DESCLIMIT 10;-- Bulk-load without the SQL execution engine overheadIMPORT INTO orders (id, customer_id, amount, created_at)  CSV DATA ('s3://my-bucket/orders.csv?AWS_ACCESS_KEY_ID=...');

ALTER DATABASE app SET PRIMARY REGION 'us-east1';ALTER DATABASE app ADD REGION 'us-west1';ALTER DATABASE app ADD REGION 'eu-west1';-- Survive an entire region outage, not just a single nodeALTER DATABASE app SURVIVE REGION FAILURE;-- Pin a table's rows to the region of the requesting clientALTER TABLE users SET LOCALITY REGIONAL BY ROW;