Привет, Хабр! Меня зовут Кирилл Абрамов, я backend-инженер в сервисе Автотека Авито. В этой статье я расскажу, как остановил постоянное увеличение объёма базы данных PostgreSQL и что надо делать, если времени на остановку не остаётся.
Что внутри статьи:
Проблема
Весной 2023 года я пришёл в команду Автотека Авито, цифровой сервис, который проверяет всю историю автомобиля с момента его производства на момент запроса. Мы столкнулись с тем, что наши базы данных упёрлись в лимит и продолжали расти. 400 гигабайт на инстанс базы данных – это внутренний потолок для корректной поддержки базы со стороны DBA.
Мы понимали, что с текущим приростом данных у нас есть ещё месяц до того, как мы достигнем предельного размера базы и когда начнутся новые проблемы.
В базе было несколько таблиц большого размера, бóльшую часть которых занимали неактуальные данные. По факту для работы нам была необходима только информация за последний месяц. Всё остальное, накопленное за много лет, было нам совершенно не нужно и висело мёртвым грузом. Такие таблицы часто встречаются, например, сессии или логи операций. А архитектура PostgreSQL устроена так, что мы не можем дёшево удалить лишние данные.
Задача
Мы хотим хранить только актуальную информацию за необходимый период времени. Для этого необходимо найти способ разделения большой таблицы на секции, которые будут содержать данные в зависимости от степени их актуальности. При этом необходимо сохранить полную консистентность данных и не допустить просадок в производительности на всех этапах процесса разделения.
И мы начали думать, как можем дёшево расстаться с накопленными за годы данными. Можно ли взять и просто отрезать старый ненужный кусок от всей базы данных.
У нас есть змейка, которая постоянно ест и удлиняется – как игра на телефоне. Нам для работы нужна только её голова, а мы тащим совсем ненужный хвост. Если мы его отрубим, то станет сильно легче, мы сразу выиграем. Нужно научиться спокойно терять хвост, чтобы при этом всё работало.
Решение
Оказалось, что это очень распространённая проблема. Сначала я прошёлся по стандартным методам очистки таблиц от устаревших данных.
Cron+Vacuum не помогли
Первый способ, который я рассмотрел, – это очистка кроном (cron). Мы задаём условия выборки, и cron с нужной периодичностью приходит, выбирает неактуальную информацию и удаляет её. Например, записи старше месяца. Для таких задач определяется лимит, потому что записей может быть очень много, и такая ёмкая история будет тормозить всё остальное.
У крона свой плюс в том, что он простой. Но есть большой минус. Для того, чтобы он был эффективен, он должен удалять не меньше записей, чем их постоянно прибавляется. То есть если у нас новых записей появляется больше, а он не успевает столько удалять, он не будет эффективен, и таблица будет разрастаться.
Также проблема заключается в том, что PostgreSQL спроектирован таким образом, что фактически он ничего не удаляет, а просто помечает на удаление. Строки помечаются на удаление, но физически остаются на диске, и место не освобождается. Данные просто переносятся в условную корзину и по факту место занимают.
Когда происходят изменения строки в PostgreSQL, он вносит в строку изменения, создаёт её копию с изменёнными данными, а старую строку просто помечает на удаление.
И бывает так, что объём бесполезной базы кратно превышает полезную. Bloat (раздувание) – это ситуация, когда из-за накопления устаревших и/или неактуальных данных таблицы или индексы в PostgreSQL занимают значительно больше места на диске, чем нужно для хранения полезных данных. Это приводит к снижению производительности, так как требуется обрабатывать больше информации, чем нужно на самом деле.
Накопление bloat ведёт к следующим проблемам:
увеличивается потребление памяти на диске. Полезных данных может быть сильно меньше, чем всего занято места;
увеличивается потребление памяти для кеша, так как кешируются страницы, а полезных данных в них мало;
замедляется полное сканирование таблиц и индексов;
замедляется индексный доступ.
Если есть таблица, в которой 10 строк, и мы каждую строку изменили, например, по 3 раза, то у нас фактически 40 строк в таблице, из которых 30 помечены на удаление, а 10 актуальные. Это особенность архитектуры PostgreSQL.
Далее PostgreSQL автоматически проводит с ненужными ячейками процедуру vacuum (вакуум). Она сжимает ячейку и освобождает, но не схлопывает её. По сути он делает её переиспользуемой.
Вакуум говорит, что ячейки стали пустыми, и теперь в них можно помещать новые записи. Но размер таблицы фактически не уменьшается, а остаётся прежним и занимает столько же места.
То есть нам нужна связка cron, который будет успевать вычищать все ненужные данные, и vacuum, который будет успевать освобождать ячейку. А если мы имеем уже большую таблицу, даже сочетание двух этих процессов не сможет никогда её сжать.
Когда в дырявое ведро наливается больше воды, чем вытекает через дырки, то рано или поздно вода побежит через край. А нам этого не нужно.
Чтобы физически освободить место и удалить ячейку есть механизм Vacuum Full, которым редко пользуются, потому что он накладывает эксклюзивную блокировку на таблицу пока обрабатывает её, а это неминуемо приведёт к деградации.
Плюсы и минусы метода:
+ нет простоя;
+ в отличие от простого delete, можно контролировать риски падения по out of memory, когда выборка в запросе не помещается в оперативную память;
+ предпочтительнее, чем вариант с временной таблицей при условии удаления незначительной части данных;
– физически место не высвобождается;
– пока vacuum не пройдет, размер базы будет расти за счет новых данных;
– много работы для вакуума;
– нужно писать SQL-код;
– медленно работает;
– нужно подбирать размер батча так, чтобы удаление длилось не слишком долго: чем меньше батч, тем дольше работает функция.
Temporary tables — тоже не решение
Следующим я рассмотрел метод с временными таблицами. Он основан на создании временной таблицы, в которой будут содержаться только нужные данные, последующем удалении старой таблицы с неактуальными данными и переименованием новой таблицы в старую.
У этого метода есть два разных способа реализации:
Выгрузка данных в файл и последующее восстановление новой таблицы из этого файла;
Непосредственное создание нового объекта в базе.
Первый вариант осуществляется с помощью команды copy. Сначала выгружаем необходимые данные в csv, затем создаём в базе новую таблицу, схема которой будет аналогична схеме целевой таблицы, и загружаем данные из файла в новую таблицу. Старую таблицу удаляем, а новую переименовываем в старую.
Второй вариант подразумевает использование операции create table table_name as select .... В этом случае необходимое количество телодвижений меньше, поскольку не требуется промежуточный файл, выборка идёт сразу в новую таблицу. С другой стороны, может быть полезно иметь "бэкапный" файл таблицы для тестирования чего-нибудь.
Плюсы и минусы метода:
+ относительно быстрый;
+ возможность избирательного удаления данных;
+ после всех манипуляций дисковое пространство освобождается;
+ попутно можно изменить структуру таблицы (не добавлять в новую таблицу ненужные столбцы или наоборот, добавить новые).
– Всё же нужен vacuum
– Таблица недоступна на время удаления старой таблицы и переименования новой
– Требуется дополнительное место для временной таблицы (и csv-файла, если используется).
В итоге метод с временной таблицей мне тоже не подошёл, потому что во время переноса и удаления будет простой, а его нельзя допустить.
Все известные и доступные методы были тяжеловесные и требовали остановки базы, но её нельзя остановить, потому так будут большие потери. Поэтому я искал другой способ.
Мой способ – партицирование+truncate
Я зацепился за truncate. Команда truncate (транкейт) – это полное моментальное удаление таблицы, она освобождает место сразу же. Но проблема в том, что truncate удаляет сразу всё, не оставляя нужный кусок, таблица исчезает целиком.
Я пришёл к варианту, что хочу сделать транкейт, но не всей таблицы, а только нужного куска. В PostgreSQL есть стандартный механизм партицирования таблицы, когда она делится на партиции – части. Обычно партицирование таблицы делают на этапе проектирования базы, когда заранее закладывают большие объёмы данных, с которыми нужно будет правильно обращаться. А таблицу, в которой уже накоплен большой объём, очень сложно партицировать, и этот вариант не рассматривают.
Так я оставил большую таблицу со старыми данными и присоединил к ней новую пустую партицию, в которую будут поступать только новые данные. То есть всё новое, что у нас появляется, кладётся только в новую часть.
При этом сервис видит все партиции как одну единую таблицу и ничего внутри не меняет: как работала с одной таблицей, так и работает.
Стоит уже построенный дом. Количество жильцов в нём всё пребывает и скоро они все туда не поместятся. Условия таковы, что строить новый дом нельзя. Но можно к старому пристроить новую часть. Юридически это будет один дом под одним адресом. Но по факту это будут два дома.
Дальше я сам определяю, где необходимо сделать отсечку. Нужно просто логически понять, в какой момент отрезать данные. Для этого набираем нужный объём данных, накапливаем их и в тот момент, когда понимаем, что старая огромная партиция нам больше не нужна, мы её обрезаем. Моментально. Хитро, просто, красиво.
Партицирование бывает двух видов: методом наследования таблиц и декларативным. В решении задачи я попробовал оба варианта.
1. Партицирование методом наследования
Для исходной таблицы A создаём 12 партиций (A_m1, A_m2... A_m12) методом наследования (inherit). Затем на базовую таблицу создаём RULE (либо TRIGGER), который перед вставкой данных определяет, в какую именно секцию должна попасть новая запись и перенаправляет INSERT.
После того, как в секциях накоплен необходимый срез данных для работы сервиса, очищаем базовую таблицу оператором TRUNCATE_ONLY и создаём регламентную задачу (cron), который очищает секции с устаревшими данными автоматически.
Алгоритм реализации:
Создаём миграцию, разделяющую таблицу на наследуемые таблицы
-- Выполняем в цикле создание 12 партиций do $$ declare i int4; begin for i in 1..12 loop -- Создаем партицию execute format('CREATE TABLE A_m%s ( like A including all )', i ); -- Делаем ее наследником базовой таблицы execute format('ALTER TABLE A_m%s inherit A', i); -- Делаем ограничение на вставку данных в эту партицию execute format('ALTER TABLE A_m%s add constraint partitioning_check check (extract("month" from created_at) = %s )', i, i); -- Делаем правило на базовую таблицу для изменения места назначения при INSERT-е новой записи execute format('CREATE RULE A_m%s AS ON INSERT TO A WHERE ( extract("month" from created_at) = %s) DO INSTEAD INSERT INTO A_m%s VALUES (NEW.*);', i, i, i); end loop; end; $$;По прошествии интервала, достаточного для среза актуальных данных, очищаем базовую таблицу:
TRUNCATE TABLE ONLY A;Создаём регламентную операцию (cron) по периодической очистке партиций.
Плюсы и минусы метода:
+ Просто и быстро реализовать партицирование, отсутствие накладных расходов (простоев сервиса) при выполнении миграции.
+ Отсутствие необходимости введения ключа секционирования и доработок в коде. Вся работа метода реализуется средствами СУБД.
– Ограничение на изменение опорного поля (например created_at – даты создания записи). Возможно изменение только в рамках секции, нет возможности переместить в другую секцию запись при обновлении.
– SELECT происходит во всех секциях, по всем ключам. Ускорение работы достигается только за счет уменьшения объема хранимых данных.
2. Партицирование декларативным методом
Партицирование декларативным методом предполагает использование таблицы А как точки входа (таблицы не имеющей своих данных) и N+1 секций, данные в которых распределены по ключу секционирования. Исходная таблица с данными подключается как default-секция. В ней создаётся новая колонка, представляющая собой partition_key, которая должна быть определена NULL или 0.
Для хранения новых данных создаём 12 секций (A_m1, A_m2... A_m12), имеющих точно такую же схему как базовая таблица и default-секция. В каждой секции при создании таблицы задаём ограничение partition_key, равное индексу секции (1,2,3...12).
При выполнении запросов INSERT необходимо указывать значение partition_key, заранее рассчитанное программным путём, например выделением месяца из значения колонки created_at, для того чтобы СУБД могла определить размещение записи среди всех секций.
При выполнении UPDATE-запросов также нужно рассчитывать изменение partition_key. Записи будут автоматически перемещаться между секциями. Таким образом можно вытащить записи из default-секции в более актуальные секции для избежания удаления этих данных.
После того, как в секциях накоплен необходимый срез данных для работы сервиса, очищаем оператором TRUNCATE_ONLY секцию A_default и создаём регламентную задачу (cron), который очищает секции с устаревшими данными.
Алгоритм реализации
Создаём миграцию, переводящую таблицу в ранг default-секции и создания новых секций для хранения данных в разрезе месяцев.
-- добавляем в старую таблицу колонку партицирования alter table A add column partition_key integer default 0 not null; -- создаем новый первичный ключ, сначала как индекс create unique index concurrently A_id_p_key_unique_idx on A(id, partition_key); -- удаляем старый первичный ключ alter table A drop constraint A_pkey; -- переводим индекс в первичный ключ alter table A add constraint A_pkey primary key using index A_id_p_key_unique_idx; -- добавим индексы, который задействованы в первичном ключе, если такие имеются -- create unique index concurrently A_id_p_key_unique_authed_idx on A(id, p_key) where (some_condition = true); -- добавляем невалидированный чек колонки партицирования (позволит быстро подключать партиции). Если сразу сделать валидный чек, то это повлечет за собой LOCK таблицы на время создания alter table A add check (partition_key = 0) not valid; -- валидируем его через shared lock, не отразится на доступности таблицы, пока будет идти валидация alter table A validate constraint A_p_key_check; -- новая, уже партицированная таблица create table A_partitioned ( -- тут расписываем DDL как в исходной таблице, добавив... partition_key integer default 0 not null, PRIMARY KEY (id, partition_key) ) PARTITION BY LIST (partition_key); -- переименовываем таблицы, чтобы партицированная была как A alter table A rename to A_default; alter table A_partitioned rename to A; -- базовые индексы для партиций (полностью повторяют индексы A_default) -- не нужен режим конкурентности, потому что это пустая таблица под партиции -- create index ... -- подключаем дефолтную партицию alter table A attach partition A_default default; -- вуаля, мы имеем партицированную таблицу с 1 партицией в которую падают все записи -- создаем с цикле партиции 1...12 для p_key 1...12 do $$ declare i int4; begin for i in 1..12 loop execute format('CREATE TABLE A_m%s PARTITION OF A FOR VALUES IN (%s);', i, i ); end loop; end; $$;Дорабатываем код сервиса, во всех запросах INSERT (и при необходимости UPDATE) мы передаём рассчитанное значение partition_key. До момента релиза доработки все новые записи будут попадать в A_default
По прошествии интервала, достаточного для накопления среза актуальных данных, очищаем базовую таблицу (при необходимости, делаем её локальный дамп через pg_dump/pg_restore):
TRUNCATE TABLE ONLY A;Создаём регламентную операцию (cron) по периодической очистке партиций.
Плюсы и минусы метода:
+ Возможность перемещения данных между секциями, например, это удобно для хранения обновляемых сессий, где некоторые из записей таблицы не теряют свою актуальность.
+ Возможность увеличения производительности за счёт поиска записей только в нужной секции.
– Сложность алгоритма реализации декларативного партицирования.
– Необходимость доработки кода сервиса для передачи ключа партицирования в запросах INSERT/UPDATE.
Запас времени
У моего способа есть ограничение. Нужно иметь запас времени и места на сервере, чтобы успеть определиться, с какой временной периодичностью отсекать у змейки растущий хвост.
После реализации этого метода нужно выдать минимальное доступное окно, которое нужно сохранить. Если нет этого окна, осталась неделя, а нужны данные за месяц, то придётся остановить базу данных ночью. Это будет минимальная жертва. К сожалению, других вариантов нет.
Если мы понимаем, что храним сессии только за последний месяц, то нам нужно накопить данные за месяц. Если мы понимаем, что у нас актуальность данных уже в разрезе недели, то нужна неделя.
Конкретно в нашем случае требовался срез данных за последний месяц.
В итоге
Вся разработка от идеи до готовности к реализации заняла у меня месяц. Изначально я экспериментировал на локальной базе разными способами. После того, как мне дали зелёный свет и я отрезал ненужную базу, освободилось 80% места. Всё прошло гладко, без сбоев.
Мне было важно, чтобы не было просадок по производительности, чтобы всё происходило моментально. Чтобы пользователи как работали, так и работали, не важно, что я делаю и меняю.
После того, как отрезали старые данные, важно не забыть автоматизировать процесс очистки ненужных партиций. В данном случае потребовалось разделить таблицу на 12 партиций, каждая из которых накапливает данные ровно один календарный месяц. Количество партиций зависит от нужной вам гранулярности.
Заключение
Каждый из приведённых выше способов имеет свои преимущества и недостатки, и также может быть использован как база для секционирования большой таблицы для дальнейших целей.
Декларативное секционирование (доступно только в PostgreSQL версии 10.0 и новее) является надстройкой над секционированием методом наследования. В своей базовой реализации оба метода не дают как существенного выигрыша, так и просадки производительности.
Лучшая производительность может быть достигнута за счёт уменьшения размера хранимых данных.
Для ускорения поиска данных в декларативно секционированной таблице необходима обязательная передача ключа партицирования (p_key) в теле условия запроса. В противном случае поиск будет идти по всем секциям (как это происходит в случае наследования).
Мой способ универсален для всех, кто использует PostgreSQL. Его можно модифицировать под конкретные задачи.
В большинстве случаев тяжёлые манипуляции с базой данных требуют либо понижения производительности, либо вообще остановки работы системы. Мой метод ничего не понижает и тем более не останавливает. Всё как работало, так и работает.
Важно, что мы по получаем не только прирост свободного места, мы также получаем большой прирост в производительности базы данных.
Узнать больше о задачах, которые решают инженеры AvitoTech, можно по этой ссылке. А вот тут мы собрали весь контент от нашей команды — там вы найдете статьи, подкасты, видео и много чего еще. И заходите в наш TG-канал, там интересно!
