Question 1

How do I fix a slow PostgreSQL query?

Accepted Answer

Run EXPLAIN ANALYZE on the query to inspect the execution plan. Look for sequential scans on large tables, mismatched row estimates, and missing indexes, then add a targeted index or rewrite the query to use joins instead of repeated lookups.

Question 2

How to prevent N+1 queries in application code?

Accepted Answer

Replace per-row queries inside loops with a single query using JOINs and JSON aggregation, or use batch loading. In PostgreSQL, json_agg with a LEFT JOIN fetches parent records and their children in one round trip.

Question 3

Which index type should I use in PostgreSQL?

Accepted Answer

B-tree indexes cover most equality and range queries. Use GIN for JSONB or array containment, GiST for geometric or full-text data, and partial indexes when queries consistently filter on a condition like status = 'published'.

Question 4

Does adding an index lock the table in production?

Accepted Answer

A standard CREATE INDEX locks writes on the table. Use CREATE INDEX CONCURRENTLY to build the index without blocking reads and writes, noting it cannot run inside a transaction block.

Question 5

Can I use connection pooling with Supabase serverless functions?

Accepted Answer

Yes. Route connections through the Supabase transaction pooler by switching the database port from 5432 to 6543, and disable session persistence in the client since server-side code does not need auth session storage.

agency-database-optimizer

SYSTEM DOCUMENTATION & REQUIREMENTS

Frequently Asked Questions about agency-database-optimizer

Related Skills

Database Optimizer

postgresql-optimization

apm

Backend Queries