PostgreSQL vs CockroachDB

Exposes transactions running beyond safety thresholds that block table vacuuming and identifies severe table dead-tuple accumulation.

# 1. Audit active transactions running longer than 60 seconds blocking cleanup
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  pid, 
  now() - xact_start AS duration, 
  state, 
  query 
FROM pg_stat_activity 
WHERE state != 'idle' AND (now() - xact_start) > interval '60 seconds' 
ORDER BY duration DESC;"

# 2. Inspect buffer cache hit ratio and table dead tuple ratios
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  relname, 
  n_live_tup, 
  n_dead_tup, 
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct 
FROM pg_stat_user_tables 
WHERE n_dead_tup > 5000 
ORDER BY dead_pct DESC 
LIMIT 6;"

Identifies queries encountering high retry counts and verifies if any range replicas are under-replicated or unavailable across the cluster.

# 1. Inspect statement execution statistics with highest retry and contention time
cockroach sql --url "$COCKROACH_URL" --execute="
SELECT 
  query, 
  count AS exec_count, 
  retry_count AS retries, 
  ROUND(mean_exec_latency, 2) AS latency_ms 
FROM crdb_internal.statement_statistics 
WHERE retry_count > 0 
ORDER BY retry_count DESC 
LIMIT 5;"

# 2. Audit under-replicated ranges and unavailable ranges across cluster nodes
cockroach sql --url "$COCKROACH_URL" --execute="
SELECT 
  node_id, 
  ranges, 
  ranges_unavailable, 
  ranges_underreplicated 
FROM crdb_internal.kv_node_status;"