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 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.