Supabase Advanced Troubleshooting

Overview

When basic debugging does not reveal the root cause, you need deep PostgreSQL diagnostics: pgstatstatements to find the slowest queries by cumulative execution time, pglocks to detect lock contention and deadlocks, pgstat_activity to find connection leaks, RLS policy conflict analysis to diagnose silent data filtering, Edge Function cold start profiling, and Realtime channel drop investigation. This skill covers every advanced diagnostic technique with real SQL queries and createClient from @supabase/supabase-js.

When to use: Slow query investigation, lock contention causing timeouts, connection pool exhaustion from leaks, RLS policies that silently filter or conflict, Edge Functions with unpredictable latency, or Realtime subscriptions that disconnect intermittently.

Prerequisites

• Supabase project with pgstatstatements extension enabled
• Direct database access via SQL Editor or psql
• @supabase/supabase-js v2+ installed in your project
• Supabase CLI for Edge Function logs
• Familiarity with PostgreSQL system catalogs

Authentication

Every technique here runs against your own project's Postgres, so authenticate with

project-scoped credentials, never a shared key:

• psql / SQL Editor — connect with the project's direct connection string or

pooled Supavisor URI from Dashboard → Settings → Database. Keep the database

password in an env var (PGPASSWORD / .pgpass), never inline in a command.

• SDK (createClient) — pass the service-role key (SUPABASESERVICEROLE_KEY)

for the diagnostic RPCs below, since they read pgstatactivity and system

catalogs that the anon key cannot. Load it from the environment; never commit it.

• supabase CLI — run supabase login once (stores a personal access token),

then supabase link --project-ref for Edge Function logs.

Instructions

Work the three diagnostic tracks in the order the symptom points to. Each track's

full query set and SDK helper lives in a reference file — SKILL.md carries the

skeleton so you can start immediately, then drill in for the complete toolkit.

Step 1: pgstatstatements and slow query analysis

Enable pgstatstatements, then rank queries by total execution time, call

frequency, and cache-hit ratio to find the real cost centers. Follow up with

EXPLAIN (ANALYZE, BUFFERS) on the worst offenders and add targeted indexes with

CREATE INDEX CONCURRENTLY. The starter query:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid, calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS avg_ms,
  left(query, 150) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The full toolkit — frequency and cache-hit-ratio rankings, the EXPLAIN ANALYZE

reading guide, and a timedQuery SDK wrapper that flags any call over 500 ms — is

in slow query analysis.

Step 2: lock debugging and connection leak detection

Find blocked/blocking query pairs via pglocks joined to pgstat_activity,

then hunt connection leaks — especially idle in transaction backends, which hold

locks and exhaust the pool. The starter query surfaces who is blocked and who holds

the lock:

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
  bl.mode AS lock_mode, left(blocked.query, 80) AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.relation = bl.relation AND kl.granted AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE blocked.state = 'active';

The full toolkit — deadlock detection, idle/idle-in-transaction leak queries with

pgterminatebackend cleanup, and a getconnectionhealth RPC with utilization

alerts — is in lock and connection diagnostics.

Step 3: RLS conflicts, Edge Function cold starts, and Realtime drops

Diagnose silent RLS data filtering, profile Edge Function cold-vs-warm latency, and

trace Realtime channel drops. The full walkthrough — RLS policy conflict analysis

(SQL and SDK), cold start profiling, channel-state monitoring, and publication

configuration — is in RLS, Edge Functions, and Realtime.

Output

This skill produces the following diagnostic artifacts:

• Slow query identification — pgstatstatements queries ranking by total time, frequency, and cache hit ratio
• EXPLAIN ANALYZE proficiency — reading execution plans and creating targeted indexes
• Lock contention diagnosis — blocked/blocking query pairs with lock modes
• Connection leak detection — idle and idle-in-transaction connections with kill commands
• Connection pool monitoring — SDK-based health check RPC with utilization alerts
• RLS conflict analysis — policy listing with permissive/restrictive classification and multi-level comparison
• Edge Function profiling — cold start vs warm invocation measurement
• Realtime debugging — channel state monitoring with system event logging

Error Handling

Examples

Example 1 — Quick performance audit:

-- Run this query to get a snapshot of database health
SELECT
  'Connections' AS metric,
  count(*)::text AS value
FROM pg_stat_activity WHERE datname = current_database()
UNION ALL
SELECT 'Cache hit ratio',
  round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0), 2)::text || '%'
FROM pg_statio_user_tables
UNION ALL
SELECT 'Table bloat (dead tuples)',
  sum(n_dead_tup)::text
FROM pg_stat_user_tables
UNION ALL
SELECT 'Longest running query',
  coalesce(max(age(now(), query_start))::text, 'none')
FROM pg_stat_activity WHERE state = 'active' AND query NOT LIKE '%pg_stat%';

Example 2 — Build a diagnostic bundle for support:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(url, serviceRoleKey, {
  auth: { autoRefreshToken: false, persistSession: false },
});

async function buildDiagnosticBundle() {
  const bundle: Record<string, any> = {
    timestamp: new Date().toISOString(),
    projectRef: process.env.SUPABASE_PROJECT_REF,
  };

  // Connection stats
  const { data: connHealth } = await supabase.rpc('get_connection_health');
  bundle.connections = connHealth;

  // Table sizes
  const { data: tableSizes } = await supabase.rpc('get_table_sizes');
  bundle.tableSizes = tableSizes;

  // Recent errors from application logs
  const { data: recentErrors } = await supabase
    .from('error_logs')
    .select('message, count, last_seen')
    .order('last_seen', { ascending: false })
    .limit(10);
  bundle.recentErrors = recentErrors;

  console.log(JSON.stringify(bundle, null, 2));
  // Submit with your support ticket at https://supabase.com/dashboard/support
}

Example 3 — Automated slow query alert:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(url, serviceRoleKey, {
  auth: { autoRefreshToken: false, persistSession: false },
});

async function checkSlowQueries(thresholdMs = 1000) {
  const { data: slowQueries } = await supabase.rpc('get_slow_queries', {
    threshold_ms: thresholdMs,
  });

  if (slowQueries && slowQueries.length > 0) {
    console.warn(`Found ${slowQueries.length} queries averaging > ${thresholdMs}ms`);
    for (const q of slowQueries) {
      console.warn(`  [${q.avg_ms}ms avg, ${q.calls} calls] ${q.query_preview}`);
    }
  }
}

// Database function:
// CREATE OR REPLACE FUNCTION get_slow_queries(threshold_ms numeric DEFAULT 1000)
// RETURNS TABLE(queryid bigint, avg_ms numeric, calls bigint, query_preview text) AS $$
//   SELECT queryid, round(mean_exec_time::numeric, 2), calls, left(query, 150)
//   FROM pg_stat_statements
//   WHERE mean_exec_time > threshold_ms AND calls > 10
//   ORDER BY mean_exec_time DESC LIMIT 10;
// $$ LANGUAGE sql SECURITY DEFINER;

Resources

• pgstatstatements — PostgreSQL Docs
• EXPLAIN ANALYZE — PostgreSQL Docs
• Supabase Performance Advisor
• RLS Debugging — Supabase Docs
• Edge Functions Logging — Supabase Docs
• Realtime Debugging — Supabase Docs
• pg_locks — PostgreSQL Docs
• Connection Pooling with Supavisor

Next Steps

• For load testing and scaling patterns, see supabase-load-scale
• For incident response procedures, see supabase-incident-runbook
• For performance tuning and index optimization, see supabase-performance-tuning
• For common error patterns and quick fixes, see supabase-common-errors