Why SQLite in WAL Mode Is the Right Database for a Pi Sensor Logger

I run a Raspberry Pi 4 as a 24/7 home sensor logger — basement temp/humidity, per-room heating, a solar shed node, whole-house power, and a server rack. That’s six data sources writing continuously. I needed a database that:

• survives a power cut without corrupting,
• handles thousands of small inserts per hour,
• doesn’t need a separate server process,
• and lets me keep years of rollups on a single SD card.

After parallel-running it against my old RRDtool setup, I landed on SQLite in WAL mode. This post is the measured case for it, drawn from the live database on the logger right now.

The numbers (pulled live from the running logger)

All figures below are from timeseries.db on the Pi 4, queried with the Python sqlite3 module. No estimates.

Why WAL, not the default rollback journal

The default SQLite journal (DELETE mode) writes the whole transaction to a rollback file, then commits by deleting it. Under a power cut mid-write, recovery is a replay. It works — but every commit touches the main database file, which means readers and writers block each other.

WAL mode flips it:

• Writers append to a separate -wal file.
• Readers read the last committed state of the main DB plus the WAL.
• A writer never blocks a reader, and a reader never blocks a writer.

For a logger, that’s the whole game: the collector is always writing, and I’m always querying charts. They don’t step on each other.

The measured proof: with 818 rows landing in a single second and ~5,100 writes per hour sustained, the live site’s FastAPI layer was still serving chart queries without latency complaints. WAL is why.

The checkpoint question

WAL has one gotcha: the -wal file grows until a checkpoint. If it never checkpointed, a 120-day run would have a gigantic WAL. My setup runs a nightly rollup (cron 03:30) that does an explicit:

PRAGMA wal_checkpoint(TRUNCATE);

I ran that by hand during measurement: WAL went 4.3 MB → 0 bytes in one call. The nightly job keeps it bounded without me thinking about it.

If you’d rather not run a cron, SQLite auto-checkpoints at ~1000 pages of WAL (wal_autocheckpoint default) — fine for low-volume setups. At my write rate I prefer the explicit nightly truncate so the WAL never surprises me.

Retention without bloat: rollups + deletes

Raw data is kept 30 days, then deleted. The 5-minute rollup is kept 730 days, the hourly rollup forever. That’s why the main DB is only 139 MB despite 1.25M rows — the raw table is a sliding 30-day window, not an infinite growth.

This is the part that makes SD-card life bearable. Without rollups, 122k rows/day would hit ~45M rows/year and the DB would balloon. With them, the working set stays small and the historical detail I actually look at (hourly trends over two years) lives in 461k compact rows.

The schema (it’s boring on purpose)

CREATE TABLE measurements (
    ts     INTEGER NOT NULL,   -- unix epoch seconds
    source TEXT    NOT NULL,   -- basement|heat|jakob|shed|power|server
    metric TEXT    NOT NULL,   -- temp, hum, temp1..temp8, watts...
    value  REAL    NOT NULL
);
CREATE INDEX idx_meas ON measurements(source, metric, ts);
CREATE INDEX idx_meas_ts ON measurements(ts);

One wide-ish table, two indexes. No partitioning, no sharding, no extensions. The “EAV-ish” shape (source/metric/value) means adding a new sensor is an INSERT, not a migration. The cost is that a single sensor’s full history is scattered — but the rollup tables materialize exactly the queries the charts run, so that cost never shows up in practice.

Where the writes actually come from

Per-source raw-row counts over the 10-day raw window:

The shed node alone is 77% of all writes — eight DS18B20 probes plus DHT22, BMP180, BH1750, INA219, all polled fast over the HC-12 link. That’s the same hardware in my parts list; the logger design is built around absorbing that firehose without flinching.

The migration angle (why this isn’t my first rodeo)

The hourly table reaches back to 2024-06-03 — 810 days — but the raw table only goes back 10 days. The gap is the story: I migrated two years of legacy RRDtool history into SQLite (migrate_legacy.py) and kept it as the permanent hourly record, while letting the detailed raw data age out on a 30-day window. Old system’s history preserved; new system’s operational simplicity. That’s the parallel-run migration pattern, and it’s the same one I’d use to retire RRDtool anywhere.

Should you use SQLite WAL for your Pi logger?

For a single-node, single-board logger: yes, almost certainly. The alternatives I considered:

• RRDtool — great for fixed-size round-robin archives, but hostile to ad-hoc queries and impossible to JOIN. I kept it only as a parallel reference.
• InfluxDB — overkill for one Pi, wants more RAM than a Pi 4 comfortably gives, and pulls in a separate server process.
• Postgres — same problem: a server to babysit for what is fundamentally a file with a query language.

SQLite WAL is a file. It’s in the repo, it’s on the SD card, it checkpoints itself, and it has not corrupted once across the run (freelist 0, zero repair events). For a home logger that’s the right complexity/robustness trade.

The one case where I’d reach for something else: if you’re aggregating many Pis or pushing past ~50k writes/sec, go straight to a time-series server. At 5k/hour, SQLite isn’t even warm.

What I’d do differently

If I started over: set PRAGMA wal_autocheckpoint lower (e.g. 200 pages) so the WAL self-bounds even if the nightly cron ever fails — belt and suspenders. The nightly TRUNCATE is enough in practice, but a failed cron shouldn’t be the only thing keeping the WAL small. Everything else — the schema, the rollup retention, synchronous=NORMAL — I’d keep exactly as is.

All numbers in this post were queried live from the running logger’s SQLite database during writing. No figures are estimated.