How to protect data in SQLite database?

Is the life of a solid state drive (SSD) affected if used with applications that use frequent writes like SQLite?

• I have an application that makes frequent COMMITs to an SQLite database and I have heard that SSDs suffer quite a bit of wear on writes. I reasoned that it could be possible to distribute the writes in such a way that any one location does not suffer undue wear, but on further thought it was not clear to me how one could do that with a non-volatile memory as a least some location (the index or file allocation table) has to be re-written. Is it preferable to delay COMMITs instead of doing them all the time? Will data be lost if the application crashes in such a case or will they remain in say a journal file and used on the next database load? Sorry if these are basic doubts, I am new to SQLite and databases in general.

• Answer:

  I have almost no experience in databases or SQLite. However what you've heard about the wear on SSDs (or any other erasable device in general) is true. Any editing of the content applies wear to the place where such editing is performed. Any location has a limited number of cycles before it is considered unreliable for use. Segments within a storage device eventually become unreliable due to overuse. Once a certain fraction of locations become unreliable, the entire device can be considered used up. Needless to say, the more often you write, the sooner your device reaches that stage. Your reasoning that it could be possible to distribute the writes in such a way that any one location does not suffer undue wear is also on the right track. This way segments are used to a greater extent before the device becomes unusable, leading to more write cycles from a device. This process is called http://en.wikipedia.org/wiki/Wear_leveling. Indeed some location would be written more often than others, mainly the ones that manage the wear leveling and all. A simple solution to this problem as given in the Wikipedia page is to provide better wear resistance to these locations. This [number of erase cycles before a segment becomes unreliable] is usually around 3,000/5,000 cyclesbut many flash devices have one block with a specially extended life of 100,000+ cycles that can be used by the http://en.wikipedia.org/wiki/Flash_memory_controller to track wear and movement of data across segments You might also like to read this: http://www.anandtech.com/show/2829/6. I cannot say for certain anything about the last part regarding the frequency of COMMITs since as I said earlier, I have no experience in such things. I am guessing this one is a problem of trade-offs: the more often you COMMIT, the lesser your disk's life; the less often you do so, higher the risk of losing your changes to unforeseen events.