SQLite в WAL-режиме: полгода на маленьком сервисе

Для внутреннего сервиса учёта (десяток пользователей, пара тысяч записей в день) я выбрал SQLite вместо PostgreSQL. Полгода спустя: проблем меньше, чем ожидал, и одна неприятная, о которой ниже.

Обязательные настройки

По умолчанию SQLite настроен очень консервативно. При открытии соединения я выполняю:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

• WAL: читатели не блокируют писателя, и наоборот;
• synchronous=NORMAL: в режиме WAL безопасно и заметно быстрее, чем FULL;
• busy_timeout: без него параллельная запись сразу падает с database is locked;
• foreign_keys: выключены по умолчанию, что регулярно удивляет.

Бэкапы

Копировать файл базы через cp во время работы нельзя: получите несогласованную копию. Правильный способ встроен в саму SQLite:

sqlite3 /srv/app/data.db ".backup '/backup/data-$(date +%F).db'"

Или VACUUM INTO, который заодно сжимает файл. Для непрерывной репликации смотрите Litestream, но мне хватает ночного бэкапа.

Неприятность: разрастающийся WAL-файл

Через два месяца файл data.db-wal вырос до 2 ГБ при базе в 80 МБ. Причина: долгоживущее читающее соединение в фоновом воркере не давало выполнить checkpoint. Решение: короткие транзакции на чтение и периодический

PRAGMA wal_checkpoint(TRUNCATE);

Когда я бы не взял SQLite

• несколько серверов приложения пишут в одну базу;
• нужна сетевая файловая система (NFS и SQLite плохо дружат);
• много одновременных пишущих транзакций: писатель всегда один.

Для всего остального это самая скучная база данных из возможных. И это комплимент.