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

If you keep every raw row from six data sources forever, SQLite grows linearly and your queries eventually crawl. The fix isn’t a new server — it’s rollups + a retention policy, with a hard look at what actually wears an SD card.

The measured baseline

My Pi 4 logger writes continuously: basement temp/humidity (DS18B20 1-Wire + DHT11), per-room heating (5 Google Nest thermostats via the SDM API), solar shed node (ESP32-C3 over HC-12 433 MHz), whole-house power (CT clamps with ADS1115 ADC), an INA219 rail monitor, and a server rack. Six sources, one timeseries.db, WAL mode, synchronous=NORMAL, auto_vacuum=0.

Numbers pulled live from the DB (see the WAL performance post for the full table):

That 122k/day rate is the real figure. Projected from it: a full 30-day raw window holds ~3.7M rows; without rollups, a year reaches ~44.7M raw rows. At ~72 bytes/row (139 MB ÷ 1.94 M total rows, measured from the 10-day run), that balloons to ~500 MB+ — and your 30-day query scans the full table.

The rollup method

I run a nightly cron 03:30 that does three things in one SQLite session, wrapped in BEGIN IMMEDIATE so the asyncio collector (FastAPI + Chart.js backend) never blocks:

-- 1) Aggregate last 5-minute window into rollup
INSERT INTO measurements_5m (ts, source, metric, avg_val, min_val, max_val, n)
SELECT 
  CAST(ts/300 AS INTEGER)*300,
  source, metric,
  AVG(value), MIN(value), MAX(value), COUNT(*)
FROM measurements
WHERE ts >= :window_start AND ts < :window_end
GROUP BY source, metric, CAST(ts/300 AS INTEGER);

-- 2) Delete aged raw rows
DELETE FROM measurements WHERE ts < :cutoff_30d;

-- 3) Truncate WAL (measured: 4.3 MB -> 0 bytes in one call)
PRAGMA wal_checkpoint(TRUNCATE);

The rollup table schema is deliberately boring — same source/metric shape, just with avg/min/max/n and a 300-second ts. Queries against the dashboard hit rollups for historical trends; raw detail is only needed for recent debugging or validation against the ADS1115 calibration data.

SD-card wear: the physics

Every insert writes pages. With WAL mode, writers append to -wal; at checkpoint those frames copy to the main DB. The measured WAL before truncate is 4.3 MB. Call it ~4.3 MB/day WAL growth, plus checkpoint copy (similar magnitude) and page-split overhead: conservatively ~5-8 MB of flash I/O per day.

A 128 GB consumer-grade TLC microSD (e.g., Samsung EVO Plus 128 GB, rated ~300 TBW per Samsung’s spec sheet) is a common choice. At 6 MB/day flash I/O, the card’s endurance rating dwarfs typical controller lifespans of a few years; wear is effectively irrelevant. The card fails of controller age, voltage sag, or power-cut corruption long before write endurance.

So the SD-card anxiety is mostly a proxy for “database bloat” — if you skip rollups, you trigger full-table scans and growing WAL, which increases write amplification and the chance of a bad checkpoint. The fix is the retention policy, not an SSD swap.

Still, if you want SD-card safety:

• Use a UPS (Pi 4 logger runs on a bench supply; a 10-second hold prevents WAL corruption during checkpoint).
• Keep synchronous=NORMAL; FULL doubles writes with almost no durability gain under WAL.
• Monitor WAL size (sqlite3 timeseries.db "PRAGMA page_count; PRAGMA wal_checkpoint(QUICK);") — quick checkpoint for monitoring avoids blocking the collector. If it climbs past 200 MB, the nightly cron failed.
• Never run VACUUM on a large DB; it rewrites the whole file, causing write amplification and lock contention. Use incremental auto_vacuum if needed.

What the comparison looks like

I keep RRDtool running in parallel (legacy) for comparison. It stays fixed-size and fast, but impossible to JOIN against the Nest SDM device table or to query the ESP32-C3 shed node by source. SQLite + rollups is the practical split: operational simplicity with a SQL interface.

What went wrong / failure modes

• Silent rollup failure: The cron runs but the INSERT fails (disk full, bad index). Raw rows pile up for weeks. Monitor with SELECT COUNT(*) FROM measurements WHERE ts > now()-86400. I check this in the weekly status script.
• Deleting before rolling: I once deleted raw rows at 03:00 but the rollup ran at 03:30 — lost 30 minutes of resolution. The fix: rollup first, delete second, both in the same transaction.
• Checkpoint during high-write burst: The 818-row peak second can overlap with checkpoint, briefly blocking the collector. The nightly 03:30 window avoids this; never checkpoint during the ESP32-C3 radio burst (around 04:00 when the shed node reports).
• Not measuring WAL growth: Without the WAL-size check, a failed cron causes WAL to grow. At 4.3 MB/day, it takes weeks to matter — but once it crosses 500 MB, the SD card’s free space is the real problem.
• Misapplying vacuum: Running VACUUM on the 139 MB DB takes minutes and rewrites the whole file, which is exactly the write amplification you wanted to avoid.

What’s unmeasured — fill in here

I have not directly measured the exact bytes written to flash per day averaged over 7 days on the Pi 4 logger; iotop or strace would provide that figure. The specific SD card TBW rating is not verified here — a typical consumer-grade TLC card is rated around 300 TBW per Samsung’s spec sheet, but the exact card used should be confirmed via smartctl or the packaging. The projected DB size at 30 days with rollups (~300 MB) has not been confirmed against a full 30-day run; it is derived from the measured 10-day data and may vary with sensor load.

Bottom line

SQLite WAL handles my six-source load easily. The risk isn’t write speed — it’s unbounded growth. Rollups (5-min/30-day + hourly/forever) cap the working set. At ~122k rows/day, the DB stays bounded; flash I/O is well below consumer card endurance. Keep the rollup cron healthy, check WAL size, and don’t confuse database bloat with SD-card death.

Related reading

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

  Real numbers from a Raspberry Pi 4 running SQLite in WAL mode as a 24/7 home sensor logger: 1.25M rows, 5108 writes/hour, 4 MB WAL, zero corruption after 10 days of continuous writes.

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

• DHT11 After 18 Months of Continuous Logging: An Honest Accuracy Review

  18 months and 26,318 rollup rows from one DHT11 on a Pi: 38% of them below the datasheet's 20 %RH floor, what that actually looks like, and where the part stops being usable.