Что такое партиции в sql
Перейти к содержимому

Что такое партиции в sql

  • автор:

$PARTITION (Transact-SQL)

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

Синтаксис

[ database_name. ] $PARTITION.partition_function_name(expression)

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

database_name
Имя базы данных, которая содержит функцию секционирования.

partition_function_name
Имя существующей функции секционирования, которая применяется к набору значений столбцов секционирования.

expression
Выражение, тип данных которого должен совпадать или иметь возможность неявного преобразования в тип данных соответствующего столбца секционирования. Аргумент expression также может быть именем столбца секционирования, который в настоящее время используется в partition_function_name.

Типы возвращаемых данных

int

Remarks

$PARTITION возвращает значение типа int в диапазоне от 1 до числа секций функции секционирования.

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

Примеры

A. Получение номера секции для набора значений столбцов секционирования

Этот пример создает функцию секционирования RangePF1 с помощью RANGE LEFT для разделения таблицы или индекса на четыре секции. $PARTITION используется для определения того, что значение 10 , представляющее столбец секционирования RangePF1 , попадет в секцию 1 таблицы.

CREATE PARTITION FUNCTION RangePF1 ( INT ) AS RANGE LEFT FOR VALUES (10, 100, 1000) ; GO SELECT $PARTITION.RangePF1 (10) ; GO

Б. Получение количества строк в каждой непустой секции секционированной таблицы или представления

В этом примере показано, как использовать $PARTITION для возврата количества строк в каждой секции таблицы, содержащей данные.

  • Создает схему секционирования RangePS1 для функции секционирования RangePF1 .
  • Создает таблицу dbo.PartitionTable в схеме секционирования RangePS1 со столбцом секционирования col1 .
  • Вставляет четыре строки в таблицу dbo.PartitionTable . В зависимости от определения функции секционирования эти строки будут вставлены в секции 2 и 3. Секции 1 и 4 останутся пустыми.
  • Запрашивает dbo.PartitionTable и использует $PARTITION.RangePF1(col1) в предложении GROUP BY для запроса количества строк в каждой секции, содержащей данные.

Чтобы выполнить этот пример, необходимо сначала создать функцию секционирования RangePF1 с помощью кода в предыдущем примере.

CREATE PARTITION SCHEME RangePS1 AS PARTITION RangePF1 ALL TO ('PRIMARY') ; GO CREATE TABLE dbo.PartitionTable (col1 int PRIMARY KEY, col2 char(10)) ON RangePS1 (col1) ; GO INSERT dbo.PartitionTable (col1, col2) VALUES ((1,'a row'),(100,'another row'),(500,'another row'),(1000,'another row')) SELECT $PARTITION.RangePF1(col1) AS Partition, COUNT(*) AS [COUNT] FROM dbo.PartitionTable GROUP BY $PARTITION.RangePF1(col1) ORDER BY Partition ; GO

Запрос SELECT должен возвратить следующий результат:

Partition (Раздел) COUNT
2 1
3 3

Строки не возвращаются для секций с номерами 1 и 4, которые существуют, но не содержат данных.

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

Следующий пример иллюстрирует получение всех строк, которые содержит секция 3 таблицы PartitionTable .

SELECT col1, col2 FROM dbo.PartitionTable WHERE $PARTITION.RangePF1(col1) = 3 ;

Вы получите следующие результаты:

col1 col2
101 another row
500 a third row
501 a fourth row

Дальнейшие шаги

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

  • Секционированные таблицы и индексы
  • CREATE PARTITION FUNCTION (Transact-SQL)
  • Изменение функции секционирования
  • Изменение схемы секционирования
  • sys.partition_functions (Transact-SQL)
  • sys.partition_schemes (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

Обратная связь

Отправить и просмотреть отзыв по

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

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

  • 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

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

  1. Создайте таблицу, если она ещё не существует. Запрос на создание можно взять из .sql файла (замените в нём ATTACH на CREATE ).
  2. Скопируйте данные из директории data/database/table/ внутри резервной копии в директорию /var/lib/clickhouse/data/database/table/detached/ .
  3. С помощью запросов 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'

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

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

  1. Загружает партицию/кусок с указанного шарда. Путь к шарду задается в секции FROM (‘path-in-zookeeper’). Обратите внимание, нужно задавать путь к шарду в ZooKeeper.
  2. Помещает загруженные данные в директорию detached таблицы table_name . Чтобы прикрепить эти данные к таблице, используйте запрос ATTACH PARTITION | PART.
ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PARTITION 201902;
  1. 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 миллионов строк в исходной таблице. Что же с этим делать?

            Мне нужно их переместить. Это относительно просто в теории, но есть пара подводных камней:

            1. Ни в коем случае обе строки не должны быть одновременно видимы ни для каких транзакций (то есть, из основной таблицы и из партиции).
            2. Я не могу удалить все строки и вставить их в партиции, потому что это заблокирует всю базовую таблицу на время перемещения.

            Но мы можем использовать для этого 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 во время процесса перемещения?

            1. До начала работы по перемещению.
            2. После создания партиций.
            3. После создания триггера.
            4. Во время перемещения.
             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 билетов, спешите зарегистрироваться по самой низкой цене!

            Партиционирование таблиц в mySQL

            Начиная с версии 5.1 mySQL поддерживает горизонтальное партицирование таблиц. Что это такое? Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.. На нижнем уровне для myISAM таблиц, это физически разные файлы, по 3 на каждую партицию (описание таблицы, файл индексов, файл данных). Для innoDB таблиц в конфигурации по умолчанию – разные пространства таблиц в файлах innoDB (не забываем, что innoDB позволяет настраивать индивидуальные хранилища на уровне баз данных или даже конкретных таблиц).

            Как это выглядит?

            CREATE TABLE orders_range (
            customer_surname VARCHAR(30),
            store_id INT,
            salesperson_id INT,
            order_date DATE,
            note VARCHAR(500)
            ) ENGINE = MYISAM
            PARTITION BY RANGE( YEAR(order_date) ) (
            PARTITION p_old VALUES LESS THAN(2008),
            PARTITION p_2008 VALUES LESS THAN(2009),
            PARTITION p_2009 VALUES LESS THAN(MAXVALUE)
            );

            Что мы получаем? Первая «таблица» будет хранить данные за «архивный» период, до 2008го года, вторая — за 2008й год, и «третья» — все остальное.

            Самое вкусное — запросы при этом совершенно не надо переписывать/оптимизировать:

            select * from orders_range where order_date='2009-08-01';

            И вот что при этом происходит:

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

            Более того, ускорение достигается даже в случае выполнения запросов, затрагивающих все данные во всех партициях — ведь в этом случае сначала происходит первичная «обработка» таблиц по меньше, потом данные объединяются и производятся финальные вычисления. Так вот как раз «первые» этапы, в данном случае будут происходить гораздо быстрее.

            Какие еще есть преимущества?

            Главным преимуществом я бы назвал тот факт, что партиция с «оперативными» данными (т.е. последними, по которым наиболее часто происходит выборка) имеют минимальный размер, и как следствие, могут постоянно находится в оперативной памяти.

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

            Какие способы «разделения» данных предоставляет mySQL?

            По диапазону значений

            PARTITION BY RANGE (store_id) (
            PARTITION p0 VALUES LESS THAN (10),
            PARTITION p1 VALUES LESS THAN (20),
            PARTITION p3 VALUES LESS THAN (30)
            );

            По точному списку значений

            PARTITION BY LIST(store_id) (
            PARTITION pNorth VALUES IN (3,5,6,9,17),
            PARTITION pEast VALUES IN (1,2,10,11,19,20)
            )

            Зачем, спросите вы? Разбивать на партиции необходимо либо исходя из соображений оптимизации выборки (что чаще) либо исходя из соображений оптимизации записи (реже). Соответственно, идеальный вариант — это когда вы разбиваете таблицу на максимально возможное количество партиций так, что бы 90% всех выборок происходило в пределах одной партиции. И если у вас сложная логика выборки (например, объекты расположенные в северных кварталах города, ID которых идут в разнобой) то иногда есть смысл перечислять их принудительно.

            PARTITION BY HASH(store_id)
            PARTITIONS 4;

            Вы никак не управляете партицированием, просто указываете, по какому полю строить хеш и сколько «подтаблиц» создавать. Зачем? Гораздо быстрее происходит выборка по указанному полю. В некоторых случаях позволяет достигнуть «равномерного разброса» и ускорения записи данных.

            Почти то же самое что и HASH, но более логично — по ключу.

            PARTITION BY KEY(s1)
            PARTITIONS 10;

            Т.е. выборка по указанному ключевому полю происходит максимально эффективно.

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

            Нет вертикального партицирования. Это когда разные столбцы (поля) находятся в разных «подтаблицах». Поскольку иногда это бывает полезно, вы можете достичь этого самостоятельно, пусть даже не так прозрачно: разделить таблицу на две, связав их по первичному ключу. Если вам совсем хочется красоты — можете дополнительно создать по ним VIEW, например для того что бы не переписывать старые части кода.

            Зачем это делать? Например, в таблице, где у вас в основном числа и даты, есть одно поле VARCHAR (255) для комментариев, которое используется на порядок реже чем остальные поля. В случае если его вынести в другую таблицу, то мы получим фиксированный размер строки (mySQL сможет совершенно точно вычислять позицию нужной строки по индексу в файле данных). Таблица станет более устойчивой к сбоям в случае внештатных ситуаций (опять же, из-за фиксированного размера строки). Ну и существенно уменьшится сам размер таблицы.

            И заканчивая статью приведу пример более «реального» партицирования таблиц — помесячно. Так как LIST/RANGE принимают только целочисленные значения, то надо немного исхитрится:

            PARTITION BY RANGE( TO_DAYS(order_date) ) (
            PARTITION y2009m1 VALUES LESS THAN( TO_DAYS('2009-02-01') ),
            PARTITION y2009m2 VALUES LESS THAN( TO_DAYS('2009-03-01') ),
            PARTITION y2009m3 VALUES LESS THAN( TO_DAYS('2009-04-01') )
            );

            PS: В mysql всегда приходится немного «исхитриться», так что скучно с ней не будет никогда, а мы в свою очередь никогда не останемся без работы 🙂

            Похожие публикации:
            1. Как вставить формулу в презентацию powerpoint
            2. Как использовать zoom h4n как микрофон
            3. Как обработать сообщение telegram bot c
            4. Как убрать точки в списке html

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *