SQLite и FTS5 на миллионах строк: где предел

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

Поэтому эта статья устроена иначе, чем обычный обзор с графиками. Чужие числа, снятые на чужом железе, вам ничего не скажут: разброс между медленным сетевым диском виртуальной машины и локальным накопителем — порядки, а не проценты. Мы не публикуем такие числа как свои. Вместо этого — стенд, который вы запустите у себя за десять минут, таблица, которую заполните своими значениями, и разбор того, что на эти значения влияет.

#Короткий ответ

Для личной нагрузки — один пишущий процесс, единицы одновременных читателей, десятки миллионов строк — SQLite работает с большим запасом, если соблюдены три условия: правильный режим журнала, индексы под форму запроса и полнотекстовый поиск через специализированный модуль, а не через LIKE.

Единственный признак, при котором пора уходить: вам нужны несколько одновременных писателей с разных машин. Всё остальное — вопрос настройки, а не выбора базы. Для личного сервиса, поднятого по маршруту из статьи о том, как поднять свой ИИ-сервис, этот признак не наступает никогда: там один процесс приложения на одной машине.

#Как это устроено

#Что растёт линейно, а что нет

Три строки здесь и есть весь сюжет. Первая: правильный индекс делает объём почти неважным. Вторая: поиск подстрокой с ведущим символом подстановки индекс не использует никогда — и именно он обычно и оказывается тем «SQLite тормозит», о котором пишут. Пятая: COUNT(*) для показа общего числа записей на каждой странице списка — самый частый способ сделать интерфейс медленным на большом объёме.

#Как устроен полнотекстовый индекс

Модуль полнотекстового поиска хранит собственный обратный индекс. У нас он объявлен во внешнем режиме — данные лежат в основной таблице, индекс ссылается на неё по идентификатору строки, синхронизация идёт триггерами:

CREATE VIRTUAL TABLE screenshots_fts USING fts5(
    ocr_text,
    window_title,
    app_name,
    content='screenshots',            -- данные не дублируются
    content_rowid='id',
    tokenize='unicode61 remove_diacritics 2'
);

Внешний режим экономит место (текст не хранится дважды) ценой того, что индекс нужно поддерживать триггерами и он может разъехаться с таблицей при прямой правке данных в обход приложения.

remove_diacritics 2 для русского текста существенно: без нормализации «ё» и «е» будут разными словами, и половина совпадений потеряется. Устройство полнотекстового индекса подробнее — в статье про полнотекстовый поиск FTS5 в SQLite.

#Стенд: получите свои числа

Скрипт наполняет базу, строит индекс и меряет четыре типовых запроса на трёх объёмах.

import sqlite3, time, random, string

def make(db, n):
    c = sqlite3.connect(db)
    c.executescript("""
        PRAGMA journal_mode=WAL;
        PRAGMA synchronous=NORMAL;
        CREATE TABLE IF NOT EXISTS doc(
            id INTEGER PRIMARY KEY, app TEXT, created_at TEXT, body TEXT);
        CREATE INDEX IF NOT EXISTS idx_doc_app_time ON doc(app, created_at);
        CREATE VIRTUAL TABLE IF NOT EXISTS doc_fts USING fts5(
            body, content='doc', content_rowid='id',
            tokenize='unicode61 remove_diacritics 2');
    """)
    words = [''.join(random.choices(string.ascii_lowercase, k=6)) for _ in range(3000)]
    rows = [(i, random.choice("abcde"), f"2026-0{random.randint(1,9)}-01",
             ' '.join(random.choices(words, k=40))) for i in range(n)]
    c.executemany("INSERT INTO doc VALUES (?,?,?,?)", rows)
    c.execute("INSERT INTO doc_fts(doc_fts) VALUES('rebuild')")
    c.commit(); return c

def bench(c, label):
    q = {
        "по индексу":       ("SELECT * FROM doc WHERE app='c' ORDER BY created_at DESC LIMIT 50", ()),
        "полнотекстовый":   ("SELECT d.id FROM doc_fts f JOIN doc d ON d.id=f.rowid "
                             "WHERE doc_fts MATCH ? ORDER BY bm25(doc_fts) LIMIT 50", ("abcdef",)),
        "LIKE подстрока":   ("SELECT id FROM doc WHERE body LIKE '%abcdef%' LIMIT 50", ()),
        "COUNT(*)":         ("SELECT COUNT(*) FROM doc", ()),
    }
    for name, (sql, args) in q.items():
        t = time.perf_counter(); c.execute(sql, args).fetchall()
        print(f"{label:>10} | {name:<16} | {(time.perf_counter()-t)*1000:8.1f} ms")

for n in (100_000, 1_000_000, 5_000_000):
    bench(make(f"bench_{n}.db", n), f"{n:,}")

Заполните своими значениями:

Что вы почти наверняка увидите — не конкретные миллисекунды, а форму зависимости: первые две строки почти не изменятся между колонками, две последние вырастут пропорционально объёму. Именно эта разница, а не абсолютные числа, определяет, доживёт ли ваше приложение до пяти миллионов строк.

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

#Что из этого следует на практике

#Настройки, дающие основной прирост

PRAGMA journal_mode = WAL;         -- читатели не блокируют писателя
PRAGMA synchronous  = NORMAL;      -- заметно быстрее FULL, база не портится
PRAGMA busy_timeout = 5000;        -- писатели встают в очередь, а не падают
PRAGMA mmap_size    = 268435456;   -- отображение файла в память, 256 МБ
PRAGMA cache_size   = -65536;      -- кэш страниц 64 МБ (минус = килобайты)
PRAGMA temp_store   = MEMORY;      -- временные таблицы и сортировки в памяти

Первые три относятся к конкурентному доступу и разобраны в статье про WAL и блокировки. Последние три — про скорость чтения, и они помогают именно там, где больно: сортировкам полнотекстового поиска и большим сканированиям.

Значения — отправная точка, а не рекомендация: mmap_size и cache_size осмысленно ставить исходя из доступной памяти машины. Учтите, что кэш страниц берётся на каждое соединение, и сорок фоновых воркеров с кэшем по 64 МБ — это уже разговор о памяти.

Все шесть — прагмы уровня соединения (кроме режима журнала, который свойство файла). Их надо выставлять на каждом соединении, иначе часть кода будет работать с одними настройками, часть с другими.

#Что делать с интерфейсом

• Уберите COUNT(*) со страниц списков. Показывайте «есть ещё» вместо точного числа либо считайте приблизительно и кэшируйте.
• Не сортируйте без индекса. Индекс должен покрывать и условие, и порядок — в том же порядке колонок.
• Пагинация по значению, а не по смещению. OFFSET 100000 заставляет базу пройти сто тысяч строк, чтобы их выбросить.
• Никаких обращений к базе из шаблона. Функция, вызываемая при отрисовке каждой строки, превращает список из пятидесяти элементов в пятьдесят запросов — почему это незаметно и как ловится, в гайде про холодный старт приложения.

#Где интуиция подводит

«Много строк — значит медленно». Медленно делает форма запроса. Миллион строк с правильным индексом быстрее, чем сто тысяч с полным сканированием.

«Полнотекстовый поиск замедляется с ростом базы». Он замедляется с ростом числа совпадений. Редкое слово ищется одинаково быстро на любом объёме; поиск слова, встречающегося в каждой второй строке, медленный всегда.

«Место в базе — это данные». Индексы, и особенно полнотекстовый, занимают заметную долю. Обычная неожиданность при планировании диска: с индексами база вдвое больше, чем ожидалось. Как считать объём — в статье о том, сколько места займёт год непрерывной записи.

«Пора переезжать на серверную СУБД». Проверьте настоящий критерий: нужны ли вам одновременные писатели с нескольких машин. Если нет, переезд принесёт вам сетевую задержку на каждом запросе, отдельный процесс для сопровождения и ноль выигрыша.

«Векторный поиск заменит полнотекстовый». Это разные инструменты: полнотекстовый ищет по словам, векторный — по смыслу, и на точных названиях и идентификаторах второй проигрывает первому. Рабочая схема — оба вместе с объединением результатов; разбор — в статье про векторные базы данных.

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

#Какой у SQLite настоящий предел по размеру?

Формальные ограничения на размер файла и число строк документированы и очень велики — конкретные величины смотрите в документации SQLite, они зависят от параметров сборки и меняются между версиями. На практике вы упрётесь не в них, а в диск, в форму запросов и в скорость единственного писателя.

#Что быстрее для поиска: полнотекстовый модуль или LIKE?

Модуль, и разница растёт вместе с объёмом. LIKE с ведущим символом подстановки не использует индекс никогда, то есть читает всю таблицу. Держите LIKE как запасной путь для случая, когда полнотекстовый индекс недоступен, но не как основной способ поиска.

#Нужно ли перестраивать полнотекстовый индекс?

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

#Сколько одновременных читателей выдержит SQLite?

В режиме WAL читатели не мешают ни друг другу, ни писателю, и для личного сервиса их число практически не ограничено. Ограничение — один писатель в каждый момент, и оно не смягчается ничем.

#Помогает ли положить базу на быстрый накопитель?

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

#Как понять, что конкретный запрос медленный из-за отсутствия индекса?

Посмотрите план выполнения через EXPLAIN QUERY PLAN. Слова SCAN по большой таблице там — прямое указание, что индекс не используется. Это первое, что стоит смотреть, и делается за секунды.