PostgreSQL handles a lot before it needs help. When it does struggle, the cause is almost always one of: missing or wrong indexes, ORM-generated N+1 queries, or misunderstood query planner behavior. Let's look at each.
Understand EXPLAIN ANALYZE first
Before optimizing anything, run EXPLAIN ANALYZE on your slow query. The output shows what the planner chose and how long each step took. Sequential scans on large tables and nested loop joins with large row counts are your targets.
-- The most useful form — shows actual timing, buffer hits, and planning time
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.tenant_id = 'tenant_abc'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 50;
-- Look for: Seq Scan on large tables, Nested Loop with many rows
-- Good: Index Scan, Hash Join, parallel workers being usedComposite indexes for multi-tenant queries
The most common indexing mistake: adding a single-column index on a frequently-filtered column when every query also filters by tenant_id. A composite index (tenant_id, column) will be dramatically faster because PostgreSQL can use it to satisfy both filter conditions at once.
-- Bad: single column index ignores the tenant_id filter
CREATE INDEX idx_orders_status ON orders (status);
-- Good: composite index — tenant_id first (equality), then status
CREATE INDEX CONCURRENTLY idx_orders_tenant_status
ON orders (tenant_id, status, created_at DESC);
-- Partial index for a common filtered subset
CREATE INDEX CONCURRENTLY idx_active_users
ON users (tenant_id, email)
WHERE deleted_at IS NULL; -- Only indexes active usersN+1 queries in ORMs
ORM convenience hides N+1 patterns. Loading 50 users and then fetching each user's orders separately makes 51 queries instead of 2. The fix is eager loading — but you need to be deliberate about what you load.
// Bad: N+1 — 1 query for users + 1 per user for orders
const users = await prisma.user.findMany({ where: { tenantId } });
for (const user of users) {
const orders = await prisma.order.findMany({
where: { userId: user.id },
});
// Process...
}
// Good: 2 queries total — users + all their orders in one JOIN
const users = await prisma.user.findMany({
where: { tenantId },
include: {
orders: {
take: 10,
orderBy: { createdAt: "desc" },
select: { id: true, total: true, status: true, createdAt: true },
},
},
});Connection pooling with PgBouncer
PostgreSQL can handle a few hundred simultaneous connections before performance degrades. At scale with a serverless deployment (where each function invocation opens a new connection), you'll hit this limit quickly. PgBouncer or Supabase's built-in pooler sits in front of PostgreSQL and multiplexes connections — essential for serverless workloads.
In transaction mode pooling (the default for serverless), prepared statements don't work across pooled connections. If you're using Prisma with PgBouncer, add `pgbouncer=true` to your connection string to disable named prepared statements.
Auravon AI
Engineering Studio
