PRAGMA in SQLite: configurare le connessioni per la produzione

Con PRAGMA parli con il motore

Un PRAGMA è un'istruzione specifica di SQLite che legge o modifica il comportamento del motore. Lo esegui come qualsiasi altro SQL, ma invece di toccare i tuoi dati tocca la configurazione del database.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Eseguito come query, un PRAGMA restituisce il valore attuale. Eseguito come assegnazione, cambia il valore:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Il modello mentale: la maggior parte dei PRAGMA vale per connessione. Apri una nuova connessione e tornano i valori predefiniti. Per questo il codice di produzione di solito ha un piccolo blocco di istruzioni PRAGMA che vengono eseguite subito dopo aver stabilito ogni connessione.

La base per la produzione

Se ti ricordi solo cinque PRAGMA, ricordati questi:

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

SELECT 'configurato' AS status;

È un valore predefinito sensato per quasi ogni applicazione che usa SQLite come archivio principale. Vale la pena capirli uno per uno: il resto della pagina li esamina tutti.

journal_mode = WAL

La modalità di journal controlla come SQLite rende durevoli le scritture. Quella predefinita, DELETE, usa un rollback journal: chi scrive blocca chi legge e chi legge blocca chi scrive. Va bene per uno strumento da riga di comando, è una sofferenza per un'app web.

WAL (Write-Ahead Logging) ribalta la situazione. Letture e scritture non si bloccano a vicenda: chi legge vede uno snapshot coerente mentre chi scrive fa il commit. Resta comunque un solo writer alla volta, ma le letture rimangono veloci sotto carico.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Alcune cose da sapere:

• journal_mode è persistente: una volta impostato, resta così per quel file di database. Non serve impostarlo a ogni connessione, ma non fa male.
• WAL crea due file aggiuntivi accanto al tuo .db: un -wal e un -shm. Non cancellarli mentre il database è aperto.
• WAL non funziona bene sui file system di rete (NFS, SMB). Tieni il database su un disco locale.

C'è una pagina dedicata alla modalità WAL e alla concorrenza che va più a fondo. Per ora: attivala.

synchronous = NORMAL

synchronous controlla con quanta insistenza SQLite scrive i dati su disco. Il compromesso è tra durabilità e velocità.

• FULL (predefinito): scrive su disco dopo ogni commit. Massima durabilità. Più lento.
• NORMAL: scrive su disco nei checkpoint sicuri. Sicuro con WAL. Più veloce.
• OFF: lascia decidere al sistema operativo. Veloce, ma rischi la corruzione in caso di blackout.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

L'intero nel risultato (1) corrisponde a NORMAL. In modalità WAL, NORMAL è l'impostazione consigliata: non perdi le transazioni già confermate in caso di crash, rischi solo di perdere le più recenti in caso di interruzione di corrente. Per la maggior parte delle app è il giusto equilibrio.

Non usare OFF a meno che tu non stia popolando un database usa e getta che puoi ricreare da zero.

foreign_keys = ON

Questo inganna molti. SQLite supporta le chiavi esterne, ma il controllo è disattivato di default, ed è un'impostazione per singola connessione:

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 ('Fantasmi', 999);

Con foreign_keys = ON, l'ultimo insert fallisce: non esiste nessun autore con id 999. Senza il PRAGMA, SQLite scrive tranquillamente la riga orfana e scopri il pasticcio mesi dopo.

Esegui PRAGMA foreign_keys = ON; come primissima istruzione a ogni nuova connessione. La maggior parte degli ORM lo fa in automatico; se usi il driver nudo e crudo, tocca a te.

busy_timeout = 5000

SQLite permette un solo writer alla volta. Se una seconda connessione prova a scrivere mentre la prima è a metà di una transazione, riceve SQLITE_BUSY e si arrende subito, per impostazione predefinita.

busy_timeout dice a SQLite di aspettare e riprovare:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Il valore è in millisecondi. 5000 significa "aspetta il lock fino a 5 secondi prima di arrenderti". Insieme a WAL, elimina la maggior parte degli errori database is locked ingiustificati nelle applicazioni concorrenti.

Se ti ritrovi ad alzarlo oltre i 30 secondi, la vera soluzione probabilmente sono transazioni più brevi, non un timeout più lungo.

cache_size

cache_size imposta quante pagine del database SQLite tiene in memoria. Più cache significa meno letture da disco, quindi query più veloci sui dati usati spesso.

Il valore ha due forme:

• Numero positivo: pagine. Con la dimensione di pagina predefinita di 4 KB, 2000 corrisponde a 8 MB.
• Numero negativo: kibibyte. -20000 corrisponde a 20 MB, qualunque sia la dimensione della pagina.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

La forma negativa è più facile da ragionare: dici "dammi 20 MB di cache" invece di fare conti con la dimensione della pagina. Per un'app piccola, da 20 a 50 MB sono più che sufficienti. Per un carico con molte letture su un database più grande, alzalo ancora. Come synchronous, anche cache_size vale per singola connessione.

mmap_size

L'I/O mappato in memoria permette a SQLite di leggere parti del file di database direttamente dalla page cache del sistema operativo, saltando una copia. Può velocizzare le letture sui database grandi:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Sono 256 MB. SQLite mapperà in memoria fino a quella quantità del database, se c'è spazio. Il paging lo gestisce il sistema operativo, quindi non stai davvero allocando 256 MB in anticipo: stai permettendo di mapparne fino a quella soglia.

mmap_size dà il meglio con i carichi a prevalenza di letture. Ed è innocuo sui database piccoli. I valori predefiniti sono prudenti, quindi aumentarlo di solito conviene.

PRAGMA optimize

Il query planner usa le statistiche per scegliere gli indici. Statistiche vecchie significano piani scadenti. PRAGMA optimize aggiorna quelle statistiche a basso costo:

PRAGMA optimize;

Lo schema consigliato è eseguirlo subito prima di chiudere le connessioni di lunga durata: allo spegnimento dell'applicazione, alla fine di un request handler che tiene una connessione aperta per un po'. È veloce (di solito millisecondi) e lavora solo quando qualcosa ha davvero bisogno di essere aggiornato.

Non è la stessa cosa di ANALYZE, che ricostruisce da capo tutte le statistiche. optimize è il cugino leggero, da eseguire spesso.

Leggere tutte le impostazioni

Se vuoi vedere com'è configurata al momento una connessione, interroga i PRAGMA senza assegnazione:

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

Utile in fase di debug: quando ti colleghi da un driver diverso e ti chiedi perché il comportamento è cambiato, quasi sempre c'è di mezzo un PRAGMA diverso.

C'è anche PRAGMA pragma_list;, che elenca tutti i PRAGMA supportati dalla build:

PRAGMA pragma_list;

Non è roba da imparare a memoria, ma torna comoda quando serve.

Impostazioni da dare alla creazione, non a runtime

Un paio di PRAGMA configurano il file di database stesso e hanno effetto solo prima che venga creata qualsiasi tabella:

• PRAGMA page_size = 8192;: dimensione della pagina su disco. Il valore predefinito è 4096, che va bene per la maggior parte dei carichi. Pagine più grandi aiutano con righe grandi.
• PRAGMA encoding = 'UTF-8';: codifica del testo.

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

Se cambi page_size su un database esistente, devi eseguire VACUUM perché abbia effetto. Imposta questi valori una volta sola, alla creazione, e dimenticali.

Un vero snippet di configurazione della connessione

Nel codice dell'applicazione, di solito questa parte vive in ciò che apre la connessione. Concettualmente:

-- Eseguire una volta a ogni nuova connessione:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Eseguire periodicamente, o prima della chiusura:
PRAGMA optimize;

temp_store = MEMORY tiene tabelle e indici temporanei in RAM, il che velocizza le query che devono ordinare o aggregare senza un indice.

Questa è tutta la checklist per la produzione. Mezza dozzina di righe, e SQLite passa da "va bene per lo sviluppo" ad "adatto a un carico reale".

Prossimo passo: errori comuni

Anche con dei buoni PRAGMA, incontrerai il solito repertorio di errori di SQLite: database is locked, disk I/O error, constraint failed. La prossima pagina spiega cosa significa davvero ciascuno e come risolverlo.