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.
| Table name | Description | Use in production |
|---|---|---|
active\_range\_feeds | Contains information about on nodes in your cluster. | ✗ |
backward\_dependencies | Contains information about backward dependencies. | ✗ |
builtin\_functions | Contains information about supported . | ✗ |
cluster\_contended\_indexes | Contains information about indexes in your cluster. | ✓ |
cluster\_contended\_keys | Contains information about keys in your cluster. | ✓ |
cluster\_contended\_tables | Contains information about tables in your cluster. | ✓ |
cluster\_contention\_events | Contains information about in your cluster. | ✓ |
cluster\_locks | Contains information about held by on specific . | ✓ |
cluster\_database\_privileges | Contains information about the on your cluster. | ✗ |
cluster\_execution\_insights | Contains information about SQL statement executions on your cluster. | ✗ |
cluster\_distsql\_flows | Contains information about the flows of the scheduled in your cluster. | ✗ |
cluster\_inflight\_traces | Contains information about in-flight in your cluster. | ✗ |
cluster\_queries | Contains 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\_sessions | Contains information about cluster sessions, including current and past queries. | ✓ |
cluster\_settings | Contains information about . | ✗ |
cluster\_transactions | Contains information about transactions running on your cluster. | ✓ |
create\_statements | Contains information about tables and indexes in your database. | ✗ |
create\_function\_statements | Contains information about in your database. | ✗ |
create\_type\_statements | Contains information about in your database. | ✗ |
cross\_db\_references | Contains information about objects that reference other objects, such as or , across databases in your cluster. | ✗ |
databases | Contains information about the databases in your cluster. | ✗ |
default\_privileges | Contains information about per-database default . | ✗ |
feature\_usage | Contains information about feature usage on your cluster. | ✗ |
forward\_dependencies | Contains information about forward dependencies. | ✗ |
gossip\_alerts | Contains information about gossip alerts. | ✗ |
gossip\_liveness | Contains information about your cluster’s gossip liveness. | ✗ |
gossip\_network | Contains information about your cluster’s gossip network. | ✗ |
gossip\_nodes | Contains information about nodes in your cluster’s gossip network. | ✗ |
index\_columns | Contains information about columns in your cluster. | ✗ |
index\_usage\_statistics | Contains statistics about the primary and secondary indexes used in statements. | ✓ |
invalid\_objects | Contains information about invalid objects in your cluster. | ✗ |
jobs | Contains information about running on your cluster. | ✗ |
kv\_node\_liveness | Contains information about . | ✗ |
kv\_node\_status | Contains information about node status at the . | ✗ |
kv\_store\_status | Contains information about the key-value store for your cluster. | ✗ |
leases | Contains information about in your cluster. | ✗ |
lost\_descriptors\_with\_data | Contains information about table descriptors that have been deleted but still have data left over in storage. | ✗ |
node\_build\_info | Contains information about nodes in your cluster. | ✗ |
node\_contention\_events | Contains information about contention on the gateway node of your cluster. | ✗ |
node\_execution\_insights | Contains information about SQL statement executions on the gateway node of your cluster. | ✗ |
node\_distsql\_flows | Contains information about the flows of the scheduled on nodes in your cluster. | ✗ |
node\_inflight\_trace\_spans | Contains information about currently in-flight spans in the current node. | ✗ |
node\_metrics | Contains metrics for nodes in your cluster. | ✗ |
node\_queries | Contains 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\_info | Contains runtime information about nodes in your cluster. | ✗ |
node\_sessions | Contains information about sessions to nodes in your cluster. | ✗ |
node\_statement\_statistics | Contains 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\_statistics | Contains transaction statistics for nodes in your cluster. | ✗ |
node\_transactions | Contains information about transactions for nodes in your cluster. | ✗ |
node\_txn\_stats | Contains transaction statistics for nodes in your cluster. | ✗ |
partitions | Contains information about in your cluster. | ✗ |
predefined\_comments | Contains predefined comments about your cluster. | ✗ |
ranges | Contains information about in your cluster. | ✗ |
ranges\_no\_leases | Contains information about in your cluster, without . | ✗ |
regions | Contains information about . | ✗ |
schema\_changes | Contains information about schema changes in your cluster. | ✗ |
session\_trace | Contains session trace information for your cluster. | ✗ |
session\_variables | Contains information about in your cluster. | ✗ |
statement\_statistics | Aggregates 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\_columns | Contains information about table columns in your cluster. | ✗ |
table\_indexes | Contains information about table indexes in your cluster. | ✗ |
table\_row\_statistics | Contains row count statistics for tables in the current database. | ✗ |
tables | Contains information about tables in your cluster. | ✗ |
transaction\_contention\_events | Contains information about historical transaction events. | ✓ |
transaction\_statistics | Aggregates 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(). | ✓ |
zones | Contains 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.
- Unless specified otherwise, queries to
crdb_internalassume the . For example, to return thecrdb_internaltable 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
| Column | Type | Description |
|---|---|---|
database\_name | STRING | The name of the database experiencing . |
schema\_name | STRING | The name of the schema experiencing . |
table\_name | STRING | The name of the table experiencing . |
index\_name | STRING | The name of the index experiencing . |
num\_contention\_events | INT8 | The number of events. |
View all indexes that have experienced contention
cluster_contended_keys
| Column | Type | Description |
|---|---|---|
database\_name | STRING | The name of the database experiencing . |
schema\_name | STRING | The name of the schema experiencing . |
table\_name | STRING | The name of the table experiencing . |
index\_name | STRING | The name of the index experiencing . |
key | BYTES | The key experiencing . |
num\_contention\_events | INT8 | The number of events. |
View all keys that have experienced contention
cluster_contended_tables
| Column | Type | Description |
|---|---|---|
database\_name | STRING | The name of the database experiencing . |
schema\_name | STRING | The name of the schema experiencing . |
table\_name | STRING | The name of the table experiencing . |
num\_contention\_events | INT8 | The number of events. |
View all tables that have experienced contention
cluster_contention_events
| Column | Type | Description |
|---|---|---|
table\_id | INT8 | Unique table identifier. |
index\_id | INT8 | Unique index identifier. |
num\_contention\_events | INT8 | The number of events. |
cumulative\_contention\_time | INTERVAL | The cumulative time that the transaction spends waiting in . |
key | BYTES | The key experiencing . |
txn\_id | UUID | Unique transaction identifier. |
count | INT8 | The 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 to always capture contention events.--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.
For more information, see the following sections.
- Cluster locks columns
- Cluster locks - basic example
- Cluster locks - intermediate example
- Blocked vs. blocking transactions
- Client sessions holding locks
- Count locks held by sessions
- Count queries waiting on locks
Cluster locks columns
Thecrdb_internal.cluster_locks table has the following columns that describe each lock:
| Column | Type | Description |
|---|---|---|
range\_id | The ID of the that stores the key the lock is being acquired on. | |
table\_id | The ID of the that includes the key the lock is being acquired on. | |
database\_name | The name of the that includes the key the lock is being acquired on. | |
schema\_name | The name of the that includes the key this lock is being acquired on. | |
table\_name | The name of the that includes the key this lock is being acquired on. | |
index\_name | The name of the that includes the key this lock is being acquired on. | |
lock\_key | The actual key that this lock is being acquired on. | |
lock\_key\_pretty | A string representation of the key this lock is being acquired on. | |
txn\_id | The ID of the that is acquiring this lock. | |
ts | The at which this lock was acquired. | |
lock\_strength | The strength of this lock. Allowed values: "Exclusive" or "None" (read-only requests don’t need an exclusive lock). | |
durability | Whether the lock is one of: Replicated or Unreplicated. For more information about lock replication, see . | |
granted | Whether this lock has been granted to the requesting it. | |
contended | Whether multiple are trying to acquire a lock on this key. | |
duration | The length of time this lock has been held for. |
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 incluster_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):
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:
- The transaction with the query in Terminal 1 asked for an
Exclusivelock on a row in thedefaultdb.kvtable, as shown in thelock_strengthcolumn. We can see that it was able to get that lock, since thegrantedcolumn istrue. - The transaction in Terminal 2 is also trying to lock the same row in the
kvtable with alock_strengthofExclusive. However, the value of thegrantedcolumn isfalse, 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.
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.
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 thecrdb_internal.cluster_locks table.
-
Initialize the workload:
-
Run it at a high concurrency setting:
-
While the workload is running, issue the following query to view a subset of the locks being requested:
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.
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 thebank 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 thebank 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 thebank 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 thebank 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.
| Column | Type | Description |
|---|---|---|
query\_id | STRING | Unique query identifier. |
txn\_id | UUID | Unique transaction identifier. |
node\_id | INT8 | The ID of the node on which the query is executed. |
session\_id | STRING | Unique session identifier. |
user\_name | STRING | The name of the user that executed the query. |
start | TIMESTAMP | The time that the query started. |
query | STRING | The query string. |
client\_address | STRING | The address of the client that initiated the query. |
application\_name | STRING | The name of the application that initiated the query. |
distributed | BOOLEAN | Whether the query is executing in a distributed cluster. |
phase | STRING | The phase that the query is in. |
View all active queries for the movr application
cluster_sessions
| Column | Type | Description |
|---|---|---|
node\_id | INT8 | The ID of the node the session is connected to. |
session\_id | STRING | The ID of the session. |
user\_name | STRING | The name of the user that initiated the session. |
client\_address | STRING | The address of the client that initiated the session. |
application\_name | STRING | The name of the application that initiated the session. |
active\_queries | STRING | The SQL queries active in the session. |
last\_active\_query | STRING | The most recently completed SQL query in the session. |
session\_start | TIMESTAMP | The timestamp at which the session started. |
active\_query\_start | TIMESTAMP | The timestamp at which the currently active SQL query in the session started. |
kv\_txn | STRING | The ID of the current key-value transaction for the session. |
alloc\_bytes | INT8 | The number of bytes allocated by the session. |
max\_alloc\_bytes | INT8 | The maximum number of bytes allocated by the session. |
View all open SQL sessions for the movr application
cluster_transactions
| Column | Type | Description |
|---|---|---|
id | UUID | The unique ID that identifies the transaction. |
node\_id | INT8 | The ID of the node the transaction is connected to. |
session\_id | STRING | The ID of the session running the transaction. |
start | TIMESTAMP | The time the transaction started. |
txn\_string | STRING | The transaction string. |
application\_name | STRING | The name of the application that ran the transaction. |
num\_stmts | INT8 | The number of statements in the transaction. |
num\_retries | INT8 | The number of times the transaction was retried. |
num\_auto\_retries | INT8 | The 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.
| Column | Type | Description |
|---|---|---|
table\_id | INT8 | Unique table identifier. |
index\_id | INT8 | Unique index identifier. |
total\_reads | INT8 | Number of times an index was selected for a read. |
last\_read | TIMESTAMPTZ | Time of last read. |
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 withtable_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
| Column | Type | Description |
|---|---|---|
aggregated\_ts | TIMESTAMPTZ NOT NULL | The time that statistics aggregation started. |
fingerprint\_id | BYTES NOT NULL | Unique 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\_id | BYTES NOT NULL | Uniquely identifies a transaction statistics. The transaction fingerprint ID that this statement statistic belongs to. |
plan\_hash | BYTES NOT NULL | Uniquely identifies a query plan that was executed by the current statement. The query plan can be retrieved from the sampled\_plan column. |
app\_name | STRING NOT NULL | The name of the application that executed the statement. |
metadata | JSONB NOT NULL | Metadata that describes the statement. See metadata column. |
statistics | JSONB NOT NULL | Statistics for the statement. See statistics column. |
sampled\_plan | JSONB NOT NULL | The sampled query plan of the current statement statistics. This column is unfilled if there is no sampled query plan. |
aggregation\_interval | INTERVAL NOT NULL | The interval over which statistics are aggregated. |
index\_recommendations | STRING[] NOT NULL | An 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:
- Add the escape character
\xat the start of thefingerprint_id:
- Encode the
fingerprint_idashex:
metadata column
| Field | Type | Description |
|---|---|---|
db | STRING | The database on which the statement is executed. |
distsql | BOOLEAN | Whether the statement is being executed by the Distributed SQL (DistSQL) engine. |
fullScan | BOOLEAN | Whether the statement performed a full scan of the table. |
implicitTxn | BOOLEAN | Whether the statement executed in an implicit transaction. |
query | STRING | The statement string. |
querySummary | STRING | The statement string summary. |
stmtTyp | STRING | The type of SQL statement: "TypeDDL", "TypeDML", "TypeDCL", or "TypeTCL". These types map to the CockroachDB statement types , , , and . |
vec | BOOLEAN | Whether 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 .
| Field | Type | Description | |
|---|---|---|---|
execution\_statistics -> cnt | INT64 | The number of times execution statistics were recorded. | |
| `execution_statistics -> contentionTime -> [mean | sqDiff]` | NumericStat | The time (in seconds) the statement spent contending for resources before being executed. |
| `execution_statistics -> cpuSQLNanos -> [mean | sqDiff]` | NumericStat | The 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 -> [mean | sqDiff]` | NumericStat | The 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 -> [mean | sqDiff]` | NumericStat | The maximum memory usage (in bytes) that occurred on a node. |
| `execution_statistics -> networkBytes -> [mean | sqDiff]` | NumericStat | The number of bytes sent over the network. |
| `execution_statistics -> networkMsgs -> [mean | sqDiff]` | NumericStat | The number of messages sent over the network. |
| `statistics -> bytesRead -> [mean | sqDiff]` | NumericStat | The number of bytes read from disk. |
statistics -> cnt | INT8 | The total number of times this statement was executed since the beginning of the aggregation period. | |
statistics -> failureCount | INT | The total number of times the execution of this statement fingerprint failed. | |
statistics -> firstAttemptCnt | INT8 | The 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 -> [mean | sqDiff]` | NumericStat | The 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 -> indexes | Array of String | The list of indexes used by the statement. Each index has the format {tableID}@{indexID}. | |
statistics -> lastErrorCode | String | The PostgreSQL Error Code from the last failed execution of the statement fingerprint. | |
statistics -> lastExecAt | TIMESTAMP | The last timestamp the statement was executed. | |
statistics -> maxRetries | INT8 | The maximum observed number of automatic retries in the aggregation period. | |
statistics -> nodes | Array of INT64 | An ordered list of nodes IDs on which the statement was executed. | |
| `statistics -> numRows -> [mean | sqDiff]` | NumericStat | The number of rows returned or observed. |
| `statistics -> ovhLat -> [mean | sqDiff]` | NumericStat | The difference (in seconds) between svcLat and the sum of parseLat+planLat+runLat latencies. |
| `statistics -> parseLat -> [mean | sqDiff]` | NumericStat | The time (in seconds) to transform the SQL string into an abstract syntax tree (AST). |
statistics -> planGists | String | A sequence of bytes representing the flattened tree of operators and various operator specific metadata of the statement plan. | |
| `statistics -> planLat -> [mean | sqDiff]` | NumericStat | The time (in seconds) to transform the AST into a logical query plan. |
| `statistics -> rowsRead -> [mean | sqDiff]` | NumericStat | The number of rows read from disk. |
| `statistics -> rowsWritten -> [mean | sqDiff]` | NumericStat | The number of rows written to disk. |
| `statistics -> runLat -> [mean | sqDiff]` | NumericStat | The time (in seconds) to run the query and fetch or compute the result rows. |
| `statistics -> svcLat -> [mean | sqDiff]` | NumericStat | The 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 instatistics->'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:
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.
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.
| Column | Type | Description |
|---|---|---|
collection\_ts | TIMESTAMPTZ NOT NULL | The timestamp when the transaction event was collected. |
blocking\_txn\_id | UUID NOT NULL | The ID of the blocking transaction. You can join this column into the cluster\_contention\_events table. |
blocking\_txn\_fingerprint\_id | BYTES NOT NULL | The ID of the blocking transaction fingerprint. To surface historical information about the transactions that caused the , you can join this column into the statement\_statistics and transaction\_statistics tables to surface historical information about the transactions that caused the contention. |
waiting\_txn\_id | UUID NOT NULL | The ID of the waiting transaction. You can join this column into the cluster\_contention\_events table. |
waiting\_txn\_fingerprint\_id | BYTES NOT NULL | The ID of the waiting transaction fingerprint. To surface historical information about the transactions that caused the , you can join this column into the statement\_statistics and transaction\_statistics tables. |
contention\_duration | INTERVAL NOT NULL | The interval of time the waiting transaction spent waiting for the blocking transaction. |
contending\_key | BYTES NOT NULL | The key on which the transactions contended. |
Transaction contention - example
The following example shows how to join thetransaction_contention_events table with transaction_statistics and statement_statistics tables to extract blocking and waiting transaction information.
-
Display contention table removing in-progress transactions.
-
Display counts for each blocking and waiting transaction fingerprint pair.
-
Join to show blocking statements text.
transaction_statistics
| Column | Type | Description |
|---|---|---|
aggregated\_ts | TIMESTAMPTZ | The time that statistics aggregation started. |
fingerprint\_id | BYTES | The ID of the transaction fingerprint. |
app\_name | STRING | The name of the application that executed the transaction. |
metadata | JSONB | Metadata that describes the transaction. See metadata column. |
statistics | JSONB | Statistics for the transaction. See statistics column. |
aggregation\_interval | INTERVAL | The 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: