Skip to main content
The crdb_internal is a that contains information about internal objects, processes, and metrics related to a specific database. crdb_internal tables are read-only.

​
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.
Table nameDescriptionUse in production
active_range_feedsContains information about on nodes in your cluster.✗
backward_dependenciesContains information about backward dependencies.✗
builtin_functionsContains information about supported .✗
cluster_contended_indexesContains information about indexes in your cluster.✓
cluster_contended_keysContains information about keys in your cluster.✓
cluster_contended_tablesContains information about tables in your cluster.✓
cluster_contention_eventsContains information about in your cluster.✓
cluster_locksContains information about held by on specific .✓
cluster_database_privilegesContains information about the on your cluster.✗
cluster_execution_insightsContains information about SQL statement executions on your cluster.✗
cluster_distsql_flowsContains information about the flows of the scheduled in your cluster.✗
cluster_inflight_tracesContains information about in-flight in your cluster.✗
cluster_queriesContains information about queries running on your cluster. Requires admin, VIEWACTIVITY, or VIEWACTIVITYREDACTED. If a user has both VIEWACTIVITY and VIEWACTIVITYREDACTED, the latter takes precedence and sensitive queries are redacted.✓
cluster_sessionsContains information about cluster sessions, including current and past queries.✓
cluster_settingsContains information about .✗
cluster_transactionsContains information about transactions running on your cluster.✓
create_statementsContains information about tables and indexes in your database.✗
create_function_statementsContains information about in your database.✗
create_type_statementsContains information about in your database.✗
cross_db_referencesContains information about objects that reference other objects, such as or , across databases in your cluster.✗
databasesContains information about the databases in your cluster.✗
default_privilegesContains information about per-database default .✗
feature_usageContains information about feature usage on your cluster.✗
forward_dependenciesContains information about forward dependencies.✗
gossip_alertsContains information about gossip alerts.✗
gossip_livenessContains information about your cluster’s gossip liveness.✗
gossip_networkContains information about your cluster’s gossip network.✗
gossip_nodesContains information about nodes in your cluster’s gossip network.✗
index_columnsContains information about columns in your cluster.✗
index_usage_statisticsContains statistics about the primary and secondary indexes used in statements.✓
invalid_objectsContains information about invalid objects in your cluster.✗
jobsContains information about running on your cluster.✗
kv_node_statusContains information about node status at the .✗
kv_store_statusContains information about the key-value store for your cluster.✗
leasesContains information about in your cluster.✗
lost_descriptors_with_dataContains information about table descriptors that have been deleted but still have data left over in storage.✗
node_build_infoContains information about nodes in your cluster.✗
node_contention_eventsContains information about contention on the gateway node of your cluster.✗
node_execution_insightsContains information about SQL statement executions on the gateway node of your cluster.✗
node_distsql_flowsContains information about the flows of the scheduled on nodes in your cluster.✗
node_inflight_trace_spansContains information about currently in-flight spans in the current node.✗
node_metricsContains metrics for nodes in your cluster.✗
node_queriesContains information about queries running on nodes in your cluster. Requires admin, VIEWACTIVITY, or VIEWACTIVITYREDACTED. If a user has both VIEWACTIVITY and VIEWACTIVITYREDACTED, the latter takes precedence and sensitive queries are redacted.✗
node_runtime_infoContains runtime information about nodes in your cluster.✗
node_sessionsContains information about sessions to nodes in your cluster.✗
node_statement_statisticsContains statement statistics for nodes in your cluster. Requires admin, VIEWACTIVITY, or VIEWACTIVITYREDACTED. If a user has both VIEWACTIVITY and VIEWACTIVITYREDACTED, the latter takes precedence and sensitive statistics are redacted.✗
node_transaction_statisticsContains transaction statistics for nodes in your cluster.✗
node_transactionsContains information about transactions for nodes in your cluster.✗
node_txn_statsContains transaction statistics for nodes in your cluster.✗
partitionsContains information about in your cluster.✗
predefined_commentsContains predefined comments about your cluster.✗
rangesContains information about in your cluster.✗
ranges_no_leasesContains information about in your cluster, without .✗
regionsContains information about .✗
schema_changesContains information about schema changes in your cluster.✗
session_traceContains session trace information for your cluster.✗
session_variablesContains information about in your cluster.✗
statement_statisticsAggregates in-memory and persisted from system.statement_statistics within hourly time intervals based on UTC time, rounded down to the nearest hour. To reset the statistics call SELECT crdb_internal.reset_sql_stats().✓
table_columnsContains information about table columns in your cluster.✗
table_indexesContains information about table indexes in your cluster.✗
table_row_statisticsContains row count statistics for tables in the current database.✗
tablesContains information about tables in your cluster.✗
transaction_contention_eventsContains information about historical transaction events.✓
transaction_statisticsAggregates in-memory and persisted from system.transaction_statistics within hourly time intervals based on UTC time, rounded down to the nearest hour. To reset the statistics, call SELECT crdb_internal.reset_sql_stats().✓
zonesContains information about in your cluster.✗

​
List crdb_internal tables

To list the crdb_internal tables for the , use the following 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 .
  • Unless specified otherwise, queries to crdb_internal assume the .
For example, to return the crdb_internal table for the index usage statistics of the database, you can use the following statement:

​
Table schema

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

​
cluster_contended_indexes

ColumnTypeDescription
database_nameSTRINGThe name of the database experiencing .
schema_nameSTRINGThe name of the schema experiencing .
table_nameSTRINGThe name of the table experiencing .
index_nameSTRINGThe name of the index experiencing .
num_contention_eventsINT8The number of events.

​
View all indexes that have experienced contention

​
cluster_contended_keys

ColumnTypeDescription
database_nameSTRINGThe name of the database experiencing .
schema_nameSTRINGThe name of the schema experiencing .
table_nameSTRINGThe name of the table experiencing .
index_nameSTRINGThe name of the index experiencing .
keyBYTESThe key experiencing .
num_contention_eventsINT8The number of events.

​
View all keys that have experienced contention

​
cluster_contended_tables

ColumnTypeDescription
database_nameSTRINGThe name of the database experiencing .
schema_nameSTRINGThe name of the schema experiencing .
table_nameSTRINGThe name of the table experiencing .
num_contention_eventsINT8The number of events.

​
View all tables that have experienced contention

​
cluster_contention_events

ColumnTypeDescription
table_idINT8Unique table identifier.
index_idINT8Unique index identifier.
num_contention_eventsINT8The number of events.
cumulative_contention_timeINTERVALThe cumulative time that the transaction spends waiting in .
keyBYTESThe key experiencing .
txn_idUUIDUnique transaction identifier.
countINT8The number of events.

​
View all contention events

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

To view the and with the most cumulative time under 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 running the at a --concurrency of 256.)

​
cluster_locks

The crdb_internal.cluster_locks schema contains information about held by on specific . 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:
ColumnTypeDescription
range_idThe ID of the that stores the key the lock is being acquired on.
table_idThe ID of the that includes the key the lock is being acquired on.
database_nameThe name of the that includes the key the lock is being acquired on.
schema_nameThe name of the that includes the key this lock is being acquired on.
table_nameThe name of the that includes the key this lock is being acquired on.
index_nameThe name of the that includes the key this lock is being acquired on.
lock_keyThe actual key that this lock is being acquired on.
lock_key_prettyA string representation of the key this lock is being acquired on.
txn_idThe ID of the that is acquiring this lock.
tsThe at which this lock was acquired.
lock_strengthThe strength of this lock. Allowed values: None, Shared, Exclusive, or Intent. For more information, refer to Lock strengths.
durabilityWhether the lock is one of: Replicated or Unreplicated. For more information about lock replication, see .
grantedWhether this lock has been granted to the requesting it.
contendedWhether multiple are trying to acquire a lock on this key.
durationThe length of time this lock has been held for.
You can see the types and default values of columns in this and other tables using .

​
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 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 .
  • Exclusive: Grants exclusive access to a key, blocking all other reads and writes. Exclusive locks are acquired using .
  • Intent: Temporary lock placed on a key during a transaction’s write operation (, ). 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 .

​
Cluster locks - basic example

In this example, we’ll use the 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 . First, connect to the running cluster (call this Terminal 1):
Next, create a table and insert some rows:
Next, we’ll start a and lock the row we want to operate on:
Press Enter twice in the 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 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 . 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 that are holding and waiting on locks for the same . 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 that are holding and waiting on locks for the same . 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 . 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 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.
ColumnTypeDescription
query_idSTRINGUnique query identifier.
txn_idUUIDUnique transaction identifier.
node_idINT8The ID of the node on which the query is executed.
session_idSTRINGUnique session identifier.
user_nameSTRINGThe name of the user that executed the query.
startTIMESTAMPThe time that the query started.
querySTRINGThe query string.
client_addressSTRINGThe address of the client that initiated the query.
application_nameSTRINGThe name of the application that initiated the query.
distributedBOOLEANWhether the query is executing in a distributed cluster.
phaseSTRINGThe phase that the query is in.

​
View all active queries for the movr application

​
cluster_sessions

ColumnTypeDescription
node_idINT8The ID of the node the session is connected to.
session_idSTRINGThe ID of the session.
user_nameSTRINGThe name of the user that initiated the session.
client_addressSTRINGThe address of the client that initiated the session.
application_nameSTRINGThe name of the application that initiated the session.
active_queriesSTRINGThe SQL queries active in the session.
last_active_querySTRINGThe most recently completed SQL query in the session.
session_startTIMESTAMPThe timestamp at which the session started.
active_query_startTIMESTAMPThe timestamp at which the currently active SQL query in the session started.
kv_txnSTRINGThe ID of the current key-value transaction for the session.
alloc_bytesINT8The number of bytes allocated by the session.
max_alloc_bytesINT8The maximum number of bytes allocated by the session.

​
View all open SQL sessions for the movr application

​
cluster_transactions

ColumnTypeDescription
idUUIDThe unique ID that identifies the transaction.
node_idINT8The ID of the node the transaction is connected to.
session_idSTRINGThe ID of the session running the transaction.
startTIMESTAMPThe time the transaction started.
txn_stringSTRINGThe transaction string.
application_nameSTRINGThe name of the application that ran the transaction.
num_stmtsINT8The number of statements in the transaction.
num_retriesINT8The number of times the transaction was retried.
num_auto_retriesINT8The number of times the transaction was automatically retried.

​
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.
ColumnTypeDescription
table_idINT8Unique table identifier.
index_idINT8Unique index identifier.
total_readsINT8Number of times an index was selected for a read.
last_readTIMESTAMPTZTime of last read.
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

ColumnTypeDescription
aggregated_tsTIMESTAMPTZ NOT NULLThe time that statistics aggregation started.
fingerprint_idBYTES NOT NULLUnique identifier of the statement statistics. This is constructed using the statement fingerprint text, and statement metadata (e.g., query type, database name, etc.)
transaction_fingerprint_idBYTES NOT NULLUniquely identifies a transaction statistics. The transaction fingerprint ID that this statement statistic belongs to.
plan_hashBYTES NOT NULLUniquely identifies a query plan that was executed by the current statement. The query plan can be retrieved from the sampled_plan column.
app_nameSTRING NOT NULLThe name of the application that executed the statement.
metadataJSONB NOT NULLMetadata that describes the statement. See metadata column.
statisticsJSONB NOT NULLStatistics for the statement. See statistics column.
sampled_planJSONB NOT NULLThe sampled query plan of the current statement statistics. This column is unfilled if there is no sampled query plan.
aggregation_intervalINTERVAL NOT NULLThe interval over which statistics are aggregated.
index_recommendationsSTRING[] NOT NULLAn array of strings containing of the format {type} : {sql query}.

​
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

FieldTypeDescription
dbSTRINGThe database on which the statement is executed.
distsqlBOOLEANWhether the statement is being executed by the Distributed SQL (DistSQL) engine.
fullScanBOOLEANWhether the statement performed a full scan of the table.
implicitTxnBOOLEANWhether the statement executed in an implicit transaction.
querySTRINGThe statement string.
querySummarySTRINGThe statement string summary.
stmtTypSTRINGThe type of SQL statement: "TypeDDL", "TypeDML", "TypeDCL", or "TypeTCL". These types map to the CockroachDB statement types , , , and .
vecBOOLEANWhether the statement executed in the vectorized query engine.

​
statistics column

The and pages display information from statistics. The statistics column contains a JSONB object with statistics and execution_statistics subobjects. are always populated and are updated each time a new statement of that statement fingerprint is executed. 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 .
FieldTypeDescription
execution_statistics -> cntINT64The number of times execution statistics were recorded.
`execution_statistics -> contentionTime -> [meansqDiff]`NumericStatThe time (in seconds) the statement spent contending for resources before being executed.
`execution_statistics -> cpuSQLNanos -> [meansqDiff]`NumericStatThe amount of CPU time spent executing the statement in nanoseconds. The CPU time represents the time spent and work done within SQL execution operators.

The CPU time includes time spent in the . It does not include time spent in the .
`execution_statistics -> maxDiskUsage -> [meansqDiff]`NumericStatThe maximum temporary disk usage (in bytes) that occurred while executing this statement. This is set in cases where a query had to spill to disk, e.g., when performing a large sort where not all of the tuples fit in memory.
`execution_statistics -> maxMemUsage -> [meansqDiff]`NumericStatThe maximum memory usage (in bytes) that occurred on a node.
`execution_statistics -> networkBytes -> [meansqDiff]`NumericStatThe number of bytes sent over the network.
`execution_statistics -> networkMsgs -> [meansqDiff]`NumericStatThe number of messages sent over the network.
`statistics -> bytesRead -> [meansqDiff]`NumericStatThe number of bytes read from disk.
statistics -> cntINT8The total number of times this statement was executed since the beginning of the aggregation period.
statistics -> failureCountINTThe total number of times the execution of this statement fingerprint failed.
statistics -> firstAttemptCntINT8The total number of times a first attempt was executed (either the one time in explicitly committed statements, or the first time in implicitly committed statements with implicit retries).
`statistics -> idleLat -> [meansqDiff]`NumericStatThe time (in seconds) spent waiting for the client to send the statement while holding the transaction open. A high wait time indicates that you should revisit the entire transaction and .
statistics -> indexesArray of StringThe list of indexes used by the statement. Each index has the format {tableID}@{indexID}.
statistics -> lastErrorCodeStringThe PostgreSQL Error Code from the last failed execution of the statement fingerprint.
statistics -> lastExecAtTIMESTAMPThe last timestamp the statement was executed.
statistics -> maxRetriesINT8The maximum observed number of automatic retries in the aggregation period.
statistics -> nodesArray of INT64An ordered list of nodes IDs on which the statement was executed.
`statistics -> numRows -> [meansqDiff]`NumericStatThe number of rows returned or observed.
`statistics -> ovhLat -> [meansqDiff]`NumericStatThe difference (in seconds) between svcLat and the sum of parseLat+planLat+runLat latencies.
`statistics -> parseLat -> [meansqDiff]`NumericStatThe time (in seconds) to transform the SQL string into an abstract syntax tree (AST).
`statistics -> planGistsStringA sequence of bytes representing the flattened tree of operators and various operator specific metadata of the statement plan.
statistics -> planLat -> [meansqDiff]`NumericStatThe time (in seconds) to transform the AST into a logical query plan.
`statistics -> rowsRead -> [meansqDiff]`NumericStatThe number of rows read from disk.
`statistics -> rowsWritten -> [meansqDiff]`NumericStatThe number of rows written to disk.
`statistics -> runLat -> [meansqDiff]`NumericStatThe time (in seconds) to run the query and fetch or compute the result rows.
`statistics -> svcLat -> [meansqDiff]`NumericStatThe time (in seconds) to service the query, from start of parse to end of execute.

​
View historical statement statistics and the sampled logical plan 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 statistics, and sampled plan for DDL and DML statements for the 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 . 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 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 (or the legacy VIEWACTIVITY or VIEWACTIVITYREDACTED ) 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 . The sql.contention.event_store.duration_threshold 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.
ColumnTypeDescription
collection_tsTIMESTAMPTZ NOT NULLThe timestamp when the transaction event was collected.
blocking_txn_idUUID NOT NULLThe ID of the blocking transaction. You can join this column into the table.
blocking_txn_fingerprint_idBYTES NOT NULLThe ID of the blocking transaction fingerprint. To surface historical information about the transactions that caused the , you can join this column into the and tables.
waiting_txn_idUUID NOT NULLThe ID of the waiting transaction. You can join this column into the table.
waiting_txn_fingerprint_idBYTES NOT NULLThe ID of the waiting transaction fingerprint. To surface historical information about the transactions that caused the , you can join this column into the and tables.
waiting_stmt_idSTRING NOT NULLThe statement id of the transaction that was waiting (unique for each statement execution).
waiting_stmt_fingerprint_idBYTES NOT NULLThe ID of the waiting statement fingerprint. To surface historical information about the statements that caused the , you can join this column into the table.
contention_durationINTERVAL NOT NULLThe interval of time the waiting transaction spent waiting for the blocking transaction.
contending_keyBYTES NOT NULLThe key on which the transactions contended.
contending_pretty_keySTRING NOT NULLThe specific key that was involved in the contention event, in readable format.
database_nameSTRING NOT NULLThe database where the contention occurred.
schema_nameSTRING NOT NULLThe schema where the contention occurred.
table_nameSTRING NOT NULLThe table where the contention occurred.
index_nameSTRING NULLThe index where the contention occurred.
contention_typeSTRING NOT NULLThe type of contention. Possible values:
  • 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 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

ColumnTypeDescription
aggregated_tsTIMESTAMPTZThe time that statistics aggregation started.
fingerprint_idBYTESThe ID of the transaction fingerprint.
app_nameSTRINGThe name of the application that executed the transaction.
metadataJSONBMetadata that describes the transaction. See metadata column.
statisticsJSONBStatistics for the transaction. See statistics column.
aggregation_intervalINTERVALThe interval of time over which statistics are aggregated.

​
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 demo database:

​
See also