Performance and Connection Pooling

Tune Postgres like Postgres

Add indexes for columns used in filters, joins, ordering and RLS policies. Use explain analyze to read query plans; the dashboard's Performance Advisor and query performance reports highlight slow queries. Serverless functions open many short connections, so connect through Supavisor, Supabase's connection pooler: transaction mode suits serverless, session mode suits long-lived servers. Pooler hosts, ports and modes are shown in the dashboard's connect panel; check the docs for your project.

Finding and fixing a slow query

SQL.

explain analyze
select id, title
from public.tasks
where project_id = 1 and done = false
order by id desc
limit 20;

-- a sequential scan on a large table suggests an index:
create index if not exists tasks_project_done_idx
  on public.tasks (project_id, done, id desc);

-- policy columns need indexes too
create index if not exists notes_user_id_idx on public.notes (user_id);

A taxi rank instead of private cars

A pooler is a taxi rank: many passengers (requests) share a small fleet of cars (database connections) instead of each one parking their own.

त्वरित जाँच: Why use Supavisor transaction mode for serverless functions?

• Many short-lived clients share a small pool of database connections
• It disables RLS for speed
• It caches all query results forever
• It replaces indexes