PostgreSQL Performance Tuning on Supabase: Row-Level Security, Indexing & Pool Management
A deep dive into query planning, composite B-tree indexes, RLS execution bottlenecks, and optimizing Supabase for high-throughput production.
Supabase provides a powerful managed PostgreSQL stack out of the box. But as your database scales past hundreds of thousands of rows and concurrent connections surge, sub-optimal queries and poorly written Row-Level Security (RLS) policies can bring query latency from 10ms to over 2,000ms.
Here are the essential PostgreSQL performance tuning techniques we deploy at Proyo Technologies.
1. Optimize Row-Level Security (RLS) Policies
RLS is executed on every single row returned by a query. If your RLS policy performs a subquery without proper indexes, PostgreSQL will execute a sequential scan for every candidate row.
-- BAD: Re-evaluates auth.uid() subquery for every single row CREATE POLICY "Users view own documents" ON documents FOR SELECT USING (user_id = auth.uid()); -- GOOD: Wraps auth function in a subselect so PostgreSQL caches the result once per statement CREATE POLICY "Users view own documents optimized" ON documents FOR SELECT USING (user_id = (SELECT auth.uid()));
2. Composite Indexes Matching Query Filters
When querying ordered lists with filters (e.g., published blog posts ordered by date):
SELECT * FROM blogs WHERE status = 'published' AND content_type = 'blog' ORDER BY published_at DESC;
A single-column index on published_at is insufficient. PostgreSQL will perform a bitmap index scan and filter out unpublished rows manually.
Instead, create a partial composite index:
CREATE INDEX idx_blogs_published_feed ON public.blogs (published_at DESC) WHERE status = 'published' AND content_type = 'blog';
This index is up to 90% smaller on disk and allows PostgreSQL to satisfy the query with a single lightning-fast Index Scan.
3. Avoiding Connection Exhaustion
In Next.js server actions and API routes, ensure:
- All client initialization reuses a single singleton
createClientinstance. - Production database URLs connect through Supavisor port
6543(Transaction Pooling), never direct port5432for serverless runtimes. - Keep database payload sizes small by omitting large JSON or markdown text columns from listing queries.
