Мы забыли вовремя создать партиции, и все новые данные полетели в events_default_partition. Default дорос до ~1.1 ТБ, а простое «ATTACH PARTITION» требовало часов сканирования и долгой блокировки. В статье — почему «быстрые» рецепты оказываются медленными, как я перенёс данные в нужные диапазоны, и как мы уложили критическую блокировку в 44 с.
Default-партиция — это не озеро Байкал. Если туда всё сливать, экосистема потом мстит.
Суть проблемы
В нашей базе данных существует таблица events. Данные в неё постоянно дописываются и располагаются в инкрементируемых event_block. В одном event_block может содержаться до нескольких сотен записей. В таблицу постоянно пишутся новые данные и читаются имеющиеся.
Больше всего читаются данные самые новые, но остаётся необходимость в доступе и к старым данным, поэтому эта таблица разбита по event_block на диапазоны по 1 000 000, чтобы ускорить вставку/чтение и упростить обслуживание (вакуум, ретеншн, бэкапы).
Активная партиция — последняя по времени. Каждая такая партиция занимает около 50-100GB.
На первый взгляд всё выглядит хорошо: данных много для одной таблицы, поэтому они разбиты на множество более мелких. Но есть одна загвоздка: партиции создаются вручную нами раз в несколько недель.
Обычно создавали наперёд 5–10 партиций, но однажды окно пропустили — default дорос до ≈ 1.1 ТБ.
Надо было разрезать без многочасовой блокировки записи.
Параметры БД
PostgreSQL: 16.1
Наблюдаемая пропускная способность чтения по EXPLAIN BUFFERS: ~77 MB/s до распила → ~1.5 GB/s после
max_parallel_workers_per_gather: 2
wal_compression: off
Объём default-партиции: 1.1 ТБ → 0 ТБ
Первые попытки решения проблемы
Добавление партиции напрямую
На тот момент в events_default_partition хранились исторические данные (51-64 миллионы) и активно дописываемые данные (65 миллион).
Первая идея была максимально простой: прикрепить к таблице новую партицию на будущий миллион блоков (66) и когда он начнёт наполняться, то все новые данные отправятся в новую партицию, а я постепенно разделю events_default_partition по мелким партициям.
Функция создания партиций уже была:
CREATE OR REPLACE FUNCTION create_events_partition_by_event_block_million( million_start INTEGER ) RETURNS void AS $$ DECLARE partition_start INTEGER; partition_end INTEGER; partition_name TEXT; BEGIN -- Calculate the start and end of the block range based on the input partition_start := million_start * 1000000; partition_end := partition_start + 1000000; -- Generate the partition name based on the million_start partition_name := 'events_partition_m' || million_start || '_m' || (million_start + 1); -- Create the partition EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%s) TO (%s);', partition_name, partition_start, partition_end); RAISE NOTICE 'Partition % created for range % to %', partition_name, partition_start, partition_end; END; $$ LANGUAGE plpgsql;
Осталось её только вызвать:
Select create_events_partition_by_event_block_million(66);
И что могло пойти не так, всё выглядит просто и казалось, что должно сработать идеально.
Но, прождав около 5 минут (а всё это время таблица была заблокирована), я прервал процесс и понял, что это не дело - длится всё слишком долго и надо найти иной подход.
Как я выяснил позже, при добавлении новой партиции PostgreSQL должен убедиться, что ни одна запись в default-партиции не подходит под диапазон новой. Для этого он начинает полное сканирование всех 1.1 ТБ данных, что и вызывало многочасовую блокировку.
Создание партиции отдельно с дальнейшим прикреплением к таблице
Я обратился к GPT, описав проблему и он мне подсказал "действенный 100%-й способ", который позволит мне за долю секунды добавить новую партицию к таблице.
Состоял он в следующем:
Я создаю будущую партицию
Добавляю
CHECK NOT VALIDна эту партицию, который проверит, что в таблице нет лишних данных, не удовлетворяющих условию ограничения диапазона блоковВключаю валидацию
Прикрепляю эту партицию к главной таблице
Удаляю этот
CHECK
При ATTACH Postgres сам добавляет партиционный constraint дочке; наш предварительный CHECK — это предварительная проверка данных для избежания блокировки при ATTACH.
Код выглядит так:
CREATE TABLE events_partition_m66_m67 (LIKE events INCLUDING ALL); ALTER TABLE events_partition_m66_m67 ADD CONSTRAINT events_partition_m66_m67_partition_check CHECK (event_block >= 66000000 AND event_block < 67000000) NOT VALID; ALTER TABLE events_partition_m66_m67 VALIDATE CONSTRAINT events_partition_m66_m67_partition_check; ALTER TABLE events ATTACH PARTITION events_partition_m66_m67 FOR VALUES FROM (66000000) TO (67000000); ALTER TABLE events_partition_m66_m67 DROP CONSTRAINT events_partition_m66_m67_partition_check;
Выглядит код отлично, рабочим. В итоге запускаю его на выполнение.
После 6 минут ожидания я понимаю, что совет не сработал в наших условиях. Совет GPT был бы идеален, если бы default-партиция была пуста. Команда ATTACH PARTITION накладывает эксклюзивную блокировку на родительскую таблицу и проверяет две вещи:
все данные в присоединяемой таблице (у нас она пустая) соответствуют её новому диапазону;
все данные в default-партиции НЕ соответствуют диапазону новой партиции.
Именно второй пункт и запускал полное сканирование нашего терабайтного "монстра".
В итоге все варианты, что мне предлагал GPT, крутились вокруг этого решения и я подумал, может стоит просто чуть дольше подождать и всё сработает? Для этого я решил посчитать сколько времени потребуется БД для того, чтобы прикрепить новую партицию к родительской таблице.
Оценка затрат по времени
С помощью GPT я разобрался в том, как бы я мог посчитать примерное время простоя таблицы на сканирование default-партиции, чтобы добавить новую.
Сначала надо было посчитать скорость чтения данных из этой таблицы:
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events_default_partition WHERE event_block >= 51000000 AND event_block < 52000000;
Результат получился следующий:
Finalize Aggregate (cost=4739280.56..4739280.57 rows=1 width=8) (actual time=99395.925..99396.012 rows=1 loops=1) Buffers: shared hit=25399 read=1001828 -> Gather (cost=4739280.35..4739280.56 rows=2 width=8) (actual time=99395.910..99395.997 rows=1 loops=1) Workers Planned: 2 Workers Launched: 0 Buffers: shared hit=25399 read=1001828 -> Partial Aggregate (cost=4738280.35..4738280.36 rows=1 width=8) (actual time=99395.672..99395.673 rows=1 loops=1) Buffers: shared hit=25399 read=1001828 -> Parallel Index Only Scan using events_default_partition_event_block_index_idx on events_default_partition (cost=0.57..4631941.76 rows=42535435 width=0) (actual time=0.021..93706.088 rows=107919484 loops=1) Index Cond: ((event_block >= 51000000) AND (event_block <= 52000000)) Heap Fetches: 1655 Buffers: shared hit=25399 read=1001828 Planning: Buffers: shared hit=317 read=1 Planning Time: 4.994 ms JIT: Functions: 5 " Options: Inlining true, Optimization true, Expressions true, Deforming true" " Timing: Generation 0.267 ms, Inlining 3.519 ms, Optimization 8.600 ms, Emission 9.538 ms, Total 21.924 ms" Execution Time: 99396.371 ms
Время выполнения: 99396.371 ms ≈ 99.4s
Просканировано 107,919,484 строк
Чтения буфера: 1,001,828 → это ~8 KB * 1,001,828 ≈ 7.64 GB чтения с диска
Это говорит о том, что при чтении основная задержка на диске и 7.64GB были прочитаны за 99.4s, следовательно пропускная способность около 76.9MB/s, что довольно мало.
Это скорость логического чтения по мнению планировщика, а не «сырой диск». На неё влияют OS-кэш, параллелизм, конкуренция и т. д. Видимо, было связано с тем, что таблица сильно разрослась и продолжала свой непрерывный рост, потому как после распила пропускная способность увеличилась до 1500MB/s.
А это значит, что для сканирования потребуется примерно 1100GB / 76.9 (MB/s) = 4 hours
Это очень долгое время блокировки таблицы и такое мы себе позволить не могли. Пришлось искать иные пути решения.
Подход с минимальным временем блокировки таблицы
Нужно было найти решение такое, чтобы таблица была заблокирована минимум времени, так как другие сервисы в неё постоянно пишут и читают, а длительный простой может стоить нам много денег.
В итоге было придумано мною следующее решение:
Я создаю партиции с check и постепенно копирую туда данные из default-партиции
Копирую данные по текущему активному миллиону блоков максимально близко к хвосту (не копирую всё вслепую, так как данные по последним блокам могут меняться и доезжать)
В рамках одной транзакции делаю следующее:
Беру блокировку на родительскую таблицу
eventsКопирую оставшиеся данные в актуальную партицию
Делаю
TRUNCATE events_default_partitionПрикрепляю все новые партиции к родительской таблице
Отдаю блокировку
Я заранее разолью львиную долю строк по целевым диапазонам. В момент транзакции БД почти нечего валидировать/перекладывать — поэтому должны уложиться в небольшое время блокировки.
Единственный минус такого подхода в том, что для этого потребуются дополнительные 1100 GB дискового пространства для новых таблиц. Это мы себе могли позволить.
Реализация подхода
Так как партиций нужно было создать достаточно большое количество, я решил сделать функции в postgres на время распила партиций (P.S. я не сторонник излишней логики на уровне БД, но в формате единовременного решения это выглядело приемлемо)
Копирование данных
Сначала я реализовал функцию, которая бы создавала партицию с проверкой по ограничениям и копировала в неё необходимые данные из default-партиции:
CREATE OR REPLACE FUNCTION create_events_partition_with_copy_data( million_start INTEGER ) RETURNS void AS $$ DECLARE partition_start INTEGER; partition_end INTEGER; partition_name TEXT; constraint_name TEXT; BEGIN -- Calculate the start and end of the block range based on the input partition_start := million_start * 1000000; partition_end := partition_start + 1000000; -- Generate the partition name based on the million_start partition_name := 'events_partition_m' || million_start || '_m' || (million_start + 1); -- Create the partition EXECUTE format('CREATE TABLE IF NOT EXISTS %I (LIKE events INCLUDING ALL);', partition_name); RAISE NOTICE 'Partition % created for range % to %', partition_name, partition_start, partition_end; constraint_name := partition_name || '_partition_check'; EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I CHECK (event_block >= %s AND event_block < %s);', partition_name, constraint_name, partition_start, partition_end); RAISE NOTICE 'CHECK % created for table %', constraint_name, partition_name; EXECUTE format('INSERT INTO %I SELECT * FROM events_default_partition WHERE event_block >= %s AND event_block < %s;', partition_name, partition_start, partition_end); RAISE NOTICE 'DATA HAVE BEEN INSERTED INTO % FROM events_default_partition for period % <= event_block < %', partition_name, partition_start, partition_end; END; $$ LANGUAGE plpgsql;
После я вызывал эту функцию для всех партиций с копированием данных от 51 до 65 миллиона поочереди (исторические данные). Запускал параллельно до двух процессов, чтобы не перегружать сильно БД.
Пример вызова:
SELECT create_events_partition_with_copy_data(51);
Частичное копирование последнего миллиона
Так как последний миллион является активно наполняемым, то для него партиция создавалась вручную с копированием только в необходимых диапазонах. Было это сделано для того, чтобы в рамках единой транзакции осталось для копирования минимум данных и время блокировки таблицы было минимальным.
Сначала создал последнюю партицию вручную:
CREATE TABLE IF NOT EXISTS events_partition_m65_m66 (LIKE events INCLUDING ALL); ALTER TABLE events_partition_m65_m66 ADD CONSTRAINT events_partition_m65_m66_partition_check CHECK (event_block >= 65000000 AND event_block < 66000000);
Скопировал оставшиеся данные в партицию перед последним шагом (2 вставки, так как первая отрабатывала долго, а блокировать таблицу хотелось на минимум времени):
INSERT INTO events_partition_m65_m66 SELECT * FROM events_default_partition WHERE event_block >= 65000000 AND event_block < 65420000; INSERT INTO events_partition_m65_m66 SELECT * FROM events_default_partition WHERE event_block >= 65420000 AND event_block < 65470000;
Прикрепление партиций к родительской таблице
Сначала я сделал функцию, прикрепляющую партицию и удаляющую check из неё:
CREATE OR REPLACE FUNCTION attach_partition_to_events( million_start INTEGER ) RETURNS void AS $$ DECLARE partition_start INTEGER; partition_end INTEGER; partition_name TEXT; constraint_name TEXT; BEGIN -- Calculate the start and end of the block range based on the input partition_start := million_start * 1000000; partition_end := partition_start + 1000000; -- Generate the partition name based on the million_start partition_name := 'events_partition_m' || million_start || '_m' || (million_start + 1); -- Create the partition EXECUTE format('ALTER TABLE events ATTACH PARTITION %I FOR VALUES FROM (%s) TO (%s);', partition_name, partition_start, partition_end); RAISE NOTICE 'Partition % has been attached to events for range % to %', partition_name, partition_start, partition_end; constraint_name := partition_name || '_partition_check'; EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I;', partition_name, constraint_name); RAISE NOTICE 'CHECK % has been dropped from partition %', constraint_name, partition_name; END; $$ LANGUAGE plpgsql;
И последним шагом я реализовал транзакцию, в которой взял блокировку на таблицу, скопировал остаток данных, очистил events_default_partition и прикрепил все новые партиции к родительской таблице. Перед этим я также предупредил коллег, что в течение 5-10 минут таблица может быть недоступна.
P.S. на default-секцию нет внешних ключей; иначе TRUNCATE … CASCADE может снести лишнее.
Визуализация подготовки и транзакции:
// Этап 1: Предварительное копирование (часы, без блокировки) events_default_partition (1.1 ТБ) | +-- INSERT INTO new_partition_51 ... (данные за 51 млн) +-- INSERT INTO new_partition_52 ... (данные за 52 млн) ... +-- INSERT INTO new_partition_65 ... (почти все данные за 65 млн) // Этап 2: Критическая секция (44 секунды, с блокировкой) BEGIN; LOCK events; 1. Копируем "хвост" данных из default в new_partition_65. 2. TRUNCATE events_default_partition; 3. ATTACH PARTITION new_partition_51; 4. ATTACH PARTITION new_partition_52; ... 5. ATTACH PARTITION new_partition_65; COMMIT;
DO $$ BEGIN SET LOCAL statement_timeout = 0; SET LOCAL lock_timeout = '600s'; -- Serialize whole operation so nobody else runs it concurrently IF NOT pg_try_advisory_lock('events'::regclass::bigint) THEN RAISE EXCEPTION 'Another maintenance run is active'; END IF; LOCK TABLE ONLY events IN ACCESS EXCLUSIVE MODE; LOCK TABLE events_default_partition IN ACCESS EXCLUSIVE MODE; -- Copy last data for newest partition INSERT INTO events_partition_m65_m66 SELECT * FROM events_default_partition WHERE event_block >= 65470000; RAISE NOTICE 'Last partition data copied'; -- Clean data from default_partition TRUNCATE TABLE ONLY events_default_partition; RAISE NOTICE 'events_default_partition has been truncated'; FOR million IN 51..65 LOOP PERFORM attach_partition_to_events(million); END LOOP; RAISE NOTICE 'partitioning of events_default_partition has been completed'; PERFORM pg_advisory_unlock('events'::regclass::bigint); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Error: %', SQLERRM; -- In case of any error, be sure to remove the advisory lock PERFORM pg_advisory_unlock('events'::regclass::bigint); RAISE; END $$;
После переноса и очистки данных мы запускаем ANALYZE, чтобы обновить статистику в планировщике PostgreSQL. Это гарантирует, что оптимизатор запросов будет видеть реальный объём и распределение данных в партициях и сможет строить адекватные планы вместо того, чтобы полагаться на устаревшие оценки, что может привести к существенному замедлению запросов.
ANALYZE events;
Также важно не забыть удалить вспомогательные функции, которые больше не используются — нечего плодить мусор в схеме:
DROP FUNCTION attach_partition_to_events; DROP FUNCTION create_events_partition_with_copy_data; DROP FUNCTION create_events_partition_by_event_block_million;
В итоге выполнения я получил следующее сообщение: completed in 44 s 358 ms, что оказалось быстрее моих ожиданий, чему я был очень рад.
По окончанию копирования скорость получения данных возросла, а events_default_partition осталась пустой, что теперь не вызывает проблем с добавлением новых партиций.
P.S. Не забывайте про EXCEPTION с разблокировкой таблицы.
Если что-то пойдёт не так, то транзакция откатится, а advisory lock может остаться висеть.
Всегда предусматривайте такой вариант в сложных миграциях.
Почему ATTACH/VALIDATE медленно на больших объёмах
ATTACH PARTITION корректен, только если все строки дочерней таблицы попадают в границы и строки в default-партиции не противоречат новым условиям.
Для этого Postgres валидирует диапазон/CHECK — фактически сканирует кандидата (дочернюю таблицу) и default-партицию и держит тяжёлые локи.
На сотнях гигабайт это часы.
Мы вынесли скан/перелив заранее, а в транзакции оставили только «добор хвоста» + TRUNCATE + ATTACH, поэтому уложились в ~44 s.
И то, эти 44s в большей степени ушли на «добор хвоста».
Заключение
Самый главный урок этой истории — автоматизируйте рутинные операции. Процесс создания партиций должен быть автоматическим.
Не забывайте про свои таблицы и не давайте им разрастись до огромных размеров, иначе потом с ними будет очень больно работать и особенно неприятно будет их очищать.
А также не забывайте чуть приостановиться и подумать в разные возможные стороны решений, потому как в таком виде решение ко мне пришло не сразу, а по частям. А ИИ-помощники могут как предложить хорошее решение, так и увести куда-то далеко от них.
Default-секция — не склад временного хранения, а бездонная яма.
