Сказ о том, как нормализация данных завела производительность many-to-many в Postgres в тупик, как это зло было повержено, и как тут нам помог Clickhouse.
Порой бывают ситуации, когда стоит посмотреть на задачу будто с нуля и отбросить предыдущий опыт и best practices. Подумать на несколько шагов вперёд. И лучше до того, когда уже вышли из SLA или нахватали негатива от клиентов или бизнеса. Об одной такой задаче и стандартном решении, которое пришлось больно редизайнить, хочется и поделиться с сообществом в этой статье.
Сразу оговорюсь: я - приверженец писать меньше кода, когда это возможно. Лучший код - это его отсутствие. Лучшее решение - стандартное, максимально покрываемое мануалом. Особенно сегодня, когда всё уже придумано и сделано, осталось лишь правильно настроить и использовать.
Что может быть лучше для Symfony-приложения чем создание сущностей и схемы таблиц с помощью maker-бандла, просто указывая названия свойств и выбирая связи?
Но иногда жизнь работа заставляет взглянуть под другим углом на привычные вещи.
Итак, задача довольно простая: есть финансовые операции - транзакции, которые помечаются тегами ключ-значение, например:
merchant: someMerhcant,
provider: somePrivider,
customer: someCustomer
card: someCardToken,
country: RUS
Требуется уметь настраивать ограничительные лимиты в любых разрезах, например:
сумма операций более 5млн.руб по someProvider в месяц,
количество операций ��т одного плательщика у someMerchant не более 10 в день,
сумма операций по someCardToken извне России не более 500тыс.р. в неделю по anotherMerchant,
и т.п.
Стек стандартный: PHP + Symfony/Doctrine. На нём схема стандартно сводится к такой связи много-ко-многим:
И к такому описанию сущностей в Symfony:
/** * @ORM\Table(name="operation") * @ORM\Entity() */ class Operation { // ...... /** * @var ArrayCollection|Tag[] * * @ORM\ManyToMany(targetEntity="Tag") * @ORM\JoinTable( * name="operation_tag", * joinColumns={@ORM\JoinColumn(name="operation_id", referencedColumnName="operation_id")}, * inverseJoinColumns={@ORM\JoinColumn(name="tag_id", referencedColumnName="tag_id")} * ) */ private $tags; // ...... } /** * @ORM\Table(name="tag") * @ORM\Entity() */ class Tag { /** * @ORM\Column(name="tag_id", type="bigint", nullable=false) * @ORM\Id * ... */ private int $tagId; /** * @ORM\Column(name="name", type="string", length=255, nullable=false) */ private string $name; /** * @ORM\Column(name="value", type="string", length=255, nullable=false) */ private string $value; // ... }
Схема немного осложняется тем, что теги - это не просто строковое значение, а пара ключ-значение.
Поэтому, например, подсчёт лимита операций по провайдеру someProvider от одного уникального плательщика у someMerchant будет выглядеть так:
-- подсчёт лимита операций для someProvider от одного плательщика у someMerchant SELECT SUM(o.amount) FROM operation o INNER JOIN operation_tag ot1 ON ot1.operation_id = o.operation_id INNER JOIN tags t1 ON ot1.tag_id = t1.tag_id INNER JOIN operation_tag ot2 ON ot2.operation_id = o.operation_id INNER JOIN tags t2 ON ot2.tag_id = t2.tag_id INNER JOIN operation_tag ot3 ON ot3.operation_id = o.operation_id INNER JOIN tags t3 ON ot3.tag_id = t3.tag_id WHERE t1.name = 'provider' AND t1.value = 'someProvider' AND t2.name = 'merchant' AND t2.value = 'someMerchant' AND t3.name = 'customer_id' AND t3.value = 'someUniqueCustomerId'; AND o.created_at BETWEEN '2023-08-01' AND '2023-08-31'
Время выполнения: ~1.5 сек.
А если в агрегации будет участвовать не 3, а 4 тега, то джойнов будет соответственно 8, и так далее.
А если проверка складывается из нескольких последовательных таких запросов, нетрудно посчитать общее время её выполнения.
Конечно ни про какие SLA в таком случае и говорить не приходится.
Почему-то мы сразу были убеждены, что упёрлись в производительность самого Postgres, и после быстрых изысканий было решено попробовать перейти на что-то кардинально более быстрое, особенно с учетом быстро растущего объём данных.
Критерии выбора были следующие:
Поддержка SQL или его подобия, чтобы переписать только слой работы с базой, минимально затрагивая бизнес-логику.
Выполнение аналитических запросов и агрегации больших объемов данных, т.к. именно это составляет львиную долю нагрузки.
Наличие надёжного клиента для php.
Недорогая цена.
Развитое сообщество и условно недорогая поддержка.
Nosql-хранилища отпали сразу на пункте 1, Oracle не подошёл по пунктам 4 и 5. В итоге выбор пал на Clickhouse. Он славится своим быстродействием на больших объёмах, к тому же бесплатный, да и вендор его вызывает доверие.
Итак, решено.
Синтаксис создания таблицы в Clickhouse похож на MySQL:
CREATE TABLE IF NOT EXISTS ourdb.operation ( ... amount UInt64, tags Nested ( name String, value String ) )
Особенность Кликхауса в том, что он силён в денормализованных данных. Фтопку джойны!
Есть такой тип Nested, где описывается вложенная таблица целиком. То ��сть внутри каждой строки-операции существует своё множество тегов ключ : значение.
И тогда в Clickhouse запрос с агрегацией суммы стал выглядеть так:
SELECT SUM(amount) AS result FROM operation WHERE created_at >= '2023-08-01 00:00:00' AND created_at <= '2023-08-31 23:59:59' AND tags.value[indexOf(tags.name, 'provider')] = 'someProvider' AND tags.value[indexOf(tags.name, 'merchant')] = 'someMerchant' AND tags.value[indexOf(tags.name, 'customer_id')] = 'someUniqueCustomerId'
Запускаем запрос... и... время выполнения: 0.4сек! Причем с любым количеством тегов, которые при many-to-many превратились бы в смертельные джойны.
Замечательно. Но вместе с тем Кликхаус привнёс и некоторые неудобства. Во-первых, он хорош для чтения, но не очень эффективен при записи, и есть официальная рекомендация вставлять данные пачками от 1000 строк. Можно конечно ею пренебречь, но экспериментировать не хотелось. Можно и ждать, пока накопится такая пачка, но в некоторых случаях отсутствие вовремя вставленной строки может быть критично, повлечь race condition и т.д. Попробовали гибридный вариант - оставлять самую свежую оперативную часть (скажем, операции за последний час) в Postgre, а остальной объём считать в Clickhouse, и затем складывать. Эффект был, но кардинально картину это не изменило, ведь дорогие джойны Postgres даже на малом объёме всё равно остаются дорогими джойнами.
А что если попробовать такую вложенность реализовать в самом Postgres? Он же обладает большими возможностями для работы с json. Сказано - сделано.
Добавляем столбец tags_nested типа jsonb, а также GIN-индекс. В нашем случае поиск будет производиться по плоским массивам данных, но если предполагаются древовидные иерархии, лучше подойдёт GIST-индекс.
ALTER TABLE operation ADD tags_nested JSONB DEFAULT '{}' NOT NULL; CREATE INDEX ix_operation_tags_nested ON operation USING gin(tags_nested jsonb_path_ops);
Попробуем выполнить:
SELECT SUM(o.amount) FROM operation o WHERE o.tags_nested::jsonb @> '[{"provider":"someProvider"},{"merchant":"someMerchant"},{"customer_id":"someUniqueCustomerId"}]' AND o.created_at BETWEEN '2023-08-01' AND '2023-08-31'
Время выполнения с указанием любого количества тегов не превышает 0.3сек! А что, так можно было?
Здесь для поиска значений, содержащих все указанные теги, хорошо подходит оператор вложенности массивов @>.
Однако это, конечно, и иллюстрирует недостаток такой схемы и подхода в целом - мы привязали бизнес-логику к реализации конкретной СУБД. Например, в мускуле его нужно будет переписать на JSON_CONTAINS().
Но поняв, что базу мы будем менять скорее всего только вместе со всей инфраструктурой и кодовой ба��ой в некоем абстрактном светлом будущем, решили, что это не стоп-фактор.
Вот так на графике выглядел момент перехода:
Заключение. Clickhouse решили приберечь до лучших времён, когда Postgres перестанет справляться с нагрузкой и объёмами.
Spoiler: сейчас, спустя 2 года, это время настало, что и побудило описать этот опыт в статье. Прямо сейчас актуализируем Clickhouse и адаптируем к текущим требованиям. И хорошо, что из кода его не выпиливали, а аккуратно отключили.
Планами запроса решил не перегружать статью, да и сильно упростил примеры - они c костами были бы не совсем релевантны.
Всем peace! Пишите, у кого был подобный опыт, буду рад.
Спасибо, что дочитали.
