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?