I fully agree with what pg said about keeping everything you can in in-memory structures. You can save modifications to a sequential logfile, or some other simple on-disk structure that is easily maintained (e.g. separate file per record), and read this structure into memory when you restart. Once that becomes infeasible, I would switch to using SQLite's BTrees [1] directly and bypassing the entire SQL layer. Using the SQL layer is just pretty pointless and a waste of resources. You're going to have a "data access layer" over your database anyway, so why not have it operate on a level where you have precise control over the access patterns and can tune it for your application? If you use the SQL layer, you're basically losing all that information by having your data access layer transform your operations into a high level data manipulation language by building a string (eww), then you leave the SQL layer of the database engine to parse your string back to something sensible and have it guess at how to optimize the query to get the best performance. Instead just tell it directly and skip the whole string construction, string parsing, and query analysis crap.

Notes

1. I'd suggest BDB, but it seems to suck. YMMV, always benchmark it yourself.