SQLite in production: WAL, блокировки, бэкапы и миграция на PostgreSQL/MySQL

Почему вокруг SQLite столько споров

SQLite — это не «маленькая база данных», а полноценный SQL-движок, который живёт внутри вашего процесса и хранит данные в одном файле. За счёт этого он часто даёт низкую стоимость владения: не нужен отдельный сервер, нет сетевой задержки, проще деплой и резервное копирование.

Проблемы начинаются там, где SQLite используют как «замену PostgreSQL/MySQL» без понимания модели конкурентного доступа. Большинство инцидентов выглядят одинаково: всплеск записи, рост задержек, затем SQLITE_BUSY и «database is locked».

Ниже — практичный разбор того, что реально работает в продакшене: WAL, ожидание блокировок, ограничения конкурентной записи, почему NFS почти всегда вреден, как делать бэкап без простоя и когда миграция на серверную СУБД становится неизбежной.

SQLite vs PostgreSQL/MySQL: архитектура и последствия

Ключевое отличие тут не в диалекте SQL, а в том, где живёт «истина» и как обеспечивается конкурентность.

• SQLite: один файл на диске + блокировки, завязанные на семантику файловой системы. Клиент и «сервер» — один процесс. Конкурентность упирается в то, как быстро и корректно ОС/ФС разруливают блокировки и fsync.

• PostgreSQL/MySQL: отдельный сервер, конкурентность управляется на уровне транзакций и внутренних структур (MVCC в PostgreSQL), есть зрелые механизмы репликации, PITR, роли/права, мониторинг.

Практическое следствие: SQLite отлично подходит для read-heavy сценариев, локальных сервисов/воркеров, небольших проектов на старте, когда запись контролируема. Но при росте параллельных записей и требований к масштабированию серверная СУБД обычно оказывается проще и надёжнее.

SQLite performance: когда он быстрый, а когда — нет

В «хороших» условиях SQLite действительно впечатляет: локальный доступ без TCP, компактные индексы, минимум накладных расходов. Особенно заметно на небольших объёмах данных и коротких транзакциях.

Производительность и стабильность обычно деградируют, если:

• есть частые конкурентные записи из нескольких процессов/воркеров/контейнеров;

• транзакции держат блокировки долго (типичный анти-паттерн: сделали SELECT, потом секунды бизнес-логики, потом UPDATE);

• файл базы лежит на медленном или «странном» хранилище (сетевые ФС, некоторые варианты volume/overlay с неожиданной семантикой);

• каждая мелкая операция вынуждена делать дорогие синхронизации записи, потому что режимы журналирования/синхронизации не подобраны под сценарий.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

WAL mode: база для адекватного продакшена

Режим журналирования WAL (Write-Ahead Log) — почти всегда то, с чего стоит начинать в сервисных сценариях. Он позволяет читателям меньше конфликтовать с записью: чтение идёт из основного файла, запись — в WAL.

Минимальный набор прагматичных PRAGMA для типового приложения:

sqlite3 app.db "PRAGMA journal_mode=WAL;"
sqlite3 app.db "PRAGMA synchronous=NORMAL;"
sqlite3 app.db "PRAGMA busy_timeout=5000;"
sqlite3 app.db "PRAGMA foreign_keys=ON;"

Что важно понимать:

• journal_mode=WAL обычно снижает конфликты «читатели против писателя».

• synchronous=NORMAL — распространённый компромисс между скоростью и устойчивостью. Если вы не готовы к риску потери последних транзакций при аварии питания/ядра, используйте FULL и считайте I/O.

• busy_timeout нужен, чтобы кратковременная занятость не превращалась в мгновенный SQLITE_BUSY.

• foreign_keys=ON — включайте явно, иначе ограничения внешних ключей могут не работать так, как вы ожидаете.

WAL не делает SQLite «про много параллельных писателей». Он улучшает конкурентность чтения, но одновременные writers всё равно сериализуются.

Locking, busy_timeout и почему вы видите SQLITE_BUSY

Ошибки «database is locked» почти всегда означают, что фактическая конкурентная модель не совпала с нагрузкой. Чтобы SQLite вёл себя предсказуемо, держитесь трёх правил.

1. Всегда задавайте ожидание через PRAGMA busy_timeout или настройку драйвера/DSN. Без этого даже короткий конфликт будет ошибкой.

2. Держите транзакции короткими: подготовили данные, открыли транзакцию, сделали изменения, сразу COMMIT. Не держите транзакцию «на время обработки запроса».

3. Ограничьте число writers: если несколько воркеров активно пишут, лучше архитектурно сделать «очередь записи» (один writer-процесс/пул) или переносить запись в PostgreSQL/MySQL.

Для диагностики полезно логировать время ожидания блокировки и конкретные запросы, которые её держат. Часто причина — не «медленный SQLite», а один участок кода, который удерживает транзакцию из-за I/O или длинной бизнес-логики.

Конкурентные записи: что реально возможно

Сколько параллельных записей выдержит SQLite — вопрос не про «цифру», а про паттерн.

• Один писатель, много читателей — обычно отлично, особенно с WAL.

• Много мелких записей из разных процессов — плохо: получите очередь на блокировках и рост latency по хвостам.

• Пакетирование — часто спасает: 100 INSERT в одной транзакции почти всегда лучше, чем 100 транзакций с отдельным COMMIT.

Пример пакетной вставки одной транзакцией (для демонстрации принципа):

sqlite3 app.db "BEGIN; INSERT INTO events(ts, payload) VALUES (strftime('%s','now'), 'a'); INSERT INTO events(ts, payload) VALUES (strftime('%s','now'), 'b'); COMMIT;"

В приложении предпочтительнее prepared statements и явные транзакции в коде, но идея та же: меньше COMMIT и меньше синхронизаций записи.

SQLite на NFS: почти всегда плохая идея

Соблазн положить базу на сетевое хранилище «для надёжности» или «чтобы шарить между узлами» встречается регулярно. На практике это часто заканчивается некорректными блокировками, зависаниями, повреждением WAL/журнала и непредсказуемыми задержками.

SQLite опирается на корректную реализацию файловых блокировок и семантику fsync. В NFS (и других сетевых ФС) гарантий может не быть или они будут отличаться в зависимости от версии/настроек/кеширования.

Если вам нужно, чтобы несколько серверов писали в одну базу, это уже задача серверной СУБД. SQLite проектировался под локальный диск и контролируемую конкурентность.

Практичный вывод: держите файл БД на локальном блочном устройстве (SSD/NVMe). Если нужна высокая доступность и совместная запись с нескольких узлов — выбирайте PostgreSQL/MySQL.

Бэкап SQLite: как сделать правильно (и проверяемо)

«Это же один файл» — правда только наполовину. В продакшене бэкап должен быть согласованным снимком, а процедура — проверяемой.

Вариант 1: онлайн-бэкап через .backup

Надёжный способ, корректно работающий с активной базой:

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

После — проверяйте целостность на копии:

sqlite3 /backup/app-$(date +%F).db "PRAGMA integrity_check;"

Вариант 2: снапшот блочного устройства (LVM/ZFS и аналоги)

Если инфраструктура даёт консистентный snapshot блочного устройства, можно снять снапшот диска и копировать файл базы из него. В WAL-режиме рядом будут файлы -wal и -shm, поэтому снапшот должен захватывать их согласованно.

Минимальные требования к бэкап-процедуре

• расписание и ретеншн, понятный RPO;

• периодическая проверка PRAGMA integrity_check хотя бы на части бэкапов;

• регулярный тест восстановления в отдельной среде (иначе это не бэкап, а надежда).

Если вы строите общую систему бэкапов для хостинга/VDS, удобно иметь единый подход к хранению и проверке копий: см. практику бэкапов в S3-совместимое хранилище с restic/borg.

Когда пора мигрировать на PostgreSQL/MySQL

SQLite часто закрывает задачу годами, и миграция не должна быть «религией». Но есть признаки, что дешевле и безопаснее перейти на серверную СУБД:

• устойчивые ошибки/задержки из-за блокировок при росте нагрузки;

• несколько приложений/узлов должны писать одновременно;

• нужны зрелые функции эксплуатации: роли/права, репликация, PITR, прогнозируемый SLA по записи;

• вы тратите время на «обходные манёвры» вокруг блокировок, вместо развития приложения.

Попытка «положить SQLite на NFS, чтобы два сервера работали с одной базой» — почти всегда симптом, что пора мигрировать.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Миграция на PostgreSQL: план без героизма

Ниже — схема миграции, которая снижает риски и даёт контроль на каждом шаге.

Шаг 1: инвентаризация несовместимостей

• Типы данных: SQLite типизирован «мягко», PostgreSQL — строго. Проверьте места, где числа лежат в TEXT, даты строками, JSON «как попало».

• Идентификаторы: в SQLite часто INTEGER PRIMARY KEY, в PostgreSQL — BIGSERIAL или GENERATED ... AS IDENTITY.

• Диалект SQL: конструкции вроде INSERT OR REPLACE и некоторые функции дат/строк придётся адаптировать.

Шаг 2: выгрузка из SQLite в переносимом виде

Дамп SQL иногда проще, но часто требует правок под PostgreSQL. Для небольших баз практично выгружать таблицы в CSV.

Экспорт таблицы в CSV:

sqlite3 -header -csv app.db "SELECT * FROM users;" > users.csv

Шаг 3: загрузка в PostgreSQL и приведение типов

Дальше загружаете CSV в PostgreSQL через COPY (или инструмент/ORM) и приводите типы, создаёте индексы и внешние ключи.

Почти всегда быстрее создать индексы после массовой загрузки.

Шаг 4: переключение приложения

• либо делаете «окно» с read-only на SQLite и финальную дельту-миграцию;

• либо включаете двойную запись на время (если архитектура позволяет) и затем отключаете SQLite;

• переключаете DSN и мониторите ошибки, задержки, рост таблиц/индексов.

Если вам важен PITR и предсказуемое восстановление PostgreSQL, заранее продумайте стратегию бэкапов и WAL: пригодится разбор PITR и восстановления PostgreSQL через WAL.

Для миграции на MySQL/MariaDB логика похожая, но уделите внимание кодировкам, SQL-режимам и поведению транзакций/индексов.

Короткий чеклист для SQLite в production

• Включён WAL: journal_mode=WAL.

• Задан busy_timeout, а ожидание блокировок наблюдаемо (логируете время и контекст).

• Транзакции короткие, записи пакетируются.

• Файл БД на локальном диске, не на NFS.

• Бэкапы через .backup или согласованные снапшоты, плюс регулярный тест восстановления.

• Заранее определён критерий «когда переезжаем на PostgreSQL/MySQL».

Итоги

SQLite — отличный инструмент, если использовать его как локальную транзакционную БД с ограниченной конкурентностью записи. Включайте WAL, задавайте ожидание блокировок, держите транзакции короткими и не кладите базу на NFS.

Если же вы упёрлись в параллельные записи и тратите время на борьбу с блокировками вместо развития продукта — это нормальный момент для миграции на PostgreSQL/MySQL. В продакшене побеждает не «самая быстрая база на бенчмарке», а та, которая даёт предсказуемость, наблюдаемость и масштабирование под ваш сценарий.