Оператор LIKE предназначен несколько для других целей. В его обязанности входит здесь и сейчас создать новую несвязанную с базовой пустую таблицу с такой же структурой, как и базовая таблица. Причём для LIKE под структурой таблицы понимается только кол-во полей, имя поля и типа поля. Всё остальное: дефолтные значения полей, описания/комментарии полей, индексы, констрэйнты и прочее - не копируются в новую таблицу. При желании их можно скопировать через INCLUDING. Мы к этому ещё вернёмся :)
Зачем отдельно добавлять CHECK, когда это можно было сделать в одном запросе: CREATE TABLE bigtable_y2021m03 ( LIKE bigtable including all, CHECK (created_at >= '2021-03-01'::DATE AND created_at < '2021-04-01'::DATE) ) INHERITS (bigtable);
LIKE не предполагает взаимодействие с базовой таблицей, с которой была скопирована структура, после создания новой таблицы. Поэтому если какие-то время спустя потребовалось добавить новый индекс, этот индекс надо создать как для мастер-таблицы, так и для каждой партиции. Тут без вариант. И LIKE тут никак не поможет, с какими бы ключами он не вызывался. И да, INHERITS не наследует индексы, поэтому помимо добавления самих индексов, нужно будет поправить партицирующую функцию, это не такая уж и сложная операция.
LIKE, вызванный одновременно с INHERITS, сгенерит нотисы/замечания о том, что поля, скопированные через LIKE, объединяются со полями, наследованными через INHERITS. Не критично, но в то же время неприятно.
Создание таблицы с LIKE bigtable INCLUDING INDEXES ведёт к желаемому эффекту: созданию индексов. Однако, имена индексов будут заданы плюс-минус произвольно (читай: далеко не всегда можно предсказать, какое имя будет у индекса). Если имя индекса не критично, тогда да, можно использовать указанный вариант. Но если имена индексов контролируются и в дальнейшем планируется выполнять манипуляции с ними, INHERITS лучше не совмещать с LIKE.
Использование LIKE bigtable INCLUDING ALL вообще опасная вещь. Например, в случае если в мастер-таблице заданы констрэйнты, то INHERITS более чем достаточно. Однако, если хотя бы один констрэйнт задан флагом NO INHERIT, то оператор INHERIT не будет его наследовать, но благодаря оператору LIKE с INCLUDING ALL или INCLUDING CONSTRAINTS скопирует его в новую наследуемую таблицу. В итоге получаем, что в наследство от мастер-таблицы получили то, что не должно было наследоваться.
И напоследок: партицирующая функция будет выполнятся от имени пользователя-владельца базы (если не задано иное). Владельцу базы дополнительных прав раздавать не нужно. Однако, если в базу ходят и другие пользователи, то бэст-практисом является не явная раздача прав на вновь создаваемые объекты, а настройка DEFAULT PRIVILEGES, на основе которых постгрес самостоятельно выдаст необходимые права на новые объекты.
Партицирование не предполагает, что критерий партицирования со временем будет изменяться. Это один из моментов, которые нужно учитывать при планирования партицирования и проведении тестирования. За всё приходится платить. Однако при необходимости в случае использования партицирования через наследование критерий партицирования можно изменить.
Более быстрый вариант, но с даунтаймом:
запретить запись в партицированную таблицу
изменить партицирующую функцию на соответствие новым критериям партицирования
перенести данные из старых партиций в новые путём запроса на вставку в мастер-таблицу (сработает триггер, который вставит данные в новые партиции)
удалить старые партиции
разрешить запись
Более долгий, но без даунтайма:
отключить триггер на вставку данных
перенести данные из партиций в мастер-таблицу
удалить старые партиции
изменить партицирующую функцию на соответствие новым критериям партицирования
подключить триггер обратно
перенести данные из мастер-таблицы по партициям путём вставки в мастер-таблицу (сработает триггер, который вставит данные в новые партиции)
Сам так не делал, и подозреваю, что это не всегда применимо из-за зависимостей исходной таблицы.
Декларативное партицирование существующей таблицы - довольно сложный кейс с кучей подводных камней и гарантированным даунтаймом, как минимум на время передеплоя и переименования/переключения на другое имя таблицы.
"секционирование" или "партицирование"
Пользовался для в основном англоязычными источниками, ни в одном не было sectioned table / sectioning и во всех было partitioned table / partitioning :) . Вполне естественно для меня использовать партицирование, а не секционирование.
PARTITION BY относится к декларативному партицированию, которое не рассматривается в статье, т.к. тему "давайте спроектируем базу/таблицу" не планировалась. Изначально хотели рассказать, как в компании решали сложные кейсы через партицирование с конкретными примерами, но в одну статью всё не поместилось.
Во-первых, для того, чтобы быть уверенным, что данные в партициях точно не пересекаются. А во-вторых, поскольку партиция - это обычная таблица, никто не запрещает туда вставить данные, относящиеся к другой партиции.
А что с индексами, у каждой партиции могут быть свои независимые индексы
Настоятельно рекомендуется, чтобы у каждой партиции были одинаковые индексы, иначе запросы будут выполняться непредсказуемо.
Например, у всех партиций есть индекс по полю updated_at, а у одной партиции такого индекса нет. Тогда вместо того, чтобы быстро отфильтровать данные по индексу для поля updated_at, для этой партиции будет выполняться полный перебор данных, что очень медленно.
или можно только один большой на мастер?
Если таблицу полностью партицировали и разнесли данные по партициям, то в мастер-таблице никаких данных не будет (только если не оставили часть данных, чтобы удалить их через месяц-два как устаревшие). Соответственно, индексы мастер-таблицы будут пустыми. Это с одной стороны. С другой стороны, каждая партиция - это "самостоятельная" таблица со своим набором индексов, и наличие или отсутствие индексов в мастер-таблице никак не влияет ни на данные, ни на индексы в партициях. Ни мастер-таблица, ни индексы мастер-таблицы ничего не знают о конкретных данных, содержащихся в партициях.
Для attach partition нужно заранее создать check constraint. Тогда не требуется длительных блокировок для проверки значений. Описано в документации.
Декларативное партицирование упомянул, но не рассматривал в данной статье, т.к. о партицировании обычно задумываются, когда всё плохо и нужно с этим что-то делать. Да, можно применить декларативное партицирование, но формально это будет не та же самая таблица, а соседняя. В этом случае точно будет даунтайм при переключении на новую таблицу, вопрос только в продолжительности этого даунтайма.
Важно помнить случаи, когда партицирование замедляет выполнение запросов. Например, если ранее большая таблица могла быстро соединяться вложенными циклами по индексу с маленькой, то после её партицирования это работать не будет эффективно из-за необходимости сканировать все патриции.
Сильно зависит от структуры таблицы и используемых запросов. Если в запросе не используется поле, по которому проводили партицирование, тогда да, будут сканироваться все таблицы. Всё это выявляется на стадии подготовки к партицированию, одним из ключевых этапов которой является тестирование.
Ну и конечно же партицирование через наследование работает значительно медленнее, в особенности если секций много.
Спору нет, однако, партицирование через наследование реализовать проще и быстрее. За всё приходится платить.
Ох, давайте разбираться по порядку:
Оператор LIKE предназначен несколько для других целей. В его обязанности входит здесь и сейчас создать новую несвязанную с базовой пустую таблицу с такой же структурой, как и базовая таблица. Причём для LIKE под структурой таблицы понимается только кол-во полей, имя поля и типа поля. Всё остальное: дефолтные значения полей, описания/комментарии полей, индексы, констрэйнты и прочее - не копируются в новую таблицу. При желании их можно скопировать через INCLUDING. Мы к этому ещё вернёмся :)
Зачем отдельно добавлять CHECK, когда это можно было сделать в одном запросе:
CREATE TABLE bigtable_y2021m03 (LIKE bigtable including all,
CHECK (created_at >= '2021-03-01'::DATE
AND created_at < '2021-04-01'::DATE)
) INHERITS (bigtable);
LIKE не предполагает взаимодействие с базовой таблицей, с которой была скопирована структура, после создания новой таблицы. Поэтому если какие-то время спустя потребовалось добавить новый индекс, этот индекс надо создать как для мастер-таблицы, так и для каждой партиции. Тут без вариант. И LIKE тут никак не поможет, с какими бы ключами он не вызывался. И да, INHERITS не наследует индексы, поэтому помимо добавления самих индексов, нужно будет поправить партицирующую функцию, это не такая уж и сложная операция.
LIKE, вызванный одновременно с INHERITS, сгенерит нотисы/замечания о том, что поля, скопированные через LIKE, объединяются со полями, наследованными через INHERITS. Не критично, но в то же время неприятно.
Создание таблицы с
LIKE bigtable INCLUDING INDEXESведёт к желаемому эффекту: созданию индексов. Однако, имена индексов будут заданы плюс-минус произвольно (читай: далеко не всегда можно предсказать, какое имя будет у индекса). Если имя индекса не критично, тогда да, можно использовать указанный вариант. Но если имена индексов контролируются и в дальнейшем планируется выполнять манипуляции с ними, INHERITS лучше не совмещать с LIKE.Использование
LIKE bigtable INCLUDING ALLвообще опасная вещь. Например, в случае если в мастер-таблице заданы констрэйнты, то INHERITS более чем достаточно. Однако, если хотя бы один констрэйнт задан флагом NO INHERIT, то оператор INHERIT не будет его наследовать, но благодаря оператору LIKE сINCLUDING ALLилиINCLUDING CONSTRAINTSскопирует его в новую наследуемую таблицу. В итоге получаем, что в наследство от мастер-таблицы получили то, что не должно было наследоваться.И напоследок: партицирующая функция будет выполнятся от имени пользователя-владельца базы (если не задано иное). Владельцу базы дополнительных прав раздавать не нужно. Однако, если в базу ходят и другие пользователи, то бэст-практисом является не явная раздача прав на вновь создаваемые объекты, а настройка DEFAULT PRIVILEGES, на основе которых постгрес самостоятельно выдаст необходимые права на новые объекты.
Партицирование не предполагает, что критерий партицирования со временем будет изменяться. Это один из моментов, которые нужно учитывать при планирования партицирования и проведении тестирования. За всё приходится платить. Однако при необходимости в случае использования партицирования через наследование критерий партицирования можно изменить.
Более быстрый вариант, но с даунтаймом:
запретить запись в партицированную таблицу
изменить партицирующую функцию на соответствие новым критериям партицирования
перенести данные из старых партиций в новые путём запроса на вставку в мастер-таблицу (сработает триггер, который вставит данные в новые партиции)
удалить старые партиции
разрешить запись
Более долгий, но без даунтайма:
отключить триггер на вставку данных
перенести данные из партиций в мастер-таблицу
удалить старые партиции
изменить партицирующую функцию на соответствие новым критериям партицирования
подключить триггер обратно
перенести данные из мастер-таблицы по партициям путём вставки в мастер-таблицу (сработает триггер, который вставит данные в новые партиции)
транкейтим мастер-таблицу для очистки места
Декларативное партицирование существующей таблицы - довольно сложный кейс с кучей подводных камней и гарантированным даунтаймом, как минимум на время передеплоя и переименования/переключения на другое имя таблицы.
Пользовался для в основном англоязычными источниками, ни в одном не было sectioned table / sectioning и во всех было partitioned table / partitioning :) . Вполне естественно для меня использовать партицирование, а не секционирование.
PARTITION BY относится к декларативному партицированию, которое не рассматривается в статье, т.к. тему "давайте спроектируем базу/таблицу" не планировалась. Изначально хотели рассказать, как в компании решали сложные кейсы через партицирование с конкретными примерами, но в одну статью всё не поместилось.
Уточните, что вы имеете ввиду?
Во-первых, для того, чтобы быть уверенным, что данные в партициях точно не пересекаются. А во-вторых, поскольку партиция - это обычная таблица, никто не запрещает туда вставить данные, относящиеся к другой партиции.
Настоятельно рекомендуется, чтобы у каждой партиции были одинаковые индексы, иначе запросы будут выполняться непредсказуемо.
Например, у всех партиций есть индекс по полю updated_at, а у одной партиции такого индекса нет. Тогда вместо того, чтобы быстро отфильтровать данные по индексу для поля updated_at, для этой партиции будет выполняться полный перебор данных, что очень медленно.
Если таблицу полностью партицировали и разнесли данные по партициям, то в мастер-таблице никаких данных не будет (только если не оставили часть данных, чтобы удалить их через месяц-два как устаревшие). Соответственно, индексы мастер-таблицы будут пустыми. Это с одной стороны. С другой стороны, каждая партиция - это "самостоятельная" таблица со своим набором индексов, и наличие или отсутствие индексов в мастер-таблице никак не влияет ни на данные, ни на индексы в партициях. Ни мастер-таблица, ни индексы мастер-таблицы ничего не знают о конкретных данных, содержащихся в партициях.
Например, такие:
перейти на более мощное железо
распределить данные по другим таблицам (нормализовать данные)
очистить старые данные и/или перенести их в долговременное хранилище
проверить наличие необходимых индексов
проверить, давно ли выполнялся автовакуум
В статье как раз рассмотрено партицирование по диапазонам. Или вы о чём-то другом спрашиваете?
Да, всё так. Далее по пунктам :)
Декларативное партицирование упомянул, но не рассматривал в данной статье, т.к. о партицировании обычно задумываются, когда всё плохо и нужно с этим что-то делать. Да, можно применить декларативное партицирование, но формально это будет не та же самая таблица, а соседняя. В этом случае точно будет даунтайм при переключении на новую таблицу, вопрос только в продолжительности этого даунтайма.
Сильно зависит от структуры таблицы и используемых запросов. Если в запросе не используется поле, по которому проводили партицирование, тогда да, будут сканироваться все таблицы. Всё это выявляется на стадии подготовки к партицированию, одним из ключевых этапов которой является тестирование.
Спору нет, однако, партицирование через наследование реализовать проще и быстрее. За всё приходится платить.