SQLite in Produzione: Quando un File Batte un Server di Database

Il pregiudizio da sfatare

Quando si parla di SQLite nell'ambiente dello sviluppo web, la reazione tipica è: "va bene per i prototipi, ma in produzione serve PostgreSQL". È un riflesso comprensibile, ma spesso sbagliato. SQLite è uno dei software più diffusi al mondo: gira dentro il tuo telefono, nel browser che stai usando, in decine di migliaia di applicazioni desktop. Non è un database "leggero" nel senso di "poco affidabile": è un database serverless nel senso letterale del termine, cioè che non richiede un processo separato a cui connettersi.

Il modello è semplice: il database è un file (o pochi file, contando il journal WAL), e la libreria legge e scrive direttamente su disco tramite chiamate di sistema. Niente rete, niente socket, niente daemon da supervisionare. Questo ha conseguenze concrete che vale la pena capire prima di decidere se fa per te.

Cosa cambia davvero rispetto a PostgreSQL

Il modo più onesto di inquadrare la questione è pensare a dove sta il collo di bottiglia. In PostgreSQL, ogni query passa per un processo server: c'è latenza di rete (anche se locale), c'è un pool di connessioni da gestire, c'è un demone che occupa memoria anche quando non fa nulla. In cambio, ottieni concorrenza in scrittura molto più raffinata, tipi avanzati, estensioni, repliche, e tutta una serie di strumenti operativi maturi.

In SQLite, una lettura è essenzialmente un'operazione su file. Su un disco locale, la latenza è nell'ordine dei microsecondi. Non c'è handshake di rete, non c'è round-trip TCP, non c'è autenticazione. Per carichi read-heavy con un solo processo applicativo, questo si traduce spesso in performance migliori di un PostgreSQL sullo stesso hardware—semplicemente perché c'è meno lavoro da fare.

Il problema emerge con la concorrenza in scrittura. SQLite usa un lock a livello di database: mentre una transazione scrive, le altre devono aspettare. Con WAL mode i lettori non bloccano gli scrittori e viceversa, ma resta il fatto che due scritture concorrenti sullo stesso file vengono serializzate. Se il tuo carico è di tipo "tante scritture brevi da molti processi diversi", SQLite non è la scelta giusta. Se è "molte letture, poche scritture serializzate da un'applicazione", spesso lo è.

La soglia pratica

Non esiste un numero magico, ma alcune regole empiriche aiutano:

• Un solo processo che scrive: SQLite è quasi sempre adeguato, anche con milioni di righe.
• Più processi (es. più istanze di un'app): diventa problematico, ma non impossibile se le scritture sono rare.
• Più macchine: qui serve altro. SQLite non ha replicazione nativa.
• Scritture continue ad alta frequenza: meglio un database client-server.

Un esempio di ordine di grandezza: un'applicazione monolitica con qualche migliaio di utenti attivi al giorno che scrivono qualche riga per richiesta è un carico che SQLite gestisce senza problemi, spesso con margine. Il punto è che il carico va misurato, non ipotizzato. Molti progetti migrano a PostgreSQL "per sicurezza" anni prima di averne bisogno, pagando complessità operativa per un problema che non esiste.

Configurazione: i pochi parametri che contano

SQLite di default è configurato per la compatibilità, non per la produzione. Alcune PRAGMA fanno una differenza enorme. Ecco una configurazione di partenza sensata:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -64000;

Vediamole una per una.

journal_mode = WAL è la più importante. In modalità di default (rollback journal), ogni scrittura blocca i lettori. In WAL (Write-Ahead Logging), le scritture vanno su un log separato e i lettori continuano a leggere la versione precedente. Il risultato è che letture e scritture non si bloccano a vicenda. È la differenza tra un database che si incastra alla prima query lenta e uno che scorre.

synchronous = NORMAL è un compromesso: con WAL attivo, il rischio di corruzione in caso di crash è praticamente nullo, ma potresti perdere le ultime transazioni in caso di kernel panic o interruzione di corrente. Se stai scrivendo un sistema bancario usa FULL; per il 99% delle applicazioni NORMAL è la scelta giusta.

foreign_keys = ON è disattivato di default per retrocompatibilità. Attivalo sempre, a meno che tu non abbia un motivo preciso per non farlo.

busy_timeout fa sì che, se il database è bloccato da un'altra transazione, la tua query aspetti invece di fallire subito con SQLITE_BUSY. Senza questo parametro, sotto concorrenza cominci a vedere errori intermittenti che sono un incubo da diagnosticare.

temp_store = MEMORY e cache_size sono ottimizzazioni: la prima evita di scrivere file temporanei su disco durante sort e join; la seconda aumenta la cache pagina (il valore negativo indica kilobyte, quindi -64000 significa circa 64 MB).

Concorrenza in scrittura: cosa aspettarsi

Con WAL attivo, SQLite permette un solo writer alla volta. Se due richieste cercano di scrivere contemporaneamente, una delle due aspetta (fino a busy_timeout) e poi procede. Questo è generalmente accettabile perché le scritture sono tipicamente molto brevi. Ma ci sono pattern che lo rompono:

• Transazioni lunghe che fanno calcoli mentre tengono il lock di scrittura.
• Batch di import che durano minuti, durante i quali l'applicazione non può scrivere.
• Molti worker concorrenti che scrivono in risposta a eventi esterni.

La soluzione non è mai "aumentare il timeout": è capire il pattern e ristrutturarlo. Se un job di import blocca le scritture per due minuti, spostalo su una transazione separata e fallo girare in un orario morto, oppure usa una coda.

Dove SQLite in produzione ha senso

Alcuni scenari in cui il file-batte-il-server è una scelta ragionevole, non un ripiego:

• Applicazioni self-hosted su una singola macchina, dove non c'è un cluster dietro.
• Strumenti interni con pochi utenti ma tanti dati storici (analytics, log, cruscotti).
• Applicazioni desktop e mobile, dove SQLite è di fatto l'unica scelta sensata.
• Siti a basso traffico dove il costo di un PostgreSQL gestito non è giustificato.
• Prototipi seri che vuoi poter migrare facilmente se crescono.

Il punto chiave è che SQLite non ti impedisce di migrare. Lo schema è SQL standard, le query sono le stesse, gli indici si creano allo stesso modo. La migrazione a PostgreSQL è un problema di dati e di codice, non di riscrittura totale.

Backup, migrazioni, indici

Tre aspetti operativi che spesso vengono trascurati.

Backup. Non copiare il file mentre il database è in uso: rischi di ottenere una copia incoerente, soprattutto in WAL mode. Usa il comando nativo VACUUM INTO per fare uno snapshot coerente in un nuovo file:

VACUUM INTO '/backup/app-2025-01-15.sqlite';

È atomico, blocca brevemente le scritture, e produce un file già deframmentato. Per backup continui esistono strumenti di terze parti che replicano il WAL verso storage esterno, ma anche un semplice cron giornaliero con VACUUM INTO copre la maggior parte dei casi.

Migrazioni. SQLite supporta un set limitato di ALTER TABLE: puoi aggiungere colonne, rinominare tabelle, ma non modificare il tipo di una colonna o aggiungere vincoli complessi. Il pattern canonico è: creare una tabella nuova con lo schema desiderato, copiare i dati, droppare la vecchia, rinominare. Farlo dentro una transazione è obbligatorio.

# esempio di migrazione "ricostruisci la tabella"
with conn:
    conn.execute("PRAGMA foreign_keys = OFF")
    conn.execute("CREATE TABLE users_new (...)")
    conn.execute("INSERT INTO users_new SELECT ... FROM users")
    conn.execute("DROP TABLE users")
    conn.execute("ALTER TABLE users_new RENAME TO users")
    conn.execute("PRAGMA foreign_keys = ON")

Indici. SQLite ha un query planner semplice rispetto a PostgreSQL. Non ti aspettare che indovini da solo: guarda EXPLAIN QUERY PLAN e crea gli indici che servono. Gli indici in SQLite costano poco in lettura e abbastanza in scrittura, quindi concentrati sulle query che girano spesso, non su tutto.

Cosa perdi rispetto a un database client-server

Per onestà intellettuale, vale la pena elencare quello a cui rinunci:

• Replicazione nativa: non esiste. Devi costruirla tu o usare strumenti esterni.
• Concorrenza in scrittura multi-processo: è serializzata. Se ti serve parallelismo reale, serve altro.
• Analisi su grandi dataset: per query analitiche pesanti, un motore colonnare o un PostgreSQL ben indicizzato vince.
• Estensioni e ecosistema: SQLite ha un ecosistema di estensioni in crescita, ma non è quello di Postgres.
• Isolamento a livello utente: il file è accessibile a chiunque abbia permessi sul filesystem. La sicurezza è delegata al sistema operativo.

Nessuno di questi è un problema nella maggior parte dei progetti personali, degli strumenti interni e dei siti a basso-medio traffico. Diventano problemi quando il progetto cresce.

Un criterio pratico per decidere

Prova a rispondere a queste tre domande:

1. Quanti processi scrivono sullo stesso database? Se è uno, SQLite va bene. Se sono più di uno, valuta caso per caso.
2. Le scritture sono brevi e serializzate, o lunghe e concorrenti? Se sono brevi, SQLite regge. Se sono lunghe, il lock diventa un collo di bottiglia.
3. Il database sta su una sola macchina per il prossimo anno? Se sì, SQLite è quasi sempre la scelta più semplice.

Se le risposte sono tutte a favore di SQLite, non migrare "per sicurezza". Il costo operativo di un PostgreSQL (backup, aggiornamenti, monitoraggio, connessioni) è reale,e se non serve, è solo complessità.

Conclusione

SQLite in produzione non è una bestemmia: è una scelta tecnica che ha senso in molti contesti concreti. I punti chiave:

• Attiva WAL mode. È la configurazione che cambia più di tutte le altre messe insieme.
• Imposta busy_timeout e synchronous in modo consapevole. I default non sono pensati per la produzione.
• Il limite vero è uno solo: un writer alla volta. Se il tuo carico non lo rompe, SQLite fa il suo lavoro molto bene.
• Backup con VACUUM INTO, migrazioni ricostruendo le tabelle, indici guidati da EXPLAIN QUERY PLAN.
• Misura prima di migrare. La migrazione a un database client-server è sempre possibile dopo, e spesso è lavoro che puoi rimandare di anni.

La domanda giusta non è "SQLite o PostgreSQL" in astratto, ma "quale problema sto risolvendo adesso". Per molti progetti, la risposta è un file.

Foto di copertina di Chris Ried su Unsplash.