Всем привет.
Я люблю базы данных, люблю строить запросы, люблю проектировать БД. Раскладывать по полочка, систематизировать это моё любимое занятие. Конечно первые годы я проектировал таблицы БД как меня научили в ВУЗе - каждому свойству отдельная колонка.
Но этот подход отстал от жизни, то есть он удобен если ваша бизнес логика работает на хранимых процедурах. Тогда действительно, записи таблицы это готовые объекты вашей бизнес логики.
Но актуальных подход это разделение ответственности за обработку данных между сервером баз данных и сервером приложений. Сервер баз данных предоставляет данные, сервер приложений их обрабатывает.
Речь конечно об Online Transaction Processing (OLTP). Когда нам надо получить данные по одной сущности, например, показать профиль пользователя, или показать товарные позиции определённого заказа.
Ниже я расскажу о продвинутом способе хранения данных.
Продвинутый формат хранения данных
Хотя и принято говорить о хранении данных, но на самом деле никому не надо хранить данные, всем надо читать данные, главное назначение сервера баз данных это предоставление данных по запросу.
То есть при проектировании БД мы должны исходить из требования быстрой выдачи необходимого объёма данных. Мы должны обеспечить быстрый поиск по заданным условиям.
Что помогает поиску ? Индексы. То есть наша таблица должна иметь индекс для быстрого поиска и собственно данные. Таблица должна состоять из колонок индекса и колонки с собственно данными. Для согласованности данных можно добавить колонки внешнего ключа.
Пример реализации
Допустим перед нами стоит задача обрабатывать некие документы. В документе важен его статус, для быстрого поиска мы делаем колонку статуса, конечно статусы у нас могут быть не какие попало, возможные значения ограничены справочником, поэтому колонка будет внешним ключом на справочник статусов, создаём колонку status_id.
Каждый документ имеет уникальный номер, создаём колонку doc_num, делаем по ней уникальный индекс.
Для данных документа делаем колонку data, тип данных строка, можно использовать специализированны формат СУБД.
Что у нас получилось
create table document
(
id integer not null
constraint document_pk
primary key,
doc_num text not null,
status_id integer
constraint document_status_id_fk
references status,
data jsonb
);
create unique index document_doc_num_uindex
on public.document (doc_num);
create index document_status_index
on public.document (status);Откуда растут уши
К такой схеме хранения данных я пришёл не сам, эту идею я взял у Филиппа Дельгядо. Вы можете посмотреть его доклады на конференциях с тайм кодами: раз, два.
Или весь доклад целиком:
В видео идея подробно излагается со всеми нюансами граблей на которые вы можете наступить, я в своей статье излагаю только основные моменты.
Способ работы с СУБД
Как правило, вам не надо делать выборки с условием отбора по языку интерфейса в профиле пользователя, вам надо только отобразить интерфейс с учётом настроек профиля. Или как правило, вам не надо делать поиск по заказам пользователя, что бы показать заказы в которых было определённое количество определённого товара, вам надо показать список заказов.
Запросы с выборками пользователей по языку интерфейса или со средним чеком по заказу делают для аналитики в системах Online Analytical Processing (OLAP), это их хлеб.
Как правило Online Transaction Processing (OLTP) занимается атомарной обработкой одной записи. Вам надо получить данные от пользователя, записать их в таблицу. На следующем этапе вы эти данные прочитаете, измените и снова запишите в эту же таблицу и скорей всего создадите связанные записи в других таблицах. Такая работа с записью будет продолжаться до тех пор пока бизнес процесс не будет полностью выполнен.
После этого вы ни когда к этой записи не вернётесь, вы выгрузите её в OLAP и забудете о ней навсегда.
При работе с СУБД через ORM происходит чтение всех колонок, ни кто не пишет в коде "дай мне колонку номер документа и дату документа", объект считывается целиком. Поэтому нет смысла разбивать свойства сущности по отдельным колонкам. Всё равно они будут прочитаны все. При разбиении информации на отдельные колонки и при записи в одну единственную колонку объем данных переданных от СУБД к приложению не измениться.
Получить JSON и превратить его в объект это элементарная задача. Обратная задача, получить JSON представление для объекта, это тоже элементарная задача.
Появляется нюанс с записью колонок индекса, когда мы сериализировали наш объект для записи в БД, мы также должны записать новые значения для колонок индекса, то есть если мы создали индекс со статусом документа, то при сохранении документа мы не только записываем новое значение в колонку data, но так же мы записываем новое значение в колонку status.
Всё это автоматизируется на уровне ORM.
Наследуемся от класса модели данных, добавляем абстрактные методы для сериализации и десериализации, теперь все классы наших моделей наследуем от нашего кастомного класса.
Для каждой нашей модели пишем реализацию сериализации с обновлением значений в колонках индекса. Это методы на несколько строк кода, ни чего сложного.
Преимущества
Какие преимущества даёт подход с единой колонкой для всех данных ?
При добавлении нового свойства у сущности, вам не надо изменять схему данных, вам не надо писать миграцию.
Теперь миграции нужны только для создания таблицы, и этот код упрощается до пары колонок.
Меньше миграций - проще деплой.
Ни кому в голову не придёт строить аналитические отчёты на СУБД Приложения, для этого будет использоваться OLAP.
Недостатки
Миграции по прежнему нужны для создания новых индексов и внешних ключей.
Для каждой модели надо писать код актуализации значений в колонках индекса.
Когда следует использовать такой подход ?
Мой ответ - всегда.
Всё что для этого надо - это преодолеть инертность своего мышления. Упрощения деплоя и сокращение количества миграций это серьёзное преимущество для по настоящему промышленных систем.
Если вы пишите домашний проект или проверяете гипотезу, то конечно вам не надо тратить время на пляски с бубном вокруг ORM, пусть всё мапиться один в один.
Если вы используете автогенерацию фронт-энда из схемы БД (пример Craftable — Laravel CRUD generator), то такой вариант с записью данных в одну колонку вам помешает.
Во всех других случаях, когда вы ведёте разработку не один год и не одной командой разработчиков - такой подход к работе с данными - облегчит жизнь вам и вашим разработчикам.
Пользуйтесь с удовольствием.
Комментарии (46)
napa3um
19.04.2022 03:18+1Так. Мой комментарий. Это про колоночное хранение данных? Иногда полезно. Конечно, не всегда. Можно использовать реляционные СУБД. Можно специализированные. Сразу. Задачи разные.
(Простите меня за мою пародию, но читать набор обрывочных тегов вместо текста очень больно. Это твиттер такое с людьми делает?)
boblenin
19.04.2022 03:26+2При добавлении нового свойства у сущности, вам не надо изменять схему данных, вам не надо писать миграцию.
Вам не нужно писать миграцию для схемы таблиц, но ваш код на сервере приложений должен понимать как данные, в которых приходит JSON со старой схемой, так и данные с новой.
Сам таким способом пользовался. Иногда задачу упрощает.
hellamps
19.04.2022 04:08+6И тут Коля придумал NoSql... Только немножко "в доморощенном" формате...
Попробуйте какую MongoDB, если вам удобно джейсон. Ну и лучше в каком динамическом языке, а не джаве, чтобы ваш ORM не сломался на отсутствующем или наоборот появившемся проперти
randomsimplenumber
19.04.2022 07:34Mongodb настраивать нужно, а sql уже есть ;)
Если нужно только insert и иногда select *, то почему бы и нет.
hellamps
19.04.2022 14:53если нужен только insert, а иногда selsct* - тогда проще складывать json прямо файлами в s3 :)
funny_falcon
20.04.2022 11:29Раньше был аргумент про транзакции. Теперь в Mongo появились транзакции.
Остаётся более человеческий способ запросов с JOIN. Можно сказать, что document-oriented создавали, чтобы двойнов не делать. Но по факту, они всё ещё нужны.
FanatPHP
20.04.2022 14:45+1"Аргумент про транзакции", серьёзно? Насколько я знаю, раньше был аргумент, что Монга — это мусорная куча, в которой чем ковыряться, лучше сразу отстрелить себе ногу. По-моему, отсутствие транзакций — это последняя в ряду множества причин, по которым надо менять эту хайпоБД на нормальную базу данных, пока не стало слишком поздно.
funny_falcon
20.04.2022 15:05Скажем так: я не был любителем монги, пока не попал в крупный проект к умным людям.
Конечно, толку от schema-less я так и не увидел, ибо схема очевидно переехала в приложение.
Но с операционной точки зрения Монга - вполне зрелое решение. По крайней мере, репликация и шардирования там работают из коробки, и действительно обеспечивают масштабируемость и отказоустойчивость.
FanatPHP
20.04.2022 16:19Не выглядит серьезным аргументом, если честно. С операционной т.з. и нормальные СУБД вполне зрелые решения. Но кроме этого в них есть еще и функциональность, позволяющая реализовывать хранение данных и их выборку. Возможно, я не знаю юзкейса в который Монга вписывается идеально, но как основная БД приложения это явное фиаско, которое случится рано или поздно. Причем чем позже, тем громче бабахнет. И это при том, что сейчас, при умении всех популярных БД работать с джейсон-полями, кобминированная схема выглядит куда как привлекательнее чисто "кучомусорной" БД.
funny_falcon
20.04.2022 16:23Да, подскажите, какую базу использовать, чтобы был автоматический фейловер с автоматическим догоном восстановившегося мастера + шардинг с решардингом + апгрейд/даунгрейд без даунтайма. И чтобы индексы были, и запись durable. И всё это в коробке, без third-party тулзов.
FanatPHP
20.04.2022 17:48Я думаю, реляционность БД всё же перевешивает возможность получить "всё из коробки". Которая является достоинством разве только в случае, когда всем рулит один ванька-тыжпрограммист, который и фронт и бэк и датабаза. Но как-то я слабо представляю его в ситуации "автоматический фейловер с автоматическим догоном восстановившегося мастера + шардинг с решардингом".
funny_falcon
20.04.2022 18:48Ну вот был стартап из 5-7 разрабов-они-же-админов. Им было удобнее «всё из коробки». А приложения они умели писать в любом стиле, в том числе и не под реляционку. Соответственно, выбрали Монгу.
Это было задолго до меня. Я присоединился, когда их было ~30 человек, и ушёл, когда было больше 150ти. Сколько сейчас там работает, не рискну гадать. Наверное сотни три или четыре.
(Почему ушёл? Хотел работать в другом месте. Но о работе там до сих пор тёплые воспоминания.)
Пока я там был (3 года) монга в бою обновилась с 3.6 до 4.4. Конечно, проблемы встречались, особенно на 3.6. Два раза был даунтайм потому, что наступали на какие-то не очевидные костыли (которые, к слову, в более поздних версиях монги были заменены на нормальные решения). Один раз даунтайм из-за человеческого фактора (стопнули две реплики из трёх).
Но ни разу не пришлось восстанавливать из бэкапа. Реплики всегда надёжно хранили самое свежее состояние.
За это время использование монги выросло от одного нешардированного репликасета до десятка кластеров с полусотней шардов и двумя сотнями инстансев.
О безостановочном, прозрачном для приложения обновлении версий монги (и не ночью, не рано утром, а в рабочее время) можно вообще поэмы сочинять. И обновление железа под монгой точно так же безболезненно происходит.
Потому, если вам очень важна «реляционная модель», используйте, что хотите. (Тем более, что уже существуют TiDB, CockroachDB, YugaByte, и вышла YDB).
А если у вас прямые руки и нужно рубить капусту, то монга вам помехой не будет.
FanatPHP
20.04.2022 19:17Извините, я не верю в стартапы, в которых "5-7 разрабов-они-же-админов" и уже требуется "автоматический фейловер с автоматическим догоном восстановившегося мастера + шардинг с решардингом".
"ни разу не пришлось восстанавливать из бэкапа" — я извиняюсь, но встретить проект, в котором реально приходилось восстанавливать боевую базу из бэкапа — это надо очень сильно постараться. Из всех мест, где я работал, такое случилось только однажды. Когда была полностью замена команда админов, и новые настолько неловко начали рулить, что таки умудрились уронить мастер со слейвом так, что пришлось таки накатывать бэкап. Больше нигде. Всё просто работало, годами.
По поводу рубить капусту — с одной стороны да, наш руководитель авиамодельного кружка любил говорить, что с хорошим движком и табуретка полетит. Вопрос в целесообразности вбухивания такого количества усилий на подъем в воздух не приспособленного к полету агрегата. С другой — даже при всех потраченных усилиях, табуретка и будет летать как табуретка.
Ладно, это уже пошли идеологические разногласия и дискуссия с открытым концом. Let's agree to disagree.
hello_my_name_is_dany
19.04.2022 05:56+1С таким подходом вам понравится любая NoSQL СУБД, например, FoundationDB, где с ключами можно творить адовые вещи.
Но, как и писали выше, UPDATE будет ужасно тяжелым и про атомарные операции можно забыть
UPDATE wallets SET balance = balance + 1 WHERE id = :idhellamps
19.04.2022 06:42не работал с фондейшн, но в той де монге есть атомарные инкременты и проч, так что вполне можно и троттлинг какой запилить и некое подобие биллинга
hello_my_name_is_dany
19.04.2022 16:46Об этом и речь, что лучше подойдут некоторые NoSQL СУБД, чем реляционные. В случае реляционной СУБД на таких UPDATE придётся гарантировать атомарность или как-то через хранимые процедуры, или на уровне приложения.
Tzimie
19.04.2022 07:29+3А уж писать отчёты или OLAP на системе с таким дизайном это вообще будет BDSM
FanatPHP
19.04.2022 08:06+14При добавлении нового свойства у сущности, вам не надо изменять схему данных, вам не надо писать миграцию.
Это какой-то ужас, schemaless DB, Монга номер два. "Давайте выкинем всё, что дала программистская мысль за последние 50 лет и будем следить за целостностью данных в БД сами!". И в итоге начнётся то же самый кордебалет, что и в монге — одно- двухбуквенные имена полей для экономии памяти, отдельная БД чтобы хранить схему — вот это вот всё.
Ну и отдельно доставляют заявления вида "2 индексов на таблицу хватит всем".
Я понимаю, когда эту чушь пишут нубы на Тостере — растущим организмам свойственно сочетание ограниченного кругозора с любовью к экспериментам. Но вот выдавать эти потуги на Хабр явно не стоило.
iMedved2009
19.04.2022 08:37+3На самом деле если посмотреть прежние статьи автора там прям видна эволюция, сначала появилась статья о нелюбви к миграциям, потом работа с EAV, а теперь цивилизация русские открыла для себя json поля. А там и миграций не надо, и геммороя как с EAV нет, лафа.
З.Ы. На самом деле насколько я помню, идея а запихнем все в одно поле, с ним будем работать и будет все в шоколаде, пробовалась еще в SQL Server когда на волне хайпа "XML всему голова", там появились XML поля.
dimsog
19.04.2022 09:51+3Когда следует использовать такой подход ?
Мой ответ - всегда.
Обожаю такие категоричные заявления.
Это как заявление - вы должны всегда писать красивый код (где-то я слышал это).
Но правда в том, что быстрый код не всегда является красивым.
Так и здесь.
dopusteam
19.04.2022 10:40Мой ответ - всегда.
Ну так, судя по статье, у подхода то недостатков раз-два и обчёлся, зато достоинств сколько
aceofspades88
19.04.2022 10:51+2Сколько головняка потом эти поделия перекладывать в реляционный вид после вот таких "новаторских" подходов
Karbofos42
19.04.2022 11:48+3Ну, да. Миграции совсем не нужны. Хранились какие-нибудь значения в километрах. Тут оказалось, что нужно хранить в метрах и крутись как хочешь. Либо появляются дублирующие атрибуты: value и value_meter и в коде потом разбирайся что задано в конкретной записи. Либо какая-то версионность в json заносится и в коде разные типы объектов нужно обрабатывать и конвертировать. Либо таки писать миграцию, которая все json в БД перелопачивать будет.
Миграции не нужны только если добавляются новые атрибуты или удаляются старые с потерей данных, а вот изменение существующих - это та ещё песня.
DrunkBear
19.04.2022 15:28Мне рассказывали про реализацию такого подхода - в итоге получилась табличка в 11000+ колонок и 14тб+ размера, с колонками в стиле value, value_meter, value_ft, value_miles, value_new и сложной логикой, которая сводит всё это в olap-куб.
Есть 3 проблемы - select * перегружает всё, никто уже не помнит, зачем половина колонок и нет идей, как это чудовище транформировать/мигрировать.
Areso
19.04.2022 21:27Кажется, что у вас на проекте отсутствует вменяемый техлид, архитектор или кто-то навроде того.
Слона нужно есть по кусочкам. Построить модель данных, посмотреть, какие столбцы реально используются, какие нет. Какие не используются - пометить как удаленные. И далее и тому подобное.
Нет нерешаемых проблем, есть
говпроблемы, за которые не хотят браться.
jakobz
20.04.2022 13:41Там у тебя в JSON начнут появляться всякие locationID. И дальше будут проблемы с JOIN-ами, например.
Потом «все равно читаем весь документ» - тоже неправильная вводная. Например, может быть типичная схема с каталогом/списком (много фильтров, не все поля показываются) и страничкой документа (нужны все поля).
Короче, подход имеет смысл в каких-то случаях - не зря в той же пострге приделали такую хорошую поддержку jsonb. Но далеко не прям «всегда». Мы вот недавно буквально раскладывали такой JSON на десяток таблиц - потому что потребовалось работать с его кусочками отдельно.
iMedved2009
А где вариант ответа - подобное решение не является серебряной пулей и его надо использовать вдумчиво и там где надо, а где не надо не использовать?
TOAST
SbWereWolf Автор
Если у вас 100 колонок и в них 100к теста, то если вы упакуете эти 100 колонок в одну, то там будут всё те же 100к текста (плюс разделители), и на ваш запрос СУБД вам пришлёт всё теже 100к текста.
При чём тут способ физической записи данных ? при чём тут Postgre ? других СУБД не бывает ? SQLight слышали ? Oracle ? MySql ? SQL Server ? давайте из них выпишите ограничения :)) Ваше замечание вызывает во мне недоумение.
От Бартунова последний доклад по JSOB слушали ? про TOAST там тоже есть, очень рекомендую https://youtu.be/CPoNZRpcHf4.
Моя статья для людей которые привыкли думать до того как начнут что то делать.
Извините что не учёл ваши персональные особенности.
Areso
А зачем вам перезаписывать всю строку?
update students
set name="newname"
where id=3;
будет равноценно patch запросу, вместо того, чтобы прочитать весь объект студента, а потом положить весь измененный JSON обратно (get/post & put).
BugM
Это еще и не атомарно получается. Когда данные потеряются только вопрос времени. Если без всяких жутких блокировок на чтение.
edogs
У него в статье там эпически это объяснено
В принципе это было нормой, лет 15 назад, наверное. Может и до сих пор есть, где-нибудь в битриксе или чем-нибудь таком.
Но мы давно уже не видели реализаций ORM, которые таскали бы чохом всю кучу. По нормальному вытаскивают только нужные данные, потому что, блин, это хоть и 2022 год, но ресурсы (ширина канала, процессор, память...) не бесконечные.
lightman
И тут Entity Framework входит в чат. Не, я в курсе, что там можно вытаскивать отдельные поля через Select, но "дефолтный" способ работы с ним - мапить выборку на модель, т.е. делать select *
iamkisly
Верно только если модель имплементирует все столбцы таблицы. EF знает о свойствах модели, и перечисляет их в селекте. Но у тебя могу быть несколько моделей на одну и ту же таблицу, каждая со своим набором свойств (и селект в базу заберет только их).. И это не считая различных вариантов наследований.
Соответственно подход автора верен только в случае, если мы используем БД, как умную, но глупую систему хранения.. потому что не все БД являются обьектно ориентированными, а значит во множестве случаев никаких тебе вью, глобального анализа по отдельным полям и тд. Простыми словами, подходит только тогда когда внутри БД мы не хотим узнать что же находится внутри этого документа.
iMedved2009
Ну если говорить про postgres там в любом случае будет новая строка, со всеми вытекающими
https://en.wikibooks.org/wiki/PostgreSQL/MVCC
iMedved2009
А я вот начинаю вспоминать ваши персональные особенности, при любой критике или возражениях вы начинаете хамить и сомневаться в способностях оппонента. Если вы не хотите возражений и критики - заведите свой бложик, закройте доступ для всех кроме родных и близких и радуйтесь. Но в общественном месте будьте так любезны вести себя прилично.
Скриншот
В вашем же видео. Только там Панченко. Прочитайте последнюю строчку вдумчиво
Просто с Postgres я работаю постоянно, а с Мускулом, SQL Server давно не работал. Но там есть аналоги - забыл как называется. Привел тот пример что на языке вертелся
SbWereWolf Автор
Я изложил идею, привел пример - профиль пользователя, товарные позиции в заказе. Откуда там быть килобайтам текста ?
Если есть такие большие поля, то их надо хранить в отдельной связанной табличке, о типе данных BLOB / CLOB я думаю вы слышали.
Я об этом не написал ? Вы считаете в статье я должен вообще все возможные случаи проектирования таблицы рассмотреть ?
Вы задались целью привести контрпример, поздравляю вам это удалось.
iMedved2009
Ой, а в статье немножко по-другому. Ну вот если бы вы здесь написали такая вещь имеет место быть в каких то местах, я бы слова не сказал.
Мне не нужны поздравления. Лучше хамить перестаньте
SbWereWolf Автор
я пишу статьи для разумных людей, писать 100500 дисклаймеров что бы ни один чудак мне слова не сказал, много чести.
iMedved2009
Ну если вы пишете какую то глупость с криком "ура я нашел серебряную пулю - используем всегда" будете получать возражения и минусы. Не стоит на них реагировать хамством, надо говорить спасибо.
Hardcoin
Вы не описали в статье ещё одно преимущество вашего подхода - когда вас попросят сделать в админке поиск пользователя по email с выпадающим списком подсказок, вы сможете запросить на эту задачу в пять раз больше времени и денег и это будет честно - ведь задача на самом деле стала больше размером.
randomsimplenumber
Задача не стала в 5 раз больше. Нужно просто больше памяти и процессор быстрее ;)
SbWereWolf Автор
Если какое то свойство сущности нужно для поиска то делаем колонку с индексом по этому свойству, вы статью читали, а смысл не уловили.
Попробуйте еще раз перечитать.
Hardcoin
Почему же не уловил? Уловил. Вы делаете новую колонку, потом заполняете её по данным из колонки data, потом меняете существующий код, работающий с базой, что бы он пользовался новой колонкой, а не data. После этого задача решается классическим способом.
Верно? Или вы по-другому видите решение?
funny_falcon
Ну вообще, если поиск не частый, то в колонку можно и не выносить: PostgreSQL умеет индексы по выражениям.
Да, будет подтормаживать. Да, PostgreSQL специфичное.