Algunas cosas nuevas que aprendí al operar SQLite

• SQLite puede usarse también en el entorno de producción de sitios web pequeños, pero a medida que el Django ORM fue asumiendo más trabajo, se hizo evidente la complejidad operativa de la base de datos
• Una búsqueda FTS5 en una tabla de 4,000 filas tardaba 5 segundos, pero después de ejecutar ANALYZE bajó a unos 0.05 segundos, lo que confirmó que las estadísticas del query planner pueden tener un gran impacto en el rendimiento
• Si un DELETE masivo supera los 5 segundos, otras operaciones de escritura también pueden agotar el tiempo de espera y hasta provocar la terminación de workers y de la VM, por lo que las tareas de limpieza se procesan en lotes pequeños
• Para los backups se usaron VACUUM INTO y restic, pero hubo terminaciones por OOM; también se está probando Litestream para backups incrementales más eficientes
• Las tablas pueden separarse en varios archivos SQLite, y Mess with DNS lleva 4 años operando con SQLite desde 2022, por lo que se considera que fue una opción adecuada para el proyecto

---

Operar SQLite en un sitio Django

• Se adoptó como base de datos para un sitio Django tomando como referencia varios artículos que muestran que SQLite puede usarse incluso en sitios pequeños de producción
• SQLite también es una base de datos compleja que requiere conocimientos operativos, y en este proyecto se le está delegando más trabajo que antes a través del Django ORM
• Siguiendo varias recomendaciones, se activó el modo WAL desde el inicio de la operación
• Este es el cuarto sitio web en el que se usa SQLite

El rendimiento de búsqueda que cambió con ANALYZE

• En una tabla de 4,000 filas, una consulta de búsqueda de texto completo con SQLite FTS5 tardaba 5 segundos
• Al ejecutar ANALYZE, la misma consulta se redujo a unos 0.05 segundos, al punto de que ya no hizo falta investigar más
• No se pudo comprobar exactamente qué problema había en el plan de consulta, y se sospecha que fue una situación similar a una complejidad temporal cuadrática accidental
• ANALYZE genera información estadística, como la cantidad de filas de las tablas, para ayudar al query planner a tomar mejores decisiones
• Aún no se ha aprendido a leer planes de consulta

Limpieza de datos y la restricción de una sola escritura

• Al eliminar en masa filas innecesarias, como las tareas completadas de django-tasks-db, surgió una cadena de problemas
  • El comando de limpieza que procesaba muchas filas se ejecutaba durante más de 5 segundos
  • Mientras tanto, las escrituras a la base de datos de otros workers superaban el límite configurado de 5 segundos y agotaban el tiempo de espera
  • Los workers que fallaban al escribir se caían, y también se terminaba la VM
• No se pudo confirmar la razón exacta por la que el DELETE era lento; también se considera la posibilidad de que se deba a la ejecución de mucho código Python dentro de una transacción
• Actualmente, las tareas de limpieza se dividen en lotes pequeños para que cada consulta a la base de datos no supere los 5 segundos
• Esta experiencia hizo sentir en la práctica por qué se necesita una base de datos como Postgres, capaz de manejar varias operaciones de escritura al mismo tiempo
• A futuro también se considera bajar el sitio durante este tipo de trabajos para realizar mantenimiento programado, pero aún no existe un flujo de trabajo definido

Consultas del ORM y tamaño actual de los datos

• Hasta ahora se han creado las consultas deseadas con el Django ORM sin revisar el rendimiento por separado, pero salvo el problema de ANALYZE, casi todo ha funcionado correctamente
• La base de datos es pequeña, de unas 10,000 filas, y se espera que siga manteniéndose en una escala reducida

Formas de hacer backup de SQLite

• Se probaron dos formas de hacer backup de SQLite: restic y Litestream
• Las tareas de backup normalmente se monitorean con un dead man's switch, pero parece que todavía no se han hecho pruebas reales de restauración

• Backup con restic

  • Se crea una copia de la base de datos con VACUUM INTO, se comprime con gzip y luego se sube a S3
  • Con restic se crea el backup, se verifican snapshots, se eliminan backups antiguos y se hace limpieza
  • A veces el backup se terminaba por OOM, dejando un bloqueo pendiente, por lo que también se ejecutaba restic unlock

  sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
  gzip /tmp/calendar.sqlite
  restic -r s3://s3.amazonaws.com/some_bucket/ unlock
  restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
  restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
  restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
  restic -r s3://s3.amazonaws.com/some_bucket/ prune
  

• Backup incremental con Litestream

  • Se empezó a usar Litestream para evitar las terminaciones por OOM de los backups con restic y probar la eficiencia de los backups incrementales
  • Después de escribir el archivo de configuración, se ejecuta la replicación con el siguiente comando

  litestream replicate -config litestream.yml
  

  • Se especificó retention: 400h para conservar el historial de la base de datos durante cierto tiempo, pero aún no se ha confirmado si funciona como se esperaba
  • Se hacen backups en AWS, pero como generar credenciales en la consola es engorroso, también se considera migrar más adelante a otro servicio compatible con S3

Usar varios archivos de base de datos SQLite

• El proyecto actual usa una sola base de datos, pero en Mess with DNS las tablas que no necesitaban estar juntas se separaron en 3 archivos de base de datos
• Se considera que esta separación ayudó en la operación
• Mess with DNS se ha operado con SQLite durante 4 años desde 2022, y en ese proyecto pasar de Postgres a SQLite fue una buena decisión

Funciones básicas descubiertas tarde durante la operación

• El primer uso de SQLite en un proyecto web fue en 2022, pero la existencia de ANALYZE se conoció recién esta vez
• Incluso las funciones básicas de las tecnologías que usamos se siguen aprendiendo durante años a través de la operación real

[GN⁺]

Comentarios de Hacker News

• Usar el modo .expert de SQLite permite posponer un poco el día en que tengas que aprender a leer planes de consulta: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
  Si analizas SELECT * FROM x1 WHERE a=? AND b>?;, te recomienda un índice como CREATE INDEX x1_idx_000123a7 ON x1(a, b);; si lo creas y vuelves a analizar, indica que ya no hace falta un índice nuevo
  Incluso en bases de datos “de verdad” como Postgres, normalmente se recomienda dividir las tareas de limpieza en lotes pequeños. Solo que, a menor escala, se nota menos que es una operación ineficiente, así que el enfoque del artículo original era más correcto de lo que parecía

  • En algunas bases de datos, si eliminas 10 millones de filas, también se registra esa misma cantidad de datos de deshacer. En Oracle, si no se respaldan y vacían a tiempo, el espacio en disco para los archive logs puede agotarse
    Hacer commits frecuentes ayuda, pero al limpiar bases de datos grandes de forma regular, lo más efectivo era el particionamiento. Eliminar la partición más antigua termina casi de inmediato
    Aun así, no queda claro por qué se apaga la VM en “el worker no puede escribir en la base de datos, entra en conflicto y la VM se apaga”. Aquí VM parece referirse a la máquina virtual donde corre el sistema operativo
  • En MySQL grande con replicación basada en filas, había que ejecutar en lotes los UPDATE o DELETE que modificaban millones de filas. Si se hacía en una sola consulta, había que enviar de golpe esos millones de filas actualizadas a todas las réplicas
  • Parece similar a EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html
    El EXPLAIN normal suele mostrar bytecode excesivamente verboso, pero EXPLAIN QUERY PLAN da un plan resumido
  • Me pregunto si se está viendo como ventaja eso de que “se note menos que es una operación ineficiente”

• Como alguien que trabaja con bases de datos, fue una lectura frustrante y me dieron ganas de encontrar la causa y resolverla yo mismo. Si es una tabla con solo 10 mil filas, hasta un full table scan debería ser muy rápido, y más aún con SQLite ejecutándose dentro del proceso en el mismo servidor físico
  La solución que se me vino a la cabeza fue, obviamente, “crea un índice”. La lentitud al borrar parece muy probablemente el clásico problema N+1 que suelen sufrir usuarios de ORM antes de entender cómo interactúan internamente con la base de datos; ojalá Julia publique una continuación

• Hace unos años, como crear credenciales en la consola de AWS era demasiado engorroso, hice una herramienta solo para resolver ese problema
  uvx s3-credentials create my-existing-s3-bucket
  Ese comando imprime credenciales de lectura y escritura limitadas solo a ese bucket. Puedes restringir más los permisos con --read-only o --write-only, o hacer que solo lea y escriba claves que empiecen con un prefijo específico usando --prefix foo/bar
  También lo probé con Restic y Cloudflare R2, y funcionó muy bien

  • Una forma más general de lidiar con servicios complejos de AWS es aprender un poco de Terraform y dejarle el resto al LLM. Así también se vuelve mucho más fácil desmontar después los recursos creados, sin necesidad de recordar qué generaste
  • Como herramienta similar ya existente, también está https://litestream.io/
  • Es una herramienta excelente; parecería algo que AWS CLI debería absorber. Aun así, me pregunto cuándo harían falta credenciales de solo escritura

• En la era de los LLM, valoro aún más el texto de Julia. Ese proceso de exploración sincero es un antídoto contra los textos generados automáticamente que hablan con total seguridad como si lo supieran todo

• Ejecuto los respaldos así:
  OUT="${i}.sql.zst"
PART="${OUT}.part"
sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
mv "${PART}" "${OUT}"
  Si el writer usa WAL, esto no lo bloquea, y produce dumps fáciles de sincronizar y con buena compresión. La base de datos de Home Assistant pesa 1.8GB, pero el dump comprimido queda en 286MB, y calculo que alrededor del 90% de lo que se genera cada día es idéntico

  • Me pregunto qué estás respaldando en Home Assistant. Mi respaldo predeterminado era demasiado grande, así que dejé solo la configuración y excluí videos, caché y los repositorios descargados por HACS, pero no sé si me estoy perdiendo algo importante
    También me pregunto si la base de datos creció tanto por registros de series temporales retenidos durante mucho tiempo
  • VACUUM INTO, .backup, sqlite3_rsync y Litestream tampoco bloquean al writer. .backup usa la API de respaldo
  • Después de que .dump bloqueó una vez una base de datos en producción, me cambié a .backup. Aun así, el enfoque de escribir en .part y luego hacer mv está muy limpio

• Considero que el texto no tiene contenido sustancial porque solo usa expresiones como “no lo investigué más”, “es mi mejor suposición”, “¿quizás otras cosas también?”, “puede que se esté ejecutando mucho código Python dentro de la transacción”. Hace suposiciones sin verificar ni investigar bien, y a veces incluso son incorrectas
  Como usuario de Debian, cuando en búsquedas sobre Linux aparece un foro de Ubuntu, no lo abro porque hay demasiadas suposiciones erróneas. En cambio, aunque Arch es bastante distinto de Debian, casi siempre consulto la Arch Wiki porque la escribe gente que sabe

  • Julia Evans tiene muchísimo conocimiento técnico y destaca especialmente por quitarle el aura de misterio a la tecnología y ayudar a entender cómo es realmente el proceso de resolver problemas
    Este artículo no pretende ser la explicación del mayor experto del mundo en SQLite; desde el título deja claras las expectativas con “aprender algunas cosas sobre operar SQLite”
    En los textos de Julia siempre está presente la idea de que no hace falta saberlo todo ni fingir que lo sabes: se puede entender un problema y construir conocimiento de forma sencilla. Compartir con la mayor claridad posible lo que has descubierto hasta ahora es una buena actitud
  • Me parece excelente porque muestra muy bien la situación de un usuario general inteligente que usa esa tecnología. El enfoque de la autora es operar un sitio web, y este tipo de problemas son justo donde suelen tropezar los usuarios cotidianos que no pasan todo el día usando una sola herramienta
    Ayer mismo, al crear una funcionalidad, usé 2 lenguajes de programación, 2 sistemas de build, un proveedor de nube, una herramienta de gestión de secretos, un framework complejo de comunicación cliente-servidor entre dos lenguajes, un sistema de control de versiones, un editor y una herramienta de CI. Y todavía podría profundizar en el sistema operativo y las versiones del runtime, la base de datos, el proxy inverso, el caché y la lógica de negocio
    Si profundizas a fondo en cada tema con el que te cruzas, nunca terminas nada, así que como la autora, hay que elegir en qué problema enfocarse
  • La Arch Wiki es uno de los mejores recursos de Linux. Cuando usaba Mint siempre la consultaba, pero curiosamente, desde que uso Arch la consulto menos que en mi época con Mint

• El problema de DELETE puede mitigarse fácilmente con borrado por lotes, pausas entre lotes y precargando rowid con SELECT. SELECT no provoca bloqueos
  Si los datos se agregaron secuencialmente a la misma tabla, es probable que también estén almacenados en el archivo en un orden parecido, así que borrarlos en ese orden o en orden inverso podría ser más rápido. El resultado cambia según el medio de almacenamiento y otras condiciones

  • Precargar IDs de fila fue muy efectivo no solo en SQLite, sino también en grandes clústeres de Aurora MySQL o Postgres. Se podía enviar el SELECT a una réplica, y la presión de memoria del índice causada por el filtrado de filas estaba cargando mucho el CPU y el buffer cache de la base de datos
    Si no puedes sacar datos innecesarios de la ruta principal de procesamiento con algo como partition pruning, es una estrategia muy poderosa

• Profundizar un poco más en bases de datos, más allá del nivel que manejas hoy o de lo que exige tu trabajo, sigue siendo una buena forma de mejorar tus habilidades. Muchos desarrolladores web se traban frente a las herramientas de base de datos, y a mí me pasa algo parecido con algunas tecnologías de operaciones como K8s
  Entender cómo SQL se traduce en operaciones de lectura y escritura de datos en disco ayuda muchísimo a juzgar intuitivamente qué enfoques pueden funcionar. También hay que comprender el sistema de bloqueos de la base de datos
  Con ese conocimiento, es menos probable que te desconcierte cuando incluso un simple COUNT en Postgres no termina rápido

• sqlite_stat1 y sqlite_stat4 guardan varias estadísticas sobre la distribución de valores de los índices, y el planificador de consultas las usa para estimar la selectividad y utilidad de esos índices
  sqlite_stat1 solo da la cantidad de registros del índice y el promedio de registros por valor; si se habilita, sqlite_stat4 también guarda datos de histograma

• Litestream es muy interesante, y logré hacerlo funcionar usando S3 como backend. Puede hacer que muchísimas apps que usan SQLite dependan muy poco del estado del sistema de archivos
  El estado de S3 es mucho más fácil de manejar, y además el proveedor se encarga del respaldo y la sincronización