"Долгоиграющие" БД PostgreSQL

Юрий Бушмелев, Ульяновск, Russia

LVEE 2013

Big PostgreSQL databases under heavy load require special care. Database administrator should keep eye on amount of indexes and tables bloat and use special tools to reduce bloat level. Big databases need extra handling when doing backup as well. Replication management is another special task. This talk describe some solutions how to deal with such databases in production.

Когда базы PostgreSQL вырастают до сотен гигабайт, обеспечение их бесперебойной и быстрой работы под высокими нагрузками начинает требовать особых подходов.

Длительный срок эксплуатации при постоянной записи в БД порождает проблему «распухания» индексов и таблиц. Необходимо уметь оценивать степень «опухания», чтобы своевременно проводить обслуживание таблиц и индексов. Для оценки можно использовать запросы, приведенные в wiki¹ или в презентации Michael Glaesemann².

«Распухшие» индексы можно пересоздать штатной конструкцией SQL REINDEX, но это заблокирует таблицу на запись на время пересоздания индекса. Более «либеральный» вариант — создать аналогичный индекс конструкцией CREATE INDEX CONCURRENTLY, затем удалить старый индекс и переименовать новый.

Для «компактизации» таблиц можно использовать конструкции VACUUM FULL и CLUSTER, но они также блокируют обрабатываемые таблицы. Поэтому были созданы альтернативные инструменты: vacuum_table.pl³/pgcompactor⁴ и pg_repack⁵.

Большой объем БД и постоянная нагрузка создают проблемы с резервным копированием. Здесь нужно соблюсти баланс между длительностью резервного копирования, создаваемой им нагрузкой на сервер БД и занимаемым им объемом. Самый простой способ — создать дамп базы с помощью pg_dump, но он использует конструкцию COPY, которая блокирует таблицы, и создает нагрузку на сервер БД. Альтернативный вариант — копирование кластера БД с файловой системы и сохранение WAL-логов. Более интересный вариант — использование снапшотов ФС и сохранение WAL-логов. Также есть специализированные решения — pg_rman⁶ и WAL-E⁷.

Необходимость постоянной доступности БД требует включения репликации, что также определяет некоторые особенности в обращении с базами.
Для создания потоковой реплики можно использовать как обычные средства сетевого копирования (scp и rsync), так и специализированные утилиты — pg_basebackup и repmgr⁸.

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

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