SQLite: PRAGMA и WAL — полный гайд

Aladdin

PRAGMA - это SQL-расширение, специфичное для SQLite. Это команда-«рычаг управления» движком: она позволяет читать и менять внутренние параметры базы данных (режим журналирования, размер кэша, поведение fsync, целостность схемы и многое другое).

Синтаксически PRAGMA выглядит как обычный SQL, но работает иначе:

-- Чтение параметра
PRAGMA journal_mode;

-- Установка параметра (две эквивалентные формы)
PRAGMA journal_mode = WAL;
PRAGMA journal_mode(WAL);

-- С указанием схемы (для ATTACH-баз)
PRAGMA main.journal_mode = WAL;
PRAGMA temp.cache_size;

Чем PRAGMA отличается от обычного SQL

Свойство	Обычный SQL	PRAGMA
Переносимость	Стандарт SQL	Только SQLite
Обратная совместимость	Гарантирована	Не гарантирована
Ошибка при опечатке	Выдаётся	Молча игнорируется
Момент выполнения	`sqlite3_step()`	Может быть в `sqlite3_prepare()`

️ Важно: если допустить опечатку в названии PRAGMA (PRAGMA jouranl_mode = WAL), SQLite не выдаст ошибку - команда просто не выполнится. Всегда проверяйте возвращаемое значение.

PRAGMA как табличные функции

Начиная с SQLite 3.16.0, PRAGMA без побочных эффектов можно использовать в SELECT как табличные функции с префиксом pragma_:

-- Через PRAGMA
PRAGMA table_info('users');

-- Через функцию (можно в JOIN, WHERE, агрегатах)
SELECT name, type FROM pragma_table_info('users') WHERE pk = 1;

-- Пример: все проиндексированные колонки во всех таблицах
SELECT DISTINCT m.name || '.' || ii.name AS indexed_col
  FROM sqlite_schema AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type = 'table'
 ORDER BY 1;

Ключевые PRAGMA: обзор

Производительность и кэш

-- Размер кэша: положительное = страницы, отрицательное = KiB
PRAGMA cache_size = -8000;     -- ~8 МБ кэша
PRAGMA cache_size;             -- читаем текущее значение (по умолч: -2000)

-- Размер страницы (только до первой записи!)
PRAGMA page_size = 4096;

-- Временные таблицы в RAM вместо диска
PRAGMA temp_store = MEMORY;

-- Memory-mapped I/O (0 = выключено)
PRAGMA mmap_size = 268435456; -- 256 МБ

Целостность и безопасность

-- Проверка целостности базы
PRAGMA integrity_check;
PRAGMA integrity_check(10);          -- первые 10 ошибок
PRAGMA integrity_check('orders');    -- только таблица orders

-- Быстрая проверка (без некоторых тестов, зато быстрее)
PRAGMA quick_check;

-- Внешние ключи (по умолчанию ВЫКЛЮЧЕНЫ!)
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;

-- Версия приложения (для идентификации формата файла)
PRAGMA user_version = 3;
PRAGMA user_version;

Информация о схеме

-- Колонки таблицы
PRAGMA table_info('orders');

-- Индексы таблицы
PRAGMA index_list('orders');

-- Подробности по конкретному индексу
PRAGMA index_info('idx_user_id');

-- Все присоединённые базы
PRAGMA database_list;

-- Список всех PRAGMA
PRAGMA pragma_list;

Таймаут при блокировке

-- Ждать 5 секунд вместо немедленного SQLITE_BUSY
PRAGMA busy_timeout = 5000;

Режимы журналирования (journal_mode)

SQLite обеспечивает атомарность транзакций через журнал. Всего режимов шесть:

Режим	Описание	Когда использовать
`DELETE`	Журнал удаляется при коммите. По умолчанию	Простые однопользовательские сценарии
`TRUNCATE`	Журнал обрезается до 0 (не удаляется). Быстрее DELETE на некоторых ОС	Альтернатива DELETE
`PERSIST`	Заголовок журнала перезаписывается нулями	Оптимизация на платформах, где удаление файла дорого
`MEMORY`	Журнал только в RAM, нет fsync. Быстро, но нет durability	Тесты, временные операции
`WAL`	Write-Ahead Log - изменения пишутся в отдельный файл	Конкурентный доступ, большинство реальных приложений
`OFF`	Нет журнала вообще. Нет атомарности!	Только bulk-import с полным контролем

PRAGMA journal_mode;        -- узнать текущий
PRAGMA journal_mode = WAL;  -- включить WAL, возвращает 'wal' при успехе
PRAGMA journal_mode = DELETE; -- вернуться к стандартному

Как работает WAL - механика

Rollback Journal vs WAL: принципиальное отличие

Rollback Journal (DELETE-режим):

Перед изменением страницы - скопировать оригинал в журнал
Записать изменения прямо в .db файл
Коммит = удалить журнал
Откат = восстановить из журнала в .db

WAL (Write-Ahead Log):

Оригинал в .db - не трогать
Изменения дописать в конец .wal файла
Коммит = записать специальную commit-запись в .wal
Читатели работают с .db + нужный «срез» .wal
Checkpoint = перенести WAL → .db

┌─────────────────────────────────────────────────────────────┐
│  Файлы на диске при WAL-режиме                              │
│                                                             │
│  mydb.db     ← основная база (стабильные данные)           │
│  mydb.db-wal ← новые изменения (дописываются в конец)      │
│  mydb.db-shm ← shared-memory индекс (wal-index)           │
└─────────────────────────────────────────────────────────────┘

Как читатели видят данные

Когда читатель открывает транзакцию, он фиксирует «end mark» - позицию последней commit-записи в WAL на этот момент. Все его чтения используют только данные до этой метки: сначала WAL (в обратном порядке, берётся последняя версия страницы), затем .db. Это обеспечивает снапшотную изоляцию без блокировки писателя.

Checkpoint: возврат данных из WAL в .db

Checkpoint - это операция переноса страниц из WAL обратно в основную базу. Она происходит автоматически (каждые 1000 страниц по умолчанию) или вручную.

Режимы checkpoint’а:

Режим	Поведение	Блокирует?
`PASSIVE`	Делает что может, не мешая другим. По умолчанию	Нет
`FULL`	Ждёт завершения всех текущих писателей, затем checkpoint	Ждёт писателей
`RESTART`	FULL + ждёт читателей, чтобы сбросить WAL с начала	Ждёт всех
`TRUNCATE`	RESTART + физически обрезает файл `.wal` до 0 байт	Ждёт всех

PRAGMA wal_checkpoint;           -- PASSIVE (по умолчанию)
PRAGMA wal_checkpoint(FULL);     -- ждёт писателей
PRAGMA wal_checkpoint(RESTART);  -- ждёт всех
PRAGMA wal_checkpoint(TRUNCATE); -- + обрезает файл

-- Возвращает 3 числа: (busy, log_pages, checkpointed_pages)
-- busy=0 → checkpoint прошёл без блокировок
-- log_pages == checkpointed_pages → WAL полностью перенесён

WAL: включение и настройка

Минимальный старт

import sqlite3

conn = sqlite3.connect("mydb.db")

# Включаем WAL - настройка персистентна (сохраняется между перезапусками)
result = conn.execute("PRAGMA journal_mode = WAL").fetchone()
print(result)  # → 'wal' при успехе, или прежний режим при неудаче

Оптимальная конфигурация для production

import sqlite3

def get_connection(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=30)

    # WAL - основной режим
    conn.execute("PRAGMA journal_mode = WAL")

    # NORMAL: fsync только при checkpoint, безопасно для WAL
    # FULL = fsync на каждый коммит (медленнее, максимальная стойкость)
    conn.execute("PRAGMA synchronous = NORMAL")

    # Кэш ~32 МБ
    conn.execute("PRAGMA cache_size = -32000")

    # Временные таблицы в памяти
    conn.execute("PRAGMA temp_store = MEMORY")

    # 256 МБ memory-mapped reads
    conn.execute("PRAGMA mmap_size = 268435456")

    # Checkpoint раз в 500 страниц вместо 1000
    conn.execute("PRAGMA wal_autocheckpoint = 500")

    # Ждать до 5 сек при блокировке
    conn.execute("PRAGMA busy_timeout = 5000")

    # Включить внешние ключи (по умолчанию выключены!)
    conn.execute("PRAGMA foreign_keys = ON")

    return conn

Конфигурация synchronous с WAL

-- OFF: нет fsync вообще. Быстро, но риск порчи при сбое питания
PRAGMA synchronous = OFF;

-- NORMAL: fsync только при checkpoint. Рекомендуется для WAL
-- Потеря данных возможна только при сбое ОС, не при сбое приложения
PRAGMA synchronous = NORMAL;

-- FULL: fsync при каждом коммите и checkpoint
PRAGMA synchronous = FULL;

-- EXTRA: FULL + extra fsync на директорию (максимальная защита)
PRAGMA synchronous = EXTRA;

В WAL-режиме при synchronous=NORMAL данные не теряются при сбое приложения, но возможны потери при сбое ОС или питания. Это приемлемый компромисс для большинства приложений.

Конкурентный доступ: как это реально работает

import sqlite3
import threading

DB = "shared.db"

def setup():
    conn = sqlite3.connect(DB)
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA busy_timeout = 3000")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS events (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            name  TEXT NOT NULL,
            ts    TEXT DEFAULT (datetime('now'))
        )
    """)
    conn.commit()
    conn.close()

def writer(thread_id: int):
    # Каждый поток открывает своё соединение
    conn = sqlite3.connect(DB, timeout=10)
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA busy_timeout = 3000")

    for i in range(50):
        conn.execute("INSERT INTO events(name) VALUES (?)", (f"t{thread_id}-e{i}",))
        conn.commit()

    conn.close()

def reader():
    conn = sqlite3.connect(DB, timeout=10)
    conn.execute("PRAGMA journal_mode = WAL")

    # В WAL-режиме: читатель НЕ блокирует писателя и наоборот
    rows = conn.execute("SELECT COUNT(*) FROM events").fetchone()
    print(f"Читатель видит: {rows} записей")
    conn.close()

setup()

# Запускаем 4 писателя одновременно
writers = [threading.Thread(target=writer, args=(i,)) for i in range(4)]
for w in writers: w.start()

# Читатель работает параллельно с писателями
reader_thread = threading.Thread(target=reader)
reader_thread.start()

for w in writers: w.join()
reader_thread.join()

Ручной checkpoint

def manual_checkpoint(db_path: str, mode: str = "FULL"):
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode = WAL")

    result = conn.execute(f"PRAGMA wal_checkpoint({mode})").fetchone()
    busy, log_pages, checkpointed = result

    print(f"Busy: {busy}")
    print(f"Pages in WAL: {log_pages}")
    print(f"Pages checkpointed: {checkpointed}")

    if log_pages == checkpointed:
        print("✓ WAL полностью перенесён в .db")
    else:
        print(f"⚠ Не перенесено {log_pages - checkpointed} страниц (есть активные читатели)")

    conn.close()

manual_checkpoint("mydb.db", "TRUNCATE")

Преимущества и ограничения WAL

Преимущества

Быстрее для большинства сценариев - записи sequential, нет двойной записи
Конкурентность - читатели не блокируют писателя, писатель не блокирует читателей
Меньше fsync() - уязвимость к broken fsync снижена
Снапшотная изоляция - каждый читатель видит согласованный срез

Ограничения

Только на одной машине - WAL требует общей памяти (.shm), сетевые ФС не поддерживаются
Один писатель - конкурентная запись невозможна, только последовательная
Нельзя менять page_size после перехода в WAL
Читаемость снижается при большом WAL - каждый читатель сканирует WAL для поиска страниц
Дополнительные файлы - .wal и .shm должны храниться рядом с .db
ATTACH + несколько баз - атомарность только внутри одной базы, не между ними

WAL-reset bug (CVE-подобная проблема)

В SQLite 3.7.0–3.51.2 существовал редкий race condition при двух одновременных checkpoint’ах, который мог привести к порче базы. Исправлено в версии 3.51.3 (2026-03-13). Если вы используете WAL с многопроцессным доступом - обновитесь.

Когда НЕ использовать WAL

# WAL НЕ подходит если:

# 1. Сетевая файловая система (NFS, SMB, CIFS)
conn = sqlite3.connect("//nas/share/mydb.db")  # ❌ WAL сломается

# 2. База открывается в read-only режиме без .shm файла
# (решается через PRAGMA locking_mode=EXCLUSIVE или флаг immutable)

# 3. Нужно менять page_size
conn.execute("PRAGMA page_size = 8192")   # нужно делать ДО WAL
conn.execute("PRAGMA journal_mode = WAL") # после этого page_size не сменить

# 4. Транзакции > 100 МБ на старых версиях SQLite < 3.11.0
# (на 3.11.0+ это ограничение снято)

Диагностика и мониторинг WAL

import sqlite3

def wal_status(db_path: str):
    conn = sqlite3.connect(db_path)

    mode = conn.execute("PRAGMA journal_mode").fetchone()
    sync = conn.execute("PRAGMA synchronous").fetchone()
    cache = conn.execute("PRAGMA cache_size").fetchone()
    page_size = conn.execute("PRAGMA page_size").fetchone()
    page_count = conn.execute("PRAGMA page_count").fetchone()
    auto_cp = conn.execute("PRAGMA wal_autocheckpoint").fetchone()
    fk = conn.execute("PRAGMA foreign_keys").fetchone()

    # Статус WAL
    cp = conn.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()

    print(f"journal_mode:       {mode}")
    print(f"synchronous:        {sync}  (0=OFF 1=NORMAL 2=FULL 3=EXTRA)")
    print(f"cache_size:         {cache} pages")
    print(f"page_size:          {page_size} bytes")
    print(f"page_count:         {page_count}")
    print(f"db_size:            {page_size * page_count / 1024:.1f} KB")
    print(f"wal_autocheckpoint: {auto_cp} pages")
    print(f"foreign_keys:       {'ON' if fk else 'OFF'}")
    print(f"WAL checkpoint:     busy={cp}, log={cp[^1]}, checkpointed={cp[^2]}")

    conn.close()

wal_status("mydb.db")

Полный шаблон: production-ready SQLite + WAL

import sqlite3
from contextlib import contextmanager
from pathlib import Path


class Database:
    def __init__(self, path: str | Path):
        self.path = str(path)
        self._init_db()

    def _make_conn(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.path, timeout=30)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA journal_mode = WAL")
        conn.execute("PRAGMA synchronous = NORMAL")
        conn.execute("PRAGMA cache_size = -32000")   # ~32 MB
        conn.execute("PRAGMA temp_store = MEMORY")
        conn.execute("PRAGMA mmap_size = 134217728") # 128 MB
        conn.execute("PRAGMA foreign_keys = ON")
        conn.execute("PRAGMA busy_timeout = 5000")
        conn.execute("PRAGMA wal_autocheckpoint = 1000")
        return conn

    def _init_db(self):
        with self._make_conn() as conn:
            conn.execute("""
                CREATE TABLE IF NOT EXISTS items (
                    id      INTEGER PRIMARY KEY AUTOINCREMENT,
                    name    TEXT NOT NULL,
                    value   REAL,
                    created TEXT DEFAULT (datetime('now'))
                )
            """)
            conn.commit()

    @contextmanager
    def connection(self):
        conn = self._make_conn()
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()

    def checkpoint(self, mode: str = "PASSIVE") -> dict:
        with self.connection() as conn:
            busy, log, done = conn.execute(
                f"PRAGMA wal_checkpoint({mode})"
            ).fetchone()
            return {"busy": busy, "log_pages": log, "checkpointed": done}


# Использование
db = Database("app.db")

with db.connection() as conn:
    conn.execute("INSERT INTO items(name, value) VALUES (?, ?)", ("test", 3.14))

print(db.checkpoint("FULL"))

Шпаргалка: наиболее используемые PRAGMA

-- === ОБЯЗАТЕЛЬНО при старте соединения ===
PRAGMA journal_mode = WAL;     -- WAL-режим (персистентен)
PRAGMA synchronous = NORMAL;   -- баланс скорость/надёжность для WAL
PRAGMA foreign_keys = ON;      -- внешние ключи (выкл. по умолчанию!)
PRAGMA busy_timeout = 5000;    -- не падать сразу при блокировке

-- === ПРОИЗВОДИТЕЛЬНОСТЬ ===
PRAGMA cache_size = -32000;    -- 32 МБ кэша
PRAGMA temp_store = MEMORY;    -- временные таблицы в RAM
PRAGMA mmap_size = 134217728;  -- 128 МБ memory-mapped I/O

-- === НАСТРОЙКА WAL ===
PRAGMA wal_autocheckpoint = 1000;  -- авто-checkpoint каждые N страниц
PRAGMA wal_checkpoint(PASSIVE);    -- мягкий checkpoint
PRAGMA wal_checkpoint(TRUNCATE);   -- checkpoint + обрезать .wal файл

-- === ДИАГНОСТИКА ===
PRAGMA integrity_check;        -- полная проверка базы
PRAGMA journal_mode;           -- текущий режим
PRAGMA page_size;              -- размер страницы
PRAGMA page_count;             -- число страниц
PRAGMA table_info('tbl');      -- колонки таблицы
PRAGMA index_list('tbl');      -- индексы таблицы
PRAGMA database_list;          -- все attach'нутые базы

References

Pragma statements supported by SQLite
PRAGMA - Using SQLite [Book] - NamePRAGMA - Look up or modify an SQLite configurationSyntaxCommon UsagePRAGMA page_size; PRAGMA cac…
Pragma statements supported by SQLite
PRAGMA System | sqlite/sqlite | DeepWiki - The PRAGMA system provides runtime configuration and introspection capabilities for SQLite databases…
Write-Ahead Logging - SQLitesqlite.org › wal