DuckDB o SQLite su un server: perché servono entrambi

DuckDB vs SQLite su un server: la risposta in una frase

SQLite è un motore OLTP (elaborazione delle transazioni online): archivia i dati come righe ed è progettato per leggere e scrivere poche righe alla volta, in modo sicuro e rapido. DuckDB è un motore OLAP (elaborazione analitica online): archivia i dati come colonne ed è progettato per analizzare milioni di righe e restituire un unico valore aggregato. Entrambi sono librerie integrate, entrambi aprono un semplice file e nessuno dei due esegue un processo server da gestire.

La risposta corretta alla domanda «quale dei due?» è quasi sempre «entrambi, sullo stesso VPS». L'applicazione mantiene lo stato corrente in SQLite. I report leggono i file Parquet e CSV con DuckDB. Non sono in concorrenza perché non svolgono lo stesso compito.

Perché l'archiviazione per righe e per colonne cambia la risposta

SQLite scrive una riga come un blocco contiguo di una pagina. Il recupero di un ordine tramite la chiave primaria coinvolge una pagina di indice e una pagina dati, per un totale di due letture. È esattamente ciò che un'applicazione esegue migliaia di volte al secondo: leggere questo utente, aggiornare questa sessione, inserire questo ordine.

DuckDB scrive ogni colonna separatamente e la comprime. Il calcolo della somma di amount_cents su cinque milioni di righe legge solo la colonna amount_cents, ignora ogni altro byte nel file ed esegue la somma tramite codice vettorializzato su batch di valori. Le altre colonne non vengono mai lette dal disco, ed è da qui che deriva la velocità.

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

Dove SQLite è vantaggioso: 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 tutte le letture. In modalità WAL, i lettori continuano a leggere lo stato dell'ultimo commit mentre un 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 sono comparsi altri due file, app.db-wal e app.db-shm, che fanno entrambi parte del database. Copiare solo app.db mentre l'applicazione è in esecuzione produce un backup incoerente. Questo aspetto è descritto più avanti.

SQLite consente comunque un solo writer alla volta. Questo limite è imposto da un lock, non da 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 dei file già disponibili

Scegli DuckDB quando la domanda inizia con "quanti", "quanto" o "quali sono i primi dieci", e l'input è un insieme di file CSV o Parquet. Installa il client a riga di comando, versione 1.5.5 a luglio 2026:

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

Lo script installa il binario in ~/.duckdb/cli/latest/duckdb e visualizza 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 cinque milioni di righe di ordini in formato Parquet, 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, attiva 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 da .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 blocchi di colonne servivano alla query e ha letto solo quelli. Un'intera directory 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 minimo di tutto il processo e una scansione per colonne è una lunga lettura sequenziale. Per questo, la differenza tra lo storage NVMe e il vecchio storage SATA 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, così una query analitica non potrà mai scrivere nello stato operativo:

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;

Questo legge le righe dal file SQLite al momento della query, senza creare una copia. È pratico, ma non è veloce, perché i dati su disco sono ancora archiviati per riga e DuckDB deve scorrerli. Usalo per l'esportazione, non per una dashboard che si ricarica ogni trenta secondi:

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

Questa istruzione contiene l'intero schema. SQLite gestisce le righe operative recenti. Un'esportazione pianificata converte i periodi chiusi in Parquet. DuckDB risponde a ogni domanda che copre più mesi e il database dell'applicazione rimane di piccole dimensioni, mantenendo rapide le scritture.

Esegui l'esportazione in base a una pianificazione anziché manualmente. Una coppia di unità systemd, composta da un servizio e da un timer è adatta a questo scopo: un'unità esegue COPY, mentre un timer la avvia ogni notte.

Eseguire entrambi su un singolo VPS

Qui non servono container né porte. Entrambi i motori sono librerie, quindi l'installazione consiste in un pacchetto e in un percorso di file. Se il resto dello stack è già in esecuzione con Docker Compose sullo stesso VPS, monta la directory dei dati nel container che ne ha bisogno invece di aggiungere un servizio di 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 dall'analisi. Se condividono una directory, un job di backup che crea uno snapshot di uno dei due finisce per entrare in conflitto con l'altro.

Non indirizzare due processi allo stesso file di database DuckDB in modalità di lettura-scrittura. Un solo processo può mantenere aperto un file DuckDB per la scrittura; il secondo non riesce ad aprirlo. Sono consentiti più lettori quando ciascuno imposta access_mode = 'READ_ONLY'. Questo comportamento sorprende chi proviene da SQLite, dove più processi condividono normalmente un file. Se l'analisi legge solo file Parquet, il problema non si pone, ed è un motivo in più per mantenere lo stato persistente in SQLite.

I backup differiscono e la differenza è un problema

Un database SQLite in esecuzione è composto da tre file. Copiarli con cp durante una scrittura produce un file che si apre, ma contiene dati errati. Usa il comando di backup del motore. Questo comando 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 è valida. Qualsiasi altro risultato indica che devi eliminare quello snapshot e crearne un altro.

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

Modalità di errore e stringhe esatte visualizzate

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

Error: unable to open database file dopo una modifica delle autorizzazioni 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 solo il file .db.

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

Out of Memory Error da DuckDB su un VPS di dimensioni ridotte significa che una query richiedeva più memoria di lavoro di quella disponibile. DuckDB usa il disco come memoria temporanea quando possibile. Per consentirgli di farlo, apri un file di database sul disco invece di :memory: e limita il consumo con SET memory_limit = '2GB';. Su un sistema che esegue altri servizi, questo limite impedisce a una query ad hoc di esaurire la RAM dell'applicazione.

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

Come scegliere in pratica

Chiediti quale sia il modello di scrittura. Molte piccole scritture che devono sopravvivere a un'interruzione di corrente indicano SQLite. Chiediti quale sia il modello di lettura. Scansioni complete con aggregazioni su una cronologia lunga indicano DuckDB. Nella maggior parte dei sistemi reali, la risposta è affermativa a entrambe le domande. La scelta corretta è quindi assegnare a ciascun motore la parte per cui è più adatto, invece di costringere uno dei due a sostituire l'altro.

La migrazione da evitare consiste nello spostare lo stato attivo dell'applicazione in DuckDB perché un report era lento. Il report era lento a causa del layout dello storage. La soluzione è quindi un'esportazione, non una 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 le analisi?

Sì, per le scansioni e le aggregazioni su una tabella di grandi dimensioni. Il motivo è il layout di archiviazione, non un accorgimento di ottimizzazione. DuckDB legge solo le colonne indicate dalla query ed elabora i valori in batch, mentre SQLite deve attraversare intere righe per raggiungere un campo. Per recuperare una singola riga tramite la chiave primaria, il risultato si inverte: 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 usa un disco. Apri un file di database invece di :memory:, in modo che DuckDB possa riversare su disco i risultati intermedi, quindi imposta SET memory_limit = '2GB'; su un valore che il tuo VPS possa 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 l'operazione secondo 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, con modalità diverse. 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, e una semplice copia può risultare incoerente. I file Parquet non cambiano dopo la scrittura, quindi è sufficiente copiare la directory.