Something I found non-obvious about WAL mode in SQLite is that it's actually a property of the database file itself.<p>When you run "PRAGMA journal_mode=wal;" against a database file the mode is permanently changed for that file - and the .db-wal and .db-shm files for that database will appear in the same directory as it.<p>Any future connections to that database will use it in WAL mode - until you switch the mode on it back, at which point it will go back to journal mode.<p>It makes sense when you think about it - of course a database can only be in one or the other modes, not both, so the setting must be at the database file level. But it took me a while to understand.<p>I wrote some notes on this here: <a href="https://til.simonwillison.net/sqlite/enabling-wal-mode" rel="nofollow">https://til.simonwillison.net/sqlite/enabling-wal-mode</a>
Also worth noting that Sqlite released a WAL2 journal mode recently that eliminates the "stop of the world" we had with WAL when checkpointing. Basically it maintains two wal files and switched between them when one needs to be checkpointed. It is quite neat!
It's become popular to talk about how scalable SQLite is lately, but let's not forget the elephant in the room, it only allows a single writer at a time.<p>It's obviously easier to manage and maintain due to it being an embedded database, but that seems to be a very separate issue from the data structure involved, which definitely has disadvantages compared to a typical SQL system.
"The database header has a read & write version at bytes 18 & 19, respectively, that are used to determine the journal mode. They're set to 0x01 for rollback journal and 0x02 for write-ahead log. They're typically both set to the same value."<p>Are there any useful situations where they wouldn't be set to the same value?
Someone recently observed that SQLite would be used a lot more in production if it didn't have the word "lite" in its name. I've personally been amazed by what it can do. Really great piece of tech with an unfortunate name.
SQLite is such an excellent little tool.<p>I recently ran an experiment with Node and was able to take writes from around 750 / second to around 24K / second just by coordinating them using Node IPC. That is, I had the main thread own the sole write connection and all other threads sent their writes operations to it, thread-local connections were read-only.<p>It's pretty cool how far you can push SQLite and it just keeps humming right along.
SQLite needs some 'put your money where your mouth is' benchmarks.<p>EDIT:Lots of devs have (unfounded) doubts about performance regarding SQLite for web apps, a single machine benchmark against postgres (with postgres and the app in the same server) would clear many doubts and create awareness that SQLite is going to be more than enough for many apps.. The app doesn't have to be a web app (we are not testing web servers) but maybe some code with some semi-complex domain models.