Profiling Transaction Fingerprints

Profiling Transaction Fingerprints

Analyzes historical transaction performance patterns using aggregated SQL statistics to identify high-retry transactions, contention patterns, and commit latency issues. Uses crdb_internal.transaction_statistics for time-windowed analysis of retry behavior, commit latency, and statement composition - entirely via SQL without requiring DB Console access.

Complement to profiling-statement-fingerprints: This skill analyzes transaction-level patterns (groups of statements with retry behavior); for statement-level optimization, see profiling-statement-fingerprints.

Complement to triaging-live-sql-activity: This skill analyzes historical transaction patterns; for immediate triage of currently active transactions, see triaging-live-sql-activity.

When to Use This Skill

• Identify transactions with high retry counts
• Analyze commit latency trends for transaction fingerprints
• Find transactions with high contention at transaction boundary
• Understand statement composition of problematic transactions
• Investigate transaction retry storms or abort patterns
• SQL-only historical transaction analysis without DB Console access

For immediate incident response: Use triaging-live-sql-activity to triage currently active transactions and cancel runaway work. For statement-level optimization: Use profiling-statement-fingerprints to analyze individual query patterns.

Prerequisites

• SQL connection to CockroachDB cluster
• VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege for cluster-wide visibility
• Same privilege requirements as profiling-statement-fingerprints
• Understanding of transaction performance concepts
• Transaction statistics collection enabled (default): sql.stats.automatic_collection.enabled = true

Check transaction stats collection:

SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;
-- Should return: true

See triaging-live-sql-activity permissions reference for RBAC setup (same privileges).

Core Concepts

Transaction Fingerprints vs Live Transactions

Transaction fingerprint: Normalized transaction pattern grouping statements with parameterized constants.

Key differences:

• Time scope: Historical hourly buckets vs real-time current state
• Granularity: Aggregated retry/commit stats vs individual transaction instances
• Relationship: Transaction = collection of statement fingerprints

Time-Series Bucketing

aggregated_ts: Hourly UTC buckets (e.g., 2026-02-21 14:00:00 = 14:00-14:59 executions) Data retention: Default ~7 days (check sql.stats.persisted_rows.max) Best practice: Always filter by time window: WHERE aggregated_ts > now() - INTERVAL '24 hours'

Aggregated vs Sampled Metrics

Critical: Sampled metrics have cnt field showing sample size. Always check:

WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL

JSON Field Extraction

CockroachDB stores transaction metadata and statistics as JSONB. Use these operators:

Operators:

• ->: Extract JSON object/value (returns JSON)
• ->>: Extract as text (returns text)
• ::TYPE: Cast to specific type
• encode(fingerprint_id, 'hex'): Convert binary fingerprint to hex string

Transaction-specific examples:

encode(fingerprint_id, 'hex') AS txn_fingerprint_id                     -- Hex encoding
(statistics->'statistics'->>'maxRetries')::INT                           -- Max retry count
(statistics->'statistics'->'retryLat'->>'mean')::FLOAT8                  -- Retry latency (seconds)
(statistics->'statistics'->'commitLat'->>'mean')::FLOAT8                 -- Commit latency (seconds)
(statistics->'statistics'->'svcLat'->>'mean')::FLOAT8                    -- Service latency (seconds)
metadata->'stmtFingerprintIDs' AS stmt_fingerprint_ids_json             -- Statement composition

Units:

• Latency fields: seconds (FLOAT8)
• CPU/contention: nanoseconds (divide by 1e9 for seconds)
• Memory/disk: bytes (consider / 1048576 for MB)

See JSON field reference for complete schema.

Statement Composition

metadata.stmtFingerprintIDs: JSONB array mapping transaction to constituent statements

Use case: Understand which statements compose high-retry transactions

Cross-reference workflow: Join transaction_statistics with statement_statistics on fingerprint IDs

Example pattern:

-- Extract statement fingerprint IDs from transaction
metadata -> 'stmtFingerprintIDs' AS stmt_ids

-- Use with jsonb_array_elements_text to expand and join
jsonb_array_elements_text(metadata->'stmtFingerprintIDs') AS stmt_fingerprint_id

Core Diagnostic Queries

Query 1: Top Transactions by Retries and Contention

-- Identify transactions with high retry counts and contention
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds,
  (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
ORDER BY (statistics->'statistics'->>'maxRetries')::INT DESC
LIMIT 20;

Key columns: max_retries shows maximum retry count; mean_retry_lat_seconds shows time spent in retries; mean_contention_seconds shows lock wait time.

Interpretation: High max_retries (>10) indicates transaction conflicts; correlate with contention to identify lock hotspots.

Query 2: Statement Composition Analysis

-- Extract statement fingerprints for high-retry transactions
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  t.metadata->>'app' AS application,
  (t.statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  jsonb_array_length(t.metadata->'stmtFingerprintIDs') AS num_statements,
  t.metadata->'stmtFingerprintIDs' AS stmt_fingerprint_ids,
  t.aggregated_ts
FROM crdb_internal.transaction_statistics t
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND (t.statistics->'statistics'->>'maxRetries')::INT > 10
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
ORDER BY max_retries DESC
LIMIT 20;

Key columns: num_statements shows transaction complexity; stmt_fingerprint_ids contains statement IDs for cross-reference with statement_statistics.

Use case: Understand which statement combinations cause retries; use Query 7 to drill down to specific statements.

Query 3: High Commit Latency Transactions

-- Find transactions with slow commit latency
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'db' AS database,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_seconds,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_seconds,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct_of_service_lat,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.1  -- > 100ms commit latency
ORDER BY (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 DESC
LIMIT 20;

Key columns: mean_commit_lat_seconds shows 2PC commit time; commit_pct_of_service_lat shows what percentage of total latency is commit overhead.

Interpretation: High commit percentage (>20%) suggests distributed transaction overhead, replication delays, or cross-region writes.

Query 4: Retry Rate by Application

-- Analyze retry patterns by application
SELECT
  metadata->>'app' AS application,
  metadata->>'db' AS database,
  COUNT(*) AS transaction_fingerprint_count,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  MAX((statistics->'statistics'->>'maxRetries')::INT) AS overall_max_retries,
  AVG((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8) AS avg_retry_lat_seconds
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
GROUP BY metadata->>'app', metadata->>'db'
ORDER BY avg_max_retries DESC
LIMIT 20;

Use case: Application-level health scorecard; identify which applications have the most problematic transaction patterns.

Customization: Adjust time window to 7 days for trends; filter by specific database.

Query 5: Transaction Resource Consumption

-- Find transactions with high resource usage
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb,
  (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
  (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb,
  ROUND(
    ((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) *
    (statistics->'statistics'->>'cnt')::INT, 2
  ) AS estimated_total_network_mb,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 > 0
ORDER BY estimated_total_network_mb DESC
LIMIT 20;

Key columns: mean_network_mb shows distributed transaction overhead; mean_disk_mb > 0 indicates memory spill.

Interpretation: High network bytes suggest cross-region transactions or inefficient distribution; disk usage indicates memory pressure.

Query 6: Retry Latency Decomposition

-- Understand retry latency as percentage of service latency
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS execution_count,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_seconds,
  ROUND(
    ((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS retry_pct_of_service_lat,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
  AND (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 > 0
ORDER BY retry_pct_of_service_lat DESC
LIMIT 20;

Interpretation: High retry percentage (>30%) means most latency is spent retrying due to contention; optimize transaction boundaries or schema.

Query 7: Cross-Reference Transaction to Statements

-- Join transaction statistics with statement statistics to see constituent statements
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  t.metadata->>'app' AS txn_application,
  (t.statistics->'statistics'->>'maxRetries')::INT AS txn_max_retries,
  stmt_fp_id AS stmt_fingerprint_id,
  s.metadata->>'query' AS statement_query,
  (s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS stmt_mean_run_lat_seconds,
  t.aggregated_ts
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
LEFT JOIN crdb_internal.statement_statistics s
  ON s.fingerprint_id = decode(stmt_fp_id, 'hex')
  AND s.aggregated_ts = t.aggregated_ts
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND (t.statistics->'statistics'->>'maxRetries')::INT > 10
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
ORDER BY txn_max_retries DESC, stmt_mean_run_lat_seconds DESC
LIMIT 50;

Use case: Drill down from high-retry transactions to specific problematic statements; identify which statement in a transaction is causing retries.

Note: Uses decode(stmt_fp_id, 'hex') to convert hex string back to binary for join with statement_statistics.

Common Workflows

Workflow 1: Retry Storm Investigation

1. Identify high-retry transactions: Run Query 1, focus on max_retries > 20 2. Analyze retry patterns by application: Run Query 4 to identify problematic apps 3. Examine statement composition: Run Query 7 to see which statements are in high-retry transactions 4. Cross-reference live activity: If ongoing, use triaging-live-sql-activity to check current transaction state 5. Remediate: Adjust transaction boundaries, batch operations, optimize statements identified in step 3

Workflow 2: Commit Latency Analysis

1. Find slow commit transactions: Run Query 3, focus on commit_pct_of_service_lat > 20% 2. Check for contention correlation: Run Query 1 for same transaction fingerprints to see if contention is related 3. Analyze time patterns: Group Query 3 by aggregated_ts to identify peak periods 4. Resource investigation: Run Query 5 to check if network overhead correlates with commit latency 5. Remediate: Consider batching operations, partitioning tables, or investigating replication configuration

Workflow 3: Statement Composition Drill-Down

1. Identify problematic transactions: Run Query 1 or Query 3 to find high-retry or slow-commit transactions 2. Extract statement IDs: Run Query 2 to see stmtFingerprintIDs for target transactions 3. Join with statement_statistics: Run Query 7 to see full statement details 4. Optimize bottleneck statements: Use profiling-statement-fingerprints skill to analyze and optimize identified statements 5. Validate retry reduction: Re-run Query 1 after optimizations to confirm improved retry counts

Workflow 4: Application Health Scorecard

1. Generate retry metrics by app: Run Query 4 to get application-level retry statistics 2. Correlate with commit latency: Modify Query 3 to group by application 3. Resource attribution: Run Query 5 grouped by application to see resource impact 4. Trend analysis: Run queries with 7-day window and compare hourly buckets 5. Contact application teams: Provide specific transaction fingerprints with high retries or latency for investigation

Safety Considerations

Read-only operations: All queries are SELECT statements against crdb_internal.transaction_statistics, which is production-approved and safe for diagnostic use.

Performance impact:

Privacy: Use VIEWACTIVITYREDACTED to redact query constants in multi-tenant environments (same as statement profiling).

Default time window: 24 hours balances recent data with manageable result sets.

Troubleshooting

Key Considerations

• Time windows: Default to 24h; expand to 7d for trends
• Sampled metrics: Not all executions captured; check sample size (cnt)
• JSON field safety: Use defensive NULL checks; handle type casting errors
• Privacy: Use VIEWACTIVITYREDACTED in production
• Performance: Always include time filters and LIMIT clauses
• Complement to statement profiling: Use together for complete coverage (transaction + statement)
• Complement to live triage: Historical patterns vs real-time (use both)
• Data retention: Default ~7 days; verify with sql.stats.persisted_rows.max
• Retry semantics: maxRetries is maximum across all executions in bucket, not average
• Fingerprint encoding: Use encode(fingerprint_id, 'hex') for human-readable IDs

References

Skill references:

• JSON field schema and extraction
• Metrics catalog and units
• SQL query variations
• RBAC and privileges (shared with triaging-live-sql-activity)

Official CockroachDB Documentation:

• crdb_internal
• Transactions Page (DB Console)
• Monitor and Analyze Transaction Contention
• VIEWACTIVITY privilege

Related skills:

• profiling-statement-fingerprints - For statement-level optimization
• triaging-live-sql-activity - For immediate triage of active transactions

JSON Field Reference

Complete schema documentation for JSONB fields in crdb_internal.transaction_statistics. This reference covers the metadata and statistics columns, which store transaction attributes and performance metrics.

Overview

crdb_internal.transaction_statistics uses JSONB columns for flexible schema evolution. Extract fields using:

• -> operator: Returns JSON type (for nested access)
• ->> operator: Returns text type (for values)
• ::TYPE casting: Convert text to specific types (INT, FLOAT8, BOOL)
• encode(fingerprint_id, 'hex'): Convert binary fingerprint to hex string for readability

Example row structure:

SELECT fingerprint_id, metadata, statistics, aggregated_ts
FROM crdb_internal.transaction_statistics
LIMIT 1;

Fingerprint ID Encoding

fingerprint_id column: Binary format (bytea) by default; convert to hex for human-readable IDs.

Hex encoding pattern:

encode(fingerprint_id, 'hex') AS txn_fingerprint_id

Decoding for joins:

decode('hex_string_value', 'hex')  -- Convert hex back to binary for joins

metadata Column

Transaction attributes and query characteristics (not performance metrics).

stmtFingerprintIDs Structure

Purpose: Maps transaction to constituent statement fingerprints for drill-down analysis.

Data type: JSONB array of hex-encoded fingerprint ID strings

Example value:

[
  "a1b2c3d4e5f6g7h8",
  "i9j0k1l2m3n4o5p6",
  "q7r8s9t0u1v2w3x4"
]

Extraction patterns:

Count statements in transaction:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  jsonb_array_length(metadata->'stmtFingerprintIDs') AS num_statements
FROM crdb_internal.transaction_statistics
WHERE metadata->'stmtFingerprintIDs' IS NOT NULL;

Expand array to rows:

SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  stmt_fp_id AS stmt_fingerprint_id
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
WHERE t.metadata->'stmtFingerprintIDs' IS NOT NULL;

Join with statement_statistics:

-- Cross-reference transaction to statements
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  stmt_fp_id,
  s.metadata->>'query' AS statement_query,
  (s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS stmt_mean_lat
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
LEFT JOIN crdb_internal.statement_statistics s
  ON s.fingerprint_id = decode(stmt_fp_id, 'hex')
  AND s.aggregated_ts = t.aggregated_ts
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL;

Key notes:

• Single-statement transactions may have empty or single-element arrays
• Hex encoding matches between transaction_statistics.stmtFingerprintIDs and statement_statistics.fingerprint_id
• Always match on same aggregated_ts bucket when joining

Implicit vs Explicit Transactions

implicitTxn field: Distinguishes auto-wrapped single statements from multi-statement transactions.

-- Analyze implicit vs explicit transaction patterns
SELECT
  (metadata->>'implicitTxn')::BOOL AS is_implicit,
  COUNT(*) AS fingerprint_count,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  AVG((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS avg_commit_lat
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
GROUP BY is_implicit;

Interpretation:

• implicitTxn = true: Single statement auto-wrapped (e.g., standalone SELECT)
• implicitTxn = false: Multi-statement transaction (e.g., BEGIN; ... COMMIT;)

statistics Column

Nested JSONB object containing two subsections: statistics (aggregated) and execution_statistics (sampled).

statistics.statistics (Aggregated Metrics)

Collected for all executions. No sampling; represents complete dataset for the time bucket.

Latency Object Structure

Each latency field (retryLat, commitLat, svcLat) contains:

Transaction-specific latency fields:

retryLat (Retry Latency):

(statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds

• Definition: Time spent retrying due to transaction conflicts/aborts
• Unit: Seconds
• Interpretation: High retry latency indicates contention; often correlates with high maxRetries

commitLat (Commit Latency):

(statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_seconds

• Definition: Time spent in 2-phase commit protocol (transaction boundary overhead)
• Unit: Seconds
• Interpretation: High commit latency suggests distributed transaction overhead, cross-region writes, or replication delays

svcLat (Service Latency):

(statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_seconds

• Definition: Total end-to-end transaction latency (execution + retries + commit)
• Unit: Seconds
• Formula: Approximately svcLat ≈ execution_time + retryLat + commitLat

Calculate standard deviation:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat,
  sqrt(
    (statistics->'statistics'->'commitLat'->>'sqDiff')::FLOAT8 /
    NULLIF((statistics->'statistics'->>'cnt')::INT, 0)
  ) AS stddev_commit_lat
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours';

Rows Object Structure

numRows field: Statistics about rows affected by transaction.

Example: Average rows affected per transaction

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  (statistics->'statistics'->'numRows'->>'mean')::FLOAT8 AS avg_rows_affected
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
ORDER BY avg_rows_affected DESC
LIMIT 20;

statistics.execution_statistics (Sampled Metrics)

Collected for ~10% of executions. Always check cnt field to verify sample presence.

Defensive filtering pattern:

WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0

Sampled Object Structure

Same as aggregated metrics: each field contains mean and sqDiff subfields.

Example: Contention analysis

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'execution_statistics'->>'cnt')::INT AS sample_size,
  (statistics->'statistics'->>'cnt')::INT AS total_executions,
  ROUND(
    (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9,
    3
  ) AS mean_contention_seconds,
  metadata->>'app' AS application
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0
ORDER BY mean_contention_seconds DESC
LIMIT 20;

Example: Network and memory analysis

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb,
  (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
  (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb,
  CASE
    WHEN (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 > 0
    THEN 'SPILLING'
    ELSE 'in-memory'
  END AS memory_status
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
ORDER BY mean_network_mb DESC
LIMIT 20;

Type Casting Patterns

Safe Casting with NULL Handling

Always use defensive NULL checks and COALESCE for optional fields:

-- Safe integer extraction
COALESCE((statistics->'statistics'->>'maxRetries')::INT, 0)

-- Safe float extraction with validation
CASE
  WHEN (statistics->'execution_statistics'->>'cnt') IS NOT NULL
    AND (statistics->'execution_statistics'->'contentionTime'->>'mean') IS NOT NULL
  THEN (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9
  ELSE NULL
END AS mean_contention_seconds

-- Boolean with default
COALESCE((metadata->>'implicitTxn')::BOOL, false)

Common Type Casting Examples

-- Text extraction (no casting needed)
metadata->>'db'                                    -- Returns: 'mydb'

-- Hex encoding for fingerprint
encode(fingerprint_id, 'hex')                      -- Returns: 'a1b2c3d4e5f6g7h8'

-- Decode hex for joins
decode('a1b2c3d4e5f6g7h8', 'hex')                  -- Returns: binary bytea

-- Integer extraction
(statistics->'statistics'->>'cnt')::INT            -- Returns: 1000
(statistics->'statistics'->>'maxRetries')::INT     -- Returns: 15

-- Float extraction
(statistics->'statistics'->'commitLat'->>'mean')::FLOAT8  -- Returns: 0.125

-- Boolean extraction
(metadata->>'implicitTxn')::BOOL                   -- Returns: true

-- JSONB array access
metadata->'stmtFingerprintIDs'                     -- Returns: ["abc...", "def..."]
jsonb_array_length(metadata->'stmtFingerprintIDs') -- Returns: 3

-- Nested object extraction (chained ->)
statistics->'statistics'->'retryLat'->>'mean'      -- Extract mean from retryLat object

Unit Conversions

-- Nanoseconds to seconds (contention)
(statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9

-- Bytes to megabytes
(statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576

-- Bytes to gigabytes
(statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1073741824

-- Latency already in seconds (no conversion)
(statistics->'statistics'->'commitLat'->>'mean')::FLOAT8

Version Compatibility Notes

Field availability varies by CockroachDB version:

Compatibility check query:

-- Verify field existence before using in production queries
SELECT
  CASE WHEN metadata ? 'stmtFingerprintIDs' THEN 'available' ELSE 'missing' END AS stmt_fp_ids,
  CASE WHEN statistics->'statistics' ? 'retryLat' THEN 'available' ELSE 'missing' END AS retry_lat,
  CASE WHEN statistics->'statistics' ? 'commitLat' THEN 'available' ELSE 'missing' END AS commit_lat
FROM crdb_internal.transaction_statistics
LIMIT 1;

Common Extraction Errors and Fixes

Complete Example: Multi-Field Extraction

SELECT
  -- Fingerprint ID
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,

  -- Metadata fields
  metadata->>'db' AS database,
  metadata->>'app' AS application,
  (metadata->>'implicitTxn')::BOOL AS is_implicit,
  jsonb_array_length(metadata->'stmtFingerprintIDs') AS num_statements,

  -- Aggregated statistics
  (statistics->'statistics'->>'cnt')::INT AS total_executions,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_sec,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_sec,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_sec,
  (statistics->'statistics'->'numRows'->>'mean')::FLOAT8 AS mean_rows_affected,

  -- Sampled execution statistics (defensive)
  CASE
    WHEN (statistics->'execution_statistics'->>'cnt') IS NOT NULL
    THEN (statistics->'execution_statistics'->>'cnt')::INT
    ELSE NULL
  END AS sample_size,

  CASE
    WHEN (statistics->'execution_statistics'->>'cnt') IS NOT NULL
    THEN ROUND((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9, 3)
    ELSE NULL
  END AS mean_contention_sec,

  CASE
    WHEN (statistics->'execution_statistics'->>'cnt') IS NOT NULL
    THEN ROUND((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576, 2)
    ELSE NULL
  END AS mean_network_mb,

  -- Derived metrics
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct_of_service_lat,

  aggregated_ts

FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 5
ORDER BY max_retries DESC
LIMIT 20;

Additional Resources

• Official schema: crdb_internal documentation
• JSONB operators: PostgreSQL JSONB functions (CockroachDB compatible)
• Metrics interpretation: See metrics-and-units.md
• Query examples: See sql-query-variations.md

Metrics and Units Reference

Comprehensive guide to interpreting transaction statistics metrics, units, conversions, and thresholds for performance analysis.

Metric Categories

Transaction statistics are divided into two collection modes:

Critical difference from statement statistics: Transaction metrics focus on transaction boundary behavior (retries, commit latency) rather than individual statement execution.

Transaction-Specific Latency Metrics

All latency metrics are stored in seconds as FLOAT8.

retryLat (Retry Latency)

Definition: Time spent retrying the transaction due to conflicts, serialization failures, or aborts.

Extraction:

(statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds

Thresholds:

Interpretation:

• OLTP workloads: Target < 100ms retry latency
• High retry latency + high maxRetries: Indicates persistent contention on hot rows
• Compare with commitLat: If retryLat > commitLat, retries dominate latency

Example analysis:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_sec,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_sec,
  ROUND(
    ((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS retry_pct_of_service_lat,
  CASE
    WHEN (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 < 0.1 THEN 'low'
    WHEN (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 < 1 THEN 'moderate'
    WHEN (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 < 5 THEN 'high'
    ELSE 'critical'
  END AS retry_latency_class
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 > 0;

commitLat (Commit Latency)

Definition: Time spent in the 2-phase commit protocol at the transaction boundary (distributed transaction coordination overhead).

Extraction:

(statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_seconds

Thresholds:

Interpretation:

• Local transactions: Target < 10ms commit latency
• Cross-region transactions: 50-200ms typical (depends on geography)
• High commit latency causes: Distributed writes, slow replicas, cross-AZ/region latency, replication lag

Commit percentage calculation:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS commit_lat_sec,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS service_lat_sec,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct_of_service_lat,
  metadata->>'app' AS application
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.05
ORDER BY commit_pct_of_service_lat DESC;

Optimization targets:

• Commit % < 10%: Well-optimized transaction
• Commit % 10-20%: Acceptable distributed transaction overhead
• Commit % > 20%: Consider batching, geo-partitioning, or reducing transaction scope

svcLat (Service Latency)

Definition: Total end-to-end transaction latency from start to commit completion.

Formula:

svcLat ≈ execution_time + retryLat + commitLat

Extraction:

(statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_seconds

Use for: Understanding total user-perceived transaction latency; baseline for calculating retry and commit percentages.

Latency decomposition:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS total_svc_lat_sec,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS retry_lat_sec,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS commit_lat_sec,
  (
    (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 -
    COALESCE((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8, 0) -
    COALESCE((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8, 0)
  ) AS estimated_execution_lat_sec,
  ROUND(
    (COALESCE((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8, 0) /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS retry_pct,
  ROUND(
    (COALESCE((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8, 0) /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 > 0.1
ORDER BY total_svc_lat_sec DESC;

Retry Metrics

maxRetries

Definition: Maximum number of automatic retries for any transaction execution within the hourly bucket.

Unit: Count (INT)

Extraction:

(statistics->'statistics'->>'maxRetries')::INT AS max_retries

Thresholds:

Analysis pattern:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  metadata->>'app' AS application,
  metadata->>'db' AS database,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_sec,
  CASE
    WHEN (statistics->'statistics'->>'maxRetries')::INT = 0 THEN 'none'
    WHEN (statistics->'statistics'->>'maxRetries')::INT <= 3 THEN 'low'
    WHEN (statistics->'statistics'->>'maxRetries')::INT <= 10 THEN 'moderate'
    WHEN (statistics->'statistics'->>'maxRetries')::INT <= 50 THEN 'high'
    ELSE 'severe'
  END AS contention_level
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
ORDER BY max_retries DESC;

Common causes:

• UPDATE/DELETE on same hot rows across concurrent transactions
• Serial writes to monotonically increasing primary keys
• Long-running transactions holding locks
• Insufficient batching of write operations

Important: maxRetries is the maximum across all executions, not average. A single problematic execution can skew this value.

Contention Metrics

contentionTime

Definition: Time spent waiting for locks held by other transactions at the transaction level.

Unit: Nanoseconds (convert to seconds: divide by 1e9)

Extraction:

(statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_sec

Sampled: Yes (check for execution_statistics.cnt)

Thresholds:

Calculate contention ratio:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  ROUND(
    ((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9), 3
  ) AS mean_contention_sec,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS service_lat_sec,
  ROUND(
    ((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9) /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0) * 100, 2
  ) AS contention_pct_of_service_lat
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0
ORDER BY contention_pct_of_service_lat DESC;

Transaction vs Statement Contention:

• Transaction-level: Cumulative contention across all statements in transaction
• Statement-level: Contention for individual queries
• Use case: Transaction contention shows total lock wait; drill to statements for specific bottlenecks

Resource Metrics

networkBytes

Definition: Bytes sent over network for distributed SQL coordination between nodes.

Unit: Bytes (convert to MB: divide by 1048576)

Extraction:

(statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb

Sampled: Yes

Thresholds:

High network causes:

• Cross-region distributed transactions
• Large intermediate result sets
• Inefficient query plans with excessive data movement

Example analysis:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  ROUND(
    ((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) *
    (statistics->'statistics'->>'cnt')::INT, 2
  ) AS estimated_total_network_mb,
  metadata->>'app' AS application
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 > 0
ORDER BY estimated_total_network_mb DESC;

maxMemUsage

Definition: Maximum memory allocated during transaction execution.

Unit: Bytes (convert to MB: divide by 1048576)

Extraction:

(statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
(statistics->'execution_statistics'->'maxMemUsage'->>'max')::FLOAT8 / 1048576 AS max_mem_mb

Sampled: Yes

Thresholds:

Default workmem limit: Check sql.distsql.temp_storage.workmem setting (typically 64 MB).

maxDiskUsage

Definition: Maximum disk space used for temporary storage when memory limit exceeded (memory spill).

Unit: Bytes (convert to MB: divide by 1048576)

Extraction:

(statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb

Sampled: Yes

Interpretation:

• > 0: Transaction exceeded workmem and spilled to disk (performance degradation ~100-1000x)
• Large values: Significant I/O overhead; immediate optimization needed

Spill analysis:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
  (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  CASE
    WHEN (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 > 0
    THEN 'SPILLING'
    ELSE 'in-memory'
  END AS memory_status,
  metadata->>'app' AS application
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
ORDER BY mean_disk_mb DESC;

Row Metrics

numRows

Definition: Number of rows affected by the transaction (INSERT/UPDATE/DELETE operations).

Unit: Count (FLOAT8 for mean)

Extraction:

(statistics->'statistics'->'numRows'->>'mean')::FLOAT8 AS mean_rows_affected

Aggregated: Yes (all executions)

Thresholds:

Example:

SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->'numRows'->>'mean')::FLOAT8 AS avg_rows_affected,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_sec,
  metadata->>'app' AS application
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
ORDER BY avg_rows_affected DESC
LIMIT 20;

Derived Metrics and Formulas

Retry Rate Percentage

Formula: Retry latency as percentage of total service latency

ROUND(
  ((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 /
  NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
) AS retry_pct_of_service_lat

Interpretation:

• < 10%: Low retry impact
• 10-30%: Moderate retry overhead
• > 30%: Retries dominate latency; high contention

Commit Latency Percentage

Formula: Commit latency as percentage of total service latency

ROUND(
  ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
  NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
) AS commit_pct_of_service_lat

Interpretation:

• < 10%: Minimal distributed overhead
• 10-20%: Normal distributed transaction cost
• > 20%: High commit overhead; investigate replication or cross-region latency

Standard Deviation (for any metric with sqDiff)

sqrt(
  (statistics->'statistics'->'commitLat'->>'sqDiff')::FLOAT8 /
  NULLIF((statistics->'statistics'->>'cnt')::INT, 0)
) AS stddev_commit_lat

Estimated Total Resource Consumption

For sampled metrics, estimate total cluster impact:

-- Estimated total network MB in time window
ROUND(
  ((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) *
  (statistics->'statistics'->>'cnt')::INT, 2
) AS estimated_total_network_mb

Transaction vs Statement Metric Comparison

When to use transaction vs statement profiling:

• High retries: Use transaction profiling to identify retry storms, then drill to statements
• Slow commits: Transaction-only metric; analyze commit latency trends
• Contention: Transaction shows total; statement shows which query causes locks
• Latency optimization: Start with statements, aggregate understanding via transactions

Unit Conversion Quick Reference

Additional Resources

• JSON schema: json-field-reference.md
• Query examples: sql-query-variations.md
• Main skill: ../SKILL.md
• Official docs: Transactions Page

SQL Query Variations

Extended query library for transaction fingerprint analysis with time window variations, filtering patterns, and transaction-specific analysis techniques.

Time Window Variations

1-Hour Window

Use case: Real-time investigation of recent patterns

-- Recent high-retry transactions (last hour)
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '1 hour'
  AND (statistics->'statistics'->>'maxRetries')::INT > 5
ORDER BY max_retries DESC
LIMIT 20;

6-Hour Window

Use case: Identify patterns during business hours or specific shifts

-- Commit latency analysis (last 6 hours)
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'db' AS database,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_sec,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '6 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.05
ORDER BY mean_commit_lat_sec DESC
LIMIT 20;

24-Hour Window (Default Recommended)

Use case: Standard daily performance analysis

-- Resource consumption analysis (last 24 hours)
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb,
  (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  ROUND(
    ((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) *
    (statistics->'statistics'->>'cnt')::INT, 2
  ) AS estimated_total_network_mb
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
ORDER BY estimated_total_network_mb DESC
LIMIT 20;

7-Day Window (Trend Analysis)

Use case: Weekly trends, performance regression detection

-- Retry trend analysis (last 7 days)
SELECT
  date_trunc('day', aggregated_ts) AS day,
  metadata->>'app' AS application,
  COUNT(DISTINCT encode(fingerprint_id, 'hex')) AS unique_txn_fingerprints,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  MAX((statistics->'statistics'->>'maxRetries')::INT) AS peak_max_retries
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '7 days'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
GROUP BY day, application
ORDER BY day DESC, avg_max_retries DESC;

Filtering Patterns

Filter by Application

-- Transactions from specific application
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_sec,
  aggregated_ts
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND metadata->>'app' = 'payments-api'
ORDER BY max_retries DESC
LIMIT 20;

Filter by Database

-- Transactions in specific database
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND metadata->>'db' = 'production'
  AND (statistics->'statistics'->>'maxRetries')::INT > 10
ORDER BY max_retries DESC
LIMIT 20;

Filter by Retry Threshold

-- Only high-retry transactions (>20 retries)
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  metadata->>'db' AS database,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_sec,
  (statistics->'statistics'->>'cnt')::INT AS executions
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 20
ORDER BY max_retries DESC;

Filter by Commit Latency Threshold

-- Only slow-commit transactions (>100ms)
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_sec,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct,
  (statistics->'statistics'->>'cnt')::INT AS executions
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.1
ORDER BY mean_commit_lat_sec DESC
LIMIT 20;

Filter by Implicit vs Explicit Transactions

-- Compare implicit (single-statement) vs explicit (multi-statement) transactions
SELECT
  (metadata->>'implicitTxn')::BOOL AS is_implicit,
  COUNT(*) AS fingerprint_count,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  AVG((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS avg_commit_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
GROUP BY is_implicit
ORDER BY is_implicit;

Aggregation Queries

Top N by Max Retries

-- Top 10 transactions by max retries
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  metadata->>'db' AS database,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
ORDER BY max_retries DESC
LIMIT 10;

Group by Application

-- Application-level retry metrics
SELECT
  metadata->>'app' AS application,
  COUNT(DISTINCT encode(fingerprint_id, 'hex')) AS unique_txn_fingerprints,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  MAX((statistics->'statistics'->>'maxRetries')::INT) AS peak_max_retries,
  AVG((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8) AS avg_retry_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
GROUP BY application
ORDER BY avg_max_retries DESC;

Group by Database

-- Database-level commit latency analysis
SELECT
  metadata->>'db' AS database,
  COUNT(*) AS transaction_fingerprint_count,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS avg_commit_lat_sec,
  MAX((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS max_commit_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0
GROUP BY database
ORDER BY avg_commit_lat_sec DESC;

Time Bucket Trends

-- Hourly retry trend
SELECT
  aggregated_ts,
  COUNT(*) AS transaction_fingerprint_count,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  AVG((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8) AS avg_retry_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
GROUP BY aggregated_ts
ORDER BY aggregated_ts DESC;

Transaction-to-Statement Join Patterns

CRITICAL UNIQUE SECTION: These patterns are specific to transaction fingerprint analysis and enable drill-down from transactions to constituent statements.

Basic Join: Transaction to Statements

-- Join transaction fingerprints with statement fingerprints
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  t.metadata->>'app' AS txn_app,
  (t.statistics->'statistics'->>'maxRetries')::INT AS txn_max_retries,
  stmt_fp_id AS stmt_fingerprint_id_hex,
  s.metadata->>'query' AS statement_query,
  (s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS stmt_mean_run_lat_sec,
  (s.statistics->'statistics'->>'cnt')::INT AS stmt_executions
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
LEFT JOIN crdb_internal.statement_statistics s
  ON s.fingerprint_id = decode(stmt_fp_id, 'hex')
  AND s.aggregated_ts = t.aggregated_ts
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
ORDER BY txn_max_retries DESC
LIMIT 50;

Key technique:

• jsonb_array_elements_text() expands stmtFingerprintIDs array to rows
• decode(stmt_fp_id, 'hex') converts hex string back to binary for join
• Match on same aggregated_ts bucket

Aggregate Statement Metrics Within Transaction

-- Aggregate statement metrics for each transaction
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  t.metadata->>'app' AS application,
  (t.statistics->'statistics'->>'maxRetries')::INT AS txn_max_retries,
  jsonb_array_length(t.metadata->'stmtFingerprintIDs') AS num_statements,
  AVG((s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8) AS avg_stmt_run_lat_sec,
  MAX((s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8) AS max_stmt_run_lat_sec,
  SUM((s.statistics->'statistics'->>'cnt')::INT) AS total_stmt_executions
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
LEFT JOIN crdb_internal.statement_statistics s
  ON s.fingerprint_id = decode(stmt_fp_id, 'hex')
  AND s.aggregated_ts = t.aggregated_ts
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
  AND (t.statistics->'statistics'->>'maxRetries')::INT > 10
GROUP BY t.fingerprint_id, application, txn_max_retries, num_statements
ORDER BY txn_max_retries DESC
LIMIT 20;

Find All Statements in High-Retry Transactions

-- Identify all statements contributing to high-retry transactions
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  (t.statistics->'statistics'->>'maxRetries')::INT AS txn_max_retries,
  substring(s.metadata->>'query', 1, 150) AS statement_query_preview,
  (s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS stmt_mean_run_lat_sec,
  (s.statistics->'statistics'->>'maxRetries')::INT AS stmt_max_retries,
  (s.statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS stmt_mean_contention_sec
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
LEFT JOIN crdb_internal.statement_statistics s
  ON s.fingerprint_id = decode(stmt_fp_id, 'hex')
  AND s.aggregated_ts = t.aggregated_ts
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND (t.statistics->'statistics'->>'maxRetries')::INT > 20
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
  AND (s.statistics->'execution_statistics'->>'cnt') IS NOT NULL
ORDER BY txn_max_retries DESC, stmt_mean_contention_sec DESC
LIMIT 100;

Statement Composition Complexity Analysis

-- Analyze transaction complexity by statement count and types
SELECT
  encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id,
  t.metadata->>'app' AS application,
  (t.statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  jsonb_array_length(t.metadata->'stmtFingerprintIDs') AS num_statements,
  COUNT(DISTINCT s.metadata->>'stmtType') AS distinct_stmt_types,
  array_agg(DISTINCT s.metadata->>'stmtType') AS stmt_types,
  AVG((s.statistics->'statistics'->'runLat'->>'mean')::FLOAT8) AS avg_stmt_latency
FROM crdb_internal.transaction_statistics t
CROSS JOIN LATERAL jsonb_array_elements_text(t.metadata->'stmtFingerprintIDs') AS stmt_fp_id
LEFT JOIN crdb_internal.statement_statistics s
  ON s.fingerprint_id = decode(stmt_fp_id, 'hex')
  AND s.aggregated_ts = t.aggregated_ts
WHERE t.aggregated_ts > now() - INTERVAL '24 hours'
  AND t.metadata->'stmtFingerprintIDs' IS NOT NULL
GROUP BY t.fingerprint_id, application, max_retries, num_statements
HAVING jsonb_array_length(t.metadata->'stmtFingerprintIDs') > 5
ORDER BY max_retries DESC
LIMIT 20;

Retry Analysis Patterns

Retry Rate by Application

-- Application retry scorecard
SELECT
  metadata->>'app' AS application,
  COUNT(*) AS transaction_fingerprints,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  MAX((statistics->'statistics'->>'maxRetries')::INT) AS peak_max_retries,
  AVG((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8) AS avg_retry_lat_sec,
  AVG(
    ROUND(
      ((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 /
      NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
    )
  ) AS avg_retry_pct_of_service_lat
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
GROUP BY application
ORDER BY avg_max_retries DESC;

Retry Latency Decomposition

-- Understand retry impact on total latency
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->>'maxRetries')::INT AS max_retries,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS total_service_lat_sec,
  (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS retry_lat_sec,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS commit_lat_sec,
  (
    (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 -
    COALESCE((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8, 0) -
    COALESCE((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8, 0)
  ) AS estimated_execution_lat_sec,
  ROUND(
    (COALESCE((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8, 0) /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS retry_pct,
  ROUND(
    (COALESCE((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8, 0) /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 10
ORDER BY retry_pct DESC
LIMIT 20;

Retry Trend Over Time

-- Hourly retry pattern
SELECT
  aggregated_ts,
  metadata->>'app' AS application,
  COUNT(*) AS transaction_fingerprints,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  MAX((statistics->'statistics'->>'maxRetries')::INT) AS peak_max_retries
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->>'maxRetries')::INT > 0
GROUP BY aggregated_ts, application
ORDER BY aggregated_ts DESC, avg_max_retries DESC;

Retry Storm Detection

-- Detect sudden retry spikes (compare to previous period)
WITH current_period AS (
  SELECT
    metadata->>'app' AS application,
    AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_retries
  FROM crdb_internal.transaction_statistics
  WHERE aggregated_ts > now() - INTERVAL '1 hour'
  GROUP BY application
),
previous_period AS (
  SELECT
    metadata->>'app' AS application,
    AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_retries
  FROM crdb_internal.transaction_statistics
  WHERE aggregated_ts > now() - INTERVAL '2 hours'
    AND aggregated_ts <= now() - INTERVAL '1 hour'
  GROUP BY application
)
SELECT
  c.application,
  c.avg_retries AS current_avg_retries,
  p.avg_retries AS previous_avg_retries,
  ROUND(
    ((c.avg_retries - p.avg_retries) / NULLIF(p.avg_retries, 0)) * 100, 2
  ) AS retry_increase_pct
FROM current_period c
LEFT JOIN previous_period p ON c.application = p.application
WHERE c.avg_retries > p.avg_retries * 2  -- 2x increase threshold
ORDER BY retry_increase_pct DESC;

Commit Latency Analysis

Commit Latency Percentiles (Approximation)

-- High commit latency transactions with standard deviation
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_sec,
  sqrt(
    (statistics->'statistics'->'commitLat'->>'sqDiff')::FLOAT8 /
    NULLIF((statistics->'statistics'->>'cnt')::INT, 0)
  ) AS stddev_commit_lat_sec,
  (statistics->'statistics'->>'cnt')::INT AS executions,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.05
ORDER BY mean_commit_lat_sec DESC
LIMIT 20;

Commit vs Service Latency Ratio

-- Transactions with high commit overhead
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  metadata->>'db' AS database,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS commit_lat_sec,
  (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS service_lat_sec,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct_of_service_lat
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0
HAVING commit_pct_of_service_lat > 20
ORDER BY commit_pct_of_service_lat DESC
LIMIT 20;

Time-of-Day Commit Latency Pattern

-- Commit latency by hour of day
SELECT
  EXTRACT(HOUR FROM aggregated_ts) AS hour_of_day,
  COUNT(*) AS transaction_fingerprints,
  AVG((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS avg_commit_lat_sec,
  MAX((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS max_commit_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '7 days'
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0
GROUP BY hour_of_day
ORDER BY hour_of_day;

Advanced Analysis

Transaction Complexity (Statement Count)

-- Analyze transaction complexity by statement count
SELECT
  jsonb_array_length(metadata->'stmtFingerprintIDs') AS num_statements,
  COUNT(*) AS transaction_fingerprints,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  AVG((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS avg_commit_lat_sec,
  AVG((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8) AS avg_service_lat_sec
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND metadata->'stmtFingerprintIDs' IS NOT NULL
GROUP BY num_statements
ORDER BY num_statements DESC;

Resource Attribution by Application

-- Application-level resource consumption scorecard
SELECT
  metadata->>'app' AS application,
  COUNT(*) AS transaction_fingerprints,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) AS avg_network_mb,
  AVG((statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576) AS avg_mem_mb,
  SUM(
    ROUND(
      ((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) *
      (statistics->'statistics'->>'cnt')::INT, 2
    )
  ) AS total_network_mb
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
GROUP BY application
ORDER BY total_network_mb DESC;

Cross-Region Transaction Detection

-- Identify likely cross-region transactions (high network + commit latency)
SELECT
  encode(fingerprint_id, 'hex') AS txn_fingerprint_id,
  metadata->>'app' AS application,
  (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb,
  (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_sec,
  ROUND(
    ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
    NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
  ) AS commit_pct
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
  AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
  AND (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 > 10
  AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.05
ORDER BY mean_network_mb DESC
LIMIT 20;

Transaction Health Scorecard

-- Comprehensive transaction health metrics by application
SELECT
  metadata->>'app' AS application,
  COUNT(*) AS transaction_fingerprints,
  SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions,
  AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries,
  AVG((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8) AS avg_retry_lat_sec,
  AVG((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8) AS avg_commit_lat_sec,
  AVG((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8) AS avg_service_lat_sec,
  AVG(
    ROUND(
      ((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 /
      NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
    )
  ) AS avg_retry_pct,
  AVG(
    ROUND(
      ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 /
      NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2
    )
  ) AS avg_commit_pct
FROM crdb_internal.transaction_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
GROUP BY application
ORDER BY avg_max_retries DESC;

Additional Resources

• JSON schema: json-field-reference.md
• Metrics interpretation: metrics-and-units.md
• Main skill: ../SKILL.md
• Official docs: Transactions Page