DuckDB vs SQLite for server: why VPS dey use both

DuckDB vs SQLite for server: answer wey fit enter one sentence

SQLite na an 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 an OLAP engine (online analytical processing): e dey store data as columns, and dem build am to scan millions of rows then return one aggregate. Both na embedded libraries, both fit open ordinary file, and neither one dey run server process wey you must babysit.

So, honest answer to “which one” almost always na “both, for the same VPS”. Your application go keep e 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 dey change the answer

SQLite dey write one row as one continuous piece for a page. To fetch one order by primary key, e go touch one index page and one data page. That na two reads. Na exactly this kind thing 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 across five million rows, e only read the amount_cents column, skip every other byte for the file, then run the sum with vectorised code across batches of values. E no ever read the other columns from disk. Na from there the speed dey come.

Now run each engine with the other engine workload. To sum one column with SQLite, e must go through every row and pull the complete row from the page before e fit reach that field. So e dey read much more data from disk than e need. To insert one order with DuckDB, e must touch the storage for every column for just one value. E also take write lock for the whole database file to do am. No be say either engine spoil. Each one dey answer a question wey e no design for.

Where SQLite win: transactional application state

Choose SQLite when writes dey small, frequent, and dem no suppose lost. Sessions, orders, queue rows, settings, anything wey web request 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 wey 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 append, so slow report no go hold the web request wey dey behind am.

Check say the row come back:

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

You go get 1|ana|2026-07-30T09:14:00Z|4200. Two more files don appear beside 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 allow only one writer at a time. That limit na lock, no be queue, so second writer wey wait too long go fail with database is locked instead of blocking forever. Increase the wait with PRAGMA busy_timeout = 5000; for every connection wey your application open. Five seconds patience dey remove most of these errors for normal web workload.

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

Pick DuckDB when di question start with "how many", "how much" or "which top ten", and di input na plenty CSV or Parquet files. Install di command line client, version 2.0.0 (an LTS release) as of 27 October 2026:

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

Di script go install di latest stable release unless you pin one, for example curl https://install.duckdb.org | DUCKDB_VERSION=1.5.6 sh to remain on di previous release. E go install di binary under ~/.duckdb/cli/latest/duckdb and print di line wey go put am for your PATH. Confirm say e dey run:

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

Build one realistic file wey you fit query. Dis one go 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 di analytical question. Open di shell, turn on di timer, and query di file directly without 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 published number, because di result depend on your disk and di number of cores wey you get. Na di shape of di result matter. No CREATE TABLE, no INSERT and no load step happen: DuckDB read di Parquet footer, work out which column chunks di query need, then read only dem ones. Whole directory work di same way with a glob, FROM '/srv/data/orders-*.parquet', and na so one month of daily exports become one query.

Disk speed na di floor for all dis, and column scan na one long sequential read, so di difference between NVMe and older SATA storage on a VPS show more clearly here than e dey show under SQLite small random reads.

Reading your SQLite database from DuckDB

DuckDB dey connect with the SQLite database 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 dey read rows from the SQLite file when query run, without making copy. E convenient, but e no fast, because data for disk still dey row storage and DuckDB must scan am. Use am for export, no be for dashboard wey dey 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 hold the recent live rows. Scheduled export dey turn closed periods to Parquet. DuckDB dey answer every question wey cover many months, while the application database remain small. This one keep its writes fast.

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

Dem dey run both for one VPS

Nothing here need container and nothing need port. Both engines na libraries, so installation na package and 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 keep this arrangement out of trouble.

Give each engine im own directory: /srv/app for the SQLite file wey application dey write, and /srv/data for the Parquet files wey analytics dey read. When dem share one directory, backup job wey dey snapshot one fit race with 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. Plenty readers dey okay when each one set access_mode = 'READ_ONLY'. This one dey 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 na another reason to keep durable state for SQLite.

Backup no dey the same, and this 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 go 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 make you throw that snapshot away 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 go be two directories inside one backup job.

Failure modes and di exact strings wey you go see

database is locked from SQLite, whether e come through your application driver or di sqlite3 shell, mean say another connection hold di write lock longer than your timeout allow. E no be corruption. Set PRAGMA busy_timeout for every connection, then find di long transaction wey suppose don be several short ones.

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

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

Out of Memory Error from DuckDB on small VPS mean say di query need more working memory than di system get. DuckDB dey spill intermediate results to disk by default, and dis include a :memory: session, so opening a database file no be wetin make spilling work. E dey write those temporary files to a .tmp directory beside di database file, or, for an in-memory session, a .tmp directory relative to where you start am. So start am for somewhere wey writable and get free disk, or point am elsewhere with SET temp_directory = '/var/tmp/duckdb';. Di setting wey matter na di cap: SET memory_limit = '2GB';. Di default na 80% of di machine RAM, so for a box wey dey run other services, na dis limit dey stop an ad hoc query from pushing your application comot from RAM. Some aggregates, like string_agg(), no fit spill. Some sorting-based aggregates no fit spill too, so query wey depend on dem fit still run out of memory.

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

How you fit choose am for real life

Ask wetin the write pattern be. If na plenty small writes wey must survive power cut, use SQLite. Ask wetin the read pattern be. If na full scans with aggregates over long history, use DuckDB. Most real systems go answer yes to both questions. The correct response na to give each engine the part wey e good at, instead of forcing one engine to do the other engine own work.

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

FAQ

DuckDB fit replace SQLite for my application database?

No, if the application 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 dey faster than SQLite for analytics?

For scans and aggregates over large 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 walk 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 enough space to spill. DuckDB dey spill intermediate results to disk by default, even inside :memory: session, as long as temporary directory writable and e get free space. Then set SET memory_limit = '2GB'; to value wey your VPS fit spare. If you leave the default at 80% of RAM, one large GROUP BY fit raise Out of Memory Error or push other services comot from RAM.

How I fit move my SQLite data enter Parquet?

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

Which one I suppose back up, and how?

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