PostgreSQL vs Supabase

Audits connection concentration by client address and identifies queries with high execution time evaluating Row Level Security auth policies.

# 1. Audit active connections by client IP, application and pooling mode
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  application_name, 
  client_addr, 
  count(*) AS connection_count, 
  state 
FROM pg_stat_activity 
GROUP BY application_name, client_addr, state 
ORDER BY connection_count DESC 
LIMIT 8;"

# 2. Inspect slow queries spending significant time in RLS policy evaluation
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  query, 
  calls, 
  ROUND(mean_exec_time::numeric, 2) AS mean_ms, 
  ROUND(total_exec_time::numeric, 2) AS total_ms 
FROM pg_stat_statements 
WHERE query ILIKE '%auth.uid()%' OR query ILIKE '%current_setting%' 
ORDER BY mean_exec_time DESC 
LIMIT 5;"

Monitors active logical replication slots used by Supabase Realtime to detect WAL retention buildup before disk volumes fill.

# 1. Inspect replication slots and unconsumed WAL byte lag
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  slot_name, 
  plugin, 
  active, 
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS retained_wal_bytes 
FROM pg_replication_slots;"

# 2. Audit database size and total WAL consumed by inactive slots
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  pg_size_pretty(pg_database_size(current_database())) AS db_size, 
  pg_size_pretty(sum(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))) AS total_slot_wal_bytes 
FROM pg_replication_slots;"