Ограничения в SQLite: лимиты и параметры компиляции

SQLite разрабатывался с целью минимизации произвольных ограничений, поскольку многие программы функционируют на устройствах с ограниченной памятью и дисковым пространством. Тем не менее, в SQLite эти лимиты не были четко определены: придерживались принципа, что объекты, допускающие хранение в памяти и подсчет с использованием 32-битного целого числа, должны работать.

Отсутствие четко оговоренных ограничений впоследствии вызвало определенные проблемы. Без ясных пределов их не тестировали, и ошибки вылезали на крайних значениях при использовании SQLite. С версии 3.5.8 от 16 апреля 2008 года в SQLite были установлены четкие ограничения, ставшие частью тестового набора.

По умолчанию ограничения достаточно высоки и подходят большинству приложений. Однако иногда требуется увеличить определенные лимиты, хотя это скорее исключение. Обычно разработчики пересобирают SQLite с более низкими лимитами, чтобы избежать избыточного использования ресурсов в случае ошибок в SQL-генераторах или предотвратить возможные угрозы.

Некоторые лимиты можно настраивать для каждого соединения индивидуально через интерфейс sqlite3_limit(). Это особенно актуально для приложений, использующих несколько баз данных: одна может быть внутренней, а другие — подвергаться внешнему воздействию. Например, веб-браузер может использовать одну базу для хранения истории, а другие — для JavaScript-приложений.

Максимальная длина строки или BLOB (SQLITE_MAX_LENGTH)

Максимальное количество байт в строке или BLOB (бинарном большом объекте, Binary Large Object) в SQLite определяется препроцессорным макросом SQLITE_MAX_LENGTH. Значение этого макроса по умолчанию составляет 1 миллиард (1 000 000 000). Увеличить или уменьшить это значение можно во время компиляции с помощью параметра командной строки следующего вида:

-DSQLITE_MAX_LENGTH=123456789

Текущая реализация поддерживает длину строки или BLOB не более 2³¹−3, или 2 147 483 645. Некоторые встроенные функции, например hex(), могут завершиться с ошибкой значительно раньше этого предела. В приложениях, критичных к безопасности, лучше не пытаться увеличивать максимальную длину строки и BLOB. Более того, возможно, стоит снизить максимальную длину строки и BLOB до нескольких миллионов, если это возможно.

В ходе части обработки INSERT и SELECT в SQLite полное содержимое каждой строки базы данных кодируется как единый BLOB. Поэтому параметр SQLITE_MAX_LENGTH также определяет максимальное количество байт в строке таблицы. Максимальную длину строки или BLOB можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_LENGTH, size).

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы.

Максимальное количество столбцов (SQLITE_MAX_COLUMN)

Параметр времени компиляции SQLITE_MAX_COLUMN используется для установки верхней границы для:

• количества столбцов в таблице
• количества столбцов в индексе
• количества столбцов в представлении
• количества элементов в предложении SET оператора UPDATE
• количества столбцов в результирующем наборе оператора SELECT
• количества элементов в предложении GROUP BY или ORDER BY
• количества значений в операторе INSERT

Значение SQLITE_MAX_COLUMN по умолчанию равно 2000. Его можно увеличить во время компиляции, но не более чем до 32767. Многие опытные разработчики уверены, что хорошо спроектированная база данных никогда не потребует больше 100 столбцов на таблицу.

В большинстве приложений число столбцов не превышает несколько десятков. В генераторе SQL в SQLite имеются алгоритмы с O(N²), где N — количество столбцов. Поэтому, если задать SQLITE_MAX_COLUMN слишком большое значение и генерировать SQL с множеством столбцов, sqlite3_prepare_v2() может работать медленно.

Максимальное количество столбцов можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_COLUMN, size).

Максимальная длина SQL-выражения (SQLITE_MAX_SQL_LENGTH)

Максимальное количество байт в тексте SQL-выражения ограничено значением SQLITE_MAX_SQL_LENGTH, которое по умолчанию равно 1 000 000 000.

Если SQL-выражение ограничено длиной в миллион байт, это не позволит вставить строки большего размера прямо в операторах INSERT. В любом случае лучше использовать параметры-заполнители для передачи данных. Следует формировать короткие SQL-выражения.

INSERT INTO tab1 VALUES(?,?,?);

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

Максимальную длину SQL-выражения можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_SQL_LENGTH, size).

Максимальное количество таблиц в JOIN

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

Максимальная глубина дерева выражений

SQLite разбирает выражения в дерево для обработки. В процессе генерации кода SQLite обходит это дерево рекурсивно. Глубина деревьев выражений поэтому ограничена, чтобы избежать чрезмерного использования пространства стека. Параметр SQLITE_MAX_EXPR_DEPTH определяет максимальную глубину дерева выражений. Если значение равно 0, ограничение не применяется. В текущей реализации значение по умолчанию равно 1000.

Максимальную глубину дерева выражений можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_EXPR_DEPTH, size), если SQLITE_MAX_EXPR_DEPTH изначально положителен. Иными словами, максимальную глубину выражений можно уменьшить во время выполнения только при наличии ограничения глубины выражений на этапе компиляции.

Если SQLITE_MAX_EXPR_DEPTH установлен в 0 во время компиляции (то есть глубина выражений не ограничена), то sqlite3_limit(db, SQLITE_LIMIT_EXPR_DEPTH, size) не выполняет никаких действий.

Максимальное количество аргументов функции

Параметр SQLITE_MAX_FUNCTION_ARG определяет максимальное количество параметров, которые можно передать SQL-функции. На протяжении многих лет значение по умолчанию составляло около 100, однако начиная с версии SQLite 3.48.0 (2025-01-14) оно было увеличено до 1000. Количество аргументов функции иногда хранится в знаковом 16-битном целом числе.

Поэтому жёсткая верхняя граница SQLITE_MAX_FUNCTION_ARG составляет 32767. Максимальное количество аргументов функции можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_FUNCTION_ARG, size).

Максимальное количество термов в составном операторе SELECT

Составной оператор SELECT — это два или более оператора SELECT, соединённых операторами UNION, UNION ALL, EXCEPT или INTERSECT. Каждый отдельный оператор SELECT внутри составного SELECT называется «термом». Генератор кода в SQLite обрабатывает составные операторы SELECT с помощью рекурсивного алгоритма. Чтобы ограничить размер стека, количество термов в составном SELECT ограничено.

Максимальное количество термов задаётся параметром SQLITE_MAX_COMPOUND_SELECT, значение по умолчанию которого равно 500. Это щедрое ограничение, поскольку на практике количество термов в составном SELECT почти никогда не превышает однозначных чисел. Максимальное количество термов составного SELECT можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_COMPOUND_SELECT, size).

Максимальная длина шаблона LIKE или GLOB

Алгоритм сопоставления с образцом, используемый в реализации LIKE и GLOB по умолчанию в SQLite, может демонстрировать производительность O(N²) (где N — количество символов в шаблоне) в определённых патологических случаях.

Чтобы избежать атак типа «отказ в обслуживании» со стороны злоумышленников, способных задавать собственные шаблоны LIKE или GLOB, длина шаблона ограничена значением SQLITE_MAX_LIKE_PATTERN_LENGTH байт. Значение этого ограничения по умолчанию равно 50000.

Современная рабочая станция может достаточно быстро вычислить даже патологический шаблон LIKE или GLOB длиной 50000 байт. Проблема отказа в обслуживании возникает только тогда, когда длина шаблона достигает миллионов байт.

Тем не менее, поскольку большинство полезных шаблонов LIKE или GLOB имеют длину не более нескольких десятков байт, параноидально настроенные разработчики приложений могут захотеть снизить этот параметр до нескольких сотен, если знают, что внешние пользователи способны генерировать произвольные шаблоны.

Максимальную длину шаблона LIKE или GLOB можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_LIKE_PATTERN_LENGTH, size).

Максимальное количество хост-параметров в одном SQL-операторе

Хост-параметр (host parameter) — это заполнитель в SQL-операторе, который заполняется с помощью одного из интерфейсов sqlite3_bind_XXXX(). Многие SQL-программисты знакомы с использованием знака вопроса (?) в качестве хост-параметра. SQLite также поддерживает именованные хост-параметры с префиксами :, $ или @, а также нумерованные хост-параметры вида ?123.

Каждому хост-параметру в операторе SQLite присваивается номер. Нумерация обычно начинается с 1 и увеличивается на единицу с каждым новым параметром. Однако при использовании формы ?123 номером хост-параметра является число, следующее за знаком вопроса. SQLite выделяет память для хранения всех хост-параметров с номерами от 1 до наибольшего использованного номера хост-параметра.

Таким образом, SQL-оператор, содержащий хост-параметр вида ?1000000000, потребует гигабайты памяти. Это может легко исчерпать ресурсы хост-машины.

Чтобы предотвратить чрезмерное выделение памяти, максимальное значение номера хост-параметра задаётся параметром SQLITE_MAX_VARIABLE_NUMBER, который по умолчанию равен 999 для версий SQLite до 3.32.0 (2020-05-22) или 32766 для версий SQLite после 3.32.0. Максимальный номер хост-параметра можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, size).

Максимальная глубина рекурсии триггеров

SQLite ограничивает глубину рекурсии триггеров, чтобы предотвратить использование неограниченного объёма памяти оператором, задействующим рекурсивные триггеры. До версии SQLite 3.6.18 (2009-09-11) триггеры не были рекурсивными, поэтому данное ограничение не имело смысла. Начиная с версии 3.6.18 рекурсивные триггеры поддерживаются, однако их необходимо явно включить с помощью оператора PRAGMA recursive_triggers.

Параметр SQLITE_MAX_TRIGGER_DEPTH имеет смысл только при включённых рекурсивных триггерах. Максимальная глубина рекурсии триггеров по умолчанию равна 1000.

Максимальное количество подключённых баз данных

Оператор ATTACH является расширением SQLite, позволяющим связать две или более баз данных с одним соединением с базой данных и работать с ними как с единой базой данных. Количество одновременно подключённых баз данных ограничено значением SQLITE_MAX_ATTACHED, которое по умолчанию равно 10. Максимальное количество подключённых баз данных не может быть увеличено выше 125.

Максимальное количество подключённых баз данных можно уменьшить во время выполнения с помощью интерфейса sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, size).

Максимальное количество страниц в файле базы данных

SQLite способен ограничивать размер файла базы данных, чтобы предотвратить его чрезмерный рост и избыточное потребление дискового пространства. Параметр SQLITE_MAX_PAGE_COUNT задаёт максимальное количество страниц, допустимое в одном файле базы данных. Попытка вставить новые данные, которая привела бы к превышению этого размера, вернёт SQLITE_FULL.

Наибольшее возможное значение SQLITE_MAX_PAGE_COUNT равно 4294967294 (2³²−2). Начиная с версии 3.45.0 (2024-01-15), значение 4294967294 также является значением по умолчанию для SQLITE_MAX_PAGE_COUNT. При использовании размера страницы по умолчанию в 4096 байт это даёт максимальный размер базы данных около 17,5 терабайта.

Если размер страницы увеличить до максимального значения в 65536 байт, файл базы данных может вырасти до приблизительно 281 терабайта. Для изменения этого ограничения во время выполнения можно использовать PRAGMA max_page_count.

Максимальное количество строк в таблице

Теоретически максимальное количество строк в таблице равно 2⁶⁴ (18446744073709551616, или около 1,8e+19). Это ограничение недостижимо, поскольку первым будет достигнут максимальный размер базы данных в 281 терабайт. База данных объёмом 281 терабайт может содержать не более приблизительно 2e+13 строк, и то лишь при отсутствии индексов и при условии, что каждая строка содержит очень мало данных.

Максимальный размер базы данных

Каждая база данных состоит из одной или нескольких «страниц». В пределах одной базы данных все страницы имеют одинаковый размер, однако в разных базах данных размер страниц может быть степенью двойки в диапазоне от 512 до 65536 включительно. Максимальный размер файла базы данных составляет 4294967294 страницы.

При максимальном размере страницы в 65536 байт это соответствует максимальному размеру базы данных приблизительно 2,8e+14 байт (281 терабайт, или 256 тебибайт, или 281474 гигабайта, или 262143 гибибайта).

Эта верхняя граница не тестировалась, поскольку у разработчиков нет доступа к оборудованию, способному достичь этого предела. Тем не менее тесты подтверждают, что SQLite ведёт себя корректно и предсказуемо, когда база данных достигает максимального размера файла базовой файловой системы (который обычно значительно меньше теоретического максимального размера базы данных), а также когда база данных не может расти из-за исчерпания дискового пространства.

Максимальное количество таблиц в схеме

Каждая таблица и индекс требуют как минимум одной страницы в файле базы данных. Таким образом, максимальное количество страниц в файле базы данных является также верхней границей числа таблиц и индексов в схеме. Под «индексом» в предыдущих предложениях подразумевается индекс, созданный явно с помощью оператора CREATE INDEX, либо неявные индексы, создаваемые ограничениями UNIQUE и PRIMARY KEY.

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

Типичные ошибки при работе с ограничениями

Работая с ограничениями SQLite, я замечаю несколько устойчивых паттернов, которые приводят к проблемам на практике.

Встраивание больших данных в SQL-литералы. Разработчики иногда формируют SQL-операторы конкатенацией строк, встраивая большие значения прямо в текст запроса. Это не только упирается в SQLITE_MAX_SQL_LENGTH, но и открывает вектор SQL-инъекций. Правильный подход — использовать sqlite3_bind_XXXX() с параметрами-заполнителями.

Использование нумерованных хост-параметров с большими номерами. Параметр вида ?1000000000 заставит SQLite выделить память под все номера от 1 до 1 000 000 000. Это почти гарантированно приведёт к исчерпанию памяти. Нумерованные параметры стоит использовать только с небольшими последовательными номерами.

Игнорирование ограничений при проектировании схемы. Таблицы с сотнями столбцов — не только нарушение нормализации, но и источник квадратичного замедления в генераторе кода SQLite. Если SQLITE_MAX_COLUMN переопределён в очень большое значение, sqlite3_prepare_v2() начнёт работать заметно медленнее при большом числе столбцов.

Отсутствие ограничений времени выполнения для ненадёжного кода. Приложения, принимающие SQL от внешних источников, должны явно устанавливать ограничения через sqlite3_limit(). Без этого злоумышленник может сформировать запрос, который исчерпает ресурсы процесса.

Как настроить ограничения под конкретное приложение

Большинство ограничений SQLite можно настроить двумя способами: на этапе компиляции через макросы препроцессора и во время выполнения через sqlite3_limit().

Компиляционные макросы задают абсолютный потолок для всего процесса. Например, -DSQLITE_MAX_LENGTH=10000000 снизит максимальную длину строки до 10 мегабайт для всех соединений. Это полезно, когда нужно жёстко ограничить потребление ресурсов на уровне сборки.

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

Стоит помнить, что sqlite3_limit() может только уменьшать ограничения относительно компиляционного максимума, но не увеличивать их выше него. Если SQLITE_MAX_EXPR_DEPTH установлен в 0 при компиляции, вызов sqlite3_limit(db, SQLITE_LIMIT_EXPR_DEPTH, size) не даст никакого эффекта.

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

Ответы на эти вопросы могут быть для вас полезными

Можно ли увеличить ограничения SQLite выше значений по умолчанию? Да, большинство ограничений можно увеличить на этапе компиляции через соответствующие макросы препроцессора. Однако интерфейс sqlite3_limit() во время выполнения позволяет только уменьшать ограничения относительно компиляционного максимума, но не превышать его.

Почему SQLite ограничивает количество таблиц в JOIN именно 64? Это архитектурное решение: генератор кода SQLite использует битовые маски, где каждый бит соответствует одной таблице объединения. 64-битное целое число даёт ровно 64 возможных позиции. Механизма для изменения этого ограничения не предусмотрено.

Что произойдёт, если использовать хост-параметр ?1000000000 в SQL-операторе? SQLite выделит память для хранения всех хост-параметров с номерами от 1 до 1 000 000 000, что потребует гигабайты оперативной памяти и, скорее всего, исчерпает ресурсы машины. Параметр SQLITE_MAX_VARIABLE_NUMBER существует именно для предотвращения таких ситуаций.

Как ограничить SQLite для ненадёжного внешнего кода, не затрагивая внутренние соединения? Используйте sqlite3_limit() для каждого соединения отдельно. Внутренним соединениям оставьте компиляционные значения по умолчанию, а для соединений, работающих с внешними данными, явно установите более низкие ограничения через sqlite3_limit(db, SQLITE_LIMIT_*, size).

Влияет ли размер схемы на производительность запуска соединения? Да. При каждом открытии базы данных вся схема сканируется, разбирается и хранится в памяти в виде дерева разбора. Время запуска соединения и начальное потребление памяти прямо пропорциональны размеру схемы, поэтому очень большое количество таблиц и индексов замедляет инициализацию соединения.