Здравствуйте, уважаемые подписчики и гости канала!
Для начала - в Postgres есть два типа json и jsonb (более новый).
Так вот, про json можете в принципе забыть и всегда использовать jsonb, так как он хранится на диске компактнее, является comparable типом, что позволяет его сравнивать и даже сортировать по это полю, хотя последнее в прикладных задачах вряд ли пригодится. Для тех, кто не знаком с этими типами данных сразу скажу, что это не то же самое, что сделать поле типа text. По данным json-полям фильтровать строки, для json можно ставить индексы на внутренности данных, прямо как в MongoDB.
Кстати, если ты не знаешь что такое и для чего существуют оконные функции, то читай другую мою статью про оконные функции в Postgres.
Хранение данных на диске
Тип jsonb как и json хранятся не в таблице, а в TOAST, что позволяет pg не читать эти данные, если вы к ним не обращаетесь, но имеет дополнительные накладные расходы, так как TOAST - отдельные файлы и за ними pg должен присматривать.
Мы в проектах активно используем jsonb для хранения неструктурированной инфо для каких-то записей. Прямо скажем она, конечно, скорее структурирована на самом деле и я бы с радостью как-то по простому схему описал, но кастомные типы городить нам не улыбается.
Итак, как я уже написал, у нас есть таблицы, где есть поле например info или Data и там по каким-то договоренностям лежат данные. Мы по ним фильтруем и счастливы, если у вас в таблице до 100 000 строк, то берите и не думайте даже — это реально пушка!
Вот пример запроса, который выбирает аккаунты, а которых оплачен текущий тариф. На выход общее количество и примеры названий - 0.09 сек, таблица как и БД под обычной нагрузкой, в таблице около 50 тыс. записей.
Можно очень просто поискать вхождение в список:
Тут часть запроса из секции WHERE для фильтрации данных по user_id. Это про случай, если в account.info->'user_id' лежит что-то такое [1,2,3,4]
За все надо платить
Как всегда, есть нюансы, поскольку json в этом моем случае хранится прямо в основной таблице, то операции UPDATE копируют и эти данные тоже - у нас же транзакции и все дела. Во всяком случае так происходит в PG 9.5. Поэтому я НЕ рекомендую запихивать мегабайтные данные в часто обновляемые таблицы — создайте отдельную, я описал случай, когда у вас 10-20 ключей и небольшие значения там, это норм работает. Тут вы сами смотрите, я просто предлагаю рабочий вариант.
Кстати, в PostgreSQL есть много прикольных фишек и кроме json, например у меня есть статейка про триггерные функции.
Функции агрегации json
Далее про агрегацию с использованием json. Знаю есть сторонники и противники полноценной работы с БД, я сторонник — не люблю вытаскивать 100 записей, чтобы их потом сгруппировать в коде ЯП, но ситуации разные бывают, думаю под нагрузкой, с кэшами и пр. там порой лучше, но мы не про этот случай.
Итак, задача: выбрать аккаунты с активными тарифами и вернуть json, где в ключе будет тип аккаунта, а в значении — список id аккаунтов. Вуаля - 0.09 сек.
Подборка видео всех видео по PG - https://dzen.ru/suite/37b67ffa-176d-493a-b1a8-4762f79e3753
Массовые операции
Я в свое время нашел отличный пример, который экономит силы, нервы и время, в том числе и базе данных.
:newval - это prepared statement ключ с json строкой.
:ex_access_id - тоже prepared statement параметр. На него забейте просто.
А работает так — на python-е мы готовим List из dict-ов и делаем ему json.dumps() b отправляем в БД. Там через конструкцию as x(kind text, размалываем во временную таблицу в памяти и посылаем на вход в INSERT, который делает DO UPDATE в случае конфликта.
Получается эдакий UPSERT, который работает быстрее, чем многое, что вы можете себе представить. А еще это удобно в принципе.
Список функций для изучения
Ребята из Postgres PRO перевели вам доку, пользуйтесь:
Мой ТОП функций для работы с json
- jsonb_build_object
- jsonb_to_recordset
- jsonb_agg
- jsonb_object_agg
Ну а to_json, jsonb_each_text, jsonb_array_length, jsonb_strip_nulls и пр. Редко или никогда.
Короче json в Postgres развивается, появляются jsonpath и пр., посматривайте — это может помочь вам не переписывать что-то, а просто и достаточно быстро решить реальную проблему и спокойно без спешки думать над «правильным» решением.
А на этом всё, спасибо за внимание!
Подписывайтесь на канал, ставьте лайки, оставляйте комментарии - это помогает продвижению в Дзене.
Кроме этого:
Подписывайтесь в Telegram: https://t.me/lets_goto_it
#postgres #json #python #разработкасайтов #совет профи #dba #базы данных #программирование