A missing index can turn a 5ms query into a 50-second nightmare. When you're running self-hosted Supabase, database performance is entirely your responsibility—and indexing is the single highest-impact optimization you can make.
Unlike Supabase Cloud, where some of the performance tooling is available in the dashboard, self-hosted deployments require you to configure and manage these tools yourself. The good news? PostgreSQL ships with everything you need. The challenge is knowing how to use it.
This guide walks you through identifying slow queries, implementing effective indexes, and monitoring performance on your self-hosted Supabase instance.
Why Indexing Matters for Self-Hosted Deployments
When you deploy Supabase on your own server, you control the hardware but also own the performance outcomes. Without proper indexing, your PostgreSQL database performs sequential scans—reading every row in a table to find matches. On small tables, this is fine. On tables with millions of rows, it's catastrophic.
Indexes create efficient lookup structures that let PostgreSQL jump directly to relevant rows. The performance difference can be 100x or more. But indexes aren't free:
- They consume disk space — sometimes significantly
- They slow down writes — every INSERT, UPDATE, and DELETE must update the index
- They require maintenance — fragmented indexes degrade over time
The goal isn't to index everything. It's to index strategically.
Enabling Performance Monitoring Extensions
Before optimizing, you need visibility. Self-hosted Supabase should have these extensions enabled by default, but verify they're active.
pg_stat_statements
This extension tracks execution statistics for every query: call count, execution time, rows returned, and I/O metrics. It's your primary tool for finding problematic queries.
-- Check if pg_stat_statements is enabled SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements'; -- If not, enable it CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
After enabling, you may need to restart PostgreSQL for full tracking to begin.
index_advisor
Supabase developed index_advisor to suggest indexes based on query patterns. It creates virtual indexes and tests their impact without actually building them.
-- Enable index_advisor CREATE EXTENSION IF NOT EXISTS index_advisor;
Note that index_advisor has limitations: it only recommends single-column B-tree indexes. For GIN/GIST indexes on JSON or array columns, you'll need to evaluate those separately.
Finding Slow Queries
The most important metric in pg_stat_statements is total_exec_time—the cumulative execution time across all invocations. A 5ms query running 100,000 times per hour is far more impactful than a 500ms query running 10 times.
Query the Top Offenders
SELECT query, calls, total_exec_time, mean_exec_time, rows, shared_blks_read, shared_blks_hit FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
Focus on queries with:
- High
total_exec_time— cumulative impact - High
shared_blks_read— reading from disk (sequential scans) - Low
shared_blks_hit— cache misses
A query with massive shared_blks_read values relative to rows returned is almost certainly missing an index.
Check Cache Hit Rate
Your cache hit rate should be above 99% for optimal performance:
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;
If this ratio drops significantly, you either need more memory allocated to shared_buffers or your queries are scanning too much data. Often, it's the latter.
Using index_advisor for Recommendations
Once you've identified slow queries, pass them to index_advisor:
SELECT * FROM index_advisor('
SELECT * FROM orders
WHERE customer_id = $1
AND status = $2
ORDER BY created_at DESC
');
The extension analyzes the query plan and suggests indexes that would improve performance. Example output:
startup_cost_before | startup_cost_after | index_statements 0.00 | 0.29 | CREATE INDEX ON orders (customer_id, status, created_at DESC)
The cost difference shows the expected improvement. However, always test suggested indexes in a staging environment first—Postgres's query planner may not use an index if it determines a sequential scan would be faster for your actual data distribution.
Index Types and When to Use Them
PostgreSQL supports multiple index types. Choosing correctly matters.
B-tree (Default)
Use for: Equality and range queries on scalar values.
-- Good for WHERE status = 'active' CREATE INDEX idx_orders_status ON orders (status); -- Good for WHERE created_at > '2026-01-01' CREATE INDEX idx_orders_created ON orders (created_at);
This is your workhorse. Most queries benefit from B-tree indexes.
BRIN (Block Range Index)
Use for: Sequential, time-series data that rarely updates.
-- Perfect for append-only tables with temporal data CREATE INDEX idx_events_created ON events USING BRIN (created_at);
BRIN indexes are 10x smaller than equivalent B-tree indexes on large datasets. The trade-off: they work best when data is physically ordered on disk (like timestamped events).
GIN (Generalized Inverted Index)
Use for: JSONB fields, arrays, and full-text search.
-- Index a JSONB column for containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Query using the index
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';
GIN indexes are essential if you're querying into JSONB structures—without them, PostgreSQL scans every row.
GiST (Generalized Search Tree)
Use for: Geometric data and range types (common with PostGIS).
-- For geospatial queries CREATE INDEX idx_locations_point ON locations USING GIST (coordinates);
If you're using PostGIS with self-hosted Supabase, GiST indexes are mandatory for performant spatial queries.
Advanced Indexing Strategies
Composite Indexes
When queries filter on multiple columns, a composite index prevents PostgreSQL from checking multiple indexes:
-- For queries like: WHERE customer_id = X AND status = 'pending' CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
Column order matters. Put the most selective column (fewest matching rows) first. If you filter by status alone sometimes, reverse the order or create a second index.
Partial Indexes
If queries frequently target a subset of data, partial indexes are smaller and faster:
-- Only index active orders CREATE INDEX idx_orders_active ON orders (customer_id, created_at) WHERE status = 'active';
The query's WHERE clause must match the index's WHERE clause for PostgreSQL to use it.
Covering Indexes (INCLUDE)
If a query selects columns beyond the indexed ones, PostgreSQL must hit the heap (table) for those values. Covering indexes include extra columns:
-- The query can be satisfied entirely from the index CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (status, total, created_at);
This eliminates heap lookups but increases index size.
Creating Indexes in Production
The default CREATE INDEX locks the table against writes. On production tables, this is unacceptable.
Always use concurrent index creation:
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
Concurrent creation takes longer and uses more resources, but it doesn't block writes. Two caveats:
- Concurrent creation can fail — if it does, you're left with an invalid index that you must drop manually
- It requires more disk space — the operation needs room for temporary structures
Monitoring Index Creation
For large tables, index creation can take hours. Monitor progress:
SELECT phase, blocks_done, blocks_total, (blocks_done::float / blocks_total * 100)::int as percent_done FROM pg_stat_progress_create_index;
Maintaining Index Health
Indexes degrade over time, especially on tables with frequent updates and deletes.
Check for Bloat
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;
An index that's never scanned (idx_scan = 0) is dead weight. Consider dropping it.
Reindexing
Fragmented indexes need rebuilding:
-- Rebuild a specific index (blocks writes) REINDEX INDEX idx_orders_customer; -- Rebuild concurrently (PostgreSQL 12+) REINDEX INDEX CONCURRENTLY idx_orders_customer;
For self-hosted Supabase running PostgreSQL 14+, concurrent reindexing is safe and recommended.
Indexing Foreign Keys
Relational databases typically require indexes on foreign key columns. PostgreSQL doesn't create these automatically:
-- order_items.order_id references orders.id -- Without an index, JOINs are slow CREATE INDEX idx_order_items_order ON order_items (order_id);
Check for missing foreign key indexes:
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE tablename = tc.table_name
AND indexdef LIKE '%' || kcu.column_name || '%'
);
How Supascale Helps
While indexing is inherently a database concern, Supascale reduces the operational burden that makes performance tuning difficult on self-hosted deployments. When you're not spending hours on backup configuration, SSL certificates, and OAuth setup, you have time to actually optimize your database.
With Supascale's one-click deployment, you get a properly configured Supabase instance with the performance extensions already enabled. The time you save on infrastructure management can go toward the work that actually matters—like building proper indexes.
Key Takeaways
- Enable pg_stat_statements — you can't optimize what you can't measure
- Focus on total_exec_time — cumulative impact matters more than single-query duration
- Use index_advisor — let PostgreSQL suggest indexes, but verify they're actually used
- Match index type to query pattern — B-tree for most cases, GIN for JSONB, BRIN for time-series
- Always use CONCURRENTLY — never lock production tables
- Index foreign keys — PostgreSQL won't do this automatically
- Monitor and maintain — indexes degrade and need periodic attention
Proper indexing transforms a struggling self-hosted database into a high-performance system. The investment in understanding your query patterns and implementing strategic indexes pays dividends every time your application makes a database call.