16.12.2025 22:52
darkeugene 0 4400 Источник

Мы забыли вовремя создать партиции, и все новые данные полетели в 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%-й способ", который позволит мне за долю секунды добавить новую партицию к таблице.

Состоял он в следующем:

  1. Я создаю будущую партицию

  2. Добавляю CHECK NOT VALID на эту партицию, который проверит, что в таблице нет лишних данных, не удовлетворяющих условию ограничения диапазона блоков

  3. Включаю валидацию

  4. Прикрепляю эту партицию к главной таблице

  5. Удаляю этот 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 накладывает эксклюзивную блокировку на родительскую таблицу и проверяет две вещи:

  1. все данные в присоединяемой таблице (у нас она пустая) соответствуют её новому диапазону;

  2. все данные в 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

Это очень долгое время блокировки таблицы и такое мы себе позволить не могли. Пришлось искать иные пути решения.

Подход с минимальным временем блокировки таблицы

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

  1. Я создаю партиции с check и постепенно копирую туда данные из default-партиции

  2. Копирую данные по текущему активному миллиону блоков максимально близко к хвосту (не копирую всё вслепую, так как данные по последним блокам могут меняться и доезжать)

  3. В рамках одной транзакции делаю следующее:

    1. Беру блокировку на родительскую таблицу events

    2. Копирую оставшиеся данные в актуальную партицию

    3. Делаю TRUNCATE events_default_partition

    4. Прикрепляю все новые партиции к родительской таблице

    5. Отдаю блокировку

Я заранее разолью львиную долю строк по целевым диапазонам. В момент транзакции БД почти нечего валидировать/перекладывать — поэтому должны уложиться в небольшое время блокировки.

Единственный минус такого подхода в том, что для этого потребуются дополнительные 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);

Частичное копирование последнего миллиона

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

  1. Сначала создал последнюю партицию вручную:

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);

  1. Скопировал оставшиеся данные в партицию перед последним шагом (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-секция — не склад временного хранения, а бездонная яма.

Комментарии (0)