PostgreSQL Full Text Search (FTS) na prática: Consultas rápidas em milhões de linhas

Introdução

Buscar palavras em colunas de texto em bancos de dados grandes pode ser um pesadelo. Usar LIKE
‘%palavra%’ ou ILIKE parece simples, mas gera varreduras completas (Seq Scan), consumindo muito tempo.
O PostgreSQL tem um recurso nativo poderoso: Full Text Search (FTS). Ele cria um índice invertido sobre o
texto, tornando buscas instantâneas, mesmo em tabelas com milhões de linhas.

Preparando o ambiente

CREATE DATABASE fts_lab;
\c fts_lab;

CREATE TABLE artigos (
 id SERIAL PRIMARY KEY,
 titulo TEXT,
 conteudo TEXT
);

Populando com 1 milhão de linhas

INSERT INTO artigos (titulo, conteudo)
SELECT
'Artigo ' || g,
(
SELECT string_agg(word, ' ')
FROM (
SELECT (ARRAY[
'PostgreSQL','MySQL','ElasticSearch','Full Text','Indexação',
'Consultas','Banco de dados','Escalabilidade','Performance',
'Tuning','Backup','Replicação','JSONB','Monitoramento','SQL','Paula'
])[(random()15+1)::int] AS word FROM generate_series(1, (random()50+20)::int)
) AS palavras
)
FROM generate_series(1, 1000000) g;

ANALYZE artigos;

Número significativo de dados adicionado, em seguida vamos fazer uma busca por uma palavra (Paula) que adicionei aleatoriamente em uma das um milhão de linhas fazendo a busca textual com o operador ILIKE.

EXPLAIN ANALYZE
SELECT *
FROM artigos
WHERE conteudo ILIKE '%Paula%';
Gather (cost=1000.00..106730.31 rows=1 width=673) (actual time=10445.397..10445.547 rows=1 loops=1) Workers Planned: 2
 Workers Launched: 2
 -> Parallel Seq Scan on artigos (...)
 Filter: (conteudo ~~* '%Paula%'::text)
 Rows Removed by Filter: 366666
Execution Time: 10446.250 ms

Analisando o plano de execução, o PostgreSQL fez um Parallel Seq Scan: varreu TODAS as linhas da tabela (1M registros). Resultado em 10,4 segundos (10446 ms) para achar 1 linha. Escalabilidade aqui é ruim e o tempo cresce linearmente com o número de registros e mesmo se houvesse algum índice nessa coluna ele não provavelmente não seria utilizado principalmente se o prefixo for fixo, exemplo: ‘%Paula%’.

Habilitando Full Text Search com Índice GIN

CREATE INDEX idx_fts_artigos_conteudo
ON artigos
USING gin(to_tsvector('portuguese', conteudo));
EXPLAIN ANALYZE
SELECT *
FROM artigos
WHERE to_tsvector('portuguese', conteudo) @@ to_tsquery('portuguese', 'Paula');
Bitmap Heap Scan on artigos (cost=670.63..19810.90 rows=5500 width=673) (actual time=0.020..0.021 r Recheck Cond: (to_tsvector('portuguese', conteudo) @@ '''paul'''::tsquery)
 -> Bitmap Index Scan on idx_fts_artigos_conteudo (...)
Execution Time: 0.044 m

O PostgreSQL usou o índice GIN (Bitmap Index Scan) para localizar rapidamente as linhas. Resultado em
0,044 ms (!), mais de 200.000x mais rápido do que o ILIKE.

Conclusão

O Full Text Search nativo do PostgreSQL é uma solução madura e poderosa:

• Indexa texto em múltiplos idiomas.

• Permite ranking, destaque e buscas semânticas.

• Escala para milhões de linhas sem perda de performance.

Se você ainda usa ILIKE para buscar texto, considere implementar FTS com GIN. É simples, rápido e transforma completamente a experiência do usuário.

Quer conhecer mais sobre o PostgreSQL?

Acesse plataforma de cursos da 4Linux e veja nosso treinamento de banco de dados focado em PostgreSQL. Ele aborda diversos temas focado nas melhores práticas desde instalação, backup, replicação e alta disponibilidade.