Минутка tech porn
.
У нас огромная multi-tenant реляционная база данных. Таблицы по 200 ГБ - рехнуться, если честно. При этом для multi-tenant архитектуры мы юзаем самую тупую модель - "Pool" - это когда во все таблицы добавляется ключик "tenant_id". Модель неэффективная, но зато простая в реализации и поддержке.
(кстати у AWS пролетала классная дока про дизайн multi-tenant систем, где разобраны все варианты, мастрид для всех CTO)
Все тормозило и заикалось. Клиенты бесились, сервера перегревались. Задачи типа "получить запись по ID" работали нормально, но любой список типа
"непрочитанные письма за сегодня" в многотерабайтной базе начинает жестко тупить. Даже с правильными индексами. Один жирный клиент с дохреллионом записей притормаживает мелких клиентов, у которых данных совсем мало. Надо что-то делать.
И тут нам пришло Великое Озарение [sarcasm], которое рано или поздно приходит любому DBA - о том, что основная работа всегда ведется с "верхушкой" данных. А огромный "long tail" всегда лежит мертвым грузом и нахуй не нужен юзается только в отчетах.
Первая мысль - надо сделать "вертикальный" партишенинг. Т.е. "старые" данные спихивать куда-то за горизонт (на отдельный диск или даже сервер), а "активные" данные держать где-то под рукой.
Мысль правильная, но нет.
Грамотный партишенинг - это оказалось сложно, долго и с первого раза не работает. Перефразируя известную поговорку про яхтинг, в жизни DBA есть два счастливых дня - день когда он настроил партишенинг и день когда он его прибил. Ибо сервер все равно время от времени сканил партишены как попало и расследовать это довольно тяжело.
Я уже слышу крики из зала: "шардинг", "кликхаус", "разделяй OLTP и DWH". И прочий оверинжиниринг. Сразу нет. У нас есть self-hosted версия, которая должна заводиться в один клик даже у домохозяек. Хотелось простой хак, который решит все проблемы одной строчкой.
И тут я случайно вспомнил про офигенный читкод -
фильтрованные индексы. Ведь по умолчанию индекс делается по всей таблице. Но зачем, если можно индексировать только 0.1%?
В коде любого CRUD-приложения, в бизнес-логике всегда есть признак, который отличает "старые" данные от "новых". Ну типа "статус проекта = сдан". Или "статус заказа = обработан". И это условие уже есть в большинстве ваших SELECT'ов. В нашем случае это был "статус тикета = закрыт".
Что делает DBA-джун? Создает индекс по этой колонке. Чтобы, значит, поиск незакрытых тикетов был быстрым и классным.
CREATE INDEX myIndex
ON messages (processed)
Что делает прошаренный DBA-синьор? Создает еще
"filtered index" с этим условием
CREATE INDEX myIndex
ON messages (column1, column2...)
WHERE processed = 0 --вот так
И следит, чтобы это условие было в селектах.
В результате даже в многотерабайтной базе мы имеем маленький быстрый индекс всего в десятки мегабайт (!), который всегда показывает на самые последние данные. Как только данные перестают удовлетворять признаку - они из индекса улетают. Сами.
Когда мы прикрутили первый фильтрованный индекс и стали смотреть статистику использования, мы офонарели - SQL Server бросил все дела, и стал жадно его жрать. Приложение ускорилось в разы, нагрузка на проц снизилась на 80%. Посмотрите график - до и после внедрения только ОДНОГО пробного индекса.
Наш бд-сервер имеет всего 4 ядра и 32 гига памяти, при этом запросто тянет базу в несколько терабайт и сотни тысяч DAU. У нас в команде есть негласный челлендж - сколько можно протянуть на этом железе без апгрейдов? Уже годы держимся))
К чему я все это - не бегите прикручивать громоздкие решения, старые и скучные rdbms умеют много крутых штук даже на дохлом железе.
PS. "Filtered/partial index" есть в SQL Server, PG и в Монге. В мускуле есть
воркераунд
PPS. есть нюанс, кстати. Когда делаете filtered index, обязательно включайте фильтрованную колонку в "include". Так мы заставляем сервер поддерживать "статистику" по колонке. Без статистики все это великолепие работать не будет, сервер индекс не заметит.
CREATE INDEX myIndex
ON Messages (Column1, Column2...)
INCLUDE (Processed) --важно
WHERE Processed = 0
