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

Эта история о том, как мы придумали и реализовали свой собственный метод, когда никакие другие сторонние инструменты не позволяли сохранять работоспособность сервиса и гарантировать надёжность. Его можно применять во всём мире, где используются платформы на базе PostgreSQL.

О чём пойдёт речь:

•‎ Что случилось

•‎ Что надо сделать

•‎ Немного теории о bloat

•‎ Решение

•‎ Итоги

•‎ Как ещё оптимизировать наш алгоритм

Что случилось

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

Что надо сделать

Мы взяли лопаты и пошли копать вручную. В ходе раскопок мы выявили несколько факторов, повлиявших на рост нагрузки и деградацию базы данных:

1. Изменение профиля нагрузки. Часть сервисов перешла на новое API, которое вело к увеличению запросов к БД. А ещё подрос трафик

2. Периодически заканчивались коннекты из-за роста времени выполнения запросов и, как следствие, постоянных прерываний контекста

3. Архивация

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

Возникший bloat приводил к деградации времени выполнения запросов

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

Немного теории о bloat

Строки в таблицах PostgreSQL практически всегда имеют несколько версий. Разные версии одной и той же строки имеют две отметки: xmin и xmax, в которые записываются номера транзакций. Когда строка создаётся, в xmin записывается номер транзакции, которая выполняет команду INSERT. При удалении строки номер транзакции, которая выполняет DELETE, записывается в xmax, то есть эта операция не выполняет немедленного удаления строки. Операцию UPDATE можно рассматривать как выполнение двух операций INSERT и DELETE, таким образом данная операция порождает две версии строки: новую и старую.

Поэтому операция DELETE не сокращает объём занимаемых данных, а UPDATE всегда порождает рост данных в таблицах. Кроме того, команда INSERT может порождать рост данных даже в тех случаях, когда она была выполнена в отменённой транзакции.

Сохранение различных версий строк необходимо для эффективной реализации MVCC (Multiversion Concurrency Control, Многоверсионное управление конкурентным доступом), реализованное через снимки данных. Снимок данных — это все версии разных строк, которые наблюдаются какой-либо транзакцией в конкретный момент времени. Благодаря этому обеспечивается согласованная картина данных в ACID смысле, и за это приходится платить.

Таким образом, bloat (раздувание) — это ситуация, когда таблицы или индексы в PostgreSQL занимают значительно больше места на диске, чем нужно для хранения полезных данных, из-за накопления устаревших или неактуальных данных, которые остаются после операций UPDATE и DELETE. Это приводит к снижению производительности, так как нужно обрабатывать больше информации, чем нужно на самом деле.

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

Накопление bloat ведёт к следующим проблемам:

• увеличивается потребление памяти на диске. Полезных данных может быть сильно меньше, чем всего занято места;

• увеличивается потребление памяти для кеша, так как кешируются страницы, а полезных данных в них мало;

• замедляется полное сканирование таблиц и индексов;

• замедляется индексный доступ. В одном из самых популярных B-tree индексе может появиться лишний уровень;

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

Перечислим их:

• внутристраничная очистка таблиц — убирает версии строк невидимые ни в одном снимке. Освобождённое место не возвращается ОС, но в дальнейшем может использоваться для обновлений и вставок. Данная очистка может выполняться при обновлении или чтении страницы;

• внутристраничная очистка индексов — удаляет индексные строки, которые ведут на устаревшие версии табличных строк или которые ссылаются на различные версии одной и той же табличной строки. Эта очистка происходит, когда заканчивается место в странице индекса;

• hot-обновления — оптимизация, которая борется с появлением лишних записей в индексах. Если в строке меняется значение столбца, который не входит ни в один индекс, то вместо создания новых записей в индексе, сохраняется цепочка указателей на версии строк внутри табличной страницы;

• обычная очистка выполняется командой VACUUM. Выполняет работу, аналогичную внутристраничной очистке таблиц и индексов, но в рамках всей таблицы. В большинстве случаев очищенное место не возвращается ОС. Для возвращения места ОС в результате работы VACUUM должно накопиться некоторое количество свободных страниц в конце файла таблицы и если при этом удаётся взять эксклюзивную блокировку за 5 секунд;

• полная очистка выполняется командой VACUUM FULL. Полностью перестраивает сначала таблицу, а затем все её индексы. Требует x2 места, так как в процессе перестраивания хранятся старые и новые данные. На время перестраивания полностью блокируются операции записи и чтения. Освобождает место на диске и возвращает его ОС. Этот способ применяют, когда объём данных малый и можно позволить даунтайм;

• кластеризация. Выполняется командой CLUSTER. Делает работу, аналогичную той, что производит команда VACUUM FULL, но дополнительно упорядочивает версии строк в соответствии с одним из имеющихся индексов;

• pg_repack — расширение Postgres Pro Enterprise, которое делает аналогичные операции, что и VACUUM FULL и CLUSTER, но выполняет их на ходу без эксклюзивных блокировок таблиц. Данное расширение является альтернативной ветвью развития проекта pg_reorg.

Более подробное описание работы данных механизмов можно найти в книге «PostgreSQL изнутри» Егора Рогова.

Бывают ситуации, когда этих средств недостаточно или же какие-то из них невозможно применить, например, когда VACUUM FULL или CLUSTER требуют x2 места и эксклюзивной блокировки таблиц, что может приводить к продолжительной остановке обработки запросов. Какие-то инструменты, например pg_repack, невозможно применить из-за отсутствия поддержки со стороны DBA.

Решение

В нашем случае bloat составил более 50%, то есть более половины от всех данных только мешались без какой-либо полезной нагрузки.

Мы решили отправить сильно поправившиеся таблицы на фитнес. Ни один из существующих методов похудения нам нельзя было применять, потому что они не гарантировали безопасность и стабильность работы сервиса. Тогда мы взяли за основу фитнес-план, который чуть ранее разработал и успешно внедрил Дмитрий Сидоренко. 

Его проблема была схожа с нашей. БД превышала максимально разрешенный размер диска в два раза, что приводило к потере SLA на работу базы, а это в свою очередь вело к увеличения времени ответа основного сервиса. DBA Авито не одобрили использование инструментов pg_repack и compacttables, а других решений без downtime не было. В свою очередь downtime даже в пару минут, в рамках BigTech компаний стоит очень дорого. Нужно было придумать свое решение.

Идея была простая и красивая. Создаём новую таблицу, переносим в неё данные из уже существующей, меняем местами новую и старую таблицу посредством переименования таблиц и удаляем старую таблицу. При этом bloat не переносится.

Мы взяли его способ, но адаптировали фитнес-программу под нас. Мы обратили внимание, что хотя новые таблицы будут без bloat, но B-tree индексы в новой таблице будут всё же с bloat, так как копирование данных каким-то образом к этому приводит. И для этой проблемы тоже есть два решения: создавать индексы после переноса данных или же делать реиндекс. По совету DBA выбрали реиндекс.

Фитнес-программу составили интенсивную:

• Дублирование данных с последующей перестановкой таблиц. Перестановка таблиц является критической операцией, так как требует захвата эксклюзивной блокировки. Такие работы мы проводим во время шатаний (shatality);

• После перестановки таблиц выполняем перестроение индексов в новой таблице, используя команду REINDEX, так как оказалось, что B-tree индексы во время дублирования таблиц могут накапливать bloat;

• Также в каждой таблице мы проверяли статистику использования индексов и те, что не использовались удаляли – DROP INDEX.

Наш алгоритм дублирования и перестановки таблиц:

1. Настраиваем триггеры на вставку, обновление и удаление данных. Они очень важны для целостности данных во время их переливки. 

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

Во всех трёх случаях мы вставляем запись, которая идентична записи в исходной таблице. В случае, если была выполнена вставка в исходную таблицу, то в переливаемой таблице записи точно не было и она просто вставится. Если происходит обновление записи в исходной таблице, то запись уже присутствует в таблице-реплике и при вставке случится конфликт. Обрабатывая конфликт, мы обрабатываем обновление, делаем это в конструкции ON CONFLICT DO.

upsert (обновление и вставка):

-- 1. Пытаемся вставить строку в table_replica, если не получилось - обновляем
CREATE OR REPLACE FUNCTION upsert_table_replica()
    RETURNS TRIGGER AS $$
BEGIN
    -- Вставка записи в table_replica с обновлением в случае конфликта по id
    INSERT INTO table_replica (id, field_1, ...)
    VALUES (NEW.id, NEW.field_1, ...)
    ON CONFLICT (id)
        DO UPDATE SET
        	field_1 = EXCLUDED.field_1,
		...
RETURN NULL;
END;
$$ LANGUAGE plpgsql;


-- 2. Создаем триггер типа AFTER на создание и обновление строк в table
CREATE TRIGGER trigger_upsert_table
    AFTER INSERT OR UPDATE ON table
    FOR EACH ROW
EXECUTE FUNCTION upsert_table_replica();

Триггер на удаление составлен тривиально. В случае удаления записи из исходной таблицы мы удаляем запись из таблицы-реплики. Бо́льшие комментарии излишни, просто взгляните на код.

delete (удаление):

-- 3. Создаем триггер на удаление для таблицы table
CREATE OR REPLACE FUNCTION delete_table_replica()
    RETURNS TRIGGER AS $$
BEGIN
    -- Удаление записи из table_replica по id
    DELETE FROM table_replica
    WHERE id = OLD.id;


    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_delete_table
    AFTER DELETE ON table
    FOR EACH ROW
EXECUTE FUNCTION delete_table_replica();

Здесь также важно поговорить о времени работы. Триггеры — неотъемлемые части операций вставки и обновления. Это значит, что время работы операций с основной таблицей увеличивается. Если говорить о блокировках на таблицах, то здесь они берутся классические, как и при обычной вставке.

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

2. Перетаскиваем все данные из таблицы-оригинала A в таблицу-реплику A_replica. Для этого используем самописный дубликатор, который с использованием CTE-запроса перетаскивает батчами данные из таблицы в её реплику:

drop function if exists copy_rows_from_table(size integer);
create or replace function copy_rows_from_table(size integer) returns bool as $$
declare
    all_duplicated bool;
begin
    with batch as (
        select
            
        from table as ts
        where ts.id > (select last_id from dup_id) and ts.id < (select max_id from dup_id)
        order by ts.id asc
        limit size
        for update -- чтобы в момент переноса ничего не происходило со строками из батча
    ), duplicated as (
        insert into table_replica select  from batch on conflict(id) do nothing
    ), duplication_offset as (
        -- Запоминаем последний id из батча
        -- если в батче для переноса ничего нет, то оставляем текущий id
        update dup_ids set last_id = COALESCE((select max(id) from batch), last_id)
    )
    --  Запрос необходимо повторять до тех пор, пока значение all_done не станет true
    -- если в батче переноса ничего нет, то продолжать нет смысла
    select count(*)::integer = 0 as all_done into all_duplicated from batch; 
    return all_duplicated;
end;
$$ language plpgsql;

3. Таблицу-оригинал table переименовываем в table_tmp, а таблицу-реплику table_replica переименовываем в table:

Важно учесть, что при перестановке оригинальная таблица получает новое название, которое обновляется в её индексах, триггерах и во внешних ключах. Однако, оригинальная таблица остаётся владеть своими генераторами последовательности (sequence).

4. Всё вышеперечисленное выполняем для каждой таблицы.

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

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

Переименование таблиц, удаление внешних ключей и реиндекс мы выполняем во время шатаний.

Шатать (shatality) прод значит вносить критичные изменения, которые могут положить часть или всю систему. Такие работы мы проводим в низкий трафик, например, ночью, чтобы, если что-то неприятное случится, то эффект был минимальным.

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

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

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

Для перестановки таблиц использовался следующий скрипт:

begin;
set local lock_timeout to '5s';
set local statement_timeout to '10s';
lock table table in ACCESS EXCLUSIVE MODE;
lock table table_replica in ACCESS EXCLUSIVE MODE;
alter table table rename to table_temp;
alter table table_replica rename to table;
-- дополнительный шаг, так как мы используем сиквенс оригинала
-- без выполнения этого шага не удастся удалить table_temp
alter sequence table_id_seq owned by table.id; 
commit;

Дополнительно во время шатаний можно удалить неиспользуемые индексы. 

Для поиска неиспользуемых индексов использовался следующий скрипт:

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(s.indexrelid)) as size
FROM
    pg_stat_user_indexes s
JOIN
    pg_index x ON s.indexrelid = x.indexrelid
JOIN
    pg_class t ON x.indrelid = t.oid
JOIN
    pg_class i ON s.indexrelid = i.oid
where
    s.schemaname = 'public' and s.idx_scan = 0 and s.idx_tup_read = 0 and s.idx_tup_fetch = 0
order by
    i.relname desc;

Нули в полях idx_scan, idx_typ_read и idx_typ_fetch означают, что индекс не использовался. Тут нужно бы понять как давно этот индекс создавался, почему он не используется, не может ли он начать вдруг использоваться и если есть реплики, то стоит проверить и на них (на случай, если чтение у вас происходит с реплик).

Весь процесс похудения занял у нас три месяца. За это время мы успели выполнить упражнения с внешними ключами, с реиндексами, с лишними индексами. В итоге всё ненужное зачистили в полуавтоматическом режиме, указывая, на какие таблицы надо натравить процесс. На финальном этапе — шаталити – таблицы переключаются и сразу зачищаются.

Итоги

В результате SR поиска составил примерно 99.99%, а SR создания – 100%.

Когда мы всё полечили, то проблемы ушли, базе данных стало легче, 99.99% времени сервис отвечает без ошибок. С того момента подобной проблемы больше не возникало. 

Чего мы добились:

1. Освободили 50% места на сервере

2. Сохранили лояльность пользователей

3. Сохранили репутацию и деньги компании

Наш метод похудения: 

1. Без downtime

2. Безопасен на любом объёме базы данных

3. Универсален для всех платформ на PostgreSQL

Выводы по bloat:

1. Без очистки bloat не уменьшить место

2. Очистка bloat улучшает работу базы данных

3. Bloat – это маааленькое зло, аномальная нагрузка — зло большое

Удаление bloat действительно сделает жизнь БД лучше. Но появление проблем из-за него не возникают в моменте, деградация из-за bloat происходит постепенно. В различных источниках говорится, что bloat выше 10-15% нужно чистить. Чистить желательно, но если вас устраивает производительность БД, то не обязательно. Лучше для начала понять, из-за чего он накапливается, и постараться уменьшить его рост. Базы данных с bloat даже больше 50% могут чувствовать себя вполне сносно.

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

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

Для себя мы выработали следующие правила по массовому переносу или удалению данных:

1. Лучше в два раза увеличить батч, чем в два раза уменьшить таймаут (100 записей за 2 секунды лучше, чем 500 записей за 1 секунду).

2. Более продолжительная дополнительная нагрузка лучше, чем менее продолжительная (3М записей за 8 часов лучше, чем 3М записей за 3 часа).

Как ещё оптимизировать наш алгоритм

Можно применить партицирование. Алгоритм, который здесь описан, нужно периодически повторять с каждыми таблицами, а партицирование позволяет сделать это только один раз: выполнить копирование – перенос данных в новую партицированную таблицу, проверку и смену таблиц. После этого можно прикрутить автоматику по созданию новых партиций и очистки пустых (пустые партиции будут появляться за счёт архивации), после этого можно забыть об очистке bloat.