DuckDB vs SQLite on a server

DuckDB vs SQLite on a server: the one-sentence answer

SQLite is an OLTP engine (online transaction processing): it stores data as rows and it is built to read and write a few of them at a time, safely and fast. DuckDB is an OLAP engine (online analytical processing): it stores data as columns and it is built to scan millions of rows and hand back one aggregate. Both are embedded libraries, both open a plain file, and neither runs a server process you have to babysit.

So the honest answer to "which one" is almost always "both, on the same VPS". Your application keeps its live state in SQLite. Your reporting reads Parquet and CSV files with DuckDB. They do not compete because they are not doing the same job.

Why row storage and column storage change the answer

SQLite writes a row as one contiguous piece of a page. Fetching one order by its primary key touches one index page and one data page, which is two reads. That is exactly what an application does thousands of times a second: read this user, update this session, insert this order.

DuckDB writes each column separately and compresses it. Summing amount_cents over five million rows reads only the amount_cents column, skips every other byte in the file, and runs the sum through vectorised code over batches of values. The other columns are never read from disk, which is where the speed comes from.

Now run each engine against the other's workload. SQLite summing a column has to walk every row and pull the whole row off the page to reach one field, so it reads far more disk than it needs. DuckDB inserting one order has to touch every column's storage for a single value, and it takes a write lock on the whole database file to do it. Neither engine is broken. Each is answering a question it was not shaped for.

Where SQLite wins: transactional application state

Pick SQLite when writes are small, frequent, and must not be lost. Sessions, orders, queue rows, settings, anything a web request creates.

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 in 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 is wal. That is the PRAGMA reporting the mode it switched to, and it is the most useful setting on a server. In the default rollback journal mode a writer blocks every reader. In WAL mode readers keep reading the last committed state while one writer appends, so a slow report no longer stalls the web request behind it.

Check the row came back:

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

You get 1|ana|2026-07-30T09:14:00Z|4200. Two more files appeared next to the database, app.db-wal and app.db-shm, and both belong to it. Copying app.db alone while the application is running gives you a torn backup, which is covered further down.

SQLite still allows one writer at a time. That limit is a lock, not a queue, so a second writer that waits too long fails with database is locked instead of blocking forever. Raise the wait with PRAGMA busy_timeout = 5000; on every connection your application opens. Five seconds of patience removes most of these errors on a normal web workload.

Where DuckDB wins: analytics over files you already have

Pick DuckDB when the question starts with "how many", "how much" or "which top ten", and the input is a pile of 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 installs the binary under ~/.duckdb/cli/latest/duckdb and prints the line that puts it on your PATH. Confirm it runs:

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

Build a realistic file to query. This writes 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 the timer on, and query the file directly with no 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 off .timer rather than trusting a published one, because the result depends on your disk and your core count. The shape of it is what matters. There was no CREATE TABLE, no INSERT and no load step: DuckDB read the Parquet footer, worked out which column chunks the query needed, and read only those. A whole directory works the same way with a glob, FROM '/srv/data/orders-*.parquet', which is how a month of daily exports becomes one query.

Disk speed is the floor under all of this, and a column scan is a long sequential read, so the gap between NVMe and older SATA storage on a VPS shows up here more clearly than it does under SQLite's small random reads.

Reading your SQLite database from DuckDB

The two engines meet through DuckDB's sqlite extension. Attach the application database read only, so an analytics query can never 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 reads rows out of the SQLite file at query time with no copy. It is convenient and it is not fast, because the data on disk is still row storage and DuckDB has to walk it. Use it for the export, not for a dashboard that reloads every thirty seconds:

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

That one statement is the whole pattern. SQLite owns the recent live rows. A scheduled export turns closed periods into Parquet. DuckDB answers every question that spans months, and the application database stays small, which keeps its writes fast.

Run the export on a schedule rather than by hand. A systemd service and timer pair is the right size for this: one unit that runs the COPY, one timer that fires it nightly.

Running both on one VPS

Nothing here needs a container and nothing needs a port. Both engines are libraries, so the install is a package and a file path. If the rest of your stack already runs under Docker Compose on the same VPS, mount the data directory into the container that needs it instead of adding a database service, because there is no service to add.

Two rules keep this arrangement out of trouble.

Give each engine its own directory: /srv/app for the SQLite file the application writes, /srv/data for the Parquet files analytics reads. When they share a directory, a backup job that snapshots one ends up racing the other.

Do not point two processes at one DuckDB database file in read-write mode. Only one process may hold a DuckDB file for writing, and the second one fails to open it at all. Many readers are fine when every one of them sets access_mode = 'READ_ONLY'. This surprises people arriving from SQLite, where several processes share a file routinely. If your analytics only reads Parquet files, the question never comes up, which is one more reason to keep durable state in SQLite.

Backups differ, and the difference bites

A running SQLite database is three files, and copying them with cp mid-write gives you a file that opens and is wrong. Use the engine's own backup command, which takes a consistent snapshot while the application keeps writing:

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 prints ok on a good copy. Anything else means throw that snapshot away and take another.

Parquet files never change after they are written, so they need no special handling: back up the directory. Send both paths off the server with restic backups from your VPS, and the whole data layer is two directories in one backup job.

Failure modes and the exact strings you will see

Error: database is locked from SQLite means another connection held the write lock longer than your timeout allowed. It is not corruption. Set PRAGMA busy_timeout on every connection, then look for a long transaction that should have been several short ones.

Error: unable to open database file after a permission change usually means the process can write the file but not its directory. SQLite creates app.db-wal and app.db-shm next to the database, so the directory itself must be writable, not only the .db file.

IO Error: Could not set lock on file from DuckDB means a second process already holds that database open for writing. Close the other shell, or open yours read only.

Out of Memory Error from DuckDB on a small VPS means a query needed more working memory than it had. DuckDB spills to disk when it can, so give it somewhere to spill by opening a database file on disk instead of :memory:, and cap its appetite with SET memory_limit = '2GB';. On a box running other services, that limit is what stops an ad hoc query from pushing your application out of RAM.

Binder Error: Referenced column "amount" not found when querying Parquet nearly always means the file's schema is not the one you remember. Run DESCRIBE SELECT * FROM '/srv/data/orders.parquet'; and read the real column names back.

How to choose in practice

Ask what the write pattern is. Many small writes that must survive a power cut means SQLite. Ask what the read pattern is. Full scans with aggregates over a long history means DuckDB. Most real systems answer yes to both questions, and the right response is to give each engine the half it is good at rather than forcing one of them to cover the other.

The migration to avoid is moving live application state into DuckDB because a report was slow. The report was slow because of storage layout, so the fix is an export, not a rewrite of your write path.

FAQ

Can DuckDB replace SQLite for my application database?

Not for one that writes often. DuckDB takes a write lock over the whole database file, allows a single read-write process at a time, and is tuned for bulk changes rather than single-row inserts. Keep transactional state in SQLite and let DuckDB read it with ATTACH '/srv/app/app.db' AS app (TYPE sqlite, READ_ONLY); when a report needs it.

Is DuckDB really faster than SQLite for analytics?

For scans and aggregates over a large table, yes, and the reason is the storage layout rather than a tuning trick. DuckDB reads only the columns a query names and processes values in batches, while SQLite must walk whole rows to reach one field. For fetching a single row by primary key the order reverses, because SQLite touches two pages and DuckDB touches every column's storage.

Do I need a lot of RAM to run DuckDB on a VPS?

No, but give it a limit and a disk. Open a database file rather than :memory: so DuckDB can spill intermediate results to disk, then set SET memory_limit = '2GB'; to a value your VPS can spare. Without a limit, one large GROUP BY can raise Out of Memory Error or push other services out of RAM.

How do I get my SQLite data into Parquet?

Attach the SQLite file from DuckDB and copy a query straight out with COPY (SELECT * FROM app.orders) TO '/srv/data/orders.parquet' (FORMAT parquet, COMPRESSION zstd);. Run it on a schedule for closed periods, such as last month's rows, and leave recent rows in SQLite where the application still writes them.

Which one should I back up, and how?

Both, in different ways. Take SQLite snapshots with sqlite3 app.db ".backup '/srv/backup/app.db'" instead of cp, because a running database is also a -wal and a -shm file and a plain copy can be torn. Parquet files never change once written, so copying the directory is enough.