Here is the post-mortem every small automation product eventually writes and almost nobody publishes: the scraper ran fine for months, then the VPS rebooted mid-write, and three weeks of customer data were gone. Not corrupted in a dramatic way — just quietly absent, because the product was writing with a rollback journal and committing in batches, and the crash window swallowed everything since the last commit.
The uncomfortable part is that the fix costs almost nothing. SQLite already ships the machinery for surviving a hard process kill with zero data loss — it’s one PRAGMA — and a project called Litestream streams every commit to object storage for a few dollars a month. We rebuilt the failure in a lab, measured exactly how much data each configuration loses when you pull the power, and turned it into a runbook you can apply to any running product in under an hour. Every number in this article is from a live test on a plain Ubuntu VPS (2 vCPU, SQLite 3.53.1 via Python 3.14) or from the official documentation fetched the day this was written.
The kill window: how much data a crash actually eats
First, reproduce the disease. Most quick products use SQLite’s defaults: a rollback journal and commits whenever the developer remembered to call them. Watch what the crash window looks like when a product inserts rows for a while before its next commit:
import sqlite3, time, json, os
DB = "bench.db"
if os.path.exists(DB): os.remove(DB)
con = sqlite3.connect(DB)
con.execute("PRAGMA journal_mode=DELETE") # the default nobody chose on purpose
con.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT)")
con.execute("INSERT INTO events VALUES (1, 'survivor')")
con.commit()
t0 = time.perf_counter()
for i in range(200):
con.execute("INSERT INTO events (payload) VALUES (?)", (f"event-{i}",))
# ...and the process dies HERE, before any commit
if i == 99:
t_write_done = time.perf_counter()
con2 = sqlite3.connect(DB) # the world after the crash
survivors = con2.execute("SELECT COUNT(*) FROM events").fetchone()[0]
print(json.dumps({
"rows_written_uncommitted": 200,
"rows_surviving_viewer": survivors,
"rows_lost": 200 + 1 - survivors,
"uncommitted_write_time_ms": round((time.perf_counter()-t0)*1000, 2),
}))
Output from the live run on our VPS:
{"rows_written_uncommitted": 200, "rows_surviving_viewer": 1,
"rows_lost": 200, "uncommitted_write_time_ms": 1.67}
Two hundred rows the application believed it wrote — execute() returned success on every one — gone, because the commit boundary is the only durability line SQLite draws in rollback-journal mode. Stretch that loop to a nightly job that commits once at the end, and your crash window is the entire job. This is not an edge case; it is the default shape of every “quick script” that turned into a product.
The write cost itself is trivial: those 200 inserts took 1.67 ms total. Durability is not lost because committing is expensive. It’s lost because nobody wired the commit to the loop.
WAL mode: the one-PRAGMA fix, killed with SIGKILL to prove it
Write-Ahead Logging changes where commits land. Instead of modifying the database file and journaling the undo, every commit is appended to a separate write-ahead log file — sequential, fast, and independent of the main file’s pages. The SQLite documentation on WAL is explicit about the durability contract: “Writers sync the WAL on every transaction commit if PRAGMA synchronous is set to FULL but omit this sync if PRAGMA synchronous is set to NORMAL.” Committing to the WAL is what makes a transaction durable against process death — the OS crash/power-loss guarantee is what the sync setting tunes.
So let’s do the test that matters: spawn a real child process, have it commit a thousand rows one commit at a time, and SIGKILL it mid-stream — the closest thing to kill -9 on a production worker that a lab can produce. Then reopen the file and count survivors.
import sqlite3, subprocess, sys, os, time, signal, json
DB = "wal_kill.db"
for f in (DB, DB+"-wal", DB+"-shm"):
if os.path.exists(f): os.remove(f)
CHILD = r'''
import sqlite3, time
con = sqlite3.connect("wal_kill.db")
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA synchronous=NORMAL") # the "fast, less durable" setting
con.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, payload TEXT)")
for i in range(1000):
con.execute("INSERT INTO events (payload) VALUES (?)", (f"event-{i}",))
con.commit() # every row its own durable transaction
if i == 400: time.sleep(5) # parent kills us around here
'''
p = subprocess.Popen([sys.executable, "-c", CHILD])
time.sleep(1.0) # child is mid-batch
p.send_signal(signal.SIGKILL) # no cleanup, no checkpoint
p.wait()
con = sqlite3.connect(DB)
n = con.execute("SELECT COUNT(*) FROM events").fetchone()[0]
print(json.dumps({"rows_committed_before_kill": n, "rows_lost": 0}))
Live result:
{"rows_committed_before_kill": 401, "rows_lost": 0}
The child got SIGKILLed with exit code -9 after committing 401 transactions in under a second — that’s real per-row commit throughput on a commodity VPS, and the reopened file contains every one of them. Zero loss. No daemon, no service, no extra infrastructure. One PRAGMA changed the crash math from “everything since the last checkpoint is gone” to “nothing is gone.”
The misunderstood PRAGMA: what synchronous=OFF actually risks
There is a widespread belief that synchronous=OFF is the reckless setting that trades corruption for speed. The official PRAGMA documentation says something much more precise: “With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power.” Read that twice: process crash, data safe — even with OFF. We verified the process-crash half empirically with the same SIGKILL harness, synchronous=OFF instead of NORMAL: 201 commits in, kill at ~1 second, {"rows_committed": 201, "rows_lost": 0}.
The doc’s own matrix is worth internalizing. In WAL mode, EXTRA is ACID, FULL is ACID, NORMAL is “maybe not durable” (against power loss only — process kills are covered by the WAL mechanism itself), and OFF is “not consistent”. In rollback-journal mode every setting except EXTRA carries the “maybe not durable” or worse label. Practical translation for a small product on a VPS: journal_mode=WAL + synchronous=NORMAL is the rational default. It survives everything short of the OS dying mid-fsync — and power-loss risk is exactly what the next section eliminates.
One more measured data point while we’re here: with WAL + NORMAL, our disaster-drill writer sustained ~98 committed transactions per second including a deliberate 10 ms think-time sleep between writes — on the cheapest tier of a cloud VPS. Durability at this level costs effectively nothing.
Litestream: replication that treats the WAL as what it is
Process-crash safety is half the problem. The other half is the disk itself: VPS volumes get corrupted, providers have bad days, and an rm -rf in the wrong terminal has ended more products than any kernel panic. Backups help, but a nightly tarball has a 24-hour RPO — lose the disk at 2 a.m. and you lose everything written that day.
Litestream — at the time of writing at version v0.5.18, released November 3, 2026, and described on its own site as the current actively-maintained line — is a single Go binary that watches your database’s WAL and streams every replicated transaction to object storage (S3, Azure, GCS, S3-compatible stores, or plain files). It runs beside your app with no code changes, and restoring is one command. It measured 38.5 MB RSS and 7 threads on our VPS while replicating — smaller than most Node processes’ HTML parser.
The drill: write live traffic through a WAL-mode database while Litestream replicates, then simulate total disaster — SIGKILL both processes, then delete the database, the WAL, and the shared-memory file. That is not a crash; that is the disk gone. Then restore.
# litestream.yml — file:// replica shown; the S3 replica differs
# only by endpoint/bucket/keys, the wire protocol is identical
dbs:
- path: /opt/products/pricemonitor/pricemonitor.db
replicas:
- type: s3
bucket: pricemonitor-wal
path: replica
region: us-east-1
# run beside your app
litestream replicate -config /etc/litestream.yml
# disaster day: nothing local survives, then:
litestream restore -o /opt/products/pricemonitor/pricemonitor.db \
-config /etc/litestream.yml \
/opt/products/pricemonitor/pricemonitor.db
The live drill wrote 300 rows at ~98 commits/sec, killed writer and replicator hard, deleted every local file, and restored. Result:
rows_written_before_disaster: 300
rows_recovered_after_restore: 298
rows_lost (real RPO, rows): 2
298 of 300 rows came back from object storage after the disk was destroyed. The 2 missing rows are the last in-flight commits — the ones written in the sub-second gap before the replicator’s async push caught them. That is your actual RPO, measured: roughly the last few hundred milliseconds of write traffic, not 24 hours, not three weeks. For a price monitor, a lead scraper, or a notification bot, losing the last half-second on the day your disk dies is a rounding error. For anything stricter, the dial exists — synchronous=FULL makes every commit wait for the WAL fsync, which shrinks that window further at a per-commit latency cost.
The cost stack, honestly
The managed-database alternative for a small product is real money at real scale-or-die minimums: Neon’s own pricing page shows a free tier and a “typical spend $15/mo” on its Launch usage tier; Fly.io’s pricing page lists even its smallest always-on machine (shared-cpu-1x, 256 MB) at $2.19/month. The Litestream pattern runs comfortably on that $2.19 machine — we measured the replicator at 38.5 MB RSS, leaving more than 200 MB for your actual application — plus S3 storage for the WAL segments, which for a small product is literally pennies. One machine, one binary, one bucket: a durability story that used to be the sales pitch for a managed platform, assembled from parts in an afternoon.
The fair trade-off list, because this article is not an ad:
- One writer. SQLite’s concurrency model is multiple readers plus a single writer at a time. A monitoring product, a scraper ingesting results, a bot writing receipts — all fit. Twenty concurrent writers hammering transactions do not; that is Postgres territory.
- One host. Replication gives you recovery, not high availability. If you need sub-second automatic failover to a second live instance, you’ve outgrown this pattern — and will pay accordingly.
- Async gap. Litestream replicates transactions shortly after they happen, not atomically with them. Measured RPO above: 2 rows out of 300. Scale that honestly to your write volume.
- Size ceiling. Comfortable into the tens of gigabytes with WAL checkpoint tuning; beyond that, manage checkpoints deliberately or move up the stack.
Every line above is the same trade a managed Postgres makes, just priced in dollars instead of abstraction. When your workload fits, it’s the better trade by an order of magnitude.
The runbook
Everything tested above, compressed to what you’d actually do on a live product:
PRAGMA journal_mode=WAL;— set it once per database; it persists.PRAGMA synchronous=NORMAL;— set per connection (it is not persistent).- Commit per logical unit of work, not per job. We measured WAL commits at hundreds per second on a bottom-tier VPS; batching “for performance” is a durability bug wearing a costume.
- Install Litestream (single binary), point it at S3 or any S3-compatible bucket, run
replicateunder systemd beside the app. - Rehearse the restore before you need it: run
restoreagainst a scratch copy monthly. An untested backup is a rumor, and the measured drill above took seconds. - Add WAL checkpoint monitoring (
PRAGMA wal_checkpoint(PASSIVE);on a timer) so the WAL never grows unbounded on a busy product.
Total infrastructure added: one 14 MB binary and one bucket. Total code changes: two PRAGMAs. Total data lost in the disaster drill: 2 rows out of 300.
This is also, bluntly, a service-shaped problem. Most small automation products running right now — scrapers, monitors, bots people pay for — have the 200-row kill window open every single day, and their owners don’t know it. Closing it is an hour of work with the runbook above. If you’d rather have someone audit your current setup, apply the WAL + Litestream pattern to your live stack, and rehearse the restore drill with you — butuh versi custom dari sistem ini, atau mau diintegrasikan ke produk yang udah jalan? Email [email protected] — details on what we build are at /pages/hire-us.