Deep Supabase diagnostics: pg_stat_statements for slow queries, lock debugging
with pg_locks, connection leak detection, RLS policy conflicts, Edge Function
cold starts, and Realtime connection drop analysis.
Use when standard troubleshooting fails, when investigating performance
regressions, when debugging race conditions, or when building evidence for a
Supabase support escalation.
Trigger with "supabase deep debug", "supabase slow query", "supabase lock
contention", "supabase connection leak", "supabase RLS conflict", "supabase
cold start".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Deep Supabase diagnostics: pg_stat_statements for slow queries, lock debugging
with pg_locks, connection leak detection, RLS policy conflicts, Edge Function
cold starts, and Realtime connection drop analysis.
Use when standard troubleshooting fails, when investigating performance
regressions, when debugging race conditions, or when building evidence for a
Supabase support escalation.
Trigger with "supabase deep debug", "supabase slow query", "supabase lock
contention", "supabase connection leak", "supabase RLS conflict", "supabase
cold start".
Designed for Claude Code, also compatible with Codex and OpenClaw
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 NOTEXISTS 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
ORDERBY 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 ANDNOT 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
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 healthSELECT'Connections'AS metric,
count(*)::text ASvalueFROM pg_stat_activity WHERE datname = current_database()
UNIONALLSELECT'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
UNIONALLSELECT'Table bloat (dead tuples)',
sum(n_dead_tup)::text
FROM pg_stat_user_tables
UNIONALLSELECT'Longest running query',
coalesce(max(age(now(), query_start))::text, 'none')
FROM pg_stat_activity WHERE state ='active'AND query NOTLIKE'%pg_stat%';
Example 2 โ Build a diagnostic bundle for support:
