SQLite WAL performance improvement

To avoid fragmentation and remove a need to pre-insert data, you should use sqlite3_file_control() with SQLITE_FCNTL_CHUNK_SIZE to set chunk size. Allocating database file space in large chunks (say 1MB at a time), should reduce file-system fragmentation and improve performance. Mozilla project is currently using this setting in Firefox/Thunderbird with great success.

Regarding WAL. If you are writing a lot of data so often, you should consider wrapping your writes into bigger transactions. Normally, every INSERT is auto-committed and SQLite has to wait until data is really flushed to disk or flash - which is obviously very slow. If you wrap multiple writes to one transaction, SQLite need not to worry about every single row, and can flush many rows at once, most likely into single flash write - which is much faster.So, if you can, try to wrap at least few hundred writes into one transaction.

In my experience, WAL on flash is not really working very well, and I find it more beneficial to stick to old journalling mode. For example, Android 4 does not use WAL mode for its SQLite databases, and probably for a reason. As you have noticed, WAL has a tendency to grow without bound in some situations (however, it will also happen if transactions are rarely committed - so be sure to do that once in a while).

In WAL mode, SQLite writes any changed pages into the -wal file.Only during a checkpoint are these pages written back into the database file.

The -wal file can be truncated only if there aren't any concurrent readers.

You can try to clean out the WAL file explicitly by calling sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_RESTART or executing PRAGMA wal_checkpoint(RESTART), but this will fail if there are any concurrent readers.