Что такое партиционирование таблиц
Партиционирование в PostgreSQL – Что? Зачем? Как?
Функцией партиционирования таблиц в PostgreSQL, к сожалению, активно пользуются пока не многие. На мой взгляд, очень достойно о ней рассказывает в своей работе Hubert Lubaczewski (depesz.com). Предлагаю вам еще один перевод его статьи! 
В последнее время я заметил, что всё чаще и чаще сталкиваюсь с кейсами, где можно было бы использовать партиционирование. И хотя, теоретически, большинство людей знает о его существовании, на самом деле эту фичу не слишком хорошо понимают, а некоторые её даже побаиваются.
Так что я постараюсь объяснить в меру своих знаний и возможностей, что это такое, зачем его стоит использовать и как это сделать.
Как вы наверняка знаете, в PostgreSQL есть таблицы, а в таблицах есть данные. Иногда это всего несколько строк, а иногда – миллиарды.
Партиционирование – это метод разделения больших (исходя из количества записей, а не столбцов) таблиц на много маленьких. И желательно, чтобы это происходило прозрачным для приложения способом.
Одной из редко используемых фич PostgreSQL является тот факт, что это объектно-реляционная база данных. И «объект» здесь ключевое слово, потому что объекты (или, скорее, классы) знают то, что называется «наследование». Именно это используется для партиционирования.
Давайте посмотрим, о чём речь.
Я создам обычную таблицу users:
Теперь, для полноты картины, давайте добавим несколько строк и дополнительный индекс:
Итак, у нас получилась тестовая таблица:
С какими-то случайными данными:
Теперь, когда таблица готова, я могу создать партиции, что означает – наследованные таблицы:
Таким образом, у нас получилась новая таблица, у которой есть определенные интересные свойства:
Теперь у нас есть все индексы и ограничения, но мы потеряли информацию о наследовании. Но мы можем добавить её позже с помощью:
Мы могли бы сделать это в один шаг, но тогда появляются разные неприятные уведомления:
В любом случае, теперь у нас есть две таблицы – основная и первая партиция.
Если я произведу какое-либо действие – выборка/обновление/удаление – с пользователями, обе таблицы будут просканированы:
Но если я обращусь к партиции напрямую, запрос будет выполнен только на ней:
Если бы мы хотели, мы могли бы обратиться только к таблице пользователей без её партиций, используя ключевое слово ONLY:
Вы могли заметить, что я сказал, что выборка/обновление/удаление работает на всех партициях. А что насчет вставок? Вставке требуется добавить куда-нибудь данные, так что она всегда работает так, как будто было использовано ONLY. Поэтому, если мне нужно добавить строку в users_1, я должен сделать так:
Выглядит как-то не слишком хорошо, но не волнуйтесь, есть способы это обойти.
Давайте попробуем произвести настоящее партиционирование. Для начала нам нужно решить, каким будет ключ партиционирования – другими словами, по какому алгоритму будут выбираться партиции.
Есть пара наиболее очевидных:
Почему стоит использовать одну схему, а не другую? Давайте разберемся в их достоинствах и недостатках:
Для начала мне нужно создать побольше партиций:
Теперь у таблицы users есть 10 партиций:
В PostgreSQL есть опция constraint_exclusion. И если её настроить на «on» или «partition», PostgreSQL будет пропускать партиции, которые не могут содержать совпадающие строки.
В моём Pg это установлено по умолчанию:
Итак, поскольку у всех моих партиций и базовой таблицы нет никаких осмысленных ограничений, так что любой запрос будет сканировать сразу все 11 таблиц (основную и 10 партиций):
Это не слишком эффективно, но мы можем поставить ограничение.
Допустим, наши партиции были сформированы методом партиционирования по id, и в каждой партиции хранится 100,000 идентификаторов.
Мы можем добавить несколько ограничений:
Теперь повторяем предыдущий запрос:
Он сканирует только 2 таблицы: основную (в которой сейчас находятся все данные, и нет ограничений, так что её нельзя исключить) и подходящую партицию.
Мы можем без проблем добавить подобные условия партиционирования по имени пользователя или created_on. Но посмотрите, что происходит, когда ключ партиционирования более сложный:
Знает ли об этом PostgreSQL?
Нет. Не знает. По сути, PostgreSQL может сделать автоматическое исключение партиций только для проверок, основанных на диапазоне (или равенстве). Ничего основанного на функциях. Даже простой модуль от числа – это уже перебор:
Это печально. Потому что у ключей партиционирования, основанных на модулях чисел, есть одно огромное (на мой взгляд) преимущество – стабильное число партиций. Вам не придется создавать их в будущем, если только вы не надумаете произвести партиционирование заново при достижении какого-то более высокого объема данных.
Значит ли это, что вы не можете использовать сложные (основанные на функциях или модулях от чисел) ключи партиционирования? Нет. Вы можете использовать их, но тогда запросы получатся более сложными:
Здесь я добавил еще одно условие, вот такое:
PostgreSQL может в процессе разбора выражения переписать его:
потому что он знает, что оператор % для целых чисел является иммутабельным. И теперь, как часть запроса, у меня есть точный ключ партиционирования – id % 10 = 3. Таким образом, Pg может использовать только те партиции, у которых либо нет ключа партиционирования (то есть, базовую таблицу), либо есть ключ, соответствующий запросу.
Стоит ли вводить дополнительное усложнение – решать вам.
Если вы предпочитаете не менять запросы, и вас не затруднит добавлять новые партиции время от времени, то вам стоит ознакомиться с PG Partition Manger, написанным моим бывшим коллегой Keith Fiske – это набор функций, которые вы запускаете вручную для определения партиций, и еще одна, которую вы запускаете по крону, и она берет на себя создание новых партиций для будущих данных.
Я уже упоминал вставки, но не объяснил, как обойти проблему с вставками, которые должны добавиться к партициям.
В целом, это работа для триггера. Pg_partman от Кита создает такие триггеры за вас, но я хочу, чтобы вы понимали, что происходит, и не использовали pg_partman как «черный ящик», а скорее как вспомогательный инструмент, который делает нудную работу за вас.
Сейчас моя схема партиционирования основана на модуле от числа (насколько я знаю, partman так сделать не может), так что давайте напишем подходящую функцию триггера. Она будет вызываться при вставке данных в таблицу users и должна без ошибок перенаправлять вставку в соответствующую партицию. Итак, пишем:
А теперь определение триггера:
Попробуем добавить строку:
Посмотрим, видны ли данные:
Выглядит хорошо, но где они находятся? В основной таблице?
Нет. Так может, в нужной партиции?
Да. Триггер сработал. Но у этого метода есть один недостаток. А именно – “RETURNING» не работает:
Так происходит, потому что, с точки зрения исполнителя, вставка ничего не вернула – триггер вернул NULL.
Мне пока не удалось найти удачное решение этой проблемы. В своих кейсах я просто предпочитаю получать первоначальное значение ключа заранее, используя nextval(), а потом вставляю готовое значение – так что оно уже имеется после вставки:
Ко всему этому есть одно уточнение. Маршрутизация всех вставок через триггер замедляет их, ведь для каждой строки PG нужно будет выполнять еще один “insert».
Для срочных объемных вставок лучшим решением будет заставить их работать напрямую с партициями. Поэтому, например, вместо
вы предварительно выясняете, сколько идентификаторов вам нужно, к примеру, вот таким способом:
А потом выдаете подходящие:
Не самый удобный способ, но он может быть полезен, если вы импортируете большие объемы данных в партиционированные таблицы.
Итак, теперь вы должны понимать, что такое партиционирование, и как оно работает. Следующий вопрос в заголовке был: зачем?
Ответить на него относительно легко: для обеспечения производительности или упрощения обслуживания.
В качестве простого примера, возьмем таблицу users, в которой 1 миллиард строк (1,000,000,000).
Поиск в ней будет прогрессивно дорожать даже с учётом индексирования, просто потому, что глубина индексов будет расти.
Это видно даже в моей маленькой тестовой таблице.
Давайте сбросим все партиции и триггер партиционирования:
Теперь в таблице users 10,000 строк. Простой поиск по имени пользователя занимает 0.020мс – это лучшее время из трех попыток.
Если я добавлю больше строк:
тот же поиск займёт 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 записей.
Схема у этой таблицы вот такая:
И все запросы к ней основываются на колонке aid, которая содержит значения от 1 до 666,600,000.
Так что давайте партиционируем её, основываясь на диапазоне значений aid.
Допустим, я помещу в каждую партицию 10 миллионов строк, тогда мне потребуется 67 партиций.
Но как я могу проверить, что мои действия не нарушат работу? Очень просто. Я запущу pgbench в цикле. Мне не интересны точные отчеты о скоростях, достаточно информации о том, как сильно моя работа влияет на то, что делает pgbench.
С этими мыслями я запустил функцию:
Она будет прогонять 10-секундные тесты и сохранять статистические данные в файл, так что я смогу позже проследить взаимосвязь результата с моей работой по партиционированию.
Когда всё готово, я создам партиции с проверками в нужных местах:
партиции готовы, и я могу убедиться, что проверки используются:
Теперь нужно добавить триггер-”маршрутизатор”:
Это всё замечательно, но он сработает только для свежевставленных строк, а у меня уже 666 миллионов строк в исходной таблице. Что же с этим делать?
Мне нужно их переместить. Это относительно просто в теории, но есть пара подводных камней:
Но мы можем использовать для этого psql (или ruby, perl, python – не важно), перемещая с каждой порцией лишь небольшое количество строк и, таким образом, блокируя основную таблицу на короткий момент времени.
В целом, единичный запрос будет выглядеть так:
Я выбрал размер порции – 1000, это достаточно малое значение, чтобы процесс не затянулся, и достаточно большое, чтобы итоговое количество порций не было чрезмерным (666 тысяч).
Теперь давайте создадим пакетный файл:
Когда я запустил это в psql, он создал файл /tmp/run.batch.migration.sql, который достаточно объемен (97 ГБ), поскольку содержит 666,600 запросов, подобных этим:
Теперь, когда всё подготовлено, я могу запустить процесс (конечно, с помощью «screen» или в «tmux», чтобы ничего не потерялось, если ssh соединение с сервером оборвется):
Это займет некоторое время. В случае с моей тестовой базой данных средний пакет обрабатывается за
92мс, а значит, у меня впереди 17 часов перемещения данных.
В реальности ушло всего 7 часов. Неплохо.
По окончании таблица pgbench_accounts всё еще весит
83Гб (думаю, моему диску не хватает скорости, чтобы справиться с pgbench, перемещением и vacuum).
Но я проверил и, похоже, что все строки переместились в партиции:
Как насчет скорости pgbench во время процесса перемещения?
Да, перемещение всё замедлило. Но отметьте, пожалуйста, что это обычный персональный компьютер с SATA дисками, а не SSD, постоянно находящийся под высокой нагрузкой – pgbench прогонял запросы так быстро, как мог.
Кроме того, некоторое замедление произошло из-за того, что vacuum не слишком хорошо справляется с удалениями. По-моему, результат абсолютно приемлемый.
По окончании я мог бы сделать:
И потом, чтобы проверить, всё ли ОК:
Всё это было проделано без каких-либо ошибок и без прерывания работы «настоящего приложения».
В конце добавлю, что партиционирование скоро (относительно) станет ещё круче. С недавних пор мы можем хранить партиции на разных серверах. И сейчас ведется работа (хотя вряд ли это обновление появится раньше версии 9.6) над тем, чтобы дать возможность проводить параллельные сканирования, что существенно улучшит весь процесс.
Надеюсь, этот текст будет вам полезен.
Какие еще аспекты партиционирования таблиц в PostgreSQL вы бы хотели обсудить? Будем рады дополнить программу докладов конференции PG Day’16 Russia наиболее интересными для вас темами! Мы уже открыли продажи early bird билетов, спешите зарегистрироваться по самой низкой цене!
Партицирование и боль MySQL
На днях пришлось делать партицирование. В таблицу каждый день будет добавляться миллион записей и ожидается, что эти записи будут браться для предоставления различных отчетов и графиков.
Сразу скажу, что делал это не первый раз, до этого успешно делал партицирование у сайта на битрикс примерно вот таким образом:
Шаг 1. Убираем AUTO INCREMENT из таблицы b_iblock_element.
ALTER TABLE b_iblock_element MODIFY ID INT(11) NOT NULL
ALTER TABLE b_iblock_element DROP PRIMARY KEY
ALTER TABLE b_iblock_element ADD CONSTRAINT id_iblock_id PRIMARY KEY (ID,IBLOCK_ID)
ALTER TABLE b_iblock_element MODIFY ID INT(11) NOT NULL AUTO_INCREMENT
ALTER TABLE b_iblock_element PARTITION BY HASH(IBLOCK_ID) PARTITIONS 10;
Все довольно просто. Функция по которой идет разбиение может содержать ключи, но все эти ключи должны быть в PRIMARY KEY.
Теперь же мне предстояло разбить другую таблицу, и хотелось бы ее разбить сразу по 2 полям: по типу и дате. Причем дату хотелось разбить по месяцам и данные хранить не больше года.
Шаг 1. Первое с чем я столкнулся, это то, что пришлось удалить все foreign keys. Mysql с ними при партицировании не работает.
ALTER TABLE table_name DROP CONSTRAINT fk_key_name
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
ALTER TABLE table_name CHANGE `date` `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
Шаг 3. Пришлось добавить в PRIMARY KEY поля даты и типа.
Шаг 4. Я решил использовать subpartitions для разбиения по двум полям
ALTER TABLE table_data PARTITION BY LIST( MONTH(`date`) )
SUBPARTITION BY HASH(`type_id`) SUBPARTITIONS 10
(
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
PARTITION p3 VALUES IN (3),
PARTITION p4 VALUES IN (4),
PARTITION p5 VALUES IN (5),
PARTITION p6 VALUES IN (6),
PARTITION p7 VALUES IN (7),
PARTITION p8 VALUES IN (8),
PARTITION p9 VALUES IN (9),
PARTITION p10 VALUES IN (10),
PARTITION p11 VALUES IN (11),
PARTITION p12 VALUES IN (12)
);
Но хоть и MySQL поддерживает функцию MONTH для партицирования и я использую LIST/RANGE вместе с HASH/KEY, то есть MySQL subpartitions поддерживает только на этом уровне, у меня ничего не получилось.
Команда explain partitions запроса показала, что когда я выбираю интервал по дате, используются все партишены. Гугл сказал мне, что я не могу использовать функцию MONTH, а только функцию TO_DAYS, YEAR и TO_SECONDS. Пришлось все таки делать партиции статичными:
ALTER TABLE table_data PARTITION BY RANGE(to_days(`date`))
SUBPARTITION BY HASH(`type_id`) SUBPARTITIONS 10
(
PARTITION p01 VALUES LESS THAN (to_days(‘2015-10-01’)),
PARTITION p02 VALUES LESS THAN (to_days(‘2015-11-01’)),
PARTITION p03 VALUES LESS THAN (to_days(‘2015-12-01’)),
PARTITION p04 VALUES LESS THAN (to_days(‘2016-01-01’)),
PARTITION p05 VALUES LESS THAN (to_days(‘2016-02-01’)),
PARTITION p06 VALUES LESS THAN (to_days(‘2016-03-01’)),
PARTITION p07 VALUES LESS THAN (to_days(‘2016-04-01’)),
PARTITION p08 VALUES LESS THAN (to_days(‘2016-05-01’)),
PARTITION p09 VALUES LESS THAN (to_days(‘2016-06-01’)),
PARTITION p10 VALUES LESS THAN (to_days(‘2016-07-01’)),
PARTITION p11 VALUES LESS THAN (to_days(‘2016-08-01’)),
PARTITION p12 VALUES LESS THAN (to_days(‘2016-09-01’)),
PARTITION p13 VALUES LESS THAN (to_days(‘2016-10-01’)),
PARTITION p14 VALUES LESS THAN (to_days(‘2016-11-01’)),
PARTITION p15 VALUES LESS THAN (to_days(‘2016-12-01’)),
PARTITION p16 VALUES LESS THAN (to_days(‘2017-01-01’)),
PARTITION p18 VALUES LESS THAN (to_days(‘2017-02-01’)),
PARTITION p19 VALUES LESS THAN (to_days(‘2017-03-01’)),
PARTITION p20 VALUES LESS THAN (to_days(‘2017-04-01’)),
PARTITION pmaxval VALUES LESS THAN MAXVALUE
);
Команда explain partitions наконец-то показала, что не станет использовать кучу таблиц. Хочется надеяться, что это будет работать, и спасибо MySQL за принесенную боль.
Партицирование таблиц в 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:
Указываем уникальное имя таблицы-партиции. Желательно в имени кратно указать критерий (условие) партицирования. Задаём критерий партицирования для каждой партиции через CHECK. Обратите внимание, условия для партиций должны быть уникальны, вставляемая строка данных должна удовлетворять условию только одной партиции. Поэтому в данном примере нижняя граница условия имеет знак нестрогого неравенства, а верхняя граница — строгого.
Если планируется разнести имеющиеся в мастер-таблице данные по партициям, то в данном шаге создаём столько партиций, сколько необходимо, чтобы покрыть ими текущие данные плюс некоторое количество партиций для будущих данных на 2-3 месяца вперёд. Иначе создаём партиции только для будущих данных.
2. Добавляем индексы, такие же, как в мастер-таблице:
3. Создаём функцию, обеспечивающую партицирование:
Цель данной функции — определение партиции, в которую будет вставлена новая строка, на основе заданных условий партицирования. Если такое условие найдено не будет, функция сгенерирует ошибку о невозможности выполнения подобной операции.
4. Подключаем функцию к мастер-таблице:
Ключевой шаг. Триггер обеспечивает вставку данных в правильную партицию. При выборке данных PostgreSQL самостоятельно определяет, из каких партиций брать данные.
5. Разносим данные из мастер-таблицы по партициям:
Итерационно по условию WHERE удаляем данные из мастер-таблицы и удалённые данные возвращаем (RETURNING) в качестве ответа на запрос DELETE FROM. Эти данные используем для вставки в нужную партицию. Обратите внимание на ключевое слово ONLY в операции удаления: без него запрос отработает не только по мастер-таблице, но и по всем партициям. Данное ключевое слово может быть полезно и при выборке данных ТОЛЬКО из мастер-таблицы, и при прочих операциях с мастер-таблицей, если не хотим, чтобы были затронуты партиции.
Если планируется полностью удалить данные из мастер-таблицы без переноса, то пропускаем данный шаг.
6. Очищаем мастер-таблицу
Здесь снова используем ключевое слово ONLY, таким образом данные в партициях затронуты не будут. В процессе работы запроса для мастер-таблицы будут созданы новые пустые дата-файлы и пустые файлы индексов, прежние файлы мастер-таблицы будут удалены. В данном шаге можно было бы использовать полный вакуум, однако, в отличие от TRUNCATE операция полного вакуума выполняет проверку на существование данных, что даже на формально пустой таблице может вызвать значительный простой.
Отлично, партицирование выполнено, всё работает.
Проходит три месяца, новые данные перестают писаться в таблицу. Всё потому, что мы забыли добавить в крон добавление новых партиций. Быстро лечим кроном и переписываем функцию (добавляем условия проверки). Через год у нас уже 100 партиций, и функция разрослась до неимоверных размеров. Обслуживать такую таблицу со временем стало сложнее. Давайте не будем так делать и вместо этого заставим PostgreSQL самостоятельно решать подобные проблемы.
Объединим первые три шага процедуры в один. В итоге получим следующую процедуру:
1. Создаём расширенную функцию, обеспечивающую партицирование с автоматическим созданием партиций:
В данном шаге на основе входных данных определяем имя партиции, к которой должны относится новые данные, и условия принадлежности к этой партиции (первые три строки блока 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.