Búsqueda de texto completo desde PostgreSQL 8.3

Bienvenidos a la tercera – y última – parte de esta serie de blogs, que explora cómo ha evolucionado el rendimiento de PostgreSQL a lo largo de los años. En la primera parte analizamos las cargas de trabajo OLTP, sometiéndolas a la prueba de pgbench. La segunda parte se enfocó en las consultas analíticas y de inteligencia empresarial, utilizando un subconjunto de la tradicional prueba de rendimiento TPC-H (esencialmente una porción del test de potencia).

Esta parte final, en cambio, se centrará en la búsqueda de texto completo, es decir, la capacidad de indexar y realizar búsquedas en grandes cantidades de datos de texto. La misma infraestructura (en particular los índices) puede ser útil para indexar datos semi-estructurados como los documentos JSONB, etc., aunque no es ese el objetivo de esta prueba de rendimiento.

Pero primero, echemos un vistazo a la historia de la búsqueda de texto completo en PostgreSQL, una característica que puede parecer extraña en un sistema de gestión de bases de datos relacionales, tradicionalmente destinado a almacenar datos estructurados en filas y columnas.

La historia de la búsqueda de texto completo

Cuando en 1996 Postgres fue lanzado como software de código abierto, no contaba con nada que pudiéramos definir como búsqueda de texto completo. Sin embargo, quienes comenzaron a usar Postgres querían realizar búsquedas inteligentes en documentos de texto, y las consultas de tipo LIKE no eran lo suficientemente buenas. Los usuarios deseaban poder lematizar los términos usando diccionarios, ignorar las palabras vacías, ordenar los documentos por relevancia, usar índices para ejecutar esas consultas, y muchas otras cosas. Todo eso no se podía lograr con los operadores SQL tradicionales.

Afortunadamente, algunos de aquellos usuarios eran también desarrolladores. Así que empezaron a trabajar para añadir las características deseadas. Lo lograron gracias a la disponibilidad a nivel mundial de PostgreSQL como software de código abierto. A lo largo de los años, muchos contribuidores han participado en el desarrollo de la función de búsqueda de texto completo. Sin embargo, inicialmente este esfuerzo fue liderado por Oleg Bartunov y Teodor Sigaev, que aparecen en la siguiente foto. Ambos siguen siendo grandes contribuidores de PostgreSQL, que trabajan en campos como la búsqueda de texto completo, la indexación, el soporte JSON y muchas otras características.

Teodor Sigaev y Oleg Bartunov

Inicialmente, la funcionalidad fue desarrollada como un módulo «contrib» externo (hoy en día la llamaríamos extensión) denominado “tsearch”, que fue lanzado en 2002. Posteriormente, fue reemplazado por tsearch2, que mejoró considerablemente la característica. En la versión 8.3 de PostgreSQL (lanzada en 2008) la característica fue integrada en el núcleo de PostgreSQL (es decir, sin necesidad de instalar ninguna extensión, aunque se siguieron proporcionando extensiones para la compatibilidad con las versiones anteriores).

Aunque desde entonces ha habido muchas mejoras, se sigue trabajando en el soporte para tipos de datos como JSONB, las consultas basadas en jsonpath, etc. Sin embargo, fue gracias a esos plugins que se introdujo la mayor parte de la funcionalidad de texto completo actualmente disponible en PostgreSQL: diccionarios, indexación de texto completo y funcionalidades de consulta, etc.

La prueba de rendimiento

A diferencia de las pruebas OLTP / TPC-H, no tengo constancia de ninguna prueba de rendimiento para la búsqueda de texto completo considerada «estándar» o diseñada para múltiples sistemas de bases de datos. La mayoría de las pruebas de rendimiento que conozco han sido concebidas para ser usadas con una sola base de datos o producto, y es difícil adaptarlas de manera adecuada. Por lo tanto, tuve que seguir un camino diferente y crear mi propia prueba de rendimiento para el texto completo.

Hace unos años escribí archie – un par de scripts en python que permiten descargar los archivos de la lista de correos de PostgreSQL, y cargar los mensajes analizados en una base de datos PostgreSQL que luego puede ser indexada y consultada. La instantánea actual de todos los archivos contiene alrededor de 1 millón de filas. Tras cargarla en una base de datos, la tabla ocupa unos 9,5 GB (sin contar los índices).

En cuanto a las consultas, probablemente podría generarlas de manera aleatoria, pero no estoy seguro de cuán realista sería el resultado. Afortunadamente, hace un par de años obtuve una muestra de 33k de búsquedas realizadas en el sitio web de PostgreSQL (cosas que la gente buscó en los archivos de la comunidad). Es poco probable que pueda conseguir algo más realista o representativo.

La combinación de esas dos partes (conjunto de datos y consultas) parece ser un buen marco para una prueba de rendimiento. Podemos simplemente cargar los datos, y realizar las búsquedas con diferentes tipos de consultas de texto completo y distintos tipos de índices.

Las consultas

Existen varios tipos de consultas de texto completo: las que pueden seleccionar únicamente las filas que coinciden, las que pueden clasificar los resultados ordenándolos por relevancia, las que devuelven sólo un pequeño número de resultados o los más relevantes, etc. Realicé pruebas de rendimiento con varios tipos de consultas, aunque en este artículo presentaré los resultados de dos consultas sencillas que, a mi parecer, reflejan muy bien el comportamiento general.

• SELECT id, subject FROM messages WHERE body_tsvector @@ $1
  
  
• SELECT id, subject FROM messages WHERE body_tsvector @@ $1
  ORDER BY ts_rank(body_tsvector, $1) DESC LIMIT 100

La primera consulta devuelve únicamente las filas que coinciden, mientras que la segunda devuelve los 100 resultados más relevantes (algo que probablemente utilizaría para las búsquedas de usuarios).

Aunque he probado otros tipos de consultas, en definitiva todas se comportaron de manera similar a uno de estos dos tipos.

Los índices

Cada mensaje contiene dos partes principales en las que podemos buscar: el asunto y el cuerpo. Cada una de ellas cuenta con una columna de tipo tsvector independiente, y con un índice distinto. Los asuntos de los mensajes son mucho más cortos que los cuerpos, por lo que los índices son lógicamente más pequeños.

PostgreSQL contiene dos tipos de índices útiles para la búsqueda de texto completo – GIN y GiST. Aunque las principales diferencias se explican en la documentación correspondiente, aquí presentamos un breve resumen:

• Los índices GIN permiten una búsqueda más rápida
• Los índices GiST son “con pérdida”.  De modo que resultan más lentos debido a la necesidad de volver a verificarlos durante las búsquedas

Solíamos afirmar que los índices GiST son más fáciles de actualizar (especialmente con muchas sesiones simultáneas), pero este dato fue eliminado de la documentación hace algún tiempo, gracias a las mejoras en el código de indexación.

Esta prueba de rendimiento no evalúa el comportamiento con las actualizaciones. Se limita a cargar la tabla sin los índices de texto completo, los genera en una sola operación y luego realiza las 33.000 consultas en los datos. Por lo tanto, basándome en esta prueba de rendimiento, no puedo hacer afirmaciones sobre la forma en que esos tipos de índices manejan las actualizaciones simultáneas. Aún así, considero que los cambios en la documentación reflejan varias mejoras realizadas recientemente en el índice GIN.

Esto también debería coincidir muy bien con el caso de uso del archivo de la lista de correo en el cual, en ocasiones, adjuntamos únicamente nuevos correos electrónicos (pocas actualizaciones, casi ninguna simultaneidad en la escritura). Sin embargo, en caso de que su aplicación haga muchas actualizaciones simultáneas, sería conveniente que realicen su propia prueba de rendimiento.

El hardware

Realicé la prueba de rendimiento utilizando las mismas dos máquinas de las pruebas anteriores. Y puesto que los resultados son casi idénticos, sólo presentaré los valores de la más pequeña, es decir…

• CPU i5-2500K (4 núcleos/hilos)
• 8GB RAM
• 6 x 100GB SSD RAID0
• kernel 5.6.15, ext4 filesystem

Ya he mencionado que el conjunto contiene casi 10 GB de datos al momento de ser cargado, por lo que excede la memoria RAM. Aún así, lo más importante para la prueba de rendimiento es que, los índices son más pequeños que la RAM.

Los resultados

OK, es hora de algunos números y gráficos. Presentaré los resultados tanto de las cargas de datos como de las consultas, primero con GIN y luego con los índices GiST.

GIN / carga de datos

En mi opinión, la carga no es particularmente interesante. En primer lugar, la mayor parte de la misma (la parte azul) no tiene nada que ver con el texto completo, porque se produce antes de la creación de los dos índices. La mayoría de este tiempo se emplea en el análisis de los mensajes, la reconstrucción de las conversaciones del correo, la actualización de la lista de respuestas, y así por el estilo. Parte de este código se implementa en los triggers PL/pgSQL, otra parte se implementa externamente a la base de datos. La única parte potencialmente relevante para el texto completo es la construcción de los tsvector, aunque es imposible determinar el tiempo empleado en eso.

Operaciones de carga de datos con una tabla e índices GIN.

La siguiente tabla presenta los datos de origen de este gráfico – los valores corresponden a la duración en segundos. La carga incluye el análisis de los archivos mbox (procedentes de un script en Python), la inserción en una tabla y varias tareas adicionales (reconstrucción de las conversaciones de un correo electrónico, etc.). El índice ASUNTO/CUERPO se refiere a la creación de un índice GIN de texto completo en las columnas de asunto/cuerpo tras la carga de los datos.

	CARGA	ÍNDICE DEL ASUNTO	ÍNDICE DEL CUERPO
8,3	2501	8	173
8.4	2540	4	78
9.0	2502	4	75
9.1	2046	4	84
9.2	2045	3	85
9.3	2049	4	85
9.4	2043	4	85
9.5	2034	4	82
9.6	2039	4	81
10	2037	4	82
11	2169	4	82
12	2164	4	79
13	2164	4	81

Se puede notar un rendimiento muy estable con una mejora considerable (alrededor del 20%) entre las versiones 9.0 y 9.1. No estoy muy seguro de qué cambio es responsable de esta mejora; nada en las notas de la versión 9.1 parece ser claramente relevante. Existe también una clara mejora en la generación de los índices GIN en la versión 8.4, lo cual reduce la duración aproximadamente a la mitad. Y eso es bueno, por supuesto. Curiosamente, tampoco en este caso se observa ninguna nota de la versión directamente relacionada con esta mejora.

Pero, ¿qué ocurre con los tamaños de los índices GIN? Existe mucha más variabilidad, al menos hasta la versión 9.4, momento en el cual el tamaño de los índices desciende de aproximadamente 1 GB a tan sólo unos 670 MB (alrededor del 30%).

Tamaño de los índices GIN en el asunto/cuerpo del mensaje. Los valores se expresan en megabytes.

La siguiente tabla muestra los tamaños de los índices GIN en el cuerpo y asunto del mensaje. Los valores se expresan en megabytes.

	CUERPO	ASUNTO
8.3	890	62
8.4	811	47
9.0	813	47
9.1	977	47
9.2	978	47
9.3	977	47
9.4	671	20
9.5	671	20
9.6	671	20
10	672	20
11	672	20
12	672	20
13	672	20

En este caso, creo que podemos afirmar con seguridad que el incremento de la velocidad se debe al siguiente elemento que aparece en las notas de la versión 9.4::

• Reducción del tamaño del índice GIN (Alexander Korotkov, Heikki Linnakangas)

Al parecer, la variabilidad de tamaño entre las versiones 8,3 y 9,1 se debe a los cambios en la lematización (la transformación de las palabras en un formato «básico»). Por ejemplo, excepto por las diferencias de tamaño, las consultas sobre esas versiones arrojan resultados un poco distintos.

GIN / consultas

A continuación analizaremos la parte principal de esta prueba: el rendimiento de la consulta. Todos los resultados aquí presentados se refieren a un solo cliente. En el post relacionado con el rendimiento OLTP, ya hemos abordado el tema de la escalabilidad en el cliente. Los resultados se aplican también a estas consultas. Además, puesto que esta máquina en concreto sólo cuenta con 4 núcleos, en cualquier caso, no llegaríamos muy lejos en términos de pruebas de escalabilidad.

SELECT id, subject FROM messages WHERE tsvector @@ $1

Primero, la búsqueda de todos los documentos que coinciden. Para las búsquedas en la columna «asunto» podemos realizar alrededor de 800 consultas por segundo (en realidad baja un poco en la versión 9.1), pero en la 9.4 se dispara repentinamente hasta 3000 consultas por segundo. Para la columna del «cuerpo» ocurre básicamente lo mismo: al principio 160 consultas, luego una caída a unas 90 consultas en la versión 9.1, seguida por un incremento a 300 en la versión 9.4.

Número de consultas por segundo en la primera consulta (búsqueda de todas las filas coincidentes).

Y de nuevo, los datos de origen – los números son el rendimiento (consultas por segundo).

	CUERPO	ASUNTO
8.3	168	848
8.4	155	774
9.0	160	816
9.1	93	712
9.2	93	675
9.3	95	692
9.4	303	2966
9.5	303	2871
9.6	310	2942
10	311	3066
11	317	3121
12	312	3085
13	320	3192

Considero que podemos afirmar con seguridad que la mejora en la versión 9.4 se debe al siguiente elemento presente en las notas de la versión::

• Mejora en la velocidad de las búsquedas GIN multi-clave (Alexander Korotkov, Heikki Linnakangas)

Así que se trata de otra mejora de los mismos dos desarrolladores (Alexander y Heikkien) que hicieron un excelente trabajo con los índices GIN en la versión 9.4 😉

SELECT id, subject FROM messages WHERE tsvector @@ $1
ORDER BY ts_rank(tsvector, $2) DESC LIMIT 100

Para la consulta que clasifica los resultados por relevancia usando ts_rank y LIMIT, el comportamiento general es casi idéntico. Por lo tanto, no considero necesario describir en detalle el gráfico.

Número de consultas por segundo en la segunda consulta (búsqueda de las filas más relevantes).

	CUERPO	ASUNTO
8.3	94	840
8.4	98	775
9.0	102	818
9.1	51	704
9.2	51	666
9.3	51	678
9.4	80	2766
9.5	81	2704
9.6	78	2750
10	78	2886
11	79	2938
12	78	2924
13	77	3028

Sin embargo, queda una inquietud: ¿por qué el rendimiento bajó entre las versiones 9.0 y 9.1? Al parecer, existe una importante disminución en el rendimiento, en un 50% para las búsquedas en el cuerpo y del 20% para las búsquedas en los asuntos de los mensajes. Aunque no tengo una explicación clara de lo que pasó, quiero hacer dos observaciones…

En primer lugar, el tamaño del índice ha cambiado. Observando el primer gráfico («GIN / tamaño del índice») y la tabla, notarán que el índice relativo al cuerpo del mensaje pasó de 813MB a unos 977MB. Se trata de un aumento significativo que podría explicar parte de la disminución. Sin embargo, el problema radica en que el índice de los asuntos no aumentó en absoluto y que las consultas se hicieron más lentas.

En segundo lugar, se puede considerar la cantidad de resultados de las consultas. Puesto que el conjunto de datos indexados es exactamente el mismo, sería razonable esperar la misma cantidad de resultados en todas las versiones de PostgreSQL, ¿verdad? Bueno, en la práctica se ve así:

Número promedio de filas devueltas para cada consulta.

	CUERPO	ASUNTO
8.3	624	26
8.4	624	26
9.0	622	26
9.1	1165	26
9.2	1165	26
9.3	1165	26
9.4	1165	26
9.5	1165	26
9.6	1165	26
10	1165	26
11	1165	26
12	1165	26
13	1165	26

Se puede notar que en la versión 9,1 el promedio de los resultados de las búsquedas en los cuerpos de los mensajes se duplica repentinamente, lo cual resulta casi perfectamente proporcional a la disminución de la velocidad. Sin embargo, el número de resultados de las búsquedas por asunto sigue siendo el mismo. No encuentro una explicación convincente para esto excepto que la indexación ha cambiado de tal manera que, aunque permite la coincidencia de más mensajes, ralentiza un poco la búsqueda. Si tienen una mejor explicación, ¡me gustaría escucharla!

GiST / carga de datos

Analizaremos ahora el otro tipo de índices de texto completo: el GiST. Estos son índices «con pérdida«, en el sentido que requieren una nueva verificación de los resultados usando los valores de la tabla. Por lo tanto, aunque el rendimiento es menor en comparación con los índices GIN, es razonable esperar aproximadamente el mismo comportamiento.

Los tiempos de carga coinciden casi perfectamente con los del GIN. Aunque los tiempos de creación de los índices son diferentes, el patrón general es el mismo: aceleración en la versión 9.1 y pequeña deceleración en la versión 11.

Operaciones de carga de datos en una tabla con índices GiST.

	CARGA	ASUNTO	CUERPO
8.3	2522	23	47
8.4	2527	23	49
9.0	2511	23	45
9.1	2054	22	46
9.2	2067	22	47
9.3	2049	23	46
9.4	2055	23	47
9.5	2038	22	45
9.6	2052	22	44
10	2029	22	49
11	2174	22	46
12	2162	22	46
13	2170	22	44

Sin embargo, el tamaño del índice se mantuvo casi constante. No se observaron mejoras del GiST similares a las del GIN en la versión 9.4, las cuales redujeron el tamaño en un 30%. Se registró un aumento en la versión 9.1, lo cual indica que en esa versión cambió la indexación del texto completo aumentando el número de palabras indexadas.

Esto es confirmado por el número promedio de resultados con el GiST que es idéntico al del GIN (con un incremento en la versión 9.1).

Tamaño de los índices GiST en el asunto/cuerpo del mensaje. Los valores se expresan en megabytes.

	CUERPO	ASUNTO
8.3	257	56
8.4	258	56
9.0	255	55
9.1	312	55
9.2	303	55
9.3	298	55
9.4	298	55
9.5	294	55
9.6	297	55
10	300	55
11	300	55
12	300	55
13	295	55

GiST / consultas

Lamentablemente, los resultados de las consultas no son tan buenos como los del GIN, cuyo rendimiento se triplicó en la versión 9.4. De hecho, con los índices GiST, observamos una constante degradación a lo largo del tiempo.

SELECT id, subject FROM messages WHERE tsvector @@ $1

Incluso si ignoramos las versiones anteriores a la 9.1 (debido a que los índices son más pequeños y devuelven más rápidamente un menor número de resultados), el rendimiento se reduce de unas 270 a 200 consultas por segundo, con una disminución considerable entre las versiones 9.2 y 9.3.

Número de consultas por segundo en la primera consulta (búsqueda de todas las filas coincidentes).

	CUERPO	ASUNTO
8.3	5	322
8.4	7	295
9.0	6	290
9.1	5	265
9.2	5	269
9.3	4	211
9.4	4	225
9.5	4	185
9.6	4	217
10	4	206
11	4	206
12	4	183
13	4	191

SELECT id, subject FROM messages WHERE tsvector @@ $1
ORDER BY ts_rank(tsvector, $2) DESC LIMIT 100

Y para las consultas con ts_rank el comportamiento es casi idéntico.

Número de consultas por segundo en la segunda consulta (búsqueda de las filas más relevantes).

	CUERPO	ASUNTO
8.3	5	323
8.4	7	291
9.0	6	288
9.1	4	264
9.2	5	270
9.3	4	207
9.4	4	224
9.5	4	181
9.6	4	216
10	4	205
11	4	205
12	4	189
13	4	195

Aunque no estoy del todo seguro de la causa, parece existir una regresión potencialmente grave en algún momento del pasado. Puede resultar interesante saber qué fue exactamente lo que ha cambiado.

Es cierto que, hasta ahora, nadie se ha quejado por esta situación. Tal vez porque la actualización a un hardware más rápido ha ocultado el problema, o porque si realmente les importa la velocidad de las búsquedas, de todas formas preferirán los índices GIN

Podemos considerarla también como una oportunidad para la optimización. Si identificamos lo que causó la regresión y logramos corregirlo, esto podría significar una aceleración del 30% para los índices GiST.

Resumen y perspectiva futura

Hasta el momento espero haberles convencido de que hubo muchas mejoras significativas desde PostgreSQL 8.3 (y en particular en la versión 9.4). No sé con qué rapidez pueden lograrse estos avances. Aún así, espero que investiguemos al menos algunas de las regresiones en GiST (incluso si los sistemas sensibles al rendimiento probablemente sigan utilizando el GIN). Oleg, Teodor y sus colegas estaban trabajando en variantes más poderosas de la indexación GIN, denominadas VODKA y RUM (¡aquí observo un interesante patrón de nombres!), que probablemente ayudarán al menos a algunos tipos de consulta.

En cualquier caso, espero ver características que amplíen las actuales funcionalidades del texto completo, o que ofrezcan un mejor soporte para los nuevos tipos de consulta (por ejemplo, los nuevos tipos de índice están diseñados para acelerar la búsqueda de frases), para los tipos de datos y los elementos introducidos por las recientes revisiones del estándar SQL (como jsonpath).