I took another look at the (somewhat outdated) SQLite Optimization FAQ, and I read about the “synchronous” setting. It is also described in the SQLite documentation: http://www.sqlite.org/pragma.html (you need to scroll down a bit)
With the default setting (“FULL”), SQLite waits for the filesystem to write any changes to the disk before returning from an UPDATE or INSERT statement. This is very safe, because the database file can never corrupt if your computer crashes in the middle of an operation, but it is also very slow.
I have tried to set the value to OFF, and in fact, the write performance increases significally. It’s as fast as PostgreSQL then. But I’m unsure if it’s a good idea to set this as a default value, because it bears a certain risk. Perhaps it’s a good idea to offer the setting as an option in the database configuration dialog, so each user can pick whatever he prefers. If you make backups of your database file regularly (and you should do so), then it might be safe for you to use the OFF setting and benefit from the increased performance.
For the moment - and this applies to Build 790 which I just uploaded - you can set the synchronous mode manually to OFF (or NORMAL) by adding the following line to the respective section in database.ini:
Synchronous=OFFMy “Database1” section for my local mAirListDB now looks like this:
[Database1]
Type=mAirListDBLite
Enabled=on
DatabaseID=3C7678A8EDA3BCC73F54941AB1128CD0
Filename=d:\projects\mAirList\trunk\database.db
Synchronous=OFFPlease give it a try.