Búsqueda de texto completo en PostgreSQL vs SQLite: cuándo SQLite te sorprende (y cuándo no)

El marco de decisión: qué estás eligiendo realmente

“PostgreSQL vs SQLite” es la pregunta equivocada. La pregunta es:
¿dónde quieres que viva tu complejidad?
SQLite sitúa la complejidad en el proceso de la aplicación y en el sistema de archivos. PostgreSQL la pone en un servidor con perillas, introspección y límites claros.

Para la búsqueda de texto completo (FTS), la decisión real depende de:

• Modelo de concurrencia: SQLite es excelente hasta que el contendio de escrituras se vuelve tu personalidad. PostgreSQL está diseñado para escrituras concurrentes.
• Ordenamiento y lingüística: ambos pueden ser “suficientemente buenos”, pero PostgreSQL te da más control estructurado; SQLite te da velocidad y portabilidad.
• Visibilidad operativa: Postgres tiene estadísticas maduras, planes de consulta e instrumentación a nivel de servidor. SQLite requiere que seas tu propio DBA.
• Ciclo de vida de los datos: copias de seguridad, replicación, manejo de corrupción y evolución de esquemas se ven diferente cuando la base de datos es un archivo.
• Contención de fallos: un escritor SQLite bloqueado puede detener tu app. Una consulta de Postgres atascada puede matarse sin matar todo el proceso.

Guía con opinión:
Si tu app es de nodo único, con pocas escrituras y quieres simplicidad embebida, SQLite FTS5 es una elección legítima.
Si necesitas concurrencia entre varios escritores, mantenimiento en línea y una introspección robusta, usa PostgreSQL y no te disculpes por ello.

Una cita que ha quedado con la gente de operaciones durante décadas, y aplica aquí:
“La esperanza no es una estrategia.” — Vince Lombardi

Hechos e historia que importan más que los benchmarks

Los benchmarks son divertidos hasta que se convierten en compras. Aquí tienes puntos de contexto concretos que afectan sistemas reales.
(Esta es la parte donde alguien dice “pero mi prueba en laptop…” y cierras la pestaña en silencio.)

1. SQLite es anterior a muchas pilas “enterprise”. Comenzó alrededor del 2000, diseñado para fiabilidad y embebido sin administración.
2. FTS de SQLite no es una sola cosa. FTS3/4 vinieron antes; FTS5 es más nuevo y tiene mejor extensibilidad y funciones como ranking BM25 integrado.
3. La búsqueda de texto de PostgreSQL se lanzó mucho antes de que estuviera de moda. tsearch2 existió como módulo externo a principios de los 2000; luego se integró en el núcleo de PostgreSQL.
4. La restricción de concurrencia de SQLite es arquitectónica. No es que sea “lenta”; es que un único archivo de base de datos tiene semántica de bloqueo que puede convertirse en cuello de botella para escritores.
5. El modo WAL fue un gran avance para SQLite. Mejoró la concurrencia lectura/escritura al separar el write-ahead log del archivo principal de la base de datos.
6. El índice GIN de PostgreSQL hizo práctica la búsqueda de texto a escala. GIN está diseñado para tipos de datos “contienen muchas claves”, como términos en tsvector.
7. Ambos sistemas tokenizan; ninguno es “Google”. Tendrás que gestionar stemming, stopwords y sinónimos por tu cuenta, y de todos modos te culparán.
8. SQLite es famoso por sus pruebas. Su suite de tests es intensa y de larga duración; es una razón por la que se confía en sistemas embebidos.
9. PostgreSQL es una plataforma, no solo una base de datos. Extensiones, diccionarios personalizados y trabajos del lado servidor cambian cómo puede verse la “búsqueda de texto completo”.

Broma #1 (corta, relevante): La búsqueda de texto es donde producto descubre que el lenguaje es ambiguo, y ingeniería descubre que el producto también lo es.

Cómo funcionan realmente SQLite FTS5 y PostgreSQL tsearch

SQLite FTS5: índice invertido que vive en un archivo

FTS5 almacena un índice invertido: términos → listas de IDs de fila (y posiciones, según opciones).
La “tabla” que consultas no es una tabla normal; es una tabla virtual respaldada por estructuras de índice FTS.
Escribes filas, tokeniza el texto usando un tokenizador (como unicode61), y actualiza el índice.

Lo bueno: es rápido, portátil y viaja con tu app.
Lo picante: el mantenimiento del índice puede ser costoso en escrituras, y si configuras mal el journaling o el checkpointing, las latencias finales aparecen en el peor lugar posible: las peticiones de usuario.

PostgreSQL tsearch: lexemas, diccionarios e índices

La búsqueda de texto en PostgreSQL gira en torno a tsvector (el documento como términos normalizados con posiciones) y tsquery (la consulta).
Puedes construir un tsvector a partir de columnas al vuelo, pero en producción normalmente lo almacenas (columna generada o trigger) y lo indexas con GIN.

Postgres añade estructura: configuraciones, diccionarios, stopwords y ponderación.
Esa estructura lo hace más controlable para búsquedas multilingües o específicas de dominio, pero también te da más formas de equivocarte al configurarlo.

Rankeo: estilo BM25 vs ts_rank

SQLite FTS5 incluye una función de ranking BM25; no es magia, pero está razonablemente alineada con lo que la gente espera de una búsqueda.
PostgreSQL provee ts_rank y afines; con pesos y normalización puedes obtener buenos resultados, pero lo afinarás.

Traducción: si quieres “ordenamiento decente desde el primer momento”, SQLite puede sorprenderte.
Si quieres “un ranking que puedas explicar a tu equipo legal”, PostgreSQL te da más palancas.

Cuándo SQLite te sorprende (en el buen sentido)

1) Búsqueda embebida con bajo overhead operativo

Despliegas un único binario (o contenedor) y un único archivo de base de datos.
No necesitas un servidor DB, pooling de conexiones, migraciones entre nodos ni descubrimiento de servicios.
Para herramientas internas, aplicaciones de escritorio, dispositivos edge y despliegues single-tenant, SQLite FTS5 es un regalo.

2) Cargas mayoritariamente de lectura con datos locales

Si tu patrón de acceso es principalmente lecturas y escrituras por lotes ocasionales, SQLite en modo WAL puede ser muy rápido.
I/O local, sin salto de red, sin contención de servidor y un motor de consultas maduro.

3) Corpus pequeño a mediano con patrones de consulta sensatos

Para decenas de miles hasta algunos millones de documentos (según tamaño de documentos y hardware), SQLite FTS5 puede sentirse “demasiado rápido para ser real”.
Es real. También es fácil superarlo si añades:
actualizaciones frecuentes, múltiples escritores o alta concurrencia de consultas bajo un régimen de bloqueo por archivo.

4) Indexado portátil y entornos de pruebas reproducibles

Enviar un conjunto de datos reproducible para pruebas de integración es más sencillo cuando es un archivo.
Si alguna vez intentaste depurar diferencias de ranking entre staging y prod por deriva en la configuración de Postgres, sabes por qué esto importa.

Cuándo SQLite no te sorprende (y duele de todos modos)

Concurrencia: el punto de ahogo del escritor

SQLite puede hacer lecturas concurrentes con WAL, pero las escrituras siguen serializándose.
Para FTS, las escrituras no son “inserciones baratas”; también actualizan el índice. Bajo carga de escritura sostenida, verás esperas de bloqueo y picos de latencia final.

Si necesitas múltiples instancias de la app escribiendo en la misma BD, te espera una conversación difícil con la física.
Sí, puedes poner el archivo en un sistema de archivos de red. No, no deberías, a menos que disfrutes del comportamiento indefinido como hobby.

Control operativo e introspección

Postgres te da pg_stat_statements, logs de consultas lentas, EXPLAIN (ANALYZE, BUFFERS), comportamiento de vacuum en background y más.
SQLite te ofrece pragmas y el rastreo que armes. Eso está bien—hasta que tu comandante de incidentes pregunta “¿qué cambió?”

Actualizaciones calentadas y comportamiento de merge

Las actualizaciones frecuentes al mismo conjunto de documentos pueden fragmentar las estructuras de índice FTS e incrementar el trabajo de merge.
Si no programas mantenimiento (o lo programas en hora pico), puedes crear un DoS autoinfligido.

Pecados de corrección: tokenización y locale

“¿Por qué ‘résumé’ no coincide con ‘resume’?” no es una pregunta filosófica; es una pregunta de configuración del tokenizador.
Los tokenizadores de SQLite son configurables pero no infinitos. Las configuraciones de búsqueda de texto de PostgreSQL pueden ser más matizadas, especialmente con diccionarios personalizados.

Broma #2 (corta, relevante): la concurrencia de SQLite es como una cafetería pequeña: el espresso es genial, pero solo un barista puede espumar la leche a la vez.

Qué te da PostgreSQL que SQLite no dará

Concurrencia multiusuario fuerte y aislamiento

PostgreSQL está diseñado para muchas sesiones haciendo lecturas y escrituras a la vez, con MVCC para evitar que lectores y escritores se pisen.
Si ejecutas búsqueda en una app web con múltiples workers y jobs en background, esto importa de inmediato.

Opciones de indexado y planificación de consultas predecible

Con PostgreSQL puedes:

• Persistir tsvector e indexarlo con GIN.
• Usar índices parciales para limitar la búsqueda a documentos “activos”.
• Usar columnas generadas para evitar triggers.
• Configurar GIN (como fastupdate) y monitorizar bloat.

Mejor tooling para “el día después del lanzamiento”

La búsqueda de texto se vuelve política después del lanzamiento.
Alguien pedirá “¿por qué este resultado está encima de este otro?” o “¿podemos excluir docs internas?”
La visibilidad de PostgreSQL hacia planes, estadísticas y mantenimiento en background facilita depurar sin conjeturas.

Pero: Postgres tampoco es un motor de búsqueda

Si necesitas coincidencia difusa, tolerancia a faltas de tipeo, sinónimos a escala, analítica en tiempo de consulta o indexado distribuido, te diriges hacia un sistema de búsqueda dedicado.
Usa SQLite/Postgres FTS cuando quieras búsqueda nativa en la base de datos, no una segunda pila de infraestructura.

Tareas prácticas: comandos, salidas y decisiones

Estas son tareas reales de “qué hago a las 2 a.m.”. Cada una incluye un comando, salida representativa, qué significa y la decisión que tomas.
Los comandos son ejecutables; ajusta rutas, nombres de base de datos y usuarios a tu entorno.

Tarea 1: Confirmar el modo de journal de SQLite (WAL o no)

cr0x@server:~$ sqlite3 app.db "PRAGMA journal_mode;"
wal

Qué significa: wal habilita mejor concurrencia lectura/escritura que delete o truncate.

Decisión: Si no está en wal y tienes lecturas concurrentes durante escrituras, cambia a WAL y vuelve a medir la latencia final.

Tarea 2: Comprobar busy timeout de SQLite (evitar “database is locked” inmediato)

cr0x@server:~$ sqlite3 app.db "PRAGMA busy_timeout;"
0

Qué significa: 0 significa que las llamadas fallan inmediatamente por contención de bloqueo.

Decisión: Establece un timeout distinto de cero en la conexión de la aplicación (o PRAGMA) si se espera contención breve; de lo contrario arregla la contención de escritores en la raíz.

Tarea 3: Verificar que existe la tabla FTS5 y que es realmente FTS5

cr0x@server:~$ sqlite3 app.db ".schema docs_fts"
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content='docs', content_rowid='id', tokenize = 'unicode61');

Qué significa: Es una tabla virtual FTS5 con contenido externo.

Decisión: Si usaste content externo, asegúrate de que los triggers o procesos de reconstrucción la mantengan sincronizada, o servirás resultados desactualizados/vacíos.

Tarea 4: Ejecutar una comprobación de integridad FTS5

cr0x@server:~$ sqlite3 app.db "INSERT INTO docs_fts(docs_fts) VALUES('integrity-check');"

Qué significa: Normalmente la ausencia de salida indica que pasó. Errores indican corrupción de índice o desajuste.

Decisión: Si falla, planea una reconstrucción (rebuild) y examina problemas de sistema de archivos/I/O y patrones de terminación abrupta de procesos.

Tarea 5: Reconstruir un índice FTS5 con contenido externo (mantenimiento planificado)

cr0x@server:~$ sqlite3 app.db "INSERT INTO docs_fts(docs_fts) VALUES('rebuild');"

Qué significa: FTS5 reconstruye el índice desde la tabla de contenido.

Decisión: Programa esto fuera de horas pico; si el tiempo de reconstrucción es inaceptable, probablemente ya es hora de Postgres (o un motor de búsqueda).

Tarea 6: Ver si SQLite está haciendo checkpoints WAL agresivamente (o nada)

cr0x@server:~$ sqlite3 app.db "PRAGMA wal_checkpoint(TRUNCATE);"
0|0|0

Qué significa: La salida es busy|log|checkpointed. Todos ceros significa que no hay frames pendientes.

Decisión: Si log crece mucho en producción, añade checkpointing controlado (a nivel app) y monitoriza la latencia de I/O.

Tarea 7: Inspeccionar tamaño de página y supuestos de cache de SQLite

cr0x@server:~$ sqlite3 app.db "PRAGMA page_size; PRAGMA cache_size;"
4096
-2000

Qué significa: Páginas de 4KB; cache_size negativo significa unidades KB (aquí ~2.000KB).

Decisión: Si tu carga es de lectura intensiva y estás thrasheando disco, aumenta cache_size en la conexión de la app, pero valida el impacto de memoria por proceso.

Tarea 8: Identificar errores “database is locked” en logs de la aplicación

cr0x@server:~$ journalctl -u app.service --since "1 hour ago" | grep -i "database is locked" | tail
Dec 30 01:12:40 host app[2219]: sqlite error: database is locked (SQLITE_BUSY) on INSERT INTO docs_fts ...
Dec 30 01:12:41 host app[2219]: sqlite error: database is locked (SQLITE_BUSY) on UPDATE docs SET ...

Qué significa: Tienes contención de escritores o transacciones largas reteniendo bloqueos.

Decisión: Acorta transacciones, mueve las actualizaciones de indexado a una cola de escritor único, o deja de fingir que el archivo es un servidor de BD multi-writer.

Tarea 9: Encontrar archivos WAL grandes (síntoma de checkpointing o ráfagas de escritura)

cr0x@server:~$ ls -lh app.db app.db-wal app.db-shm
-rw-r--r-- 1 app app  12G Dec 30 01:10 app.db
-rw-r--r-- 1 app app 3.8G Dec 30 01:12 app.db-wal
-rw-r--r-- 1 app app  32K Dec 30 00:55 app.db-shm

Qué significa: WAL es enorme; el coste de recuperación/checkpoint y la presión en disco están por venir.

Decisión: Investiga lectores largos que impiden checkpoints, añade checkpoints periódicos y verifica que el disco tenga margen; de lo contrario “descubrirás” ENOSPC durante el tráfico pico.

Tarea 10: PostgreSQL: confirmar tu configuración de búsqueda de texto

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

Qué significa: Tu parseo/stemming por defecto es inglés.

Decisión: Si tienes contenido multilingüe, no dejes esto por defecto y esperanza. Elige configuración por documento o almacena múltiples vectores.

Tarea 11: PostgreSQL: ver si tienes el tipo de índice correcto para tsvector

cr0x@server:~$ psql -d app -c "\d+ docs"
                                                   Table "public.docs"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | bigint  |           | not null |         | plain    |             |              |
 title  | text    |           |          |         | extended |             |              |
 body   | text    |           |          |         | extended |             |              |
 tsv    | tsvector|           |          |         | extended |             |              |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)
    "docs_tsv_gin" gin (tsv)

Qué significa: Tienes un índice GIN en tsv. Bien.

Decisión: Si falta este índice, añádelo antes de culpar a Postgres de ser “lento”.

Tarea 12: PostgreSQL: inspeccionar el plan real para una consulta de búsqueda

cr0x@server:~$ psql -d app -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tsv @@ plainto_tsquery('english','refund policy') ORDER BY ts_rank(tsv, plainto_tsquery('english','refund policy')) DESC LIMIT 20;"
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..35.71 rows=20 width=8) (actual time=4.112..4.168 rows=20 loops=1)
   Buffers: shared hit=812
   ->  Index Scan using docs_tsv_gin on docs  (cost=0.42..271.08 rows=152 width=8) (actual time=4.110..4.156 rows=20 loops=1)
         Index Cond: (tsv @@ plainto_tsquery('english'::regconfig, 'refund policy'::text))
 Planning Time: 0.214 ms
 Execution Time: 4.242 ms
(6 rows)

Qué significa: Está usando el índice GIN; los buffers son hits (en caché). 4ms es saludable.

Decisión: Si ves un sequential scan, probablemente faltó el índice, usaste una función que impide el uso del índice, o la consulta tiene selectividad muy baja.

Tarea 13: PostgreSQL: comprobar si autovacuum está al día (el bloat también afecta a GIN)

cr0x@server:~$ psql -d app -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
 relname | n_dead_tup |        last_autovacuum
---------+------------+-------------------------------
 docs    |     842113 | 2025-12-30 00:41:12+00
 events  |      12044 | 2025-12-29 23:58:06+00
(2 rows)

Qué significa: Muchas tuplas muertas en docs; actualizaciones/eliminaciones se están acumulando.

Decisión: Ajusta autovacuum para esa tabla, reduce la tasa de actualización, o acepta que los índices de búsqueda se inflarán y se volverán más lentos con el tiempo.

Tarea 14: PostgreSQL: encontrar las consultas más lentas por tiempo total (si pg_stat_statements está activado)

cr0x@server:~$ psql -d app -c "SELECT calls, total_exec_time::int AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, left(query,80) AS q FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 calls | total_ms | mean_ms | rows |                                        q
-------+----------+---------+------+--------------------------------------------------------------------------------
 10213 |   912345 |   89.33 |  0   | SELECT id FROM docs WHERE tsv @@ plainto_tsquery($1,$2) ORDER BY ts_rank...
  1400 |   221100 |  157.92 |  1   | UPDATE docs SET body = $1, tsv = to_tsvector($2, $1) WHERE id = $3
(2 rows)

Qué significa: Tu consulta de búsqueda domina el tiempo total de ejecución; la ruta de actualización también es pesada.

Decisión: Decide si optimizar la consulta/ranking, cachear resultados, precomputar vectores o reducir la frecuencia de actualizaciones.

Tarea 15: Comprobar saturación de I/O en Linux (usualmente es el disco, no tu tokenizador)

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.41    0.00    6.02   21.15    0.00   60.42

Device            r/s     w/s   rkB/s   wkB/s  await  %util
nvme0n1        112.0   980.0  8200.0 65200.0  18.40  97.50

Qué significa: El disco está casi al máximo; el tiempo de espera promedio es alto. La latencia de búsqueda seguirá esta tendencia.

Decisión: Deja de afinar funciones de ranking y empieza a arreglar el I/O: almacenamiento más rápido, reducir amplificación de escrituras, agrupar escrituras, o mover la búsqueda fuera del disco caliente.

Tarea 16: Comprobar presión de descriptores de archivo (apps en proceso con SQLite pueden alcanzar límites)

cr0x@server:~$ cat /proc/$(pgrep -n app)/limits | grep "Max open files"
Max open files            1024                 1048576              files

Qué significa: El límite suave es 1024; tu app puede alcanzarlo con logs, sockets y archivos de BD.

Decisión: Aumenta límites si ves errores de I/O intermitentes; es aburrido, pero evita “problemas de base de datos” falsos.

Guion de diagnóstico rápido

Tienes búsqueda lenta o timeouts. No tienes tiempo para una tesis.
Haz esto en orden. Para cuando encuentres el cráter humeante, para.

Primero: decide si estás limitado por CPU, I/O o bloqueos

• I/O-bound: alto %iowait, %util del disco cerca del 100%, WAL en crecimiento, fsyncs lentos.
• CPU-bound: CPU de usuario alta, I/O estable, tokenización/ranking intensivos, conjuntos de resultados grandes ordenados.
• Lock-bound: “database is locked” (SQLite), transacciones de larga duración (Postgres), escritores bloqueados.

Segundo: verifica que el índice se esté usando

• SQLite: confirma que usas docs_fts MATCH ? y no LIKE '%term%' sobre la tabla de contenido.
• Postgres: EXPLAIN (ANALYZE, BUFFERS); busca uso del índice GIN, no seq scan.

Tercero: comprueba la amplificación de escritura y el mantenimiento en background

• SQLite: tamaño de WAL, frecuencia de checkpoints, comportamiento de rebuild/merge de FTS.
• Postgres: retraso de autovacuum, tuplas muertas, comportamiento de lista pendiente de GIN, bloat.

Cuarto: inspecciona la forma de la consulta y el ranking

• ¿Estás ordenando miles de coincidencias para devolver 20?
• ¿Estás usando consultas con prefijo en todas partes?
• ¿Estás calculando to_tsvector al vuelo en lugar de vector almacenado?

Quinto: decide si la arquitectura está equivocada

Si el cuello de botella es “muchos escritores” y estás en SQLite, afinar es un mecanismo de afrontamiento.
Si el cuello de botella es “la búsqueda es ahora una característica de producto con SLAs”, Postgres puede ser suficiente, pero un sistema de búsqueda dedicado puede ser inevitable.

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

1) Síntoma: picos de “database is locked” durante el pico

Causa raíz: Múltiples escritores o transacciones largas en SQLite; las actualizaciones del índice FTS amplifican las escrituras.

Solución: Patrón de escritor único (cola), acortar transacciones, establecer busy_timeout, habilitar WAL, migrar a Postgres si el multi-writer es real.

2) Síntoma: resultados de búsqueda faltan para documentos recientemente actualizados

Causa raíz: Tabla FTS con contenido externo fuera de sincronía (triggers faltantes, fallidos o deshabilitados durante una carga masiva).

Solución: Recrea triggers, ejecuta FTS5 rebuild, añade checks invariantes en CI (contar desajustes) y alerta sobre deriva.

3) Síntoma: consulta de búsqueda en Postgres usa sequential scan

Causa raíz: No hay índice GIN en tsvector, o la consulta envuelve tsvector en una función, o términos de baja selectividad.

Solución: Persiste tsvector, añade índice GIN, usa operadores correctos (@@), considera índices parciales, ajusta configuración/stopwords.

4) Síntoma: el ranking parece aleatorio o “peor que antes”

Causa raíz: Cambios en tokenizador/diccionario, cambios en lista de stopwords, mezclar idiomas o pasar de consultas de frase a bolsa de palabras.

Solución: Versiona tu configuración de búsqueda, escribe tests de regresión con consultas doradas y trata el ranking como un contrato de API.

5) Síntoma: uso de disco crece continuamente y el rendimiento degrada

Causa raíz: Bloat en Postgres (tuplas muertas), bloat en GIN, WAL de SQLite sin checkpoint, actualizaciones frecuentes de campos de texto grandes.

Solución: Ajusta autovacuum; reduce la tasa de actualizaciones; en SQLite, gestiona checkpoints; en Postgres, considera REINDEX periódicos/ventanas de mantenimiento donde proceda.

6) Síntoma: “funciona en mi máquina” pero falla en prod con texto no ASCII

Causa raíz: Diferentes reglas de tokenización, colaciones o configuraciones de búsqueda de texto entre entornos.

Solución: Fija explícitamente el tokenizador/config; crea casos de prueba para Unicode y puntuación; deja de depender de defaults.

7) Síntoma: picos de latencia cada pocos minutos

Causa raíz: Checkpointing de SQLite o ráfagas de mantenimiento FTS; autovacuum de Postgres o presión de I/O por cargas no relacionadas.

Solución: Correlaciona con logs/métricas; programa mantenimiento; aisla almacenamiento; añade jitter/backoff a escritores por lotes.

8) Síntoma: la búsqueda devuelve demasiadas coincidencias irrelevantes

Causa raíz: Construcción de consulta demasiado permisiva (muchos OR), sin ponderación de campos, sin coincidencia de frases, stopwords removidos demasiado agresivamente.

Solución: Pondera título vs cuerpo (pesos en Postgres; columnas separadas y ajustes de ranking en SQLite), añade consultas de frase para patrones comunes, afina stopwords.

Listas de verificación / plan paso a paso

Elegir SQLite FTS5: la lista “no me despierten por esto”

1. Habilita el modo WAL y verifica que permanezca habilitado entre despliegues.
2. Establece busy_timeout (o reintento a nivel app con jitter) deliberadamente.
3. Usa un patrón de escritor único si tienes escrituras frecuentes; trata las actualizaciones FTS como intensivas en escrituras.
4. Define una política de checkpoints; monitoriza el tamaño de WAL y el margen de disco.
5. Si usas contenido externo, mantiene triggers y añade una comprobación de integridad periódica.
6. Fija la configuración del tokenizador y prueba casos Unicode.
7. Tener un plan de reconstrucción (cronómetro); no descubras que la reconstrucción tarda horas durante un incidente.
8. Backups: haz snapshot del archivo DB de forma segura (y entiende qué significa “seguro” en modo WAL).

Elegir PostgreSQL tsearch: la lista “hazlo aburrido”

1. Persiste tsvector (columna generada o trigger) y añade un índice GIN.
2. Elige default_text_search_config intencionalmente; no dejes contenido multilingüe al azar.
3. Usa EXPLAIN (ANALYZE, BUFFERS) para confirmar uso de índices antes de afinar cualquier otra cosa.
4. Activa pg_stat_statements para que puedas encontrar las consultas realmente caras.
5. Ajusta autovacuum para las tablas que churnean; vigila tuplas muertas y bloat.
6. Decide tu función de ranking y consérvala detrás de una interfaz estable.
7. Planifica migraciones (cambios de configuración, cambios de diccionario) como despliegues versionados.

Migrar de SQLite FTS a PostgreSQL tsearch: paso a paso

1. Inventaria el comportamiento de tokenización (qué coincide hoy) y escribe una suite de regresión de consultas y resultados top esperados.
2. Exporta documentos e IDs; conserva identificadores estables para comparar resultados entre sistemas.
3. En Postgres, crea tsvector con la configuración más cercana; indexalo con GIN.
4. Ejecuta una comparación offline: recall/precision para tus consultas de regresión, no benchmarks sintéticos.
5. Despliega doble escritura o indexado asíncrono; no bloquees escrituras de usuario por el indexado durante la migración.
6. Vuelve lecturas gradualmente; monitoriza latencia y quejas de relevancia por separado.
7. Retira la búsqueda en SQLite solo después de poder reconstruir índices de Postgres y recuperarte de fallos rápidamente.

Tres micro-historias corporativas desde el campo

Incidente: la suposición equivocada (“SQLite es solo un archivo, así que será más rápido”)

Un equipo SaaS mediano lanzó una búsqueda de documentos embebida en el proceso principal de la app usando SQLite FTS5.
Era para reducir dependencias: no más cluster Postgres, no servicio de búsqueda, no nueva rotación de on-call.
El primer mes fue bien. El dataset era modesto y la mayoría de las escrituras venían de una importación nocturna.

Luego añadieron notas generadas por usuarios con auto-guardado. Las escrituras se volvieron constantes.
Asumieron que el modo WAL significaba “básicamente concurrente”, y escalaron la app horizontalmente. Cada instancia escribía en el mismo archivo de base de datos en almacenamiento compartido.
Los síntomas fueron clásicos: timeouts aleatorios, “database is locked” ocasional, y gráficas de CPU que parecían tranquilas mientras los usuarios gritaban.

El incidente no fue un crash único. Fue un colapso en cámara lenta.
Un escritor retuvo un bloqueo más tiempo de lo esperado, los demás se acumularon, hilos de petición se amontonaron y la app dejó de servir tráfico mucho antes de que la base de datos “fallara”.
Aprendieron por las malas que un límite de bloqueo por archivo no es un modelo de concurrencia distribuido.

La solución no fue ingeniosa. Movieron la búsqueda a Postgres en una instancia dedicada, pusieron el trabajo de indexado en cola y mantuvieron SQLite para desarrollo local y pruebas.
El cambio más valioso fue social: documentaron las suposiciones de concurrencia en el design doc para que no volviera a pasar.

Optimización que salió mal: “Reconstruyamos el índice cada noche para mantenerlo rápido”

Otro equipo tenía un gran índice SQLite FTS5 y notó que la latencia de consultas subía con el tiempo.
Alguien propuso una reconstrucción nocturna completa de la tabla FTS. Funcionó en staging, era fácil de automatizar y hizo que las gráficas se vieran bien por exactamente un día.
Lo desplegaron con confianza y una invitación de calendario.

La reconstrucción se ejecutó en el mismo host que la app. Saturó I/O de disco por una hora, luego dos.
Las consultas de lectura se volvieron más lentas durante la reconstrucción, así que los usuarios reintentaron, generando más carga. El trabajo se superpuso con el tráfico matutino porque existen zonas horarias.
Soporte abrió tickets. Ingeniería culpó a la red. El equipo de red culpó a DNS, como manda la tradición.

El fracaso no fue que reconstruir sea “malo”. Fue que reconstruir es un evento de I/O.
El equipo trataba el archivo de la base de datos como una caché interna, pero se había convertido en una característica primaria visible al usuario.
Necesitaban mantenimiento controlado, no un reinicio a fuerza bruta.

La solución eventual: dejaron de reconstruir y en su lugar gestionaron patrones de escritura, checkpointing y salud del índice.
Para un churn mayor se mudaron a Postgres, donde podían reindexar de forma más aislada dentro de ventanas y aislar mejor el I/O.
La lección fue aburrida: no programes una tormenta de disco junto a tus clientes.

Práctica tediosa pero correcta: “Probamos la relevancia como probamos APIs”

Una tercera compañía usaba Postgres full-text search para documentos de una base de conocimiento interna.
Nada sofisticado: tsvector, índice GIN y un puñado de ajustes de ranking.
Su arma secreta no fue la tecnología. Fue la disciplina.

Mantenían un pequeño corpus de “consultas doradas” con resultados top esperados.
Cada cambio a diccionarios, configuraciones o pesos de ranking pasaba por CI y producía un informe de diferencias.
Los ingenieros aprendieron rápido que “tweak menor” puede significar “aluvión de tickets de soporte”.

Un día, una actualización rutinaria de paquetes del SO cambió un comportamiento relacionado con colaciones en staging, que luego modificó resultados de tokenización de forma sutil.
La suite de regresión lo detectó. Bloquearon el despliegue e investigaron.
No fue un outage dramático. Fue un no-evento silencioso, el mejor tipo.

Cuando llegó el momento de añadir otro idioma, no discutieron por sentimientos. Añadieron vectores por idioma y ampliaron el conjunto dorado.
Les costó algo de almacenamiento y tiempo.
Les salvó de semanas de reuniones “por qué la búsqueda está peor” donde todos tienen razón y nadie es útil.

Preguntas frecuentes

1) ¿SQLite FTS5 es “suficiente” para producción?

Sí, si eres nodo único (o escritor único), mayormente lectura y tratas el mantenimiento (WAL, checkpoints, reconstrucciones) como una preocupación operativa real.
Falla de forma predecible cuando violas sus suposiciones de concurrencia.

2) ¿La búsqueda de texto en PostgreSQL es más lenta que SQLite?

No inherentemente. SQLite puede sentirse más rápido porque es embebido y evita saltos de red.
Postgres puede ser extremadamente rápido con tsvector persistido + GIN, pero debes confirmar el uso de índices y mantener autovacuum sano.

3) ¿Debo calcular to_tsvector en tiempo de consulta en Postgres?

Casi nunca para una carga real. Persiste el vector (columna generada o trigger), indexalo y mantén las consultas lo suficientemente simples para que el planner use el índice.

4) ¿Puede SQLite manejar múltiples instancias de app leyendo y escribiendo?

Lecturas: sí, especialmente con WAL. Escrituras: se serializan. Si tienes escrituras frecuentes desde múltiples instancias, encontrarás contención de bloqueo y picos de latencia.
Usa una cola de escritor único o migra a una base de datos servidor.

5) ¿Por qué obtengo resultados diferentes entre entornos?

Deriva de tokenizador/config. Los tokenizadores de SQLite y las configuraciones de búsqueda de Postgres son defaults hasta que no lo son.
Fija configuraciones, versiona y prueba Unicode y puntuación explícitamente.

6) ¿Cómo mejoro la relevancia sin construir toda una pila de búsqueda?

Empieza con ponderación de campos (title > body), consultas de frase para patrones comunes y ajuste de stopwords.
Luego añade una suite de regresión con consultas doradas para que la relevancia no retroceda en silencio.

7) ¿Cuándo dejo de afinar y cambio de SQLite a Postgres?

Cuando el cuello de botella es concurrencia (escritores), visibilidad operativa, o cuando ventanas de reconstrucción/mantenimiento se vuelven inaceptables.
Si la solución es “hacer que los usuarios esperen menos con esperanza”, ya perdiste.

8) ¿Cuándo dejo de usar Postgres FTS y adopto un motor de búsqueda?

Cuando necesitas tolerancia a faltas de tipeo, sinónimos, scoring complejo, facetas/aggregaciones a escala o indexado distribuido.
También cuando la búsqueda se vuelve una característica central con SLAs propios y necesitas herramientas especializadas.

9) ¿El modo WAL en SQLite hace las escrituras concurrentes?

Mejora la concurrencia lectura/escritura, no la concurrencia escritura/escritura. Un escritor sigue ganando a la vez.
WAL evita principalmente que los lectores bloqueen a los escritores.

10) ¿GIN es siempre el índice correcto para búsqueda de texto en Postgres?

Usualmente sí. GiST existe y puede ser útil en algunos casos, pero GIN es la elección estándar para consultas de contención de tsvector.
Valida con EXPLAIN, no con superstición.

Siguientes pasos prácticos

Si vas a decidir hoy:

• Elige SQLite FTS5 si puedes comprometerte con un modelo de escritor único, WAL, checkpointing explícito y disciplina de backups basados en archivos.
• Elige PostgreSQL tsearch si necesitas concurrencia, introspección, operaciones predecibles y estás dispuesto a gestionar un servidor de base de datos.

Si ya estás sufriendo:

1. Ejecuta el guion de diagnóstico rápido. Identifica si estás limitado por bloqueo, I/O o CPU.
2. Demuestra uso de índice (SQLite: MATCH; Postgres: EXPLAIN (ANALYZE, BUFFERS)).
3. Estabiliza el mantenimiento: checkpoints WAL (SQLite), autovacuum/bloat (Postgres).
4. Bloquea configuraciones y añade tests de regresión con consultas doradas. La relevancia es un contrato, no una sensación.
5. Si la concurrencia es el factor limitante, deja de negociar con la física del bloqueo por archivo y mueve la carga a Postgres.

La versión madura de “elección de base de datos” es elegir qué incidentes quieres depurar.
SQLite te sorprende por hasta dónde puede llegar. PostgreSQL te sorprende por cuánto puedes controlar.
Elige según las sorpresas que puedas permitirte.