SQLite перестал справляться: как переехать на взрослую базу

MAATRIX

Проект стартовал с SQLite — не потому что «дёшево», а потому что это было правильное решение: ноль конфигурации, файл базы в репозитории, никакого отдельного сервера для локальной разработки. Спустя год в логах стали появляться database is locked, фоновые воркеры начали ждать друг друга на записи, а продакту нужны права доступа по ролям и репликация для отчётов. Это не значит, что вы выбрали SQLite неправильно тогда — это значит, что проект вырос из класса задач, для которого SQLite создавалась. Разберём, как отличить реальный предел от временной перегрузки и как переехать на PostgreSQL или MySQL без потери данных и лишнего простоя.

Признаки, что SQLite уже не тянет

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

• SQLITE_BUSY / database is locked регулярно, а не эпизодически, даже при разумном busy_timeout и после того, как запись в приложении уже сериализована через одну очередь.
• Несколько независимых процессов или реплик приложения (несколько подов за балансировщиком, несколько воркеров на разных машинах) пишут в одну логическую базу как норма нагрузки, а не как редкий случай.
• Задержка записи растёт вместе с числом писателей, хотя диск и CPU не загружены — узкое место именно в очереди на эксклюзивную блокировку, это видно по профилю: время в состоянии «ждёт лока» растёт быстрее, чем время самого INSERT.
• Нужен сетевой доступ к базе с нескольких машин — SQLite открывается только локальным процессом через файловую систему, никакого протокола для удалённых клиентов у неё нет.
• Нужны права доступа на уровне ролей, а не только на уровне файловой системы ОС.
• Нужна репликация или отказоустойчивость — у SQLite нет встроенной сетевой репликации между инстансами, только нишевые сторонние надстройки поверх WAL.
• Нужно партиционирование больших таблиц, полноценные оконные функции для тяжёлой аналитики, параллельное выполнение запросов — возможности, которых во встраиваемой библиотеке нет по архитектуре.

Если у вас один активный писатель (даже если он обслуживает запросы от многих источников, сериализуя их внутри приложения) — это ещё не повод переезжать. Переезд оправдан, когда писателей реально несколько и это системная ситуация, а не всплеск нагрузки, который решается батчингом транзакций или включением WAL.

Куда переезжать: PostgreSQL или MySQL

Оба варианта закрывают проблему SQLite одинаково — сервер принимает соединения по сети и разруливает конкуренцию на уровне строк (row-level locking, MVCC), а не блокирует весь файл целиком. Разница между ними — не в том, кто «лучше», а в том, что удобнее конкретному проекту:

Если нет специфичных требований к экосистеме (ORM, уже используемые расширения, команда, знакомая с одной из СУБД), для переезда с SQLite чаще проще идти в PostgreSQL — прежде всего из-за pgloader, который умеет читать файл SQLite напрямую и переносить схему и данные с разумным маппингом типов из коробки. Подробное сравнение — в статье «PostgreSQL или MySQL: что выбрать для сервера», а установка целевого сервера — в статье «Как установить и настроить PostgreSQL на VPS». Дальше план дан на примере PostgreSQL, но логика шагов та же для MySQL.

Шаг 1. Экспорт данных в универсальный формат

Не переносите данные напрямую SQL-дампом SQLite в целевую СУБД — синтаксис .dump заточен под саму SQLite (AUTOINCREMENT, отсутствие типов у колонок, свои функции) и в PostgreSQL или MySQL не заведётся без ручной правки. Практичнее взять один из двух путей.

Через pgloader, если едете в PostgreSQL — он сам читает файл .db и переносит и схему, и данные за один проход:

# на сервере с pgloader и доступом к обеим базам
pgloader sqlite:///var/data/app.db \
  postgresql://appuser:secret@localhost/app_db

Это не значит, что миграцию можно доверить одной команде вслепую — дальше в статье разбираем, что именно нужно проверить после такого переноса.

Через CSV, если нужен полный контроль над каждым шагом (в том числе для MySQL, где готового прямого коннектора из SQLite нет):

sqlite3 /var/data/app.db <<'EOF'
.headers on
.mode csv
.output users.csv
SELECT * FROM users;
.output orders.csv
SELECT * FROM orders;
EOF

CSV — универсальный формат, который прочитает любая СУБД, но у него есть свои грабли: значения NULL в SQLite при экспорте в CSV превращаются в пустую строку, неотличимую от реального пустого текста. Если в таблице есть текстовые поля, где NULL и '' — разные по смыслу значения, экспортируйте такие таблицы отдельно с явной меткой:

.mode csv
.output orders_export.csv
SELECT id, COALESCE(comment, '\N') AS comment FROM orders;

а при импорте в PostgreSQL указать этот же маркер как признак NULL:

\copy orders FROM 'orders_export.csv' WITH (FORMAT csv, HEADER true, NULL '\N');

Перед экспортом зафиксируйте базу от параллельной записи — либо переведите приложение в режим только для чтения, либо снимите копию файла атомарно (sqlite3 /var/data/app.db ".backup /tmp/app_snapshot.db", это безопасно даже при активных писателях) и экспортируйте уже из снапшота, чтобы не унести в дамп половину незакоммиченной транзакции.

Шаг 2. Схема в целевой СУБД: различия типов данных

SQLite использует динамическую типизацию с «type affinity» — колонка объявлена как INTEGER, но фактически может хранить текст, если приложение туда его когда-то записало и SQLite не отказал (по умолчанию она это разрешает). PostgreSQL и MySQL типы проверяют строго и такую вольность не простят при импорте. Прежде чем создавать схему в целевой базе, стоит явно свести типы, а не полагаться на автоматический маппинг вслепую:

Практические шаги:

1. Выгрузите фактическую схему (sqlite3 app.db .schema) и пройдитесь по каждой колонке — не по объявленному типу, а по тому, что там реально лежит: SELECT typeof(column), count(*) FROM table GROUP BY 1; покажет, если в «числовой» колонке завалялись строки.
2. Создайте схему в целевой базе руками или через pgloader --with "create tables", но обязательно проверьте результат — автомаппинг pgloader в целом надёжен, но булевы поля и даты он иногда угадывает по эвристике, а не по факту.
3. Явно перенесите ограничения, которых в SQLite могло не быть по умолчанию. PRAGMA foreign_keys в SQLite выключен по умолчанию для обратной совместимости — если приложение никогда не включало его явно, в данных вполне могут быть «осиротевшие» внешние ключи, которые благополучно проходили годами и всплывут только при создании FOREIGN KEY в целевой СУБД.
4. Заведите индексы заново — pgloader переносит их автоматически, при ручном переносе их придётся выписать из .schema SQLite и создать через CREATE INDEX уже под конкретный план запросов целевой СУБД, а не один в один.

Шаг 3. Перенос данных, проверка целостности и переключение

Импорт данных — не конечная точка, а начало проверки. Минимальный набор сверок, который стоит пройти перед тем, как выключать SQLite:

Построчные счётчики по каждой таблице — самая грубая, но обязательная проверка:

-- в SQLite
SELECT 'users', count(*) FROM users
UNION ALL SELECT 'orders', count(*) FROM orders;

-- в PostgreSQL
SELECT 'users', count(*) FROM users
UNION ALL SELECT 'orders', count(*) FROM orders;

Если числа разошлись — ищите причину до разбора остального: чаще всего это отфильтрованные при импорте строки с ошибкой типа (многие загрузчики по умолчанию пропускают битую строку и пишут в лог, а не останавливаются).

Контрольные суммы по содержимому, а не только по количеству строк — счётчики совпадают, а данные могут быть перепутаны местами или обрезаны:

-- пример для PostgreSQL, аналогично для SQLite с md5() как расширением
SELECT md5(string_agg(id::text || coalesce(email,'') , ',' ORDER BY id))
FROM users;

Сравнивать построчный хеш для каждой таблицы дешевле и надёжнее, чем визуально сверять выгрузки — особенно для таблиц на миллионы строк, где diff двух CSV попросту не открыть.

Проверка последовательностей (sequences) в PostgreSQL. После импорта id-шники перенесены, но счётчик автоинкремента на новой стороне не знает о них и по умолчанию начинает с 1 — первая же вставка после переезда столкнётся с конфликтом первичного ключа:

SELECT setval('users_id_seq', (SELECT max(id) FROM users));

Это нужно сделать для каждой таблицы с автогенерируемым ключом — забытый setval — одна из самых частых причин падения приложения в первые минуты после переключения.

Проверка внешних ключей и точечная сверка выборок. После того как FOREIGN KEY создан, выполните SELECT по нескольким случайным записям каждой таблицы и сравните значения полей вручную между старой и новой базой — контрольные суммы ловят массовые расхождения, но не всегда ловят единичную порчу конкретной строки при преобразовании типа (например, обрезание слишком длинной строки под VARCHAR(n), если лимит выставлен туже, чем было в SQLite).

Переключение (cutover). Практичная последовательность, которая минимизирует простой:

1. Перевести приложение в режим обслуживания или как минимум только для чтения.
2. Снять финальный снапшот SQLite (.backup) и импортировать дельту, накопившуюся с первой пробной прогонки миграции.
3. Прогнать все проверки из этого раздела ещё раз на финальных данных.
4. Переключить конфигурацию приложения на новую СУБД и снять режим обслуживания.
5. Оставить файл SQLite нетронутым (read-only, с бэкапом) на несколько дней как страховку для быстрого отката, если что-то всплывёт уже под боевой нагрузкой.

Если база большая и простой критичен, стоит заранее посмотреть на стратегии переноса с минимальным даунтаймом — они разобраны в статье «Перенос большой базы данных с минимальным даунтаймом»: двойная запись на переходный период, поэтапная сверка вместо одного большого окна простоя.

Частые вопросы