Что означает кластеризация таблиц в PostgreSQL, как это связано с индексацией и очисткой данных, чем полезно применение команды CLUSTER для AO/CO-таблиц в Greenplum 7, а также какой SQL-запрос поможет найти все кластеризованные таблицы в текущей базе данных.
Как работает кластеризация таблиц в PostgreSQL
Будучи основанной на объектно-реляционной базе данных PostgreSQL, о чем мы писали здесь, MPP-СУБД Greenplum использует множество ее операторов. Одним из них является оператор CLUSTER, который позволяет кластеризовать таблицу по существующему в ней индексу. В результате кластеризации таблицы её содержимое физически переупорядочивается.
В отличие от обычных индексов, кластерный индекс для любой таблицы может быть только один. Кластеризация дает преимущество в том случае, если большую часть данных, которые выбираются из таблицы, составляют группы индексированных данных, а не случайно выбранные отдельные данные, поскольку группы располагаются на одной или следующих друг за другом физических страницах.
Кластеризация является одноразовой операцией: последующие изменения в таблице нарушают порядок кластеризации: порядок новых или изменённых строк в соответствии с индексом не сохраняется. Сохранить порядок кластеризации при изменениях поможет установка параметра FILLFACTOR в значение меньше 100%, поскольку изменяемые строки будут помещаться в ту же страницу, если в ней достаточно места. Для установки индекса, который будет использоваться для будущих операций кластеризации, или очистки предыдущего значения можно также применить команду CLUSTER или уточнение SET WITHOUT CLUSTER команды ALTER TABLE. CLUSTER без параметров повторно кластеризует все ранее кластеризованные таблицы в текущей базе данных, принадлежащие пользователю, вызывающему эту команду.
CLUSTER нельзя выполнять внутри блока транзакции. В процессе кластеризации таблицы для устанавливается эксклюзивная блокировка доступа (ACCESS EXCLUSIVE), что препятствует одновременному выполнению всех других операций чтения и записи данных.
При обращении к случайным единичным строкам таблицы, фактический порядок данных в этой таблице не важен. Но если обращения к одним данным происходят чаще, чем к другим, и они проиндексированы, команда CLUSTER может оптимизировать обращение к диску и ускорить выполнение SQL-запросов. В частности, если из таблицы запрашивается диапазон индексированных значений или одно индексированное значение, которому соответствуют несколько строк, страница таблицы, найденная по индексу для первой искомой строки, будет содержать и все остальные искомые строки. CLUSTER меняет порядок строк таблицы, выполняя сканирование указанного индекса на основе статистики и параметров планировщика. При сканирование индекса создаётся временная таблица с данными целевой таблицы по порядку индекса и копии всех индексов таблицы. При этом нужен объём дискового пространства не менее суммированного размеров таблицы и ее индексов. При последовательном сканировании и сортировке создаётся ещё и временный файл для сортировки, увеличивая требуемый для выполнения этой операции размер дискового пространства. Хотя этот метод работает быстрее, чем простое сканирование по индексу, при нехватке дискового пространства можно отключить его, установив параметр enable_sort в значение off. Перед кластеризацией рекомендуется увеличить значение параметра maintenance_work_mem, чтобы выделить достаточный объем памяти. А, поскольку планировщик записывает статистику, связанную с порядком таблиц, для вновь кластеризуемых таблиц, ее следует обновить с помощью команды ANALYZE. Это поможет планировщику выбрать наиболее оптимальный план выполнения SQL-запроса.
Благодаря тому, что команда CLUSTER запоминает индексы, по которым были кластеризованы таблицы, ее можно выполнить вручную лишь однажды, а потом повторять автоматически, регулярно запуская скриптом по расписанию. Процессы операции CLUSTER отображают ход ее выполнения в представлении pg_stat_progress_cluster. При кластеризации партиционированной таблицы каждый раздел кластеризуется по разделу заданного партиционированного индекса, который должен быть указан.
Команда CLUSTER в Greenplum
До версии Greenplum 7, оптимизированные для добавления и ориентированные на столбцы таблицы (AO/CO) нельзя было кластеризовать. В 7-м релизе, о новинках которого мы рассказывали здесь и здесь, это стало возможным. Команда CLUSTER гарантирует, что блоки таблицы физически упорядочены по столбцам, принадлежащим предоставленному индексу. Это дает существенный выигрыш для таблиц, которые загружаются неупорядоченным образом, повышая пространственную локальность в файле физической таблицы для ключей индекса. Таким образом, строки с одинаковым или близкими значениями ключа индекса с большей вероятностью будут находиться в одном и том же блоке (блок фиксированного размера для таблиц кучи и varblock для таблиц AO/CO). В результате этого выборка строк на основе диапазона ключей индекса будет извлекать меньшее количество блоков с диска. Если рабочая нагрузка запроса не является точечным запросом (выбор одной или нескольких строк) и превышает диапазон ключей индекса разумного размера, SQL-запросы выполняются на порядок быстрее: в отличие от таблиц кучи, блоки, считанные с диска, не буферизуются в общих буферах, а напрямую преобразуются в блочные чтения, сохраненные с диска.
При использовании команды кластеризации в Greenplum рекомендуется учитывать следующие лучшие практики:
- физический порядок кластеризованной таблицы не сохраняется, если в ней есть новые вставки/обновления с момента последней операции CLUSTER. Оэтому кластеризация дает преимущество таблицам, которые загружаются один раз и читаются часто, например, партиционированные таблицы по какой-либо форме измерения времени (день, месяц, год и пр.), со старыми разделами без обновлений.
- таблицы, которые загружаются с помощью периодически повторяющегося ETL-задания, тоже могут выиграть от кластеризации, которую можно проводить после каждого окна загрузки данных. При этом стоит помнить, что эксклюзивная блокировка доступа к таблице во время выполнения команды CLUSTER может привести простою приложений аналитики больших данных.
- Из-за того, что кластеризация фактически перезаписывает таблицу, эта операция эффективно выполняет полную очистку таблицы, т.е. команду VACUUM, что мы разбирали здесь, читая из старого relfile действительные строки.
- Поскольку CLUSTER делает значения столбцов индекса непрерывными, это приводит к лучшему сжатию файлов ссылок для каждого столбца индекса, что особенно полезно для CO-таблиц. Наибольший эффект заметен, если индексированный столбец закодирован с помощью RLE.
- Когда команда CLUSTER вызывается без каких-либо аргументов, она повторно кластеризует все ранее кластеризованные таблицы в текущей базе данных, которой владеет вызывающий пользователь, позволяя автоматизировать выполнение этой операции. Найти все кластеризованные таблицы в текущей базе данных поможет SQL-запрос
SELECT indrelid::regclass FROM pg_index WHERE indisclustered;
- Таблицы кучи поддерживают изменение коэффициента заполнения (параметр FILLFACTOR), установка которого менее 100% поможет сохранить обновленные кортежи на одной странице, сохраняя порядок данных, несмотря на обновления кластеризованных таблиц.
Узнайте больше про администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
