Сказ о том, как нормализация данных завела производительность 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, и после быстрых изысканий было решено попробовать перейти на что-то кардинально более быстрое, особенно с учетом быстро растущего объём данных.

Критерии выбора были следующие:

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

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

3. Наличие надёжного клиента для php.

4. Недорогая цена.

5. Развитое сообщество и условно недорогая поддержка.

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! Пишите, у кого был подобный опыт, буду рад.

Спасибо, что дочитали.