supabase-advanced-troubleshooting

Overview

When basic debugging does not reveal the root cause, you need deep PostgreSQL diagnostics: pg_stat_statements to find the slowest queries by cumulative execution time, pg_locks to detect lock contention and deadlocks, pg_stat_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 pg_stat_statements 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 (SUPABASE_SERVICE_ROLE_KEY) for the diagnostic RPCs below, since they read pg_stat_activity 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 <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: pg_stat_statements and slow query analysis

Enable pg_stat_statements, 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 pg_locks joined to pg_stat_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 pg_terminate_backend cleanup, and a get_connection_health 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 — pg_stat_statements 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

| Error | Cause | Solution | | ------- | ------- | ---------- | | pg_stat_statements not available | Extension not enabled | Run CREATE EXTENSION pg_stat_statements; | | Seq Scan on large table | Missing index on filter column | Create index with CREATE INDEX CONCURRENTLY | | deadlock detected | Circular lock dependency | Ensure consistent lock ordering across transactions | | All connections in idle in transaction | Application not closing transactions | Add connection timeout; review ORM connection pool settings | | RLS returns empty for authenticated user | JWT claims don't match policy | Check auth.jwt() output; verify app_metadata is set | | Edge Function > 2s cold start | Large dependency bundle | Lazy-import heavy modules; reduce function size | | Realtime TIMED_OUT | Network/firewall blocking WebSocket | Check port 443 (HTTPS/WSS) is open outbound; verify no proxy strips the Upgrade header | | CHANNEL_ERROR on subscribe | Table not in Realtime publication | Run ALTER PUBLICATION supabase_realtime ADD TABLE ... |

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

• pg_stat_statements — 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