Concurrently reading and writing from an SQLite3 database can be very slow in some cases. Since concurrency is usually very important in web applications, it’s recommended to turn on WAL mode to greatly increase overall performance.
db.pragma('journal_mode = WAL');
WAL mode has a few disadvantages to consider:
However, you trade those disadvantages for extremely fast performance in most web applications.
Checkpoint starvation is when SQLite3 is unable to recycle the WAL file due to everlasting concurrent reads to the database. If this happens, the WAL file will grow without bound, leading to unacceptable amounts of disk usage and deteriorating performance.
If you don’t access the database from multiple processes simultaneously, you’ll never encounter this issue.
If you do access the database from multiple processes simultaneously, preventing this issue is very simple: use the db.checkpoint() method when the WAL file gets too big.
This distribution of SQLite3 uses the SQLITE_DEFAULT_WAL_SYNCHRONOUS=1 compile-time option, which makes databases in WAL mode default to the “NORMAL” synchronous setting. This allows applications to achieve extreme performance, but introduces a slight loss of durability while in WAL mode.
You can override this setting by running db.pragma('synchronous = FULL').