3 совета по управлению большими базами данных Postgres

Попробуйте эти удобные решения распространенных проблем при работе с огромными базами данных.

Реляционная база данных PostgreSQL (также известная как Postgres) становится все более популярной, и предприятия и государственный сектор используют ее по всему миру. Благодаря такому широкому распространению базы данных стали больше, чем когда-либо. В Crunchy Data мы регулярно работаем с базами данных размером более 20 ТБ, и наши существующие базы данных продолжают расти. Мы с моим коллегой Дэвидом Кристенсеном собрали несколько советов по управлению базой данных с огромными таблицами.

Большие столы

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

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

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

CREATE INDEX ON customers (last_name)

Решение. Используйте функцию СОЗДАТЬ ИНДЕКС ОДНОВРЕМЕННО. Этот подход разделяет создание индекса на две части: первая с кратковременной блокировкой для создания индекса, который немедленно начинает отслеживать изменения, но сводит к минимуму блокировку приложений, после чего следует полное построение индекса, после чего запросы могут начать его использовать.

CREATE INDEX CONCURRENTLY ON customers (last_name)

Добавление столбцов

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

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

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

Добавьте столбец:

ALTER TABLE all_my_exes ADD COLUMN location text

Добавьте значение по умолчанию:

ALTER TABLE all_my_exes ALTER COLUMN location

SET DEFAULT texas()

Используйте UPDATE, чтобы добавить значение по умолчанию:

UPDATE all_my_exes SET location = DEFAULT

Добавление ограничений

Проблема: вы хотите добавить проверочное ограничение для проверки данных. Но если вы используете простой подход к добавлению ограничения, оно заблокирует таблицу, пока проверяются все существующие данные в таблице. Кроме того, если на каком-либо этапе проверки возникнет ошибка, произойдет откат.

ALTER TABLE favorite_bands 

ADD CONSTRAINT name_check

CHECK (name = 'Led Zeppelin')

Решение: сообщите Postgres об ограничении, но не проверяйте его. Подтвердите на втором этапе. На первом этапе потребуется короткая блокировка, гарантирующая, что все новые/измененные строки будут соответствовать ограничению, а затем проверка на отдельном проходе, чтобы подтвердить, что все существующие данные проходят ограничение.

Сообщите Postgres об ограничении, но не применяйте его:

ALTER TABLE favorite_bands

ADD CONSTRAINT name_check

CHECK (name = 'Led Zeppelin') NOT VALID

Затем ПРОВЕРЬТЕ его после создания:

ALTER TABLE favorite_bands VALIDATE CONSTRAINT name_check

Хотите большего?