PostgreSQL vs SQLite: шлях масштабування — як перейти з файлової БД без простою

October 30, 2025 • February 3, 2026 • Читання: 6 хв • Views: 11

Було корисно?

Ви не «переростаєте» SQLite у вівторок о 14:00. Ви переростаєте його о 2:07, коли випадкове розгортання додає ще одного записувача, ваші графіки латентності перетворюються на сучасне мистецтво, і хтось запитує, чому «файл» блокує флот веб-серверів.

Ось прагматичний шлях масштабування: як перейти з SQLite (відмінна вбудована база даних) на PostgreSQL (відмінний сервер бази даних) з без простою — або, реалістичніше, з простоями настільки короткими, що їх можна сховати між перевірками здоровʼя балансувальника навантаження. Ми поговоримо про dual writes, захоплення змін, паритет даних, механіку cutover і режими відмов, які ви реально зустрінете в продакшені.

Коли SQLite — правильне рішення (і коли ні)

SQLite — це не «іграшкова БД». Це серйозна інженерна робота: транзакційна SQL-база даних у вигляді однієї бібліотеки, що пише в один файл. Вона вбудована. Вона портативна. Її надзвичайно просто розгорнути. Такий набір властивостей настільки потужний, що його легко неправильно використати.

Коли SQLite перемагає

Ваш додаток здебільшого одно-процесний або має одного записувача, і база знаходиться на локальному диску.
Потрібні низькі операційні витрати: немає демона, немає мережі, немає управління користувачами, нема бекапів, що вимагають консистентних знімків по хостах.
Ви доставляєте ПЗ на пристрої на краю мережі, десктопи, мобільні пристрої, кіоски або апарати. SQLite фактично є там lingua franca.
«База даних» — частина вашого артефакту: упакований каталог, статичний набір даних, кеш, який можна відновити, або локальна черга.

Коли SQLite починає шкодити

Ви вводите кілька одночасних записувачів (або думали, що ні, але код каже інакше).
Ви масштабуєтеся горизонтально і тепер кілька інстансів додатка повинні розділяти одну правду.
Файлова система поводиться дивно: мережеві файлові системи, оверлейні шари контейнерів, повільні диски, галасливі сусіди.
Потрібні операційні можливості, такі як керований HA, репліки для читання, тонкий доступ, online-зміни схеми та вбудована спостережуваність на сервері.

Ключ — модель блокувань у SQLite: вона добре серіалізує одного записувача і багато читачів, але «добре» не означає «безмежно». Якщо навантаження еволюціонує в «багато дрібних записів з кількох хостів», PostgreSQL стає дорослою людиною в кімнаті.

Короткий жарт (1/2): SQLite схожий на велосипед: ідеальний, поки ви не спробуєте тягнути човен ним.

PostgreSQL vs SQLite: ключові рішення для продакшену

Паралелізм і блокування: справжня причина міграцій

SQLite координує доступ до одного файлу бази даних. Читачі можуть бути одночасними; записувачі більш обмежені. У режимі WAL ви отримуєте значно кращу конкуренцію — читачі менше блокують записувачів — але у вас все одно є одне основне місце, де живе істина: цей файл.

PostgreSQL — це багатопроцесний сервер з MVCC, розроблений для одночасних записів від багатьох клієнтів, з блокуванням на рівні рядка, видимістю снапшотів і окремим WAL для довговічності та реплікації. Якщо ви будуєте щось багатокористувацьке, з багатьма воркерами або на кількох хостах, PostgreSQL просто для цього створений.

Довговічність і відновлення: «виконано» має означати одне й те саме щодня

SQLite може бути дуже довговічним, якщо його правильно налаштувати, але також легко налаштувати в режим «швидко, але можливо» (synchronous=NORMAL/OFF, налаштування журналу, небезпечна поведінка fsync в певних середовищах). PostgreSQL має свої підводні камені, але загалом його значення за замовчуванням орієнтовані на довговічність сервера з WAL і відновленням після краху як першокласною функцією.

Операційна ергономіка

SQLite робить ваш додаток оператором бази даних. Це нормально, поки ви не виконуєте роботу оператора погано. PostgreSQL робить оператором сервер бази даних, який все одно потрібно запускати, патчити, бекапити і моніторити — але межі чисті, а екосистема інструментів зріла.

Планувальник запитів і функції

Планувальник PostgreSQL, індекси та розширені можливості (CTE, віконні функції, часткові індекси, GIN/GiST, JSONB, згенеровані стовпці, надійні обмеження) змінюють спосіб моделювання даних і допомагають тримати латентність передбачуваною. SQLite теж має багато функцій, але підхід «велика система під навантаженням» зазвичай передбачає серверну БД.

Це не питання морального верховенства. Це відповідність архітектурі. SQLite не «гірший». Він «інший», і різниця починає вам виставляти рахунки, коли навантаження змінюється.

Цікаві факти та коротка історія (що впливає на рішення)

SQLite створили в 2000 році Д. Річардом Гіппом, щоб уникнути адміністративного навантаження та замінити ad-hoc плоскі файли в вбудованих системах.
SQLite у публічному домені, що нетипово для рушія баз даних і є головною причиною його широкого розповсюдження без ліцензійних проблем.
SQLite прагне бути «малим, швидким, надійним» як бібліотека — без окремого серверного процесу — тож ваш додаток переймає обовʼязки БД (права файлів, місце на диску, семантика блокувань).
PostgreSQL походить від POSTGRES (1986), наукового проєкту, що еволюціонував у продакшен-grade реляційну систему з акцентом на коректність і розширюваність.
MVCC у PostgreSQL став основою для високої конкуренції без блокувань читачів/записувачів у типових OLTP-навантаженнях.
Режим WAL у SQLite був великим кроком для конкуренції; багато команд «виявляють» WAL лише після першої хвилі блокувань.
Реплікація PostgreSQL дозрівала з часом: фізична streaming-реплікація стара й перевірена; логічна реплікація новіша і змінює підхід до міграцій і розгортань.
SQLite — найпоширеніший рушій БД за кількістю пристроїв/пакетів, навіть якщо ніхто «не запускає» його як сервер.

Шлях масштабування: від файлової БД до серверної БД без простою

Мета міграції проста: продовжувати обслуговувати запити, переміщаючи авторитативний датасет зі файлу SQLite до PostgreSQL, потім переключити читання/записи на PostgreSQL з мінімальним ризиком.

Якщо ваш додаток може терпіти вікно техобслуговування — добре. Зробіть так. Але якщо не може — бо ви B2B SaaS з клієнтами в усіх часових поясах або керуєте флотом апаратів, що ніколи не сплять — вам потрібен план, що ставиться до міграції як до релізу в продакшені: поступовий, спостережуваний, відворотний.

Крок 0: Визначте, що означає «без простою» насправді

Міграції без простою зазвичай означають одне з:

Видимий нульовий простій: користувачі не помічають, бо повтори й перевірки здоровʼя поглинають мить.
Нульовий простій записів: читання може погіршитися, але записи продовжуються (або навпаки).
Нуль планованого простою: ви не плануєте вікно, але все ще можете мати короткий інцидент при cutover, якщо будете недбалі.

Виберіть ціль. Визначте число. Якщо ваш SLO — 99.9% доступності на місяць, ваш бюджет — близько 43 хвилин. Якщо ви вже витрачаєте цей бюджет на розгортаннях, «міграція без простою» перестає бути демонстрацією і стає стратегією виживання.

Крок 1: Зробіть сторону SQLite максимально здоровою

Перед міграцією стабілізуйте. Міграція підсилює існуючий безлад. Якщо ваша SQLite база вже час від часу пошкоджується або у вас є півдесятка ad-hoc патернів підключення, ви скопіюєте безлад у PostgreSQL і потім надасте йому ярлик «нестабільність Postgres». Фізика так не працює.

Принаймні:

Увімкніть режим WAL, якщо він ще не ввімкнений (якщо середовище не робить це небезпечним).
Стандартизуйтесь поведінку відкриття/закриття підключень і busy_timeout.
Визначте всі шляхи запису. Їх завжди більше, ніж здається.

Крок 2: Делікатно перетворіть схему та типи даних

SQLite динамічно типізований. PostgreSQL — ні. Якщо ви зберігали timestamps «як що підходило», PostgreSQL змусить вибрати: timestamptz або timestamp, числові типи, text vs jsonb, обмеження, які ви раніше «пропускали».

Виберіть суворість зараз. Міграції — час сплатити типовий борг. Інакше ви заплатите пізніше з відсотками у вигляді інцидентів.

Крок 3: Побудуйте backfill (масове копіювання), яке можна перезапустити

Перший великий копій — це backfill: витяг зі SQLite і завантаження у PostgreSQL. Ставтеся до нього як до будь-якої іншої job в продакшені:

Идемпотентний: безпечний для багаторазового запуску.
Порціонований: не вибухає памʼять чи журнали транзакцій.
Спостережуваний: логувати кількості, тривалості та помилки.

Можливість перезапуску важлива, бо ви знайдете невідповідності. Ви завжди їх знайдете.

Крок 4: Тримайте PostgreSQL синхронізованим: dual writes або захоплення змін

У вас два практичні підходи:

Патерн A: Dual writes (додаток пише в обидві)

Плюси: концептуально просто, без складних інструментів. Мінуси: складна обробка відмов; можна створити split-brain на рівні додатка, якщо не продумати.

Правила для dual writes, які тримають вас подалі від проблем:

Визначте одне джерело істини під час фази dual-write. Зазвичай SQLite залишається авторитативним до cutover.
Робіть записи ідемпотентними. Використовуйте стабільні первинні ключі і безпечно повторюйте.
Логувати та звіряти помилки. «Вистрілив і забув» dual writes — це шлях до тихих втрат даних.

Патерн B: Change data capture (CDC) зі SQLite

SQLite не має вбудованої логічної реплікації, як PostgreSQL. Але ви можете наблизити CDC так:

Додати тригери, які пишуть у додаткову таблицю змін.
Використати читач WAL підхід (складніше, крихке і залежне від середовища).
Писати зміни в outbox таблицю у SQLite, яку воркер відправляє до PostgreSQL.

Тригери + outbox часто — найменш поганий шлях. Це не гламурно. Але працює.

Крок 5: Шадоу-читання та перевірки паритету

Перш ніж переключити читання, шадоу їх: читайте зі SQLite як раніше, паралельно читайте з PostgreSQL у фоні, порівнюйте результати і виводьте метрики. Це ловить проблеми з типами, відмінності колацій і семантичні відмінності запитів.

Не порівнюйте всю корисність сліпо. Порівнюйте те, що має значення: повернені первинні ключі, підрахунки, агрегати, конкретні поля з детермінованим упорядкуванням. PostgreSQL і SQLite можуть відрізнятися в порядку без явного ORDER BY.

Крок 6: Спочатку переключайте читання, потім записи (зазвичай)

Поширена низькоризикова послідовність:

Backfill у PostgreSQL.
Dual-write при цьому SQLite — джерело істини.
Shadow-read і порівняння.
Переключіть читання на PostgreSQL (зберігаючи dual writes).
Переключіть записи на PostgreSQL (SQLite тимчасово стає резервом / аудитом).
Видаліть dual-write після наростаючого часу впевненості.

Cutover записів — момент, який може боліти. Cutover читань — де ви вчитеся. Розбивайте етапи.

Крок 7: Тримайте реальний план відкату

Плани відкату зазнають поразки, бо вони припускають ідеальну симетрію. Після того як ви переключили записи на PostgreSQL, відкат до SQLite означає, що треба відтворити ці записи назад. Якщо ви не побудували цей pipeline, план відкату — це презентація, а не можливість.

Реалістичний план: після cutover записів тримайте dual-write або outbox доти, поки відкат залишається можливим. Визначте часовий горизонт (години/дні). Зміряйте складність відтворення. Практикуйте один раз у стенді зі справжнім набором даних.

Цитата (перефразована ідея): Вернер Вогельс (CTO Amazon) часто підкреслює ідею, що «все ламається, весь час», тож ви проєктуєте системи, припускаючи відмову, а не сподіваючись, що її не буде.

Практичні завдання: команди, виводи та які рішення приймати

Це реальні завдання, які можна виконати при плануванні, виконанні або усуненні несправностей міграції. Кожне включає (1) команду, (2) приклад виводу і (3) рішення, яке з нього випливає.

Завдання 1: Визначити режим журналу SQLite та поведінку busy timeout

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA journal_mode; PRAGMA synchronous; PRAGMA busy_timeout;"
wal
2
5000

Що це означає: режим WAL увімкнено; synchronous=2 (FULL); busy_timeout=5000ms.

Рішення: WAL корисний для конкурентності; FULL безпечніший, але повільніший. Якщо бачите контеншн блокувань, збільшіть busy_timeout і зменшіть частоту записів. Не виставляйте synchronous=OFF як «фікс продуктивності», якщо не любите пояснювати втрати даних.

Завдання 2: Швидко виявити контеншн блокувань SQLite

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA compile_options;" | grep -i threads
THREADSAFE=1

Що це означає: SQLite зкомпільовано з threadsafe, тож ваші проблеми блокувань не через те, що sqlite однопотоковий. Вони викликані робочим навантаженням і паттернами доступу.

Рішення: Зосередьтесь на області транзакцій і кількості записувачів, а не на фольклорі. Інструментуйте місця запису.

Завдання 3: Отримати розмір таблиць у SQLite (щоб оцінити час backfill)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "SELECT name, SUM(pgsize) AS bytes FROM dbstat GROUP BY name ORDER BY bytes DESC LIMIT 5;"
events|2147483648
users|402653184
sessions|268435456
sqlite_schema|4096

Що це означає: events близько 2 GiB; стратегія backfill потребує порціонування і, можливо, партиціювання в PostgreSQL.

Рішення: Якщо ви зробите backfill в одній транзакції, ви підірвете WAL і, можливо, диск. Плануйте порціоноване копіювання по первинному ключу або часових вікнах.

Завдання 4: Перевірити цілісність SQLite перед копіюванням

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA integrity_check;"
ok

Що це означає: Базова перевірка корупції пройшла.

Рішення: Якщо результат не «ok», зупиніться. Виправте корупцію спочатку (відновлення з бекапу, перебудова з джерел подій). Міграція корупції просто переносить провину.

Завдання 5: Здампити схему SQLite (не довіряйте памʼяті)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db ".schema users"
CREATE TABLE users(
  id TEXT PRIMARY KEY,
  email TEXT,
  created_at TEXT
);
CREATE INDEX idx_users_email ON users(email);

Що це означає: timestamps зберігаються як TEXT. Це стане предметом дискусії пізніше.

Рішення: Визначте, як мапити created_at в timestamptz і як парсити старі значення. Побудуйте функцію конверсії і тести.

Завдання 6: Створити ролі PostgreSQL і виділену базу

cr0x@server:~$ sudo -u postgres psql -c "CREATE ROLE myapp LOGIN PASSWORD 'REDACTED' NOSUPERUSER NOCREATEDB NOCREATEROLE;"
CREATE ROLE

Що це означає: У вас є роль з найменшими привілеями для додатка.

Рішення: Не запускайте додаток як postgres. Якщо будете — рано чи пізно зробите щось цікаве і незворотне.

Завдання 7: Перевірити, що PostgreSQL справді довговічний (fsync on, full_page_writes on)

cr0x@server:~$ sudo -u postgres psql -tAc "SHOW fsync; SHOW synchronous_commit; SHOW full_page_writes;"
on
on
on

Що це означає: Ви не в режимі «YOLO довговічність».

Рішення: Тримайте ці налаштування ввімкненими для продакшена. Якщо продуктивність погана — виправляйте схему та I/O, а не коректність.

Завдання 8: Оцінити ємність PostgreSQL і ризик bloat (базовий розмір БД)

cr0x@server:~$ sudo -u postgres psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"
  datname  | pg_size_pretty
-----------+----------------
 postgres  | 8456 kB
 template1 | 8280 kB
 template0 | 8208 kB
(3 rows)

Що це означає: Базова база невелика; місця вистачає. Ви ось-ось зробите її не маленькою.

Рішення: Підтвердіть запас дискового простору для: даних + індексів + WAL + оверхед + бекапів. Якщо не можете це озвучити — ви не готові до cutover.

Завдання 9: Backfill за допомогою безпечного порційного pipeline (приклад: CSV + COPY)

cr0x@server:~$ sqlite3 -header -csv /var/lib/myapp/app.db "SELECT id,email,created_at FROM users;" > /tmp/users.csv
cr0x@server:~$ sudo -u postgres psql myapp -c "\copy users(id,email,created_at) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);"
COPY 120543

Що це означає: Завантажено 120,543 рядків.

Рішення: Якщо COPY повільний, перевірте індекси і обмеження. Під час backfill завантажуйте в тимчасову таблицю без важких індексів, а потім створюйте індекси після завантаження.

Завдання 10: Перевірити відповідність кількості рядків (грубий паритет)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "SELECT COUNT(*) FROM users;"
120543
cr0x@server:~$ sudo -u postgres psql myapp -tAc "SELECT COUNT(*) FROM users;"
120543

Що це означає: Кількості співпадають для цієї таблиці.

Рішення: Кількості необхідні, але недостатні. Якщо кількості збігаються, але дані відрізняються — ви все одно втрачаєте.

Завдання 11: Перевірити покриття ключів і відсутні ID (виявити мовчазні втрати)

cr0x@server:~$ sqlite3 -csv /var/lib/myapp/app.db "SELECT id FROM users ORDER BY id LIMIT 5;"
001a,00b9,00c1,00d0,00f2
cr0x@server:~$ sudo -u postgres psql myapp -tAc "SELECT id FROM users ORDER BY id LIMIT 5;"
001a
00b9
00c1
00d0
00f2

Що це означає: Низькі ID існують; упорядкування здається узгодженим для цього прикладу.

Рішення: Також перевіряйте випадкові вибірки та максимальні діапазони; баги ховаються у «хвості».

Завдання 12: Перевірити повільні запити PostgreSQL під час shadow reads

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
                query                 | calls | mean_exec_time
--------------------------------------+-------+---------------
 SELECT * FROM events WHERE user_id=$1 |  9321 |        187.42
 SELECT * FROM sessions WHERE id=$1    | 21144 |         12.11
(2 rows)

Що це означає: Ваш найгарячіший запит повільний у PostgreSQL; ймовірно бракує індексу або погана форма запиту.

Рішення: Додайте індекс (events(user_id, created_at) можливо), або змініть запит, щоб вибирати конкретні колонки. Не переключайте читання, поки це не під контролем.

Завдання 13: Підтвердити використання індексів за допомогою EXPLAIN (не вгадуйте)

cr0x@server:~$ sudo -u postgres psql myapp -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id='00f2' ORDER BY created_at DESC LIMIT 50;"
 Limit  (cost=0.42..12.55 rows=50 width=128) (actual time=0.311..0.829 rows=50 loops=1)
   Buffers: shared hit=210
   ->  Index Scan Backward using idx_events_user_created_at on events  (cost=0.42..812.11 rows=3400 width=128) (actual time=0.309..0.814 rows=50 loops=1)
         Index Cond: (user_id = '00f2'::text)
 Planning Time: 0.220 ms
 Execution Time: 0.901 ms

Що це означає: Index scan; виконання менше мілісекунди. Це бажаний результат.

Рішення: Якщо бачите sequential scans на великих таблицях — не сперечайтеся з планувальником — виправте схему/індекси або запит.

Завдання 14: Слідкуйте за насиченістю підключень (класична проблема при cutover на Postgres)

cr0x@server:~$ sudo -u postgres psql -tAc "SHOW max_connections; SELECT count(*) FROM pg_stat_activity;"
200
187

Що це означає: Ви вже близькі до max connections.

Рішення: Додайте пулінг підключень (PgBouncer), зменшіть розміри пулів у додатку або масштабируйте БД. Якщо переключитесь зараз, отримаєте сплески підключень і самонанесені аварії.

Завдання 15: Монітор WAL під час backfill (не заповніть диск мовчки)

cr0x@server:~$ sudo -u postgres psql -tAc "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS wal_bytes_since_start;"
42 GB

Що це означає: Великий обʼєм WAL згенеровано (приклад виводу). Під час масових завантажень WAL може роздутися.

Рішення: Переконайтеся у запасі дискового простору; розгляньте пакетування і чекпоїнти. Якщо реплікуєте, переконайтеся, що репліки встигають, інакше ви накопичуватимете WAL і заповните диск.

Завдання 16: Підтвердити відставання реплікації (якщо використовуєте репліки для безпеки)

cr0x@server:~$ sudo -u postgres psql -c "SELECT application_name, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
 application_name |   state   | write_lag | flush_lag | replay_lag
------------------+-----------+-----------+-----------+------------
 standby1          | streaming | 00:00:01  | 00:00:02  | 00:00:03
(1 row)

Що це означає: Реплікація здорова з низьким відставанням.

Рішення: Не переключайте записи, якщо репліки відстають на хвилини і ваш план відновлення припускає, що вони актуальні.

Завдання 17: Перевірити обмеження, які SQLite не накладав так, як ви думали

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT COUNT(*) FROM users WHERE email IS NULL;"
14

Що це означає: У вас є null email-адреси. Якщо ви планували email NOT NULL, міграція зламається або додаток — поведінка зміниться.

Рішення: Вирішіть: виправити дані (backfill дефолтів), змінити обмеження або додати staged constraint пізніше. Не «ігноруйте тимчасово» це назавжди.

Завдання 18: Перевірка перед cutover: додаток вказує на Postgres і health checks проходять

cr0x@server:~$ systemctl restart myapp
cr0x@server:~$ journalctl -u myapp -n 20 --no-pager
Dec 30 12:11:02 server myapp[19422]: db: connected to postgres host=pg1 dbname=myapp
Dec 30 12:11:02 server myapp[19422]: migrations: none pending
Dec 30 12:11:03 server myapp[19422]: http: listening on :8080

Що це означає: Додаток використовує PostgreSQL і стартував чисто.

Рішення: Проведіть canary-реліз спочатку. Якщо лише одна інстанція на Postgres, ви можете швидко відкотитись.

Короткий жарт (2/2): Ніщо не є більш постійним, ніж «тимчасовий dual-write», який ніхто не наважується видалити.

Швидкий план діагностики: знайти вузьке місце за хвилини

Це шлях «pager дзвонить, Slack горить». Ви посеред міграції або після cutover і щось повільне або зависло. Перевірте це в порядку.

Перше: це контеншн блокувань чи вичерпання підключень?

Симптоми SQLite: помилки «database is locked», підвищена латентність записів, потоки зависають на commit.
Симптоми Postgres: таймаути підключень, забагато клієнтів, пул додатка чекає.

cr0x@server:~$ sudo -u postgres psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"
 state  | count
--------+-------
 active | 42
 idle   | 131
(2 rows)

Рішення: Якщо active і idle високі — ймовірно у вас надмірні пули в додатку. Якщо active заповнений і запити повільні — проблема в запитах/індексах.

Друге: чи обмежений I/O бази даних?

cr0x@server:~$ iostat -x 1 3
Device            r/s     w/s   rkB/s   wkB/s  await  %util
nvme0n1         120.0   340.0  8200.0 42000.0   9.4  92.1

Рішення: Якщо %util близько 100% і await високий — ви I/O bound. Припиніть додавати індекси під час міграції. Зменшіть швидкість записів, батчуйте або масштабируйте сховище/інстанс.

Третє: чи повільні запити — причина (і чи вони нові)?

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;"
                  query                   | calls | total_exec_time
------------------------------------------+-------+----------------
 SELECT * FROM events WHERE user_id=$1     |  9233 |       1730042.0
 UPDATE users SET last_seen_at=$1 WHERE id=$2 | 60321 |        402112.3
(2 rows)

Рішення: Якщо один запит домінує у загальному часі — виправляйте його першим. Додайте відсутній індекс, зменшіть повернутий набір колонок або кешуйте. Не «тюньте Postgres» загально.

Четверте: чи autovacuum встигає (повільний важкий режим Postgres)?

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
 relname | n_dead_tup |        last_autovacuum
---------+------------+-------------------------------
 events  |   18200421 | 2025-12-30 11:40:22.12345+00
(1 row)

Рішення: Якщо dead tuples величезні і autovacuum застарілий — таблиця з оновленнями деградує. Налаштуйте autovacuum для цієї таблиці або переробіть оновлення.

Пʼяте: чи відставання реплікації ламає ваші припущення безпеки?

cr0x@server:~$ sudo -u postgres psql -c "SELECT application_name, replay_lag FROM pg_stat_replication;"
 application_name | replay_lag
------------------+------------
 standby1          | 00:05:12
(1 row)

Рішення: Якщо відставання хвилини — відновлення може втратити дані відносно primary, якщо ви не використовуєте синхронну реплікацію (яка має свої компроміси). Скоригуйте cutover і припущення щодо довговічності.

Три корпоративні історії (біль, жаль та одна тиха перемога)

Історія 1: Інцидент через неправильне припущення

Вони були середньою SaaS з «простим» планувальником завдань. Планувальник зберігав стан у SQLite, бо починався як утиліта на одній ВМ. З часом він став сервісом: кілька воркерів, автоскейлінг і балансувальник навантаження. Файл SQLite перемістили на спільну мережеву файлову систему, бо «всі воркери повинні його бачити».

Неправильне припущення було такого типу: спільна файловa система робить файлову базу спільною базою. У спокійний день це працювало. У зайнятий день система поводилася як круговий рух з однією смугою і тисячею машин. Записувачі накопичувалися за блокуваннями. Читачі повторювалися. Латентність зростала. Додаток почав таймаутитися, що викликало повтори задач, що збільшувало записи, що збільшувало блокування. Зворотний звʼязок, що виглядав як «раптовий ріст».

Коли вони нарешті це побачили, курильним стовпом була помилка, що буквально казала «database is locked». Але через те, що система була розподілена, кожний вузол бачив маленький шматочок болю. Ніхто не бачив всю картину, поки не агрегували логи. До того моменту клієнти вже відчули наслідки.

Виправлення не було героїчним. Вони перестали прикидатися, що спільний файловий простір — база даних. Перенесли стан планувальника в PostgreSQL, поставили PgBouncer попереду і використали патерн single-writer для кількох критичних таблиць. Найцікавіше: після фіксу вони знайшли купу «оптимізацій» (sleep loops, backoff hacks), що були там лише щоб терпіти SQLite блокування. Ці хаки потім стали багами латентності в PostgreSQL, бо затримували легітимну роботу.

Історія 2: Оптимізація, що повернулась бумерангом

Рітейл-компанія мала локальний кеш SQLite на кожному хості додатка. Вони хотіли мігрувати авторитативне сховище в PostgreSQL, але не хотіли «витрачати час» на коректні dual-write механіки. Тож зробили те, що люди роблять під тиском дедлайну: зробили записи в SQLite асинхронними.

Конкретно: вони чергували записи в памʼяті і скидали їх батчами кожні кілька секунд. У бенчмарках це працювало. У продакшені, звісно, стало інакше. Під навантаженням батчі росли. Памʼять зростала. Потім деплой рестартонули сервіс і in-memory черга зникла. Користувачі побачили застарілі корзини і пропущені оновлення. Постмортем заголовок був приблизно: «Ми винайшли втрату даних, щоб зекономити 15% CPU».

Коли вони намагалися виправити, додали чергу на диску. Краще, але все ще тонко: черга була на тому ж диску, що і SQLite, і сплески flush викликали знову контеншн блокувань. Вони зменшили системні виклики, але збільшили гірший випадок латентності. Це був чистий програш: система стала складнішою для розуміння і все ще не масштабувалася.

Врешті вони зробили нудну, але правильну річ: outbox таблицю в SQLite з тригерами, надійний корабельник до PostgreSQL і дашборд паритету. Це не було «швидко», але було детерміновано. Їх частота інцидентів знизилась, головно тому що вони перестали дивувати себе.

Історія 3: Нудна, але правильна практика, що врятувала день

B2B компанія спланувала безпростійний cutover зі SQLite до PostgreSQL для сервісу конфігурацій. Набір даних був малий, але зона ураження величезна: кожен запит в платформі читав конфіг. Вони ставилися до міграції як до вправи на надійність, а не лише перенесення даних.

Вони побудували шадоу-читання рано. Кожне читання конфігу все ще бралося з SQLite, але бекграунд goroutine запитував той самий ключ у PostgreSQL і порівнював хеш. Розбіжності рахувалися і тагувалися по префіксу ключа. Звучало параноїдально. І було правильно.

За два дні до cutover вони побачили сплеск розбіжностей на ключах з Unicode. Відмінності колації та нормалізації SQLite і PostgreSQL кусали їх: порівняння рядків і упорядкування не були ідентичні, і один код шлях залежав від поведінки «перший матч перемагає» без явного ORDER BY. Без шадоу-читань вони б переключилися і повільно «пошкодили» читання конфігу у способи, що виглядали як «випадкові відмови».

Вони виправили запит, зробили явне сортування і додали міграційне твердження, що відхиляє неоднозначні ключі. День cutover пройшов нудно. Ніхто не помітив. Це найвища похвала для роботи операцій.

Поширені помилки: симптом → корінна причина → виправлення

Ось патерни, що часто зʼявляються при міграціях SQLite → Postgres. Якщо ви впізнали себе — добре. Це означає, що ви можете перестати так робити.

1) Шторм «database is locked» після додавання однієї фічі

Симптом: піки латентності, періодичні помилки запису, багато повторів.
Корінна причина: кілька записувачів збільшили перекриття транзакцій; довгі транзакції або невизначений обсяг транзакцій.
Виправлення: скоротіть транзакції; увімкніть WAL; додайте busy_timeout; консолідуйте записи; або прийміть реальність і перемістіть записи в PostgreSQL.

2) Postgres «повільний» одразу після cutover

Симптом: стрибок P95 латентності; CPU в нормі; I/O помірний; запити повільніші, ніж очікувалося.
Корінна причина: відсутні індекси через прогалини при трансляції схеми; запити використовували особливості SQLite (неявні касти, вільна типізація).
Виправлення: використовуйте pg_stat_statements і EXPLAIN (ANALYZE); додайте правильні індекси; примусьте типи; перепишіть запити явними.

3) Унікальні обмеження раптово падають під час backfill

Симптом: COPY падає з помилками duplicate key.
Корінна причина: SQLite дозволяв дублі через відсутність обмеження або значення відрізнялися тільки колацією/регістром/пробілами.
Виправлення: проведіть аудит дублів у SQLite перед міграцією; визначте правила канонізації; реалізуйте їх у додатку і в скриптах міграції.

4) Таймстампи стають нісенітницею (зсув на години або некоректні)

Симптом: дані виглядають зі зсувом; запити по вікну часу пропускають записи.
Корінна причина: SQLite зберігав timestamps як TEXT без timezone; Postgres парсить у timestamp або timestamptz з різними припущеннями.
Виправлення: Виберіть timestamptz, якщо немає підстав інакше; явно парсьте старі рядки; встановіть часові пояси зʼєднання свідомо.

5) Міграція «спрацювала», але дані тонко відсутні

Симптом: кількості збігаються; користувачі скаржаться на зниклі елементи; аудити показують прогалини.
Корінна причина: backfill запустився один раз, але нові записи під час backfill не були скопійовані; dual-write не обробляв відмов; немає перевірок паритету.
Виправлення: реалізуйте захоплення змін (outbox/trigger) і перевіряйте відставання; запускайте паритетні перевірки; не дозволяйте cutover, поки дрейф не нульовий (або пояснений).

6) Postgres закінчує підключення під сплесками трафіку

Симптом: «too many clients», таймаути, каскадні збої.
Корінна причина: кожна інстанція додатка тримає великий пул; плюс бекграунд джоби; плюс адмін-скрипти; Postgres не іграшка «тред-на-зʼєднання».
Виправлення: Використовуйте PgBouncer; оптимізуйте розміри пулів; зменшіть idle підключення; встановіть statement timeouts і circuit breakers.

7) Autovacuum не встигає після cutover

Симптом: продуктивність погіршується за днями; bloat таблиць; індекси ростуть; VACUUM виконується вічно.
Корінна причина: інтенсивні оновлення (зазвичай для полів статусу) і невід tune autovacuum для таблиць.
Виправлення: Налаштуйте autovacuum thresholds для «гарячих» таблиць; уникайте зайвих оновлень (оновлюйте лише при зміні); розгляньте append-only модель для подій.

Чеклісти / покроковий план (нудно, отже ефективно)

Фаза A: Чекліст перед міграцією

Перерахувати всі SQLite бази, файли та шляхи запису. Якщо не можете їх перерахувати — не зможете їх мігрувати.
Стандартизувати налаштування SQLite: journal_mode, synchronous, busy_timeout.
Визначити цільову версію PostgreSQL і модель розгортання (керована проти self-hosted).
Визначити правила мапінгу типів даних (TEXT timestamps, булеві, JSON-обʼєми, точність чисел).
Визначити політику «джерела істини» для фази dual-write.
Побудувати перевірки паритету: підрахунки, суми, хеші і точкові перевірки для важливих сутностей.

Фаза B: Побудувати механізм міграції

Створити схему PostgreSQL з явними типами і обмеженнями, етапно (почніть помірковано, потім посилюйте).
Backfill у staging-таблиці, потім swap/rename після завантаження і індексації.
Реалізувати захоплення змін (dual writes або SQLite outbox з shipper-ом).
Реалізувати шадоу-читання для репрезентативної частини запитів.
Операціоналізувати: метрики для відставання, кількості невідповідностей, помилок запису і латентності запитів.

Фаза C: План cutover, який можна виконати під стресом

Canary: направте невеликий відсоток читань на PostgreSQL; тримайте SQLite як авторитет.
Поступово розширюйте cutover читань; слідкуйте за error budgets і дашбордами повільних запитів.
Cutover записів: переключіть шлях запису на PostgreSQL, одночасно захоплюючи зміни для відкату (тимчасово).
Заморозьте і валідуйте: коротке вікно, під час якого ви переконуєтесь, що дрейф нульовий і обмеження виконані.
Видаляйте dual write тільки після періоду випікання і успішної вправи «реплей в SQLite» (якщо відкат вимагається політикою).

Фаза D: Закріплення після cutover

Увімкніть statement timeouts для ролі додатка.
Додайте моніторинг відставання реплікації, використання диску і здоровʼя autovacuum.
Проведіть кампанію з посилення обмежень: NOT NULL, CHECK, FK там, де доречно.
Документуйте операційні рукописи: відновлення, failover, rebuild індексів і екстрене зниження навантаження.

Часті питання

1) Чи витримає SQLite великий трафік, якщо увімкнути WAL?

WAL значно допомагає, особливо для читано-важких навантажень з поодинокими записами. Це не перетворює SQLite у серверну базу з багатьма записувачами і хостами. Якщо ваша проблема масштабування — «багато записувачів з багатьох інстансів», WAL — це пластир, а не ліки.

2) Яка найнадійніша стратегія «без простою»?

Backfill → захоплення змін (outbox або dual writes) → shadow reads → cutover читань → cutover записів. Найбезпечніша версія включає дашборди паритету і вікно відкату, під час якого можна відтворити записи назад.

3) Писати dual-write в додатку чи використовувати тригери?

Якщо можете безпечно змінити додаток і впевнені в обробці повторів/ідемпотентності, dual-write в додатку — простіше. Якщо багато додатків пишуть або хочете централізований механізм — тригери SQLite, що пишуть в outbox, можуть бути чистішими. У будь-якому випадку потрібна реконсиляція і спостережуваність.

4) Чому не просто зупинити світ і зробити один великий дамп/рестор?

Якщо можете дозволити — робіть так. Путь «без простою» має більше рухомих частин і більше способів створити тонкий дрейф. Єдиний привід робити його — якщо ви маєте обслуговувати під час міграції.

5) Як боротися з вільною типізацією SQLite при переході до PostgreSQL?

Визначайте типи явно і пишіть код конверсії. Очікуйте мотлоху: числові рядки, порожні таймстампи, змішані формати. Побудуйте шлях карантину для поганих рядків замість того, щоб зупиняти всю міграцію.

6) Що з тими функціями SQLite, що погано мапляться на Postgres?

Найгостріші кути — типова афінність, неявні перетворення, conflict clauses і date/time функції. Аудитуйте запити, додавайте явні касти в Postgres і не покладайтеся на невизначене сортування.

7) Чи потрібен пул підключень для PostgreSQL?

У більшості продакшенів — так. Особливо якщо багато інстансів додатка. PgBouncer поширений, бо оверхед підключень PostgreSQL реальний, а «просто підняти max_connections» зазвичай закінчується погано.

8) Як довести паритет даних крім підрахунків рядків?

Використовуйте кілька перевірок: підрахунки по таблицях, підрахунки по партиціях (за днями/клієнтами), контрольні суми/хеши ключових полів і випадкові вибірки. Також запускайте shadow reads по реальних запитах додатка і порівнюйте результати.

9) Чи можу я залишити SQLite як локальний кеш після міграції на Postgres?

Так, але ставтеся до нього як до кешу: відновлюваного, з терміном життя і ніколи не авторитетного, якщо тільки ви явно не проектуєте режим офлайн. Інакше ви знову введете split-brain під новою назвою.

10) Який найбільший ризик міграції, який люди недооцінюють?

Не backfill. Постійна синхронізація. Дрейф — тихий вбивця: виглядає нормально, поки не встане перший аудит, розрахунок білінгу або інцидент безпеки. Будуйте виявлення дрейфу з першого дня.

Висновок: наступні кроки, які можна виконати цього тижня

Якщо ви на SQLite і відчуваєте біль, не панікуйте та не мчіться мігрувати. Спочатку стабілізуйте, потім мігруйте як оператор: вимірювано, відворотно і нудно.

Перерахунок шляхів запису: знайдіть усі кодові шляхи, що змінюють SQLite, включно з «бекграундними» роботами.
Виберіть стратегію синхронізації: dual writes з ідемпотентністю або outbox з тригерами і shipper-ом.
Побудуйте backfill + перевірки паритету, які можна перезапустити без драми.
Розгорніть PostgreSQL з запобіжниками: пулінг, моніторинг, налаштування довговічності і запас ємності.
Шадоу-читання перед cutover. Якщо ви це пропустите — граєте в рулетку з невідомими невідомими.
Переключайте читання, потім записи, і тримайте відкат можливим протягом визначеного вікна.

Перемога — не просто «ми тепер на Postgres». Перемога в тому, що ви перестали ставитись до бази даних як до файлу, на який сподіваються, і почали ставитись до неї як до системи, яку можна спостерігати, аналізувати і відновлювати під навантаженням.

Схожі статті

PostgreSQL проти SQLite на VPS: найшвидший вибір без жалю Плани виконання в MariaDB і PostgreSQL: знаходьте реальне вузьке місце, а не симптоми