This post contains a collection of CockroachDB commands I have learned and used.

I will continue updating this content as I discover new commands.

Sources of these commands are numerous and below are a few.

CockroachDB documentation and blog and Youtube channel
Special shoutout for Rob Reid. I have learnt a lot from his videos
My on the job learning
Cockroach University great source of free, rich content

To start CockroachDB

cockroach start-single-node --insecure --listen-addr=localhost:26257 --http-addr=localhost:8080
cockroach start-single-node --insecure --listen-addr localhost
cockroach start-single-node --insecure --listen-addr=localhost:26257 --http-addr=localhost:8080 --background

To force stop CockroachDB

pkill -9 cockroach -On Linux/Mac
TASKKILL /F /IM cockroach.exe -On Windows

Node level commands

To get details of node

SELECT * FROM (SELECT node_id AS id,
address,
sql_address,
build_tag AS build,
started_at,
updated_at,
locality,
CASE WHEN split_part(expiration,',',1)::decimal > now()::decimal
THEN true
ELSE false
END AS is_available,
ifnull(is_live, false)

FROM crdb_internal.gossip_liveness LEFT JOIN crdb_internal.gossip_nodes USING (node_id) WHERE membership = 'active' OR split_part(expiration,',',1)::decimal > now()::decimal) ORDER BY id;

Cluster level commands

To see the short cuts/help about the commands that can be issued from command line

To see the regions of Cluster:

show regions from cluster;

To see the locality:
show locality;

To see the regions of Database:
show regions from database <database_name>;

To change the Primary region of Database:

alter database <database_name> primary region "<region_name>";

To get value of Cluster setting parameter:

select * from [show cluster settings] where variable like '<parameter name>';

E.g. select * from [show cluster settings] where variable like '%changefeed.fast%';

To change value of Cluster setting parameter:
set cluster setting <parameter_name> = <parameter_value>;
E.g set cluster setting changefeed.fast_gzip.enabled=true;

If a default value of a parameter is changed, we can notice that value of "origin" column in the output has changed from "default" to "override"

How to generate a SQL for a command?

cockroach node status --echo-sql --insecure

cockroach node status --echo-sql

Database level commands

To list Databases

select * from pg_catalog.pg_databases order by oid;

To list Schemas

select * from pg_catalog.pg_namespace;

To list Survival of Database

show survival goal from databsae <database_name>;

To change Survival of Database; default is "zone"
alter database <database_name> survive region failure;

To list Roles

show roles;

select * from pg_roles where rolsuper is true;

Currently connected users

show sessions;

select distinct user_name from [show sessions];

with x as (show cluster sessions) select * from x where user_name='<username>';

Table level commands

To list tables

show tables;

show tables from <database_name>;

select * from pg_class;

To list/describe columns in table

show columns from <table_name>;

To view/get DDL of table

SHOW CREATE <TABLE_NAME>;

To alter locality of table

alter table <TABLE_NAME> set locality <locality_name>;

We can set locality to "global" if we want this table to have Fast reads Globally

We can set locality to "regional by row" if we want this table to have both Fast reads and writes. In the background, it will create a hidden column, which will store origin region from where the Record has come and then data will be stored in that respective region and reads will also be served from that region.

To view ranges/shards table

SHOW RANGES FROM TABLE table_name WITH DETAILS

To get the number of Leaseholders

SELECT count(DISTINCT lease_holder) FROM [SHOW RANGES FROM TABLE public.<TABLE_NAME> WITH DETAILS];

To get size of the table

SHOW RANGES FROM TABLE table_name WITH DETAILS

SELECT sum(RANGE_SIZE_MB) FROM [SHOW RANGES FROM TABLE table_name WITH DETAILS]

To map from range IDs to schema objects

WITH movr_tables AS (SHOW RANGES FROM DATABASE dbname WITH TABLES),

movr_indexes AS (SHOW RANGES FROM DATABASE dbname WITH INDEXES)

SELECT array_agg(movr_indexes.range_id) AS ranges,

movr_tables.table_name,

movr_indexes.index_name

FROM movr_tables, movr_indexes

WHERE movr_tables.range_id = movr_indexes.range_id

GROUP BY movr_tables.table_name, movr_indexes.index_name

ORDER BY table_name, index_name;

To get lease holder distribution details of the table

select lease_holder, count(*) from [SHOW RANGES FROM TABLE table_name WITH DETAILS]

group by lease_holder;

To get the table ID when table name is there

SELECT <tablename>::regclass::oid;

To get the table name when table ID is there

SELECT oid, relname FROM pg_class WHERE oid = '<table_id>';

To list TTL enabled tables

SELECT relname, reloptions FROM pg_class WHERE reloptions is NOT NULL;

To look at the statistics for the table

SHOW STATISTICS FOR TABLE <table_name>;

To Collect database statistics on the table, run

ANALYZE <table_name>; and then run "SHOW STATISTICS" command again to verify

To alter table's Garbage collection(gc)

ALTER TABLE <table_name> CONFIGURE ZONE USING gc.ttlseconds = x; here x is the number of seconds

Index level commands

Index types in CRDB (this list is just beginning and will be updated )

Primary Index
Unique Index
Secondary index
Partial indexes - Creating query basing on the predicate in where clause. Index size will be less and queries will be relatively faster as the number of records scanned will be lesser
Hash-sharded indexes - there are to eliminate hot spots during writes
Generalized Inverted indexes (GIN indexes)
Forward indexes
Inverted indexes

To list indexes on table

SHOW INDEX FROM <table_name>

To list unused indexes

SELECT

ti.descriptor_name as table_name,

ti.index_name,

total_reads,

last_read

FROM

crdb_internal.index_usage_statistics AS ius

JOIN

crdb_internal.table_indexes AS ti

ON ius.index_id = ti.index_id

AND ius.table_id = ti.descriptor_id

ORDER BY total_reads ASC;

Drop index recommendation would be provide when the index has not been used for over 7 days defined by cluster setting sql.index_recommendation.drop_unused_duration

To get the Index size and number of rows included in that Index

select range_id, round(range_size_mb) as range_size_mb,

span_stats -> 'key_count' AS row_count

from [SHOW RANGES FROM INDEX index_name WITH DETAILS];

Constraints commands

To list Constraints available in database

select * from information_schema.table_constraints;

To list tables with Foreign Key Constraints

select distinct table_name from information_schema.table_constraints where constraint_type='FOREIGN KEY';

Security commands

To list roles granted for specific user

show grants on role for <username>;

To list roles and it's grantees

show grants on role;

To list members of a specific role

show grants on role <rolename>;

Querying pg_*

To list Databases

select * from pg_catalog.pg_databases order by oid;

To list Schemas
select * from pg_catalog.pg_namespace;

To list tables
select * from pg_class;

To list available timezones:
select * from pg_timezone_names;

Commands related to Performance

To list queries with latency

select count::float*service_lat_avg, key as sqlstmt from crdb_internal.node_statement_statistics order by 1 desc limit 10

select key as sqlstmt, count AS executions, service_lat_avg AS avg_time FROM crdb_internal.node_statement_statistics WHERE count > 3600 AND application_name = 'app_name' ORDER by 2 DESC;

select crdb_internal.node_id(); - to know the node id to which we are connected
select current_timestamp; - to know the current timestamp
select current_time; (or) select now(); - to know the current time
select current_date; - to know the current date
select current_date - 1; - to know yesterday's date

init means initiate and it creates a table with appropriate schema

cockroach workload init movr

Databases (Db2 LUW, Teradata), Operating Systems(Linux, UNIX) and Others

Pages

CockroachDB Commands