Cómo copiar bases de datos SQLite más rápido entre computadoras

• Al mover una base de datos SQLite grande desde un servidor remoto a una máquina local, copiar un volcado SQL comprimido con gzip en lugar del archivo .db original reduce mucho la cantidad de datos transferidos
• La principal causa de que el archivo crezca son los índices: duplican datos de las tablas para acelerar las consultas, lo que aumenta el uso de disco
• .dump de SQLite convierte la base de datos en texto con sentencias SQL, y los índices se representan con una sola línea CREATE INDEX, no como datos reales, evitando transferir duplicados
• En la base de datos de ejemplo, el original pesaba 3.4 GB, el texto del volcado 1.3 GB y la versión comprimida con gzip 240 MB; el volcado comprimido era 14 veces más pequeño que el original
• Generar antes un archivo de volcado fijo puede reducir situaciones en las que el original cambia durante la transferencia y aparece el error database disk image is malformed

---

Copiar un volcado de SQLite comprimido

• Para bases de datos SQLite pequeñas, copiarlas directamente con rsync suele ser suficiente
  • Ejemplo: rsync --progress username@server:my_remote_database.db my_local_database.db
• Cuando la base de datos crece, la copia directa puede volverse lenta e inestable
  • Descargar una base de datos de 250 MB desde un servidor web con una conexión doméstica toma alrededor de 1 minuto
  • En bases de datos de varios GB, el tiempo de espera aumenta aún más
• El cuello de botella del crecimiento está en los índices
  • Los índices aumentan mucho la velocidad de las consultas, pero también agrandan el archivo de la base de datos
  • En una base de datos, un índice específico ocupaba la mitad del tamaño en disco
  • Los índices, más que contener datos únicos, duplican datos de otras tablas para acelerar las búsquedas
• SQLite puede volcar una base de datos completa a un archivo de texto
  • Comando: sqlite3 my_database.db .dump > my_database.db.txt
  • El archivo resultante está compuesto por sentencias SQL como CREATE TABLE, INSERT INTO y CREATE INDEX
  • Los índices se guardan como comandos de creación, por ejemplo CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);, no como los datos reales del índice
• El archivo de volcado puede restaurarse nuevamente como una base de datos SQLite
  • Comando: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
• Los volcados SQL tienen muchas repeticiones, por lo que la compresión gzip es muy eficiente
  • Ejemplo: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
  • Comparación de tamaños:
    • Base de datos SQLite original: 3.4 GB
    • Volcado de texto: 1.3 GB
    • Texto comprimido con gzip: 240 MB
  • La versión comprimida con gzip era 14 veces más pequeña que la base de datos SQLite original

Procedimiento real y efecto en la estabilidad

• El nuevo método crea un volcado comprimido en el servidor remoto, descarga solo ese archivo y luego reconstruye la base de datos localmente
• El procedimiento de copia es el siguiente
  • Crear el archivo de texto comprimido con gzip en el servidor: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
  • Copiarlo a la máquina local: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
  • Eliminar el archivo comprimido temporal del servidor: ssh username@server "rm my_remote_database.db.txt.gz"
  • Descomprimir localmente: gunzip my_local_database.db.txt.gz
  • Reconstruir la base de datos local: cat my_local_database.db.txt | sqlite3 my_local_database.db
  • Eliminar el archivo de texto local: rm my_local_database.db.txt
• El volcado comprimido funciona como una fuente de copia consistente
  • Si el original se actualiza mientras se copia una base de datos grande durante mucho tiempo, la parte inicial del archivo resultante puede quedar en el estado previo a la actualización y la parte final en el estado posterior
  • Ese tipo de archivo puede producir el error database disk image is malformed al abrirlo localmente
• Si se crea un volcado de texto antes de copiar, el archivo que transfiere rsync no cambia a mitad del proceso
  • Esto aumenta la probabilidad de copiar un archivo de texto completo y consistente
• Si mueves bases de datos SQLite grandes con frecuencia, este método puede reducir el tiempo de descarga y mejorar la confiabilidad de la copia

[GN⁺]

Opiniones de Hacker News

• Si copias tal cual un archivo de base de datos que está en ejecución y recibiendo actualizaciones, obviamente puede terminar corrompido.
  Para replicar una base de datos SQLite de forma segura existe Litestream: https://github.com/benbjohnson/litestream

  • Litestream funciona incluso sobre SFTP común, así que puedes hacer streaming de la replicación de la base de datos hacia casi cualquier endpoint UNIX accesible por SSH.
    Hay servicios preferidos, pero cualquier servidor SFTP sirve.
    [1] https://github.com/benbjohnson/litestream/issues/140
    [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
  • Hay excepciones a lo de que “necesariamente” se corrompe. Si en Btrfs o ZFS haces una copia atómica de un archivo o subvolumen, en una base de datos ACID o un árbol LSM, en el peor caso solo habrá un rollback.
    Claro que, si son varios archivos, hay que agruparlos en un subvolumen para que todos se copien dentro de la misma transacción; simplemente usar cp --reflink=always no basta.
    Detener el proceso con SIGSTOP también podría producir un resultado parecido, pero no quisiera depender de eso.
  • El comando integrado .backup también es una herramienta oficial pensada para crear una versión snapshot de una base de datos en ejecución y permitir copiarla.
  • En mi sistema uso Litestream sin problemas, pero también está buena esta documentación que explica con bastante detalle cómo hacer algo similar manualmente solo con herramientas integradas: https://litestream.io/alternatives/cron/
  • Litestream es realmente genial. Planeo usarlo para backup y recuperación de SQLite a nivel de contenedores, como hizo aquel ex-Google que creó una pequeña startup de KVM y al que se le inundó el almacén durante las vacaciones.
    Si no recuerdo mal, escribió una guía perfecta, pero las probabilidades de encontrarla son cercanas a cero. Sería bueno que alguien que conozca esta referencia compartiera el enlace.

• Esto se siente como decir que vas a copiar una base de datos entre computadoras, dibujar solo un círculo y omitir el resto del búho.
  Como dijeron otros, un rsync incremental sería mucho más rápido, pero lo que más molesta es que diga que enviar sentencias SQL es más rápido que enviar la base de datos y omita por completo el hecho de que hay que ejecutar esas sentencias SQL. Después también hay que correr /optimize/ y /vacuum/.
  Ahora tengo una situación en la que debo “reconstruir incrementalmente” una base de datos a partir de archivos CSV, y en mi caso es más óptimo crearla de nuevo desde cero. Aun así, incluso después de muchas optimizaciones, solo hacer inserciones por lotes en una base de datos vacía en memoria y crear los índices tarda 30 minutos.

  • Espero que hayas visto https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite.
    Es una muy buena recopilación de formas de insertar rápido en SQLite3.
  • Para cualquier optimización, aquí lo importante es dónde está el cuello de botella. En el caso del artículo original, el cuello de botella parece ser el ancho de banda, y la CPU/I/O de disco parecen suficientes.
    Decía que descargar una base de datos de 250 MB tardaba 1 minuto; yo, gracias a mi fibra de 1 Gbps, traje desde el servidor de la empresa una base de datos SQLite de prueba de 2 GB en 15 segundos.
  • 30 minutos suena mucho. ¿Tienes muchísimos datos? Estoy trabajando en bootstrapping de una base de datos SQLite con muchos datos JSON, y encontré un sweet spot de rendimiento bastante bueno: acumular valores en listas e insertar de a 10 mil permite cargar millones de filas en pocos minutos.
    Tuve que usar trucos como filtros Bloom y caché LRU, pero ahora puedo crear una base de datos de 6 GB en unos 20 minutos.

• SQLite tiene una herramienta oficial para este caso de uso: https://www.sqlite.org/rsync.html
  Funciona a nivel de página.
  “El protocolo funciona así: la réplica envía al origen un hash criptográfico de cada página, y el origen devuelve el contenido completo de las páginas cuyos hashes no coinciden”.

  • Es cierto, pero lamentablemente el equipo de SQLite no incluye esa herramienta en el tarball de autotools que la mayoría de las distribuciones y brew usan para empaquetar SQLite.
    Para usarla, no queda otra que compilarla uno mismo.

• La utilidad sqlite_rsync, lanzada recientemente, usa una versión del algoritmo rsync optimizada para la estructura interna de las bases de datos SQLite. Compara eficientemente las páginas internas de datos y luego sincroniza solo las páginas modificadas o faltantes.
  Los trucos del artículo también están buenos, pero ahora es más fácil usar la utilidad integrada :)
  Escribí en detalle cómo funciona en este blog: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

  • sqlite3_rsync ahora está integrado en la plataforma rsync.net.
    ssh [e-mail] sqlite3_rsync … blah blah …
    Lo agregaron recién la semana pasada y todavía no se desplegó en todas las regiones, pero los primeros usuarios dicen que funcionó exactamente como esperaban.
  • sqlite_rsync solo puede usarse en modo WAL. Como restricción adicional del modo WAL, el archivo de la base de datos debe estar en un disco local.
    Para la mayoría eso será lo normal, pero si no es posible, no se puede usar esta utilidad.
  • Si sincronizas con frecuencia, por el ancho de banda deberías aumentar el tamaño de página

• Me sorprendió que no se haya intentado usar la compresión durante la transferencia que ofrece rsync
-z, --compress comprime los datos de los archivos durante la transferencia, y --compress-level=NUM especifica explícitamente el nivel de compresión
  Quizá comprimir con gzip y luego transferir sea más rápido, pero está bueno que se pueda mejorar la transferencia con una sola bandera

  • Si se mencionó el problema de corrupción, es mejor usar sqlite3_rsync (https://sqlite.org/rsync.html) junto con -z
    En la práctica, sería rsync consciente de las transacciones de SQLite y WAL, con compresión durante la transferencia incluida
  • La clave es omitir los índices, y eso debe hacerse antes de comprimir
    Cuando hago este tipo de tareas, transmito el dump directamente a gzip. Por lo general también se puede encontrar una forma de transmitirlo directo al destino sin archivos intermedios
    Además, así queda almacenado comprimido en el destino. Sobre todo si el objetivo no es una replicación de pobre, sino un backup
  • Creo que la compresión solo conviene en redes lentas
  • Esto es imprescindible. Si usas rsync sobre un archivo comprimido, estás evitando por completo lo esencial de usar rsync: la transferencia diferencial basada en checksums rodantes
  • Con --remove-source-files no habría hecho falta volver a entrar por SSH para hacer rm

• Guardarlo como archivo de texto es ineficiente. Yo guardo las bases de datos SQLite con VACUUM INTO
sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
  Según https://sqlite.org/lang_vacuum.html, el comando VACUUM con la cláusula INTO es una alternativa a la API de backup para crear una copia de respaldo de la base de datos en ejecución. La ventaja es que el tamaño de la base de datos de backup resultante se minimiza, lo que puede reducir el I/O del sistema de archivos

  • Suena bien, pero no resuelve el problema de los índices mencionado en el texto original. La idea central era no enviar datos de índices por un enlace lento, y el enfoque con VACUUM INTO conserva los índices
    Un archivo de texto puede ser ineficiente tal cual, pero se comprime bien incluso con herramientas primitivas como gzip. No estoy seguro de que el formato binario de SQLite se comprima igual de bien, aunque es posible
  • ¿Ese método conserva los índices? En el texto original se decía que la razón por la que el archivo SQLite era grande eran los índices

• En DuckDB se puede hacer lo mismo, pero exportando a Parquet, con lo que el tamaño de los datos queda un orden de magnitud menor que con sentencias SQL basadas en texto. También se transfiere más rápido y se carga más rápido
  https://duckdb.org/docs/stable/sql/statements/export.html

  • Desde la línea de comandos se puede hacer así
    duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
    En mi base de datos de prueba fue alrededor de un 20% más pequeño que las sentencias SQL de texto comprimidas con gzip

• SQLite tiene una extensión de sesiones que rastrea cambios en un conjunto de tablas y crea changesets/patchsets que se pueden aplicar a una versión anterior de una base de datos SQLite
  https://www.sqlite.org/sessionintro.html

  • Todavía no he visto ningún binding de SQLite que soporte esto. Así que si no piensas escribir la aplicación en C o parchear el binding del lenguaje, resulta bastante menos útil
    En uno de mis proyectos implementé una sesión de pobre por mi cuenta: registraba todas las sentencias SQL y sus parámetros en una base de datos separada, luego la sincronizaba y la reproducía. Funciona bastante bien para una base de datos de unos 30 GB en la que solo cambia alrededor del 0.1% por día
  • ¿Lo has usado alguna vez? Leí la documentación, pero nunca escuché de alguien que realmente use esta extensión
  • Sería muy bueno que SQLite tuviera una forma básica de hacer captura de datos de cambios mediante sesiones o algo similar

• Si sincronizas regularmente de una versión antigua a una nueva, se puede optimizar más con la opción --rsyncable de gzip
  La tasa de compresión baja alrededor de un 1%, pero las diferencias entre una versión y la siguiente quedan localizadas en vez de propagarse en cadena por toda la salida comprimida
  Otra forma es no comprimir la salida del dump, dejar que rsync calcule la diferencia entre el dump anterior sin comprimir y el dump actual, y que comprima solo el conjunto de cambios que se envía por la red. Es decir, usar rsync -z

• Parece que esto podría hacerse como un solo pipeline
  ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

  • Si activas la compresión de SSH con -oCompression=on o -C en la llamada SSH, gzip/gunzip podría ser redundante
  • Eso también fue lo primero que pensé. Y también parece bastante obvio que ahí debería entrar pv