SQLite for production on one VPS: e good?

When SQLite na the right production database for a VPS

Running SQLite for production on a VPS na the right choice for plenty small applications, and the reason simple: one process for one machine wey dey write to one file no need database server. No daemon to supervise, no port to put for firewall, no password to rotate, and no second machine to keep alive. Query na function call instead of network round trip, so page wey run forty queries dey cost forty function calls.

The cost get clear limit, and e real. SQLite allow only one writer at a time for the whole database file, and two machines no fit share the file. Both limits dey okay for one VPS wey dey run one application. But both become fatal once your application pass that shape. This guide cover settings wey make SQLite safe for server, continuous backup with Litestream, and the point wey you suppose stop.

Install the command line tool first. Everything for this guide run on Ubuntu 24.04.

sudo apt update
sudo apt install -y sqlite3
sqlite3 --version

That one go print version wey start with 3., followed by build date and source hash. As of July 2026, Ubuntu 24.04 dey ship SQLite 3.45.1. Your application likely no dey use this binary: most language runtimes dey bundle their own copy of SQLite library, often newer one. So check the version wey your database driver report before you depend on any recent feature.

Why WAL mode na the first thing wey you change

By default, SQLite dey use rollback journal. Before e change one page, e copy the original page enter one -journal file, then e edit the database in place. To do this safely, e take exclusive lock on the whole file. So every reader go wait while any write dey happen. For laptop, person no go notice am. For web server, one slow write go delay every request wey touch the database.

WAL (write-ahead log) mode dey reverse this order. Writer dey append the new pages to separate -wal file and e leave the main database as e dey. Readers continue to read the main file from the snapshot wey dem start with. So readers no dey block writer, and writer no dey block readers. Later, checkpoint go copy the WAL pages wey don accumulate back into the main database. Na this one change mostly make SQLite usable behind web application.

WAL mode on o confirm say e persist

mkdir -p ~/app
sqlite3 ~/app/app.db "PRAGMA journal_mode=WAL;"

The command go print wal. That output no be decoration. PRAGMA journal_mode dey return the mode wey database dey actually use, so if e reply delete, the change fail and you still dey use rollback journal.

WAL mode dey persist. E dey stored for database header, no be connection setting. So, you run am once for each database file, and every connection wey come after go inherit am, including after reboot. Prove am with fresh connection.

sqlite3 ~/app/app.db "PRAGMA journal_mode;"

Now create table and check wetin show for disk.

sqlite3 ~/app/app.db <<'SQL'
CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes (body) VALUES ('first row');
SQL
ls -l ~/app/

Three files dey now: app.db, app.db-wal and app.db-shm. -wal file dey hold committed pages wey never undergo checkpoint yet. -shm file na shared memory index wey every connection maps, so all of dem fit agree on wetin WAL contain. Both files belong to the database; dem no be scratch files. If you copy app.db alone while application dey run, you go get file wey no get every recent commit. If you delete app.db and leave the other two files, SQLite go apply those stale WAL pages to any new file wey later appear with that name. Na so people dey corrupt fresh database when dem dey try reset one.

Settings wey every production app need for connection

Na only journal_mode dey stored for database. Every other setting below na per connection. This mean say your application must run am for every connection wey e open, including every connection wey connection pool create for background.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

busy_timeout = 5000 tell SQLite make e keep retrying locked database for up to 5000 milliseconds before e return database is locked. Default na 0. So by default, SQLite fail immediately the first time two writers overlap. If you set this one value, e go remove most lock errors wey people dey blame on SQLite itself.

synchronous = NORMAL na the correct setting for WAL mode, and you need understand the trade-off. For FULL, SQLite dey call fsync for WAL on every commit. For NORMAL, e dey sync during checkpoints instead. SQLite documentation talk plainly about wetin you sacrifice: transactions no longer durable after power failure or hard reset. That power loss no go corrupt the database. You go only lose the last commits wey never reach disk. For VPS, this trade-off usually make sense because e remove one fsync from every single write path.

foreign_keys = ON dey off by default for backwards compatibility, and e dey apply per connection. Schema wey full of REFERENCES clauses no enforce anything until every connection turn this on.

One more setting go matter later. SQLite automatically dey checkpoint once WAL grow pass 1000 pages. The connection wey happen to finish transaction at that moment na the one wey do the work. That one fine by itself. E become a question when Litestream dey run, because Litestream want control over when checkpoints happen.

Why database is locked still dey happen after you set busy_timeout

Na this failure dey make people return to Postgres, and e get one specific cause.

A busy timeout dey install busy handler, but SQLite no promise say e go call am.

If SQLite determine say calling the busy handler fit cause deadlock, e go return SQLITE_BUSY to the application instead of calling the busy handler.

The deadlock wey e dey avoid dey happen when transaction upgrade. A bare BEGIN for SQLite mean BEGIN DEFERRED. If the first statement after am na SELECT, you dey inside read transaction. When later UPDATE for that same transaction need change to write transaction, and another connection don write since your read start, SQLite no fit make you wait. Your snapshot don already old, and waiting go only make the two connections deadlock each other. The documentation talk the result directly:

Subsequent write statements go upgrade the transaction to write transaction if possible, or return SQLITE_BUSY.

Your 5000 millisecond timeout no dey get checked at all. The error dey come immediately, na why e look like say the setting no do anything.

The fix na one word.

BEGIN IMMEDIATE;
UPDATE notes SET body = 'edited' WHERE id = 1;
COMMIT;

BEGIN IMMEDIATE dey take write lock from the beginning, before e read anything. No upgrade dey happen, so no deadlock dey to avoid. This mean busy handler go apply, and the connection go wait for im turn instead of failing. Keep read-only transactions deferred. Any transaction wey contain write make e be immediate.

The second cause of lock errors dey harder to notice: keeping write transaction open while slow work dey happen. SQLite dey serialize writers, so transaction wey open, call external API through network, then commit go block every other writer for as long as that call take. Read wetin you need, close the transaction, do the slow work, then open short write transaction to store the result.

Continuous backup with Litestream

Nightly copy fit lose up to one day of writes, and if you run cp against SQLite database wey still dey active, e fit produce copy wey no go open. Two options dey safe. sqlite3 app.db ".backup /path/to/backup.db" dey use SQLite online backup interface, and e fit work against database wey dey in use. Litestream dey go further: e dey monitor the WAL and continuously send changes go object storage. This reduce worst-case data loss from one day to about one second.

Litestream na one Go binary wey dey run beside your application. E no dey sit between the app and database. Your application go continue write to SQLite the same way as before, while Litestream reads the WAL and uploads wetin change.

cd /tmp
curl -fsSL -O https://github.com/benbjohnson/litestream/releases/download/v0.5.14/litestream-0.5.14-linux-x86_64.deb
sudo dpkg -i litestream-0.5.14-linux-x86_64.deb
litestream version

v0.5.14 na the release wey the official Linux install page document as of July 2026, and v0.5.15 follow on 21 July 2026. Change the version for both lines to match the current tag for the releases page. If your VPS na arm64, use the matching arm64 package instead.

The configuration file dey for /etc/litestream.yml. Start with local file replica, because e go prove the complete process without cloud credentials.

dbs:
  - path: /home/appuser/app/app.db
    replica:
      type: file
      path: /var/backups/litestream/app

Notice say the field na replica, singular. Litestream 0.5 replace the replicas array from the 0.3 series with one replica block. Config wey carry two entries go fail when e start. Plenty third-party guides still show the old array, so copy the structure above instead of the first example wey search show you. The 0.5 series also rename the litestream wal subcommand to litestream ltx, because the on-disk backup format change.

Check say the config parse correctly before you enable anything.

sudo litestream databases -config /etc/litestream.yml

Then test the complete round trip by hand. This form skip the config file and replicate one database to one path.

mkdir -p /tmp/replica
litestream replicate ~/app/app.db file:///tmp/replica/app

That command go run for foreground and continue to run. For another shell, write one row and restore the replica into new file.

sqlite3 ~/app/app.db "INSERT INTO notes (body) VALUES ('written after replication started');"
litestream restore -o /tmp/restored.db file:///tmp/replica/app
sqlite3 /tmp/restored.db "SELECT count(*) FROM notes;"

The count include the new row. If e no include am, the change never sync yet. Litestream dey push on a sync-interval wey default to 1 second, so wait and restore again. That one second na also your recovery point. If crash happen, you fit lose at most the writes from the last sync interval. No configuration fit make that zero.

For real storage, replace the replica block with S3 URL. This one work with Amazon S3 and S3-compatible object storage from other providers.

dbs:
  - path: /home/appuser/app/app.db
    replica:
      url: s3://your-bucket-name/app
      region: us-east-1

snapshot:
  interval: 24h
  retention: 24h

No put credentials inside that file. Litestream reads LITESTREAM_ACCESS_KEY_ID and LITESTREAM_SECRET_ACCESS_KEY from environment, so put dem inside systemd drop-in wey root own, with mode 600.

The snapshot values above na the defaults, and the retention default dey surprise people. Retention na how long Litestream keeps snapshots and the files wey belong to dem. So e also determine how far back for time you fit restore. Twenty-four hours mean say if you notice bad migration on Wednesday morning, you no fit recover Monday state again. Set retention: 168h to one week, and pay for the extra storage.

Prove the restore before you need it

litestream restore -o /tmp/check.db /home/appuser/app/app.db
sqlite3 /tmp/check.db "PRAGMA integrity_check;"
sqlite3 /tmp/check.db "SELECT count(*) FROM notes;"

If you give am database path, litestream restore go find the matching replica for /etc/litestream.yml and pull am down. PRAGMA integrity_check go print ok for healthy file, and any other output mean say the restored copy no usable. Run this on schedule with systemd service and timer and read the output. Until you don restore backup at least once, you no know whether e dey work.

Run Litestream under systemd

The Debian package dey install one litestream unit wey dey read /etc/litestream.yml.

sudo systemctl enable litestream
sudo systemctl start litestream
sudo journalctl -u litestream -f

Healthy output dey show each database from the config, then e go quiet apart from periodic sync lines. Error of no such file or directory against your database path mean say the path for the config wrong, or the process no fit read am. The unit dey run as root by default, but this job no need that level of privilege. Litestream must fit read and write both the database and the directory wey hold am, because e dey work with the -wal and -shm files wey dey beside your database. So give am the account wey your application already dey use.

# /etc/systemd/system/litestream.service.d/override.conf
[Service]
User=appuser
Group=appuser

Apply am with sudo systemctl daemon-reload and sudo systemctl restart litestream. To set up dedicated service account with least privilege no dey take pass few minutes. Na this one separate backup agent from another root process for the machine.

One ordering detail important if you ever rebuild the machine from zero. You want make the database restore before the application start. litestream restore accepts -if-db-not-exists, wey dey exit 0 when the file don already dey there, so e safe to run am for every boot. Put am inside an ExecStartPre line for your application's unit. Then fresh VPS go pull the database down, while existing one no go do anything. litestream replicate get matching -restore-if-db-not-exists flag if you prefer keep am for one place.

Wey SQLite dey break for VPS

Network filesystems. Na limit wey you no fit configure around. WAL mode need every process wey dey use the database to share small memory region, and na the -shm file dey provide am. SQLite documentation state the rule without exception:

All processes wey dey use database must dey for the same host computer; WAL no dey work over network filesystem.

So database wey dey on mounted NFS (network file system) or SMB share fit corrupt, and no pragma fit prevent am. One difference dey here wey people dey miss. Network block device, wey na wetin most VPS providers attach as extra storage, dey appear to Linux like ordinary disk with ordinary filesystem, and that one dey okay. Mounted file share no be the same thing.

A second application server. No setting fit make this work. Once you need two machines to serve the same data, you need database wey dey communicate over network. Decide to make that change while you still get time to plan am.

Write-heavy workloads. One writer at a time na property of the file format, no be setting you fit tune. Short writes cheap because every commit na append to the WAL, so throughput dey follow your disk small-write latency more than your CPU. See NVMe against SATA SSD storage for VPS for how that difference dey look. Long transactions na the real problem, because dem queue every other writer behind dem.

Analytical queries. SQLite na row store wey dem build for transactions. Dashboard wey dey scan hundred million rows na different work for different tool, and DuckDB compared with SQLite for server work explain where that boundary dey.

VACUUM under replication. A full VACUUM dey rewrite the entire database file. That means Litestream must upload everything again, and Litestream documentation advise against running am in place while replication dey active. Stop the replicator, run vacuum, start am again, and expect fresh full snapshot.

Two replicators on one database. Never run two Litestream processes against the same database or the same replica destination. Documentation clear say na your responsibility to prevent this, and the result fit be replica wey you no fit restore.

Wetin Litestream no cover

Litestream dey protect database file only, nothing else. Uploaded files, application config, TLS (transport layer security) certificates, and unit files still na your responsibility to handle. Pair am with encrypted off-box backups using restic on schedule, and both sides go dey covered. If the machine na new one, the first ten minutes on a new VPS covers the user account and firewall work wey this guide assume say don already finish.

FAQ

SQLite good enough for production application?

For one application wey dey run for one server, yes, as long as you turn on WAL mode, set a busy timeout, and back am up continuously. The limits wey matter na structural: na one writer at a time, and na one host machine. Application wey fit inside these limits go get database wey no need network hop and no need separate process to monitor. Application wey no fit inside these limits need client-server database, and no amount of tuning go change that.

Why I still dey get database is locked after I set busy_timeout?

Because SQLite no dey call busy handler when waiting fit cause deadlock. Transaction wey start with bare BEGIN dey deferred: opening SELECT puts am for read transaction, and later write need upgrade am. If another connection write in between, SQLite return SQLITE_BUSY immediately instead of calling your busy handler, because your read snapshot don already stale. Start any transaction wey go write with BEGIN IMMEDIATE so e go take write lock from the beginning and timeout go apply.

I fit keep my SQLite database for network storage?

No, if na network filesystem like NFS or SMB. WAL mode need all processes to share memory through the -shm file, and SQLite documentation talk say every process wey dey use the database must dey for the same host computer. Network block device wey your provider attach na different thing: Linux dey see normal disk with normal filesystem, and SQLite dey work there.

I need Litestream if I already dey run nightly backups?

E depend on how much data you fit afford to lose. Nightly job mean say you fit lose up to twenty-four hours of writes. Litestream dey sync about once every second, so crash go cost you roughly the last second. E also safer than copying database file with cp, because that one fit capture database while write still dey happen. Litestream cover only the database, so keep general file backup running beside am.