PRAGMA en SQLite: ajustes clave para producción

PRAGMA: tu canal directo con el motor de SQLite

Un PRAGMA es una sentencia propia de SQLite que sirve para consultar o modificar el comportamiento del motor. Se ejecuta como cualquier otra sentencia SQL, pero en lugar de tocar tus datos, ajusta la configuración interna de la base de datos.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Si lo ejecutas como consulta, un PRAGMA te devuelve el valor actual. Si lo ejecutas como asignación, cambia ese valor:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

La idea clave: la mayoría de los PRAGMA son por conexión. Si abres una conexión nueva, vuelven los valores por defecto. Por eso, en código de producción es habitual tener un bloque pequeño de sentencias PRAGMA que se ejecutan justo después de abrir cada conexión.

Configuración base para producción

Si solo te vas a quedar con cinco PRAGMA, que sean estos:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'configurado' AS status;

Es un punto de partida razonable para casi cualquier aplicación que use SQLite como almacén principal. Vale la pena entender cada uno por separado, y el resto de esta página los va recorriendo uno a uno.

journal_mode = WAL

El modo de journal define cómo SQLite garantiza la durabilidad de las escrituras. El valor por defecto, DELETE, usa un rollback journal: los escritores bloquean a los lectores y los lectores bloquean a los escritores. Aceptable para una herramienta de línea de comandos, un dolor de cabeza para una aplicación web.

El modo WAL (Write-Ahead Logging) le da la vuelta a esto. Lectores y escritores ya no se bloquean entre sí: los lectores ven una instantánea consistente mientras un escritor está confirmando cambios. Sigue habiendo un único escritor a la vez, pero las lecturas se mantienen rápidas incluso bajo carga.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Algunas cosas que conviene tener claras:

• journal_mode es persistente: una vez configurado, queda así para el archivo de la base de datos. No hace falta volver a establecerlo en cada conexión, aunque tampoco pasa nada si lo haces.
• WAL genera dos archivos extra junto a tu .db: uno -wal y otro -shm. No los borres mientras la base de datos esté abierta.
• WAL no funciona bien sobre sistemas de archivos en red (NFS, SMB). Mantén la base de datos en disco local.

Hay un documento aparte que profundiza en el modo WAL y la concurrencia. Por ahora, basta con activarlo.

synchronous = NORMAL

synchronous controla con qué insistencia SQLite vuelca los datos a disco. El equilibrio está entre durabilidad y velocidad.

• FULL (por defecto): vuelca tras cada commit. Máxima durabilidad. Más lento.
• NORMAL: vuelca en puntos de control seguros. Seguro con WAL. Más rápido.
• OFF: deja la decisión al sistema operativo. Rápido, pero con riesgo de corrupción si se va la luz.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

El entero del resultado (1) corresponde a NORMAL. Con el modo WAL, NORMAL es el ajuste recomendado: no pierdes transacciones confirmadas si la aplicación se cae, solo te arriesgas a perder las más recientes ante un corte de luz. Para la mayoría de las aplicaciones, ese es el equilibrio adecuado.

No uses OFF salvo que estés llenando una base de datos desechable y puedas regenerarla desde cero.

foreign_keys = ON

Aquí es donde mucha gente se tropieza. SQLite soporta claves foráneas, pero la verificación viene desactivada por defecto, y además es un ajuste por conexión:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Fantasmas', 999);

Con foreign_keys = ON, ese último insert falla: no existe ningún autor con id 999. Sin el PRAGMA, SQLite escribe la fila huérfana tan tranquilo y te enteras del desastre meses después.

Ejecuta PRAGMA foreign_keys = ON; como la primerísima sentencia en cada nueva conexión. La mayoría de los ORMs lo hacen automáticamente; si trabajas con el driver en crudo, te toca a ti.

busy_timeout = 5000

SQLite solo permite un escritor a la vez. Si una segunda conexión intenta escribir mientras la primera está a mitad de una transacción, recibe un SQLITE_BUSY y se rinde al instante. Ese es el comportamiento por defecto.

Con busy_timeout le dices a SQLite que espere y reintente en lugar de abandonar:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

El valor se expresa en milisegundos. 5000 significa "espera hasta 5 segundos a que se libere el bloqueo antes de rendirte". Junto con WAL, esto elimina la mayoría de errores espurios de database is locked en aplicaciones concurrentes.

Si te ves subiendo este valor por encima de 30 segundos, lo más probable es que la solución real sean transacciones más cortas, no un timeout más largo.

cache_size

cache_size define cuántas páginas de la base de datos mantiene SQLite en memoria. Más caché implica menos lecturas a disco, y eso se traduce en consultas más rápidas sobre los datos frecuentes.

Acepta dos formatos:

• Número positivo: páginas. Con el tamaño de página por defecto de 4 KB, 2000 equivale a 8 MB.
• Número negativo: kibibytes. -20000 son 20 MB sin importar el tamaño de página.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

La forma negativa es más fácil de razonar: estás diciendo "dame 20 MB de caché" en lugar de hacer cuentas con el tamaño de página. Para una app pequeña, con 20–50 MB vas sobrado. Si tienes una base de datos grande con muchas lecturas, súbelo más. Igual que synchronous, cache_size se aplica por conexión.

mmap_size

La E/S mapeada en memoria permite a SQLite leer partes del archivo de la base de datos directamente desde la caché de páginas del sistema operativo, ahorrándose una copia. Puede acelerar las lecturas en bases de datos grandes:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Eso son 256 MB. SQLite mapeará en memoria hasta esa cantidad de la base de datos, siempre que haya espacio. La paginación la gestiona el sistema operativo, así que en realidad no estás reservando 256 MB de golpe: simplemente le das permiso para mapear hasta ese tope.

mmap_size brilla en cargas con mucha lectura. Y en bases de datos pequeñas no hace daño. Los valores por defecto son conservadores, así que subirlo casi siempre te da ganancia.

PRAGMA optimize en SQLite

El planificador de consultas se apoya en estadísticas para elegir índices. Si esas estadísticas están desactualizadas, los planes salen malos. PRAGMA optimize actualiza esas estadísticas de forma muy barata:

PRAGMA optimize;

El patrón recomendado es ejecutarlo justo antes de cerrar conexiones de larga duración: al apagar la aplicación, o al final de un handler que mantiene la conexión abierta un buen rato. Es rápido (normalmente milisegundos) y solo hace trabajo cuando hay algo que realmente necesita actualizarse.

Ojo, no es lo mismo que ANALYZE, que reconstruye las estadísticas por completo. optimize es su primo ligero, pensado para ejecutarse a menudo.

Consultar todos los ajustes activos

Si quieres ver con qué configuración está funcionando una conexión, basta con consultar los PRAGMA sin asignarles valor:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

Resulta muy útil al depurar: cuando te conectas desde otro driver y te preguntas por qué cambió el comportamiento, casi siempre es por una diferencia en algún PRAGMA.

También tienes PRAGMA pragma_list;, que lista todos los PRAGMA disponibles en tu build de SQLite:

PRAGMA pragma_list;

No es algo que vayas a memorizar, pero viene bien tenerlo a mano cuando hace falta.

Ajustes que van en el CREATE, no en tiempo de ejecución

Hay un par de PRAGMAs que configuran el propio archivo de base de datos y solo surten efecto antes de que se cree cualquier tabla:

• PRAGMA page_size = 8192; — tamaño de página en disco. El valor por defecto es 4096, que sirve de sobra para la mayoría de los casos. Las páginas más grandes ayudan cuando manejas filas grandes.
• PRAGMA encoding = 'UTF-8'; — codificación del texto.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

Si cambias page_size en una base de datos ya existente, hace falta ejecutar VACUUM para que surta efecto. Lo ideal es definir estos valores una sola vez, al crear la base de datos, y olvidarte del tema.

Ejemplo real de configuración de la conexión

En el código de una aplicación, esto suele ir en la parte que abre la conexión. A grandes rasgos:

-- Ejecutar una vez en cada nueva conexión:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Ejecutar periódicamente, o antes de cerrar:
PRAGMA optimize;

temp_store = MEMORY mantiene las tablas e índices temporales en RAM, lo que acelera las consultas que necesitan ordenar o agregar datos sin un índice.

Y con eso queda lista la checklist para producción. Media docena de líneas y SQLite pasa de "sirve para desarrollo" a "apto para una carga real".

Lo que viene: errores comunes

Aun con los PRAGMA bien configurados, te vas a topar con los errores típicos de SQLite: database is locked, disk I/O error, constraint failed. En la siguiente página repasamos qué significa cada uno y cómo solucionarlos.