DuckDB o SQLite su un server: perché usare entrambi

DuckDB rispetto a SQLite su un server: la risposta in una frase

SQLite è un motore OLTP (online transaction processing): memorizza i dati come righe ed è progettato per leggere e scrivere poche righe alla volta in modo sicuro e rapido. DuckDB è un motore OLAP (online analytical processing): memorizza i dati come colonne ed è progettato per analizzare milioni di righe e restituire un singolo aggregato. Entrambi sono librerie embedded, entrambi aprono un file normale e nessuno dei due richiede un processo server da amministrare.

La risposta corretta alla domanda «quale scegliere?» è quindi quasi sempre «entrambi, sulla stessa VPS». L'applicazione mantiene lo stato operativo in SQLite. I report leggono i file Parquet e CSV con DuckDB. Non entrano in concorrenza perché svolgono attività diverse.

Perché lo storage per righe e quello per colonne cambiano la risposta

SQLite scrive una riga come un blocco contiguo all'interno di una pagina. Recuperare un ordine tramite la chiave primaria richiede l'accesso a una pagina dell'indice e a una pagina dati, quindi comporta due letture. È esattamente ciò che un'applicazione esegue migliaia di volte al secondo: leggere un utente, aggiornare una sessione, inserire un ordine.

DuckDB scrive ogni colonna separatamente e la comprime. Calcolare la somma di amount_cents su cinque milioni di righe richiede la lettura soltanto della colonna amount_cents; tutti gli altri byte del file vengono ignorati e la somma viene eseguita con codice vettorializzato su batch di valori. Le altre colonne non vengono mai lette dal disco: è da qui che deriva la velocità.

Ora esegui ciascun motore sul carico di lavoro dell'altro. Per calcolare la somma di una colonna, SQLite deve scorrere ogni riga e leggere dalla pagina l'intera riga per raggiungere un solo campo, quindi legge dal disco molti più dati del necessario. Per inserire un ordine, DuckDB deve modificare lo storage di ogni colonna per un singolo valore e acquisisce un blocco di scrittura sull'intero file del database. Nessuno dei due motori è difettoso. Ognuno risponde a una domanda per la quale non è stato progettato.

Dove SQLite è la scelta giusta: stato transazionale dell’applicazione

Scegli SQLite quando le scritture sono piccole, frequenti e non devono andare perse. Sessioni, ordini, righe di coda, impostazioni e qualsiasi dato creato da una richiesta web.

sudo apt update && sudo apt install -y sqlite3
sudo install -d -o "$USER" -g "$USER" /srv/app

Crea la tabella e abilita il write-ahead logging nella stessa operazione.

sqlite3 /srv/app/app.db <<'SQL'
PRAGMA journal_mode = WAL;
CREATE TABLE IF NOT EXISTS orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  placed_at    TEXT    NOT NULL,
  amount_cents INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS orders_customer ON orders (customer);
INSERT INTO orders (customer, placed_at, amount_cents)
  VALUES ('ana', '2026-07-30T09:14:00Z', 4200);
SQL

La prima riga dell’output è wal. È PRAGMA, che indica la modalità attivata, ed è l’impostazione più utile su un server. Nella modalità predefinita rollback journal, una scrittura blocca tutti i lettori. In modalità WAL, i lettori continuano a leggere l’ultimo stato sottoposto a commit mentre un unico writer aggiunge dati. Un report lento non blocca più la richiesta web che lo attende.

Verifica che la riga sia stata restituita:

sqlite3 /srv/app/app.db "SELECT * FROM orders WHERE customer = 'ana';"

Ottieni 1|ana|2026-07-30T09:14:00Z|4200. Accanto al database compaiono altri due file, app.db-wal e app.db-shm, che fanno entrambi parte del database. Copiare soltanto app.db mentre l’applicazione è in esecuzione produce un backup incoerente, come descritto più avanti.

SQLite consente comunque una sola scrittura alla volta. Questo limite è un lock, non una coda. Di conseguenza, un secondo writer che attende troppo a lungo termina con database is locked invece di bloccarsi indefinitamente. Aumenta il tempo di attesa con PRAGMA busy_timeout = 5000; su ogni connessione aperta dall’applicazione. Cinque secondi di attesa eliminano la maggior parte di questi errori in un normale carico web.

Dove DuckDB è efficace: analisi sui file già disponibili

Scegli DuckDB quando la domanda inizia con «quanti», «quanto» o «quali sono i primi dieci» e i dati sono distribuiti in numerosi file CSV o Parquet. Installa il client da riga di comando, alla versione 1.5.5 a luglio 2026:

curl https://install.duckdb.org | sh

Lo script installa il binario in ~/.duckdb/cli/latest/duckdb e stampa la riga che lo aggiunge a PATH. Verifica che venga eseguito:

~/.duckdb/cli/latest/duckdb :memory: "SELECT version();"

Crea un file realistico da interrogare. Questo comando scrive in Parquet cinque milioni di righe di ordini, con compressione zstd:

mkdir -p /srv/data
duckdb :memory: "
COPY (
  SELECT i                                                     AS id,
         'cust_' || (i % 5000)                                 AS customer,
         TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (i) MINUTE AS placed_at,
         (i * 37) % 20000                                      AS amount_cents
  FROM range(5000000) t(i)
) TO '/srv/data/orders.parquet' (FORMAT parquet, COMPRESSION zstd);"

Ora poni la domanda analitica. Apri la shell, abilita il timer e interroga direttamente il file senza una fase di importazione:

.timer on
SELECT customer,
       count(*)                AS orders,
       sum(amount_cents)/100.0 AS revenue
FROM '/srv/data/orders.parquet'
GROUP BY customer
ORDER BY revenue DESC
LIMIT 5;

Leggi il valore ottenuto in .timer invece di fidarti di un valore pubblicato, perché il risultato dipende dal disco e dal numero di core disponibili. È importante la struttura del risultato. Non c'erano CREATE TABLE, INSERT né una fase di caricamento: DuckDB ha letto il footer Parquet, ha determinato quali chunk di colonne servivano alla query e ha letto soltanto quelli. Una directory intera funziona nello stesso modo con un glob, FROM '/srv/data/orders-*.parquet', trasformando un mese di esportazioni giornaliere in una singola query.

La velocità del disco è il limite inferiore di tutto il processo e una scansione per colonne consiste in una lunga lettura sequenziale. Per questo, la differenza tra storage NVMe e SATA meno recenti su un VPS è più evidente qui che nelle piccole letture casuali di SQLite.

Lettura del database SQLite da DuckDB

I due motori comunicano tramite l'estensione sqlite di DuckDB. Collega il database dell'applicazione in sola lettura, in modo che una query analitica non possa mai modificare lo stato in produzione:

INSTALL sqlite;
LOAD sqlite;
ATTACH '/srv/app/app.db' AS app (TYPE sqlite, READ_ONLY);
SELECT customer, count(*) AS orders
FROM app.orders
GROUP BY customer
ORDER BY orders DESC;

In questo modo le righe vengono lette dal file SQLite al momento della query, senza creare copie. È una soluzione pratica, ma non veloce, perché i dati su disco sono ancora organizzati per righe e DuckDB deve attraversarli. Usala per l'esportazione, non per una dashboard che si aggiorna ogni trenta secondi:

COPY (SELECT * FROM app.orders)
  TO '/srv/data/orders-2026-07.parquet' (FORMAT parquet, COMPRESSION zstd);

Questa singola istruzione rappresenta l'intero schema. SQLite gestisce le righe recenti in produzione. Un'esportazione pianificata converte i periodi chiusi in Parquet. DuckDB risponde alle query che coprono più mesi, mentre il database dell'applicazione rimane di dimensioni ridotte e mantiene rapide le operazioni di scrittura.

Esegui l'esportazione in base a una pianificazione, invece di avviarla manualmente. Una coppia di servizio e timer systemd è la soluzione adatta: un'unità esegue COPY, mentre un timer la avvia ogni notte.

Eseguire entrambi sulla stessa VPS

Qui non servono container né porte. Entrambi i motori sono librerie, quindi l'installazione consiste in un pacchetto e in un percorso file. Se il resto dello stack è già eseguito tramite Docker Compose sulla stessa VPS, monta la directory dei dati nel container che ne ha bisogno invece di aggiungere un servizio database, perché non c'è alcun servizio da aggiungere.

Due regole evitano problemi in questa configurazione.

Assegna a ciascun motore una directory dedicata: /srv/app per il file SQLite scritto dall'applicazione e /srv/data per i file Parquet letti dal motore di analisi. Se condividono una directory, un job di backup che crea uno snapshot di uno dei due può entrare in conflitto con l'altro.

Non configurare due processi per usare lo stesso file di database DuckDB in modalità di lettura e scrittura. Un solo processo può mantenere aperto un file DuckDB per la scrittura; il secondo non riesce ad aprirlo. Sono invece possibili più lettori se ognuno imposta access_mode = 'READ_ONLY'. Questo comportamento sorprende chi proviene da SQLite, dove più processi condividono normalmente lo stesso file. Se il sistema di analisi legge soltanto file Parquet, il problema non si presenta. È un ulteriore motivo per mantenere lo stato persistente in SQLite.

I backup sono diversi e la differenza è importante

Un database SQLite in esecuzione è composto da tre file. Copiarli con cp durante una scrittura produce un file che si apre, ma contiene dati non corretti. Usare il comando di backup del motore, che acquisisce uno snapshot coerente mentre l'applicazione continua a scrivere:

sqlite3 /srv/app/app.db ".backup '/srv/backup/app-$(date -u +%Y%m%dT%H%M%SZ).db'"
sqlite3 /srv/backup/app-20260730T091400Z.db "PRAGMA integrity_check;"

integrity_check stampa ok quando la copia è corretta. Qualsiasi altro risultato indica che è necessario eliminare quello snapshot e crearne un altro.

I file Parquet non cambiano dopo la scrittura, quindi non richiedono una gestione speciale: è sufficiente eseguire il backup della directory. Inviare entrambi i percorsi fuori dal server con backup restic dal proprio VPS. L'intero livello dati consiste così in due directory incluse in un unico job di backup.

Modalità di errore e stringhe esatte visualizzate

Error: database is locked di SQLite indica che un'altra connessione ha mantenuto il lock di scrittura più a lungo del timeout configurato. Non indica una corruzione. Imposta PRAGMA busy_timeout su ogni connessione, quindi cerca una transazione lunga che avrebbe dovuto essere suddivisa in più transazioni brevi.

Error: unable to open database file dopo una modifica dei permessi indica in genere che il processo può scrivere nel file, ma non nella relativa directory. SQLite crea app.db-wal e app.db-shm accanto al database, quindi la directory deve essere scrivibile, non soltanto il file .db.

IO Error: Could not set lock on file di DuckDB indica che un secondo processo ha già aperto il database in scrittura. Chiudi l'altra shell oppure apri il database in sola lettura.

Out of Memory Error di DuckDB su un VPS di piccole dimensioni indica che una query richiedeva più memoria di quella disponibile. Quando possibile, DuckDB usa il disco per lo spill. Per consentirglielo, apri un file di database sul disco invece di usare :memory: e limita il consumo con SET memory_limit = '2GB';. Su un server che esegue altri servizi, questo limite impedisce a una query ad hoc di esaurire la RAM disponibile per l'applicazione.

Binder Error: Referenced column "amount" not found durante una query su Parquet indica quasi sempre che lo schema del file non corrisponde a quello previsto. Esegui DESCRIBE SELECT * FROM '/srv/data/orders.parquet'; e verifica i nomi effettivi delle colonne.

Come scegliere nella pratica

Chiedete quale sia il modello di scrittura. Molte scritture di piccole dimensioni che devono sopravvivere a un'interruzione dell'alimentazione indicano SQLite. Chiedete quale sia il modello di lettura. Scansioni complete con aggregazioni su uno storico esteso indicano DuckDB. La maggior parte dei sistemi reali risponde affermativamente a entrambe le domande. La scelta corretta consiste quindi nell'assegnare a ciascun motore il compito per cui è più adatto, invece di obbligare uno dei due a svolgere anche il lavoro dell'altro.

La migrazione da evitare consiste nello spostare lo stato applicativo in uso in DuckDB perché un report era lento. Il report era lento a causa dell'organizzazione dei dati su disco. La correzione consiste quindi in un'esportazione, non nella riscrittura del percorso di scrittura.

FAQ

DuckDB può sostituire SQLite come database dell'applicazione?

Non se l'applicazione esegue scritture frequenti. DuckDB acquisisce un blocco di scrittura sull'intero file del database, consente un solo processo di lettura e scrittura alla volta ed è ottimizzato per modifiche in blocco, non per gli inserimenti di singole righe. Mantieni lo stato transazionale in SQLite e lascia che DuckDB lo legga con ATTACH '/srv/app/app.db' AS app (TYPE sqlite, READ_ONLY); quando serve un report.

DuckDB è davvero più veloce di SQLite per l'analisi dei dati?

Sì, per le scansioni e le aggregazioni su una tabella di grandi dimensioni. Il motivo è il layout di archiviazione, non un semplice accorgimento di ottimizzazione. DuckDB legge soltanto le colonne richieste dalla query ed elabora i valori in batch, mentre SQLite deve scorrere intere righe per raggiungere un campo. Per recuperare una singola riga tramite la chiave primaria il rapporto si inverte, perché SQLite accede a due pagine mentre DuckDB accede allo storage di ogni colonna.

Serve molta RAM per eseguire DuckDB su un VPS?

No, ma assegna un limite e uno spazio su disco. Apri un file di database invece di :memory:, così DuckDB può riversare su disco i risultati intermedi, quindi imposta SET memory_limit = '2GB'; su un valore che il tuo VPS può riservare. Senza un limite, un GROUP BY di grandi dimensioni può causare Out of Memory Error o espellere dalla RAM altri servizi.

Come trasferisco i dati SQLite in Parquet?

Collega il file SQLite da DuckDB e copia direttamente una query con COPY (SELECT * FROM app.orders) TO '/srv/data/orders.parquet' (FORMAT parquet, COMPRESSION zstd);. Esegui questa operazione in base a una pianificazione per i periodi chiusi, ad esempio le righe del mese scorso, e lascia le righe recenti in SQLite, dove l'applicazione continua a scriverle.

Quale dei due devo sottoporre a backup e come?

Entrambi, ma in modi diversi. Crea snapshot di SQLite con sqlite3 app.db ".backup '/srv/backup/app.db'" invece di cp, perché un database in esecuzione è anche un -wal e un file -shm, quindi una copia semplice può risultare incoerente. I file Parquet non cambiano dopo la scrittura, quindi è sufficiente copiare la directory.