DuckDB vs SQLite for Server: Why You Need Both

DuckDB vs SQLite for server: di one-sentence answer

SQLite na OLTP engine (online transaction processing): e dey store data as rows, and dem build am to read and write small number of rows at once, safely and fast. DuckDB na OLAP engine (online analytical processing): e dey store data as columns, and dem build am to scan millions of rows and return one aggregate. Both na embedded libraries, both fit open plain file, and none of dem dey run server process wey you need dey monitor.

So, honest answer to "which one" almost always na "both, for the same VPS". Your application go keep im live state for SQLite. Your reporting go read Parquet and CSV files with DuckDB. Dem no dey compete because dem no dey do the same work.

Why row storage and column storage change the answer

SQLite dey write one row as one continuous piece of a page. To fetch one order with its primary key, e go touch one index page and one data page. Na two reads be that. Na exactly wetin application dey do thousands of times every second: read this user, update this session, insert this order.

DuckDB dey write each column separately and compress am. To sum amount_cents over five million rows, e go read only amount_cents column, skip every other byte for the file, then run the sum with vectorised code over batches of values. E no go ever read the other columns from disk. Na from there the speed dey come.

Now run each engine with the workload wey belong to the other one. If SQLite dey sum one column, e must pass through every row and remove the whole row from the page before e fit reach that field. So e dey read much more disk than e need. If DuckDB dey insert one order, e must touch the storage for every column for just one value, and e dey take write lock on the whole database file to do am. No engine get fault. Each one dey answer question wey e no design for.

Where SQLite wins: transactional application state

Choose SQLite when writes dey small, frequent, and dem no suppose lost. Sessions, orders, queue rows, settings, and anything wey web request dey create.

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

Create the table and turn on write-ahead logging for the same step.

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

The first line of output na wal. Na PRAGMA dey report the mode wey e switch to, and na the most useful setting for server. For the default rollback journal mode, one writer dey block every reader. For WAL mode, readers continue to read the last committed state while one writer dey append. So, slow report no longer dey delay the web request behind am.

Check say the row return:

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

You get 1|ana|2026-07-30T09:14:00Z|4200. Two more files show next to the database: app.db-wal and app.db-shm, and both belong to am. If you copy only app.db while the application dey run, you go get incomplete backup. We cover this further down.

SQLite still allows only one writer at a time. This limit na lock, no be queue. So, if second writer wait too long, e fails with database is locked instead of blocking forever. Increase the wait with PRAGMA busy_timeout = 5000; for every connection wey your application opens. Five seconds patience dey remove most of these errors for normal web workload.

Where DuckDB dey win: analytics over files wey you already get

Choose DuckDB when the question start with "how many", "how much" or "which top ten", and the input na plenty CSV or Parquet files. Install the command line client, version 1.5.5 as of July 2026:

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

The script install the binary under ~/.duckdb/cli/latest/duckdb and print the line wey go put am for your PATH. Confirm say e dey run:

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

Build one realistic file to query. This one write five million rows of orders to Parquet, compressed with 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);"

Now ask the analytical question. Open the shell, turn on the timer, and query the file directly without any import step:

.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;

Read your own number from .timer instead of trusting one wey dem publish, because the result depend on your disk and how many cores you get. Na the shape of the result matter. No CREATE TABLE, no INSERT and no load step happen: DuckDB read the Parquet footer, work out which column chunks the query need, and read only those ones. Whole directory work the same way with a glob, FROM '/srv/data/orders-*.parquet', and na so one month of daily exports become one query.

Disk speed na the base for all this, and column scan na one long sequential read. So, the difference between NVMe and older SATA storage for a VPS show more clearly here than e dey show under SQLite small random reads.

Reading your SQLite database from DuckDB

You fit two engines through DuckDB's sqlite extension. Attach the application database as read only, so analytics query no fit write to live state:

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;

This one reads rows from the SQLite file when query dey run, without making copy. E convenient, but e no fast, because data for disk still dey row storage and DuckDB must waka through am. Use am for export, no use am for dashboard wey reload every thirty seconds:

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

That one statement na the complete pattern. SQLite dey control recent live rows. Scheduled export dey turn closed periods into Parquet. DuckDB dey answer every question wey cross months, and application database stay small, so e writes fast.

Run the export by schedule instead of doing am by hand. A systemd service and timer pair fit this work well: one unit wey runs the COPY, and one timer wey triggers am every night.

One VPS dey run both together

Nothing for here need container, and nothing need port. Both engines na libraries, so installation na package plus file path. If the rest of your stack already dey run under Docker Compose for the same VPS, mount the data directory inside the container wey need am instead of adding database service, because no service dey to add.

Two rules go help make this arrangement no cause problem.

Give each engine its own directory: /srv/app for the SQLite file wey application dey write, /srv/data for the Parquet files wey analytics dey read. If dem share one directory, backup job wey dey take snapshot of one fit end up dey race the other.

No point two processes to one DuckDB database file for read-write mode. Na only one process fit hold DuckDB file for writing, and the second one go fail to open am at all. Many readers dey okay when each one set access_mode = 'READ_ONLY'. This one fit surprise people wey come from SQLite, where several processes dey routinely share one file. If your analytics only dey read Parquet files, this question no go come up, and this na another reason to keep durable state for SQLite.

Backups dey differ, and difference dey cause problem

SQLite database wey dey run na three files, and if you copy dem with cp while write operation still dey happen, you go get file wey fit open but wrong. Use the engine own backup command. E dey take consistent snapshot while application still dey write:

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 dey print ok when copy good. Anything else mean say discard that snapshot and take another one.

Parquet files no dey change after dem don write am, so dem no need special handling: back up the directory. Send both paths comot from the server with restic backups from your VPS, and the whole data layer na two directories inside one backup job.

Failure modes and the exact strings wey you go see

Error: database is locked from SQLite mean say another connection hold the write lock pass the timeout wey you allow. E no be corruption. Set PRAGMA busy_timeout for every connection, then find long transaction wey suppose don be several short ones.

Error: unable to open database file after permission change usually mean say the process fit write the file, but e no fit write the directory. SQLite dey create app.db-wal and app.db-shm beside the database, so the directory itself must dey writable, no be only the .db file.

IO Error: Could not set lock on file from DuckDB mean say another process don already open that database for writing. Close the other shell, or open your own read only.

Out of Memory Error from DuckDB on small VPS mean say query need more working memory than e get. DuckDB dey spill to disk when e fit, so give am place to spill by opening database file for disk instead of :memory:, and cap wetin e fit use with SET memory_limit = '2GB';. For box wey dey run other services, na that limit dey stop ad hoc query from pushing your application comot from RAM.

Binder Error: Referenced column "amount" not found when you dey query Parquet almost always mean say the file schema no be the one wey you remember. Run DESCRIBE SELECT * FROM '/srv/data/orders.parquet'; and read the real column names wey e return.

How to choose in practice

Ask wetin di write pattern be. Plenty small writes wey suppose survive power cut mean say na SQLite. Ask wetin di read pattern be. Full scans with aggregates across long history mean say na DuckDB. Most real systems answer yes to both questions. Di correct response na to give each engine di part wey e good for, instead of forcing one engine to handle di other engine own.

Di migration wey you suppose avoid na moving live application state enter DuckDB because one report slow. Di report slow because of storage layout. So, di fix na export, no be rewrite of your write path.

FAQ

DuckDB fit replace SQLite for my application database?

No be for database wey dey write often. DuckDB dey take write lock for the whole database file, e allow only one read-write process at a time, and dem tune am for bulk changes instead of single-row inserts. Keep transactional state for SQLite, then make DuckDB read am with ATTACH '/srv/app/app.db' AS app (TYPE sqlite, READ_ONLY); when report need am.

DuckDB really faster pass SQLite for analytics?

For scans and aggregates over big table, yes. The reason na the storage layout, no be tuning trick. DuckDB dey read only the columns wey query name and process values in batches, while SQLite must waka through complete rows before e reach one field. For fetching one row by primary key, the order reverse, because SQLite touch two pages while DuckDB touch the storage for every column.

I need plenty RAM to run DuckDB for VPS?

No, but give am limit and disk. Open database file instead of :memory: so DuckDB fit spill intermediate results go disk, then set SET memory_limit = '2GB'; to value wey your VPS fit spare. Without limit, one big GROUP BY fit raise Out of Memory Error or push other services comot from RAM.

How I fit move my SQLite data go Parquet?

Attach the SQLite file from DuckDB and copy query straight out with COPY (SELECT * FROM app.orders) TO '/srv/data/orders.parquet' (FORMAT parquet, COMPRESSION zstd);. Run am on schedule for closed periods, like rows from last month, and leave recent rows for SQLite where the application still dey write dem.

Which one I suppose back up, and how?

Both, but each one get different method. Take SQLite snapshots with sqlite3 app.db ".backup '/srv/backup/app.db'" instead of cp, because database wey dey run na also -wal and -shm file, and plain copy fit tear. Parquet files no dey change after dem write am, so copying the directory dey enough.