Postgres para desenvolvedores: EXPLAIN, índices e pooling

Depois deste artigo você vai conseguir pegar uma query lenta no Postgres, ler o plano de execução, decidir qual índice criar e confirmar que ele resolveu o problema. Também vai saber reconhecer o N+1 do ORM e entender por que muitas conexões derrubam o banco antes do CPU. Os exemplos valem para o Postgres 17 e 18.

O problema real#

A aplicação ficou lenta. Alguém sugere subir uma réplica de leitura, outra pessoa limpa o cache, e a conta de nuvem cresce. Ninguém abriu o plano de execução da query suspeita.

Na maioria dos casos o culpado é um destes três: falta um índice, o índice existe mas não serve para aquela consulta, ou o ORM está disparando uma query por linha. Os três se descobrem em minutos com as ferramentas certas.

EXPLAIN ANALYZE: lendo o plano de verdade#

EXPLAIN mostra o plano que o Postgres escolheu. EXPLAIN ANALYZE executa a query e mostra o que aconteceu de fato. Como ele executa, cuidado com UPDATE e DELETE: envolva em BEGIN; ... ROLLBACK;.

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, u.email, COUNT(o.id) AS total_pedidos
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.email;

A saída tem esta forma (valores ilustrativos, a sua vai ser diferente):

HashAggregate  (cost=... rows=45 ...) (actual time=410.2..410.9 rows=45 loops=1)
  ->  Hash Right Join  (cost=... rows=9000 ...) (actual time=12.1..380.4 rows=9000 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=... rows=2000000 ...) (actual time=0.02..210.5 rows=2000000 loops=1)
        ->  Hash  (...)
              ->  Seq Scan on users u  (...)
                    Filter: (status = 'active')
                    Rows Removed by Filter: 950000

O que olhar, nesta ordem:

Pista	O que indica
actual time	Tempo real em milissegundos. O nó com o maior valor próprio é o gargalo.
Seq Scan com Rows Removed by Filter alto	A tabela inteira foi lida para descartar quase tudo. Candidato a índice.
rows= estimado muito diferente do real	Estatísticas desatualizadas. Rode ANALYZE nome_da_tabela.
loops= alto	O nó rodou muitas vezes. Costuma ser sinal de N+1 ou de um nested loop ruim.
Buffers: shared read alto	Muita leitura de disco em vez de cache.

O cost é uma unidade interna do planejador, não tem relação com segundos. Use para comparar planos, não para medir.

Um Seq Scan não é erro por si só. Em tabela pequena, ou quando a query precisa de boa parte das linhas, ler tudo é a escolha mais barata.

Índices B-tree#

É o tipo padrão e resolve a maior parte dos casos.

CREATE INDEX idx_orders_user_id ON orders (user_id);

Em tabela em produção, prefira CREATE INDEX CONCURRENTLY, que não bloqueia as escritas (não pode rodar dentro de uma transação):

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Rode o EXPLAIN ANALYZE de novo. Se o plano continua com Seq Scan, o planejador decidiu que o índice não compensa, ou a query não está escrita de um jeito que o índice consiga usar (função aplicada na coluna, por exemplo).

Índice composto: a ordem importa#

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

Esse índice atende bem filtros por user_id e por user_id junto com created_at. Para um filtro só em created_at, ele historicamente não ajudava, porque o índice é ordenado primeiro por user_id. O Postgres 18 passou a fazer "skip scan" em alguns desses casos, mas só vale quando a primeira coluna tem poucos valores distintos. Não conte com isso: se a consulta filtra só por created_at, crie um índice para ela.

A regra prática é colocar na frente as colunas filtradas por igualdade e depois a coluna de intervalo ou de ordenação.

Índice parcial#

Se quase toda consulta olha só uma fatia da tabela, indexe só ela:

CREATE INDEX idx_users_ativos_email ON users (email)
WHERE status = 'active';

O índice fica menor e mais rápido de manter. O planejador só o usa quando a query tem uma condição compatível com o WHERE do índice.

GIN para JSONB e busca textual#

Para consultas de contenção em JSONB, use GIN com o operador @>:

CREATE INDEX idx_events_data ON events USING GIN (data);

SELECT * FROM events WHERE data @> '{"type": "purchase"}';

Atenção: a forma data->>'type' = 'purchase' não usa esse índice GIN. Para ela, crie um índice de expressão:

CREATE INDEX idx_events_type ON events ((data->>'type'));

Para busca de texto, indexe a mesma expressão que a consulta usa:

CREATE INDEX idx_articles_body ON articles
USING GIN (to_tsvector('portuguese', body));

SELECT * FROM articles
WHERE to_tsvector('portuguese', body) @@ to_tsquery('portuguese', 'inteligencia & artificial');

Quanto índice é demais#

Cada índice acelera leituras e atrasa escritas, porque todo INSERT e UPDATE precisa atualizá-los. Para achar índices que ninguém usa:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

N+1 em ORM#

O N+1 acontece quando o código busca uma lista e depois faz uma query para cada item.

# N+1: 1 query para usuários + 1 para cada usuário
usuarios = session.scalars(select(User).where(User.status == "active")).all()
for u in usuarios:
    print(u.email, len(u.orders))  # dispara SELECT em orders a cada iteração

Com 100 usuários são 101 queries. Cada uma é rápida, mas a latência de rede se soma. A solução mais simples no SQLAlchemy 2.x é pedir o carregamento junto:

from sqlalchemy.orm import selectinload

usuarios = session.scalars(
    select(User)
    .where(User.status == "active")
    .options(selectinload(User.orders))
).all()

Isso resulta em 2 queries, independentemente do número de usuários. Se você só precisa da contagem, agregue no banco:

from sqlalchemy import func

linhas = session.execute(
    select(User.email, func.count(Order.id))
    .join(Order, isouter=True)
    .where(User.status == "active")
    .group_by(User.id)
).all()

Para pegar N+1 antes de produção, ligue o log de queries do ORM em desenvolvimento e conte quantas saem por requisição. Em produção, pg_stat_statements mostra as queries que mais consomem tempo total:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Transações e isolamento#

O nível padrão é READ COMMITTED: cada comando enxerga o que já foi commitado até aquele momento. Dentro da mesma transação, duas leituras podem devolver valores diferentes.

BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 100
-- outra sessão atualiza para 50 e faz commit
SELECT balance FROM accounts WHERE id = 1;  -- 50
COMMIT;

Com REPEATABLE READ a transação trabalha sobre um retrato fixo dos dados:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- 100
-- outra sessão atualiza para 50 e faz commit
SELECT balance FROM accounts WHERE id = 1;  -- continua 100
COMMIT;

Resumo dos níveis no Postgres:

• READ UNCOMMITTED existe na sintaxe, mas se comporta como READ COMMITTED. O Postgres nunca mostra dados não commitados.
• READ COMMITTED é o padrão.
• REPEATABLE READ dá um retrato consistente, mas pode abortar com erro de serialização, e sua aplicação precisa tentar de novo.
• SERIALIZABLE evita todas as anomalias, com o mesmo custo de precisar repetir transações abortadas.

Para uma transferência entre contas, o mais simples e previsível costuma ser travar as linhas envolvidas, em ordem fixa para evitar deadlock:

BEGIN;
SELECT balance FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Pooling de conexões#

Cada conexão no Postgres é um processo do sistema operacional, com memória própria. Centenas de conexões abertas por várias instâncias da aplicação esgotam max_connections (o padrão é 100) e prejudicam o desempenho, mesmo que a maioria esteja parada.

SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;

A saída é colocar um pooler como o PgBouncer entre a aplicação e o banco. Centenas de conexões de clientes viram algumas dezenas no Postgres. Um pgbouncer.ini mínimo:

[databases]
app = host=postgres port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
max_prepared_statements = 100

Duas observações. No modo transaction, o servidor só fica reservado durante a transação, então recursos que dependem da sessão (como SET solto ou advisory locks de sessão) não funcionam como esperado. E as versões recentes do PgBouncer (1.21 em diante) suportam prepared statements nesse modo por meio de max_prepared_statements, o que antes era uma fonte comum de erros.

A aplicação passa a apontar para a porta 6432 em vez da 5432. Consulte a documentação do PgBouncer para montar o userlist.txt e escolha uma imagem mantida ativamente se for usar Docker.

Armadilhas comuns#

• Rodar SELECT * sem LIMIT em tabela grande, num cliente interativo.
• Aplicar função na coluna filtrada (WHERE lower(email) = ...) sem um índice de expressão correspondente.
• Usar NOT IN com subconsulta que pode devolver NULL: basta um NULL para a comparação nunca ser verdadeira e o resultado vir vazio. Prefira NOT EXISTS:

SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

• Esquecer de rodar ANALYZE depois de uma carga grande, deixando o planejador com estatísticas velhas.

Quando o Postgres não é a resposta#

Para análises sobre volumes enormes, um data warehouse faz mais sentido. Arquivos grandes como imagens e vídeos ficam melhor em armazenamento de objetos, guardando só a referência no banco. E se uma leitura precisa responder em poucos milissegundos com muita repetição, um cache na frente ajuda, como mostra o artigo sobre cache com Redis.

Próximos passos#

Se a ideia é busca por similaridade dentro do próprio Postgres, veja Vector database na prática, que cobre a extensão pgvector. Para acompanhar o banco em produção, Monitoramento com Prometheus e Grafana mostra como expor métricas e alertas.

Para praticar, suba um Postgres local, gere um milhão de linhas com generate_series, rode o EXPLAIN ANALYZE antes e depois de criar o índice e compare os tempos.

Quer aplicar isso na sua empresa? Marque uma conversa de 45 minutos em https://iauaicloud.com.br/consultoria