Облегчаем жизнь PostgreSQL таблице под нагрузкой

У любого современного продукта — если он успешен — есть тот момент жизни, когда он из гадкого стартапа вдруг становится вполне себе прекрасным “энтерпрайз монолит платформ систем легаси”. Без тестов, без мониторинга, с highload и, конечно же, уймой родовых травм, вызванных быстрым развитием.

На критическую бизнес логику тесты будут написаны сами, без них никуда. Мониторинг и хайлоад — это курица и яйцо. После того, как у вас появится кто-то один из этой пары, на горизонте появится и второй. А вот все те, казалось бы, “удачные” и “быстрые” решения, заложенные при рождении, придётся исправлять. И если кодовую базу можно спокойно переписать (ну или хотя бы закидать костылями), то вот база данных — это одна сплошная горячая точка. Запросы и миграции, которые легко проходили на момент становления вашего продукта, легко могут сейчас положить прод, потому что ваша база теперь под постоянной нагрузкой, а ещё она неприлично раздулась. 

Привет! Меня зовут Константин, и в Каруне я работаю backend-разработчиком. Данная статья — компиляция ряда сложностей, с которыми мы столкнулись, и методик для их решения. Вся проблема громоздких таблиц в том, что они, как визит к стоматологу — неожиданно, дорого, больно и ужасно вариативно. 

Что позволено деплоить Юпитеру, не дозволено выводить в прод быку.

Зло банально и выбирает обычные формы и имена: толстая таблица с названием ‘users’, ‘customers’, ‘orders’, ‘employers’ всегда есть на проекте. Такая таблица —  фундамент бизнес логики, в ней хранится огромный массив данных как в ширину (очень много записей), так и в длину (каждый сегмент бизнес логики что-то читает или пишет в эту таблицу). Плюс такая таблица — это всегда кладезь артефактов прошлого. Был у вас когда-то гениальный маркетологический продуктовый гуру, вы успешно вышли на новые бизнес горизонты, опробовав его идею. Сейчас этот гуру на випассане с друзьями соевое мясо доедает, а у вас в таблице, которая под постоянной нагрузкой, есть странные поля, которые содержат информацию: пользователь кришнаит? Открывал ли он промо ссылки в полную луну? Средний размер количества его кликов с учётом того, что сейчас идёт год Тигра?

Впереди нас ждут методы, которые мы использовали для облегчения жизни с большой таблицей в БД Postgresql (v.13) — они позволят ей, вашему продакшену, отделу мониторинга и СТО глубоко дышать и спокойно спать по ночам.

Меняем типы полей на горячую

Зачастую в таблице появляется одно или несколько полей, которые характеризуют какой-либо момент её жизненного цикла или принадлежность к какому-то типу. Это всеми нами любимые поля, вроде ‘status’ или ‘type’. Проблема таких полей в том, что они не всегда тянут на вынос в отдельную полноценную связанную таблицу, хранятся в виде текста, и замечаем мы это только в тот момент, когда у нас уже идёт борьба за живучесть нашего продакшена в условиях хайлоад шторма (ну или борьба за бюджет на хранение).

Самое простое решение в таком случае — это конкретизировать возможные варианты, которые могут быть в данном поле, и переехать на ENUM. Но как сделать это с минимальным инвазивным путём для БД? Чтобы не было простоев и потери данных?

Тут нужна постепенная миграция, в несколько этапов.

Первый этап

1.1 Добавим новый тип enum в нашу базу, собрав возможные варианты значений поля в нём.

CREATE TYPE orders_status AS ENUM ( 'confirmed',
	'created',
	'in_process',
	'success',
	'rejected',
	'failure'
);

1.2 Добавим новое поле к нашей таблице, в будущем оно заменит старый текстовый статус.

ALTER TABLE orders ADD COLUMN status_new orders_status;

1.3 Создадим триггер, который будет дублировать значение старого статуса в новый каждый раз, когда будет изменяться его значение или добавляться новая запись.

CREATE OR REPLACE FUNCTION mirror_orders_status_to_status_new()
  RETURNS trigger AS
$BODY$
BEGIN
  NEW.status_new = NEW.status;

  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;;

CREATE TRIGGER insert_orders_status_new_trigger
	AFTER INSERT
	OR UPDATE ON orders
	FOR EACH ROW
	EXECUTE PROCEDURE mirror_orders_status_to_status_new ();

2. Следующим этапом нам требуется перенести исторические значения из поля status в поле status_new. Самый банальный способ — просто пройтись простым UPDATE по всем пустым значениям искомой таблицы, но вы можете продумать более хитрое заполнение данных, постепенно или пачками.

UPDATE
	orders
SET
	status_new = status::orders_status
WHERE
	status_new IS NULL;

3. На данном этапе нам нужно переключить чтение и запись в нашей кодовой базе со старого поля status на новое. Это, наверное, самый критичный этап, потому что неожиданно выяснится, что есть некий класс, построенный через метапрограммирование, или что почему-то в вашу БД напрямую ходили какие-то аналитики, маркетологи, и прочие бизнес-бесы.

4. Далее мы можем спокойно удалить триггеры с поля status, так как данные и логика уже идут через нашу кодовую базу в поле status_new, уже с новым типом.

DROP TRIGGER IF EXISTS insert_orders_status_new_trigger ON orders;
DROP ROUTINE IF EXISTS mirror_orders_status_to_status_new();

5. В финале мы удаляем старое поле status, переименовываем status_new в status — как в БД, так и в нашей кодовой базе.

ALTER TABLE orders DROP COLUMN IF EXISTS status;

Тут показан пример миграции с текстового типа на enum, но данная методика подходит и для других типов данных — с учётом их специфики, конечно. Одна из частых проблем — это момент, когда ваш ‘orders.id’ хранит в себе int, диапазон значений начинает заканчиваться, и вам нужно переехать на bigint. Вам придётся учесть только, что это работа с PRIMARY KEY, и вам придётся помнить о констрейтах и индексах. Но в целом вам предстоят эти же работы и такие же этапы.

Вакуумация и клонирование

PostgreSQL — база данных версионированная. Фактически мы не записываем в ту же ячейку памяти новое значение, а просто помечаем старое значение как deprecated. Пока на твой сервис ходят только друзья и бабушка твоего продуктолога — всё идёт хорошо. А вот потом, в какой-то момент, количество мёртвых “кадров” в базе начинает расти, а значит, и размер базы в памяти так же вырастает. Причём фактический размер таблицы и записей в ней гораздо меньше чем то, что наблюдается. Всё это связано с накоплением устаревших записей в памяти, и для решения этой проблемы в PostgreSQL используется функция vacuum.

VACUUM (автоматический или запущенный вручную) позволяет пометить неактуальные страницы памяти БД как готовые к переиспользованию, и они становятся доступными для Free Space Map (FSM), при этом объём физически занятой памяти не уменьшится. А вот VACUUM FULL полностью перезапишет таблицу и освободит память. Одна проблема: такой метод требует эксклюзивных блокировок, памяти и времени.

AUTOVACUUM реагирует на 2 параметра, которые по умолчанию прописаны в конфигурации:

autovacuum_vacuum_scale_factor - 0,2
autovacuum_vacuum_threshold - 50

PostgreSQL использует их в формуле для расчёта порога запуска автовакуума.

pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor ) + autovacuum_vacuum_threshold

Простыми словами, автовакуум запустится в тот момент, когда количество мёртвых кадров станет больше, чем количество строк в этой таблице, помноженных на autovacuum_vacuum_scale_factor + порог запуска автовакуума autovacuum_vacuum_threshold.

Для таблиц малого и среднего размера данные значения этих конфигурационных параметров вполне приемлемы. Но если мы возьмём таблицу поприличнее, от 100к записей, то ждать каждый раз, когда размер таблицы вырастет в 1,2 (а это 20к мёртвых кадров) — не самое лучшее решение. Особенно, если эта таблица всё время находится под нагрузкой, и там постоянно идут апдейты.

Стоит знать, что у нас есть альтернатива для использования VACUUM — это инструменты вроде https://github.com/dataegret/pgcompacttable. Но зачастую легче и лучше настроить штатные средства борьбы с раздуванием БД.

Что делать, если мы настроили автовакуум слишком поздно, и наша таблица уже имеет неприличный размер? Мы, конечно, можем запустить VACUUM FULL. Но он на определённое время заблокирует таблицу, и данному факту не будет рада секта поклонников “99.99% Uptime”.

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

1. Создадим копию нашей таблицы, но без данных. Теперь мы также можем добавить все нужные нам индексы конкурентно. Единственное отличие от родительской таблицы — id должен быть привязанным серийно к старой таблице.

CREATE TABLE 'orders_new' AS TABLE 'orders' WITH NO DATA;
ALTER TABLE ONLY orders_new ALTER COLUMN id SET DEFAULT nextval('orders_id_seq'::regclass);

2. Добавим триггер и процедуру. Каждый раз, когда мы будем писать в старую таблицу, мы будем добавлять и обновлять данные в новой таблице. Удаление у нас не предусмотрено, мы поклонники софт делета.

CREATE OR REPLACE FUNCTION orders_mirror_f ()
  RETURNS TRIGGER
  AS $body$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO orders_new
    SELECT
      NEW.*;
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE
      orders_new
    SET
      user_id = NEW.user_id,
      value = NEW.value
    WHERE
      id = OLD.id;
    RETURN NEW;
  END IF;
END;
$body$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_orders
	AFTER INSERT
	OR UPDATE ON orders
	FOR EACH ROW
	EXECUTE PROCEDURE orders_mirror_f ();

3. Теперь нам нужно перенести архивные данные, и тут самое важное — не пытаться грузить их вагонами, а переносить по одной строчке. Так мы избежим возможных блокировок. Это один из вариантов решения. Вы вполне можете придумать своё: например, скрипт на RUBY/GO/PHP/etc, который будет делать то же самое пачками приемлемого размера в фоне, по ночам, с возможностью мониторинга процесса и кнопкой “стартстоп”.

CREATE OR REPLACE PROCEDURE procedure_orders_sync ()
AS $body$
DECLARE
	current_id INTEGER DEFAULT 0;
	flag BOOLEAN DEFAULT TRUE;
BEGIN
	WHILE flag LOOP
		INSERT INTO orders_new
		SELECT
			*
		FROM
			orders AS temp
		WHERE
			temp.id > current_id
		ORDER BY
			temp.id
		LIMIT 1
	FOR UPDATE ON CONFLICT DO NOTHING
RETURNING
	id INTO current_id;
		IF current_id IS NULL THEN
			flag := FALSE;
		END IF;
	END LOOP;
		RETURN;
END;
$body$
LANGUAGE 'plpgsql';

CALL procedure_orders_sync();

4. Теперь самое интересное — нам нужно переключиться на новую таблицу и исправить последовательность для id, тоже на новую таблицу. Данная процедура вызывает блокировку, но гораздо меньшую, чем полный вакуум.

ALTER TABLE ONLY orders_new ALTER COLUMN id SET DEFAULT nextval('orders_new_id_seq'::regclass);
ALTER TABLE 'orders' RENAME TO 'orders_old';
ALTER TABLE 'orders_new' RENAME TO 'orders';
ALTER SEQUENCE orders_new_id_seq RENAME TO orders_id_seq;

5. Убедившись, что всё работает, мы можем удалить старую таблицу, а также все триггеры и функции.

Таким образом, мы можем вполне уменьшить размер таблицы, не прибегая к полной вакуумации и помощи реаниматолога для СТО.

Секционируй и властвуй

А что делать, когда вы считаете, что ваша таблица уже полностью оптимизирована (или вы так думаете), а объём данных и размер таблицы продолжают расти? Каждая вставка и обновление в таблице даются всё тяжелей, ведь индексам нужно перестроиться. Да и сами индексы разрастаются и перестают быть эффективными. Именно в этот момент эффективным средством от поседения вашего СТО и покраснения графиков мониторинга может стать секционирование таблицы. Объём данных это не уменьшит, но манипулировать данными станет легче.

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

Плюсы секционированной таблицы:

1. Ваши запросы будут быстрее, если вы используете в теле запроса ключ, который был использован для секционирования.

2. Вы легко сможете управлять архивными данными — перенести их в другое хранилище или на более медленный носитель.

3. Гораздо легче удалять или добавлять большие объемы данных путём подключения/отключения секций. Например, если вы хотите удалять логи периодически, сделайте разбиение по дате и открепляйте, удаляйте старые секции, когда они перестанут быть нужны.

Минусы:

1. Дополнительный уровень структуры данных. Про него нужно помнить, его нужно обслуживать, документировать, мониторить. И, естественно, там можно ошибиться.

2. Партиции не создаются автоматически. Вам придётся об этом помнить.

3. Все уникальные ключи должны быть учтены в ключе секционирования.

Далее мы разберём практический метод перехода на секционированную таблицу с минимальным простоем.

1. Создадим копию нашей таблицы orders, но уже с использованием секционирования. На данном этапе важно не забыть про индексы, констрейты, права доступа к которым есть у оригинальной таблицы, а также создать дефолтную секцию.

CREATE TABLE orders_new (
  id integer NOT NULL,
  user_id integer NOT NULL,
  value bigint DEFAULT 0 NOT NULL,
) PARTITION BY LIST (user_id);

ALTER TABLE ONLY orders_new ALTER COLUMN id SET DEFAULT nextval('orders_id_seq'::regclass);

-- constraints
ALTER TABLE ONLY orders_new ADD CONSTRAINT orders_new_pkey PRIMARY KEY (id, user_id);
ALTER TABLE ONLY orders_new ADD CONSTRAINT positive_value CHECK (value >= 0);

-- indexes
CREATE INDEX idx_orders_new_user_id_and_value ON orders_new
  USING btree (user_id, value DESC);

-- default partition
CREATE TABLE orders_new_def PARTITION OF orders_new DEFAULT;

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

2. Следующим шагом будет генерация функций для создания и удаления секций, это будет нужно для последующей автоматизации.

CREATE OR REPLACE FUNCTION create_partition_for_orders(user_id bigint) RETURNS VOID AS
  $BODY$
    DECLARE
      partition_name TEXT;
    BEGIN
      partition_name := 'orders' || '_u' || user_id::text;

      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition_name) THEN
        RAISE NOTICE 'A partition has been created %', partition_name;

        EXECUTE 'CREATE TABLE ' || partition_name || ' PARTITION OF orders FOR VALUES IN ('|| user_id ||');';
      END IF;
    END;
  $BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION drop_partition_for_orders(user_id bigint) RETURNS VOID AS
  $BODY$
    DECLARE
      partition_name TEXT;
    BEGIN
      partition_name := 'orders' || '_u' || user_id::text;

      IF EXISTS(SELECT relname FROM pg_class WHERE relname=partition_name) THEN
        RAISE NOTICE 'A partition has been droped %', partition_name;

        EXECUTE 'DROP TABLE ' || partition_name || ' CASCADE;';
      END IF;
    END;
  $BODY$
LANGUAGE plpgsql;

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

4. Следующим шагом мы создадим триггеры для автоматической вставки и обновления данных из старой таблицы в новую. Что интересно: за счёт декларативного метода секционирования нам не нужно указывать конкретную партицию, куда будет идти вставка данных. И мы можем воспользоваться триггерами, которые мы использовали для копирования таблицы.

CREATE TRIGGER trigger_orders
	AFTER INSERT
	OR UPDATE ON orders
	FOR EACH ROW
	EXECUTE PROCEDURE orders_mirror_f ();

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

6. Самое сложное — подменить по горячим следам старую таблицу на новую. И тут нужно запланировать блокировку старой таблицы и нашего сервиса.

6.1 Заблокируем старую таблицу и все дочерние таблицы для того, чтобы внести нужные изменения.

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

6.2 Создадим последовательность для первичного ключа на новой таблице.

ALTER TABLE ONLY orders_new
  ALTER COLUMN id
  SET DEFAULT nextval('orders_new_id_seq'::regclass);

6.3 Удалим триггеры со старой таблицы.

DROP FUNCTION IF EXISTS insert_data_into_orders_new_f CASCADE;
DROP FUNCTION IF EXISTS update_data_into_orders_new_f CASCADE;

6.4 Переименуем оригинальную таблицу в старую, а секционированной таблице присвоим имя прежней таблицы. Также нам лучше переименовать дефолтную партицию.

ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
ALTER TABLE orders_new_def RENAME TO orders_def;

7. Ну вот, почти всё. Осталось удалить старую таблицу и переименовать сиквенс.

DROP TABLE IF EXIST 'orders_old' CASCADE;

Справедливости ради, стоит упомянуть о том что есть много инструментов которые позволяют делать подобные манипуляции из вашего приложения. Например для Ruby on Rails можно использовать гемы типа pg_party. Но тут вопрос вашего вероисповедания - готовы ли вы отдать это на уровень логики вашего приложения?

Всё есть яд и всё есть лекарство

Каждый из описанных методов может вам помочь, но прежде, чем сломя голову реализовать любую из этих процедур, следует подумать и проанализировать — а нужно ли это? Поможет ли это? Может, стоит устроить ужин при свечах с девопсами и попросить добавить железа, и этого будет достаточно? Решены ли все проблемы с логикой приложения? Актуальны ли индексы и запросы к вашей БД? Возможно, что решение этих, казалось бы, банальных проблем уже будет достаточным.

Описанные здесь методы — не панацея. Особенности вашей БД, нагрузки и возможностей PostgreSQL дают множество способов, как снизить нагрузку: ищите да обрящете!