Это перевод статьи на сайте 2ndquadrant автора craig.ringer

В PostgreSQL есть поддержка json, но вам не стоит этим пользоваться в большинстве случаев. То же самое относится к hstore и к новому типу jsonb. Эти типы полезны там, где они нужны, но им не нужно отдавать предпочтения при моделировании данных в PostgreSQL, потому что это усложнит манипуляции с ними и написание запросов.

Некоторые читатели уже знакомы с этим антипаттерном. Сущность-атрибут-значение (САЗ) уже давно выступает в роли необходимого зла в схемах баз данных. Это то, против чего мы предостерегаем, и на что смотрим с пренебрежением, но к чему, тем не менее, прибегаем, когда реальность и требования бизнеса подразумевают, что не все может быть загнано в жестко определенные поля в тщательно смоделированных реляционных таблицах. Для тех, кто не в курсе, это дизайн схемы, где вы превращаете реляционную базу данных в некое подобие хранилища ключ/значение, используя таблицу со столбцами: id объекта(«сущности»), ключом («атрибутом») и значением. Запросы к такой структуре обычно включают многие-многие self join (соединение таблицы с самой собой).

JSON — это новый САЗ — замечательный инструмент, когда он вам дествительно нужен, но это не то, что вы должны применять при первом удобном случае.

(Многое из этого также верно для массивов в PostgreSQL, которые являются хорошим инструментом для построения запросов, но обычно неподходящим средством для хранения данных.)

Таблицы с JSON Blob (бинарный json)

Начиная с релиза PostgreSQL 9.3 и даже еще чаще с появления jsonb в 9.4, я всё чаще и чаще вижу вопросы на Stack Overflow, где люди спрашивают, как делать соединения join для json-объектов, как указывать в запросе вложенный ключ внутри какого-нибудь элемента массива и т.п. Это всё вполне разумные вещи, но когда у автора вопроса просят привести схему и пример данных, то они часто приводят что-то наподобие этого:

CREATE TABLE people(
    id serial primary key,
    data jsonb not null
);

INSERT INTO people(data) VALUES ($$
{
    "name": "Bob",
    "addresses": [
        {
            "street": "Centre",
            "streetnumber": 24,
            "town": "Thornlie",
            "state": "WesternAustralia",
            "country": "Australia"
        },
        {
            "street": "Example",
            "streetnumber": "4/311-313",
            "town": "Auckland",
            "country": "NewZealand"
        }
    ],
    "phonenumbers": [
        {
            "type": "mobile",
            "number": "12345678"
        }
    ]
}
$$);

INSERT INTO people(data) VALUES ($$
{
  "name": "Fred",
  "phonenumbers": [
    { "type": "mobile", "number": "12345678" }
  ]
}
$$);

INSERT INTO people(data) VALUES ($$
{
  "name": "John Doe"
}
$$);

и спрашивают: «Как мне найти людей с одинаковым номером телефона?»

Опытные пользователи реляционных баз данных уже сморщились от боли… но помните, что далеко не все приучены выворачивать ход своих мыслей так, чтоб проследить за внешними ключами назад от потомка к родителю и разложить составные сущности на отдельные части, чтобы привести к нормальной форме. Если об этом подумать, то реляционные базы данных странные.

Однако, больше всего возможностей PostgreSQL происходит как раз из этой реляционной модели. Когда вы сохраняете свои данные как двоичные объекты json, то лишаете планировщик запросов возможности принимать разумные решения, основываясь на табличной и столбцовой статистике, вы теряете большую часть возможностей индексации и разных типов поиска, и, вообще говоря, ограничиваете себя достаточно примитивными операциями. Чтобы сделать что-то интересное, скорее всего, придётся привлечь много self-join-ов и фильтров.

Причудливые запросы

Например, запрос, ищущий людей с совпадающими номерами телефонов может выглядеть так:

select 
  p1.id AS person1,
  p2.id AS person2,
  p1.data ->> 'name' AS "p1 name",
  p2.data ->> 'name' AS "p2 name",
  pns1 ->> 'type' AS "type", 
  pns1 ->> 'number' AS "number" 
from people p1 
  inner join people p2 
    on (p1.id > p2.id)
  cross join lateral jsonb_array_elements(p1.data -> 'phonenumbers') pns1
  inner join lateral jsonb_array_elements(p2.data -> 'phonenumbers') pns2 
    on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');

Услада для глаз, правда?

Нет ничего особо неправильного в поддержке json-функций и операторов, просто странно брать составные данные, подобные объекту, и запрашивать их, пользуясь реляционными join-ами и предикатами.

PostgreSQL хотя бы поддерживает LATERAL; без этого написание запроса превратилось бы в кошмар.

Так случилось, что для этого конкретного случая эквивалентный запрос к реляционной схеме выглядит не особо симпатичнее:

SELECT
  p1.id AS "person1",
  p2.id AS "person2",
  p1.name AS "p1 name",
  p2.name AS "p2 name",
  pns1.phoneno AS "p1 phone",
  pns2.phoneno AS "p2 phone"
FROM
  person p1
  INNER JOIN person p2 ON (p1.id < p2.id)
  INNER JOIN phone pns1 ON (p1.id = pns1.person_id)
  INNER JOIN phone pns2 ON (p2.id = pns2.person_id)
WHERE
  pns1."type" = pns2."type"
  AND pns1.phoneno = pns2.phoneno;

… но он, скорее всего, выполнится с индексом на (phone.»type», phone.phoneno) *намного* быстрее, чем запрос, основанный на json, и при этом возьмёт намного меньше данных с диска в процессе.

Нет фиксированной типизации данных

Основанный на json запрос, данный выше, также глючный, потому что сравнения для jsonb чувствительны к типам данных, так что jsonb-значения «12345678» и 12345678 не эквивалентны:

regress=> SELECT '12345678'::jsonb, '"12345678"'::jsonb, '12345678'::jsonb = '"12345678"'::jsonb AS "isequal";
  jsonb   |   jsonb    | isequal 
----------+------------+---------
 12345678 | "12345678" | f

так что это:

insert into people (data) values ('{"phonenumbers": [{"type":"mobile","number":12345678}]}');

не будет идентифицировано как дубликат, хотя должно бы.

Заметьте, что это, возможно, ограничение PostgreSQL, так как слабая типизация интерпретатора JavaScript означает, что при сравнении они будут равны:

> 12345678 == "12345678"
> true

Чтобы числа считались равными в запросе PostgreSQL, мы должны доработать наш запрос, так чтобы это:

on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');

использовало оператор ->> json-value-as-text:

on (pns1 -> 'type' = pns2 -> 'type' AND pns1 ->> 'number' = pns2 ->> 'number');

так как текстовая форма чисел одинакова.

Нет ограничений для защиты целостности (constraints)

Конечно, кто-то может сделать вставку:

insert into people (data) values ('{"phonenumbers": [{"type":"Mobile","number":"1234 5678"}]}');

…, которая не воспримется как дубликат из-за заглавной M и пробела в числе.

Потому что всё находится в двоичном объекте json, и нельзя просто добавить ограничение CHECK для значения, используйте типы ENUM или DOMAIN, создавайте управляемые по триггеру теневые нормализованные столбцы или применяйте любой из обычных подходов для управления нормализацией данных. Было бы необходимо писать json валидатор/трансформирующий триггер на процедурном языке типа  pl/v8 или проделать всё это в приложении.

Так когда следует использовать json?

Читая вышенаписанное, вы могли бы подумать, что я против использования json-типов в базе денных. Это вовсе не так. Они часто лучше, чем САЗ, когда у вас есть данные с динамическими столбцами, которые просто не впишутся в традиционную реляционную модель. Или, как, я видел, люди пытались в прошлом, осуществляя координацию с внешней MongoDB для хранилища json.

Например,  иногда пользователи приложения хотят иметь возможность добавлять к сущности произвольные атрибуты. Это требование бизнеса. Клиенту не нравится, когда вы говорите, что база данных этого не делает, так что они не могут делать заметки в их приложениях, и они тогда бормочут что-то наподобие: «Просто сделайте это в приложении» или «У нас не было таких проблем с MongoDB».

Таким образом, вы должны моделировать типичные вещи реляционно (хотя и с беспорядочным применением суррогатных ключей), но добавлять json-поле в таблицу person только для тех дополнительных данных, чтоб приложение могло их взять, отображая данные по человеку (person).

Как решить, когда использовать json?

Применяйте json, если ваши данные не вписываются в базу данных, используя нормальное реляционное проектирование. Если вы выбираете между использованием САЗ, сериализацией объекта Java/Ruby/Python в поле bytea и хранением ключа, чтобы искать внешний структурированный объект где-то ещё, то в этом случае вы должны прибегнуть к полям json.

На данный момент, возможно, стоит заменить hstore на jsonb во всех новых приложениях.

Заметьте, что простые (не двоичные — прим. пер.) json поля также полезны — если вы не собираетесь индексировать json и обращаться в запросе к его содержимому, то они обычно компактнее и быстрее как для чтения, так и для записи.

Оригинал статьи (англ.) взят с сайта  2ndquadrant.