SQLite on Embedded Linux: When It's the Right Database and When It Isn't

SQLite on embedded Linux handles most local persistence needs—config, telemetry buffer, offline queue—without running Postgres on a Pi. The failure mode that burned us: WAL mode on eMMC with abrupt power loss, leaving -wal/-shm in a state that survived reboot but corrupted logical invariants. Use SQLite until you cannot tolerate that class of risk or need concurrent writers at scale.

Where SQLite fit

Product: edge gateway on Raspberry Pi CM4, 32 GB eMMC, Yocto Kirkstone, SQLite 3.40.1 via sqlite3 CLI and C API.

Workload:

• 1 writer thread (sensor batch insert every 30 s)
• 2 reader threads (REST API, cleanup job)
• Database size capped at 500 MB with rolling delete

PRAGMA journal_mode=WAL gave read concurrency we wanted. Throughput: ~800 inserts/s in batch mode during stress test—far above 30 s cadence.

Configuration that worked (mostly)

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;  -- not FULL on eMMC: latency vs safety trade
PRAGMA cache_size=-64000;   -- 64 MB
PRAGMA temp_store=MEMORY;
PRAGMA mmap_size=268435456;

Single-writer discipline enforced in app layer mutex—not optional. WAL allows concurrent readers but one writer at a time; two writers = SQLITE_BUSY retries or deadlock patterns.

Where it broke

Power loss during checkpoint. Field unit unplugged during heavy ingest; on boot SQLite opened clean per PRAGMA integrity_check, but application-level "latest reading per sensor" query returned duplicates—transaction boundaries crossed power cut in application logic, not SQLite atomic commit.

Root cause: we batched 50 inserts per explicit BEGIN IMMEDIATE but updated a separate sync_cursor table outside that transaction.

Fix: wrap business-unit transactions; add PRAGMA wal_checkpoint(TRUNCATE) on graceful shutdown hook from systemd ExecStop.

eMMC wear. WAL file churn increased writes 3× vs rollback journal. iostat showed sustained 2 MB/s during bulk import test—acceptable for lab, borderline for cheap eMMC over 5-year life.

Mitigation: batch inserts, synchronous=NORMAL, nightly checkpoint job in maintenance window.

When I pick Postgres instead

• Multiple services need concurrent write access to same tables
• Replication/failover required
• Complex query analytics without ETL export

On the same hardware class we run Postgres vacuum tuning notes for a gateway admin UI backend—different SKU, ops team available.

When I pick flat files / LMDB

• Read-mostly config blobs
• Append-only event log with external compaction
• Sub-10 KB total state

io_uring side note

Bulk export to USB used Linux io_uring edge notes for async read of SQLite file snapshot copy—never copy -wal alone while DB live without checkpoint.

Decision checklist

Requirement	SQLite OK?
Single process owner	Yes
Power-loss safe business invariants	Only with transaction discipline + sync policy
5+ concurrent writers	No
eMMC, WAL, years-long uptime	Caution—monitor wear, checkpoint strategy
Ad-hoc SQL from support	Yes (CLI on device)

What I'd do next

1. systemd-poweroff hook always checkpoint; mark dirty flag if unclean stop detected, run extended integrity job on next boot.
2. Evaluate SQLite 3.45+ incremental vacuum for rolling delete workload.
3. Document "no WAL on SD card class 4" in hardware BOM notes—some integrators still spec trash storage.

Backup without stopping the world

Hot backup via SQLite backup API (sqlite3_backup_init) while app paused writes 200 ms—acceptable on maintenance cron. Copying .db file alone while WAL active produced orphan WAL state on restore test; always use API or checkpoint first.

Corruption recovery playbook

On SQLITE_CORRUPT: mount read-only, dump with .recover (SQLite 3.29+ CLI), replay last known good export from gateway. We ship weekly auto-export to USB config stick for field techs—unromantic, saved one farm deployment.

Read pool contention

Two reader threads plus WAL worked until REST API spike during batch insert—SQLITE_BUSY retries spiked. Serialized readers through connection pool size 1 for API path; cleanup job exclusive lock during insert window 200 ms every 30 s. Simpler than distributed DB, still requires scheduling thought.

Migration path off SQLite

When gateway SKU grew second service needing write access, we migrated hot table to Postgres on same Pi using logical replication pattern (app dual-write 2 weeks)—not SQLite limitation hit suddenly, planned threshold at 3 concurrent writers.

fsync policy A/B

PRAGMA synchronous=FULL on eMMC added 40 ms per transaction batch—acceptable for financial adjacency SKU, rejected for sensor logger. SKUs diverge on same codebase with compile-time profile; document in release matrix.

VACUUM scheduling

Weekly VACUUM during maintenance window reclaimed 120 MB on fleet average after rolling delete policy—without it, free pages fragment file size. auto_vacuum=INCREMENTAL enabled on new SKUs to spread cost.

SELinux and WAL paths

Yocto image with SELinux enforcing blocked SQLite WAL create in /var/lib/app until file context added—fresh install worked in dev (permissive), failed in production image. Include DB path in SELinux policy module checklist.

SQLite is not a toy database—it is a contract between your process and one file. On embedded Linux, read the WAL + power-loss chapter before you ship.