Aprende a diseñar una búsqueda global en PostgreSQL para pantallas internas eligiendo full-text, índices trigram y índices parciales para obtener resultados rápidos.
En una pantalla interna, “búsqueda en todas partes” suele significar: “Ayúdame a encontrar el registro exacto que tengo en mente, rápido, aunque no lo recuerde perfectamente.” La gente no está navegando; intenta saltar directamente a un cliente, ticket, factura o dispositivo.
Por eso una búsqueda lenta se siente peor que una página lenta. Una página se carga una vez. La búsqueda se ejecuta muchas veces seguidas, a menudo mientras alguien está en una llamada o haciendo triage. Si los resultados tardan 2–3 segundos, los usuarios cambian la consulta, borran, prueban otro término y terminas con más carga y más frustración.
Desde una sola caja de búsqueda, los usuarios esperan un conjunto de comportamientos: coincidencias parciales ("alex" encuentra "Alexander"), tolerancia a pequeños errores ("microsfot" aún encuentra "Microsoft"), un orden razonable de “mejor resultado” (IDs o correos exactos arriba), un sesgo por recencia y filtros que se aplican por defecto (tickets abiertos, clientes activos).
Lo complicado es que una entrada suele ocultar múltiples intenciones. Un agente puede pegar un número de ticket, escribir un fragmento de nombre, buscar un correo o introducir un teléfono. Cada intención quiere una estrategia distinta, distintos índices y a veces una regla de ranking diferente.
Así que no empieces por los índices. Empieza listando las pocas intenciones de búsqueda que tus usuarios realmente tienen, y separa campos de identidad (IDs, emails) de campos difusos (nombres, asuntos) y texto largo (notas).
Antes de elegir un índice, anota lo que la gente realmente escribe. “PostgreSQL search everywhere” suena a una sola característica, pero normalmente es una mezcla de búsquedas muy diferentes.
Las herramientas internas mezclan identificadores “duros” (order ID, número de ticket, código de factura) con texto “blando” (nombre del cliente, email, notas, tags). Esos grupos se comportan distinto en PostgreSQL, así que tratarlos igual es un camino rápido hacia consultas lentas.
A continuación, separa los comportamientos:
TCK-104883 espera un resultado preciso.john smth quiere una coincidencia tolerante en nombres (y quizá emails) y revisará una lista corta.Decide pronto si los resultados deben estar ordenados por relevancia (mejores coincidencias primero) o simplemente filtrados. El ranking importa para notas y descripciones largas. Para IDs y correos, el ranking suele sentirse aleatorio y añade coste.
Una lista corta suele bastar:
Si nombras esas decisiones desde el inicio, elegir índices dejará de ser un juego de adivinanzas.
ILIKE suele empeorar las cosasAsegura las victorias fáciles primero. Para muchas pantallas internas, un índice B-tree simple ya da resultados instantáneos para coincidencias exactas como IDs, números de orden, correos y referencias externas.
Si la gente pega un valor exacto, asegúrate de que la consulta sea realmente exacta. WHERE id = ... o WHERE email = ... pueden ser extremadamente rápidas con un índice normal. Un índice único en email suele merecer la pena dos veces: velocidad y mejor calidad de datos.
El problema aparece cuando “búsqueda en todas partes” se transforma en ILIKE. Una consulta como name ILIKE '%ann%' tiene un wildcard al inicio, así que PostgreSQL no puede usar un índice B-tree normal. Acaba comprobando muchas filas y se vuelve más lenta a medida que la tabla crece.
La búsqueda por prefijo puede funcionar, pero solo cuando el patrón está anclado al inicio: name ILIKE 'ann%'. Aun así importan los detalles (collation, manejo de mayúsculas y si indexaste la misma expresión que consultas). Si tu UI debe ser insensible a mayúsculas, un enfoque común es consultar lower(name) y crear un índice coincidente sobre lower(name).
También ayuda ponerse objetivos claros sobre qué significa “ágil”:
Con objetivos así, es más fácil decidir si te quedas con exacto y prefijo, o si es momento de full-text o trigram.
La búsqueda de texto completo encaja mejor cuando la gente escribe lenguaje natural y espera que el sistema encuentre los ítems correctos, no solo coincidencias exactas. Piensa en mensajes de tickets, notas internas, descripciones largas, artículos de KB y registros de llamadas.
La gran ventaja es el ranking. En vez de devolver una larga lista donde el mejor resultado está oculto, full-text puede ordenar por relevancia. En herramientas internas eso importa: alguien necesita una respuesta en segundos, no después de revisar 50 filas.
A alto nivel, full-text tiene tres partes móviles:
tsvector (el texto indexable, almacenado o generado)tsquery (lo que el usuario escribió, convertido a consulta)La configuración de idioma es donde el comportamiento se vuelve visible. PostgreSQL elimina stop words comunes (como “el” o “y”) y aplica stemming, por eso “pay”, “paid” y “payment” pueden coincidir. Eso es genial para notas y mensajes, pero puede sorprender cuando alguien busca una palabra corta y común y no obtiene nada.
Los sinónimos son otro punto a decidir. Ayudan cuando tu compañía usa palabras diferentes para lo mismo (por ejemplo, “refund” vs “chargeback”), pero requieren mantenimiento. Mantén la lista de sinónimos corta y basada en lo que soporte u ops realmente escriben.
Un ejemplo práctico: buscar “can’t login after reset” debería traer tickets donde el mensaje dice “cannot log in after password reset”, aunque la redacción difiera. Ese comportamiento de “encontrar lo relevante” es para lo que está pensado full-text, y suele ser mejor que intentar que ILIKE actúe como motor de búsqueda.
Los índices trigram son una buena elección cuando los usuarios escriben fragmentos, cometen errores tipográficos o solo recuerdan “algo parecido”. Brillan en campos de texto cortos donde full-text es demasiado estricto: nombres de personas, nombres de empresas, asuntos de tickets, SKUs, números de pedido y códigos de producto.
Un trigram es un trozo de texto de 3 caracteres. PostgreSQL compara dos cadenas por cuántos trigramas comparten. Por eso puede emparejar "Jon Smth" con "John Smith", o "ACM" con "ACME", y encontrar resultados cuando la consulta aparece en medio de una palabra.
A menudo es la vía más rápida hacia una caja de “búsqueda en todo” tolerante cuando la tarea es “encuéntrame la fila correcta”, no “encuentra documentos sobre un tema”.
Full-text es excelente para texto largo y ranking por significado, pero no maneja de forma natural cadenas parciales y pequeños errores en campos cortos. La búsqueda por trigram está construida para ese tipo de difusividad.
Los índices trigram son más grandes y añaden sobrecarga en escrituras, así que sé selectivo. Indexa las columnas que la gente realmente usa:
Si puedes nombrar los campos exactos que tu equipo escribe en la caja de búsqueda, normalmente puedes mantener el índice trigram pequeño y rápido.
Una caja de “búsqueda en todas partes” suele tener defaults ocultos. La gente busca dentro de un workspace, en items activos y con eliminados excluidos. Si esos filtros están presentes en casi cada petición, acelera el caso común indexando solo las filas que los cumplen.
Un índice parcial es un índice normal con una cláusula WHERE. PostgreSQL lo mantiene más pequeño porque solo almacena entradas para las filas que te importan más. Eso suele significar menos páginas que leer y mejores ratios de acierto en caché.
Objetivos comunes de índices parciales: filas activas (status = 'active'), borrados lógicos (deleted_at IS NULL), scope por tenant y ventanas “recientes” (por ejemplo, últimos 90 días).
La clave es que coincida con tu UI. Si la pantalla siempre oculta filas borradas, tus consultas deberían siempre incluir deleted_at IS NULL, y tu índice parcial debería usar la misma condición. Desajustes pequeños, como usar is_deleted = false en un sitio y deleted_at IS NULL en otro, pueden impedir que el planner use el índice.
Los índices parciales también funcionan junto a full-text y trigram. Por ejemplo, indexar búsqueda de texto solo para filas no borradas mantiene el tamaño del índice bajo control.
Compensación: los índices parciales ayudan menos en consultas raras. Si alguien busca ocasionalmente en registros borrados o en todos los workspaces, PostgreSQL puede usar un plan más lento. Maneja eso con una ruta separada solo para administradores, o añade un segundo índice solo si la consulta rara se vuelve común.
La mayoría de equipos acaban mezclando técnicas porque una caja de búsqueda tiene que atender distintas intenciones. El objetivo es dejar claro el orden de operaciones para que los resultados se sientan previsibles.
Un orden de prioridad simple ayuda, ya sea implementado como consultas separadas o como una consulta con lógica CASE clara.
Empieza estricto y ve relajando según sea necesario:
Si te apegas a la misma escalera, los usuarios aprenden qué “significa” la caja. Dejan de pensar que el sistema está roto cuando “12345” encuentra un ticket al instante mientras “refund policy” busca en texto más largo.
La búsqueda difusa se vuelve cara cuando tiene que considerar la tabla completa. Reduce el conjunto candidato con los filtros que la gente realmente usa (estado, equipo asignado, rango de fechas, cuenta) y luego ejecuta trigram o full-text sobre lo que queda. Incluso un índice trigram rápido puede sentirse lento si le pides puntuar millones de filas.
También vale la pena escribir una regla de una frase que compañeros no técnicos entiendan, como: “Coincidimos número de ticket exactamente, luego nombre de cliente con tolerancia a errores, luego buscamos en notas.” Esa definición compartida evita discusiones sobre por qué apareció una fila.
Una caja de búsqueda rápida es un conjunto de pequeñas decisiones. Escríbelas primero y el trabajo en la base de datos se simplifica.
EXPLAIN (ANALYZE, BUFFERS).Para desplegar con seguridad, cambia una cosa a la vez y mantén fácil la reversión. Para índices nuevos en tablas grandes, prefiere CREATE INDEX CONCURRENTLY para no bloquear escrituras. Si puedes, despliega detrás de un feature flag y compara latencias antes y después.
Un patrón práctico para “búsqueda PostgreSQL en todas partes” es: coincidencia exacta primero (rápida y precisa), trigram para campos “humanos” donde la gente se equivoca, y full-text para texto largo que se beneficia del ranking.
Imagina un panel de soporte donde el equipo tiene una sola caja de búsqueda, pero espera encontrar clientes, tickets e incluso notas. Este es el problema clásico de “una entrada, muchos significados”.
La primera mejora es hacer la intención visible sin añadir fricción. Si la consulta parece un correo o un teléfono, trátala como búsqueda de cliente. Si parece un ID de ticket (por ejemplo, "TKT-10482"), enrútalo directamente a tickets. Todo lo demás vuelve a búsqueda de texto en asunto y notas del ticket.
Para búsqueda de clientes, los índices trigram suelen funcionar mejor. Nombres y empresas son desordenados y la gente escribe fragmentos. Un índice trigram puede hacer rápidas búsquedas como “jon smi” o “acm”.
Para notas de tickets, usa full-text. Las notas son oraciones reales y normalmente quieres coincidencias relevantes, no “contiene esta subcadena”. El ranking ayuda cuando docenas de tickets mencionan la misma palabra.
Los filtros importan más de lo que la mayoría espera. Si los agentes viven en “tickets abiertos”, añade un índice parcial que cubra solo filas abiertas. Haz lo mismo para “clientes activos”. Mantiene los índices pequeños y acelera el camino común.
Consultas muy cortas merecen reglas, de lo contrario la BD hace trabajo caro por ruido:
La mayoría de bugs de “¿por qué la búsqueda es lenta?” son auto-infligidos. La meta no es indexarlo todo, sino indexar lo que la gente realmente hace.
Una trampa común es añadir índices en muchas columnas “por si acaso”. Las lecturas mejoran, pero cada insert y update ahora hace más trabajo. En herramientas internas donde los registros cambian todo el día (tickets, órdenes, usuarios), la velocidad de escritura importa.
Otro error es usar full-text cuando lo que necesitas es búsqueda tolerante a errores en nombres o correos. Full-text es genial para documentos y descripciones. No es la solución mágica para “Jon” vs “John” o “gmail.con” vs “gmail.com”. Eso suele ser territorio trigram.
Los filtros también pueden romper tu plan silenciosamente. Si la mayoría de búsquedas incluyen un filtro fijo (como status = 'open' u org_id = 42), el mejor índice puede ser un índice parcial que coincida con esa condición. Si olvidas esto, PostgreSQL puede escanear muchas más filas de las esperadas.
Unos cuantos errores que se repiten:
Ejemplo: una pantalla de soporte busca tickets por asunto, nombre del cliente y número de ticket, luego ordena por última actividad. Si latest_activity_at no está indexado para el subconjunto filtrado (por ejemplo, tickets abiertos), ese sort puede eliminar la velocidad ganada por el índice de búsqueda.
Antes de dar por terminada la función de “búsqueda en todas partes”, concreta el comportamiento que prometes.
Si mezclas modos, decide cuál gana cuando confligen.
Luego identifica las 2–3 columnas que mueven la mayoría de búsquedas. Si el 80% de las búsquedas son por email, nombre y ticket ID, optimiza eso primero y trata lo demás como secundario.
Una breve checklist antes del envío:
Finalmente, prueba con tamaños de datos y tiempos realistas, no solo con corrección. Una consulta que se siente instantánea con 1.000 filas puede arrastrarse con 1.000.000.
Una caja de búsqueda se mantiene rápida cuando el equipo acuerda qué debe hacer. Escribe las reglas en lenguaje llano: qué significa “coincidir” (exacto, prefijo, tolerante a errores), qué campos se buscan y cómo los filtros cambian el conjunto de resultados.
Mantén un conjunto pequeño de pruebas con búsquedas reales y trátalo como una suite de regresión. Diez a veinte consultas suelen bastar: un par de nombres comunes, algunos emails parciales, un typo, un fragmento largo de nota y un caso de “sin resultados”. Ejecútalas antes y después de cambios para que el trabajo de rendimiento no rompa silenciosamente la relevancia.
Si construyes herramientas internas con AppMaster (appmaster.io), ayuda definir esas reglas de búsqueda junto al modelo de datos y la lógica de negocio, para que el comportamiento de la UI y las decisiones de la base de datos no diverjan a medida que cambien los requisitos.
¿Qué significa “búsqueda en todas partes” en una herramienta interna?Trátalo como “encuéntrame rápidamente el registro exacto que tengo en mente”, no como navegación. Empieza escribiendo las pocas intenciones reales que tienen los usuarios (búsqueda por ID, búsqueda por nombre/correo con tolerancia a errores, búsqueda en notas largas) y los filtros por defecto que suelen aplicar. Esas decisiones te dicen qué consultas ejecutar y qué índices merecen la pena.
¿Por qué `ILIKE '%...%'` hace que la búsqueda sea lenta?ILIKE '%term%' tiene un wildcard al inicio, por lo que PostgreSQL normalmente no puede usar un índice B-tree y acaba escaneando muchas filas. En tablas pequeñas puede parecer rápido, pero va empeorando a medida que crece la data. Si necesitas búsqueda por subcadena o tolerancia a errores, planifica usar trigram o full-text en vez de depender de ILIKE.
¿Cuál es la forma más rápida de manejar búsquedas exactas como IDs o correos?Usa comparaciones exactas como WHERE id = $1 o WHERE email = $1 respaldadas por un índice B-tree (a menudo único para emails o códigos). Las búsquedas exactas son las más baratas y además hacen que los resultados sean previsibles. Si un usuario pega un número de ticket o un correo completo, enrútalo primero a esta vía.
¿Cómo hago búsqueda por prefijo insensible a mayúsculas sin romper índices?Prefiere un patrón de prefijo como name ILIKE 'ann%' y mantén la consistencia con cómo creas el índice. Para un comportamiento insensible a mayúsculas fiable, muchos equipos usan lower(name) en la consulta y crean un índice sobre la misma expresión para que el planner pueda aprovecharlo. Si no puedes anclar el patrón al inicio, la búsqueda por prefijo no bastará.
¿Cuándo debo usar índices trigram para una caja de búsqueda?Usa índices trigram cuando los usuarios escriben fragmentos, cometen typos pequeños o solo recuerdan “algo parecido”, especialmente en campos cortos como nombres, asuntos, códigos o usernames. Los trigramas funcionan bien para coincidencias en medio de la cadena y para errores ortográficos. Sé selectivo con las columnas porque los índices trigram son más grandes y añaden coste en escrituras.
¿Cuándo es mejor usar la búsqueda de texto completo de PostgreSQL?Usa full-text cuando las personas buscan frases o palabras en contenidos largos como notas, mensajes, descripciones o artículos tipo knowledge-base. Su gran ventaja es el ranking por relevancia, que coloca los mejores resultados arriba en vez de obligar a revisar muchas filas. Ten en cuenta el comportamiento de idioma —stemming y stop-words—: útil para prosa, pero puede sorprender en búsquedas muy cortas.
¿Cómo ayudan los índices parciales en pantallas de “búsqueda en todas partes"?Añade índices parciales cuando la mayoría de búsquedas incluyen los mismos filtros, por ejemplo deleted_at IS NULL, status = 'open' o una restricción de tenant/espacio. Como el índice solo cubre el subconjunto común, se mantiene más pequeño y suele ser más rápido. Asegúrate de que las consultas usen exactamente la misma condición que el índice parcial, o PostgreSQL podría ignorarlo.
¿Cómo combinar búsqueda exacta, trigram y full-text sin confundir a los usuarios?Usa una escalera de prioridad consistente para que los resultados sean estables: coincidencia exacta primero para IDs/emails, luego prefijo donde encaje, después trigram para nombres/títulos tolerantes a errores y por último full-text para notas largas y descripciones. Aplica los filtros por defecto al principio para reducir el conjunto que la búsqueda difusa debe considerar. Así la relevancia y el rendimiento no se volverán aleatorios al crecer los datos.
¿Qué hago con búsquedas de 1–2 caracteres o con input vacío?Define reglas simples como requerir 3+ caracteres antes de ejecutar búsqueda difusa y usa consultas cortas para mostrar registros recientes o frecuentemente usados. Entradas muy cortas generan mucho ruido y pueden disparar trabajo caro con poco valor. También decide qué mostrar con input vacío para que la UI no golpee la BD con consultas “matchea todo”.
¿Cómo valido el rendimiento y entrego cambios de búsqueda de forma segura?Crea el índice y luego verifica la consulta real con EXPLAIN (ANALYZE, BUFFERS) en datos de tamaño real, no solo en un dataset de desarrollo. Despliega cambios uno por uno y mantén fácil la reversión; en tablas grandes crea índices con CREATE INDEX CONCURRENTLY para no bloquear escrituras. Si construyes la pantalla en AppMaster (appmaster.io), define las reglas de búsqueda junto al modelo de datos y la lógica de negocio para que el comportamiento UI no se desvíe con el tiempo.
21 ene 2026
8 min
20 ene 20268 min
20 ene 20268 minExperimente con AppMaster con plan gratuito.
Cuando esté listo, puede elegir la suscripción adecuada.
