Lessons from Debugging Embedded Database Lockups

The author shares their experience debugging a Raspberry Pi robot that was randomly freezing due to an embedded database issue. They explore the challenges of balancing low latency, data durability, and resource constraints in edge AI systems.

Details

The author was debugging an issue where a Raspberry Pi robot would randomly freeze for 2-3 seconds every few minutes. After three weeks of debugging, they found the culprit was the embedded database doing a full WAL checkpoint on the main thread. This highlighted the challenge of balancing low latency, data durability, and resource constraints when building edge AI systems. The author tried various approaches like using SQLite in WAL mode, a separate write thread with a message queue, and an in-memory buffer with async flush, but each had its own tradeoffs. The key realization was that the issue was not the database itself, but the mismatch between the database abstraction and the actual data access patterns of the robot. Robots need specialized storage for time-series data, state data, and vector data, rather than trying to fit everything into a single database. The author ended up building a custom storage layer tailored to these different data types, which provided sub-millisecond latency and eliminated the random freezing issues.