Миллиард строк в таблице: на каком порядке запрос перестаёт укладываться в секунду

MAATRIX

Таблица растёт тихо: сначала тысяча строк, потом миллион, потом кто-то замечает, что дашборд грузится не мгновенно, а через пару секунд. Разработчики гуглят «на каком объёме база тормозит» и находят чужие бенчмарки с чужим железом и чужими индексами — и число оттуда почти ничего не говорит про их запрос. Точки перелома нет как константы, но есть закономерности в том, от чего она зависит, и способ измерить её самостоятельно за час, а не искать в интернете.

Почему нет единого числа: что определяет точку перелома

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

Первый — есть ли подходящий индекс под конкретный WHERE, JOIN или ORDER BY, и использует ли его планировщик. Индексированный поиск по равенству растёт логарифмически с размером таблицы, последовательное сканирование — линейно. Разница между O(log n) и O(n) на миллиарде строк — это разница между миллисекундами и минутами для одного и того же запроса, при одном и том же железе.

Второй — помещаются ли рабочие данные в память. Пока индекс и «горячая» часть таблицы живут в page cache и в буферном пуле СУБД, чтение идёт со скоростью RAM. Как только рабочий набор перестаёт помещаться в доступную память, каждое обращение может обернуться случайным чтением с диска — а оно на порядки медленнее последовательного и чтения из памяти. Один и тот же запрос на сервере с 16 ГБ RAM и на сервере с 128 ГБ RAM может отличаться по времени не в разы, а на порядок — при абсолютно одинаковой таблице.

Третий — селективность условия. WHERE user_id = 12345 на таблице в миллиард строк, где у каждого пользователя в среднем сто записей, — чтение сотни строк через индекс, доли миллисекунды. WHERE status = 'active', если активных 40% от миллиарда, — это уже сотни миллионов строк, и здесь индекс скорее помешает: планировщик правильно выберет последовательное сканирование.

Четвёртый — тип операции: точечный SELECT, диапазонный скан, JOIN, агрегация с GROUP BY — у каждого своя кривая роста стоимости, и они расходятся на больших объёмах всё сильнее. Пятый — актуальность статистики планировщика и наличие подходящей структуры хранения; здесь чаще всего теряют производительность внезапно, а не постепенно — об этом ниже.

Из-за этой многофакторности любой ответ вида «после 50 миллионов строк начинаются проблемы» без указания железа, схемы и типа запроса — не факт, а анекдот с чужого проекта.

От тысяч до миллионов: где индекс становится обязательным, а не опциональным

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

Граница начинает ощущаться там, где таблица перестаёт целиком помещаться в кеш, а последовательное сканирование начинает упираться в реальную скорость чтения с диска, а не с RAM. Порядок величины зависит от размера строки и объёма памяти под кеш: таблица с узкими строками (несколько чисел) может держаться в памяти и при сотнях миллионов записей, а таблица с текстовыми полями и JSON-колонками упрётся в диск уже на нескольких миллионах.

Практическое правило: индекс стоит считать обязательным, как только на часто выполняемом запросе вы видите в EXPLAIN узел Seq Scan с числом обработанных строк, растущим вместе с таблицей. Вопрос тогда не «нужен ли индекс», а «когда именно вы его добавите: сейчас на спокойном проде или ночью во время инцидента». Подробнее о том, как индекс ускоряет запрос и в каких случаях наоборот замедляет вставку и обновление, — у индексов есть реальная цена, и её тоже нужно учитывать, а не считать индекс бесплатным ускорителем.

Индекс не спасает от плохой селективности: индекс по колонке, где 90% значений одинаковы, планировщик проигнорирует сам — и правильно сделает, потому что чтение по индексу 900 миллионов строк из миллиарда медленнее, чем последовательное сканирование той же таблицы.

JOIN и агрегации: как цена растёт нелинейно на десятках и сотнях миллионов строк

Точечный SELECT по индексированному ключу почти не чувствует роста таблицы — логарифм растёт медленно: разница между поиском в миллионе строк и миллиарде строк — это разница в 2-3 «уровня» B-дерева, доли миллисекунды. А вот JOIN и агрегация ведут себя принципиально иначе, потому что их стоимость зависит не от размера одной таблицы, а от произведения или суммы объёмов данных, которые нужно сопоставить друг с другом.

Планировщик выбирает между несколькими алгоритмами соединения, и выбор меняется вместе с объёмом данных:

• Nested Loop — для каждой строки внешней таблицы ищется совпадение во внутренней: n × log(m), дёшево при маленькой внешней таблице и индексе на внутренней. На тысячах и десятках тысяч строк это часто самый быстрый вариант.
• Hash Join — по меньшей таблице строится хеш-таблица в памяти, большая сканируется один раз. Хорошо работает, пока меньшая таблица помещается в work_mem; если нет — база сбрасывает части хеш-таблицы на диск, и время резко растёт.
• Merge Join — обе таблицы отсортированы (или уже упорядочены индексом) и сливаются за один проход. Эффективен на очень больших объёмах, если сортировка уже есть или дёшева.

План, оптимальный для миллиона строк, часто перестаёт быть оптимальным для сотни миллионов — не потому что база «сломалась», а потому что изменилось соотношение стоимостей. Nested Loop с квадратичным по сути поведением на двух больших нефильтрованных таблицах — классика, когда запрос, работавший секунду на тестовых данных, часами работает в проде. Разбор того, как именно планировщик выбирает между этими способами и на чём чаще всего ошибается, стоит прочитать до того, как таблицы вырастут, а не после.

С агрегациями похожая история, только резче. COUNT(*) без условий на тысяче строк — мгновенно. На миллиарде строк в PostgreSQL (без покрывающего индекса и index-only scan) это может означать чтение всей таблицы целиком, потому что MVCC не хранит готовое число живых строк — его нужно посчитать. Отдельно про то, почему COUNT на большой таблице стоит так дорого и какие есть обходные пути, — один из частых источников «внезапного» тормоза: запрос выглядит тривиально, а стоит как полное сканирование. GROUP BY с большим числом групп ведёт себя похоже: пока промежуточные данные помещаются в work_mem, агрегация быстрая; как только нет — начинается сортировка на диске, и время растёт не в разы, а на порядок буквально на соседнем миллионе строк.

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

Планировщик СУБД не читает таблицу перед каждым запросом, чтобы понять, сколько строк подойдёт под условие, — это было бы медленнее самого запроса. Вместо этого он полагается на статистику: гистограммы распределения значений, число уникальных значений в колонке, примерную корреляцию физического порядка строк с логическим. В PostgreSQL эта статистика обновляется командой ANALYZE (обычно автоматически через autovacuum) и хранится в pg_statistic; в MySQL — похожий механизм через ANALYZE TABLE и внутреннюю статистику индексов InnoDB.

Пока таблица маленькая, устаревшая статистика почти не вредит: даже ошибка в оценке числа строк вдвое не меняет исход — оба варианта плана выполнятся быстро. На большой таблице та же ошибка приводит к качественно другому решению: планировщик может выбрать Nested Loop там, где нужен Hash Join, или проигнорировать хороший индекс, посчитав условие недостаточно селективным. Разница между «оценили 100 строк» и «оценили 10 миллионов» — это разница между планом на миллисекунды и планом на часы, и оба варианта технически корректны с точки зрения SQL, просто один катастрофически медленнее.

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

Практический вывод: после любой операции, которая резко меняет объём или распределение данных в таблице (массовая загрузка, удаление большого куска, смена характера трафика), стоит явно запустить ANALYZE на затронутых таблицах, а не полагаться на то, что autovacuum успеет вовремя — особенно на таблицах, для которых уже настроен нестандартный порог autovacuum_analyze_scale_factor.

Партиционирование: когда режет время запроса, а когда добавляет только сложности

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

Партиционирование помогает, когда выполняются оба условия сразу: подавляющее большинство запросов фильтруют по колонке, по которой партиционирована таблица (обычно это дата), и периодически нужно быстро удалять или архивировать старые данные целиком. Тогда планировщик умеет отсекать ненужные партиции ещё на этапе планирования (partition pruning) — запрос за последнюю неделю в таблице, партиционированной по месяцам и хранящей пять лет данных, читает одну-две партиции вместо всей таблицы, и это ускорение того же порядка, что даёт хороший индекс. А DROP старой партиции — операция с метаданными, а не построчное удаление миллионов строк с последующим часовым VACUUM.

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

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

Как замерить свою точку перелома, а не гадать по чужим бенчмаркам

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

Сгенерируйте синтетические данные несколькими порядками величины: например, 10 тысяч, 1 миллион, 100 миллионов и 1 миллиард строк — в тестовую таблицу с той же схемой, теми же индексами и похожим распределением значений (равномерное распределение и распределение с перекосом в сторону нескольких значений ведут себя по-разному, и если в проде перекос есть, тестовые данные должны его повторять). Для PostgreSQL быстрый способ генерации:

INSERT INTO test_table (user_id, status, created_at, payload)
SELECT
  (random() * 1000000)::int,
  (ARRAY['active','inactive','pending'])[floor(random()*3+1)],
  now() - (random() * interval '365 days'),
  md5(random()::text)
FROM generate_series(1, 100000000);

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

Замеряйте не просто время выполнения, а через EXPLAIN (ANALYZE, BUFFERS, TIMING) — команда покажет реальный план, время каждого узла и, что важнее всего, соотношение shared hit (страницы из кеша) и shared read (страницы с диска). Резкий рост доли shared read при переходе к следующему порядку объёма — это и есть момент, когда рабочий набор перестал помещаться в память, и именно он, а не абстрактное число строк, обычно и есть настоящая точка перелома.

Тестируйте на железе, похожем на прод, — не на ноутбуке с локальным NVMe, если прод работает на сетевом диске облака с лимитом IOPS, и не с холодным кешем сразу после рестарта СУБД, если в проде база работает неделями и кеш прогрет. Разница между случайным и последовательным чтением сама по себе даёт порядок разницы в скорости — это отдельная переменная, которую стоит контролировать, а не смешивать с эффектом от роста таблицы.

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

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