How to minimize number of bytes writen to disk by sqlite?

Question

I use sqlite on Raspberry Pi to store sensor readings. The table structure is simple:

CREATE TABLE IF NOT EXISTS sensors(id INTEGER PRIMARY KEY AUTOINCREMENT, codename TEXT, name TEXT)
CREATE TABLE IF NOT EXISTS sensorvalues(id INTEGER PRIMARY KEY AUTOINCREMENT, sensorid INTEGER, value DECIMAL(10,2), time DATETIME, FOREIGN KEY(sensorid) REFERENCES sensors(id))

Each time I insert record to 'sensorvalues' table (about 20 bytes of data) iotop shows that about 30Kb is written do disk. In order to protect sd card from corruption I would like to minimize data written to permanent memory. I know that writing exactly 20 bytes is not possible because IO operations use page structures and buffers, but writing 30Kb on each commit seems too much. Is there any way to tune sqlite to minimize ammount of written data to neccessary minimum?

Answer 1

Inserting into these tables requires updating both the table and the index, and for reliability in the case of a crash, the database first writes the old data to a rollback journal, then updates the database file itself, then deletes the rollback journal.

You could disable the safety features with PRAGMA journal_mode = memory and/or PRAGMA synchronous = off, but

if the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

For many inserts, WAL mode is likely to be more efficient.