When your self-hosted Supabase application starts feeling sluggish, the culprit is usually sitting right in your database: slow queries. Unlike Supabase Cloud's built-in Query Performance dashboard, self-hosted users need to roll up their sleeves and dig into Postgres directly. The good news? You have full access to powerful debugging tools that can cut query times from seconds to milliseconds.
This guide walks you through identifying, analyzing, and fixing slow queries in your self-hosted Supabase instance using the same techniques professional DBAs use.
Understanding Query Performance in Self-Hosted Supabase
Self-hosted Supabase runs on standard PostgreSQL, which means you have access to all of Postgres's built-in performance analysis tools. The key difference from Supabase Cloud is that there's no pre-built dashboard showing you slow queries—you need to query the performance data yourself.
The two essential tools for query debugging are:
- pg_stat_statements: An extension that tracks execution statistics for every SQL query
- EXPLAIN ANALYZE: A command that shows exactly how Postgres executes a query
Both are available in your self-hosted instance, but pg_stat_statements needs to be enabled first.
Enabling pg_stat_statements
Before you can find slow queries, you need to enable the pg_stat_statements extension. Connect to your Postgres database and run:
-- Enable the extension CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Verify it's working SELECT * FROM pg_stat_statements LIMIT 1;
For the extension to capture meaningful data, you may need to adjust your postgresql.conf:
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all pg_stat_statements.max = 10000
After modifying the config, restart Postgres. In a Docker Compose setup, you can add these as command arguments to your Postgres container.
Finding Your Slowest Queries
Once pg_stat_statements is enabled, you can identify the queries consuming the most time:
SELECT
substring(query, 1, 100) as short_query,
calls,
round(total_exec_time::numeric, 2) as total_ms,
round(mean_exec_time::numeric, 2) as avg_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) as percent_of_total
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 20;
This query shows you:
- The query text (truncated for readability)
- How many times it's been called
- Total execution time across all calls
- Average time per call
- What percentage of total database time this query represents
Focus on queries with high total_ms values first—these are your biggest opportunities for improvement. A query that runs 1000 times at 10ms each (10 seconds total) might be more impactful to optimize than one that runs once at 500ms.
Using EXPLAIN ANALYZE Effectively
Once you've identified a slow query, EXPLAIN ANALYZE shows you exactly what Postgres is doing:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM posts WHERE user_id = 'abc-123' AND created_at > '2026-01-01' ORDER BY created_at DESC LIMIT 10;
The output reveals:
- Planning Time: How long Postgres spent planning the query
- Execution Time: Actual query execution time
- Buffers: How many disk reads vs cache hits occurred
- Rows: Estimated vs actual rows at each step
Here's what to look for in the output:
Sequential Scans (Seq Scan)
Seq Scan on posts (cost=0.00..15420.00 rows=50000 width=180) Filter: (user_id = 'abc-123') Rows Removed by Filter: 950000
A sequential scan reading 1 million rows to return 50,000 is a red flag. This query needs an index on user_id.
Index Scans (Good!)
Index Scan using idx_posts_user_id on posts (cost=0.43..8.45 rows=1 width=180) Index Cond: (user_id = 'abc-123')
This is what you want to see—Postgres using an index to jump directly to relevant rows.
Nested Loops with High Row Counts
Nested Loop (actual time=0.030..2847.291 rows=100000 loops=1)
Nested loops are fine for small result sets but become expensive with large row counts. Consider restructuring the query or adding appropriate indexes.
The 80/20 Rule: Indexes First
Missing indexes cause roughly 80% of slow queries. If you're filtering, sorting, or joining on a column, it probably needs an index:
-- Single column index CREATE INDEX CONCURRENTLY idx_posts_user_id ON posts (user_id); -- Composite index for multi-column filters CREATE INDEX CONCURRENTLY idx_posts_user_created ON posts (user_id, created_at DESC); -- Partial index for specific conditions CREATE INDEX CONCURRENTLY idx_active_users ON users (email) WHERE deleted_at IS NULL;
Always use CONCURRENTLY in production—it creates the index without locking the table, preventing downtime.
After creating an index, run EXPLAIN ANALYZE again to verify Postgres uses it. Sometimes the query planner decides a sequential scan is still faster (usually when selecting a large percentage of the table).
Optimizing Row Level Security Policies
If you're using Row Level Security, your policies can silently tank performance. A common pattern that causes problems:
-- Slow: auth.uid() called for every row CREATE POLICY "Users see own posts" ON posts FOR SELECT USING (user_id = auth.uid());
With 100,000 rows, auth.uid() is evaluated 100,000 times. The fix is simple but not obvious:
-- Fast: auth.uid() called once CREATE POLICY "Users see own posts" ON posts FOR SELECT USING (user_id = (SELECT auth.uid()));
Wrapping the function in a subquery forces Postgres to evaluate it once and cache the result. This single change can improve query times by 10x or more on large tables.
Monitoring Long-Running Queries in Real-Time
To catch problematic queries as they happen, use pg_stat_activity:
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
AND state != 'idle'
ORDER BY duration DESC;
This shows all queries running longer than 5 seconds. For production systems, consider setting up automated alerts when queries exceed a threshold.
To kill a runaway query:
SELECT pg_terminate_backend(pid);
Setting Up Query Timeout Protection
Prevent any single query from monopolizing resources by setting timeouts:
-- Set timeout for current session (10 seconds) SET statement_timeout = '10s'; -- Set default for all connections ALTER DATABASE postgres SET statement_timeout = '30s'; -- Set for specific role ALTER ROLE authenticator SET statement_timeout = '15s';
For your self-hosted Supabase's authenticator role (used by PostgREST), a 15-30 second timeout is usually appropriate.
Self-Hosted Query Analysis Tools
Since you don't have Supabase Cloud's Query Performance UI, consider these self-hosted alternatives:
pgBadger
pgBadger parses Postgres logs and generates detailed HTML reports showing slow queries, error rates, and connection patterns. It's free, runs locally, and requires no ongoing infrastructure:
pgbadger /var/log/postgresql/postgresql-*.log -o report.html
Percona Monitoring and Management (PMM)
PMM provides Datadog-level query analytics as a self-hosted Docker container. It captures query statistics from pg_stat_statements and presents them with execution time breakdowns, I/O wait analysis, and historical trends—all without monthly fees.
pgwatch2
A lightweight option that stores metrics in InfluxDB or TimescaleDB with Grafana dashboards. Good for teams wanting monitoring without the overhead of larger solutions.
Connection Pooling Impact on Query Performance
If you're running your self-hosted Supabase with connection pooling via PgBouncer (included in the default Docker Compose), be aware that some debugging techniques differ:
pg_stat_activityshows pooler connections, not application connectionspg_stat_statementsstill works correctly- Long-running transactions can starve the pool
For detailed connection analysis, check PgBouncer's admin console:
psql -h localhost -p 6432 pgbouncer SHOW STATS; SHOW POOLS;
Automating Query Performance Checks
Create a simple health check script that alerts on degraded performance:
-- Find queries that suddenly got slower
WITH recent AS (
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE calls > 100
)
SELECT
substring(query, 1, 80) as query,
round(mean_exec_time::numeric, 2) as avg_ms
FROM recent
WHERE mean_exec_time > 100 -- Alert if avg > 100ms
ORDER BY mean_exec_time DESC
LIMIT 10;
Run this on a schedule (via pg_cron or external cron) and send alerts when queries exceed your thresholds.
When Supascale Helps
Managing query performance across multiple self-hosted Supabase instances gets complex. With Supascale, you can deploy and manage multiple projects from a single interface, with each project having its own isolated Postgres instance. This makes it easier to test query optimizations in staging before deploying to production.
Supascale's selective service deployment also helps—if you're not using Realtime or Edge Functions, disabling them frees up server resources for your database, potentially improving query performance on resource-constrained servers.