Otimização de consultas PostgreSQL com Prisma: EXPLAIN ANALYZE, índices e pool de conexões

Contexto

Desenvolver APIs em TypeScript com Prisma e PostgreSQL é comum, mas performance real depende de três pilares: entender o plano de execução (EXPLAIN/ANALYZE), ter índices corretos e manter a camada de conexões saudável (pooling). Este guia avançado foca exatamente nisso — como diagnosticar consultas lentas geradas pelo Prisma, otimizar índices e configurar pooling (incluindo recomendações para ambientes serverless).

Explicação técnica

Como o Prisma gera SQL e por que importa

Prisma Client traduz chamadas TypeScript/JS em SQL. Uma mesma consulta de alto nível pode gerar JOINs, subqueries ou CTEs que afetam o plano. Entender o SQL gerado é o primeiro passo: sem ele você estará apenas chutando otimizações.

EXPLAIN ANALYZE — o diagnóstico definitivo

EXPLAIN mostra o plano estimado; EXPLAIN ANALYZE executa a consulta e retorna tempos reais e estatísticas (ROWS, BUFFERS). Use sempre:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <sua_sql>;

O FORMAT JSON facilita parsing programático (útil em scripts Node). Dados a observar:

• Total runtime (time)
• Rows estimadas vs rows reais (estimation error)
• Sequential scan vs index scan
• Buffers (hit/read ratio) — revela I/O

Índices — tipos e quando usar

Escolher e criar índices é o maior ganho unitário para queries. Tipos e casos:

• B-Tree: padrão. Ótimo para equality e range (WHERE col = x, col >= x).
• GIN/GiST: para jsonb, tsvector e arrays. Use GIN para buscas full-text e containment (@>).
• Index expression: quando consulta usa função (ex: lower(email)). Crie índice em expression para evitar seq scan.
• Partial index: índice apenas para um subconjunto (ex: WHERE active = true). Reduz custo quando maioria dos registros não é consultada.
• Covering index (INCLUDE): inclui colunas não-indexadas para evitar lookup à heap (Index-only scan).

Pool de conexões — fundamentos

Prisma Client usa o driver node-postgres (pg) que emprega um pool de conexões. Cada instância do PrismaClient cria seu próprio pool. Em processos persistentes (containers, monolito), isto é OK. Em ambientes serverless (lambda/FaaS), criação de muitas instâncias causa esgotamento de conexões no PostgreSQL.

Soluções práticas:

• Em produção tradicional: limite o número de instâncias e ajuste max_connections do Postgres.
• Serverless: use PgBouncer em transaction pooling ou o Prisma Data Proxy (recomendado) para evitar burst de conexões.
• Monitore com pg_stat_activity e ferramentas de métricas.

Outras considerações técnicas

• VACUUM/ANALYZE: mantenha estatísticas atualizadas — planners dependem delas.
• Parameter sniffing / generic plans: Postgres escolhe entre custom/generic plans para prepared statements. Isso pode impactar perf. Ajustes finos via configuration (plan_cache_mode em versões futuras) são raros; melhor é garantir estatísticas e índices adequados.
• Evite SELECT * quando não precisa; selecione colunas específicas.

Exemplos práticos

Exemplo 1 — Extrair SQL gerado pelo Prisma e rodar EXPLAIN ANALYZE

Mostre o SQL gerado, execute EXPLAIN (ANALYZE, FORMAT JSON) e analise resultados em Node/TS.

// prisma-explain.ts
import { PrismaClient } from '@prisma/client';
import { Client } from 'pg';

const prisma = new PrismaClient({ log: ['query'] });

async function main() {
  // 1) Expor SQL via log (opcional)
  prisma.$on('query', (e) => {
    console.log('SQL gerado:', e.query, 'params:', e.params);
  });

  // 2) Obter a consulta SQL equivalente (exemplo: findMany com where)
  // Aqui executamos diretamente um raw EXPLAIN ANALYZE para o SQL de interesse.
  const pg = new Client({ connectionString: process.env.DATABASE_URL });
  await pg.connect();

  const sql = `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    SELECT u.id, u.email
    FROM users u
    JOIN posts p ON p.author_id = u.id
    WHERE u.active = true AND p.published = true
    ORDER BY p.created_at DESC LIMIT 100;`;

  const res = await pg.query(sql);
  // res.rows[0]['QUERY PLAN'] ou res.rows[0].query_plan dependendo do cliente
  console.log('EXPLAIN JSON:', JSON.stringify(res.rows[0], null, 2));

  await pg.end();
}

main().catch(console.error).finally(() => process.exit());

Resultado: analise "Plan" JSON. Compare rows estimadas e reais e veja se há seq scan.

Exemplo 2 — Criando índices adequados e rodando migrate

Se EXPLAIN mostrar seq scan por causa de lower(email) em buscas case-insensitive, crie índice de expressão:

-- migration.sql
CREATE INDEX ux_users_lower_email ON users (lower(email));

-- para jsonb queries
CREATE INDEX idx_posts_tags_gin ON posts USING gin ((metadata->'tags'));

-- partial index e covering include
CREATE INDEX idx_active_posts_created_at ON posts (created_at DESC) WHERE published = true;
CREATE INDEX idx_posts_author_include ON posts (author_id) INCLUDE (title, created_at);

Depois rode ANALYZE users; e reexecute EXPLAIN ANALYZE para confirmar mudança de plano (index scan ou index-only scan).

Exemplo 3 — EXPLAIN via Prisma $queryRaw e parsing do JSON

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function explainViaPrisma() {
  const explain = await prisma.$queryRaw`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    SELECT * FROM users WHERE lower(email) = lower(${ 'alice@example.com' });`;

  // prisma.$queryRaw retorna array de objetos dependendo da versão
  console.log('EXPLAIN result', JSON.stringify(explain, null, 2));
}

explainViaPrisma().catch(console.error).finally(() => process.exit());

Com o JSON você pode automatizar detecção de discrepancies (rows vs plan->Plan->Actual Rows) e gerar alertas.

Erros comuns (e como corrigi-los)

1. Conexões esgotadas em produção/serverless

Sintomas: erros de conexão, latência alta ao criar conexões. Causa: muitas instâncias do pool abrindo conexões simultâneas.

Soluções:

• Use Prisma Data Proxy (se disponível) ou PgBouncer em transaction pooling.
• Reduza pool size e número de instâncias; monitore pg_stat_activity.

2. Índices criados mas não usados

Sintomas: EXPLAIN ainda mostra seq scan. Causas possíveis:

• Estatísticas desatualizadas — rode ANALYZE (ou VACUUM ANALYZE).
• Operador incompatível: função aplicada à coluna sem índice de expressão (ex: WHERE lower(col) = 'x' sem índice lower(col)).
• Cardinalidade baixa: planner pode preferir seq scan para tabelas pequenas.

3. Planos com estimativas muito erradas

Causa: stats desatualizadas, ou tipos de dados complexos (jsonb). Corrija com ANALYZE e considere criar STATISTICS para colunas problemáticas:

ALTER TABLE mytable ALTER COLUMN json_column SET STATISTICS 1000;
ANALYZE mytable;

4. Uso indevido de $queryRawUnsafe

Evite injectable SQL. Prefira template-tagged $queryRaw`...${param}`. Quando for necessário, escape e valide manualmente.

Checklist final — passos práticos para diagnosticar e otimizar

1. Logue SQL gerado pelo Prisma: configure PrismaClient com log: ['query'] ou use prisma.$on('query').
2. Reproduza a consulta lentamente no psql e rode: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <sql>;
3. Compare rows estimadas vs reais e identifique seq scans/large buffers read.
4. Crie índices adequados: expression index, partial index, GIN para jsonb/tsvector, INCLUDE para covering index.
5. Rode ANALYZE após criar índices e reavalie o plano.
6. Evite SELECT * e traga apenas colunas necessárias para reduzir I/O.
7. Monitore conexões (pg_stat_activity). Em serverless, use PgBouncer (transaction pooling) ou Prisma Data Proxy.
8. Se usar PgBouncer: configure pool_size no PgBouncer e mantenha autenticação/timeout adequados.
9. Instrumente métricas: latência por query, rows returned, cache hit ratio (buffers), e taxa de conexões ativas.

Exemplo de configuração básica do PgBouncer (pgbouncer.ini)

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction    ; para serverless
max_client_conn = 1000
default_pool_size = 50

timeout = 600

# ajuste default_pool_size = número de conexões que cada pgbouncer vai manter para o Postgres

Conectar Prisma ao PgBouncer usando postgresql://user:pass@pgbouncer-host:6432/mydb.

Conclusão

Otimização com Prisma não é mágica: exige visibilidade (SQL), diagnóstico (EXPLAIN ANALYZE) e mudanças na camada de dados (índices, stats) e infraestrutura (pooling). Siga o checklist: capture SQL, execute EXPLAIN JSON, crie índices específicos, atualize estatísticas e resolva gargalos de conexão com PgBouncer ou Data Proxy. Pequenas mudanças (índice de expressão, partial index, ajuste de pool) frequentemente cortam latência em ordens de grandeza.