PostgreSQL замедляются на большой таблице с массивами и большим количеством обновлений
У меня есть довольно большая таблица (20M записи), который имеет 3 индекса столбца и столбец массива. Столбец массива ежедневно обновляется (путем добавления новых значений) для всех строк. Существует, также вставляет, но не так, как существуют обновления.
Данные в массиве представляют ежедневные измерения, соответствующие этим трем ключам, чему-то вроде этого: [[date_id_1, my_value_for_date_1], [date_id_2, my_value_for_date_2]]. Это используется для рисования графика тех дневных значений. Скажите, что я хочу визуализировать значение для ключа (a, b, c) со временем, я делаю SELECT values FROM t WHERE a = my_a AND b = my_b AND c = my_c. Затем я использую values выстройте для рисования графика.
Выполнение обновлений (которые происходят в объеме один раз в день) ухудшалось значительно со временем.
Использование PostgreSQL 8.3.8.
Можно ли дать мне какие-либо подсказки того, где искать решение? Это могло быть что-либо от тонкой настройки некоторых параметров в пост-ГРЭС к ровному перемещению в другую базу данных (я предполагаю, что нереляционная база данных лучше подошла бы для этой конкретной таблицы, но у меня нет большого опыта с теми).
4 ответа
Я бы посмотрел на FILLFACTOR для таблицы. По умолчанию он установлен на 100, вы можете снизить его до 70 (для начала). После этого нужно сделать VACUUM FULL, чтобы перестроить таблицу.
ALTER TABLE tablename SET (FILLFACTOR = 70);
VACUUM FULL tablename;
REINDEX TABLE tablename;
Это дает UPDATE возможность разместить обновленную копию строки на той же странице, что и оригинал, что более эффективно, чем размещение ее на другой странице. Или, если ваша база данных уже несколько фрагментирована в результате множества предыдущих обновлений, она уже может быть достаточно щадящей. Теперь ваша база данных также имеет возможность делать HOT обновления, при условии, что столбец, который вы обновляете, не участвует ни в одном индексе.
Не уверен, что массивы - это то, что нужно.
Почему бы не хранить их в отдельной таблице (одно значение плюс ключи на строку). тогда массовое обновление будет чисто вставкой.
Ну, индекс из трех столбцов - это не повод для беспокойства. Это не обязательно сделает его намного медленнее. Но этот массив-столбец действительно может быть проблемой. Вы говорите, что ежедневно добавляете значения в этот массив-столбец. Под добавлением вы имеете в виду добавление значений ко всем 20 млн. записей в таблице? Или только некоторые записи?
Ситуация мне не совсем ясна, но я бы предложил поискать способ избавиться от этого массива-столбца. Например, сделать его отдельной таблицей. Но это зависит от вашей ситуации и может быть не вариант. Возможно, это только мое мнение, но я всегда чувствую себя "грязным", имея такой столбец в одной из моих таблиц. И чаще всего есть лучшее решение для проблемы, которую вы пытаетесь решить с помощью этого массива-столбца. С учетом этого, конечно, есть ситуации, в которых такой столбец уместен, но на данный момент я не могу вспомнить ни одной. Уж точно не в таблице с 20 млн. записей.
Проблема в обновлениях. Измените схему с массива на несколько строк в день, и проблема производительности исчезнет.
Вы можете добавить сворачивание в массивы, позже, с помощью какого-нибудь cronjob, но избегайте обновлений.