Комментарии 19
Пример плохой :) Это сколько же документов должно быть в базе чтобы партиционорование имело смысл :) Изначально партиционирование было придумано дла ОЧЕНЬ больших таблиц
Не могу судить насчет постгре но в оракле с партиционированием надо быть достаточно осторожным.
Если doc_id до рефакторинга являлся первичным ключом, то его одного достаточно для связи вспомогательных таблиц с основной таблицей, и doc_id+doc_date не нужны. Разве нет?
После партиционирования возможна ситуация, когда в разных партициях записи могут содержать одинаковые значения doc_id, поэтому используется пара doc_id+doc_date (это также отражение требования «контроль возложить на СУБД»).
Вот всё-таки вопрос про модель данных. Есть исходная таблица.
create table docs_initial (
doc_id integer not null,
doc_date date not null,
doc_number varchar(15) not null,
doc_text text not null,
constraint docs_initial_pk primary key (doc_id)
);Её можно партицировать по-разному, но лучше партицировать так
create table doc (
id integer not null,
date date not null,
number varchar(15) not null,
text text not null,
constraint doc_pk primary key (id)
) partition by range (id);
create index idx_doc__number on doc using btree (number);
create index idx_doc__date on doc using btree (date);Тогда PK сохранится как есть и будет поддерживать глобальную уникальность.
Второй уникальный индекс по номеру документа, конечно, поддерживать уникальность глобально уже не сможет. Чтобы смог, нужна другая "проекция" этой таблицы, партицированная уже по doc_number.
create table doc_number (
number varchar(15) not null
constraint doc_number_pk primary key (number)
) partition by hash (number);Синхронизацию данных между таблицами можно будет сделать в триггерах, при этом возможно будет делать изменение любой из "проекций", а вторая будет поддерживаться консистентной автоматически.
Именно при таком подходе обеспечен минимальный объем переделок в базе и приложениях.
В статье есть фактические ошибки. Но если честно, самое большое разочарование, что не пишете про то, зачем именно партицировать, хотя заголовок статьи это подразумевает. Это исключительно простота обслуживания, масштабирование и снижение стоимости хранения.
Каким образом партиционирование по id поможет улучшить выполнение запросов, которые зависят от даты документа (фильтруют по ней)?
Синхронизация на триггерах имеет ряд недостатков. Из-за отсутствия FK, вероятно, не получится возложить контроль синхронности данных на СУБД.
Если id serial, то будет высокая прямая корреляция между значениями id и датой. Поэтому на каждой из партиций надо добавить через check её фактический диапазон дат, тогда предикат по дате будет отключать ненужные партиции при constraint_exclusion=on.
Допустим id не serial, а, например uuid. Зачем он вообще такой нужен? Он будет ничуть не лучше pk(number).
Корреляция может быть, а может и быть, поле типа serial определяет default, но не запрещает вставлять другие значения. При партиционировании по id может понадобиться прогнозировать соотношение дат и диапазонов id, устанавливать/корректировать диапазоны.
Прогнозировать соотношение дат и диапазонов id не нужно, поскольку все партиции будут одинаковы по числу строк и примерно равны по объёму. Это число строк можно выбрать таким, чтобы последняя горячая партиция гарантированно оставалась в shared buffers. Конечно же, это число может меняться со временем в зависимости от доступных ресурсов. Что же касается вставки других значений = это ведь при большом изменениях данных требуется, и это само изменение по времени должно случиться сильно позже, чем даты в добавляемых документах. Если так, то правильнее создавать под такую операцию отдельную партицию (или несколько) со своими границами.
Касательно второго утверждения про FK.
В этой модели возможно создание FK
Ошибочно думать, что FK надёжнее триггеров, поскольку сам FK реализован на основе триггеров
Hidden text
create table doc (
id integer not null,
date date not null,
number varchar(15) not null,
text text not null,
constraint pk_doc primary key (id)
) partition by range (id);
create index idx_doc__number on doc using btree (number);
create index idx_doc__date on doc using btree (date);
create table doc_0_100000 partition of doc for values from (0) to (100000);
create table doc_100000_200000 partition of doc for values from (100000) to (200000);
create table doc_200000_300000 partition of doc for values from (200000) to (300000);
create table doc_300000_400000 partition of doc for values from (300000) to (400000);
create table doc_400000_500000 partition of doc for values from (400000) to (500000);
create table doc_500000_600000 partition of doc for values from (500000) to (600000);
create table doc_600000_700000 partition of doc for values from (600000) to (700000);
create table doc_700000_800000 partition of doc for values from (700000) to (800000);
create table doc_800000_800000 partition of doc for values from (800000) to (900000);
create table doc_900000_1000000 partition of doc for values from (900000) to (1000000);create table doc_number (
number varchar(15) not null,
constraint pk_doc_number primary key (number)
) partition by hash (number);
create table doc_number_p0 partition of doc_number for values with (modulus 10, remainder 0);
create table doc_number_p1 partition of doc_number for values with (modulus 10, remainder 1);
create table doc_number_p2 partition of doc_number for values with (modulus 10, remainder 2);
create table doc_number_p3 partition of doc_number for values with (modulus 10, remainder 3);
create table doc_number_p4 partition of doc_number for values with (modulus 10, remainder 4);
create table doc_number_p5 partition of doc_number for values with (modulus 10, remainder 5);
create table doc_number_p6 partition of doc_number for values with (modulus 10, remainder 6);
create table doc_number_p7 partition of doc_number for values with (modulus 10, remainder 7);
create table doc_number_p8 partition of doc_number for values with (modulus 10, remainder 8);
create table doc_number_p9 partition of doc_number for values with (modulus 10, remainder 9);alter table doc add constraint fk_doc__doc_number__number foreign key(number) references doc_number(number);
insert into doc_number(number) select number from doc;alter table doc add constraint fk_doc__doc_number__number foreign key(number) references doc_number(number);Добавим ещё партицию и данные в doc
create table doc_1000000_1100000 partition of doc for values from (1000000) to (1100000);
insert into doc(id, date, number, text) select id.id, '2022-02-24'::date + id.id * interval '1s', left(encode(decode(md5(id.id::text), 'hex'), 'base64'), 15), md5(hashint8(id.id)::text) from generate_series(1000000, 1099999, 1) as id(id);
ОШИБКА: INSERT или UPDATE в таблице "doc_1000000_1100000" нарушает ограничение внешнего ключа "fk_doc__doc_number__number"Выключим триггеры, повторим, отвалидируем
set session_replication_role = 'replica';
insert into doc(id, date, number, text) select id.id, '2022-02-24'::date + id.id * interval '1s', left(encode(decode(md5(id.id::text), 'hex'), 'base64'), 15), md5(hashint8(id.id)::text) from generate_series(1000000, 1099999, 1) as id(id);
--INSERT 0 100000
reset session_replication_role;
alter table doc validate constraint fk_doc__doc_number__number;
--ALTER TABLEИтого, в примере выше получено неконсистентное состояние с живым FK за счёт выключения триггеров.
Любопытна политика OTUS: начинаешь серьёзное техническое обсуждение и сразу игнор. В вашем обучении такой же подход с игнором?
В указанной модели несколько документов могут ссылаться на одну запись во "второй проекции" таблице doc_number?
Могут, это должно быть решено организацией триггера. При добавлении в doc он сперва добавит строку в партицированную таблицу doc_number, а затем в doc.
Равнозначность триггеров и FK по надёжности я уже доказал в предыдущем сообщении, этот момент Вы не прокомментировали.
Дополнительно обозначу, что эту модель можно превратить из партицированной в шардированную, добавив в doc_number столбец doc_id и превратив doc_number в распределённый индекс по номеру документа.
После партиционирования возможна ситуация, когда в разных партициях записи могут содержать одинаковые значения doc_id, поэтому используется пара doc_id+doc_date
Получается, поле doc_date мутабельно? По номеру doc_number мы получаем связку id+date, по ней идем уже в нужную партицию, и при этом где-то там могут лежать "мусорные" записи с тем же id, но другой датой?
А партицирование по doc_number не выглядит эффективней? Если предположить, что doc_number (или какой-то иной счетчик) идут в нарастающей последовательности, и запрашиваются / редактируются обычно самые свежие документы, то это также бы решило проблему "локализации" доступа.
плюсы партицирования по сравнению с индексом
гораздо лучше ускоряет низкоселективную выборку
не замедляет инсерт
не занимает место
обмен и добавление партиций - супер эффективная вещь для масс-инсерта
Минусы:
апдейт поля партицирования невозможен
индексов много - партицировангие только одно
для высокоселективных выборок - размер метаданных увеличивается критически
Хорошая статья о том, как делать не надо. Единственное, что не жалко было времени на чтение, чтобы пришлось подумать, почему так плохо сделали реорганизацию. Фраза "в разных партициях записи могут содержать одинаковые значения doc_id" - просто шедевральна. Обновлять дату тоже придется в двух таблицах одновременно, возможно перемещая записи из партиции в партицию? В ORACLE на эту тему есть глобальный индекс. Поэтому при поиске по ID ищется запись в одно индексе, а не в 50 маленьких кусочках. Надеюсь тут тоже такое потом появится (в MS SQL тоже, как в PG, нельзя сделать глобальный уникальный индекс поверх всех партиций). Также понравилась идея "Recheck Cond" в плане запроса. Кажется, что это гениально. PS: только изучаю Posgresql, что-то нравится, что-то вымораживает.
PS: в MS SQL на эту тему сделал кластеризованный ключ по PrDate+PrId (в итоге данные гарантированно посортированы по дате). А по PrId отдельный некластеризованный уникальный индекс. Благодаря этому большинство запросов (все они с фильтром за период) смотрят только таблицу, только нужные записи. Ходить через индекс по дате было бы менее гуманно. Большая часть запросов "за сегодня" - вообще смотрят только небольшой кусок данных. Плюс эта же дата попадает по все другие индексы, что тоже часто удобно, и обычно не мешает. Никаких партиций не потребовалось. По сути это получилось партиционирование по дню.
В PG, к сожалению, в настоящее время нет кластеризованного индекса, который постоянно поддерживал бы порядок записей.
Фраза "в разных партициях записи могут содержать одинаковые значения doc_id" - просто шедевральна.
При партиционировании по дате - да, технически могут и СУБД это допустит. "Прилететь" такие данные могут, отказываться от такого контроля не хотелось бы.
Когда помогает партиционирование, пример в PostgreSQL