https://news.ycombinator.com/item?id=47616704

Working with JSON data

Full-text search with FTS5

Strict tables and better typing

Write-ahead logging and concurrency

From Axlerod:
While Write-Ahead Logging (WAL) is generally recommended for most SQLite applications because it significantly improves concurrency (allowing simultaneous readers and writers), there are a few specific scenarios where you might not want to use it:

Network File Systems: WAL mode relies heavily on shared-memory primitives (mmap) to coordinate access between different database connections. These primitives often do not work correctly over network file systems like NFS, SMB, or CIFS.

Single-File Distribution: One of SQLite's main appeals is that a database is a single file. When you enable WAL mode, SQLite creates two additional temporary files alongside your main database: a -wal file and a -shm (shared memory) file. If you need to easily copy, email, or distribute the database as a single file, these extra files can complicate things (though you can mitigate this by forcing a full checkpoint before copying).

Read-Only Databases: If your database is purely read-only (e.g., a static lookup table distributed with an application), WAL mode provides no benefits and adds a tiny bit of overhead. The default rollback journal is slightly more efficient for read-only operations.

Checkpointing Overhead: In WAL mode, writes are appended to the -wal file. Eventually, this data must be transferred back to the main database file in a process called "checkpointing." If your application has a massive, continuous stream of writes, the WAL file can grow very large, and the checkpointing process can cause occasional latency spikes or performance hiccups.