Как пользоваться партициями

Партиции и загрузка данных в оперативную память Текст научной статьи по специальности «Компьютерные и информационные науки»

Текст научной работы на тему «Партиции и загрузка данных в оперативную память»

ПАРТИЦИИ И ЗАГРУЗКА ДАННЫХ В ОПЕРАТИВНУЮ ПАМЯТЬ

Юлия Владимировна Мельник

Консультант SAP BI / BW / BO / HANA БДО Юникон Бизнес Солюшнс.

Окончила Белгородский государственный национальный исследовательский университет. Магистр компьютерных наук и математики, математик-программист со специализацией «Математическое и программное обеспечение информационных систем».

Ю.В. Мельник. Партиции и загрузка данных в оперативную память

В данной статье будет рассмотрено, что такое партиции и партиционные таблицы, какие спецификации партицирования существуют, и как произвести загрузку данных в оперативную память в SAP HANA.

В связи с тем что существует ограничение на количество строк в таблице — не более 2 млрд., возникает задача преодоления этого ограничения. Задача решается с помощью разбиения таблицы на части, или партицирования. Таким образом, очень большие таблицы могут быть разбиты на маленькие, более управляемые части — партиции. Партиции могут быть распределены между несколькими хостами. Это значит, что запрос к таблице может обрабатываться на нескольких серверах вместо одного, что также способствует дальнейшему распараллеливанию с помощью выполнения нескольких потоков для каждой таблицы.

Партицирование — функция, которая позволяет разбивать большие таблицы на логические части по выбранным критериям, благодаря чему улучшается производительность базы данных.

Партиция — часть таблицы, логически выделенная для удобства хранения и обработки в памяти и состоящая из смежных блоков. Поэтому таблица, которая была разбита на партиции, называется партицированной.

Пример. Имеется таблица с данными кодов операций и сумм, где ключевой столбец — «Дата» (см. рисунок ниже). Представим, что данная таблица содержит много данных, и количество строк подходит к пороговому ограничению 2 млрд. при постоянном увеличении данных. Чтобы избежать переполнения таблицы, разобьем ее на партиции по ключевому полю «Дата», а именно по году. Отметим, что столбец, который используется для разбиения, называется столбцом партиций. В нашем примере будут заданы партиции по годам (2011, 2012, 2013) с помощью следующего SQL-кода (используется функция даты — year ()):

CREATE COLUMN TABLE MY_TABLE (a DATE, b VARCHAR (4), c INT, PRIMARY KEY (a, b)) PARTITION BY RANGE (year (a)) (PARTITION ‘2011′

В итоге получим следующие партиции (см. рисунок 1).

Мы получили три партиции, каждая из которых меньше основной таблицы, т.е. задача с ограничением по количеству строк решена. Отметим, что количество строк в каждой новой партиции также не должно превышать 2 млрд.

Вы можете просмотреть свойства таблицы, вызвав ее контекстное меню и выбрав пункт “Open Definition” (рисунок 2). Так, в столбце “Number of Entries”, представленном на рисунке ниже, приведено количество строк, которые были отнесены к каждой из партиций, а столбец “Loaded” показывает статус загрузки в оперативную память (“FULL” — данные полностью загружены в оперативную память).

Чтобы просмотреть список всех таблиц и определить, какие из них партицированы, а какие нет, у вас должны быть следующие полномочия: системная привилегия DATA ADMIN и привилегии SELECT и UPDATE для изменения таблицы или схемы. Если приви-

Технологическая платформа для решения современных бизнес-задач

Дата Код операции Сумма

08.2011 1003 6200

Дата Код операции Сумма

01.2012 5006 1250

02.2012 4375 2550

03.2013 1003 3800

01.2013 5006 4300

03.2012 4375 5500

08.2011 1003 6200

09.2013 1360 7350

04.2012 2180 8750

Дата Код операции Сумма

01.2012 5006 1250

02.2012 4375 2550

03.2012 4375 5500

04.2012 2180 8750

Дата Код операции Сумма

03.2013 1003 3800

01.2013 5006 4300

09.2013 1360 7350

Т>Ы«Турв TsbkW?me Sfhtmj Nam*

Ш C0ST.TYPE MELNIK ГП

яз H.MIN1.SRC MELNIK Ш

?53 MyjTASLE MELNK 1> 1 3, 4

т МУТШЕ1 MELNIK XI 3,4

2ВЗ МУ_ТЛ81_Е2 MELNK 1,2,3

1Ш MV.TA8LE3 MELNK 1.2

MV_TA0LE4 MELNIK 1,13

т NAFJTOG MELNIK m

Ю.В. Мельник. Партиции и загрузка данных в оперативную память

легии есть, то нужно вызвать контекстное меню папки Catalog, схемы или папки Tables и выбрать пункт “Show Table Distribution”. Отобразится список таблиц (см. рисунок 3). Заметим, что значок в столбце “Table Type” у партицированных и непартицированных таблиц выглядит по-разному. Также непартицированные таблицы отмечены крестиком «х», а для партицированных указаны партиции, на которые они разбиты. В данном окне вы можете разбить непартицированные таблицы или объединить партиции в уже разбитых таблицах с помощью команд из контекстного меню:

• Partition Table — Разбить таблицу на партиции

• Merge Partitions — Объединить партиции

Также для объединения всех частей партицированной таблицы в единую таблицу можно использовать следующий SQL-код:

ALTER TABLE MY_TABLE3 MERGE PARTITIONS;

Результат объединения партиций представлен на рисунке 4.

Стоит отметить, что партицирование также важно при распределении таблиц по нескольким хостам SAP HANA, что способствует улучшенному распараллеливанию запросов. Поэтому партицирование обычно используется в распределенных системах, но оно также может быть полезно для систем с одним сервером.

Критерии, в соответствии с которыми таблица разбивается на партиции, называются спецификациями партицирования. В зависимости от использования спецификаций, партицирование бывает одно- и многоуровневым.

Существуют следующие спецификации партицирования:

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

Column1.^Indexes j Furihif Propirtiis| Runtime Infer rmaticn]_____

Tcul Memory Consumption (Кв): ST Nvfflbtief Entries: 6

Size on Cis-k (KB): LS

PartitionSpecification: RANGE топ111(Д)2М51>1-211

Table Name: |Ц/_ТАеш

Columns Indexes I Furtber Properties I Runtime Information

Total Men-or> Consumpt(in(KB): 22 Numbirof Entries; 6

Size or> Disk (KB): 32

Partition $pe cificatjon:

Ddeib lor Table

Host. PsrtlC1 Rang* Total Size (KB) Main Sc

1 1 20«Й)Ь200507 42

2 i 2Q№Qi-2QQfQl 14

Detail* for Table

Host;.- Part ID Range

Total Size (Кв) 2)

Технологическая платформа для решения современных бизнес-задач

i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

Hash-партицирование используется для одинакового распределения строк по партициям при распределении нагрузки и преодоления ограничения в 2 млрд. строк. Количество партиций вычисляется с помощью хэш-функции со значением заданного столбца.

Hash-партицирование не требует углубленного знания фактических значений таблицы. Для него должны быть указаны столбцы партиций. Можно использовать несколько таких столбцов. Если таблица имеет первичный ключ, то столбцы партиций должны быть частью первичного ключа. Ниже приведен пример SQL-кода для создания таблицы с помощью Hash-партицирования и четырьмя партициями.

CREATE COLUMN TABLE MY_TABLE (a INT, b INT, c INT, PRIMARY KEY (a, b))

PARTITION BY HASH (a, b) PARTITIONS 4;

С помощью функции GET_NUM_SERVERS, которая получает значение количества серверов, можно не указывать фиксированное количество партиций — оно будет задано динамически во время выполнения в соответствии с конфигурацией. Сколько серверов в системе, столько и будет создано партиций. Рекомендуется задавать количество партиций следующим образом:

PARTITION BY HASH (a, b) PARTITIONS GET_NUM_SERVERS ();

Более подробная информация о SQL-синтаксисе для партицирования содержится в руководстве SAP HANA SQL Reference.

Как же происходит процесс загрузки созданной таблицы в оперативную память? SAP HANA сама управляет загрузкой и выгрузкой таблиц в память и из памяти. Но при необходимости можно загружать и выгружать отдельные таблицы вручную.

Например, при создании таблицы она еще не загружена в память. Это можно про-

Columni Indents further PrtjMrtitl [Runtime Letenri»ban|

Number of Entries: )

Size on Disk (KB>: Li

Memaiy CenwiWjKiart in Meirt SttHtj* (kE): •

Memory Ceimimptian in Deln Storage (kE): —

Eitimated Maximum MimoiyConjum|ilion (IS): —

Манипуляции с партициями и кусками

Для работы с партициями доступны следующие операции:

DETACH PARTITION — перенести партицию в директорию detached ;
DROP PARTITION — удалить партицию;
ATTACH PARTITION | PART — добавить партицию/кусок в таблицу из директории detached ;
ATTACH PARTITION FROM — скопировать партицию из другой таблицы;
REPLACE PARTITION — скопировать партицию из другой таблицы с заменой;
MOVE PARTITION TO TABLE — переместить партицию в другую таблицу;
CLEAR COLUMN IN PARTITION — удалить все значения в столбце для заданной партиции;
CLEAR INDEX IN PARTITION — очистить построенные вторичные индексы для заданной партиции;
FREEZE PARTITION — создать резервную копию партиции;
UNFREEZE PARTITION — удалить резервную копию партиции;
FETCH PARTITION | PART — скачать партицию/кусок с другого сервера;
MOVE PARTITION | PART — переместить партицию/кускок на другой диск или том.
UPDATE IN PARTITION — обновить данные внутри партиции по условию.
DELETE IN PARTITION — удалить данные внутри партиции по условию.

DETACH PARTITION | PART

ALTER TABLE table_name [ON CLUSTER cluster] DETACH PARTITION|PART partition_expr

Перемещает заданную партицию в директорию detached . Сервер не будет знать об этой партиции до тех пор, пока вы не выполните запрос ATTACH.

ALTER TABLE mt DETACH PARTITION '2020-11-21'; ALTER TABLE mt DETACH PART 'all_2_2_0';

Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.

После того как запрос будет выполнен, вы сможете производить любые операции с данными в директории detached . Например, можно удалить их из файловой системы.

Запрос реплицируется — данные будут перенесены в директорию detached и забыты на всех репликах. Обратите внимание, запрос может быть отправлен только на реплику-лидер. Чтобы узнать, является ли реплика лидером, выполните запрос SELECT к системной таблице system.replicas. Либо можно выполнить запрос DETACH на всех репликах — тогда на всех репликах, кроме реплик-лидеров (поскольку допускается несколько лидеров), запрос вернет ошибку.

DROP PARTITION | PART

ALTER TABLE table_name [ON CLUSTER cluster] DROP PARTITION|PART partition_expr

Удаляет партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут.

Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.

Запрос реплицируется — данные будут удалены на всех репликах.

ALTER TABLE mt DROP PARTITION '2020-11-21'; ALTER TABLE mt DROP PART 'all_4_4_0';

DROP DETACHED PARTITION | PART

ALTER TABLE table_name [ON CLUSTER cluster] DROP DETACHED PARTITION|PART partition_expr

Удаляет из detached кусок или все куски, принадлежащие партиции. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.

ATTACH PARTITION | PART

ALTER TABLE table_name [ON CLUSTER cluster] ATTACH PARTITION|PART partition_expr

Добавляет данные в таблицу из директории detached . Можно добавить данные как для целой партиции, так и для отдельного куска. Примеры:

ALTER TABLE visits ATTACH PARTITION 201901; ALTER TABLE visits ATTACH PART 201901_2_2_0;

Как корректно задать имя партиции или куска, см. в разделе Как задавать имя партиции в запросах ALTER.

Этот запрос реплицируется. Реплика-иницатор проверяет, есть ли данные в директории detached . Если данные есть, то запрос проверяет их целостность. В случае успеха данные добавляются в таблицу.

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

Вы можете поместить данные в директорию detached на одной реплике и с помощью запроса ALTER . ATTACH добавить их в таблицу на всех репликах.

ATTACH PARTITION FROM

ALTER TABLE table2 [ON CLUSTER cluster] ATTACH PARTITION partition_expr FROM table1

Копирует партицию из таблицы table1 в таблицу table2 .

Обратите внимание, что:

Данные не удаляются ни из table1 , ни из table2 .
table1 может быть временной таблицей.

Следует иметь в виду:

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

Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.

REPLACE PARTITION

ALTER TABLE table2 [ON CLUSTER cluster] REPLACE PARTITION partition_expr FROM table1

Копирует партицию из таблицы table1 в таблицу table2 с заменой существующих данных в table2 .

Обратите внимание, что:

Данные из table1 не удаляются.
table1 может быть временной таблицей.

Следует иметь в виду:

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

Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.

MOVE PARTITION TO TABLE

ALTER TABLE table_source [ON CLUSTER cluster] MOVE PARTITION partition_expr TO TABLE table_dest

Перемещает партицию из таблицы table_source в таблицу table_dest (добавляет к существующим данным в table_dest ) с удалением данных из таблицы table_source .

Следует иметь в виду:

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

CLEAR COLUMN IN PARTITION

ALTER TABLE table_name [ON CLUSTER cluster] CLEAR COLUMN column_name IN PARTITION partition_expr

Сбрасывает все значения в столбце для заданной партиции. Если для столбца определено значение по умолчанию (в секции DEFAULT ), то будет выставлено это значение.

ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902

CLEAR INDEX IN PARTITION

ALTER TABLE table_name [ON CLUSTER cluster] CLEAR INDEX index_name IN PARTITION partition_expr

Работает как CLEAR COLUMN , но сбрасывает индексы вместо данных в столбцах.

FREEZE PARTITION

ALTER TABLE table_name [ON CLUSTER cluster] FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name']

Создаёт резервную копию для заданной партиции. Если выражение PARTITION опущено, резервные копии будут созданы для всех партиций.

Примечание

Создание резервной копии не требует остановки сервера.

Для таблиц старого стиля имя партиций можно задавать в виде префикса (например, 2019 ). В этом случае, резервные копии будут созданы для всех соответствующих партиций. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.

Запрос формирует для текущего состояния таблицы жесткие ссылки на данные в этой таблице. Ссылки размещаются в директории /var/lib/clickhouse/shadow/N/. , где:

/var/lib/clickhouse/ — рабочая директория ClickHouse, заданная в конфигурационном файле;
N — инкрементальный номер резервной копии.
если задан параметр WITH NAME , то вместо инкрементального номера используется значение параметра ‘backup_name’ .

Примечание

При использовании нескольких дисков для хранения данных таблицы директория shadow/N появляется на каждом из дисков, на которых были куски, попавшие под выражение PARTITION .

Структура директорий внутри резервной копии такая же, как внутри /var/lib/clickhouse/ . Запрос выполнит chmod для всех файлов, запрещая запись в них.

Обратите внимание, запрос ALTER TABLE t FREEZE PARTITION не реплицируется. Он создает резервную копию только на локальном сервере. После создания резервной копии данные из /var/lib/clickhouse/shadow/ можно скопировать на удалённый сервер, а локальную копию удалить.

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

ALTER TABLE t FREEZE PARTITION копирует только данные, но не метаданные таблицы. Чтобы сделать резервную копию метаданных таблицы, скопируйте файл /var/lib/clickhouse/metadata/database/table.sql

Чтобы восстановить данные из резервной копии, выполните следующее:

Создайте таблицу, если она ещё не существует. Запрос на создание можно взять из .sql файла (замените в нём ATTACH на CREATE ).
Скопируйте данные из директории data/database/table/ внутри резервной копии в директорию /var/lib/clickhouse/data/database/table/detached/ .
С помощью запросов ALTER TABLE t ATTACH PARTITION добавьте данные в таблицу.

Восстановление данных из резервной копии не требует остановки сервера.

Подробнее о резервном копировании и восстановлении данных читайте в разделе Резервное копирование данных.

UNFREEZE PARTITION

ALTER TABLE table_name [ON CLUSTER cluster] UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name'

Удаляет с диска «замороженные» партиции с указанным именем. Если секция PARTITION опущена, запрос удаляет резервную копию всех партиций сразу.

FETCH PARTITION | PART

ALTER TABLE table_name [ON CLUSTER cluster] FETCH PARTITION|PART partition_expr FROM 'path-in-zookeeper'

Загружает партицию с другого сервера. Этот запрос работает только для реплицированных таблиц.

Запрос выполняет следующее:

Загружает партицию/кусок с указанного шарда. Путь к шарду задается в секции FROM (‘path-in-zookeeper’). Обратите внимание, нужно задавать путь к шарду в ZooKeeper.
Помещает загруженные данные в директорию detached таблицы table_name . Чтобы прикрепить эти данные к таблице, используйте запрос ATTACH PARTITION | PART.

ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PARTITION 201902;

FETCH PART

ALTER TABLE users FETCH PART 201901_2_2_0 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PART 201901_2_2_0;

Следует иметь в виду:

Запрос ALTER TABLE t FETCH PARTITION|PART не реплицируется. Он загружает партицию в директорию detached только на локальном сервере.
Запрос ALTER TABLE t ATTACH реплицируется — он добавляет данные в таблицу сразу на всех репликах. На одной из реплик данные будут добавлены из директории detached , а на других — из соседних реплик.

Перед загрузкой данных система проверяет, существует ли партиция и совпадает ли её структура со структурой таблицы. При этом автоматически выбирается наиболее актуальная реплика среди всех живых реплик.

Несмотря на то что запрос называется ALTER TABLE , он не изменяет структуру таблицы и не изменяет сразу доступные данные в таблице.

MOVE PARTITION | PART

Перемещает партицию или кусок данных на другой том или диск для таблиц с движком MergeTree . Смотрите Хранение данных таблицы на нескольких блочных устройствах.

ALTER TABLE table_name [ON CLUSTER cluster] MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'

Запрос ALTER TABLE t MOVE :

Не реплицируется, т.к. на разных репликах могут быть различные конфигурации политик хранения.
Возвращает ошибку, если указан несконфигурированный том или диск. Ошибка также возвращается в случае невыполнения условий перемещения данных, которые указаны в конфигурации политики хранения.
Может возвращать ошибку в случае, когда перемещаемые данные уже оказались перемещены в результате фонового процесса, конкурентного запроса ALTER TABLE t MOVE или как часть результата фоновой операции слияния. В данном случае никаких дополнительных действий от пользователя не требуется.

ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow' ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd'

UPDATE IN PARTITION

Манипулирует данными в указанной партиции, соответствующими заданному выражению фильтрации. Реализовано как мутация mutation.

ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr

Пример

ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2;

Смотрите также

DELETE IN PARTITION

Удаляет данные в указанной партиции, соответствующие указанному выражению фильтрации. Реализовано как мутация mutation.

ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE [IN PARTITION partition_id] WHERE filter_expr

Пример

ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2;

Смотрите также

Как задавать имя партиции в запросах ALTER

Чтобы задать нужную партицию в запросах ALTER . PARTITION , можно использовать:

Имя партиции. Посмотреть имя партиции можно в столбце partition системной таблицы system.parts. Например, ALTER TABLE visits DETACH PARTITION 201901 .
Кортеж из выражений или констант, совпадающий (в типах) с кортежем партиционирования. В случае ключа партиционирования из одного элемента, выражение следует обернуть в функцию tuple(. ) . Например, ALTER TABLE visits DETACH PARTITION tuple(toYYYYMM(toDate(‘2019-01-25’))) .
Строковый идентификатор партиции. Идентификатор партиции используется для именования кусков партиции на файловой системе и в ZooKeeper. В запросах ALTER идентификатор партиции нужно указывать в секции PARTITION ID , в одинарных кавычках. Например, ALTER TABLE visits DETACH PARTITION ID ‘201901’ .
Для запросов ATTACH PART и DROP DETACHED PART: чтобы задать имя куска партиции, используйте строковой литерал со значением из столбца name системной таблицы system.detached_parts. Например, ALTER TABLE visits ATTACH PART ‘201901_1_1_0’ .

Использование кавычек в имени партиций зависит от типа данных столбца, по которому задано партиционирование. Например, для столбца с типом String имя партиции необходимо указывать в кавычках (одинарных). Для типов Date и Int* кавычки указывать не нужно.

Замечание: для таблиц старого стиля партицию можно указывать и как число 201901 , и как строку ‘201901’ . Синтаксис для таблиц нового типа более строг к типам (аналогично парсеру входного формата VALUES).

Правила, сформулированные выше, актуальны также для запросов OPTIMIZE. Чтобы указать единственную партицию непартиционированной таблицы, укажите PARTITION tuple() . Например:

OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL;

IN PARTITION указывает на партицию, для которой применяются выражения UPDATE или DELETE в результате запроса ALTER TABLE . Новые куски создаются только в указанной партиции. Таким образом, IN PARTITION помогает снизить нагрузку, когда таблица разбита на множество партиций, а вам нужно обновить данные лишь точечно.

Партиционирование в PostgreSQL – Что? Зачем? Как?

Функцией партиционирования таблиц в PostgreSQL, к сожалению, активно пользуются пока не многие. На мой взгляд, очень достойно о ней рассказывает в своей работе Hubert Lubaczewski (depesz.com). Предлагаю вам еще один перевод его статьи!

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

Так что я постараюсь объяснить в меру своих знаний и возможностей, что это такое, зачем его стоит использовать и как это сделать.

Как вы наверняка знаете, в PostgreSQL есть таблицы, а в таблицах есть данные. Иногда это всего несколько строк, а иногда – миллиарды.

Партиционирование – это метод разделения больших (исходя из количества записей, а не столбцов) таблиц на много маленьких. И желательно, чтобы это происходило прозрачным для приложения способом.

Одной из редко используемых фич PostgreSQL является тот факт, что это объектно-реляционная база данных. И «объект» здесь ключевое слово, потому что объекты (или, скорее, классы) знают то, что называется «наследование». Именно это используется для партиционирования.

Давайте посмотрим, о чём речь.

Я создам обычную таблицу users:

$ create table users ( id serial primary key, username text not null unique, password text, created_on timestamptz not null, last_logged_on timestamptz not null );

Теперь, для полноты картины, давайте добавим несколько строк и дополнительный индекс:

$ insert into users (username, password, created_on, last_logged_on) select random_string( (random() * 4 + 5)::int4), random_string( 20 ), now() - '2 years'::interval * random(), now() - '2 years'::interval * random() from generate_series(1, 10000); $ create index newest_users on users (created_on);

Итак, у нас получилась тестовая таблица:

$ \d Table "public.users" Column | Type | Modifiers ----------------+--------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null password | text | created_on | timestamp with time zone | not null last_logged_on | timestamp with time zone | not null Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_username_key" UNIQUE CONSTRAINT, btree (username) "newest_users" btree (created_on)

С какими-то случайными данными:

$ select * from users limit 10; id | username | password | created_on | last_logged_on ----+----------+----------------------+-------------------------------+------------------------------- 1 | ityfce3 | 2ukgbflj_l2ndo3vilt2 | 2015-01-02 16:56:41.346113+01 | 2015-04-15 12:34:58.318913+02 2 | _xg_pv | u8hy20aifyblg9f3_rf2 | 2014-09-27 05:41:05.317313+02 | 2014-08-07 14:46:14.197313+02 3 | uvi1wo | h09ae85v_f_cx0gf6_8r | 2013-06-17 18:48:44.389313+02 | 2014-06-03 06:53:49.640513+02 4 | o6rgs | vzbrkwhnsucxco5pjep0 | 2015-01-30 11:33:25.150913+01 | 2013-11-05 07:18:47.730113+01 5 | nk61jw77 | lidk_mnpe_olffmod7ed | 2014-06-15 07:18:34.597313+02 | 2014-03-21 17:42:44.763713+01 6 | 3w326_2u | pyoqg87feemojhql7jrn | 2015-01-20 05:41:54.133313+01 | 2014-09-07 20:33:23.682113+02 7 | m9rk9mnx | 6pvt94s6ol46kn0yl62b | 2013-07-17 15:13:36.315713+02 | 2013-11-12 10:53:06.123713+01 8 | adk6c | egfp8re0z492e6ri8urz | 2014-07-23 11:41:11.883713+02 | 2013-10-22 07:19:36.200513+02 9 | rsyaedw | ond0tie9er92oqhmdj39 | 2015-05-11 16:45:40.472513+02 | 2013-08-31 17:29:18.910913+02 10 | prlobe46 | _3br5v97t2xngcd7xz4n | 2015-01-10 20:13:29.461313+01 | 2014-05-04 06:25:56.072513+02 (10 rows)

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

$ create table users_1 () inherits (users); $ \d users_1 Table "public.users_1" Column | Type | Modifiers ----------------+--------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null password | text | created_on | timestamp with time zone | not null last_logged_on | timestamp with time zone | not null Inherits: users

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

она использует тот же sequence, что и основная таблица, для своей колонки id;
все столбцы имеют одинаковое определение, включая ограничения not null;
нет ни первичного ключа, ни ограничений уникальности для имени пользователя, ни индекса для created_on.

$ drop table users_1; $ create table users_1 ( like users including all ); $ \d users_1 Table "public.users_1" Column | Type | Modifiers ----------------+--------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null password | text | created_on | timestamp with time zone | not null last_logged_on | timestamp with time zone | not null Indexes: "users_1_pkey" PRIMARY KEY, btree (id) "users_1_username_key" UNIQUE CONSTRAINT, btree (username) "users_1_created_on_idx" btree (created_on)

Теперь у нас есть все индексы и ограничения, но мы потеряли информацию о наследовании. Но мы можем добавить её позже с помощью:

$ alter table users_1 inherit users; $ \d users_1 Table "public.users_1" Column | Type | Modifiers ----------------+--------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null password | text | created_on | timestamp with time zone | not null last_logged_on | timestamp with time zone | not null Indexes: "users_1_pkey" PRIMARY KEY, btree (id) "users_1_username_key" UNIQUE CONSTRAINT, btree (username) "users_1_created_on_idx" btree (created_on) Inherits: users

Мы могли бы сделать это в один шаг, но тогда появляются разные неприятные уведомления:

$ drop table users_1; $ create table users_1 ( like users including all ) inherits (users); NOTICE: merging column "id" with inherited definition NOTICE: merging column "username" with inherited definition NOTICE: merging column "password" with inherited definition NOTICE: merging column "created_on" with inherited definition NOTICE: merging column "last_logged_on" with inherited definition $ \d users_1 Table "public.users_1" Column | Type | Modifiers ----------------+--------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null password | text | created_on | timestamp with time zone | not null last_logged_on | timestamp with time zone | not null Indexes: "users_1_pkey" PRIMARY KEY, btree (id) "users_1_username_key" UNIQUE CONSTRAINT, btree (username) "users_1_created_on_idx" btree (created_on) Inherits: users

В любом случае, теперь у нас есть две таблицы – основная и первая партиция.

Если я произведу какое-либо действие – выборка/обновление/удаление – с пользователями, обе таблицы будут просканированы:

$ explain analyze select * from users where QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Append (cost=0.29..16.47 rows=2 width=66) (actual time=0.008..0.009 rows=1 loops=1) -> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1) Index Cond: (id = 123) -> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) Planning time: 0.327 ms Execution time: 0.031 ms (7 rows)

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

$ explain analyze select * from users_1 where QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (id = 123) Planning time: 0.162 ms Execution time: 0.022 ms (4 rows)

Если бы мы хотели, мы могли бы обратиться только к таблице пользователей без её партиций, используя ключевое слово ONLY:

$ explain analyze select * from only users where QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1) Index Cond: (id = 123) Planning time: 0.229 ms Execution time: 0.031 ms (4 rows)

Вы могли заметить, что я сказал, что выборка/обновление/удаление работает на всех партициях. А что насчет вставок? Вставке требуется добавить куда-нибудь данные, так что она всегда работает так, как будто было использовано ONLY. Поэтому, если мне нужно добавить строку в users_1, я должен сделать так:

INSERT INTO users_1 .

Выглядит как-то не слишком хорошо, но не волнуйтесь, есть способы это обойти.

Давайте попробуем произвести настоящее партиционирование. Для начала нам нужно решить, каким будет ключ партиционирования – другими словами, по какому алгоритму будут выбираться партиции.

Есть пара наиболее очевидных:

партиционирование по дате – например, выбирать партиции, основываясь на годе, в котором пользователь был создан;
партиционирование по диапазону идентификаторов – например, первый миллион пользователей, второй миллион пользователей, и так далее;
партиционирование по чему-нибудь другому – например, по первой букве имени пользователя.

Почему стоит использовать одну схему, а не другую? Давайте разберемся в их достоинствах и недостатках:

партиционирование по дате:
- достоинства:
  - легко понять;
  - количество строк в данной таблице будет достаточно стабильным;
  - требует поддержки – время от времени нам придётся добавлять новые партиции;
  - поиск по имени пользователя или id потребует сканирования всех партиций;
  - достоинства:
    - легко понять;
    - количество строк в партиции будет на 100% стабильным;
    - требует поддержки – время от времени нам придётся добавлять новые партиции;
    - поиск по имени пользователя или id потребует сканирования всех партиций;
    - достоинства:
      - легко понять;
      - никакой поддержки – есть строго определенный набор партиций и нам никогда не придется добавлять новые;
      - количество строк в партициях будет стабильно расти;
      - в некоторых партициях будет существенно больше строк, чем в других (больше людей с никами, начинающимися на “t*», чем на “y*»);
      - поиск по id потребует сканирования всех партиций;
      - достоинства:
        
        никакой поддержки – есть строго определенный набор партиций и нам никогда не придется добавлять новые;
        
        строки будут равно распределяться между партициями;
        
        количество строк в партициях будет стабильно расти;
        
        поиск по id потребует сканирования всех партиций;
        
        поиск по имени пользователя будет сканировать только одну партицию, но только при использовании дополнительных условий.
        
        Для начала мне нужно создать побольше партиций:
        
        $ create table users_2 ( like users including all ); $ alter table users_2 inherit users; . $ create table users_10 ( like users including all ); $ alter table users_10 inherit users;
        
        Теперь у таблицы users есть 10 партиций:
        
        $ \d users Table "public.users" Column | Type | Modifiers ----------------+--------------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null password | text | created_on | timestamp with time zone | not null last_logged_on | timestamp with time zone | not null Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_username_key" UNIQUE CONSTRAINT, btree (username) "newest_users" btree (created_on) Number of child tables: 10 (Use \d+ to list them.)
        
        В PostgreSQL есть опция constraint_exclusion. И если её настроить на «on» или «partition», PostgreSQL будет пропускать партиции, которые не могут содержать совпадающие строки.
        
        В моём Pg это установлено по умолчанию:
        
        $ show constraint_exclusion; constraint_exclusion ---------------------- partition (1 row)
        
        Итак, поскольку у всех моих партиций и базовой таблицы нет никаких осмысленных ограничений, так что любой запрос будет сканировать сразу все 11 таблиц (основную и 10 партиций):
        
        $ explain analyze select * from users where QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.29..89.98 rows=11 width=81) (actual time=0.009..0.013 rows=1 loops=1) -> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=1) Index Cond: (id = 123) -> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_2_pkey on users_2 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_3_pkey on users_3 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_4_pkey on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_5_pkey on users_5 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_6_pkey on users_6 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_7_pkey on users_7 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_8_pkey on users_8 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_9_pkey on users_9 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_10_pkey on users_10 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) Planning time: 1.321 ms Execution time: 0.087 ms (25 rows)
        
        Это не слишком эффективно, но мы можем поставить ограничение.
        
        Допустим, наши партиции были сформированы методом партиционирования по id, и в каждой партиции хранится 100,000 идентификаторов.
        
        Мы можем добавить несколько ограничений:
        
        $ alter table users_1 add constraint partition_check check (id >= 0 and id < 100000); $ alter table users_2 add constraint partition_check check (id >= 100000 and id < 200000); . $ alter table users_10 add constraint partition_check check (id >= 900000 and id < 1000000);
        
        Теперь повторяем предыдущий запрос:
        
        $ explain analyze select * from users where QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Append (cost=0.29..16.47 rows=2 width=66) (actual time=0.008..0.009 rows=1 loops=1) -> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1) Index Cond: (id = 123) -> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) Planning time: 1.104 ms Execution time: 0.031 ms (7 rows)
        
        Он сканирует только 2 таблицы: основную (в которой сейчас находятся все данные, и нет ограничений, так что её нельзя исключить) и подходящую партицию.
        
        Мы можем без проблем добавить подобные условия партиционирования по имени пользователя или created_on. Но посмотрите, что происходит, когда ключ партиционирования более сложный:
        
        $ alter table users_1 drop constraint partition_check, add constraint partition_check check (abs( hashtext(username) ) % 10 = 0); $ alter table users_2 drop constraint partition_check, add constraint partition_check check (abs( hashtext(username) ) % 10 = 1); . $ alter table users_10 drop constraint partition_check, add constraint partition_check check (abs( hashtext(username) ) % 10 = 9);
        
        На случай, если вы не в курсе, hashtext() берет строку и возвращает целое число в диапазоне от -2147483648 до 2147483647.
        Благодаря простой арифметике мы знаем, что abs(hashtext(string)) % 10 всегда будет выдавать значение в диапазоне 0..9, и его легко посчитать для любого параметра.
        
        Знает ли об этом PostgreSQL?
        
        $ explain analyze select * from users where username = 'depesz'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.29..89.98 rows=11 width=81) (actual time=0.023..0.023 rows=0 loops=1) -> Index Scan using users_username_key on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_1_username_key on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_2_username_key on users_2 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_3_username_key on users_3 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_4_username_key on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_5_username_key on users_5 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_6_username_key on users_6 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_7_username_key on users_7 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_8_username_key on users_8 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_9_username_key on users_9 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (username = 'depesz'::text) -> Index Scan using users_10_username_key on users_10 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (username = 'depesz'::text) Planning time: 1.092 ms Execution time: 0.095 ms (25 rows)
        
        Нет. Не знает. По сути, PostgreSQL может сделать автоматическое исключение партиций только для проверок, основанных на диапазоне (или равенстве). Ничего основанного на функциях. Даже простой модуль от числа – это уже перебор:
        
        $ alter table users_1 drop constraint partition_check, add constraint partition_check check ( id % 10 = 0); $ alter table users_2 drop constraint partition_check, add constraint partition_check check ( id % 10 = 1); . $ alter table users_10 drop constraint partition_check, add constraint partition_check check ( id % 10 = 9); $ explain analyze select * from users where QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.29..89.98 rows=11 width=81) (actual time=0.009..0.016 rows=1 loops=1) -> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1) Index Cond: (id = 123) -> Index Scan using users_1_pkey on users_1 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_2_pkey on users_2 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_3_pkey on users_3 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_4_pkey on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_5_pkey on users_5 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_6_pkey on users_6 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_7_pkey on users_7 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_8_pkey on users_8 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_9_pkey on users_9 (cost=0.15..8.17 rows=1 width=84) (actual time=0.000..0.000 rows=0 loops=1) Index Cond: (id = 123) -> Index Scan using users_10_pkey on users_10 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) Planning time: 0.973 ms Execution time: 0.086 ms (25 rows)
        
        Это печально. Потому что у ключей партиционирования, основанных на модулях чисел, есть одно огромное (на мой взгляд) преимущество – стабильное число партиций. Вам не придется создавать их в будущем, если только вы не надумаете произвести партиционирование заново при достижении какого-то более высокого объема данных.
        
        Значит ли это, что вы не можете использовать сложные (основанные на функциях или модулях от чисел) ключи партиционирования? Нет. Вы можете использовать их, но тогда запросы получатся более сложными:
        
        $ explain analyze select * from users where and id % 10 = 123 % 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Append (cost=0.29..16.48 rows=2 width=66) (actual time=0.010..0.011 rows=1 loops=1) -> Index Scan using users_pkey on users (cost=0.29..8.31 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=1) Index Cond: (id = 123) Filter: ((id % 10) = 3) -> Index Scan using users_4_pkey on users_4 (cost=0.15..8.17 rows=1 width=84) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: (id = 123) Filter: ((id % 10) = 3) Planning time: 1.018 ms Execution time: 0.033 ms (9 rows)
        
        Здесь я добавил еще одно условие, вот такое:
        
        id % 10 = 123 % 10
        
        PostgreSQL может в процессе разбора выражения переписать его:
        
        id % 10 = 3
        
        потому что он знает, что оператор % для целых чисел является иммутабельным. И теперь, как часть запроса, у меня есть точный ключ партиционирования – id % 10 = 3. Таким образом, Pg может использовать только те партиции, у которых либо нет ключа партиционирования (то есть, базовую таблицу), либо есть ключ, соответствующий запросу.
        
        Стоит ли вводить дополнительное усложнение – решать вам.
        
        Если вы предпочитаете не менять запросы, и вас не затруднит добавлять новые партиции время от времени, то вам стоит ознакомиться с PG Partition Manger, написанным моим бывшим коллегой Keith Fiske – это набор функций, которые вы запускаете вручную для определения партиций, и еще одна, которую вы запускаете по крону, и она берет на себя создание новых партиций для будущих данных.
        
        Я уже упоминал вставки, но не объяснил, как обойти проблему с вставками, которые должны добавиться к партициям.
        
        В целом, это работа для триггера. Pg_partman от Кита создает такие триггеры за вас, но я хочу, чтобы вы понимали, что происходит, и не использовали pg_partman как «черный ящик», а скорее как вспомогательный инструмент, который делает нудную работу за вас.
        
        Сейчас моя схема партиционирования основана на модуле от числа (насколько я знаю, partman так сделать не может), так что давайте напишем подходящую функцию триггера. Она будет вызываться при вставке данных в таблицу users и должна без ошибок перенаправлять вставку в соответствующую партицию. Итак, пишем:
        
        $ create function partition_for_users() returns trigger as $$ DECLARE v_parition_name text; BEGIN v_parition_name := format( 'users_%s', 1 + NEW.id % 10 ); execute 'INSERT INTO ' || v_parition_name || ' VALUES ( ($1).* )' USING NEW; return NULL; END; $$ language plpgsql;
        
        А теперь определение триггера:
        
        $ create trigger partition_users before insert on users for each row execute procedure partition_for_users();
        
        Попробуем добавить строку:
        
        $ insert into users (username, password, created_on, last_logged_on) values ( 'depesz', random_string( 20 ), now() - '2 years'::interval * random(), now() - '2 years'::interval * random() ); $ select currval('users_id_seq'); currval --------- 10003 (1 row)
        
        Посмотрим, видны ли данные:
        
        $ select * from users where username = 'depesz'; id | username | password | created_on | last_logged_on -------+----------+----------------------+-------------------------------+------------------------------- 10003 | depesz | bp7zwy8k3t3a37chf1hf | 2014-10-24 02:45:51.398824+02 | 2015-02-05 18:24:57.072424+01 (1 row)
        
        Выглядит хорошо, но где они находятся? В основной таблице?
        
        $ select * from only users where username = 'depesz'; id | username | password | created_on | last_logged_on ----+----------+----------+------------+---------------- (0 rows)
        
        Нет. Так может, в нужной партиции?
        
        $ select * from users_4 where username = 'depesz'; id | username | password | created_on | last_logged_on -------+----------+----------------------+-------------------------------+------------------------------- 10003 | depesz | bp7zwy8k3t3a37chf1hf | 2014-10-24 02:45:51.398824+02 | 2015-02-05 18:24:57.072424+01
        
        Да. Триггер сработал. Но у этого метода есть один недостаток. А именно – “RETURNING" не работает:
        
        $ insert into users (username, password, created_on, last_logged_on) values ( 'test', random_string( 20 ), now() - '2 years'::interval * random(), now() - '2 years'::interval * random() ) returning *; id | username | password | created_on | last_logged_on ----+----------+----------+------------+---------------- (0 rows)
        
        Так происходит, потому что, с точки зрения исполнителя, вставка ничего не вернула – триггер вернул NULL.
        
        Мне пока не удалось найти удачное решение этой проблемы. В своих кейсах я просто предпочитаю получать первоначальное значение ключа заранее, используя nextval(), а потом вставляю готовое значение – так что оно уже имеется после вставки:
        
        $ select nextval('users_id_seq'); nextval --------- 10005 (1 row) $ insert into users (id, username, password, created_on, last_logged_on) values ( 10005, 'test', random_string( 20 ), now() - '2 years'::interval * random(), now() - '2 years'::interval * random() );
        
        Ко всему этому есть одно уточнение. Маршрутизация всех вставок через триггер замедляет их, ведь для каждой строки PG нужно будет выполнять еще один “insert".
        
        Для срочных объемных вставок лучшим решением будет заставить их работать напрямую с партициями. Поэтому, например, вместо
        
        COPY users FROM stdin; . \.
        
        вы предварительно выясняете, сколько идентификаторов вам нужно, к примеру, вот таким способом:
        
        select nextval('users_id_seq') from generate_series(1, 100);
        
        А потом выдаете подходящие:
        
        COPY users_p1 FROM stdin; . \. COPY users_p2 FROM stdin; . \. .
        
        Не самый удобный способ, но он может быть полезен, если вы импортируете большие объемы данных в партиционированные таблицы.
        
        Итак, теперь вы должны понимать, что такое партиционирование, и как оно работает. Следующий вопрос в заголовке был: зачем?
        
        Ответить на него относительно легко: для обеспечения производительности или упрощения обслуживания.
        
        В качестве простого примера, возьмем таблицу users, в которой 1 миллиард строк (1,000,000,000).
        
        Поиск в ней будет прогрессивно дорожать даже с учётом индексирования, просто потому, что глубина индексов будет расти.
        Это видно даже в моей маленькой тестовой таблице.
        
        Давайте сбросим все партиции и триггер партиционирования:
        
        $ drop table users_1; $ drop table users_2; . $ drop table users_10; $ drop trigger partition_users on users;
        
        Теперь в таблице users 10,000 строк. Простой поиск по имени пользователя занимает 0.020мс – это лучшее время из трех попыток.
        
        Если я добавлю больше строк:
        
        $ insert into users (username, password, created_on, last_logged_on) select random_string( (random() * 4 + 5)::int4), random_string( 20 ), now() - '2 years'::interval * random(), now() - '2 years'::interval * random() from generate_series(1, 100000);
        
        тот же поиск займёт 0.025мс. Увеличение времени поиска на 0.005мс может быть небольшим, но у нас по-прежнему всего лишь 110,000 строк, и в системе нет других таблиц, так что вся таблица с индексами помещается в память.
        
        Конечно, ваше партиционирование должно быть осмысленным. Например, если вы обычно осуществляете поиск по имени пользователя, то бессмысленно делать партиционирование по id – Pg придется искать по всем партициям (это может стать осмысленным в будущем, но об этом я расскажу в самом конце статьи).
        
        То есть вам нужно определиться с тем, что вы обычно запрашиваете – будь то поиск по какому-то ключу или, возможно, вы обычно просматриваете только свежие данные? И партиционировать таким образом, чтобы ограничить количество партиций, которые Pg нужно просканировать.
        
        Важно то, что партиционирование делает вашу жизнь проще, особенно если вы в большей степени администратор баз данных, нежели программист. Любые задачи по техобслуживанию (создание индекса, vacuum, pg_reorg/pg_repack, pg_dump) могут быть эффективно разбиты на столько подзадач, сколько у вас имеется партиций. Так что вместо одной многочасовой транзакции для переупаковки большой таблицы у вас будет 20 гораздо более быстрых и использующих меньше места на диске транзакций, а результат, в целом, окажется тем же самым!
        
        Конечно, хорошими новостями дело не ограничивается. В партиционировании есть один большой недостаток: у вас не может быть внешних ключей, указывающих на партиционированную таблицу.
        
        Это просто не работает. Вы могли бы завести внешние ключи, указывающие прямо на партиции, но это (обычно) является бессмысленным.
        
        Большая ли это проблема лично для вас, зависит от вашего юзкейса. Мне кажется, что в большинстве случаев, когда мы достигаем таблиц достаточно больших для того, чтобы партиционирование было оправдано, приложение протестировано достаточно хорошо, и мы можем смириться с отсутствием внешнего ключа. К тому же, мы всегда можем добавить задачу в крон для тестирования наличия «плохих» значений.
        
        Теперь мы знаем, что такое партиционирование, как оно работает и зачем используется. Остался последний вопрос: как преобразовать таблицу в партиционированную. Обычно, приложение не создается с партиционированными таблицами – в начале это не имеет смысла. Но, вскоре, у вас появится какая-нибудь таблица с множеством строк и вы подумаете: «Надо было партиционировать её сразу при создании».
        
        Но может быть, мы всё ещё можем её партиционировать, когда приложение уже работает? С минимумом проблем?
        Давайте посмотрим. Для теста я создал базу данных pgbench на 97 ГБ. Большая её часть, 83 ГБ, находится в таблице pgbench_accounts, которая содержит 666,600,000 записей.
        
        Схема у этой таблицы вот такая:
        
        Table "public.pgbench_accounts" Column | Type | Modifiers ----------+---------------+----------- aid | integer | not null bid | integer | abalance | integer | filler | character(84) | Indexes: "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
        
        И все запросы к ней основываются на колонке aid, которая содержит значения от 1 до 666,600,000.
        
        Так что давайте партиционируем её, основываясь на диапазоне значений aid.
        
        Допустим, я помещу в каждую партицию 10 миллионов строк, тогда мне потребуется 67 партиций.
        
        Но как я могу проверить, что мои действия не нарушат работу? Очень просто. Я запущу pgbench в цикле. Мне не интересны точные отчеты о скоростях, достаточно информации о том, как сильно моя работа влияет на то, что делает pgbench.
        
        С этими мыслями я запустил функцию:
        
        $ while true do date pgbench -T 10 -c 2 bench done 2>&1 | tee pgbench.log
        
        Она будет прогонять 10-секундные тесты и сохранять статистические данные в файл, так что я смогу позже проследить взаимосвязь результата с моей работой по партиционированию.
        
        Когда всё готово, я создам партиции с проверками в нужных местах:
        
        do $$ declare i int4; aid_min INT4; aid_max INT4; begin for i in 1..67 loop aid_min := (i - 1) * 10000000 + 1; aid_max := i * 10000000; execute format('CREATE TABLE pgbench_accounts_p_%s ( like pgbench_accounts including all )', i ); execute format('ALTER TABLE pgbench_accounts_p_%s inherit pgbench_accounts', i); execute format('ALTER TABLE pgbench_accounts_p_%s add constraint partitioning_check check ( aid >= %s AND aid
        партиции готовы, и я могу убедиться, что проверки используются: $ explain analyze select * from pgbench_accounts where aid = 123; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.57..16.75 rows=2 width=224) (actual time=6.468..6.473 rows=1 loops=1) -> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..8.59 rows=1 width=97) (actual time=6.468..6.469 rows=1 loops=1) Index Cond: (aid = 123) -> Index Scan using pgbench_accounts_p_1_pkey on pgbench_accounts_p_1 (cost=0.14..8.16 rows=1 width=352) (actual time=0.004..0.004 rows=0 loops=1) Index Cond: (aid = 123) Planning time: 3.475 ms Execution time: 6.497 ms (7 rows) Теперь нужно добавить триггер-”маршрутизатор”: $ create function partition_for_accounts() returns trigger as $$ DECLARE v_parition_name text; BEGIN v_parition_name := format( 'pgbench_accounts_p_%s', 1 + ( NEW.aid - 1 ) / 10000000 ); execute 'INSERT INTO ' || v_parition_name || ' VALUES ( ($1).* )' USING NEW; return NULL; END; $$ language plpgsql; $ create trigger partition_users before insert on pgbench_accounts for each row execute procedure partition_for_accounts(); Это всё замечательно, но он сработает только для свежевставленных строк, а у меня уже 666 миллионов строк в исходной таблице. Что же с этим делать? Мне нужно их переместить. Это относительно просто в теории, но есть пара подводных камней: Ни в коем случае обе строки не должны быть одновременно видимы ни для каких транзакций (то есть, из основной таблицы и из партиции). Я не могу удалить все строки и вставить их в партиции, потому что это заблокирует всю базовую таблицу на время перемещения. Но мы можем использовать для этого psql (или ruby, perl, python – не важно), перемещая с каждой порцией лишь небольшое количество строк и, таким образом, блокируя основную таблицу на короткий момент времени. В целом, единичный запрос будет выглядеть так: with x as (delete from only pgbench_accounts where aid between .. and .. returning *) insert into appropriate_partition select * from x; Я выбрал размер порции – 1000, это достаточно малое значение, чтобы процесс не затянулся, и достаточно большое, чтобы итоговое количество порций не было чрезмерным (666 тысяч). Теперь давайте создадим пакетный файл: \pset format unaligned \pset tuples_only true \o /tmp/run.batch.migration.sql SELECT format( 'with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= %s AND aid Когда я запустил это в psql, он создал файл /tmp/run.batch.migration.sql, который достаточно объемен (97 ГБ), поскольку содержит 666,600 запросов, подобных этим: with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= 1 AND aid = 1001 AND aid = 2001 AND aid Теперь, когда всё подготовлено, я могу запустить процесс (конечно, с помощью «screen» или в «tmux», чтобы ничего не потерялось, если ssh соединение с сервером оборвется): $ psql -d bench -f /tmp/run.batch.migration.sql Это займет некоторое время. В случае с моей тестовой базой данных средний пакет обрабатывается за ~ 92мс, а значит, у меня впереди 17 часов перемещения данных. В реальности ушло всего 7 часов. Неплохо. По окончании таблица pgbench_accounts всё еще весит ~ 83Гб (думаю, моему диску не хватает скорости, чтобы справиться с pgbench, перемещением и vacuum). Но я проверил и, похоже, что все строки переместились в партиции: $ select count(*) from only pgbench_accounts; count ------- 0 (1 row) Как насчет скорости pgbench во время процесса перемещения? До начала работы по перемещению. После создания партиций. После создания триггера. Во время перемещения. phase | min | avg | max ---------+-----------+------------------+----------- Phase 1 | 28.662223 | 64.0359512839506 | 87.219148 Phase 2 | 21.147816 | 56.2721418360656 | 75.967217 Phase 3 | 23.868018 | 58.6375074477612 | 75.335558 Phase 4 | 5.222364 | 23.6086916565574 | 65.770852 (4 rows) Да, перемещение всё замедлило. Но отметьте, пожалуйста, что это обычный персональный компьютер с SATA дисками, а не SSD, постоянно находящийся под высокой нагрузкой – pgbench прогонял запросы так быстро, как мог. Кроме того, некоторое замедление произошло из-за того, что vacuum не слишком хорошо справляется с удалениями. По-моему, результат абсолютно приемлемый. По окончании я мог бы сделать: $ truncate only pgbench_accounts; И потом, чтобы проверить, всё ли ОК: $ select count(*) from pgbench_accounts; count ----------- 666600000 (1 row) Всё это было проделано без каких-либо ошибок и без прерывания работы «настоящего приложения». В конце добавлю, что партиционирование скоро (относительно) станет ещё круче. С недавних пор мы можем хранить партиции на разных серверах. И сейчас ведется работа (хотя вряд ли это обновление появится раньше версии 9.6) над тем, чтобы дать возможность проводить параллельные сканирования, что существенно улучшит весь процесс. Надеюсь, этот текст будет вам полезен. Какие еще аспекты партиционирования таблиц в PostgreSQL вы бы хотели обсудить? Будем рады дополнить программу докладов конференции PG Day'16 Russia наиболее интересными для вас темами! Мы уже открыли продажи early bird билетов, спешите зарегистрироваться по самой низкой цене! Партицирование таблиц в PostgreSQL: чек-лист для старта Часто возникает проблема: одна из таблиц в базе данных сильно выросла и время выполнения запросов к этой таблице увеличилось. Одним из вариантов решения такой проблемы в PostgreSQL является партицирование. В статье затронем не только техническую реализацию, но и опишем этапы подготовки к партицированию. Представим, что у нас есть батон хлеба. Порежем его на части. Каждый отрезанный кусочек — часть целого батона, но не сам батон. То есть мы поделили целое на части — это и есть партицирование. Батон как целое соответствует таблице, а кусочки батона как части — партициям этой таблицы. Заметим, что кусочки батона не равны между собой: одни тоньше, другие толще, у одних корочки нет только с одной стороны, у других — с двух сторон и так далее. Так же и с партициями: они могут содержать разное количество строк, а значит и размер на диске будет разным. Стоит отметить, что таблица партицируется построчно. Таблицу, которую партицируют, называют мастер-таблицей. Партиция имеет связь с мастер-таблицей и представляет собой обычную таблицу, то есть к ней можно обращаться точно так же, как к самой обычной таблице: SELECT, INSERT (если не нарушает ограничений, накладываемых на партицию), UPDATE, DELETE. Допустимы операции обслуживания (VACUUM, ANALYZE), а также операции по изменению схемы таблицы (ALTER), правда, с некоторыми ограничения (подробнее см. официальную документацию). Забегая вперёд, отметим, что при партицировании через наследование нет необходимости переписывать код приложения: обращения на чтение и запись выполняются к мастер-таблице. PostgreSQL самостоятельно определяет, к каким партициям следует обратиться. В то же время, если есть такая необходимость и приложение поддерживает подобную логику работы, можно напрямую обращаться к конкретным партициям. Какие проблемы может решить партицирование? ускорение выборки данных; ускорение вставки данных; упрощение удаления старых данных; упрощение обслуживания таблицы. Следует помнить, что партицирование — не панацея. Как и с любым другим инструментом, его применение не означает автоматически, что, например, проблема ускорения выборки или вставки данных будет решена. Результат сильно зависит от структуры таблицы, используемых индексов, критерия партицирования, размера партиций и прочих условий. Стандартный процесс при удалении старых данных: выполняем DELETE FROM по условию, а затем запускаем полную вакуумизацию (VACUUM FULL) с простоем (про полный вакуум без простоя см. pg_repack). Обе операции затратны по времени и нагрузке на сервер БД и слабо контролируемы: нельзя понять прогресс выполняемой операции. В случае партицирования (при условии, что партицирование выполнено по полю created_at либо аналогичному) удаление старых данных занимает 2-3 секунды и выполняется через удаление соответствующей партиции с помощью DROP TABLE. В случае упрощения обслуживания тот же полный вакуум гораздо быстрее отработает на сумме всех партиций, чем на одной большой таблице того же размера. В каких случаях партицирование не поможет либо никак не повлияет? время создания бэкапа; время восстановления из бэкапа; место на диске. В первых двух случаях время практически не изменится, так как выполняется полный перебор данных. В последнем случае: был 1 млрд строк, после партицирования имеем тот же 1 млрд. То есть занимаемое место на диске будет практически тем же. Некоторые ограничения и возможные проблемы по итогам партицирования партицируемая таблица должна быть достаточно большого размера; согласно документации, рекомендуется партицировать в случае, если таблица превосходит размер физической памяти; на партицируемую таблицу нельзя ссылаться через FOREIGN KEYS (можно, начиная с PostgreSQL 12); при этом обратное (партицируемая таблица ссылается на другие) допустимо; в некоторых случаях партицирование может ухудшить производительность на операциях чтения и записи; как указывалось выше, итоги партицирования сильно зависят от многих условий; в идеале запрос будет выполняться против одной партиции, но в худшем случае — затронет все партиции и, в зависимости от настроек PostgreSQL, увеличит время выполнения запроса, как в предыдущем пункте. Виды партицирования Декларативное партицирование появилось в PostgreSQL 10. Является встроенным (built-in) и наиболее производительным решением по партицированию, но имеет существенные ограничения: требует изначально создать таблицу, готовую к партицированию; нельзя партицировать уже существующую таблицу через ALTER TABLE; при добавлении и удалении партиций будет простой в работе таблицы из-за ACCESS EXCLUSIVE LOCK (начиная с PostgreSQL 12, режим блокировки более щадящий: можно использовать SELECT, но только без FOR UPDATE/SHARE); имеет ряд других ограничений по сравнению с другим типом партицирования. Партицирование через наследование является более гибким решением: можно партицировать уже существующую таблицу; нет даунтайма при добавлении и удалении партиций; можно задать любой произвольный критерий партицирования (об этом ниже); возможно множественное наследование (наследование схем более чем одной таблицы); в конце концов партицирование можно безболезненно отменить. Зачастую о партицировании задумываются, когда таблица сильно разрослась, поэтому подробнее остановимся на партицировании через наследование. Процедура партицирования через наследование 1. Создаём таблицы-партиции с использованием ключевого слова INHERITS: CREATE TABLE bigtable_y2021m03 ( CHECK (created_at >= '2021-03-01'::DATE AND created_at < '2021-04-01'::DATE) ) INHERITS (bigtable); CREATE TABLE bigtable_y2021m04 ( CHECK (created_at >= '2021-04-01'::DATE AND created_at < '2021-05-01'::DATE) ) INHERITS (bigtable); Указываем уникальное имя таблицы-партиции. Желательно в имени кратно указать критерий (условие) партицирования. Задаём критерий партицирования для каждой партиции через CHECK. Обратите внимание, условия для партиций должны быть уникальны, вставляемая строка данных должна удовлетворять условию только одной партиции. Поэтому в данном примере нижняя граница условия имеет знак нестрогого неравенства, а верхняя граница — строгого. Если планируется разнести имеющиеся в мастер-таблице данные по партициям, то в данном шаге создаём столько партиций, сколько необходимо, чтобы покрыть ими текущие данные плюс некоторое количество партиций для будущих данных на 2-3 месяца вперёд. Иначе создаём партиции только для будущих данных. 2. Добавляем индексы, такие же, как в мастер-таблице: ALTER TABLE ONLY bigtable_y2021m03 ADD CONSTRAINT bigtable_y2021m03__pkey PRIMARY KEY (id); CREATE INDEX bigtable_y2021m03__created_at ON bigtable_y2021m03 (created_at); ALTER TABLE ONLY bigtable_y2021m04 ADD CONSTRAINT bigtable_y2021m04__pkey PRIMARY KEY (id); CREATE INDEX bigtable_y2021m04__created_at ON bigtable_y2021m04 (created_at); 3. Создаём функцию, обеспечивающую партицирование: CREATE OR REPLACE FUNCTION bigtable_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.created_at >= '2021-03-01'::DATE AND NEW.created_at < '2021-04-01'::DATE ) THEN INSERT INTO bigtable_y2021m03 VALUES (NEW.*); ELSIF ( NEW.created_at >= '2021-04-01'::DATE AND NEW.created_at < '2021-05-01'::DATE ) THEN INSERT INTO bigtable_y2021m04 VALUES (NEW.); ELSE RAISE EXCEPTION 'Date out of range. Fix the bigtable_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; Цель данной функции — определение партиции, в которую будет вставлена новая строка, на основе заданных условий партицирования. Если такое условие найдено не будет, функция сгенерирует ошибку о невозможности выполнения подобной операции. 4. Подключаем функцию к мастер-таблице: CREATE TRIGGER insert_bigtable BEFORE INSERT ON bigtable FOR EACH ROW EXECUTE FUNCTION bigtable_insert_trigger(); Ключевой шаг. Триггер обеспечивает вставку данных в правильную партицию. При выборке данных PostgreSQL самостоятельно определяет, из каких партиций брать данные. 5. Разносим данные из мастер-таблицы по партициям: WITH x AS ( DELETE FROM ONLY bigtable WHERE created_at BETWEEN .. AND .. RETURNING *) INSERT INTO bigtable_y20XXmYY SELECT * FROM x; Итерационно по условию WHERE удаляем данные из мастер-таблицы и удалённые данные возвращаем (RETURNING) в качестве ответа на запрос DELETE FROM. Эти данные используем для вставки в нужную партицию. Обратите внимание на ключевое слово ONLY в операции удаления: без него запрос отработает не только по мастер-таблице, но и по всем партициям. Данное ключевое слово может быть полезно и при выборке данных ТОЛЬКО из мастер-таблицы, и при прочих операциях с мастер-таблицей, если не хотим, чтобы были затронуты партиции. Если планируется полностью удалить данные из мастер-таблицы без переноса, то пропускаем данный шаг. 6. Очищаем мастер-таблицу TRUNCATE ONLY bigtable; Здесь снова используем ключевое слово ONLY, таким образом данные в партициях затронуты не будут. В процессе работы запроса для мастер-таблицы будут созданы новые пустые дата-файлы и пустые файлы индексов, прежние файлы мастер-таблицы будут удалены. В данном шаге можно было бы использовать полный вакуум, однако, в отличие от TRUNCATE операция полного вакуума выполняет проверку на существование данных, что даже на формально пустой таблице может вызвать значительный простой. Отлично, партицирование выполнено, всё работает. Проходит три месяца, новые данные перестают писаться в таблицу. Всё потому, что мы забыли добавить в крон добавление новых партиций. Быстро лечим кроном и переписываем функцию (добавляем условия проверки). Через год у нас уже 100 партиций, и функция разрослась до неимоверных размеров. Обслуживать такую таблицу со временем стало сложнее. Давайте не будем так делать и вместо этого заставим PostgreSQL самостоятельно решать подобные проблемы. Объединим первые три шага процедуры в один. В итоге получим следующую процедуру: 1. Создаём расширенную функцию, обеспечивающую партицирование с автоматическим созданием партиций: CREATE OR REPLACE FUNCTION bigtable_insert_trigger() RETURNS TRIGGER AS $$ DECLARE current_date_part DATE; current_date_part_id TEXT; partition_table_name TEXT; first_day_of_month DATE; last_day_of_month DATE; BEGIN current_date_part := CAST(DATE_TRUNC('month', NEW.created_at) AS DATE); current_date_part_text := REGEXP_REPLACE(current_date_part::TEXT, '-','_','g'); partition_table_name := FORMAT('bigtable_%s', current_date_part_text::TEXT); IF (TO_REGCLASS(partition_table_name::TEXT) ISNULL) THEN first_day_of_month := current_date_part; last_day_of_month := current_date_part + '1 month'::INTERVAL; EXECUTE FORMAT( 'CREATE TABLE %I (' ' CHECK (created_at >= DATE %L AND created_at < DATE %L)' ') INHERITS (bigtable);' , partition_table_name, first_day_of_month, last_day_of_month); EXECUTE FORMAT( 'ALTER TABLE ONLY %1$I ADD CONSTRAINT %1$s__pkey PRIMARY KEY (id);' , partition_table_name); EXECUTE FORMAT( 'CREATE INDEX %1$s__created_at ON %1$I (created_at);' , partition_table_name); END IF; EXECUTE FORMAT('INSERT INTO %I VALUES ($1.*)', partition_table_name) USING NEW; RETURN NULL; END; $$ LANGUAGE plpgsql; В данном шаге на основе входных данных определяем имя партиции, к которой должны относится новые данные, и условия принадлежности к этой партиции (первые три строки блока BEGIN). Проверяем существование такой партиции (TO_REGCLASS), и если её нет — создаём соответствующую партицию и индексы к ней (EXECUTE FORMAT … CREATE TABLE, ALTER TABLE, CREATE INDEX). В конце вставляем новые данные в правильную партицию. Соответственно, если партиция существует, то просто вставляем в неё новые данные. 2. Подключаем функцию к мастер-таблице. 3. Разносим данные из мастер-таблицы по партициям. 4. Очищаем мастер-таблицу. Таким образом, вместо 6 шагов процедура сократилась до 4; нет необходимости в периодическом ручном, либо полуавтоматическом (через cron) создании партиций; функция существенно упростилась (да, всё так: 100 if’ов для каждой партиции против одного if’а на проверку существования партиции — это значительное упрощение); изменение функции требуется только при изменении схемы мастер-таблицы. Может показаться, что функция усложнилась, появились переменные, выполняются дополнительные шаги по идентификации имени и созданию партиции — это же всё очень медленно. Но, во-первых, партиции создаются не каждую секунду, эта операция фактически выполняется раз в месяц (в случае помесячного разбиения). Во-вторых, как далее будет видно на примерах, даже вставка данных легко может быть ускорена, несмотря на дополнительные накладные расходы. На этом можно было бы закончить статью, ведь с технической точки зрения мы всё сделали: создали функцию, прикрутили её к таблице, при необходимости перенесли данные. Однако возникает вопрос: по какому критерию мы партицировали таблицу? Как определить этот критерий? Почему выбрали шаг в один месяц? Что ещё мы не учли? Давайте разберёмся с этими вопросами! Собираем чек-лист партицирования Итак, вы решили выполнить партицирование таблицы и, вероятно, таким образом хотите решить какую-то проблему. Какой итоговый результат вы ожидаете от партицирования? Почему вы выбрали для решения этой проблемы партицирование? Рассматривали ли вы другие варианты решения? Может быть стоит выполнить рефакторинг кода? Или на сервере БД поставить более мощное железо? Или просто в таблице не хватает индекса? Иными словами, есть ли у вас цель? Без цели выполнение партицирования чревато простоями и ухудшением производительности. Если у вас есть цель — решаемая проблема, если прочие варианты решений были рассмотрены и отвергнуты по какой-либо причине, тогда можно попробовать реализовать партицирование. Повторюсь, партицирование — не панацея, и никто не даст никаких гарантий, что оно поможет. Далее нужно определить самый часто встречающийся запрос на чтение данных из партицируемой таблицы (в некоторых случаях требуется найти самый тяжёлый запрос). В этом могут помочь разработчики. В запросе смотрим, по какому условию выполняется выборка. Если в условии выборка выполняется по одному полю, то это поле — единственный кандидат в критерии партицирования. Если же полей в условии несколько, то либо пытаемся определить победителя, просмотрев топ запросов, либо все поля в условии считаем кандидатами в критерии партицирования. В любом спорном случае проверяем, какое из полей более всего согласуется с поставленной целью. Например, в самом часто встречающемся запросе выборка выполняется по полям id и created_at. Топ запросов не выявил победителя. Если цель партицирования — упростить удаление старых данных, тогда в качестве критерия партицирования выбираем поле created_at. Если же цель — ускорить выборку данных, — скорее всего, поле id будет лучшим выбором. Однако, есть и третий вариант: сделать комбинированный критерий из нескольких полей. Но, несмотря на то, что партицирование через наследование позволяет реализовать подобный сценарий, такого варианта следует опасаться. Во-первых, это значительно усложняет код функции, обеспечивающей партицирование. Во-вторых, это неизбежно ведёт к возрастанию накладных расходов при вставке данных и увеличивает время на выполнение операций вставки. В-третьих, общая производительность на чтение данных из таблицы после такого партицирования скорее всего значительно снизится. Но повторюсь, результат сильно зависит от структуры таблицы, используемых индексов, критерия партицирования, размера партиций и прочих условий. Вполне возможно, в вашем случае вариант с комбинированным критерием будет наиболее оптимальным решением. Следует помнить, что запросы, в которых выборка выполняется не по критерию партицирования, будут обращаться ко всем партициям и, следовательно, будут выполняться медленнее, чем без партицирования. Определившись с критерием партицирования, выбираем размер партиции. Причём, размер — это не только байты, килобайты и прочее, это может быть: количество строк в партиции; периоды времени хранения информации (день, неделя, месяц, год); диапазон идентификаторов записей в таблице (например, 1 млн id на партицию); иные варианты, которые считаете приемлемыми для оценки размера партиции. Иногда вместо размера партиции говорят о шаге партицирования: партицировали таблицу с шагом 1 месяц, 10 млн id, 100 млн строк и т.п. В простом случае в выборе размера партиции могут помочь самый частый запрос и критерий партицирования. Например, самый частый запрос делает выборку за последний месяц, а критерий партицирования — поле created_at. Тогда размером партиции можно выбрать период времени в 1 календарный месяц. В более сложных случаях, ответ не так очевиден. Например, в случае если критерий партицирования — поле id, то какой размер будет оптимальным: 1 млн id, 10 млн, 100 млн, 1 млрд? Если есть сомнения, выберите несколько возможных вариантов. Желательно ограничить количество таких вариантов числом 5. Оптимальный размер партиции 5-20ГБ, но в вашем случае может быть и другой размер. Когда вы оценили размер партиции и остановились на одном из вариантов, добавьте к нему ещё два: с бОльшим и мЕньшим шагом. Например, при партицировании с шагом в 1 месяц добавляем варианты с шагом 1 неделя и с шагом 2 месяца. Подобьём промежуточный итог имеется некая проблема, которую пытаемся решить; найден самый часто выполняющийся запрос на чтение (либо топ запросов); определен один или несколько критериев партиционирования; подобраны несколько вариантов шага партиционирования для каждого из критериев. Итого имеем N гипотез (К критериев x Ш шагов) партиционирования таблицы. Теперь для каждой гипотезы нужно подготовить: SQL-запрос на создание функции, обеспечивающей партицирование; SQL-запрос (одинаковый для всех гипотез) на добавление триггера к таблице; скрипт по переносу данных из мастер-таблицы по партициям; для случая отката изменений запросы на удаление функции и триггера, а также скрипт обратного переноса данных. Переходим к тестированию Ни в коем случае не пропускайте и не игнорируйте данный этап: именно здесь мы должны подтвердить или опровергнуть наши гипотезы. Оценивать результаты тестирования следует с точки зрения поставленной цели. Если цель — ускорение выборки данных, то гипотезы, в которых происходит замедление выборки, точно не подходят, так как не согласуются с целью. Если же цель — упрощение удаления данных, то сам факт партицирования реализует поставленную цель. Однако, в этом случае стоит подобрать такой размер партиции, чтобы замедление выборки было минимальным. Выполнять тестирование следует либо на таком же железе, как и на продакшн-базе, либо на максимально приближенном по характеристикам. В крайнем случае, если продакшн не нагружен, можно развернуть тестируемую базу рядом. Не следует тестировать на более мощном железе: при успешном тестировании реализация партицирования в проде может привести к отрицательным результатам. Как тестировать? Разворачиваем из бэкапа либо всю базу, либо только партицируемую таблицу со связанными таблицами. Добавляем функцию и триггер. С помощью подготовленного скрипта переносим данные из мастер-таблицы по партициям. Прогоняем пачку топ запросов, замеряем время выполнения (в этом поможет поможет команда \timing), смотрим план выполнения, сравниваем результаты с продом. Если тестирование показало отрицательные результаты, стоит пересмотреть критерий партицирования и шаг партицирования. Может быть, вы поставили слишком много целей: ускорить всё и вся и чтобы проще было удалять старые данные. Достигнуть таких целей возможно, но подобная комбинация схемы таблицы и логики работы приложения (а значит и структура запросов) встречается крайне редко. Нашли расхождение, исправили, повторно протестировали, получили успешные результаты — отлично, проверяем работу скриптов отката изменений и переходим к реализации на проде. В ином случае, к сожалению, партицирование не решает поставленной задачи, необходимо найти иное решение. Итого, получаем следующий чек-лист по партицированию: определяем цель; находим самый часто встречающийся запрос; определяем критерий партицирования; подбираем размер партиции; готовим скрипты для рассматриваемых гипотез: функцию, обеспечивающую партицирование; подключение триггера; скрипт переноса данных; скрипты отката изменений; Пара слов о параметрах СУБД, которые влияют на партицирование Наиболее критичные из них: параметр constraint_exclusion — должен быть включен, иначе план выполнения запроса будет сформирован неоптимально: операции выборки будут затрагивать все партиции без учёта налагаемых ограничений на партиции (PostgreSQL «забудет» о критерии партицирования); параметр max_parallel_workers_per_gather, отвечающий за максимальное количество воркеров, используемых для сборки результатов поиска от разных воркеров (при выполнении операции Gather или Gather Merge); в некоторых случаях может существенно ускорить работу запросов, в других — привести к деградации производительности всей СУБД. В следующей статье расскажу на примерах, как нам помогло партицирование в решении задач в Skyeng. Полезные ссылки Статья по партицированию из официальной документации PostgreSQL Как работает распараллеливание процессов в PostgreSQL Параметры, влияющие на производительность PostgeSQL Читать: Как в вайбере переслать сообщение другому человеку

Партиции и загрузка данных в оперативную память Текст научной статьи по специальности «Компьютерные и информационные науки»

Похожие темы научных работ по компьютерным и информационным наукам , автор научной работы — Юлия Владимировна Мельник

Текст научной работы на тему «Партиции и загрузка данных в оперативную память»

Манипуляции с партициями и кусками

DETACH PARTITION | PART​

DROP PARTITION | PART​

DROP DETACHED PARTITION | PART​

ATTACH PARTITION | PART​

ATTACH PARTITION FROM​

REPLACE PARTITION​

MOVE PARTITION TO TABLE​

CLEAR COLUMN IN PARTITION​

CLEAR INDEX IN PARTITION​

FREEZE PARTITION​

UNFREEZE PARTITION​

FETCH PARTITION | PART​

MOVE PARTITION | PART​

UPDATE IN PARTITION​

Пример​

Смотрите также​

DELETE IN PARTITION​

Пример​

Смотрите также​

Как задавать имя партиции в запросах ALTER​

Партиционирование в PostgreSQL – Что? Зачем? Как?

Партицирование таблиц в PostgreSQL: чек-лист для старта

Какие проблемы может решить партицирование?

В каких случаях партицирование не поможет либо никак не повлияет?

Некоторые ограничения и возможные проблемы по итогам партицирования

Виды партицирования

Процедура партицирования через наследование

Собираем чек-лист партицирования

Переходим к тестированию

Пара слов о параметрах СУБД, которые влияют на партицирование

Похожие записи

DETACH PARTITION | PART

DROP PARTITION | PART

DROP DETACHED PARTITION | PART

ATTACH PARTITION | PART

ATTACH PARTITION FROM

REPLACE PARTITION

MOVE PARTITION TO TABLE

CLEAR COLUMN IN PARTITION

CLEAR INDEX IN PARTITION

FREEZE PARTITION

UNFREEZE PARTITION

FETCH PARTITION | PART

MOVE PARTITION | PART

UPDATE IN PARTITION

Пример

Смотрите также

DELETE IN PARTITION

Пример

Смотрите также

Как задавать имя партиции в запросах ALTER