SQLite WAL Raspberry Pi Logger Performance: 1.25M Rows, 10 Days Zero Corruption

I run a Raspberry Pi 4 as a 24/7 home sensor logger — basement temp/humidity, per-room heating (five Google Nest thermostats polled through the SDM API), a solar shed node, whole-house power, and a server rack. That’s six data sources writing continuously, gathered by the asyncio collector pattern and landed in one SQLite database. 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 1-Wire probes writing those rows are DS18B20s — if one starts giving suspicious numbers the first thing to rule out is whether the chip is genuine, not the wiring; the isolation procedure is in how to spot a fake DS18B20.)

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. For how I keep the DB from eating the SD card — rollups, retention, and why SD-card wear is rarely the real problem — see the companion post on SQLite rollups and retention.

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 — and every one of those rows arrives over the HC-12 433 MHz radio link from the ESP32-C3 out in the shed, which reads its own DHT22 and ships them back as a CSV burst. The eight DS18B20 probes, the INA219, and one of the DHT11 are not on that link: they’re wired straight to the Pi 4’s own GPIO (1-Wire and I2C) as a separate local bus, landing in the same database under different sources. So the logger absorbs two independent firehoses — the Pi’s local sensor bus and the radio link from the shed — and the design is built to take both without flinching.

The framed stream from the shed runs through the HC-12 packet-loss retry protocol, which is the application-layer framing the collector uses to validate frames before they reach SQLite.

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

The hourly table reaches back to 2024-05-14 — includes migrated legacy data — 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. The raw table’s 10-day window and the nightly rollup that keeps it bounded are documented in the SQLite rollups and retention post.

Raw data grows about 122k rows a day. Without a retention policy that data eventually dominates the card and the queries slow down — see SQLite rollups and retention: stop your sensor DB from eating the SD card for the rollup tables and 30-day window I run on top of this WAL setup.

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.

Related reading

• SQLite Rollups and Retention: Stop Your Sensor DB From Eating the SD Card

  How rollup tables and a retention policy keep a Raspberry Pi sensor SQLite database from growing unbounded — and why SD-card wear is rarely the real problem.

• Parallel-Run: How to Replace a Monitoring System Without Trusting It Yet

  How I ran my old RRDtool logger and its SQLite replacement in parallel for months — why you tee the reads instead of double-polling, and how to set a real cutover test before a monitoring-system migration.

• Migrating RRDtool to SQLite on Raspberry Pi: Keep Every Historical Data Point

  How I migrated a two-year RRDtool logging setup to SQLite in WAL mode on a Raspberry Pi 4 — rescued 145,742 legacy rows, kept every historical data point, and used a parallel-run cutover to verify.