Búsqueda de texto completo en PostgreSQL: bien hecha, es rápida (desmintiendo el mito de que es lenta)

• Existe la percepción de que el Full-Text Search (FTS) nativo de PostgreSQL es lento, pero con la optimización adecuada puede funcionar muy rápido
• En el blog de Neon se compara la extensión pg_search, basada en Rust, con el FTS nativo, y se afirma que este último es lento
• Sin embargo, es muy probable que esa comparación se haya hecho omitiendo optimizaciones básicas indispensables para el FTS de PostgreSQL
• Este artículo demuestra con cifras que incluso aplicando optimizaciones simples a la configuración base del FTS se puede lograr una mejora de 50 veces en el rendimiento

Resumen de la configuración del benchmark

• Se realizó una prueba basada en una tabla con 10 millones de registros de logs

  CREATE TABLE benchmark_logs (  
      id SERIAL PRIMARY KEY,  
      message TEXT,  
      country VARCHAR(255),  
      severity INTEGER,  
      timestamp TIMESTAMP,  
      metadata JSONB  
  );  
  

• Estructura de la consulta problemática:

  SELECT country, COUNT(*)  
  FROM benchmark_logs  
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
  GROUP BY country  
  ORDER BY country;  
  

  • Ejecutar to_tsvector() dentro de la consulta → muy ineficiente
  • Incluso si hay un índice GIN, no se aprovecha correctamente

Entorno de pruebas (réplica de la configuración base)

• Instancia EC2 i7ie.xlarge, usando SSD NVMe local
• 4 vCPUs, PostgreSQL 16 (Docker)
• Configuración principal de PostgreSQL:

  -c shared_buffers=8GB  
  -c maintenance_work_mem=8GB  
  -c max_parallel_workers=4  
  -c max_worker_processes=4  
  

• Límite de paralelismo: max_parallel_workers_per_gather = 2 (Neon usa 8)

Factor 1 de degradación: cálculo de tsvector en tiempo real

• Al ejecutar to_tsvector() dentro de la consulta:
• Se realizan cada vez el parsing de texto, análisis morfológico, etc.
• No se puede aprovechar el índice en absoluto

• Solución: crear e indexar previamente una columna tsvector

  • 1. Agregar la columna tsvector

  ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;  
  

  • 2. Llenar los datos

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);  
    

  • 3. Crear el índice (desactivando fastupdate)

    CREATE INDEX idx_gin_logs_message_tsvector  
    ON benchmark_logs USING GIN (message_tsvector)  
    WITH (fastupdate = off);  
    

  • 4. Modificar la consulta

    SELECT country, COUNT(*)  
    FROM benchmark_logs  
    WHERE message_tsvector @@ to_tsquery('english', 'research')  
    GROUP BY country  
    ORDER BY country;  
    

Factor 2 de degradación: fastupdate=on en el índice GIN

• fastupdate=on favorece el rendimiento de escritura, pero perjudica el rendimiento de búsqueda
• Para datasets de solo lectura o centrados en búsquedas, fastupdate=off es indispensable
• El índice es más pequeño y más rápido, y no requiere procesar la pending list

• Cómo crear un índice GIN optimizado

  CREATE INDEX idx_gin_logs_message_tsvector  
  ON benchmark_logs USING GIN (message_tsvector)  
  WITH (fastupdate = off);  
  

Mejora de rendimiento: más de 50 veces

• Antes de optimizar: aprox. 41.3 segundos (41,301 ms)
• Después de optimizar: aprox. 0.88 segundos (877 ms)
• Muestra una mejora de rendimiento de alrededor de 50 veces
• Es posible alcanzar este rendimiento incluso en entornos con poco paralelismo

El rendimiento de ts_rank sí puede ser realmente lento

• ts_rank o ts_rank_cd pueden ser relativamente lentos porque evalúan todos los resultados antes de ordenarlos
• Especialmente al manejar grandes volúmenes de resultados, la carga de CPU/IO es alta

Funciones avanzadas de ranking: extensión VectorChord-BM25

• Si la precisión y la velocidad del ordenamiento son importantes, puede ser más efectivo usar una extensión dedicada
• VectorChord-BM25 es una extensión para PostgreSQL que ofrece funciones de ranking basadas en el algoritmo BM25
• Incluso hay reportes de que es 3 veces más rápido que Elasticsearch

Ventajas de VectorChord-BM25

• Algoritmo BM25: un algoritmo de ranking de búsqueda más avanzado que TF-IDF
• Formato de índice dedicado: optimizado para búsquedas de alta velocidad como Block WeakAnd
• Proporciona el tipo bm25vector: almacena representaciones tokenizadas
• Mejora tanto la precisión como la velocidad de búsqueda

Conclusión: el FTS nativo de PostgreSQL también es suficientemente rápido

• Si se usan una columna tsvector y un índice GIN adecuado (fastupdate=off), es posible lograr búsquedas muy rápidas incluso con el FTS nativo
• Las comparaciones de rendimiento deben hacerse sobre una base optimizada
• Si se necesitan funciones avanzadas de ranking, vale la pena considerar herramientas de extensión como VectorChord-BM25
• Mensaje clave: no es que la herramienta sea lenta, sino que la configuración puede ser el problema

[stadia]

Gracias a eso, optimizamos la consulta.

[pcj9024]

Las opiniones en Hacker News dan miedo... "¿Diez millones? ¿Es una broma?"

[GN⁺]

Comentarios en Hacker News

• Como mantenedor de pg_search, según la documentación de Postgres, tanto la estrategia del artículo de Neon/ParadeDB como la usada aquí se presentan como alternativas válidas

  • El problema de Postgres FTS no es optimizar una sola consulta, sino ofrecer un rendimiento de nivel Elastic para una variedad de consultas reales
  • pg_search fue diseñado para resolver este último problema, y los benchmarks también lo reflejan
  • El benchmark de Neon/ParadeDB incluye solo 12 consultas en total, lo cual no es realista para casos de uso del mundo real
  • pg_search funciona con una variedad de consultas de estilo "Elastic" y tipos de Postgres con solo una definición de índice simple

• Calcular tsvector en tiempo real es un gran error

  • Cuando implementé Postgres FTS en un proyecto personal, leí la documentación y seguí las guías
  • La documentación explica claramente cómo crear primero un caso base no optimizado y luego optimizarlo
  • Parece que quien cometió este error no leyó la documentación o tenía la intención de presentar mal Postgres FTS

• No entiendo la tendencia de querer meter todo en Postgres

• Me alegra ver más implementaciones de búsqueda de texto completo nativas de Postgres

  • Las soluciones alternativas (lucene/tantivy) están diseñadas para segmentos inmutables, así que al combinarlas con tablas heap de Postgres pueden terminar siendo una solución peor

• Sin el plan de ejecución es difícil entender qué está pasando

  • Si la consulta usa el índice, la reverificación en tiempo real de tsvector solo se aplica a las coincidencias, y como la consulta del benchmark usa LIMIT 10, hay poca reverificación
  • Como la condición de la consulta tiene restricciones sobre 2 índices gin, parece que el planificador vuelve a verificar primero todas las coincidencias

• Hace algunos años quise usar FTS nativo, pero fracasé

  • En una tabla con miles de inserciones por segundo, las actualizaciones completas se volvieron lentas y las transacciones agotaban el tiempo
  • Agregué índices, pero cuando terminó el segundo índice empezaron los timeouts en el sistema
  • Tuve que borrar otra vez los índices y nunca tuve la oportunidad de probar el rendimiento real de FTS

• Empaqueté extensiones RPM/DEB para pg_search y vchord_bm25

  • Dejo el enlace para quien quiera hacer sus propios benchmarks

• He visto a muchos equipos saltar directamente a Elasticsearch o Meilisearch

  • Si se usa bien, se puede sacar mucho rendimiento del FTS nativo de PG
  • Me pregunto si se podría obtener un rendimiento parecido en el navegador usando SQLite + FTS5 + Wasm

• 10 millones de registros es un dataset de juguete

  • Un dataset de texto grande, como toda Wikipedia o los comentarios de Reddit anteriores a 2022, sería más apropiado para benchmarks

• Usé por primera vez el texto completo de pg alrededor de 2008

  • El problema de la búsqueda de texto completo de Postgres no es que sea demasiado lenta, sino que no es lo bastante flexible
  • Está bien para agregar búsquedas simples, pero se queda corta si quieres ajustar la búsqueda
  • Solr y Elasticsearch permiten configurar índices complejos y procesamiento de búsqueda
  • Postgres podría adoptar esas funciones, pero por ahora no ofrece nada de eso
  • Postgres divide por espacios en blanco, y uno puede usar manualmente stopwords y stemming
  • No es posible asignar puntuación de búsqueda basada en pesos de campos
  • Comparado con las alternativas, es un sistema de juguete