Búsqueda full-text en Postgres con tsvector

Cuando empecé a construir la búsqueda de notas para NotasMD, mi primer instinto fue reservar tiempo para integrar Elasticsearch o Algolia. Pero con menos de un millón de filas, Postgres ya trae todo lo necesario: tsvector, tsquery e índices GIN.

El problema con LIKE '%query%'

Un LIKE con comodines a ambos lados no puede usar un índice B-tree normal, así que Postgres termina escaneando la tabla completa. Funciona con 100 filas, pero se degrada rápido.

-- Escanea toda la tabla, no usa índice
SELECT * FROM notes WHERE content LIKE '%markdown%';

Columna generada + índice GIN

La solución es agregar una columna tsvector generada automáticamente a partir del contenido, y luego indexarla con GIN:

ALTER TABLE notes
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('spanish', title || ' ' || content)) STORED;
 
CREATE INDEX notes_search_idx ON notes USING GIN (search_vector);

Con eso, una búsqueda pasa de escanear filas a resolver contra un índice:

SELECT id, title, ts_rank(search_vector, query) AS rank
FROM notes, to_tsquery('spanish', 'markdown & editor') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

ts_rank para relevancia

ts_rank pondera por frecuencia y posición de las coincidencias, así que resultados donde el término aparece en el título suben antes que los que solo lo mencionan una vez en el cuerpo. No es tan sofisticado como BM25, pero para búsqueda dentro de notas personales es más que suficiente.

Cuándo sí vale la pena algo externo

Si necesitas facetas, typo-tolerance agresivo, o buscar sobre decenas de millones de documentos con baja latencia garantizada, Elasticsearch o Meilisearch siguen siendo la mejor opción. Pero para la mayoría de productos internos o MVPs, tsvector evita una pieza más de infraestructura que mantener.