Привет! Меня зовут Васильев Виктор, я DBA в компании CoMagic. В этой статье на реальных примерах расскажу, как можно сэкономить время разработчика, администратора баз данных и ресурсы сервера(ов), используя утилиту pg_rebuild_table. Сопровождая большие, высоконагруженные системы, с бо’льшей вероятностью каждый сталкивался с кейсами, о которых будет рассказано дальше. Некоторые технические подробности пройду без детализации, чтобы сильно не усложнять и не делать статью очень громоздкой. Лучше отвечу на вопросы в комментариях.

Какие кейсы разберем:

• Чистка неактуальных данных в 100+ таблицах.

• Экономия дискового пространства, RAM и CPU.

• Изменение порядка колонок в больших таблицах.

• Изменение типа данных колонок в большой таблице.

• Перестроение таблицы без долгих транзакций.

В современном мире есть тенденция переводить системы на микро-сервисную, слабосвязанную архитектуру с маленькими базами данных, где четко разделяются OLTP и OLAP профили нагрузки. Но не всегда мир идеален, и часто приходится работать с тем, что отлично существует многие годы и при этом развивается и обрастает новыми фичами.

Речь пойдет про обслуживание таблиц в монолите большого размера. Несмотря на то, что на дворе уже 15-ая версия PostgreSQL, рассказывать буду в контексте 12-ой версии. Итак, в нашем распоряжении шардированный по приложениям (далее “app”) кластер баз данных (далее “БД”) на PostgresSQL 12.5 версии, где БД является отдельной нодой, в которой тонны бизнесового кода в хранимых функциях на plpgsql, около 1000 партицированных и не партицированных таблиц некоторые из них размером 500 ГБ и более, данные которых хранятся в разрезе app. Общий объем одной ноды около 20 ТБ. Отдельно развернут прокси-кластер БД, который знает про все ноды и место расположения данных каждого app. Перечисленные кластера хранятся на дорогих серверах с SSD-дисками. Все сервисы выполняют запросы на прокси БД и в свою очередь прокси маршрутизирует запросы на ноду, где лежат данные app, посредством маршрутизатора запросов plexor (про plexor подробнее в презентации). Профиль нагрузки принимают как OLTP, так и OLAP.

С появлением в компании хранилища BI-storage нагрузка OLAP планомерно переводится туда. Для поддержки около реалтаймового обновления данных в таблицах использовали встроенную логическую репликацию с созданием публикации по изменениям данных для списка таблиц (см. картинку «Кластер баз данных с хранилищем BI-storage»).

Не секрет, что большую базу данных сложно обслуживать. Особенно расстраивает, когда утилизация ресурсов сервера приближается к предельным показателям. Мы часто с этим сталкиваемся. Отсюда появляется задача разделить БД на две в разрезе app. Это значит, что для половины app данные должны остаться в текущей ноде БД, а другая половина данных должна уехать в новую ноду БД на новый сервер. Такое решение позволит размазать нагрузку на два сервера и уменьшить размер БД в два раза.

Подходов для решения задачи оказалось немало. Остановился на одном из самых простых. Поднять реплику БД на другом сервере, используя pg_basebackup, в определенный момент сделать ему promote и сказать прокси БД, что появилась новая нода и теперь половина app переехали туда. Решение выглядело простым, и было реализовано, нагрузка распределилась на два сервера, в итоге немного выдохнули. Физически данные по app задублировались и на обеих нодах остались, как балласт.

Не все проблемы удалось решить, да еще и новые появились:

• Место на сервере по прежнему заканчивается. требуется очистить таблицы от ненужных данных по app и обеспечить максимальную плотность хранения данных.

• С появлением новой ноды, первичные ключи на основе сиквенса int4 стали скручиваться еще быстрее. И таких таблиц немало, а допускать пересечения значений в первичных ключах на шардированной БД нельзя  —  требуется изменять тип данных первичного ключа на int8.

Чистка неактуальных данных в 100+ таблицах без простоя в работе.

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

Зная, как работают публикации, становится понятно, что комбинация команд delete from [table_name] where app_id in (...);, с последующим запуском pg_repack для очистки пустот в данных, нам не поможет. Да и делать минимум по два прохода по таблицам долгая и муторная затея.

Возникает вопрос: как очистить данные в таблицах и не отправить команды удаления в публикациях?

В 15-ой версии PostgreSQL появилась возможность добавлять предикат к таблице в публикациях, что позволяет фильтровать отправку реплицируемых данных, например по списку app. Но напомню, мы имеем дело с 12-ой версией и обновление до 15-ой не такой уж быстрый и простой проект для нашего монолита. В этом решении подсвечиваются еще два минуса:

1. Добавление предиката к каждой таблице в публикации потенциально может замедлить репликацию из-за дополнительной фильтрации WAL и оказать влияние на БД.

2. По прежнему придется делать минимум два полных прохода по таблице.

В ходе поиска решений, вспоминается простой подход:

1. Создать рядом новую таблицу с той же структурой;

2. Перелить в новую таблицу данные, на лету фильтруя по app.

3. Применить инкремент, который копился в процессе переливки;

4. В транзакции удалить старую таблицу, а новой таблице дать название старой.

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

Что мы получим для решения поставленной задачи:

• Благодаря тому, что данные переливаются с применением предиката, за один проход мы получаем таблицу только с нужными данными, которые упорядочены по первичному ключу и без лишних пустот. Аналогично тому, если бы мы делали delete from [table_name] where app_id in (...); + pg_repack, но за один проход.

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

Кажется, что решение найдено, осталось его заскриптовать. Понимая, что придется писать скрипты для ~1000 таблиц, захотелось написать универсальное решение. Так зародилась утилита pg_rebuild_table.

Экономия дискового пространства, RAM и CPU.

Ранее упоминалось, что мы используем дорогие сервера с SSD-дисками. Имеем много больших таблиц, которые стали достаточно широкими из-за добавления колонок в порядке появления новых фич. Так вот благодаря эффекту выравнивания данных, порядок столбцов может ощутимо влиять на размер таблицы (идея неплохо раскрывается тут). На больших по размеру таблицах это особенно сильно проявляется. Изменение порядка колонок позволит нам экономить на утилизации CPU, RAM на занимаемом месте на SSD-дисках и, как бонус, повысить производительность.

Перед нами стоит задача перестроить пару сотен таблиц, да так чтобы порядок колонок был предельно оптимальным для выравнивания данных. Все свелось к небольшой доработке pg_rebuild_table с использованием уже базового подхода перестроения таблицы. В результате по некоторым таблицам сжатие составило до 20%.

Изменение порядка колонок в больших таблицах без простоя в работе.

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

Изменение типа данных колонок в большой таблице без простоя в работе.

С утилизацией ресурсов немного разобрались, теперь вернемся к задаче, где требуется изменять тип данных первичного ключа на int8. Когда меняется в таблице первичный ключ, то за ним тянется цепочка таблиц, часто больших, где требуется также изменять тип колонки, которая является внешним ключом. Мы уже умеем перестраивать таблицы, менять порядок колонок и все это одной командой. Теперь же, используя базовый подход в утилите pg_rebuild_table, сделали еще одна доработку, которая позволяет создавать колонки с новыми типами. Пока решение позволяет преобразовывать только те типы, которые умеет PostgreSQL из коробки, например int4 в int8, timestamp в timestamptz. В перспективе будет поддержка, аналогичная using при изменении типа данных колонки, для сложных преобразований данных.

Перестроение таблицы без долгих транзакций и простоя в работе.

Работая с высоконагруженными системами (более 30 тыс. TPS), мы знаем, что долгие транзакции порождают сильное распухание таблиц и, как следствие, получаем деградацию производительности всей базы данных. В нашем случае есть большие таблицы и запуск pg_repack при переливке данных может занять несколько часов. За это время сервисы начнут сильно страдать и пользователи почувствуют задержки в работе сервисов. А запуск vacuum full полностью блокирует таблицу.

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

Ограничения pg_rebuild_table

• Использовать утилиту могут только суперпользователи.

• Целевая таблица должна иметь ПЕРВИЧНЫЙ КЛЮЧ.

• Триггер "z_pg_rebuild_table__delta" должен быть последним триггером в наборе "before".

Примеры использования pg_rebuild_table

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

pg_rebuild_table -p 5432 -h /tmp -d database_name --chunk_limit 100000 -T employee -ac 't.app_id in (select app.id from app)'

pg_rebuild_table -p 5432 -h /tmp -d database_name --chunk_limit 100000 -T employee -ac 't.group_id in (43597,43789,43791,44229)'

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

pg_rebuild_table -p 5432 -h /tmp -d database_name --chunk_limit 100000 -T employee --reorder_columns

Перестроить таблицу с новым порядком колонок.

pg_rebuild_table -p 5432 -h /tmp -d database_name -T employee --set_column_order id,app_id,first_visit,url,title,site_id

Перестроить таблицу с изменением типов данных столбцов «app_id» и «group_id» с «int» на «bigint».

pg_rebuild_table -p 5432 -h /tmp -d database_name -T employee --set_data_type '[{"name":"app_id", "type":"bigint"}, {"name":"group_id", "type":"bigint"}]'

Планы развития pg_rebuild_table:

1. Научить работать с разными версиями PostgreSQL;

2. Научить партицировать таблицу с доступными стратегиями в PostgreSQL.

На момент написания статьи утилита pg_rebuild_table уже обработала около 1,5 тыс. таблиц общим объемом ~50 ТБ на базах данных PostgreSQL 12 версии.

P.S. Буду очень рад любым предложениям, замечаниям, пожеланиям по данной статье, а также по использованию утилиты pg_rebuild_table.

Пишите свои вопросы и комментарии::

• В телеграмм https://t.me/pg_rebuild_table

• На почту viktor-b_90@inbox.ru

Ссылка на репозиторий pg_rebuild_table:

https://github.com/comagic/pg-rebuild-table

Разработки, в которые подглядывал при написании кода:

https://github.com/comagic/transparent_alter_type

https://github.com/comagic/pg_export

Благодарности

Хочу выразить благодарность коллегам, которые оказывали поддержку при написании статьи, а именно Стороженко Николаю, Пашкову Денису, Чернякову Андрею.

Отдельная благодарность Чернякову Андрею за его кодовые наработки, которые использовались при написании утилиты pg_rebuild_table, что значительно ускорило реализацию решения.