Which database stores the metric data for Raspberry Pi?

Question

I want to use the Raspberry Pi as an independent sensor that will measure a certain value every second and store this metric data in a local database. Then I would like to query the database based on a date range. Which database should I use, taking into account the limited resources of RPi and what will be there approx. 30758400 records / year? Are there any RPi-specific lightweight database systems specifically for this purpose?

Answer 1

I think SQLite will work well in this role. You may need to tweak the pragma settings for rPi a bit (e.g. set journal_mode = WAL), but SQLite can easily handle multiple gigabyte databases. (The main drawback of SQLite is concurrent access, but that will not be a problem for your application.)

If you need to store time / value stamp data and request only time intervals, you can use a key / value store, such as LevelDB. You are losing the flexibility of the SQL engine, but gaining performance.

Which storage medium do you plan to use? The ACID database will be written to disk for each transaction. A continuous I / O method can quickly kill an SD card.