Как оптимизировать скорость обновления материализованного представления Postgres?
Есть таблица (GREENPLUM 6.26 от Arenadata), содержащая десятки миллионов строк, один столбец которой содержит json. В этом json десятки элементов разных типов данных - целые числа, строки, флоат.
Чтобы достать эти данные создано материализованное представление, которое раскладывает json в плоский вид (каждый элемент json - это отдельный столбец).
REFRESH этого материализованного представления занимает очень много времени (минуты, иногда десятки минут).
Что можно сделать чтобы оптимизировать разложение json на элементы?
SELECT, определяющий материализованное представление выполняется почти мгновенно.
Так же, если вместо материализованного представления создавать обычное представление с таким же SELECT, то оно создается мгновенно, но SELECT * из этого обычного представления так же долго, как и REFRESH мат. представления.
REFRESH этого материализованного представления занимает очень много времени (минуты, иногда десятки минут).
Что можно сделать чтобы оптимизировать разложение json на элементы?
Без тонкой диагностики непонятно. На что там идут ресурсы. Диск? Процессор?
Возможно тратиться время на парсинг. Попробоуйте в оригинальной таблице заменить JSON на JSONB.
Спасибо всем, кто ответил!
Дело было в неоптимальном SELECT. Дело в том, что присутствовало многократное приведение к типу JSON. Убрали его - время сократилось до секунд.
Прекратить пользоваться json, разобрав его структуру хотя бы для тех данных, которые требуются независимо друг от друга, и разместить в таблицах. А то это тупо убивает любые методологии, которые созданы в postgres для ускорения работы с этими данными.
Единственное, где сериализация данных в реляционных базах оправдана - это когда данные запрашиваются целиком из записи и имеют нерегулярную (непрогнозируемую/случайную/сложную) структуру.
REFRESH в постгресе выполняет по сути пересоздание представления. Если у вас огромная таблица-источник, да еще куча данных упакована в JSON, тут матвью вряд ли в текущем виде спасет.
Как часто меняются данные в JSON? Какая часть таблицы обновляется (или там вообще aphend-only)?
Если данные меняются редко и/или лишь небольшая их часть (или вы готовы несколько пожертвовать производительностью таких UPDATE), можете триггером при обновлении/вставке JSON колонки распаковывать данные и записывать отдельную таблицу (которая раньше у вас была матвью).
Если скорость операций обновления на исходной таблице критична, добавьте поле типа json_col_updated TIMESTAMP (можно сделать отдельную таблицу со связью 1-к-1, если исходную не хочется трогать) и обновляйте его триггером, когда меняется поле с JSON. Дальше с нужной периодичностью распаковывайте массово только обновленные данные (json_col_updated > last_unpack).
Средний
Простой
