Заранее извиняюсь... многословный вопрос.
Предположим, у меня есть таблица table_x с 20 полями:
table_x_id (identity pk)
int1
int...
int8
text1
text...
text8
jsonb1
jsonb2
Теперь предположим, что я хочу поддерживать быстрый доступ к сгруппированным данным (скажем, полям int1, int2, text1_id, text2_id и jsonb1) в table_x. Назовите это Report 1. Данные на самом деле не играют важной роли в постановке этого вопроса, но вот воображаемый фрагмент из Report 1:
+-----------------------------------------------------------------------+
| int1value int2value text1value text2value jsonb1->item1 jsonb1->item2 |
+-----------------------------------------------------------------------+
| (table_x_id) 12 |
| (table_x_id) 20 |
| (table_x_id) 34 |
+-----------------------------------------------------------------------+
Теперь представьте, что у меня есть три или более подобных отчетов, и каждый отчет включает в себя группировку многих (но не всех) полей в table_x.
Каждое текстовое поле может легко достигать, скажем, 1000 символов, а поля jsonb, хотя и невелики, только усугубляют проблему.
Задача: ускорить группировку для отчетов.
Чтобы ускорить операции группирования и уменьшить размер строки table_x, я разбил уникальные значения текстовых полей (которые действительно сильно перекрываются) в отдельный text_table.
Теперь table_x это:
table_x_id (identity pk)
int1
int...
int8
text1_id (fk lookup)
text..._id (fk lookup)
text8_id (fk lookup)
jsonb1
jsonb2
Что касается группировки, то я думал сохранить хэш соответствующих столбцов внутри самого table_x, используя вызовы дайджеста() в триггере вставки/обновления. (Идея заключалась в том, чтобы преобразовать все соответствующие поля в моей группе в строки, соединить их вместе и выполнить хеширование полученной строки.)
Теперь table_x это:
table_x_id (identity pk)
int1
int...
int8
text1_id (lookup)
text..._id (lookup)
text8_id (lookup)
jsonb1
jsonb2
hash1_bytea (based on int1, int2, text1_id, text2_id and jsonb1)
hash2_bytea (based on int3, int7, text3_id, jsonb1 and jsonb2)
hash3_bytea (based on int2, int5, text1_id and jsonb2)
Теперь для отчета требуется больше поисков, но это быстро, и теперь мне нужно сгруппировать только по hash1_bytea, чтобы получить тот же результат Report 1.
Опасение: эквивалентные поля jsonb в разных строках могут не быть эквивалентными при сравнении их представлений jsonb::text. Из того, что я прочитал здесь, эти опасения кажутся оправданными.
Но если я не могу преобразовать значения jsonb в текст детерминированным образом, мое решение «хеш-поле внутри таблицы» развалится по швам.
Затем я решил сохранить значения jsonb в отдельном jsonb_table, где я гарантирую, что любая строка имеет уникальный объект jsonb.
jsonb_table is:
jsonb_id (identity pk)
jsonb (unique jsonb)
Для любого уникального объекта jsonb (не обращая внимания на порядок объектов в нем при представлении в тексте) теперь есть одна и ровно одна строка в jsonb_table, которая его представляет.
Теперь table_x это:
table_x_id (identity pk)
int1
int...
int8
text1_id (fk lookup)
text..._id (fk lookup)
text8_id (fk lookup)
jsonb1_id (fk lookup)
jsonb2_id (fk lookup)
hash1_bytea (based on int1, int2, text1_id, text2_id and jsonb1_id )
hash2_bytea (based on int3, int7, text3_id, jsonb1_id and jsonb2_id )
hash3_bytea (based on int2, int5, text1_id and jsonb2_id )
Да, поддерживать text_table и jsonb_table проблематично, но это выполнимо, и table_x сейчас кажется довольно эффективным, способным быстро поддерживать несколько хэшей.
Кажется, я выполнил быструю и точную группировку по нескольким разновидностям группировок со многими полями.
На данный момент у меня есть два вопроса:
Является ли мой подход разумным и относительно хорошо продуманным? Или есть лучший способ достичь моих целей?
JSON в jsonb1 и jsonb2 на самом деле представляет собой просто массив менее часто используемых специальных пар ключ-значение. Однако данные в jsonb1 и jsonb2 нуждаются в ссылочной целостности с данными, поддерживаемыми в нормализованных реляционных таблицах. В таком случае не будет ли плохой идеей создать
jsonb_child_table?
jsonb_child_table is:
jsonb_child_id (pk identity)
jsonb_id (fk to jsonb_table)
key_lookup_id (fk lookup)
value_lookup_id (fk lookup)
Опять же, хлопотно убедиться, что записи в jsonb_child_table являются правильными прорывами поля jsonb в jsonb_table, но, сделав это таким образом, я могу:
- быстро поддерживать всю эту групповую информацию, обсуждавшуюся ранее
- гарантировать хорошую ссылочную целостность
- отчет о jsonb1 (например), используя поля в
jsonb_child_table, упорядоченные (например) на основании метаданных (через соединение sql с использованиемkey_lookup_id), которые не хранятся в самом jsonb1.
Этот последний пункт, кажется, перекликается с тем, что я читал в другом месте в SO... что поддержка массива значений ключа в jsonb требует переосмысления... Если вы хотите обеспечить упорядочение пар, имейте ссылочную целостность и получайте данные быстрее, jsonb может быть плохим выбором. Однако в моем случае поддержка таблицы "заголовков" jsonb (с одним полем идентификатора внешнего ключа) позволяет быстро группировать разрозненные наборы (в table_x) пар значений. Поэтому я вижу преимущества в сохранении одних и тех же данных как в jsonb (для легкой группировки), так и в реальных таблицах (для RI и более быстрой и чистой отчетности).
Да, этот второй вопрос сам по себе достоин другого вопроса SO, но кажется, что вся эта куча взаимосвязана, поэтому я представляю все это здесь в одном (извините) длинном посте.
Заранее спасибо за отзыв!
