Supabase Performance Monitoring - When Your Database Betrays You

Supabase's dashboard lies. Not maliciously, but it shows averages while your users experience the worst-case scenarios that get buried in the stats.

Why the Built-in Dashboard Fails You

Supabase's monitoring showed "197 active connections" during our Product Hunt launch meltdown. Looked manageable, right? Wrong. Those 197 connections were the ones PostgreSQL could see, but pgbouncer was queueing another 400+ requests from our Next.js API routes.

Users tweeted screenshots of timeout errors while my dashboard glowed green. Took 3 hours to realize the connection count excluded serverless functions that grabbed connections and never released them. Our signup endpoint was leaking one connection per failed attempt.

Dashboard averages are criminal. You see 180ms response times while 15% of users wait 12+ seconds for pages to load. Twitter fills with "is this site down?" while your graphs show healthy performance.

The Metrics That Actually Matter

Cache hit rate dropped to 87% during our busiest hour. Page loads jumped from 200ms to 6 seconds. Users abandoned shopping carts because checkout took forever to respond.

Connection count hit 184/200 and stayed there. API started throwing ECONNREFUSED within minutes. Our mobile app crashed trying to sync user data because every request timed out. Dashboard still said "healthy" while support emails poured in asking if we were down.

pg_stat_statements Saves Your Life

pg_stat_statements comes enabled, thank god. Without it, you're debugging performance blind.

Last Tuesday our API started timing out randomly. Spent hours checking server logs, Redis cache, CDN settings. Finally ran the pg_stat_statements query and found one UPDATE statement taking 45 seconds. Someone forgot to add an index on user_id in our notifications table. 50,000 rows, full table scan on every notification update.

-- Find the queries that are killing your app
SELECT 
  query,
  calls,
  total_exec_time + total_plan_time as total_time,
  mean_exec_time + mean_plan_time as avg_time,
  max_exec_time + max_plan_time as max_time
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

This query reveals which queries are burning the most cumulative time, not just the "slow" ones. That innocent-looking query that runs 1000 times per minute with 100ms average? It's killing performance more than the one big report that takes 10 seconds but runs once per hour.

CLI Commands That Don't Lie

The Supabase CLI inspection commands tell you the truth when the dashboard won't:

## Cache hit rates - mine was 73% during the incident
supabase inspect db cache-hit

## Found 8 unused indexes eating 200MB and slowing writes
supabase inspect db unused-indexes

## Shows which tables need indexes (hint: most of them)
supabase inspect db seq-scans

## Current locks preventing queries from completing
supabase inspect db blocking

Run these during quiet hours, not during a production fire. Found out the hard way that unused-indexes revealed 8 indexes I created "just in case" that were killing INSERT performance. Deleted them and write speed doubled overnight.

Connection Pool Nightmares

Our app handled 50 concurrent users fine. Hit 200 users and everything died with ECONNREFUSED errors. Each Next.js API route grabbed its own connection and held it until the serverless function timed out.

-- Check if you're about to run out of connections
SELECT 
  state,
  count(*) as connections
FROM pg_stat_activity 
WHERE state IS NOT NULL
GROUP BY state;

-- Find connections hogging resources
SELECT 
  datname,
  usename,
  state,
  query_start,
  now() - query_start as duration,
  query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY query_start;

Free tier gives you 200 connections. Sounds like plenty until you launch. Product Hunt traffic hit us with 400 concurrent requests, each serverless function grabbed a connection, and we maxed out in 3 minutes.

?pgbouncer=true in your connection string stops this disaster. One parameter change saved our launch day.

Supavisor is their newer pooler but pgbouncer has worked fine for two years. If it ain't broke, don't fix it.

When Your Database Hits the Disk

Cache hit rate at 89% sounds pretty good until you realize it means 11% of your queries are reading from disk instead of memory. Disk reads are 100x slower than RAM hits.

-- Check if you're reading from disk too much
SELECT 
  'index hit rate' as metric,
  (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) * 100 as percentage
FROM pg_statio_user_indexes
UNION ALL
SELECT 
  'table hit rate' as metric,
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100 as percentage
FROM pg_statio_user_tables;

Our cache hit rate dropped to 83% during Black Friday traffic. Page loads went from 250ms to 8 seconds. Users abandoned their carts because product pages took forever to load. Had to upgrade from the 1GB instance to 4GB just to fit our product catalog in memory.

Below 90% means your database is thrashing between memory and disk on every query. Users notice immediately.

Setting Up Alerts That Don't Suck

Supabase's built-in alerts are garbage. First notification I got was "Database offline" after users had been complaining for 20 minutes.

What to Monitor Before Things Break

• Connection count >160 - found this out when we hit 198/200 and crashed
• Cache hit rate <90% for 5+ minutes - performance tanks fast after this
• Any query running >30 seconds - usually means a missing index
• Disk usage >85% - gives you time to upgrade before hitting the wall

App-Level Monitoring That Predicts Disasters

• API response time >3 seconds - users start abandoning after 3 seconds
• Error rate >0.5% - normal apps see <0.1%, anything higher needs investigation
• Connection pool >85% - gives you a few minutes before the crash

// Example monitoring setup with structured logging
export const logPerformanceMetrics = async (req: Request, startTime: number) => {
  const duration = Date.now() - startTime;
  
  console.log(JSON.stringify({
    event: 'api_request',
    path: req.url,
    method: req.method,
    duration_ms: duration,
    timestamp: new Date().toISOString(),
    // Add custom metrics that matter to your app
    user_id: req.headers.get('user-id'),
    query_count: req.headers.get('x-query-count')
  }));
  
  // Alert on slow requests
  if (duration > 5000) {
    console.error(JSON.stringify({
      event: 'slow_request_alert',
      path: req.url,
      duration_ms: duration,
      threshold_exceeded: '5000ms'
    }));
  }
};

What Tools Actually Help When Everything's Broken

Supabase Dashboard: Pretty graphs, useless for debugging. Shows you problems after users already left.

PostgreSQL Views: pg_stat_statements saved me countless hours. Learn it or suffer.

Supabase CLI: Run the inspect commands weekly during quiet hours. Found more issues with 5 minutes of CLI than hours of dashboard staring.

Sentry: Sentry works for errors but their $26/month quickly becomes $280 when your traffic spikes. Ask me how I know.

Grafana: Grafana is for people who love configuring dashboards more than fixing actual problems. Spent 2 weeks making pretty charts while users complained about slow pages.

Monitor connection exhaustion, slow queries, and cache misses. The rest is just pretty graphs that don't help when you're debugging at 3am on a Saturday.

PostgreSQL's monitoring docs contain everything you need and cure insomnia.

Database is slow. Users are complaining. Your monitoring shows problems but you don't know where to start fixing them.

The Three Things That Always Break

Missing indexes killed our launch day. Spent 8 hours debugging connection exhaustion only to find our users table didn't have an index on email. 47,000 users, full table scan on every login attempt.

Connection pool hit 198/200 within minutes of Product Hunt launch. Every API call grabbed a connection that serverless functions never released. ECONNREFUSED errors everywhere.

Finding the Missing Indexes That Matter

index_advisor extension crashed three times before I gave up. When it did work, it suggested indexing our created_at timestamps which made writes 40% slower for minimal query benefit.

Manual approach that actually works:

-- This might work or crash with \"extension not found\" 
CREATE EXTENSION IF NOT EXISTS index_advisor;

-- Get index suggestions (when it works)
SELECT * FROM index_advisor('
  SELECT users.name, posts.title 
  FROM users 
  JOIN posts ON users.id = posts.user_id 
  WHERE users.created_at > $1 
  ORDER BY posts.created_at DESC 
  LIMIT 10
');

When index_advisor fails (which happens frequently), use this manual approach to identify tables with expensive sequential scans:

-- Find tables doing expensive full scans
SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  seq_tup_read / seq_scan as avg_rows_per_scan
FROM pg_stat_user_tables 
WHERE seq_scan > 100  -- Tables scanned frequently
ORDER BY seq_tup_read DESC;

Our notifications table showed 47,000 avg_rows_per_scan. Every notification update scanned the entire table because we forgot to index user_id. Added CREATE INDEX ON notifications(user_id) and update times dropped from 12 seconds to 80ms.

The Indexes I Wish I'd Created Sooner

Composite indexes - Put the wrong column first and wasted 3 hours debugging slow queries:

-- For queries with user_id AND status filters  
CREATE INDEX CONCURRENTLY idx_posts_user_status 
  ON posts(user_id, status);

-- Most selective column first or you're wasting space
-- This works for user_id queries, but not status-only
CREATE INDEX CONCURRENTLY idx_orders_user_status_date 
  ON orders(user_id, status, created_at);

Partial indexes to save space and improve performance:

-- Only index active users - why waste space on deleted accounts?
CREATE INDEX CONCURRENTLY idx_users_active_email 
  ON users(email) 
  WHERE status = 'active';

-- Index recent data only - old orders rarely queried
CREATE INDEX CONCURRENTLY idx_orders_recent 
  ON orders(created_at, status) 
  WHERE created_at > NOW() - INTERVAL '30 days';

Partial indexes can be 10x smaller and faster than full table indexes. Most queries are probably on recent or active data anyway.

BRIN indexes for timestamp columns (if you have tons of data):

-- Tiny index for huge time-series tables
CREATE INDEX CONCURRENTLY idx_events_created_brin 
  ON events USING BRIN(created_at);

BRIN indexes are magical for time-series data but useless for random access. They work well when your data is naturally sorted by the indexed column (like timestamps).

Index Maintenance: The Shit Nobody Talks About

Indexes add overhead to every INSERT, UPDATE, and DELETE operation. Write performance can drop 50% when unnecessary indexes accumulate on heavily modified tables:

-- Find indexes with low usage
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes 
WHERE idx_scan < 50 -- Adjust threshold based on your app
ORDER BY pg_relation_size(indexrelid) DESC;

Figuring Out Why Queries Are Fucked

When EXPLAIN ANALYZE Shows Your Query is Broken

Spent 2 hours staring at EXPLAIN ANALYZE output that looked like alien hieroglyphics. Our user dashboard query was taking 15 seconds and I had no idea why.

PEV turns that PostgreSQL gibberish into something humans can understand. Found out our query was doing a sequential scan on 50,000 users because we didn't index created_at.

-- Analyze query performance with actual execution
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;

Signs your query plan is fucked:

• Seq Scan on anything bigger than a lookup table
• Nested Loop that processes thousands of rows (killed our product page load times)
• Sort operations when you could index the ORDER BY column
• Hash Join when a proper index would make it a fast Nested Loop

Common Query Anti-Patterns

The N+1 problem in disguise:

-- ❌ This generates separate queries for each user
SELECT id, name FROM users WHERE id = ANY($1);
-- Then for each user: SELECT COUNT(*) FROM posts WHERE user_id = ?

-- ✅ Single query with proper join
SELECT 
  u.id,
  u.name,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.id = ANY($1)
GROUP BY u.id, u.name;

Inefficient pagination:

-- ❌ OFFSET becomes slow with large offsets
SELECT * FROM posts ORDER BY created_at DESC OFFSET 10000 LIMIT 20;

-- ✅ Cursor-based pagination using indexes
SELECT * FROM posts 
WHERE created_at < $1  -- Previous page's last created_at
ORDER BY created_at DESC 
LIMIT 20;

Unindexed JSON queries:

-- ❌ Can't use indexes on JSON content
SELECT * FROM users WHERE metadata->>'role' = 'admin';

-- ✅ Extract frequently queried JSON to columns
ALTER TABLE users ADD COLUMN role TEXT;
UPDATE users SET role = metadata->>'role';
CREATE INDEX CONCURRENTLY idx_users_role ON users(role);

Fixing the Nastiest Query Problems

When Subqueries Kill Your Database

Subqueries that execute once per row:

-- ❌ Correlated subquery (executes for each row)
SELECT * FROM users u 
WHERE EXISTS (
  SELECT 1 FROM posts p 
  WHERE p.user_id = u.id AND p.status = 'published'
);

-- ✅ Semi-join (much faster)
SELECT DISTINCT u.* FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published';

Use appropriate join types:

-- ✅ When you need all matching rows
SELECT u.name, p.title 
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- ✅ When you need user data even without posts
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

Connection Pool Optimization

Connection management affects performance more than most queries:

// ❌ Opening connections per request
export default async function handler(req: Request) {
  const supabase = createClient(url, key); // New connection
  const { data } = await supabase.from('users').select('*');
  return new Response(JSON.stringify(data));
}

// ✅ Connection pooling with pgbouncer
const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!,
  {
    db: {
      schema: 'public',
    },
    auth: {
      persistSession: false, // Don't store sessions in serverless
    },
  }
);

// Use ?pgbouncer=true in connection string
const connectionString = `postgresql://postgres:${password}@${host}:6543/${db}?pgbouncer=true`;

Monitoring Query Performance Over Time

Set up automated performance regression detection:

-- Create a view for tracking query performance trends
CREATE OR REPLACE VIEW query_performance_summary AS
SELECT 
  LEFT(query, 100) as query_sample,
  calls,
  total_exec_time + total_plan_time as total_time,
  (total_exec_time + total_plan_time) / calls as avg_time,
  max_exec_time + max_plan_time as max_time,
  stddev_exec_time + stddev_plan_time as stddev_time
FROM pg_stat_statements
WHERE calls > 10  -- Only queries with significant usage
ORDER BY total_time DESC;

-- Weekly performance review
SELECT 
  query_sample,
  avg_time,
  max_time,
  calls
FROM query_performance_summary
WHERE avg_time > 1000  -- Queries averaging >1 second
OR max_time > 10000;   -- Any query >10 seconds

Things That Actually Improved Our Performance

What worked after 3 weeks of debugging:

1. Added indexes on email, user_id, and status columns (response times dropped 80%)
2. Deleted 8 unused indexes that were slowing every INSERT
3. Added ?pgbouncer=true to connection string (stopped ECONNREFUSED crashes)
4. Fixed the 5 slowest queries from pg_stat_statements (eliminated 30-second page loads)
5. Reset pg_stat_statements to get clean metrics after fixes
6. Cache hit rate went to 97% after upgrading from 1GB to 2GB instance
7. No query takes longer than 3 seconds under normal traffic
8. Set up alerts for connection pool >85% and cache hit rate <90%

Fixed missing indexes first. Spent weeks on "advanced" query optimization when the real problem was no index on user_id in our biggest table.

Start with the obvious stuff. Most performance disasters are missing WHERE clause indexes, not exotic query patterns.