PostgreSQL проти SQLite при одночасних записах: хто перемагає і чому

Груба відповідь: хто перемагає?

Для одночасних записів перемагає PostgreSQL. І не трохи — за своєю архітектурою.

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

PostgreSQL — це серверна база, створена для прийому багатьох клієнтських підключень і підтримки постійного потоку транзакцій
від численних одночасних сесій. Вона використовує MVCC (контроль багатьох версій) щоб відокремити читачів від писачів і має зріле керування блокуваннями,
фонові процеси, опції WAL (write-ahead log) і інструменти для того, щоб показати, що саме зависло і чому.

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

Правило в одній реченні: якщо в задачі є конкуренція за запис, не обирайте однофайлову базу і потім дивуйтеся, коли файл стане вузьким місцем.

Цікаві факти та історичний контекст

• SQLite почалася в 2000 році як вбудована база для внутрішніх інструментів. Її «безсерверний» дизайн — це фіча, а не недолік.
• PostgreSQL бере витоки з 1980‑х (проєкт POSTGRES в UC Berkeley). Це спадщина, що відображається у фокусі на конкуренції та розширюваності.
• Вся база SQLite — один файл (плюс опціональні файли shared memory / WAL). Це зручно операційно, але вороже до конкурентності.
• WAL‑режим у SQLite (введений 2010 року) значно покращив взаємодію читачів і писачів, але не перетворив його на багатописачний движок.
• WAL у PostgreSQL існує для гарантії стійкості і відновлення після краху; він також забезпечує реплікацію та відновлення до точки часу.
• SQLite всюди: телефони, браузери, пристрої, ігри. Типове навантаження — локальні короткі транзакції, мала конкуренція писачів.
• MVCC у PostgreSQL означає, що оновлення створюють нові версії рядків; старі версії зберігаються до vacuum. Це ціна за високу конкуренцію.
• Повідомлення «database is locked» у SQLite — не таємниця; це двигун каже вам: «я зараз пишу; зачекайте свою чергу».
• Діагностика блокувань у PostgreSQL — першокласна через системні каталоги; ви можете побачити блокуючі запити, очікувальників і текст запиту в продакшені.

Що саме означає «одночасні записи» в кожному движку

Конкурентність — це не паралелізм; це планування під навантаженням

Коли люди кажуть «одночасні записувачі», вони часто мають на увазі «я маю N воркерів і хочу пропускну спроможність приблизно пропорційну N».
Це очікування розумне для PostgreSQL (поки ви не наситите CPU, I/O, блокування або затримку коміта). В SQLite це неправильна модель,
якщо тільки ви не централізували записи.

Писач — це не лише INSERT або UPDATE. Це:

• отримання блокувань,
• запис сторінок / записів WAL,
• fsync для стійкості,
• оновлення індексів,
• і можливе очікування інших писачів.

Якщо система витрачає 20 мс на транзакцію, чекаючи стійкого коміту, ви не зможете домогтися 10 000 TPS просто додаванням потоків.
Ви лише створите фестиваль очікувань блокувань.

SQLite: «одна смуга, зливайтесь чемно»

SQLite використовує блокування на файлі бази (а в WAL‑режимі — координаційні файли), щоб забезпечити узгодженість. У режимі rollback journal
(старіший режим за замовчуванням) писач блокує читачів під час фаз коміту. WAL‑режим покращує це: читачі рідше блокують писачів і навпаки.
Але писачі все одно серіалізуються. Лише один писач може виконати коміт одночасно.

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

PostgreSQL: «багато смуг, світлофори і диспетчерська»

PostgreSQL спроектовано для одночасних сесій. Писачі зазвичай не блокують читачів завдяки MVCC. Писачі можуть блокувати один одного,
але зазвичай — лише на тих самих рядках (або на важких блокуваннях типу змін схеми).

PostgreSQL має:

• блокування на рівні рядка для конфліктних оновлень,
• знімки транзакцій,
• фоновий процес writer і checkpointer,
• буфери WAL і груповий commit,
• та представлення для інспекції і діагностики очікувань.

Ключове: конкуренція — це частина архітектури, а не режим «може працювати, якщо бути обережним».

SQLite під навантаженням записів: що насправді відбувається

Режими блокування і чому ваш «швидкий» бенчмарк брешe

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

У режимі rollback journal писачеві потрібно заблокувати базу на частинах транзакції запису. Блокування переходить через фази
(shared, reserved, pending, exclusive), і інші підключення можуть блокуватися або отримувати відмову в залежності від таймінгу.
У WAL‑режимі читачі можуть продовжувати роботу, поки писач додає до WAL, але коміти все одно серіалізуються.

Що ви відчуваєте операційно:

• затримки запису ростуть під навантаженням,
• зʼявляються помилки «database is locked», якщо ви не чекаєте/не повторюєте спробу,
• чекпоінти стають прихованою важелем продуктивності,
• і «просто додай ще воркерів» швидко припиняє працювати.

WAL‑режим допомагає, але це не диво для багатьох писачів

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

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

Налаштування стійкості: ви міняєте безпеку на швидкість

PRAGMA synchronous і PRAGMA journal_mode — це місця, де команди тихо домовляються з дияволом надійності.
Якщо ви ставите synchronous=OFF, ви безумовно отримаєте більшу пропускну здатність. Ви також безумовно можете втратити підтверджені транзакції
під час відключення живлення. Вирішіть свідомо: зафіксуйте вимоги до стійкості, а потім налаштовуйте.

Жарт №1: SQLite — «безсерверне» так само, як швидка локшина — «готування». Працює, але не прикидайтеся, що це одна й та сама кухня.

PostgreSQL під навантаженням записів: що насправді відбувається

MVCC: чому читачі не кричать, коли зʼявляються писачі

MVCC у PostgreSQL означає, що читачі бачать знімок бази на початку своєї транзакції (або на початку запиту, залежно від ізоляції).
Писачі створюють нові версії рядків. Читачі продовжують читати старі версії, поки не завершать роботу. Оце й є основна перевага конкурентності.

Обмін — це прибирання. Старі версії рядків (dead tuples) потрібно прибирати VACUUM. Якщо ви ігноруєте vacuum, потім заплатите за це у вигляді розростання,
промивання кешу і неефективності індексів. PostgreSQL не врятує вас від нехлюйства; він просто дочекається, поки ви будете найзавантаженіші.

WAL, коміти і чому fsync — ваш реальний верх пропускної здатності

Кожний стійкий коміт означає, що WAL має потрапити на стабільне сховище. PostgreSQL може робити груповий commit: кілька транзакцій можуть поділити один fsync.
Ось як воно підтримує високі швидкості комітів на адекватному сховищі. Але фундаментальні фізичні обмеження все одно діють: стійкі коміти залежать від затримки запису.

Якщо ваш I/O підсистема має 3–10 мс затримки для скидання під навантаженням, ваш максимум стійких комітів на секунду не безмежний, незалежно від CPU.
Тут зʼявляється реальність SRE: налаштування продуктивності швидко перетворюється на інженерію зберігання.

Блокування: справжній провал — не «є блокування», а «вони вас дивують»

PostgreSQL має стійку семантику блокувань. Це не проблема. Проблема — команди, які не розуміють, звідки виникають блокування:

• довгі транзакції, що утримують блокування рядків,
• міграції схеми, що беруть ACCESS EXCLUSIVE,
• autovacuum або vacuum full,
• перевірки зовнішніх ключів під високими темпами записів,
• гарячі точки на тих самих сторінках індексу (особливо з монотонно зростаючими ключами).

Ви можете бачити блокуючі та очікувальні запити у PostgreSQL. Користуйтеся цим. Не гадйте.

Парафраз: Werner Vogels часто наголошує, що «усе ламається весь час» — проєктуйте системи так, щоб відмови були нормою, а не винятком.

Жарт №2: Якщо ви поставите SQLite за веб‑сервером і назвете це «розподіленим», вітаю — ви винайшли дуже маленький затор руху.

Затримки, fsync і шар зберігання (де живуть більшість «проблем з БД»)

Конкурентні записи — це не тільки історія про движок бази. Це історія про сховище. Стійкі транзакції означають, що ви виконуєте flush.
Flush означає, що ядро та пристрій погодилися, що дані надійно на диску. Ця операція має затримку і варіативність.
Варіативність вбиває: p99 затримки коміту визначає накопичення черги, наростання очікувань блокувань і хвостову затримку в сервісах.

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

Практичний висновок: якщо PostgreSQL повільний під навантаженням записів, ви часто можете виправити це кращими IOPS/затримками, налаштуванням WAL,
налаштуванням чекпоінтів, змінами схеми/індексів, пакетуванням або партиціюванням. Якщо SQLite повільніє при багатьох писачах, виправлення зазвичай архітектурне:
зменшіть конкуренцію на файл бази або перейдіть на серверну базу.

Швидкий план діагностики

Коли в продакшені «записи повільні», не починайте з війни думок. Почніть з доказів. Ось порядок дій, що мінімізує марну витрату часу.

По-перше: підтвердьте клас вузького місця (блокування, CPU або I/O)

• SQLite: чи бачите ви database is locked або довгі очікування записів? Це контенція, а не «таємнича повільність».
• PostgreSQL: перевірте wait events. Якщо сесії чекають на блокування — це контенція. Якщо чекають на WAL або I/O — це сховище/коміт. Якщо CPU завантажений — це обчислення/запити/індекси.

По-друге: ізолюйте затримку коміту від роботи запиту

• Якщо транзакції невеликі, але коміти повільні — дивіться fsync, WAL flush і затримки сховища.
• Якщо коміти швидкі, але оператори повільні — дивіться індекси, гарячі рядки та плани запитів.

По-третє: перевірте, чи нема «однієї великої транзакції», що утримує усіх у заручниках

• Довгі транзакції блокують vacuum у PostgreSQL, що призводить до блоату і може каскадно збільшувати write amplification.
• У SQLite транзакція, що залишається відкритою під час пакета операцій, може утримувати писачів довше, ніж ви очікували.

По-четверте: перевірте, чи ваша конкуренція контролюється навмисно

• SQLite: чи встановлено busy timeout і логіку retry/backoff? Чи увімкнений WAL? Чи сенсорно ви ставите чекпоінти?
• PostgreSQL: чи є пул підключень? Чи не насичуєте ви сервер занадто великою кількістю одночасних писачів?

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

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

Завдання 1: Знайти режим журналу SQLite і рівень synchronous

cr0x@server:~$ sqlite3 /var/lib/app/app.db 'PRAGMA journal_mode; PRAGMA synchronous;'
wal
2

Значення: режим журналу — WAL; synchronous=2 (FULL). Ви платите за стійкість на кожній транзакції.

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

Завдання 2: Перевірити логи на помилки блокування SQLite

cr0x@server:~$ journalctl -u app.service -n 200 | grep -E 'database is locked|SQLITE_BUSY' | tail
Dec 30 09:22:11 server app[1842]: ERROR db write failed: database is locked
Dec 30 09:22:12 server app[1842]: ERROR db write failed: SQLITE_BUSY in insert_event

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

Рішення: Додайте busy_timeout, реалізуйте retry з джитером і зменшіть кількість одночасних писачів (патерн single‑writer/черга).
Якщо писачі незалежні і високошвидкісні, плануйте міграцію на PostgreSQL.

Завдання 3: Підтвердити busy timeout у SQLite під час роботи

cr0x@server:~$ sqlite3 /var/lib/app/app.db 'PRAGMA busy_timeout;'
0

Значення: таймаут відсутній. SQLite відмовляє негайно, коли не може отримати блокування.

Рішення: Встановіть розумний таймаут при налаштуванні підключення в додатку (наприклад, 2000–10000 мс залежно від SLO) і додайте ретраї.

Завдання 4: Спостерігати за ростом WAL (тиск на чекпоінти)

cr0x@server:~$ ls -lh /var/lib/app/app.db*
-rw-r----- 1 app app 1.2G Dec 30 09:23 /var/lib/app/app.db
-rw-r----- 1 app app 3.8G Dec 30 09:23 /var/lib/app/app.db-wal
-rw-r----- 1 app app  32K Dec 30 09:23 /var/lib/app/app.db-shm

Значення: WAL суттєво більший за базу. Чекпоінти не встигають або блокуються довгими читачами.

Рішення: Знайдіть довгі читальні транзакції; відрегулюйте стратегію чекпоінтів (використовуйте wal_checkpoint якщо доречно),
або перенесіть інтенсивні записи з SQLite.

Завдання 5: Перевірити довгі читачі SQLite (часта причина блокування чекпоінта)

cr0x@server:~$ lsof /var/lib/app/app.db | head
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
app     1842  app   12u   REG  253,0 1288490189  49155 /var/lib/app/app.db
worker  1910  app   10u   REG  253,0 1288490189  49155 /var/lib/app/app.db
report  2201  app    8u   REG  253,0 1288490189  49155 /var/lib/app/app.db

Значення: декілька процесів відкрили БД. Це нормально, але довгі звіти можуть тримати знімки відкритими.

Рішення: Забезпечте, щоб звіти працювали з репліками (у PostgreSQL) або з експортованими даними. У SQLite скоротіть час читальних транзакцій і уникайте «відкритої транзакції під час стрімінгу результатів».

Завдання 6: PostgreSQL: хто чекає і на що

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, wait_event_type, wait_event, state, now()-query_start AS age, left(query,80) AS query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 8;"
 pid  | usename | wait_event_type | wait_event | state  |   age    | query
------+--------+-----------------+------------+--------+----------+-----------------------------------------------
 6241 | app    | Lock            | tuple      | active | 00:01:12 | UPDATE orders SET status='paid' WHERE id=$1
 6310 | app    | IO              | DataFileRead| active | 00:00:49 | INSERT INTO events(ts, type, payload) VALUES...
 6188 | app    | WAL             | WALWrite   | active | 00:00:20 | INSERT INTO events(ts, type, payload) VALUES...

Значення: у вас принаймні три типи вузьких місць: очікування рядкових блокувань (tuple), читання файлів даних і запис WAL.

Рішення: Для Lock/tuple знайдіть блокувальник і зменшіть конфлікти на «гарячих» рядках. Для WAL — дослідіть затримки пристрою для комітів/WAL і налаштування чекпоінтів.
Для DataFileRead перевірте кэш‑хітрейт і відсутні індекси.

Завдання 7: PostgreSQL: знайти блокуючий запит

cr0x@server:~$ psql -d appdb -c "SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, blocked.query AS blocked_query, blocker.query AS blocker_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid=blocked.pid AND NOT bl.granted JOIN pg_locks kl ON kl.locktype=bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page IS NOT DISTINCT FROM bl.page AND kl.tuple IS NOT DISTINCT FROM bl.tuple AND kl.virtualxid IS NOT DISTINCT FROM bl.virtualxid AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid AND kl.classid IS NOT DISTINCT FROM bl.classid AND kl.objid IS NOT DISTINCT FROM bl.objid AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid AND kl.pid != bl.pid JOIN pg_stat_activity blocker ON blocker.pid=kl.pid;"
 blocked_pid | blocker_pid |           blocked_query            |             blocker_query
------------+------------+------------------------------------+------------------------------------------
       6241 |       6177 | UPDATE orders SET status='paid'... | UPDATE orders SET status='paid'...

Значення: писачі конфліктують на тих самих рядках (або на сусідніх «гарячих» рядках).

Рішення: Виправте логіку додатку: уникайте кількох воркерів, що бʼються за ті самі рядки; використовуйте патерни черги з SELECT ... FOR UPDATE SKIP LOCKED; партиціонуйте «гарячі» таблиці.

Завдання 8: PostgreSQL: перевірити тиск на коміти та WAL через статистику

cr0x@server:~$ psql -d appdb -c "SELECT datname, xact_commit, xact_rollback, blks_read, blks_hit, temp_files, temp_bytes FROM pg_stat_database WHERE datname='appdb';"
 datname | xact_commit | xact_rollback | blks_read | blks_hit | temp_files | temp_bytes
--------+-------------+---------------+----------+---------+-----------+-----------
 appdb  |     8921341 |         31221 |  1842290 | 44211987|      1842 | 987654321

Значення: велика кількість комітів і помітне використання тимчасових файлів. Тимчасові файли часто означають сорти/хеші, що виливаються на диск.

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

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

cr0x@server:~$ psql -d appdb -c "SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_backend FROM pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_backend
------------------+-----------------+-----------------------+----------------------+-------------------+----------------
              122 |             987 |                932112 |               121009 |           8123344 |         223445

Значення: багато запитів на чекпоінти (через обсяг WAL) і великий час запису чекпоінта. Є також бекенд‑записи.

Рішення: Зменшіть піки чекпоінтів: збільшіть max_wal_size, налаштуйте checkpoint_timeout, підвищте checkpoint_completion_target.
Потім перевірте, чи сховище здатне витримувати такий рівень записів.

Завдання 10: PostgreSQL: швидко виміряти відсоток попадань у кеш

cr0x@server:~$ psql -d appdb -c "SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit)+sum(blks_read),0),2) AS cache_hit_pct FROM pg_stat_database;"
 cache_hit_pct
--------------
         96.01

Значення: приличний відсоток попадань, але 96% під сильним навантаженням все одно може означати багато читань.

Рішення: Якщо пропускна здатність записів стримується читаннями (відсутні індекси, випадковий I/O), спочатку працюйте з планами запитів і індексами перед покупкою заліза.

Завдання 11: Системний рівень: підтвердити затримку сховища під навантаженням

cr0x@server:~$ iostat -xz 1 3
Linux 6.8.0 (server)  12/30/2025  _x86_64_  (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    6.11   14.90    0.00   60.77

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz  aqu-sz  %util
nvme0n1         120.0   12288.0     0.0   0.00    1.80   102.40   950.0   97280.0     0.0   0.00   12.40   102.40   4.10  96.00

Значення: середня затримка запису ~12 мс з 96% використанням і помітним iowait. Ваша «проблема з базою» тепер проблема сховища.

Рішення: Зменште частоту синхронних комітів (пакетування/групування), перемістіть WAL на швидше сховище або оновіть пристрій/RAID/налаштування кешу.
Також перевірте, чи вас не обмежують ліміти хмарного тому.

Завдання 12: PostgreSQL: перевірити швидкість генерації WAL

cr0x@server:~$ psql -d appdb -c "SELECT now(), pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'0/0')) AS wal_since_boot;"
              now              | wal_since_boot
------------------------------+----------------
 2025-12-30 09:25:33.11452+00 | 384 GB

Значення: ви генеруєте багато WAL. Це нормально при інтенсивних записах, але це впливає на чекпоінти і пропускну здатність реплікації.

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

Завдання 13: PostgreSQL: швидко знайти топ‑запити на записи

cr0x@server:~$ psql -d appdb -c "SELECT calls, rows, mean_exec_time, left(query,90) AS query FROM pg_stat_statements WHERE query ILIKE 'insert%' OR query ILIKE 'update%' ORDER BY mean_exec_time DESC LIMIT 5;"
 calls |  rows  | mean_exec_time | query
-------+--------+----------------+------------------------------------------------------------------------------------------
  8821 |  8821  |          14.22 | INSERT INTO events(ts, type, payload) VALUES ($1,$2,$3)
  1102 |  1102  |          11.90 | UPDATE orders SET status=$1, updated_at=now() WHERE id=$2

Значення: вставки матеріально дорогі. Причиною можуть бути WAL/fsync, індекси або контенція.

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

Завдання 14: SQLite: протестувати контенцію, симулювавши двох писачів (швидкий репро)

cr0x@server:~$ bash -lc 'sqlite3 /tmp/t.db "PRAGMA journal_mode=WAL; CREATE TABLE IF NOT EXISTS t(id INTEGER PRIMARY KEY, v TEXT);"; (sqlite3 /tmp/t.db "BEGIN; INSERT INTO t(v) VALUES(\"a\"); SELECT \"writer1 holding\"; SELECT sleep(2); COMMIT;" &) ; (sqlite3 /tmp/t.db "PRAGMA busy_timeout=500; INSERT INTO t(v) VALUES(\"b\");" ; wait)'
wal
writer1 holding
Error: database is locked

Значення: навіть у WAL‑режимі, якщо перший писач утримує транзакцію, другий писач зазнає відмови, якщо не може достатньо довго чекати.

Рішення: Тримайте транзакції короткими; встановіть busy timeout; не тримайте транзакцію відкритою під час виконання роботи додатку.
Якщо вам потрібні справжні паралельні записи — припиніть боротися з фізикою: переходьте на PostgreSQL.

Три короткі історії з корпоративного світу (анонімізовано, правдоподібно, болісно)

Інцидент: неправильне припущення («SQLite підтримує одночасні записи, так?»)

Команда середнього розміру випустила фічу «легка аналітика» всередині сервісу, що вже обслуговував користувацькі запити.
Вони обрали SQLite, бо це було просто: файл, скрипт міграції і жодної інфраструктури. Вони навіть увімкнули WAL‑режим.
У стейджингу все летіло. У продакшені сервіс працював з 12 процесами воркерів і бурстовою чергою.

Перший симптом був делікатним: періодичні 500 під час піків трафіку, всі з помилками вставки. Текст помилки був прозорий:
database is locked. Але команда сприймала це як тимчасовий глітч, а не як архітектурне обмеження.
Вони додали ретраї. Стало «краще», потім гірше.

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

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

Оптимізація, що повернулась бумерангом: «Збільшимо конкуренцію, щоб пришвидшити інжест»

Інша компанія мала pipeline інжесту на PostgreSQL, що записував події в одну велику таблицю.
Щоб досягти нової мети пропускної здатності, вони збільшили кількість воркерів з «кількох» до «багатьох».
CPU був у нормі. Мережа була в нормі. Латентність все одно вибухнула.

Вони припустили, що база масштабуватиметься лінійно з кількістю воркерів. Натомість система вперлася в контенцію і обмеження I/O.
Autovacuum почав працювати сильніше. Чекпоінти стали частішими через обсяг WAL.
p99 часу коміту піднявся, що призвело до затору в черзі, і парадоксально підвищило паралелізм, бо «воркери були вільні, чекаючи».

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

Виправлення не було «ще більше тюнінгу». Було: зменшити число воркерів до дозволеної сховищем пропускної здатності, видалити або відкласти дорогий індекс,
пакетувати вставки через COPY у пікові години. Після цього пропускна здатність зросла, латентність стабілізувалась.
Вони згадали старий урок SRE: конкуренція — інструмент, а не чеснота.

Нудна, але правильна практика, що врятувала ситуацію: «Ми розрахували WAL і репетиції відновлення»

Система поруч із платіжною (не головна книга, але близько) працювала на PostgreSQL із стабільним потоком записів.
Інженер‑менеджер наполягав на трьох речах, які ніхто не вважав захопливими:
плановий перегляд vacuum, налаштування WAL і чекпоінтів задокументовані з мотиваціями, та регулярні відпрацювання відновлення у другому середовищі.

Одного дня міграція схеми з індексом збільшила write amplification і обсяг WAL більше, ніж очікували. Реплікація відстала. Запрацювали алерти.
На виклику було видно статистику: більше запитів на чекпоінти, більше WAL, зростання затримок коміту.

Оскільки команда вже консервативно підібрала max_wal_size і цілі чекпоінтів, система не пішла у повний треш одразу.
Через репетиції відновлення відкат і повторний безпечний деплой не викликали паніки.
І через моніторинг vacuum розростання не погіршило ситуацію ще більше.

Інцидент залишився дратівливим, але в межах «дратівливого». Без втрати даних. Без багатоденної епопеї з відновлення.
Нудні практики не потрапили в заголовки новин. Вони їх і запобігли.

Типові помилки: симптом → корінь проблеми → виправлення

1) Симптом: SQLite «database is locked» під час пікового трафіку

Корінь проблеми: занадто багато одночасних писачів, відсутній busy timeout, довгі транзакції або записи, виконувані під час відкритої транзакції.

Виправлення: увімкнути WAL‑режим; встановити busy_timeout; тримати транзакції короткими; реалізувати retry з джитером; централізувати записи через одного писача; мігрувати write‑heavy шляхи на PostgreSQL.

2) Симптом: WAL‑файл SQLite росте без контролю

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

Виправлення: скоротіть час читальних транзакцій; уникайте стрімінгових читань під час відкритої транзакції; запускайте періодичні чекпоінти; розгляньте відокремлення звітності від OLTP‑записів.

3) Симптом: вставки в PostgreSQL уповільнюються при збільшенні конкуренції

Корінь проблеми: затримка коміту (WAL flush), насичення сховища або занадто багато дрібних транзакцій.

Виправлення: пакетувати вставки; використовувати COPY; забезпечити, щоб WAL був на низькозатримковому сховищі; налаштувати чекпоінти (max_wal_size, checkpoint_completion_target); розглянути async commit лише якщо прийнятно.

4) Симптом: писачі PostgreSQL застрягають в очікуванні блокувань

Корінь проблеми: гарячі рядки, патерни черги без SKIP LOCKED, довгі транзакції або DDL під час пікового навантаження.

Виправлення: переробити гарячі точки; використовувати SELECT ... FOR UPDATE SKIP LOCKED; тримати транзакції короткими; виконувати DDL у дружньому до блокувань режимі; планувати важкі міграції поза піковими годинами.

5) Симптом: блоат PostgreSQL і зростання write amplification з часом

Корінь проблеми: autovacuum відстає, довгі транзакції не дозволяють прибирати старі версії, або часті оновлення одних і тих самих рядків.

Виправлення: моніторити vacuum; виправити довгі транзакції; налаштувати пороги autovacuum по таблицях; зменшити churn; партиціонувати дані з високим churn.

6) Симптом: «Ми додали індекси і стало повільніше»

Корінь проблеми: кожна вставка/оновлення повинна підтримувати всі індекси; шлях запису став важчим, ніж вигода для читання.

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

Чеклісти / покроковий план

Чекліст для рішення: чи підходить ця робоче навантаження для SQLite чи PostgreSQL?

1. Порахуйтесь писачів. Якщо ви очікуєте кількох незалежних процесів/потоків для частих записів — за замовчуванням обирайте PostgreSQL.
2. Визначте стійкість. Якщо ви не можете терпіти втрату «успішних» записів при аварії, не використовуйте небезпечні PRAGMA в SQLite. Налаштування PostgreSQL за замовчуванням безпечніші.
3. Форма транзакцій має значення. Якщо ви можете пакетувати і терпіти патерн одного писача, SQLite може працювати.
4. Операційні вимоги. Якщо вам потрібні реплікація, PITR, онлайн‑зміни схеми та інспекція — PostgreSQL.
5. Деплой середовище. Якщо ви не можете запустити DB‑сервер (крайові пристрої, офлайн‑додатки), SQLite — дарунок.

Покроково: як змусити SQLite поводитись при помірній конкуренції записів

1. Увімкніть WAL‑режим.
2. Встановіть busy timeout і реалізуйте retry з джитером.
3. Тримайте транзакції короткими; ніколи не утримуйте їх під час мережевих викликів.
4. Пакетуйте записи: менше комітів, більше роботи в одній транзакції.
5. Керуйте чекпоінтами, якщо WAL росте (і перевірте, що довгі читачі не блокують їх).
6. Якщо черга записів продовжує рости — припиніть оптимізувати і плануйте міграцію.

Покроково: стабілізація пропускної здатності записів у PostgreSQL

1. Виміряйте wait events і контенцію блокувань у pg_stat_activity.
2. Перевірте чекпоінти і тиск WAL у pg_stat_bgwriter.
3. Підтвердіть затримку сховища за допомогою iostat; підтвердьте продуктивність для пристрою WAL.
4. Зменште кількість транзакцій, пакетизуючи; використовуйте COPY для інжесту.
5. Видаліть дорогі не потрібні індекси на таблицях інжесту.
6. Виправте гарячі рядки (шаблони черг, лічильники, оновлення статусу).
7. Переконайтеся, що vacuum встигає; виправте довгі транзакції.
8. Додавайте потужність тільки після того, як ви зʼясували, що саме насичене.

Питання та відповіді

1) Чи може SQLite взагалі обробляти кількох писачів?

Кілька підключень можуть намагатися записувати, але SQLite серіалізує фактичну роботу запису/коміту через блокування. Під контенцією ви побачите SQLITE_BUSY.
Це працює, якщо ви тримаєте транзакції короткими і погоджуєтеся, що записи стоятимуть у черзі.

2) Чи робить WAL‑режим SQLite «конкурентним», як PostgreSQL?

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

3) Чому PostgreSQL краще витримує багато писачів?

MVCC зменшує блокування між читачами і писачами, блокування на рівні рядка локалізує конфлікти, і система побудована навколо одночасних сесій з фоновими процесами.
Також підтримується груповий commit, який амортизує вартість fsync на багато транзакцій.

4) Який реальний ліміт пропускної здатності записів у PostgreSQL?

Зазвичай: затримка стійкого коміту (WAL flush) і пропускна здатність сховища. Далі — контенція на гарячих рядках/індексах, поведінка чекпоінтів і CPU на обробку індексів.
Ліміт зазвичай не «PostgreSQL не може», а «ваше сховище і схема не погоджуються з навантаженням».

5) Чи варто відключити fsync/synchronous для швидкості?

Тільки якщо ви готові втратити дані при аварії/відключенні живлення. В SQLite PRAGMA synchronous=OFF — реальний важіль для втрати даних.
У PostgreSQL зміна налаштувань стійкості має аналогічні компроміси. Спочатку зафіксуйте припустимі межі втрати.

6) Чи обовʼязковий пул підключень для писачів PostgreSQL?

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

7) Коли SQLite — правильний вибір навіть для записів?

Локально‑орієнтовані застосунки, однокористувацькі системи, крайові пристрої, CI/test інструменти, кеші або коли ви можете забезпечити одного писача і пакетні оновлення.
Якщо ви не можете забезпечити такі обмеження, SQLite стане крихкою залежністю.

8) Який найпоширеніший шлях міграції з SQLite на PostgreSQL?

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

9) Чому я бачу очікування блокувань у PostgreSQL, коли я «лише вставляю»?

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

10) Який найпростіший безпечний патерн для черги задач?

У PostgreSQL: таблиця черги з SELECT ... FOR UPDATE SKIP LOCKED і короткі транзакції. У SQLite: один процес‑писач, який декує і записує,
а читачі споживають знімки за потреби.

Наступні кроки, які ви справді можете виконати

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

• Якщо ви на SQLite і бачите помилки блокування: увімкніть WAL, встановіть busy timeout, скоротіть транзакції і централізуйте записи. Якщо навантаження все ще вимагає багатьох писачів — заплануйте міграцію на PostgreSQL.
• Якщо ви на PostgreSQL і записи повільні: перевірте wait events і блокувальників, потім чекпоінти/WAL, потім затримки сховища. Виправте гарячі рядки і форму транзакцій перед купівлею заліза.
• У обох випадках: вимірюйте p95/p99 затримки коміту і частоту транзакцій. Це — істина для «одночасних записів».

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