Комментарии 20
Вы приятно удивитесь, если в Postgres создадите структуры аналогичные CH.
Почему-то мы сразу были убеждены, что упёрлись в производительность самого Postgres
Забавно, что не начали с денормализации данных в PostgreSQL и решили сразу переехать на другую базу. Можно было точно так же собрать Many to Many из исходной схемы в одну запись.
Разработчик пытается костылями исправить ошибку аналитика. В методологии Data Vault операция - это не сущность (hub). Операция - это связь (link). Поэтому все ID тегов должны быть в одной таблице связей Операция, а ER-диаграмма кардинально перерисована. В фильтрах нужно использовать не сами теги, а их ID. То есть, джойны вообще не нужны. Вдобавок inner join работает медленнее, чем left join. И не надо обвинять ни в чем не повинные PostgreSQL и нормализацию данных.
Но зачем сразу JSON, когда в Postgres есть те же вложенные таблицы, что в CH, разве что в документации их зовут массивами.
Nosql-хранилища отпали сразу на пункте 1,
Я понимаю, что оно отвалилось бы позже, там же, где и Oracle, но есть nosql решения с поддержкой самого что ни на есть SQL. Например, Iris от Intersystems (раньше оно звалось Caché). Весьма приятная штука, только стоит дорого. Есть бесплатная версия для ознакомления, но она сильно ограничена в возможностях (сервер с хорошей нагрузкой не организуешь).
Рассказ про Clickhouse и его возможности мог бы быть интересен, как и сравнение разных реализаций одного и того же в разных СУБД. Но к выводу нас подвели через несколько ложных утверждений, что не добавляет баллов статье.
EAV не тождественно равен нормализации, применен явно не самым удачным образом. Через восемь джойнов фильтр по восьми тегам делать вовсе не нужно (распростаренная задачка на базовые возможности SQL), в самом по-себе M2M никаких заложенных природой проблем нет. Аналоги предложенного решения, как уже упомянули, есть в самом Postgres. Планы выполнения и прочие факты при анализе производительности как раз имеют первоочередное значение, а сравнение 1.5 сек с 0.3 сек при очевидно неодинаковых условиях это как раз вообще не интересно.
Почему-то мы сразу были убеждены, что упёрлись в производительность самого Postgres
при many-to-many превратились бы в смертельные джойны
Это довольно смелые утверждения.
Выглядит так, как будто теги здесь это неправильное решение, а нормализация ни при чем.
Если известно, что все операции это финансовые транзакции, то надо сделать merchant/provider/customer/country полями operation.
Если не все, то либо делаем поля null, либо отдельные таблицы для каждого типа операции со своим набором полей.
У вас получился EAV со всеми его недостатками. Причем value обычно делают в той таблице, которая у вас называется operation_tag. Отсутствие избыточности в результате нормализации лишь кажется на первый взгляд, на самом деле она ушла в таблицу tag, где теперь находятся все возможные комбинации ('merchant', merchant_id), ('customer', customer_id), ..., то есть значение 'merchant' повторяется для многих строк таблицы. Логично его вынести в отдельную таблицу с id, тогда можно будет указывать этот id в operation_tag, и там же рядом писать value. Так было бы меньше джойнов.
С JSON у вас получился видоизмененный вариант "делаем поля null". Раз в операции вряд ли могут быть указаны 2 разных customer_id, то нет смысла делать массив значений {key: value}, можно сделать один объект {key1: value1, key2: value2}. Или можно сделать не JSON-объект с полями, а нормальные поля.
В таблице tag, name и value образуют очевидный естественный первичный ключ. Зачем там tag_id? Убираем, первичным ключом делаем (name, value). Тогда operation_tag должна ссылаться на тег по (name, value). Как-то странно, получается дублирование информации зачем-то. Выкидываем таблицу tag за ненадобностью.
И вот, не отходя далеко от вашего EAV, выбираем нужные строки за 20 мс: https://gist.github.com/portnov/0f8ec7c651316b5d5d1deffd2f14a2bf .
Но EAV обычно применяются во всяких DWH или OLAP-системах. А у вас, судя по упоминанию каких-то лимитов — OLTP. Для OLTP обычно делают "нормальную" нормализацию: из operation ссылки на provider, merchant, customer и всё что надо. И вот уже селектим за 4 мс: https://gist.github.com/portnov/dbc3fc94e69fc72f0bf70d3976e92052.
Меня вообще забавляет свойство современных разработчиков, не разобравшись как следует с решением и не вникая в теорию, одним махом переехать на другую платформу или внести в проект ещё одну тяжёлую зависимость. У меня один разраб топил за редис как средство оптимизации хреново написанных запросов. В итоге был послан и нагружен дополнительно теорией и переписыванием тех самых запросов.
Честно говоря, вообще непонятно зачем вам здесь many-to-many
У вас в одной и той же операции несколько мерчантов или покупателей участвует? У покупателя в одной операции сразу несколько карт? Или покупатель активно путешествует и одну и ту же операцию сразу из нескольких стран совершает?)
Many to many отражает связь "множество операций ко множеству тегов",
У покупателя в одной операции сразу несколько карт?
В одной операции несколько тегов, среди которых есть карта
покупатель активно путешествует и одну и ту же операцию сразу из нескольких стран совершает?
Совершает множество операций, у всех них будет множество тегов, среди которых есть страна
Всё это и есть Many to many
Не понял зачем присоединять одну и ту же таблицу несколько раз, если это можно было сделать один раз. Даже без использования расширенных возможностей PostgreSQL.
Если записать тот же самый запрос по другому, то становится виднее "почему так".
SELECT SUM(o.amount)
FROM operation o
INNER JOIN operation_tag ot1 ON (ot1.operation_id = o.operation_id )
INNER JOIN operation_tag ot2 ON (ot2.operation_id = o.operation_id )
INNER JOIN operation_tag ot3 ON (ot3.operation_id = o.operation_id )
INNER JOIN tags t1 ON (t1.tag_id = ot1.tag_id )
INNER JOIN tags t2 ON (t2.tag_id = ot2.tag_id )
INNER JOIN tags t3 ON (t3.tag_id = ot3.tag_id )
WHERE (1=1) AND (o.created_at BETWEEN '2023-08-01' AND '2023-08-31')
AND (t1.name = 'provider' ) AND (t1.value = 'someProvider' )
AND (t2.name = 'merchant' ) AND (t2.value = 'someMerchant' )
AND (t3.name = 'customer_id' ) AND (t3.value = 'someUniqueCustomerId')
Мда... форматирование в каментах неудачное...
Все равно не понятно почему нельзя так:
SELECT SUM(o.amount)
FROM operation o
JOIN operation_tag ot ON ot.operation_id = o.operation_id
JOIN tags t ON t.tag_id = ot.tag_id
WHERE o.created_at BETWEEN '2023-08-01' AND '2023-08-31'
AND (
( t.name = 'provider' AND t.value= 'someProvider')
OR ( t.name = 'merchant' AND t.value= 'someMerchant')
OR ( t.name = 'customer_id' AND t.value= 'someUniqueCustomerId') );
Нужно найти операции, содержащие все 3 тега, поэтому так не получится.
А если бы был поиск по любому из тегов - то да, OR подошёл бы
Я предполагал, что к любой записи обязательно привязаны все аналитики. Но если это не так, то:
SELECT SUM(o.amount)
FROM operation o
JOIN LATERAL (
SELECT COUNT(1) AS cnt
FROM operation_tag ot
JOIN tags t ON t.tag_id = ot.tag_id
AND (
( t.name = 'provider' AND t.value = 'someProvider')
OR ( t.name = 'merchant' AND t.value = 'someMerchant')
OR ( t.name = 'customer_id' AND t.value = 'someUniqueCustomerId') )
WHERE ot.operation_id = o.operation_id ) ON cnt = 3
WHERE o.created_at BETWEEN '2023-08-01' AND '2023-08-31';Автор в личке ответил, что он скорее имел ввиду особенности Symphony (с которыми я не знаком) и что для описанного случая ORM генерит именно такие модели и именно такие запросы. Но формулировки получились слишком обобщающие, потому и возникают вопросы и к толпе джойнов и в целом к решению. Статья скорее про Symphony, чем про Postgresql и М2М.
А так - да, задачка уровня sqlacademy про поиск записей, у которых есть все теги. Возможно дистинкта не хватает, т.к. там уникальности по тегу на операцию вроде не навешано.
Вы вообще нормальн… нормализованный??