MySQL vs PostgreSQL Búsqueda de texto completo: cuándo lo incorporado es suficiente y cuándo es una trampa

La línea de decisión: cuándo la búsqueda incorporada es suficiente

Aquí va la versión directa: la búsqueda de texto completo incorporada es suficiente cuando tu búsqueda es una característica interna, no tu producto. Es suficiente cuando puedes explicar tus requisitos en un párrafo y ese párrafo no contiene las palabras “¿querías decir?”, “sinónimos”, “fuzzy”, “rankings personalizados”, “autocompletar” o “analítica casi en tiempo real”.

Lo incorporado es suficiente cuando…

• El volumen de datos es moderado y el alcance de búsqueda está limitado. Piensa: buscar tickets en una herramienta de soporte, artículos de una base de conocimientos, nombres de producto, documentos internos.
• Los requisitos de latencia son “rápidos para la app”, no “rápidos como un motor de búsqueda bajo caos”. Si el P95 puede ser 200–500 ms y nadie filtra en vivo en cada pulsación, a menudo puedes quedarte dentro de la base de datos.
• El ranking puede ser “suficientemente bueno”, y estás dispuesto a ajustarlo mediante pesos y stopwords—no mediante entrenamiento de modelos.
• La consistencia importa más que la sofisticación de relevancia. La búsqueda en la base de datos es transaccionalmente consistente si la diseñas así; los motores externos añaden desfase del índice y otro dominio de fallo.
• Puedes convivir con limitaciones como rarezas de tokenización, decisiones de stemming por idioma y el ocasional ticket “¿por qué no coincide esta frase exacta?”.

La búsqueda incorporada se convierte en una trampa cuando…

• Estás construyendo un producto de búsqueda. Si la relevancia de búsqueda es tu diferenciador, no finjas que un motor general de BD reemplazará indefinidamente una pila de búsqueda dedicada.
• Tus consultas mezclan texto completo con filtros de alta cardinalidad y ordenación y esperas que escale linealmente. No lo hará. Escalará como un comité.
• La búsqueda multiinquilino es “buscar entre todos los inquilinos” y tu diseño de índice no está explícitamente construido para eso. Pagarás en CPU, memoria y contención de bloqueos desagradable.
• “Simplemente añade un índice” se convierte en tu modelo operativo. Los índices de texto completo no son índices B-tree regulares. Puedes hincharlos, provocarlos a churn y convertir el mantenimiento en un trabajo a tiempo parcial.
• Necesitas funciones avanzadas: coincidencia difusa, sinónimos por inquilino, scoring complejo, analizadores por campo, resaltado, “más como esto”, sugerencias de consulta, tolerancia a errores tipográficos o búsqueda híbrida vectorial+léxica.

Si estás indeciso: comienza con la búsqueda incorporada para validar comportamiento y necesidades de los usuarios, pero diseña el flujo de datos para que luego puedas replicar documentos a un motor dedicado sin reescribir toda la app. Dicho de otro modo: no conviertas la base de datos en tu clúster de búsqueda y luego te sorprendas cuando empiece a comportarse como uno.

Cómo funciona realmente la búsqueda de texto completo en MySQL (y dónde falla)

La búsqueda de texto completo de MySQL es engañosamente simple: añade un FULLTEXT index, usa MATCH() AGAINST(), despliega. Bajo el capó, tu motor de almacenamiento importa (InnoDB hoy, MyISAM históricamente), tus reglas de tokenización importan, y tu “relevancia” estará moldeada por valores por defecto que no sabías que habías aceptado.

InnoDB FULLTEXT: lo que realmente estás comprando

InnoDB implementa FULLTEXT manteniendo tablas auxiliares para el índice invertido. Está integrado, lo suficientemente transaccional para la mayoría de aplicaciones, pero tiene su propio perfil de recursos: mantenimiento de índices en segundo plano, E/S potencialmente intensiva durante actualizaciones grandes y sensibilidad a stopwords y tamaños mínimos de token.

Modo natural vs modo booleano: dos bestias diferentes

El modo natural es “simplemente busca este texto” y devuelve una puntuación de relevancia. El modo booleano añade operadores y te permite forzar inclusión/exclusión y coincidencia por prefijo. En producción, el modo booleano se vuelve popular porque los usuarios quieren “debe incluir X”, pero el modo booleano también facilita escribir consultas que devuelven demasiado, muy poco o que escanean más de lo que crees.

Modos de fallo que verás en MySQL

• “¿Por qué buscar ‘to be’ no devuelve nada?” Stopwords y longitud mínima de palabra. Tu sistema está “funcionando como diseñado”, lo cual rara vez consuela.
• La relevancia parece aleatoria porque el modelo de puntuación es tosco y está fuertemente influenciado por la frecuencia de término y la longitud del documento de una manera que puede no coincidir con tu dominio.
• Los costes de construcción/actualización del índice te sorprenden. Actualizaciones masivas o ediciones frecuentes a campos de texto grandes pueden crear amplificación de escritura y lag de replicación.
• Problemas de colación y charset afectan la tokenización y comparaciones. Puedes tener problemas de “cadena que parece igual, diferentes tokens” cuando la normalización Unicode no es consistente.
• Los planes de consulta se degradan cuando combinas predicados de texto completo con otros filtros y ordenaciones. MySQL puede elegir planes subóptimos o forzar tablas temporales.

Una realidad operativa: cuando FULLTEXT de MySQL se descontrola, los síntomas parecen “la base de datos está lenta”. La búsqueda se convierte en el vecino ruidoso que roba el presupuesto de CPU al resto de tu app. Y dado que vive dentro de la BD, es más difícil aislarlo sin limitar la tasa de la función o moverla fuera.

Cómo funciona realmente la búsqueda de texto completo en PostgreSQL (y dónde falla)

La búsqueda de texto completo de PostgreSQL (FTS) es una caja de herramientas. Construyes documentos tsvector, los consultas con tsquery, los indexas con GIN (habitualmente) o GiST (a veces), y clasificas resultados con ts_rank o similares. Es más explícito que MySQL. Eso es bueno porque puedes ajustarlo, y malo porque puedes ajustarlo mal.

tsvector, tsquery y por qué la normalización no es opcional

Postgres no “busca el texto bruto” de la misma forma. Analiza el texto en lexemas (tokens normalizados), aplicando típicamente stemming según una configuración de búsqueda de texto (como english). Eso significa que debes decidir qué configuración de idioma usas y si el stemming es deseable. Es fantástico para que “running” coincida con “run”. Es terrible cuando tus códigos de producto o citas legales se deforman.

Elección de índice: GIN es rápido en lectura, no gratis de mantener

Los índices GIN son la herramienta para FTS porque manejan bien la forma de índice invertido. Las lecturas son rápidas. Las escrituras pueden ser costosas. Si actualizas documentos con frecuencia, las listas pendientes de GIN y el comportamiento de vacuum se convierten en tu nuevo pasatiempo. (Nadie elige ese pasatiempo a propósito.)

Modos de fallo que verás en PostgreSQL

• Hinchazón y ralentizaciones del índice GIN si tienes mucho churn y vacuum insuficiente, o si indexas documentos grandes sin pensar.
• Disputas de ranking porque las funciones de ranking por defecto no son lo que el gerente de producto considera “obvio”. Necesitarás pesos, normalización y a veces campos separados.
• Desajuste de configuración de idioma conduce a “¿por qué ‘analysis’ no coincide con ‘analyses’?” o “¿por qué buscar un número de parte coincide con tonterías?”
• Mala construcción de consultas (usar to_tsquery directamente con entrada de usuario) convierte la puntuación en errores de sintaxis y transforma consultas de usuario en incidentes operativos.
• Combinar FTS con filtrado y ordenación puede desencadenar grandes bitmap index scans y presión de memoria si no estructuras la consulta e índices deliberadamente.

Postgres te recompensa por ser explícito: vectores separados por campo, ranking ponderado, columnas generadas, índices parciales por inquilino y construcción de consulta limpia vía plainto_tsquery o websearch_to_tsquery. También te castiga por improvisar en producción.

Hechos e historia interesantes que puedes usar

1. MyISAM tuvo full-text primero, mucho antes de que InnoDB lo soportara; muchas “opiniones sobre FTS en MySQL” están fosilizadas desde la era MyISAM.
2. InnoDB FULLTEXT almacena su índice en tablas auxiliares, por eso las reconstrucciones grandes y actualizaciones intensas pueden parecer tormentas internas de E/S misteriosas.
3. tsearch de PostgreSQL precede la tendencia actual de “buscar en todas partes”; ha sido parte de Postgres durante muchas versiones importantes, evolucionando desde módulos contrib hasta el núcleo.
4. Los índices GIN fueron diseñados para valores compuestos (arrays, estructuras tipo JSON, conjuntos de lexemas). La búsqueda de texto completo es una de las mejores coincidencias para su diseño.
5. Postgres FTS tiene múltiples diccionarios y configuraciones (stemming, stopwords, parseo simple). Esa flexibilidad le permite adaptarse bien a distintos idiomas—si realmente lo configuras.
6. MySQL tiene reglas de tamaño mínimo de token que históricamente causaron misses en palabras cortas; la gente lo descubre solo después de que un CEO busca una línea de producto de dos letras.
7. Ambos sistemas hacen concesiones respecto a stopwords: quitarlas reduce tamaño de índice y ruido, pero puede destruir la relevancia para dominios donde las palabras comunes importan (texto legal, títulos, letras de canciones).
8. La relevancia en FTS no es una verdad universal. MySQL y Postgres puntúan distinto porque sus modelos difieren; migrar motores cambia resultados aunque los datos sean idénticos.
9. La replicación magnifica el dolor: una carga con mucha FTS puede aumentar el volumen de binlog/WAL y amplificar el lag durante reindexaciones masivas o actualizaciones masivas.

Una idea parafraseada que vale mantener en un post-it, atribuida a un ingeniero famoso por fiabilidad: idea parafraseada — Werner Vogels (sobre construir sistemas bajo fallo y diseñar para lo que se rompe). Trata la búsqueda como un dominio de fallo, incluso cuando vive en la base de datos.

Broma #1: La búsqueda de texto completo es como el café de oficina: cuando está bueno nadie lo menciona, y cuando está malo todos crean tickets al mismo tiempo.

Tres mini-historias corporativas desde el frente

Mini-historia 1: El incidente causado por una suposición equivocada

Una empresa SaaS mediana añadió “buscar todas las notas del cliente” en su panel de administración. Se lanzó rápido: una tabla MySQL, un índice FULLTEXT, un nuevo endpoint que ejecutaba MATCH(notes) AGAINST(?). Funcionó en staging. Incluso funcionó en producción—hasta que dejó de hacerlo.

Soporte reportó “la búsqueda está caída” en horario laboral, pero el resto de la app también se sentía lenta. El on-call vio CPU al máximo y consultas acumulándose. El equipo asumió que el índice de texto completo hacía las búsquedas “básicamente O(1)”. La suposición equivocada era más sutil: creyeron que añadir un predicado de texto completo siempre estrecharía resultados pronto y de forma barata.

En realidad, la consulta también incluía filtros por inquilino, rangos de fechas y un orden por “más recientemente actualizado”. El optimizador eligió un plan que hizo mucho más trabajo del esperado, y el “ordenar por recencia” forzó tablas temporales para conjuntos candidatos grandes. Bajo ciertos términos comunes, el conjunto candidato era masivo.

El incidente terminó con un rate-limit en el endpoint, un cambio en la UI para requerir al menos 3 caracteres no-stopword, y un rediseño: separaron “buscar” de “ordenar”, prefiltraron por inquilino y actividad reciente primero, y sólo entonces aplicaron la coincidencia de texto completo. Más tarde añadieron un servicio de búsqueda separado. La lección no fue “FULLTEXT de MySQL es malo”. Fue: el optimizador no es tu gerente de producto, y “indexado” no significa “barato”.

Mini-historia 2: La optimización que salió mal

Un equipo de marketplace en PostgreSQL intentó acelerar la búsqueda de listados creando un único gran tsvector que concatenaba título, descripción y notas del vendedor. Le dieron el mismo peso a todo. También lo almacenaron como columna generada y lo indexaron con GIN. Las consultas se hicieron más rápidas y el equipo declaró victoria.

Entonces las escrituras se volvieron más lentas. No “un poco”. Empezaron a ver picos periódicos en generación de WAL y lag de autovacuum. La aplicación tenía una función que permitía a los vendedores ajustar descripciones con frecuencia, a menudo en ráfagas (piensa en actualizaciones estacionales). Cada edición hacía churn del índice GIN. La lista pendiente de GIN creció, vacuum no pudo seguir el ritmo y la latencia de consultas se volvió irregular. La “optimización” había movido costo de lecturas a escrituras, y su sistema era intensivo en escrituras.

Intentaron ajustar autovacuum, lo que ayudó, pero volvió a fallar cuando compitió con la carga normal y causó contención de E/S. Eventualmente dividieron el vector en campos más pequeños, redujeron el contenido indexado (las notas del vendedor dejaron de ser buscables) y movieron la “búsqueda de notas” a un índice asíncrono actualizado por fuera de la ruta. La relevancia mejoró también porque los pesos volvieron a tener sentido en lugar de “todo es igualmente importante”.

La lección: el vector más grande rara vez es el mejor vector. Indexa solo lo necesario y recuerda que GIN es un acelerador de consultas, no comida gratis.

Mini-historia 3: La práctica aburrida pero correcta que salvó el día

Una aplicación empresarial ejecutaba búsqueda FTS en Postgres para documentación interna de múltiples departamentos. Nada sofisticado: tsvector por documento, índice GIN y un puñado de filtros. Lo sofisticado fue su disciplina operativa: tenían una consulta “canario” de búsqueda en el monitoreo, ejecutada cada minuto con un conjunto de términos conocido.

Un martes, vieron la latencia del canario subir gradualmente durante una hora. Aún no era un fallo, solo una pendiente. El on-call revisó estadísticas de vacuum, encontró que autovacuum se estaba quedando atrás para la tabla que almacenaba los documentos. También vieron aumento de dead tuples debido a una nueva función que actualizaba documentos con más frecuencia.

Porque tenían líneas base, respondieron antes de que fuera visible al usuario: ajustaron umbrales de autovacuum para esa tabla, programaron un VACUUM (ANALYZE) dirigido durante un periodo tranquilo y limitaron temporalmente el trabajo por lotes que actualizaba documentos. Sin reunión de postmortem, sin correos internos enfadados, solo un silencioso retorno a la normalidad.

Esta es la práctica aburrida: una consulta sintética, monitoreada en el tiempo, ligada a un subsistema específico. No es glamorosa. Es más barata que los heroísmos.

Broma #2: La forma más rápida de mejorar la relevancia de búsqueda es renombrar la función “filtro de palabras clave” y ver cómo las expectativas caen para empatar con la realidad.

Tareas prácticas: comandos, salida, significado, decisión

Estas son tareas reales que puedes ejecutar durante un despliegue, incidente o postmortem. Cada una incluye un comando, salida de ejemplo, qué significa y qué decisión tomar.

Task 1 (MySQL): Confirmar que existen índices FULLTEXT y qué cubren

cr0x@server:~$ mysql -e "SHOW INDEX FROM articles WHERE Index_type='FULLTEXT'\G"
*************************** 1. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: ft_title_body
 Seq_in_index: 1
  Column_name: title
   Index_type: FULLTEXT
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: ft_title_body
 Seq_in_index: 2
  Column_name: body
   Index_type: FULLTEXT

Significado: Tienes un índice FULLTEXT compuesto en title y body. Si la consulta de búsqueda usa solo body pero tu índice es compuesto en otro orden, aún puedes usarlo, pero el comportamiento puede diferir según el engine/version y la forma de la consulta.

Decisión: Asegura que el MATCH(title, body) de tu consulta coincida con la lista de columnas indexadas. Si no, cambia la consulta o el índice; no confíes en la magia del optimizador.

Task 2 (MySQL): Comprobar stopwords y ajustes de tamaño de token que expliquen “resultados faltantes”

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'innodb_ft_%';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3     |
| innodb_ft_max_token_size | 84    |
| innodb_ft_enable_stopword| ON    |
+--------------------------+-------+

Significado: Los tokens más cortos que 3 caracteres no se indexan. Los stopwords están activados. Búsquedas de dos letras fallarán silenciosamente.

Decisión: Si tu dominio tiene tokens cortos importantes (SKUs, códigos), planifica un cambio de configuración y reconstrucción de índice—o rediseña para almacenar un campo normalizado separado para códigos y buscarlo con índices B-tree.

Task 3 (MySQL): Validar si el optimizador está usando FULLTEXT o está haciendo algo costoso

cr0x@server:~$ mysql -e "EXPLAIN SELECT id FROM articles WHERE MATCH(title, body) AGAINST('incident response' IN NATURAL LANGUAGE MODE) AND tenant_id=42 ORDER BY updated_at DESC LIMIT 20\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
         type: fulltext
possible_keys: ft_title_body,idx_tenant_updated
          key: ft_title_body
      key_len: 0
          ref:
         rows: 12000
        Extra: Using where; Using filesort

Significado: Está usando FULLTEXT (type: fulltext), pero también hace un filesort. Ahí es donde la latencia suele morir bajo carga.

Decisión: Considera cambiar la UX/consulta: primero obtener los N mejores matches (sin ORDER BY updated_at), luego ordenar en la app; o mantener una estrategia de índice “recientes por inquilino”; o aceptar un orden menos estricto.

Task 4 (MySQL): Identificar consultas pesadas de texto completo en el slow log

cr0x@server:~$ sudo awk '/MATCH\(|AGAINST\(/ {print}' /var/log/mysql/mysql-slow.log | head -n 5
# Query_time: 1.842  Lock_time: 0.000 Rows_sent: 20  Rows_examined: 250000
SELECT id,title FROM articles WHERE MATCH(title,body) AGAINST('status page' IN BOOLEAN MODE) AND tenant_id=42 ORDER BY updated_at DESC LIMIT 20;
# Query_time: 1.221  Lock_time: 0.000 Rows_sent: 0  Rows_examined: 180000
SELECT id FROM articles WHERE MATCH(body) AGAINST('to be' IN NATURAL LANGUAGE MODE) AND tenant_id=42 LIMIT 20;

Significado: Tienes consultas examinando enormes cantidades de filas en relación con las filas devueltas. También nota que “to be” devuelve 0 (probablemente stopwords/longitud mínima de token).

Decisión: Añade guardarraíles: longitud mínima de consulta, mensajes UI conscientes de stopwords y reescrituras de consulta que apliquen filtros selectivos primero.

Task 5 (MySQL): Comprobar lag de replicación causado por churn de indexado

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 143

Significado: Estás 143 segundos detrás. Las actualizaciones intensivas en FULLTEXT pueden amplificar esto durante ráfagas.

Decisión: Si las actualizaciones de búsqueda causan lag, desacopla el indexado (asíncrono), agrupa actualizaciones fuera de pico o mueve la búsqueda fuera de la ruta primaria de BD.

Task 6 (PostgreSQL): Confirmar tipo y tamaño del índice FTS (alerta temprana de bloat)

cr0x@server:~$ psql -d appdb -c "\di+ public.*fts*"
                                           List of relations
 Schema |     Name      | Type  |  Owner   |   Table   | Persistence | Access method |  Size  | Description
--------+---------------+-------+----------+-----------+-------------+---------------+--------+-------------
 public | docs_fts_gin  | index | app_user | docs      | permanent   | gin           | 845 MB |
(1 row)

Significado: El índice GIN ocupa 845 MB. Eso puede estar bien—o puede ser síntoma de bloat, dependiendo del tamaño de la tabla y el churn.

Decisión: Compara el crecimiento del índice en el tiempo. Si crece más rápido que la tabla, investiga vacuum y patrones de actualización.

Task 7 (PostgreSQL): Inspeccionar la salud de autovacuum/vacuum para la tabla FTS

cr0x@server:~$ psql -d appdb -c "SELECT relname,n_live_tup,n_dead_tup,last_autovacuum,last_vacuum FROM pg_stat_user_tables WHERE relname IN ('docs');"
 relname | n_live_tup | n_dead_tup |     last_autovacuum     |        last_vacuum
---------+------------+------------+-------------------------+-------------------------
 docs    |    2100342 |     482991 | 2025-12-28 09:42:11+00  |
(1 row)

Significado: Casi medio millón de dead tuples. Eso es riesgo de backlog de vacuum, y para FTS respaldado por GIN puede traducirse en peor latencia de consultas y mayores índices.

Decisión: Ajusta autovacuum para esta tabla (umbrales más bajos) y considera reducir el churn de actualizaciones en el texto indexado.

Task 8 (PostgreSQL): Ver si tu consulta usa GIN y cuán costosa es

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tenant_id=42 AND fts @@ websearch_to_tsquery('english','incident response') ORDER BY updated_at DESC LIMIT 20;"
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2312.44..2312.49 rows=20 width=16) (actual time=118.230..118.244 rows=20 loops=1)
   Buffers: shared hit=18543 read=412
   ->  Sort  (cost=2312.44..2320.12 rows=3071 width=16) (actual time=118.228..118.237 rows=20 loops=1)
         Sort Key: updated_at DESC
         Sort Method: top-N heapsort  Memory: 34kB
         ->  Bitmap Heap Scan on docs  (cost=122.50..2231.64 rows=3071 width=16) (actual time=24.911..113.775 rows=5208 loops=1)
               Recheck Cond: (fts @@ websearch_to_tsquery('english'::regconfig, 'incident response'::text))
               Filter: (tenant_id = 42)
               Buffers: shared hit=18512 read=412
               ->  Bitmap Index Scan on docs_fts_gin  (cost=0.00..121.73 rows=16347 width=0) (actual time=23.021..23.022 rows=16221 loops=1)
                     Index Cond: (fts @@ websearch_to_tsquery('english'::regconfig, 'incident response'::text))
 Planning Time: 1.112 ms
 Execution Time: 118.410 ms
(13 rows)

Significado: Se usa GIN, pero el filtrado por inquilino se aplica después del bitmap heap scan, es decir, recuperas muchas coincidencias y luego descartas la mayoría. Los buffers muestran trabajo sustancial.

Decisión: Considera una estrategia compuesta: añade un índice parcial por inquilino (si el número de inquilinos es pequeño), o almacena tenant_id en la identidad del documento y particiona por inquilino, o mantiene un fts separado por shard de inquilino.

Task 9 (PostgreSQL): Confirmar configuración de búsqueda de texto y comportamiento de stemming

cr0x@server:~$ psql -d appdb -c "SHOW default_text_search_config;"
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

Significado: La configuración por defecto es English. Si estás indexando contenido multilingüe, esto es un riesgo para relevancia y corrección.

Decisión: Elige configuraciones por idioma de documento, o usa simple para tokens no lingüísticos. No finjas que una configuración de stemming sirve para todo.

Task 10 (PostgreSQL): Validar qué tokens se indexan realmente

cr0x@server:~$ psql -d appdb -c "SELECT to_tsvector('english','Running runners ran easily') AS v;"
                     v
-------------------------------------------
 'easili':4 'ran':3 'run':1,2
(1 row)

Significado: “Running” y “runners” se normalizan a “run”, “easily” a “easili”. Por eso el stemming puede ayudar o perjudicar.

Decisión: Si el stemming rompe términos de dominio, cambia de configuración o mantiene campos separados con diccionarios simple para códigos/nombres.

Task 11 (PostgreSQL): Atrapar construcción “insegura” de tsquery antes de que sea una página

cr0x@server:~$ psql -d appdb -c "SELECT to_tsquery('english','foo:bar');"
ERROR:  syntax error in tsquery: "foo:bar"

Significado: La entrada de usuario puede generar errores de sintaxis si usas to_tsquery directamente.

Decisión: Usa plainto_tsquery o websearch_to_tsquery para la entrada de usuario. Trata to_tsquery crudo como API interna.

Task 12 (System): Identificar si la búsqueda está limitada por CPU o por E/S en el host de la base de datos

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  0      0  81244  52120 912340    0    0   210   180  790 1460 45 12 40  3  0
 6  1      0  65010  52080 910120    0    0  4520  1100 1200 2400 32 10 35 23  0
 7  2      0  64122  51990 905332    0    0  6120  1450 1350 2600 28 11 33 28  0
 5  1      0  63200  51920 900110    0    0  5900  1500 1300 2500 29 12 34 25  0
 4  0      0  62910  51860 899820    0    0  4800  1200 1150 2300 31 11 37 21  0

Significado: wa (IO wait) salta a 20–28%. Eso sugiere que el almacenamiento es un cuello de botella, no solo la CPU. Las consultas de texto completo a menudo se convierten en cargas de “leer muchas páginas” cuando la selectividad es baja.

Decisión: Si IO wait es alto, prioriza la tasa de aciertos de caché, la selectividad del índice y el rendimiento del almacenamiento. Si la CPU está al máximo con bajo IO wait, enfócate en planes de consulta, costes de tokenización y límites de concurrencia.

Task 13 (PostgreSQL): Encontrar las declaraciones que consumen más tiempo (incluyendo búsqueda)

cr0x@server:~$ psql -d appdb -c "SELECT calls, total_exec_time::bigint AS ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;"
 calls |   ms   | mean_ms | rows |                              query
-------+--------+---------+------+-------------------------------------------------------------------
  8123 | 984221 | 121.21  |  0   | SELECT id FROM docs WHERE tenant_id=$1 AND fts @@ websearch_to_tsquery('english',$2) ORDER BY updated_at DESC LIMIT 20
  1102 | 312110 | 283.22  |  1   | UPDATE docs SET body=$1, fts=to_tsvector('english',$1) WHERE id=$2
   989 | 221004 | 223.45  |  1   | SELECT count(*) FROM docs WHERE fts @@ plainto_tsquery('english',$1)
(3 rows)

Significado: Tus principales consumidores incluyen tanto lecturas de búsqueda como escrituras que actualizan FTS. Ese es el perfil clásico de “la función de búsqueda lo consume todo”.

Decisión: Decide si (a) reducir la frecuencia de actualizaciones de campos indexados, (b) mover actualizaciones de vector fuera del camino de la petición, o (c) mover la búsqueda a un sistema separado.

Task 14 (MySQL): Confirmar si tu consulta FULLTEXT devuelve baja selectividad (demasiadas coincidencias)

cr0x@server:~$ mysql -e "SELECT COUNT(*) AS matches FROM articles WHERE MATCH(title, body) AGAINST('status' IN NATURAL LANGUAGE MODE);"
+---------+
| matches |
+---------+
| 182344  |
+---------+

Significado: Un término muy común coincide con un gran porcentaje de documentos. Eso es baja selectividad; genera trabajo intenso, problemas de ordenación y fallos de caché.

Decisión: Añade filtros, exige términos adicionales, ajusta stopwords o cambia la UX a búsquedas con ámbito (por ejemplo, dentro de un proyecto/inquilino/rango de fechas).

Guion de diagnóstico rápido

Cuando la búsqueda se pone lenta, no tienes tiempo para un debate filosófico sobre motores. Necesitas encontrar el cuello de botella en minutos.

Primero: ¿es plan de consulta, saturación de recursos o sesgo de datos?

• Revisa saturación del sistema: CPU vs IO wait. Si IO wait es alto, estás leyendo demasiado del disco o el almacenamiento está luchando. Si la CPU está alta con bajo IO wait, estás haciendo ranking/parsing costoso o demasiada concurrencia.
• Revisa el plan de consulta: ¿Estás usando el índice FTS? ¿Estás ordenando conjuntos candidatos enormes? ¿Se aplican los filtros pronto o tarde?
• Revisa la selectividad: ¿Búsquedas con términos comunes que devuelven conjuntos enormes? Eso no es “rendimiento”, es matemática.

Segundo: separa el dolor de “lectura de búsqueda” del dolor de “escritura de índice”

• Dolor de lectura: selects lentos, misses de buffer, grandes bitmap scans, filesorts, tablas temporales, muchas filas examinadas.
• Dolor de escritura: actualizaciones lentas, lag de replicación, picos de WAL/binlog, autovacuum/vacuum atrasado, contención de bloqueos alrededor del mantenimiento.

Tercero: decide la estrategia de contención

• Contener ahora: rate-limit a la búsqueda, añadir longitud mínima de consulta, deshabilitar ordenaciones costosas, limitar resultados, devolver resultados parciales.
• Arreglar después: añadir o corregir índices, cambiar la estructura de la consulta, afinar vacuum/autovacuum, reescribir la estrategia de tokenización.
• Rearquitecturar: si la búsqueda es un gran consumidor de recursos, aíslala: réplicas de lectura para búsqueda, base de datos dedicada o motor de búsqueda externo.

Errores comunes: síntoma → causa raíz → solución

1) “La búsqueda no devuelve nada para términos cortos”

Síntoma: Códigos de producto de dos letras o nombres cortos nunca coinciden.

Causa raíz: innodb_ft_min_token_size en MySQL demasiado alto, stopwords activadas; configuración de stemming/tokenización de Postgres inadecuada para códigos.

Solución: Para MySQL, ajusta el tamaño de token y reconstruye índices (planifica downtime/mantenimiento). Para Postgres, indexa códigos por separado con B-tree o usa la configuración simple en campos dedicados.

2) “La búsqueda se volvió lenta después de añadir ORDER BY updated_at”

Síntoma: La consulta usa índice FTS pero la latencia sube y CPU/E/S aumentan.

Causa raíz: Ordenar grandes conjuntos candidatos; filesort/tablas temporales en MySQL; sort después de bitmap heap scan en Postgres.

Solución: Cambia la UX (ordenar por rank en lugar de updated_at), usa recuperación en dos pasos o pre-calcula “documentos recientes” por inquilino y busca dentro de esa ventana.

3) “Postgres FTS de repente es errático y luego se queda mal”

Síntoma: Picos de latencia periódicos; tamaño de índice crece; vacuum parece atrasado.

Causa raíz: Bloat de índice GIN + vacuum atrasado; actualizaciones intensas en campos de texto indexados.

Solución: Ajusta autovacuum por tabla; considera reducir el churn, agrupar actualizaciones o reconstruir índices durante mantenimiento. Si el churn es negocio-requerido, aisla la carga de búsqueda.

4) “Algunas búsquedas de usuario devuelven error en Postgres”

Síntoma: Un subconjunto de consultas 500 con errores de sintaxis tsquery.

Causa raíz: Usar to_tsquery directamente con entrada de usuario.

Solución: Reemplaza por websearch_to_tsquery (mejor UX) o plainto_tsquery. Registra tokens rechazados, no bloquees la operación.

5) “La búsqueda en MySQL se siente inconsistente entre entornos”

Síntoma: Staging devuelve resultados; producción no, o el scoring difiere.

Causa raíz: Diferencias en listas de stopwords, tamaño de token, collation/charset o versiones/engine distintas.

Solución: Estandariza la configuración de MySQL entre entornos; establece collations/charsets explícitos; trata la lista de stopwords como configuración que pruebas.

6) “La búsqueda ralentiza todo lo demás en la base de datos”

Síntoma: Cuando aumenta el tráfico de búsqueda, endpoints no relacionados se vuelven más lentos.

Causa raíz: CPU/E/S/caché compartidos; escaneos y ranking de texto completo compiten con carga OLTP; efectos de cola.

Solución: Limita la concurrencia de búsqueda; mueve lecturas de búsqueda a réplicas; aísla en una base de datos o servicio separado cuando se convierta en contribuyente principal.

7) “Los resultados parecen tontos: documentos comunes dominan”

Síntoma: Documentos largos o spammy siempre encabezan los resultados.

Causa raíz: Ranking no afinado; pesos iguales; normalización no alineada con tu dominio; campos de texto de baja calidad incluidos.

Solución: Pondera el título más que el cuerpo; excluye boilerplate; almacena campos estructurados por separado; en Postgres usa vectores ponderados y normalización de ranking; en MySQL considera restricciones en modo booleano o filtros adicionales.

8) “La búsqueda multiinquilino es lenta incluso con índices”

Síntoma: El filtro por inquilino está presente pero la consulta aún lee volúmenes enormes.

Causa raíz: El índice FTS no incorpora tenant_id, así que obtienes coincidencias de todos los inquilinos y luego filtras.

Solución: Particiona por inquilino, usa tablas separadas por inquilino (si es factible) o mantiene índices/índices parciales por inquilino; como mínimo, rediseña para buscar dentro de un subconjunto acotado por inquilino primero.

Listas de verificación / plan paso a paso

Paso a paso: desplegar FTS incorporado sin odiar a tu yo futuro

1. Escribe el “contrato de búsqueda”: operadores soportados, longitud mínima de consulta, idiomas soportados, reglas de ranking y qué significa “frase”. Si no puedes describirlo, los usuarios lo descubrirán por las malas.
2. Elige límites de alcance: búsqueda solo por inquilino, ventanas de fecha, tipos de documento. Añade filtros que reduzcan conjuntos candidatos pronto.
3. Decide tokenización y configuraciones de idioma (Postgres) o stopwords/tamaños de token (MySQL). Hazlo explícito en la gestión de configuración.
4. Indexa solo lo que importa: no metas cada blob de texto en el vector/índice. Trata el texto indexado como un centro de costo.
5. Define “construcción segura de consultas”: nunca parsees la entrada de usuario como sintaxis sin escapar/traducir. Postgres: prefiere websearch_to_tsquery. MySQL: sanitiza operadores booleanos si los expones.
6. Prueba con consultas adversarias: palabras comunes, resultados vacíos, puntuación, Unicode, cadenas extremadamente largas, “copiar/pegar desde Word”.
7. Instala guardarraíles: límites de petición, timeouts, circuit breakers y respuestas “lo siento, afina tu búsqueda”.
8. Monitorea un canario y sigue P95, lecturas de buffers y lag de replicación. Las fallas de búsqueda suelen aparecer como degradación gradual.
9. Planifica tu salida: documenta cómo replicarías documentos a un sistema de búsqueda dedicado. Aunque no lo uses, la disciplina de diseño ayuda.

Checklist: decidir entre MySQL y Postgres para FTS incorporado

• Si necesitas flexibilidad y relevancia tunable: Postgres gana. Puedes ponderar campos, elegir diccionarios y construir comportamientos de búsqueda más estructurados.
• Si quieres pocas piezas móviles y aceptas scoring simple: MySQL FULLTEXT puede estar bien, especialmente para búsqueda de palabras clave directa con restricciones.
• Si tienes alto churn de escrituras en texto indexado: ten cuidado con ambos; mantenimiento GIN en Postgres y actualizaciones de índices auxiliares en MySQL duelen. Considera patrones de indexado asíncrono.
• Si el multilenguaje importa: Postgres suele ser más fácil de configurar correctamente—si realmente lo haces.
• Si necesitas semántica tipo “búsqueda web”: websearch_to_tsquery en Postgres es una ventaja práctica para entradas de usuario.

Checklist: señales de que deberías moverte a un motor de búsqueda dedicado

• La búsqueda está entre los 3 principales consumidores de CPU o E/S de la BD.
• Necesitas coincidencia difusa, sinónimos, autocompletar, resaltado, analizadores por campo o ranking híbrido léxico+vectorial.
• Estás añadiendo reglas de relevancia por inquilino y tu esquema empieza a parecerse a un archivo de configuración de motor de búsqueda.
• Incidentes implican repetidamente “el tráfico de búsqueda dejó la BD triste”.
• Tu organización puede operar otro sistema con estado sin tratarlo como una mascota.

Preguntas frecuentes

1) ¿FULLTEXT de MySQL es “malo”?

No. Es opinado y limitado. Es excelente para búsqueda de palabras clave simples con restricciones. Se vuelve doloroso cuando necesitas relevancia sofisticada o cuando la carga de búsqueda compite con carga OLTP.

2) ¿La búsqueda de texto completo de PostgreSQL reemplaza a Elasticsearch/OpenSearch?

A veces, para funciones de búsqueda internas o moderadas. Si necesitas tolerancia a errores tipográficos, analizadores ricos, facetado rápido a escala o pipelines de ranking avanzados, extrañarás un motor dedicado pronto.

3) ¿Por qué mis búsquedas en Postgres se comportan raro con puntuación y caracteres especiales?

Porque tsquery tiene sintaxis. Si construyes tsquery directamente desde la entrada de usuario, la puntuación puede convertirse en operadores o errores de sintaxis. Usa websearch_to_tsquery o plainto_tsquery.

4) ¿Por qué es tan caro “ORDER BY updated_at” con FTS?

Porque FTS devuelve un conjunto de documentos coincidentes; ordenar por un atributo distinto a menudo requiere ordenar un gran conjunto candidato. Si el conjunto de coincidencia es grande, ordenas mucho. Considera ordenar por rank, prefiltrar o recuperación en dos pasos.

5) ¿Cómo mejoro el ranking en Postgres?

Separa campos (título, cuerpo, tags), asigna pesos y usa vectores ponderados. También considera eliminar texto boilerplate del campo indexado. El ranking es tanto higiene de datos como matemática.

6) ¿Cuál es la mejor forma de manejar búsqueda multiinquilino?

Acota la búsqueda al inquilino primero. Para Postgres, considera particionar por inquilino o índices parciales cuando el número de inquilinos sea manejable. Para MySQL, asegura que los filtros por inquilino sean selectivos y evita búsquedas globales que luego filtran.

7) ¿Debo almacenar tsvector como columna generada?

Las columnas generadas son convenientes, pero ponen el coste de indexado en el camino de escritura. Si tienes mucho churn, considera actualizaciones asíncronas o actualizaciones por lotes, según tus necesidades de consistencia.

8) ¿Cómo sé si el problema es el índice o la consulta?

Mira el plan y los buffers/filas examinadas. Si se usa el índice FTS pero aún así lees mucho, probablemente tienes baja selectividad o una ordenación/filtrado costoso. Si el índice no se usa, es construcción de consulta o estadísticas del planificador.

9) ¿Puedo hacer búsqueda de frase con FTS incorporado?

Postgres soporta búsqueda de frase vía phraseto_tsquery y características relacionadas, pero no es idéntico a “coincidencia exacta de subcadena”. MySQL en modo booleano tiene operadores pero el comportamiento de frase no es equivalente al de motores con indexado posicional afinado para resaltado.

10) ¿Cuál es el mayor riesgo operativo con FTS incorporado?

La carga de búsqueda es explosiva y guiada por usuarios. Convierte tu base de datos primaria en una piscina de cómputo compartida para consultas impredecibles, y así es como los endpoints no relacionados reciben daño colateral.

Próximos pasos prácticos

Si ya ejecutas búsqueda de texto completo incorporada, tu trabajo es evitar que silenciosamente se convierta en el mayor consumidor de tu base de datos.

1. Añade un canario de búsqueda (una consulta, un panel, una alerta). Monitorea latencia, filas examinadas y lecturas de buffer en el tiempo.
2. Implementa guardarraíles: longitud mínima de consulta, límites de tasa, timeouts y una ventana máxima de resultados sensata.
3. Ejecuta las tareas de diagnóstico anteriores en una ventana tranquila y registra líneas base: tamaños de índices, estadísticas de vacuum, consultas lentas, formas de plan.
4. Decide tu estrategia de contención: réplicas para lecturas de búsqueda o aislar la búsqueda en un servicio separado cuando se convierta en un gran consumidor de recursos.
5. Haz de la relevancia una decisión de producto, no un resultado accidental de valores por defecto. Si necesitas más que pesos y tokenización, deja de negociar con tu base de datos y presupuesta un motor de búsqueda dedicado.

La búsqueda incorporada puede ser una herramienta afilada. Solo no la uses como martillo para un problema que claramente requiere una pistola de clavos.