mayton2019

Какой формат данных (JSON или JSONB) лучше использовать в этой ситуации?

Похоже автор занялся любимой задачей скучающих разработчиков. А именно - ПРЕЖДЕВРЕМЕННОЙ оптимизацией.

JSON и JSONB возникли например из задачи хранения в БД ДОКУМЕНТОВ. Документ - предполагает специфический юзкейс. Например однократное создание и редкую модификацию. И частое чтение с поиском по текстовому индексу например.

Является ли задача автора - подходящей под данный use-case? Чорт его знает. Я-бы сказал что пока нет. Все таки комментарии пользователя это такие себе... частые модификации документа которых хотелось-бы избежать.

И вообще пока не будет создано 2 макета или 2 proof-of-concept с бенчмарками - мы не можем точно сказать что лучше.

Сам-же Бартунов например в одном из своих докладов рассказывал что сама идея затащить в PG документы возникла из идеи работать с properties в одном поле. С такой себе неструктурированной информацией. А сама задача вознила из прикладной проблемы в дизайне базы для системы образования. Им нужно было хранить в строке неспецифицированный лист атрибутов. Это еще не JSON но уже дедушка его. Вот его так порешали. Это похоже на кейс автора? Я-бы сказал что далеко нет.

Вообще чтоб доказать или опровергнуть огульный тезис о JSON-ификации я-бы довел постановку до абсурда. Зачем мы будем трекать комментарии в JSON. Давайте и посты туда-же. И странички. И вообще всю модель положим в 1 документ JSON. Каково а? У нас будет база с 1 единственной JSON строкой которая хранит в себе всё. Технологично? Да. И не запрещается.

Вот как-то так.

на сайте есть различные небольшие блоки (о нас и тд). Встречаются они единожды, но должны редактироваться менеджером -> храниться в базе.

Это различные небольшие блоки по смыслу ближе к обычным файлам в файловой системе.
Файловая система - наиболее гибкая и толерантная в данном варианте использования.

Но поскольку у вас стоит такое требование как хранить в базе - отсюда и возникает вопрос.
А с каким ключом туда это все ложить? Ключ должен быть осмысленным настолько чтобы эти
блоки потом найти и не потерять. И это даже не с нормализацией а скорее просто с особенностями
подобных хранилищ. Key-Value очень быстро захламляется и превращается в свалку или кладбище
документов без внятного понимания нужны они или нет.

Если url будет ключом - то это хорошо.

Перетащил это всё на MongoDB с такой структурой:

Справочники остались в MySQL.
.......
Какие есть идеи?

Думаю попробовать перенести структуру на PostgreSQL аналогично MongoDB и использовать

Дружище. Так жеж не делается в мире Документно-ориентированных БД! В монге ты делаешь не таблицы. А хранилища документов. Где каждый документ - самодостаточен и полностью хранит в себе всю информацию. Грубо говоря никаких СПРАВОЧНИКОВ и СВЯЗНЫХ таблиц у тебя быть не должно. И нельзя джойнить документы. И нельзя джойнить документы с таблицами MySQL.

Почитай про модель АГРЕГАТОВ в противовес реляционной модели. Это можно найти в книжках типа NoSQL и еще я находил это в доках по Cassandra.

Это sequence. Последовательность. Это - как туалетная бумага. Использованные номера можно выкинуть. Зачем их повторно брать? У вас же нет желания из мусорного ведра тягать грязные бумажки?

А попробуй сделай так

create index name_idx on emails(name);

и повтори свой запрос еще разик.

В современных БД практически нет ограничения на длину строк varchar. А для блобов (BLOB) там специально созданы условия чтоб аттачить толстые файлы произвольного размера.

Когда-то давно реляционные БД ограничивали длину строки varchar. Это было связано с блочной организацией дисковой памяти. Например в Oracle varchar2 был максимум 4000 байт. Потом где-то с 12 релиза его можно растянуть до 32к. Ну а для XML/JSON типов там создается специальное поле которое является просто подтипом BLOB но имеет в виду что лежит например JSON. Констрейнт короче.

Есть специальные dbms такие как Mongo, CouchDb, Tarantool. Они специально создавались под JSON. Обратите на них внимание.

Для баз данных существует еще больше способов виртуализации. Кроме docker/kuber можно создавать много баз данных в одном и том-же хосте (pod). Но подумайте будет ли вам удобно бэкапировать такой будерброд. И не будет-ли проблем с безопасностью если кто-то будет создавать в одной базе public объекты.

NULL и 0 будут давать разный результат при подчете агрегации. Sum, Avg и прочие стат- функкии будут учитывать 0 и игнорировать NULL.

Вообще в реляционной алгебре правильно использовать NULL когда данных нету.

Еще для некоторых dbms (Oracle) Null не индексируется. Это экономит место в сегменте индекса и делает поиск более быстрым дла nullable колонок.

В принципе имеет. Почему нет? Но есть нюансы.

Вот самое слабое утверждение .

"регулярно" пересчитываем хэш найденных сущностей и сравниваем с хэшем сохраннённой sqlite

Регулярно это как? Каждую секунду? Или минуту. Вобщем тут нет единого правильного ответа. Нужно разговаривать с бизнесом и понимать где можне чем пожертвовать. Или точно знать что работаем с архивными данными. Следовательно кеш - вечен. Или работаем со справочником товаров которые обновляются раз в сутки. Тогда обновление или переасчет кеша можно просто привязать к старту системы. И каждый день перегружать ее.
Короче инвалидация кеша - это главная проблема в любых инициативах с кешом.

Далее объем. Надо быть очень удачливым или иметь очень строгое ТЗ чтобы гарантировать что кеш будет иметь разумный объем. Если кеша будет не хватать или он будет в ротации в памяти с другими кешами то вреда от него будет больше чем пользы.

Далее идеология. Может ваша система уже давно переросла реляционку (EAV как признак) и ей просто нужно волевое архитектурное решение. Затащите документную БД. Mongo, CouchDb. Может вам с ней будет проще работать. EAV никогда не была эффективной в смысле скорости.

JOIN - не плохой.

В эпоху соц-сетей (facebook e.t.c.) придумали подготавливать веб-контент на сервере для каждого пользователя персонально. Ваша лента новостей. Ваш landing. Статистика. Все это хранится в какой нибудь RocksDb (на самом деле я не знаю в какой просто для примера взял) и извлекается просто по ключу. Обновляются эти документы по событиям. Тоесть мессенджинг системы тоже нужны. LinkedIn был настолько озабочен этим вопросом что создал Kafka решая задачи своей соц-сети.

Все эти хитрости сделаны только для того чтобы убрать реляционные БД из стека веб-технологий и сделать отрисовку landing как можно более быстрой. И этой быстроты невозможно было-бы достичь если-бы делать select всех новостей где подписчик == вы и где еще и все 100500 новостей order by... короче поняли. Ну и join тоже убрать. В любой умной БД есть звезда или снежинка и отрисовать на экране звезду и снежинку без join невозможно. Вобщем join здесь не причем. А важно что - убрали стек одинаковых повторяющихся операций поиска-соединения-фильтрации-сортировки и заменили на извлечение документа из документной БД.

Про что ERP системы? Они - про управление ресурсами предприятия. Нужно ли вам в процессе управления осуществлять поиски и соединения по разным сущностям? Скорее всего - да. 99% что да.

NoSQL системы принципиально не поддерживают JOINS. Они разрабатывались для других моделей где отклик важен а JOIN не нужен. NoSQL системы не умеют делать эффективное индексирование не-ключевых полей. Фактически если вы хотите индекс - вам производитель NoSQL нагло предложит просто создать реплику всей коллекции данных только сделав искомые поля ключевыми. Как поддержать реплику и сколько ресурсов это будет стоить - отдельный вопрос. Возможно в некотором гипотетическом сценарии поддержка хорошо индексированной NOSQL системы будет стоить дороже чем реляционной.

Вот и думайте. NoSQL в ERP - это авантюра.

Если вам критично в какой-то части время вставки - подумайте про CQRS или создание очередей в некоторых наиболее горячих точках системы. Должен быть компромисс. Невозможно построить везде быструю систему.

В данной обобщенной постановке база вообще не нужна. У нас есть в одной руке уникальный индентификатор картинки. Например IMG0001. И мы по нему можем сформировать все 3 линки на дисковое хранилище с картинками.

Не совсем понятно о каких гарантиях тут идет речь. Когда база создается с нуля (таблицы еще пустые) то после создания таблиц (или во время создания) делаются contstraints которые описывают связи между таблицами и реакцию на delete/update. Типа

.... CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
  RESTRICT...;

И после этого из базы нельзя удалять родительские ключи пока на них есть дочерние связи.

Посмотри модель EAV. Может поможет.

Для оптимизации загрузки landing page, можно хранить копии этой картинки в уменьшенном виде. 2000х1000 => 1000x500 => 500x250 e.t.c. Так игроделы делают для быстрого рендеринга текстур. Mip-уровни кажется называется.
Обновлять эти mip уровни можно не спеша. Через минутку.

База mysql вам вобщем-то не нужна. Картинку можно в реальном времени править atomic операциями и хранить в raw формате. Не знаю делают ли такое на PHP. Возможно вам нужен рукастый програмист на C++ или еще на каком-то языке чтобы подружить PHP с С++ и сделать сервис для этой картинки.

Из того что автор описал - у меня возникает архитектурный вопрос. Кто придумал - использовать JSON для данных которые часто обновляются? Это - антипаттерн. Вы какое железо не поставте - у вас будет плохой перформанс.

Вам необходимо все данные которые имеют реляционный (точечный) доступ убрать из JSON. По сути - развалить его на модель EAV или что-то вроде того (Relational Data (RD)). Обновления станут быстрее. А для отчотности - если вам так уж важен JSON - отдельные джобы которые будут переливать данные из RD в JSON или формировать его на лету средствами клиента. В этом случае у вас не будет накладных расходов даже на хранение.

Архитектурно есть много путей решения этой задачи. Есть дорогие и коробочные. Oracle + GoldenGate.
Есть более дешевые (выше упомянуты). Все зависит от ваших амбиций. Вы хотите сами больше разрабатывать или просто брать готовые решения.

Теги можно хранить в поле типа JSONB