PostgreSQL - A armadilha silenciosa da chave estrangeira
Recentemente, durante uma verificação de rotina no sistema em produção, me deparei com algo que parecia, à primeira vista, inofensivo. Tudo começou com uma consulta simples no monitoramento de desempenho:
SELECT * FROM users WHERE profile_id = 1 LIMIT 1;
Simples, direta e - teoricamente - eficiente. Só que essa query, sozinha, estava levando cerca de 3 segundos para ser concluída. Três segundos por uma linha. Alarmante.
Naturalmente, o primeiro passo foi acessar diretamente o banco de dados e executar um EXPLAIN para entender o plano de execução. O resultado foi o seguinte:
Seq Scan on users (cost=0.00..1212652.31 rows=1 width=759)
Filter: (profile_id = 1)
O PostgreSQL estava utilizando um Sequential Scan, ou seja, varrendo toda a tabela users para encontrar o registro com profile_id = 1.
Isso explica a lentidão, principalmente considerando que a tabela tem alguns milhões de registros. A ausência de uso de índice para essa coluna foi, no mínimo, inesperada. Afinal, profile_id é uma chave estrangeira, e como minha experiência anterior é no MySql, eu esperava que uma coluna usada em uma foreign key seja automaticamente indexada. No PostgreSQL, isso não acontece.
O problema disfarçado de convenção
É compreensível para nós desenvolvedores, presumirmos que definir uma chave estrangeira automaticamente implica em ter um índice para ela. Afinal, se a coluna será usada em joins frequentes, essa parece ser uma suposição razoável. Mas o PostgreSQL é explícito: ele garante a integridade referencial, não a performance.
##A solução Assim que entendi o motivo do Seq Scan, criei uma migration simples para adicionar o índice à coluna:
$table->index('profile_id');
Depois de aplicada a mudança, executei novamente a query:
EXPLAIN SELECT * FROM users WHERE profile_id = 1 LIMIT 1;
Agora, o plano de execução era muito mais agradável:
Index Scan using idx_users_profile_id on users (cost=0.29..8.31 rows=1 width=759)
Index Cond: (profile_id = 1)
A diferença foi imediata: a query passou de 3 segundos para menos de 250 milissegundos.
Por que o PostgreSQL não cria o índice automaticamente?
Essa foi a pergunta que ficou martelando na minha cabeça depois de resolver o problema. Fui atrás de documentação, fóruns e artigos da comunidade para entender o motivo. Descobri que esse comportamento é intencional: o PostgreSQL não cria índices automaticamente em chaves estrangeiras porque parte do princípio de oferecer flexibilidade total ao desenvolvedor. Nem toda foreign key necessariamente precisa de um índice - em alguns casos, ela existe apenas para garantir a integridade referencial e dificilmente participa de consultas. Criar índices indiscriminadamente em todas as FKs poderia gerar um volume desnecessário de estruturas no banco, prejudicando a performance das operações de CRUD como um todo.
No fim das contas, o PostgreSQL joga a responsabilidade para você. É uma liberdade que vem com um preço: você precisa saber o que está fazendo.
Sempre confiei que os ORMs ou o próprio banco fariam isso por mim, como acontece no MySql. Mas dessa vez, fui surpreendido - e aprendi do jeito mais eficaz possível: resolvendo um problema real em produção.
Hoje, vejo com outros olhos cada definição de chave estrangeira. Se você, assim como eu, usa PostgreSQL, recomendo fortemente que revise suas tabelas. Veja quais FKs realmente participam de queries e adicione os índices manualmente onde fizer sentido. E acima de tudo, não subestime uma query aparentemente simples. No mundo real, até a consulta mais básica pode esconder armadilhas de performance. Um EXPLAIN no momento certo pode economizar horas de dor de cabeça - aprendi isso na prática.
Você pode dar mais detalhes sobre Isto?
Afinal, profile_id é uma chave estrangeira, e em muitos bancos de dados relacionais (como o MySQL, por padrão), espera-se que uma coluna usada em uma foreign key seja automaticamente indexada. No PostgreSQL, isso não acontece
Quais muitos? O MySQL é um, tem outros? Na verdade só no InnoDB, porque isso é não uma unanimidade, pode ser ruim em alguns casos.
É compreensível para nós desenvolvedores, presumirmos que definir uma chave estrangeira automaticamente implica em ter um índice para ela
Por que? Pra mim é presumível que não vai criar. De tudo que estudei desde os anos 80, até hoje, em livros dos mais renomados, eu mesmo tendo iniciado um projeto de banco de dados onde tive quer aprender detalhes que quase ninguém estuda, e nunca vi motivo para criar qualquer índice que não seja chave primária de forma automática, mesmo para PK há quem diga que pode ter vantagemem não ser automático e eu tendo a concordar que no mínimo deveria ser opt-out, mas por que não fazer opt-in até para fazer a pessoa pensar sobre com mais cuidado?
Em vários casos a FK não será usada com frequência, especialmente para fazer JOIN e até a PK pode ter casos, especialmente em tabelas pequenas que pode ficar mais caro ter índice, apesar de ser um caso que não fará tanta diferença.
Criar um índice torna toda escrita mais cara e muitas vezes o gargalo do DB é na escrita, então a ideia que nenhum índice ou só a PK deveria ser automático faz sentido.
Eu já vi gente dizendo, e fez, que deveria criar índice para cada oluna, o que não faz sentido algum, até porque a maioria nuca será usado e muitos que precisaria vão continuar não existiundo já que vários índices só tem valor com chaves compostas. A regra é bem simples e me parece imutável, só crie índices que você possa provar que dá um ganho e não deixe de criar nenhum que dá algum ganho. Exceções sempre podem existir em cada contexto.
Sempre precisa saber oque está fazend, até com o MySQL. Claro que o MySQL tem uma filosofia parecida com JS, tenta dar um resultado para o usuário, mesmo que ele seja errado. Programador bom não gosta dessa filosofia. Na verdade, ninguém deveria gostar.
Alguns ORMs criam índice para a FK automaticamente em todos os SGDBs que eles suportarem, eventualmente dependendo do engine que foi feito para ele usar determinado SGDB. Espero que estes pelo menos tenham opt-out, e não ter é mais um motivo para não usar ORM.
Pensar sobre a criação de índices é fundamental.
Ver mais:
- https://pt.stackoverflow.com/q/35088/101
- https://pt.stackoverflow.com/q/183305/101
- https://pt.stackoverflow.com/q/23348/101
- https://pt.stackoverflow.com/q/55118/101
- https://pt.stackoverflow.com/q/32052/101
S2
Farei algo que muitos pedem para aprender a programar corretamente, gratuitamente (não vendo nada, é retribuição na minha aposentadoria) (links aqui no perfil também).
Meus 2 cents:
Tai um problema comum em app gerado por Vibe Coding: ausencia de otimizacoes.
Neste momento, a IA nao eh tao inteligente no sentido de entender as nuances de um sistema e aplicar varias otimizacoes nos BD: criar indices (ou quando nao criar), aplicar normalizacao / 3FN, notar quando a desnormalizacao eh mais eficiente.
Uma das sacadas para ser um bom DBA era justamente conhecer as pesquisas mais usadas em um sistema e perceber como otimizar a organizacao dos dados, que ia desde indices, escolha no tipo de campos ate como distribuir as informacoes em tablespaces (e estas em particoes ou discos separados).
Confesso que uma das coisas que me incomoda nos sistemas "modernos" (react/prisma/drizzle/etc) eh o padrao de usar uuid/cuid como chave primaria - me acostumei a poupar cada byte sempre que possivel, entao usar utiny, usmallint, umediumint e quando a tabela tinha tendencia a acrescer muito, ubigint.
Enfim, parabens pelo artigo - eh um bom alerta sobre este tipo de risco.