Comments 17
Cначала нам казалось очень удобным, что ничего не нужно разрабатывать, DWH сами придут в нашу базу и заберут то, что им нужно. Но потом, когда мы что-то захотели изменить в своей схеме базы данных, оказались связанными по рукам и ногам и ничего изменить уже не могли.
Немного странно, что вы пускаете сторонний сервис непосредственно в таблицы. Тем более что ему нужен чистый RO. Можно было бы сформировать набор представлений (или даже табличных функций), который бы формировал, фильтровал и отдавал только нужные данные в заданной форме. Тогда проблемы с изменениями в структуре бы и не было - просто одновременно с корректировкой структуры соответствующим образом корректируется и прослойка представлений. Ведь вы же ничего из структуры не удаляете.
Если быть точнее, мы пускали не совсем сервис, а некий платформенный инструмент. И не совсем в таблицы, а скорее в слот репликации. И из-за этого фокус с вьюхами вряд ли бы получился, через слот репликации не получилось бы их изменения получать. Здесь на 100% не ручаюсь, очень глубоко я в устройство этого инструмента не погружался, но кажется ситуация была такая.
К счастью это всё уже в прошлом, теперь отправка данных в DWH делается другим, намного более гибким и независимым от БД способом.
Мы решили полностью переделать запрос получения активностей и вернуться к идее split query, но реализовать ее самим, а не полагаться на Entity Framework.
А еще такой подход показал очень хорошие результаты на нагрузочных тестах, даже на непартицированной базе.
Ну, в общем, как я понял, если бы сразу написали "нормально" SQL, то всё было бы "нормально".
На 99м перцентиле новый вариант получения записей давал ускорение на несколько десятков миллисекунд. Учитывая, что на начало сбоя время работы достигало несколько секунд - этого явно было бы недостаточно.
Самый хороший буст дала ещё более простая мера - отфильтровывание автоответчиков (в статье об этом сказано). Дальнейшие работы по партицированию были нужны для стабилизации размеров активно используемых индексов по мере роста базы и для упрощения обслуживания (например, архивации старых записей)
Старые (90+ дней) партиции на том же диске оставили или выносите отдельно?
Выглядит как «из пушки по воробьям».
1) почему вы продолжали использовать include, а не вписали проекции только тех значений что нужны конкретно для dwh? Вряд ли там все поля таблицы используются
2) как следствие пункта выше: почему не пытались сделать покрывающие индексы и свести все к index only scan?
3) почему не применили filtered indexes, вы бы могли для целей dwh сделать индекс c where, чтобы из индекса убирать автоответчики ?
Кроме того можно было сделать в индексе where по дате, чтобы отсекать данные старше 90 дней и перестраивать индекс по ночам, причем конкурентно, чтобы не мешать другим. Получился бы всего один ddl запрос
4) возможно стоило попробовать перенести op_data в jsonb поле в самой activity, тогда и читать пришлось бы меньше (вероятно) и размер возвращемого датасета снизился бы (точно). Делать key-value хранилище в таблицах бд - не самое эффективное решение. В крайнем случае сделать json_agg (два array_agg на key и value)в запросе.
почему вы продолжали использовать include, а не вписали проекции только тех значений что нужны конкретно для dwh? Вряд ли там все поля таблицы используются
.NET-сервис отдавал данные не для DWH, а для другой системы. Там отдавались нужные проекции (в статье просто не стал загромождать код этими деталями). Но они к слову тоже довольно развесистые были, там почти все поля.
почему не пытались сделать покрывающие индексы и свести все к index only scan?
Покрывающие индексы могут быть полезны если нужно читать пару полей. Когда почти вся таблица нужно, то смысла почти всю таблицу дублировать в индексах уже нет.
почему не применили filtered indexes
Т.к. всё равно прорабатывали партицированием по дате и всё делали в сжатые сроки - Банально об этом не подумали в тот момент, т.к. прорабатывали партицирование по дате и зациклились немного на идее партицирования как таковой. А так идея отличная. Частичные индексы можно было бы использовать вместо партицирования вторым уровнем по result_code, всё верно.
возможно стоило попробовать перенести op_data в jsonb поле в самой activity, тогда и читать пришлось бы меньше
Согласен, будь моя воля, я бы вообще всё в одной таблице сделал. Но тогда переезд на другую схему был для нас недоступной опцией, в статье я об этом упоминал. К слову, сейчас рассматривается вариант переезда этого сервиса на Cassandra с одной таблицей как раз т.к. приближаемся к 1 ТБ, а постгресовые инстансы больше 1 ТБ у нас очень не любят.
То есть в вашу базу напрямую не ходили внешние приложение? Тогда почему вы не могли структуру вашей базы поменять?
Про покрывающие индексы стоит говорить если вы запросы в программе сделали с нудными полями. А у вас include, который все равно в select все потащит.
Покрывающий индекс при чтении выгоден всегда. Потому что обычны идекс скан - это поиск записи в индексе (одно логическое чтение), а потом получение записи из кучи (второе логическое чтение). Если вы сделаете «широкий индекс» при выборке относительно небольшого количества строк, то index only наверное всегда выиграет у index scan. Но вы получите большой импакт при записи, так как индексы надо будет обновлять и перестраивать.
Поэтому как общий подход делать все через index only я не могу рекомендовать, но для особо экстремальных случаев помогает даже покрывающий индекс , в котором все поля кроме одного включены.
Про работу над ошибками:
В базах данных есть view, их как раз и нужно использовать при интеграции с другими системами на уровне данных. Причем Вы могли прямо в текущей базе сделать нормальную модель и отдать потребителям view.
Это у вас НЕДОСТАТОЧНАЯ нормализация данных, как и в таблице коммуникантов. Недостаточная нормализация как раз увеличивает объемы, ухудшает возможности оптимизации с помощью индексов и приводит к конфликтам рассогласованности данных.
Вы неверно употребляете термин «нормалмзация». Нормализация это отсутствие связей между не ключевыми полями в таблице и строгая связь с ключевым полем. У вас это просто ошибка моделирования.
Есть хороший инженерный коэффициент - 6. Или более наукообразно - 2Пи. Система должна выдерживать шестикратное превшение нагрузки над «рабочими» значениями. То есть во время тесты вы должны были в 6 раз увеличить объемы данных для тестирования, если у вас проблема упирается именно в объем или rps, если система упирается в частоту запросов.
Причем Вы могли прямо в текущей базе сделать нормальную модель и отдать потребителям view
Выше писал, что насколько мне известно, DWH через слот репликации данные забирали, не уверен что вьюхи тут бы помогли.
Это у вас НЕДОСТАТОЧНАЯ нормализация данных, как и в таблице коммуникантов. Недостаточная нормализация как раз увеличивает объемы, ухудшает возможности оптимизации с помощью индексов и приводит к конфликтам рассогласованности данных
Недостаточная в том плане, что коммуниканты дублируются и каждой коммуникации свой отдельных коммуникант - это да.
Под денормализацией я имел в виду то, что наша модель из пяти таблиц нормально могла бы уложиться в одну. И было бы нужно в 5 раз меньше индексов и индекс-сканов.
Насчёт корректности термина "нормализация" вас понял, тут спорить не буду.
То есть во время тесты вы должны были в 6 раз увеличить объемы данных для тестирования
И RPS и объём БД на тестах были кратно увеличены. Ошибка была в том что при этом абсолютно не воспроизводилась ситуация, в которой на одном clientId было много активностей. Вместо этого было генерилось много клиентов с одной активностью, что абсолютно не соответствовало ситуации на проде.
Если у вас не напрямую из базы читали в dwh, а вы сначала из твоей системы отдавали по api данные в другую, а уже оттуда данные попадали в dwh, то тут могла бы помочь интеграция с этой системой на уровне данных. Тем более, судя по всему, там etl типа airflow
То есть целевая модель такая:
Вы выставляете в базе вьюху, которая отдает нужные данные одной таблицей
Обкладываете её индексами
Другая система подключается к вашей с правами только на чтение этой вьюхи
PROFIT!
Вы экономите огромное количество ресурсов на сериализацию данных
Но вы писали что другую систему изменить нельзя (единственный специалист по airflow занят на другой задаче), то просто поменяйте структуру у себя
вы сначала из твоей системы отдавали по api данные в другую, а уже оттуда данные попадали в dwh
Не так. По апи данные мы отдавали системе которая к DWH отношения не имеет. DWH же данные забирали через слот репликации из нашей базы. Не через SQL-запросы к нашим таблицам, а через логическую репликацию они получали поток строк конкретных таблиц конкретной структуры. Именно поэтому вьюхи были неприменимы, на изменения во вьюхе же невозможно подписаться через репликацию.
Не рассматривали вариант с всего двумя значениями для result_code: 0-Автоответчик(неважные), 1-все остальное(важные)? Тогда партиционирование можно было сделать не по range, а по list, логика упростилась бы (и скорее всего немного выросла производительность, т.к. в условии было бы строгое равенство, а не сравнение с интервалом; оптимизатор мог бы вообще исключить это сравнение из запроса, выбирая все строки из одной из двух партиций по result_code).
Хороший вариант кстати, кажется такой не рассматривали.
Рассматривали похожий, но еще более простой - вообще не добавлять поле result_code а сделать партицирование по выражению result == 'Автоответчик' и потом методом LIST true положить в одну партицию, false в другую. Но в таком варианте возможна проблема в случае если вдруг у нас кроме Автоответчика появится какой-то еще result который относится к not-important. Тогда пришлось бы менять выражение для ключа партицирования.
Ваш вариант такой проблемы не имеет и нам бы подошёл, да.
за последующие три месяца оно выросло уже с 2 млн до почти 12 млн
"Народ не хочет платить, милорд")
Оптимизация через партицирование