Skip to main content
The crdb_internal system catalog is a schema that contains information about internal objects, processes, and metrics related to a specific database. crdb_internal tables are read-only. The crdb_internal schema is intended for advanced support scenarios only, and should be accessed under the guidance of Cockroach Labs.

​
Access control

CockroachDB treats most objects in the crdb_internal schema, as well as tables and built-in functions in the system database, as unsafe internals. Access to these objects is controlled by the allow_unsafe_internals session variable. This variable defaults to off, restricting access to the system and crdb_internal namespaces. Queries to these namespaces will fail unless access is manually enabled. With allow_unsafe_internals set to off, you should access only information_schema and pg_catalog tables.
If you need information not available through production-supported information_schema or pg_catalog tables, contact your account team or contact Cockroach Labs support.
When allow_unsafe_internals is set to off, external sessions can still read allowlisted crdb_internal objects that are supported for production use (those marked ✓ in the table below). To access all other tables and built-in functions in crdb_internal and system, you must explicitly enable allow_unsafe_internals for the session.
Some SHOW commands, such as SHOW DATABASES, and CockroachDB tools, such as the DB Console and cockroach debug zip, rely on internal queries that access restricted data. These commands and tools are designed to bypass the allow_unsafe_internals setting and continue to function even when direct access is disabled. CockroachDB emits log events to the SENSITIVE_ACCESS channel when a user overrides or is denied access to unsafe internals, creating an audit record of access to system internals. Monitor these logs to ensure that neither workloads nor you and your users are unintentionally accessing unsafe internals.

​
Tables

Do not use the crdb_internal tables marked with ✗ in production environments for the following reasons:
  • The contents of these tables are unstable, and subject to change in new releases of CockroachDB, without prior notice.
  • There are memory and latency costs associated with each table in crdb_internal. Accessing the tables in the schema can impact cluster stability and performance.
To view the schema and query examples for a table supported in production, click the table name.

​
List crdb_internal tables

To list the crdb_internal tables for the current database, use the following SHOW TABLES statement:

​
Query crdb_internal tables

To get detailed information about objects, processes, or metrics related to your database, you can read from the crdb_internal table that corresponds to the item of interest.
  • To ensure that you can view all of the tables in crdb_internal, query the tables as a user with the admin role.
  • Unless specified otherwise, queries to crdb_internal assume the current database.
For example, to return the crdb_internal table for the index usage statistics of the movr database, you can use the following statement:

​
Table schema

This section provides the schema and examples for tables supported in production.

​
cluster_contended_indexes

​
View all indexes that have experienced contention

​
cluster_contended_keys

​
View all keys that have experienced contention

​
cluster_contended_tables

​
View all tables that have experienced contention

​
cluster_contention_events

​
View all contention events

​
View the tables/indexes with the most time under contention

To view the tables and indexes with the most cumulative time under contention since the last server restart, run the query below. The default tracing behavior captures a small percent of transactions, so not all contention events will be recorded. When investigating transaction contention, you can set the sql.trace.txn.enable_threshold cluster setting to always capture contention events.
(The output above is for a local cluster running the TPC-C workload at a --concurrency of 256.)

​
cluster_locks

The crdb_internal.cluster_locks schema contains information about locks held by transactions on specific keys. Queries acquire locks on keys within transactions, or they wait until they can acquire locks until other transactions have released locks on those keys.
Querying the crdb_internal.cluster_locks table triggers an RPC fan-out to all nodes in the cluster, which can make it a relatively expensive operation.
For more information, see the following sections.

​
Cluster locks columns

The crdb_internal.cluster_locks table has the following columns that describe each lock:
You can see the types and default values of columns in this and other tables using SHOW COLUMNS FROM {table}.

​
Lock strengths

Lock strengths in CockroachDB define how transactions interact with data, balancing concurrency and consistency to prevent conflicts and ensure isolation. The lock_strength column can have one of the following values:
  • None: Used for standard reads that do not acquire locks. A simple SELECT * WHERE id = x corresponds to lock strength None.
  • Shared: Allows multiple transactions to read the same key simultaneously but prevents exclusive locks or writes. Shared locks are acquired through SELECT * WHERE id = x FOR SHARE.
  • Exclusive: Grants exclusive access to a key, blocking all other reads and writes. Exclusive locks are acquired using SELECT * WHERE id = x FOR UPDATE.
  • Intent: Temporary lock placed on a key during a transaction’s write operation (INSERT, UPDATE). It conflicts with shared locks, exclusive locks, and non-locking readers at higher timestamps.
For a detailed explanation of how CockroachDB handles transaction locks, refer to Writes and reads (phase 1).

​
Cluster locks - basic example

In this example, we’ll use the SELECT FOR UPDATE statement to order two transactions by controlling concurrent access to a table. Then, we will look at the data in cluster_locks to see the locks being held by these transactions on the objects they are accessing. This example assumes you are running a local unsecured cluster. First, connect to the running cluster (call this Terminal 1):
Next, create a table and insert some rows:
Next, we’ll start a transaction and lock the row we want to operate on:
Press Enter twice in the SQL client to send the statements to be evaluated. This will result in the following output:
Now open another terminal and connect to the database from a second client (call this Terminal 2):
From Terminal 2, start a transaction and try to lock the same row for updates that is already being accessed by the transaction we opened in Terminal 1:
Press Enter twice to send the statements to be evaluated. Because Terminal 1 has already locked this row, the SELECT FOR UPDATE statement from Terminal 2 will appear to “wait”. Now that we have two transactions both trying to update the kv table, let’s query the data in crdb_internal.cluster_locks. We should see two locks:
As expected, there are two locks. This is the case because:
  • The transaction with the SELECT FOR UPDATE query in Terminal 1 asked for an Exclusive lock on a row in the defaultdb.kv table, as shown in the lock_strength column. We can see that it was able to get that lock, since the granted column is true.
  • The transaction in Terminal 2 is also trying to lock the same row in the kv table with a lock_strength of Exclusive. However, the value of the granted column is false, which means it could not get the exclusive lock yet, and is waiting on the lock from the query in Terminal 1 to be released before it can proceed.
Further, both transactions show the contended column as true, since these transactions are both trying to update rows in the defaultdb.kv table at the same time. The following more complex query shows additional information about lockholders, sessions, and waiting queries. This may be useful on a busy cluster for figuring out which transactions from which clients are trying to grab locks. Note that joining with cluster_queries will only show queries currently in progress.
The output is similar to querying cluster_locks alone, except you can see the text of the SQL queries whose transactions are waiting on other transactions to finish, with additional information about the clients that initiated those transactions.
Locks are held by transactions, not queries. A lock can be acquired by a transaction as a result of a query, but CockroachDB does not track which query in a transaction caused that transaction to acquire a lock.

​
Cluster locks - intermediate example

This example assumes you have a cluster in the state it was left in by the previous example. In this example you will run a workload on the cluster with multiple concurrent transactions using the bank workload. With a sufficiently high concurrency setting, the bank workload will frequently attempt to update multiple accounts at the same time. This will create plenty of locks to view in the crdb_internal.cluster_locks table.
  1. Initialize the workload:
  2. Run it at a high concurrency setting:
  3. While the workload is running, issue the following query to view a subset of the locks being requested:
As in the basic example, you can see that some transactions that wanted locks on the bank table are having to wait (granted is false), usually because they are trying to operate on the same rows as one or more other transactions (contended is true). The following more complex query shows additional information about lockholders, sessions, and waiting queries. This may be useful on a busy cluster for figuring out which transactions from which clients are trying to grab locks. Note that joining with cluster_queries will only show queries currently in progress.
The output is similar to querying cluster_locks alone, except you can see the text of the SQL queries whose transactions are waiting on other transactions to finish, with additional information about the clients that initiated those transactions. Locks are held by transactions, not queries. A lock can be acquired by a transaction as a result of a query within that transaction, but CockroachDB does not track which query in a transaction caused that transaction to acquire a lock.

​
Blocked vs. blocking transactions

Run the query below to display a list of pairs of transactions that are holding and waiting on locks for the same keys. This example assumes you are running the bank workload as described in the intermediate example.

​
Client sessions holding locks

Run the query below to display a list of sessions that are holding and waiting on locks for the same keys. This example assumes you are running the bank workload as described in the intermediate example.

​
Count locks held by sessions

Run the query below to show a list of lock counts being held by different sessions. This example assumes you are running the bank workload as described in the intermediate example.

​
Count queries waiting on locks

Run the query below to show a list of keys ordered by how many transactions are waiting on the locks on those keys. This example assumes you are running the bank workload as described in the intermediate example.

​
cluster_queries

Requires admin, VIEWACTIVITY, or VIEWACTIVITYREDACTED. If a user has both VIEWACTIVITY and VIEWACTIVITYREDACTED, the latter takes precedence and sensitive queries are redacted.

​
View all active queries for the movr application

​
cluster_sessions

​
View all open SQL sessions for the movr application

​
cluster_transactions

​
View all active transactions for the movr application

​
index_usage_statistics

Contains one row for each index in the current database surfacing usage statistics for that specific index. This view is updated every time a transaction is committed. Each user-submitted statement on the specified index is counted as a use of that index and increments corresponding counters in this view. System and internal queries (such as scans for gathering statistics) are not counted. You can reset the index usages statistics by invoking the function crdb_internal.reset_index_usage_stats(). For example:

​
View index statistics by table and index name

To view index usage statistics by table and index name, join with table_indexes:

​
Determine which indexes haven’t been used in the past week

To determine if there are indexes that have become stale and are no longer needed, show which indexes haven’t been used during the past week with the following query:

​
Determine which indexes are no longer used

View which indexes are no longer used with the following query:

​
statement_statistics

​
fingerprint_id column

The value is in hexadecimal format. The following examples show how to use this value to query statement_statistics:
  1. Add the escape character \x at the start of the fingerprint_id:
  1. Encode the fingerprint_id as hex:

​
metadata column

​
statistics column

The DB Console Statements and Statement Fingerprint pages display information from statistics. The statistics column contains a JSONB object with statistics and execution_statistics subobjects. statistics are always populated and are updated each time a new statement of that statement fingerprint is executed. execution_statistics are collected using sampling. CockroachDB probabilistically runs a query with tracing enabled to collect fine-grained statistics of the query execution. The NumericStat type tracks two running values: the running mean mean and the running sum of squared differences sqDiff from the mean. You can use these statistics along with the total number of values to compute the variance using Welford’s method. CockroachDB computes the variance and displays it along with mean in the Statements table.

​
View historical statement statistics per fingerprint

This example command shows how to query the two most important JSON columns: metadata and statistics. It displays the first 60 characters of query text, statement type, and statement statistics for DDL and DML statements for the movr demo database:

​
Detect suboptimal and regressed plans

Historical plans are stored in plan gists in statistics->'statistics'->'planGists'. To detect suboptimal and regressed plans over time you can compare plans for the same query by extracting them from the plan gists. Suppose you wanted to compare plans of the following query:
To decode plan gists, use the crdb_internal.decode_plan_gist function, as shown in the following query. The example shows the performance impact of adding an index on the start_time column in the rides table. The first row of the output shows the improved performance (reduced number of rows read and latency) after the index was added. The second row shows the query, which performs a full scan on the rides table, before the index was added.

​
transaction_contention_events

Contains one row for each transaction contention event.
Querying the crdb_internal.transaction_contention_events table triggers an expensive RPC fan-out to all nodes, making it a resource-intensive operation. Avoid frequent polling and do not use this table for continuous monitoring.
Requires either the VIEWACTIVITY or VIEWACTIVITYREDACTED system privilege (or the legacy VIEWACTIVITY or VIEWACTIVITYREDACTED role option) to access. If you have the VIEWACTIVITYREDACTED privilege, contending_key will be redacted. If you have both VIEWACTIVITY and VIEWACTIVITYREDACTED, the latter takes precedence and contending_key will be redacted. Contention events are stored in memory. You can control the amount of contention events stored per node via the sql.contention.event_store.capacity cluster setting. The sql.contention.event_store.duration_threshold cluster setting specifies the minimum contention duration to cause the contention events to be collected into the crdb_internal.transaction_contention_events table. The default value is 0. If contention event collection is overwhelming the CPU or memory you can raise this value to reduce the load. You can also monitor transaction contention using logs for a durable history of contention.
  • LOCK_WAIT: Indicates that the transaction waited on a specific key. The record includes the key and the wait duration.
  • SERIALIZATION_CONFLICT: Represents a serialization conflict specific to a transaction execution. This is recorded only when a client-side retry error containing the conflicting transaction details is emitted.
After recording, the contention_type is not modified. A transaction may have multiple LOCK_WAIT events, as they correspond to specific keys, but only one SERIALIZATION_CONFLICT event.

​
Transaction contention - example

The following example shows how to join the transaction_contention_events table with transaction_statistics and statement_statistics tables to extract blocking and waiting transaction information.
  1. Display contention table removing in-progress transactions.
  2. Display counts for each blocking and waiting transaction fingerprint pair.
  3. Join to show blocking statements text.

​
transaction_statistics

​
View historical transaction statistics per fingerprint

This example command shows how to query the two most important JSON columns: metadata and statistics. It displays the statistics for transactions on the movr demo database:

​
See also