SQLite vs PostgreSQL: quando scegliere l'uno o l'altro

Due database, due forme diverse

SQLite e PostgreSQL parlano entrambi SQL, salvano entrambi dati relazionali ed entrambi possono far girare applicazioni reali. Oltre a questo, sono costruiti per mondi diversi.

• SQLite è una libreria. Vive dentro il processo della tua applicazione e legge da un unico file .db su disco. Nessun server, nessuna porta, nessun utente da configurare.
• PostgreSQL è un server. Gira come processo a sé, resta in ascolto su una porta di rete e la tua applicazione si collega come client.

Quasi ogni altra differenza tra i due, cioè concorrenza, deploy, rigidità dei tipi e prestazioni, deriva da questa unica separazione di architettura. Tienila a mente mentre andiamo avanti.

Architettura: nel processo contro client/server

Aprire un database SQLite significa aprire un file:

-- SQLite: il database è semplicemente questo file.
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('ciao da un file');
SELECT * FROM notes;

Nessun demone da avviare, nessun pg_hba.conf da modificare, nessuna porta da esporre. La tua app carica la libreria SQLite, apre notes.db e inizia a eseguire query. Il deploy consiste nel "copiare il file".

Postgres invece è più così:

# Avvia il server (una volta, come amministratore):
sudo systemctl start postgresql

# Poi collegati dalla tua app:
psql -h localhost -U alice -d mydb

La tua applicazione parla con un processo separato, di solito via TCP, a volte tramite un socket Unix. Questo livello in più ti costa tempo di configurazione e un passaggio di connessione per ogni query, ma ti dà accesso di rete, autenticazione multiutente e veri writer concorrenti.

La concorrenza è il punto decisivo

Di solito è il fattore che decide. SQLite serializza le scritture: in ogni momento un solo writer tiene il lock sul file del database e gli altri aspettano. Le letture possono avvenire in parallelo (soprattutto in modalità WAL), ma le scritture passano una alla volta.

Postgres usa MVCC (multi-version concurrency control) e lock a livello di riga. Molte transazioni possono scrivere su righe diverse contemporaneamente senza bloccarsi a vicenda.

In pratica:

• Un blog con 50 lettori al secondo e un autore che scrive ogni tanto? SQLite va benissimo.
• Il checkout di un e-commerce in cui centinaia di utenti aggiornano l'inventario nello stesso momento? Postgres.
• La cache locale di un'app mobile? SQLite, senza discussioni.
• Il backend di un SaaS multi-tenant con decine di worker in background? Postgres.

La modalità WAL (PRAGMA journal_mode = WAL;) migliora molto la concorrenza di SQLite, perché chi legge non blocca chi scrive, ma non cambia la regola di un solo writer alla volta.

Sistemi di tipi: flessibile contro rigido

Postgres è rigido. Una colonna dichiarata INTEGER rifiuta le stringhe, punto:

-- Postgres
CREATE TABLE t (n INTEGER);
INSERT INTO t (n) VALUES ('not a number');
-- ERROR: invalid input syntax for type integer

SQLite, per impostazione predefinita, usa l'affinità di tipo: un suggerimento più che una regola. Lo stesso insert va a buon fine:

CREATE TABLE t (n INTEGER);
INSERT INTO t (n) VALUES ('non è un numero');
SELECT n, typeof(n) FROM t;

La stringa se ne sta lì, in una colonna INTEGER. SQLite l'ha salvata come testo. Questa flessibilità è stata una scelta di progetto deliberata: utile per i prototipi veloci, pericolosa per gli schemi destinati a durare.

Le versioni moderne di SQLite (3.37+) supportano le tabelle STRICT, che si comportano più come Postgres:

CREATE TABLE t (n INTEGER) STRICT;
-- INSERT INTO t (n) VALUES ('not a number');  -- ora è un errore
INSERT INTO t (n) VALUES (42);
SELECT n, typeof(n) FROM t;

Se inizi un nuovo progetto con SQLite, usa STRICT. Elimina un'intera categoria di sorprese del tipo "perché c'è una stringa nella mia colonna numerica".

Funzionalità disponibili

Postgres ha di più quasi in tutto: tipi di dati (array, intervalli, geometrici, di rete, enum personalizzati), linguaggi procedurali (PL/pgSQL, PL/Python), ricerca full-text con ranking, viste materializzate, partizionamento delle tabelle, replica, sicurezza basata sui ruoli e un ricco ecosistema di estensioni (PostGIS, TimescaleDB, pgvector).

SQLite copre l'essenziale e aggiunge alcune funzioni utili per la sua scala: funzioni JSON, ricerca full-text con FTS5, indici R-Tree, funzioni finestra, CTE, colonne generate. Quello che manca è tutto ciò che presuppone un server: utenti, ruoli, replica, accesso di rete.

Un modello mentale approssimativo:

• Ti servono GIS, ricerca vettoriale o replica? Postgres.
• Devi distribuire un database dentro un'app iOS? SQLite.
• Ti servono entrambe le cose? Molti team sviluppano e testano su SQLite e poi fanno il deploy su Postgres, anche se questo mix può crearti problemi con le differenze di sintassi (vedi sotto).

Le differenze di sintassi che incontrerai davvero

La maggior parte dell'SQL di tutti i giorni è identica. Le differenze si concentrano su schema, tipi e alcune funzioni integrate:

-- Chiave primaria con incremento automatico
-- SQLite:
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
-- Postgres:
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
-- oppure, in Postgres moderno:
CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);

-- Timestamp attuale
-- SQLite:  CURRENT_TIMESTAMP   (restituisce testo)
-- Postgres: NOW()              (restituisce timestamp)

-- Tipo booleano
-- SQLite:  nessun vero BOOLEAN; usa INTEGER 0/1
-- Postgres: BOOLEAN con TRUE/FALSE

Se sviluppi su SQLite e fai il deploy su Postgres, conviene tenere un ORM o uno strumento di migrazione tra te e l'SQL grezzo, altrimenti queste differenze finiscono dentro la tua app.

Prestazioni, onestamente

"Più veloce" dipende dalla domanda. Per un singolo processo che fa letture e piccole scritture, SQLite è difficile da battere: nessun passaggio di rete, nessun parsing di protocollo, nessuna connessione client. Nei benchmark con un solo client, SQLite spesso supera Postgres sulle query semplici.

Aggiungi writer concorrenti, grandi insiemi di dati che richiedono l'esecuzione parallela delle query o piani di query complessi che traggono vantaggio dal planner maturo di Postgres, e Postgres passa in testa. Postgres scala anche verticalmente (macchine più grandi, più core) in modi per cui SQLite semplicemente non è progettato.

Il riassunto onesto: SQLite è veloce per ciò a cui serve. Postgres è veloce per ciò a cui serve. Scegli in base alla forma del carico di lavoro, non ai titoli dei benchmark.

Una guida rapida alla scelta

Usa SQLite quando:

• I dati vivono accanto a una sola applicazione: desktop, mobile, embedded, strumento da riga di comando.
• Le scritture arrivano da un solo processo o da pochi processi.
• Vuoi un deploy senza configurazione.
• Stai facendo un prototipo e vuoi concentrarti sullo schema, non sull'infrastruttura.

Usa Postgres quando:

• Più server applicativi o worker scrivono sul database.
• Ti serve accesso di rete da molti client.
• Ti servono funzioni avanzate: ruoli, replica, GIS, tipi personalizzati, stored procedure.
• I dati sono l'archivio centrale e durevole di un servizio in produzione.

Un percorso comune: parti con un piccolo progetto su SQLite e passa a Postgres se e quando la forma del traffico lo richiede. La migrazione non è gratis, ma è un'operazione nota, e la maggior parte dei progetti non ne ha mai bisogno.

Prossimo passo: quando SQLite è la scelta giusta

Il confronto qui sopra ti dà i compromessi. La prossima pagina approfondisce le ragioni a favore di SQLite: i carichi di lavoro in cui non è solo abbastanza buono ma è davvero lo strumento migliore, e i segnali che indicano che ne hai superato i limiti.