Tips & Caveats
Running an application that uses SQLite and Litestream can require some small tweaks to optimize performance and usability. There are also some important caveats to know.
SQLite is built to run as a multi-process embedded database and Litestream acts as just another process. However, Litestream requires periodic but short write locks on the database when checkpointing occurs. SQLite will return an error by default if your application tries to obtain a write lock at the same time.
To prevent this, your application will need to set the
pragma when it initializes the database connection. This pragma
will wait up to a given number of milliseconds before failing a query if it is
blocked on a write. It is recommended to set this to 5 seconds:
PRAGMA busy_timeout = 5000;
WAL journal mode
Litestream only works with the SQLite WAL journaling mode. This is the
recommended mode for most applications as it generally performs better and
allows for concurrent read access. Litestream will automatically set the
database mode to
WAL if it has not already been enabled by the application.
Deleting SQLite databases
If you’re deleting and recreating a SQLite database from scratch, there are 3 files which must be deleted:
- Database file
- Shared memory file (
- WAL file (
If you delete your database file but not your WAL file then SQLite will try to apply those old WAL pages to your new database. Litestream also tracks changes via the WAL so it can cause replication issues if the WAL file is leftover.
Additionally, Litestream currently does not track database deletions. If you
remove your database and recreate it, you should delete the
directory next to your database file and restart Litestream.
SQLite must call
fsync() to flush data to disk to ensure transactions are
durable. While in WAL journaling mode, fsync calls can be relaxed in exchange
for durability without risking data corruption.
To do this, you can change the
SYNCHRONOUS mode to
PRAGMA synchronous = NORMAL;
This mode will ensure that the
fsync() calls are only called when the WAL
becomes full and has to checkpoint to the main database file. This is safe as
the WAL file is append only.
Data loss window
Litestream performs asynchronous replication which means that changes are replicated out-of-band from the transaction that wrote the changes. This is how many replication tools work including PostgreSQL’s log-shipping replication. Asynchronous replication is typically much faster than synchronous replication but it trades off durability of recent writes.
By default, Litestream will replicate new changes to an S3 replica every 10
seconds. This can be reasonably configured down to 1 second with the
sync-timeout configuration setting. During this time where data
has not yet been replicated, a catastrophic crash on your server will result in
the loss of data in that time window.
For more typical shutdown scenarios, when Litestream receives a signal to close, it will attempt to synchronize all outstanding WAL changes to the S3 replica before terminating.
Synchronous replication is on the Litestream roadmap but has not yet been implemented.