¿SQLite o PostgreSQL? ¡Es complicado!

Nos tomamos los blogs muy en serio en Twilio. Para ayudarnos a entender cuál contenido funciona bien y cuál no en nuestro blog, tenemos un panel de control que combina los metadatos que mantenemos para cada artículo, como autor, equipo, producto, fecha de publicación, etc., con información de tráfico de Google Analytics. Los usuarios pueden solicitar gráficos y tablas de manera interactiva al mismo tiempo que filtran y agrupan los datos de muchas maneras diferentes.

Elegí SQLite para la base de datos que admite este panel de control. A principios de 2021, cuando construí este sistema, parecía una opción perfecta para lo que pensé que sería una pequeña aplicación de nicho que mis compañeros de equipo y yo podemos usar para mejorar nuestros blogs. Pero, casi un año y medio después, esta aplicación rastrea el tráfico diario de cerca de 8000 artículos en los blogs de Twilio y SendGrid , con aproximadamente 6.5 millones de registros de tráfico individuales todos los días y con una base de usuarios que creció hasta más de 200 empleados.

En algún momento me di cuenta de que algunas consultas tardaban unos segundos en producir resultados, así que comencé a preguntarme si una base de datos más sólida, como PostgreSQL, proporcionaría un mejor desempeño. Tras haber declarado públicamente mi disgusto por los indicadores de rendimiento, resistí el deseo de buscar cualquier comparación en línea y, en cambio, me embarqué en una serie de experimentos para medir con precisión el rendimiento de estas dos bases de datos para los casos de uso específicos de esta aplicación. Lo que sigue es una descripción detallada de mi esfuerzo, los resultados de mis pruebas (que incluye un giro sorprendente) y mi análisis y decisión final, que terminó siendo más compleja de lo que esperaba.

Cómo portar la aplicación a PostgreSQL

El primer paso que hice fue asegurarme de que puedo reconfigurar fácilmente la aplicación para utilizar SQLite o PostgreSQL, ya que esto me permitiría ejecutar comparaciones lado a lado. Instalé PostgreSQL en mi computadora portátil Mac para poder trabajar en el desarrollo.

La adaptación del código para que sea compatible con PostgreSQL fue relativamente fácil de lograr, ya que esta aplicación utiliza SQLAlchhy, una biblioteca de ORM para Python que permite al desarrollador crear esquemas y consultas utilizando principalmente construcciones de Python independientes de la base de datos. Al configurar la URL de conexión de la base de datos, pude cambiar entre URLR postgresql:// y sqlite:// . El código Python no necesitó modificaciones, ya que SQLAlchhy manejó internamente las diferencias entre estas bases de datos. SQLAlchemy incluso creó todas las tablas e índices en mi nueva base de datos PostgreSQL, exactamente como estaban en SQLLite.

Para permitir que la aplicación funcione completamente con PostgreSQL, también necesité migrar los datos actualmente almacenados en la base de datos de SQLite. Utilicé pgloader, un script código abierto bastante completo que importa datos de varias fuentes a una base de datos PostgreSQL. Por suerte, SQLite es una de las fuentes de datos compatibles.

La manera más fácil de importar datos con pgloader es crear un archivo de configuración que defina el origen, el destino y las opciones. Esto es lo que he realizado:

La única opción que utilicé es data only, que indica a pgloader que no cree tablas o índices, sino que solamente migre los datos de la fuente a las bases de datos de destino. Esto es importante, porque quería tener el esquema de la base de datos lo más parecido posible al de SQLite One y la mejor manera de hacerlo es dejar que SQLAlchemy cree todas las tablas e índices.

Para realizar la migración de datos, se invoca pgloader con el nombre del archivo de configuración como único argumento:

Luego de un par de intentos, el proceso de importación funcionó sin ningún problema. Los primeros intentos fallidos me alertaron de un par de problemas potencialmente graves que tenía en el diseño de mi base de datos, sobre los cuales SQLite decidió no informar ni tomar ninguna precaución:

• Para los slugs del artículo, he definido una columna VARCHAR de 256 caracteres. La migración de datos falló en cuatro artículos que tenían slugs más largos que el máximo. SQLite, con su sistema de escritura poco común, no se preocupó y almacenó los slugs más largos sin quejarse. PostgreSQL tiene un estricto control de tipos de columnas, por lo que estas cuatro publicaciones no se pudieron migrar. La solución fue ampliar la columna slug a 512 caracteres y volver a intentarlo.
• Además, en relación con la escritura, una de mis tablas inicialmente tenía claves primarias enteras, pero en algún momento decidí cambiar estas claves a cadenas UUID. Cuando hice este cambio en SQLite, migré la tabla manualmente y no me di cuenta de que dejé la columna de clave primaria en esta tabla definida como integer. Una vez más, SQLite almacenó felizmente mis UUID en esta columna e incluso los indexó, pero para transferirlos a PostgreSQL necesitaba corregir el esquema y declarar correctamente estas claves como cadenas.

En este punto, con un esfuerzo relativamente bajo, pude alternar entre las bases de datos SQLite y PostgreSQL equivalentes con tan solo editar la URL de la base de datos en un archivo de configuración y reiniciando el servidor Python.

Probé manualmente algunas consultas en mi computadora portátil de desarrollador y sentí que todo fue más rápido con la base de datos de PostgreSQL, lo cual fue una primera impresión motivadora.

Creación de una prueba de rendimiento

Mi siguiente tarea fue crear una prueba que se pueda automatizar y repetir, de modo que pueda probar el sistema en ambas bases de datos y determinar cuál es más eficiente. Simple, ¿verdad?

Lamentablemente, diseñar una prueba que cubra todas las formas en que se pueda utilizar este sistema es muy difícil debido a la gran cantidad de posibilidades, ya que la diseñé para permitir que el usuario cree muchas consultas diferentes. Por ejemplo, permite al usuario obtener estadísticas de tráfico durante un período que puede abarcar días, semanas o incluso años, y categorizar los resultados por artículo, autor, equipo, producto, lenguaje informático, lenguaje escrito o año de la publicación. El usuario también puede dividir los resultados de tráfico por país de origen. Al recuperar artículos, el usuario puede optar por ver todas las traducciones de un artículo agrupadas con el original. Para todas las consultas, el usuario puede filtrar dentro o fuera de una sección del conjunto de datos, determinado por cualquier combinación de autores, equipos, productos, lenguajes informáticos, lenguajes escritos o fechas de publicación. Y todo esto se puede hacer para los blogs de Twilio o SendGrid o para ambos blogs en combinación.

Para evitar irme por las ramas, decidí concentrarme en los casos de uso más comunes, así que creé una lista de solicitudes de API que los representan. Esta es la lista que mencioné:

Estas direcciones URL se deben completar con argumentos de cadena de consulta adicionales. Los argumentos start y end se requieren en todas las consultas, ya que especifican el período que se solicita. Me di cuenta que esto tiene una gran influencia en los tiempos de respuesta, por lo que mi plan era probar diferentes periodos.

Hay una gran cantidad de argumentos de cadena de consulta que se pueden utilizar para implementar filtros. El argumento blog que he incluido en las URL anteriores selecciona el blog de Twilio, que es el que tiene más tráfico. Decidí no complicar mis pruebas con otros filtros porque, al revisar los registros de uso, observé que la mayoría de las consultas de los usuarios no incluyen ninguno.

Con esta lista en mano, creé una función de Python corta que se ejecuta a través de estas consultas y registra cuánto tiempo demoró cada consulta en un diccionario results :

La función test() hace una copia de la lista requests , la vuelve a ordenar de manera aleatoria y luego ejecuta la solicitud con curl como un subproceso. Utiliza la función timeit() de la biblioteca estándar de Python para medir el tiempo que tarda cada consulta en devolver una respuesta y agrega esta medición al diccionario results , con la dirección URL correspondiente.

El motivo por el que estoy aleatorizando la lista es que tengo la intención de ejecutar varias instancias de esta función en paralelo para simular clientes concurrentes. Resulta conveniente tener la función para revisar las consultas en orden aleatorio, ya que garantiza que la base de datos tendrá una variedad de consultas para trabajar en cualquier momento determinado, en lugar de recibir varias instancias de la misma consulta.

La función recibe la parte de dominio de la URL como argumento server . Quería que esto se pudiera configurar fácilmente, de modo que pueda cambiar entre mis sistemas de desarrollo y producción. También se transmite una clave de API a esta función, para que las solicitudes se puedan autenticar. Por último, los argumentos de cadena de consulta start y end requeridos se pasan y se agregan a la URL antes de realizar la solicitud.

El diccionario results utiliza las URL de la lista requests como claves. Para cada clave, almaceno un arreglo de resultados y no un solo valor, ya que esto me permitiría registrar varias ejecuciones para cada consulta y obtener un promedio.

Para completar mi script de prueba, agregué una función run_test() y un analizador de línea de comandos:

La función run_test() lanza uno o más threads, según el argumento num_threads . Todos los threads están configurados para ejecutar la función test() en paralelo. Esto simula la carga proveniente de varios clientes concurrentes, cada uno de ellos a través de la lista de direcciones URL en su propio orden aleatorio, para crear algo de imprevisibilidad.

Después de que todos los threads hayan finalizado, run_test() imprime el tiempo promedio de solicitud para cada URL de solicitud y también un promedio de todas las consultas combinadas, que es la métrica que decidí utilizar para mi análisis.

Los argumentos de la línea de comandos me permiten pasar la raíz del servidor, la clave de API y las fechas de inicio y finalización para las consultas y la concurrencia. Con estos controles, tengo la capacidad de probar una variedad de situaciones.

El script de prueba ya está listo, ¡así que es el momento de obtener algunas métricas!

Pruebas en el sistema de desarrollo

El sistema de desarrollo que utilizo es una computadora portátil Mac con 6 núcleos hyperthreaded y 16 GB de RAM. El entorno de producción para este panel de control es un servidor virtual Linode con 1 vCPU y 2 GB de RAM.

A partir de experiencias pasadas con la evaluación comparativa, sé que los resultados de un sistema rápido no siempre son los mismos que uno más lento, por lo que mi objetivo final es probar el sistema de producción y tomar decisiones según los resultados que obtengo en esa plataforma.

Pero antes de hacerlo, quería hacer una primera ronda de pruebas de “práctica” en mi computadora portátil, tanto como una manera de garantizar que el script de prueba funcionó correctamente, y también, por qué no admitirlo, porque sentía curiosidad acerca de cómo estas dos bases de datos se desempeñan en una plataforma bastante poderosa.

La metodología de análisis que decidí emplear es la siguiente: Probaría el sistema que se ejecuta en las dos bases de datos, para consultas con períodos de una semana, un mes, un trimestre y un año, y todas las consultas con fecha de inicio a partir del 1 de enero de 2021. También repetiría las pruebas con 1, 2 y 4 clientes concurrentes. Para cada prueba individual, ejecutaría el script tres veces y registraría la mejor ejecución de las tres. La métrica que usaría es el promedio total de todas las consultas.

Con este plan, terminé con 24 puntos de datos (2 bases de datos x 4 períodos de consulta x 3 niveles de concurrencia). La siguiente tabla muestra el tiempo de respuesta para PostgreSQL (en azul) y SQLite (en rojo), con un solo cliente.

Aquí está muy claro que PostgreSQL arroja resultados más rápido y es especialmente eficiente para consultas más cortas. Los tiempos de respuesta de PostgreSQL aumentan más o menos linealmente a medida que crece el período de consulta, pero, curiosamente, SQLite tuvo un tiempo de respuesta casi plano en las primeras tres pruebas y luego sufrió mucho en las últimas.

A continuación, se presentan las tablas para la misma prueba con 2 y 4 clientes concurrentes.

Creí que era interesante que cambiar la concurrencia no parece tener un gran impacto en los tiempos de respuesta para la mayoría de las pruebas, probablemente debido a los múltiples núcleos que tengo en esta computadora portátil. El único caso en el que existe una diferencia es en las consultas de un año, donde PostgreSQL parece sufrir más que SQLite proporcionalmente.

Para ayudar a entender esto, la siguiente tabla muestra que los tiempos de respuesta para PostgreSQL se ponen a prueba, en comparación con los tres niveles de concurrencia para cada uno de los períodos de consulta.

Esto confirma que para las consultas que involucran una cantidad pequeña o media de filas, una concurrencia de hasta cuatro clientes no afecta los tiempos de respuesta de gran manera. Pero a medida que aumenta la cantidad de filas involucradas, una concurrencia más alta comienza a ralentizar las cosas.

Esta es la misma tabla para SQLite:

Esto es realmente una sorpresa, ya que demuestra que SQLite puede manejar cargas concurrentes mucho mejor que PostgreSQL en este sistema. Por supuesto que los tiempos de respuesta son más largos aquí, pero es muy interesante el hecho de que la concurrencia tiene menos efecto sobre estos tiempos de respuesta.

¿La plataforma de producción mostrará resultados similares? Mi expectativa es que una sola CPU, en comparación con las 6 en mi computadora portátil, hará que la concurrencia sea un factor mucho más grande en los resultados.

Pruebas en el sistema de producción

Con un script de evaluación comparativa ahora bien probado, inicié un nuevo código Linode con un clon del sistema de producción, repetí todos los pasos para migrar la base de datos SQLite a PostgreSQL y me aseguré de que pueda, una vez más, cambiar fácilmente las bases de datos mediante la edición del archivo de configuración y el reinicio del servicio.

Para este esfuerzo de prueba, decidí ejecutar el script de prueba en mi propia computadora portátil, por lo que todas las latencias involucradas en el tráfico que pasa por Internet, el cifrado y el proxy se incluirían en los resultados. Pude haber elegido ejecutar las pruebas en el mismo host que el servicio y evitar estos costos adicionales, pero, al final, mi objetivo es mejorar el rendimiento del servicio para los usuarios y estos no tienen otra opción que pasar por todas estas capas aparentemente no relacionadas.

Esta es la tabla que obtuve al utilizar una concurrencia de uno:

Y este es un giro totalmente inesperado. ¡Las líneas se cruzan!

PostgreSQL supera a SQLite en las consultas cortas y medianas, pero tiene muchos problemas con la consulta de un año, tarda casi el doble de tiempo que SQLite. En caso de que pienses que esto debe haber sido un golpe de suerte, repetí estas pruebas varias veces y confirmé que estos resultados son realmente correctos.

A continuación, se muestran las tablas para 2 y 4 clientes en este servidor de producción.

Si bien las formas de las curvas son bastante idénticas en todos los niveles de concurrencia, solo debes observar la escala de los ejes Y para ver que, en esta plataforma, los tiempos de respuesta se ven mucho más afectados por la concurrencia que mi computadora portátil. Para las consultas de todo el año con cuatro clientes concurrentes, los tiempos de respuesta fueron extremadamente lentos, aproximadamente a los 14 y 8 segundos para PostgreSQL y SQLite, respectivamente.

¿PostgreSQL y SQLite se ven afectados igualmente por la concurrencia? Veamos estos datos por separado para averiguarlo. La siguiente tabla muestra cómo PostgreSQL se desempeñó en las cuatro longitudes de período con 1, 2 y 4 clientes concurrentes.

Esto demuestra que, para esta base de datos, las cosas se ven mucho descontroladas para las consultas grandes, pero, para consultas pequeñas y medianas, los tiempos de respuesta son relativamente estables.

A continuación, se muestra el mismo gráfico para SQLite:

Esto muestra algo diferente, ¿verdad? Con una sola CPU, la concurrencia en SQLite cumple una función incluso en las consultas de tamaño pequeño y mediano. Los tiempos de respuesta aumentan menos que de forma lineal, pero siempre aumentan, incluso en las pruebas más pequeñas.

Para ayudarme a entender mejor estas diferencias, creé otro conjunto de tablas. Para cada período de consulta que probé, los siguientes gráficos comparan las dos bases de datos en cada punto de concurrencia.

Estas tablas muestran claramente que PostgreSQL puede manejar las consultas pequeñas y medianas mucho mejor que SQLite, a tal punto que el aumento de la concurrencia tiene un efecto muy pequeño en los tiempos de respuesta, lo cual es genial. SQLite, por otro lado, se ve más afectado directamente por la concurrencia para todos los tamaños de consulta y, aunque se desempeña peor en las consultas de tamaño pequeño y mediano, termina considerablemente antes de PostgreSQL para las consultas más largas.

Ajuste para el rendimiento

Hasta ahora, no me preocupaba configurar las bases de datos, simplemente asumí que la configuración predeterminada sería adecuada. En el caso de SQLite no hay mucho que configurar, pero PostgreSQL sí tiene algunas palancas de ajuste para usar.

Después de revisar las opciones de configuración relacionadas con el uso de la memoria, encontré la opción work_mem , que configura cuánta memoria puede utilizar cada consulta antes de comenzar a almacenar datos en los archivos temporales en el disco. El valor predeterminado para esta opción es 4 MB. Lo cambié a 16 MB:

Las pruebas de la semana, el mes y el trimestre tuvieron tiempos de respuesta similares a los anteriores. Sin embargo, este cambio mejoró las consultas más grandes. A continuación, se muestran los gráficos para 1, 2 y 4 clientes después de ejecutar nuevamente las pruebas de PostgreSQL de un año de duración:

Con este cambio, las líneas sin cruzar y PostgreSQL comenzaron a devolver más rápido que SQLite, incluso para las consultas más grandes. Para la prueba más pesada con consultas de un año de duración y cuatro clientes, los tiempos de respuesta pasaron de 14 a 6 segundos con el servidor de base de datos sintonizado con la memoria. Estas consultas aún son más lentas de lo que desearía, pero considerando que estoy ejecutando en una plataforma de gama baja, creo que son aceptables.

Conclusión

Como era de esperarse, después de obtener los resultados, decidí cambiar a PostgreSQL. Después de actualizar el sistema de producción, le pregunté informalmente a algunos usuarios sobre el rendimiento y pensé que el sistema respondía considerablemente más rápido que antes, por lo que cumplí con mi objetivo.

Si vas a tomar algo de este artículo, espero que los únicos valores de referencia valiosos sean aquellos que se ejecutan en tu propia plataforma, con tu propia pila, con tus propios datos y con tu propio software. Y aun así, es posible que debas agregar optimizaciones personalizadas para obtener el mejor rendimiento.

En términos de mejorar aún más el rendimiento, este ejercicio de prueba me deja con dos posibles caminos:

• Si obtengo el presupuesto, ahora sé que es probable que agregar CPU mejore esas pruebas más largas. Cambiar a un servidor dedicado con 4 CPU debería marcar una diferencia considerable y acercar los tiempos de respuesta a lo que medía en mi computadora portátil.
• Sin arrojar dinero adicional al problema, el área principal de mejora es en esas consultas más grandes, lo que podría simplificarse si la base de datos almacenara totales mensuales calculados previamente además de los datos diarios. Luego, se podría recuperar un año de tráfico de 12 filas por artículo en lugar de 365. Algunos de los ahorros se compensarán por una lógica más compleja cuando las consultas no estén alineadas con los límites de los meses, pero en general esto debería reducir los tiempos de consulta más grandes.

Espero que hayas encontrado algunas ideas nuevas en este artículo y que te haya motivado a comparar y optimizar tus propios proyectos.

Miguel Grinberg es un ingeniero de software principal para contenidos técnicos en Twilio. Comunícate con él a la dirección mgrinberg@twilio.com si tienes un proyecto genial que te gustaría compartir en este blog.