Всем привет! Поводом для написания этой статьи послужила ситуация, с которой мы в команде Геосервисов столкнулись.

Когда наша база данных нормализованных OSM-данных достигла размеров в 600+ ГБ, VACUUM стал занимать 6+ часов. Мы начали приближаться к пределу хранилища (600 GB из 1 TB), а производительность запросов деградировала. Стало очевидным — партиционирование неизбежно.

Масштаб задачи:

• 760M nodes (179 GB data + 170 GB indexes)

• 800M ways (120 GB data + 42 GB indexes)

• 5B way_nodes (~150 GB)

Изначальная оценка времени миграции: 40-70 часов. Мы выбрали подход shadow tables с zero downtime и автоматическими алертами.

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

Контекст: OSM данные

Мы храним данные OpenStreetMap в нормализованном виде, и вот что это значит.

Структура OSM:

• nodes — точки на карте (координаты, теги);

• ways — линии дорог (состоят из node-ов);

• way_nodes — связь ways ↔ nodes (5B строк!).

Почему это боль:

• Одна дорога = сотни node-ов;

• Удаление/обновление дороги = обновление десятков таблиц;

• Данные растут геометрически: 185M nodes → 800M ways → 5B way_nodes;

• VACUUM на таких таблицах — часы простоя (downtime).

Партиционирование по H3 (геоиндекс) разделяет мир на ячейки.

Как мы выбирали уровень H3 для партиционирования

Что такое H3

H3 — это иерархическая система гексагонов от Uber. Каждый уровень делит ячейки предыдущего уровня на 7 частей. Чем выше уровень, тем мельче ячейки, тем больше партиций.

Данные из официальной документации H3.

Наш выбор: Level 0 (122 партиции)

Мы выбрали H3 Level 0 — 122 гексагона покрывают всю Землю.

Level 1 дал бы 842 таблицы на каждую сущность (nodes, ways, relations). Level 2 — 5,882 таблиц. Для нас это феноменально много: PostgreSQL должен обслуживать тысячи таблиц, а это overhead на метаданные, autovacuum, статистику. А вот 122 партиции — удобное количество, VACUUM на одной партиции не блокирует остальные.

Пустые партиции — это не баг, это фича

Да, большинство партиций пустые или почти пустые. OpenStreetMap данные распределены неравномерно: плотно в городах, разрежено в океанах/пустынях.

Но это было известно заранее, и это нормально:

• пустые партиции не занимают много места (только метаданные, почти нет данных);

• если масштабироваться на другие страны/континенты — партиции заполнятся;

• 122 таблицы для PostgreSQL — это хорошо (не тысячи).

Если бы мы выбрали Level 1/2, получили бы тысячи таблиц. Это было бы больновато контролировать. Но в будущем можно будет выборочно партицировать некоторые L0-партиции по L2-L3.

Это тоже плюс L0-партиционирования — можно позже «дробить» отдельные регионы.

Вывод

Level 0 = правильный баланс. Пустые партиции — «плата» за масштабируемость в будущем.

Было / Стало

Как мы выбирали подход

Когда мы начали планировать партиционирование, рассматривали три варианта.

Вариант 1: pg_dump/pg_restore в новую базу

Классический подход: поднять новую базу, перелить данные, переключить приложения.

Спойлер: к этому и придём

Плюсы:

• чистая схема с нуля;

• не нужно думать о shadow tables;

• можно параллелить pg_dump с -j 8.

Минусы:

• требуется вторая база данных (ещё 700GB+ storage);

• downtime на переключение сервисов (оказалось проще чем shadow table);

• нужно координировать деплой всех приложений;

• при ошибке откат сложный.

Вопрос места для хранения (storage) был для нас критичным: при 700GB занятых из 1TB лимита, поднять вторую базу было нельзя без дополнительных ресурсов.

Вариант 2: ALTER TABLE ... PARTITION BY

В PostgreSQL 12+ появилась возможность конвертировать обычную таблицу в партиционированную через ALTER TABLE. Звучит идеально, но:

• требует exclusive lock на всё время операции;

• на 700GB таблице это часы downtime;

• не работает, если есть foreign keys.

Для production с требованием минимального downtime не подходит.

Вариант 3: Shadow Tables (наш выбор)

Создаём новую партиционированную таблицу рядом со старой, копируем данные batch-ами, в конце атомарный RENAME.

Плюсы:

• минимальный downtime (~100ms на swap);

• возможность pause/resume;

• rollback через обратный RENAME;

• верификация перед swap.

Минусы:

• нужно 2x disk space на время миграции;

• сложнее в реализации;

• нужно отслеживать изменения в исходной таблице (для нас не критично: read-mostly данные, см. контекст ниже).

Мы выбрали shadow tables в связи с тем что в тот момент это показалось лучшим решением из-за того что:

• заказать новый кластер оказалось не так просто, и требовало бы длительного ожидания;

• планировалось закончить за 1-2 дня, shadow table в этом плане более гибкое решение.

Read-mostly данные

Данные OpenStreetMap у нас обновляются раз в неделю из OSM diffs. 99.9% времени данные read-only, поэтому не отслеживали изменения во время миграции.

Какие индексы мы удалили

Довольно простая операция, оказалось что за последние 30 дней не использовалось целых 3 больших индекса в таблице ways:

1. idx_way_tags_gin — не использовался 30 дней (tags query ушёл из кода).

2. idx_way_geom_partially — дублирует idx_way_geom_full (частичный индекс, не нужен).

3. idx_way_timestamp_old — старый индекс, заменён на BRIN-индекс idx_way_timestamp_brin.

Проверка через pg_stat_user_indexes:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'ways'
ORDER BY idx_scan ASC;

Результат:

idx_way_tags_gin         | 0 |     0 |     0
idx_way_geom_partially   | 0 |     0 |     0
idx_way_timestamp_old    | 0 |     0 |     0

Производительность не пострадала — индексы действительно не использовались.

Как мы выбирали тип партиционирования

Вариант 1: LIST по top-150 ячейкам

Изначально пробовали LIST partitioning по top-150 H3 ячейкам. На staging получили 70% данных в DEFAULT партиции. Это убивает смысл партиционирования — большая часть данных всё равно в одной «куче».

Проблема: данные распределены неравномерно. Некоторые H3 ячейки содержат миллионы строк, другие — десятки тысяч. Top-150 ячеек покрывают только 30% данных.

Вариант 2: RANGE по H3 Level 0 (наш выбор)

RANGE по H3 Level 0 даёт 122 партиции. DEFAULT партиция пустая (если h3_l0 вычисляется правильно). Все таблицы (nodes, ways, way_nodes) партиционированы по одному ключу h3_l0, что позволяет использовать partition-wise JOIN.

Почему Grafana недостаточно

У нас была настроена полноценная Grafana с метриками PostgreSQL: disk space, connections, query latency, replication lag. Казалось бы, всё покрыто. Но когда начали планировать партиционирование, стало очевидно, что дашборды мониторинга и дашборд управления процессом — это разные вещи.

Проблема 1: Grafana показывает метрики, но не позволяет действовать

У нас есть общая борда Grafana от команды PostgreSQL-менов. Она показывает детальную информацию: активные транзакции, долгие запросы, блокировки, кто кого ждёт. Проблема не в отсутствии видимости — она в том, что из неё нельзя действовать.

Видишь зависшую транзакцию, которая держит блокировку и мешает миграции. Что дальше? SSH на сервер, найти PID, убедиться, что это безопасно, выполнить pg_terminate_backend. В 3 часа ночи с телефона это не сделаешь.

Реальный кейс из миграции

В 2:47 AM во время копирования batch #342 миграция встала. В Grafana видели зависшую транзакцию PID 18432, которая держала AccessExclusiveLock на таблице ways. Через 3 минуты Telegram бот прислал алерт: «Migration paused: deadlock detected».

Открыли partition-skeleton дашборд (о нём чуть ниже будет рассказано подробнее) с телефона:

1. Вкладка «Active Transactions» → увидели PID 18432, запрос SELECT * FROM ways WHERE tags @> '{"highway":"primary"}'.

2. Нажали «Kill» → миграция продолжилась через 5 секунд.

3. Общее время реакции: 2 минуты.

Без инструмента пришлось бы: VPN → SSH → psql → искать PID → проверять безопасность → pg_terminate_backend. Минимум 15 минут, если всё делать с телефона.

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

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

• на какой фазе миграция (preflight, migrate_batch, create_indexes);

• сколько строк осталось обработать;

• какой batch сейчас выполняется.

Но ничего не сможет с этим сделать. Команды в SQL клиенте решат проблему, но это неудобно: приходится вести внешний notebook, копировать ID транзакций, сопоставлять фазы миграции с метриками. Это увеличивает риск ошибок и замедляет реакцию.

Проблема 3: Алерты уходят в никуда

Миграция требует места под новую таблицу плюс старую плюс индексы. Примерно 2x от размера таблицы. Grafana покажет, что диск заполнен на 95%, и алерт отправится, с этим всё в порядке.

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

Проблемы стандартных алертов:

• общий канал с кучей шума;

• алерт про disk space, а не про «твоя миграция под угрозой»;

• нет автоматической реакции: миграция продолжает заполнять диск;

• у нас в WB не настроен нормальный процесс дежурства и оповещения, каждая команда живёт в «своём мире».

Проблема 4: Deadlock видно, убить нельзя

Deadlock-и и блокировки видно в PostgreSQL dashboard. Кто кого ждёт, как долго, какие запросы. Информация есть.

Но чтобы «прибить» зависшую транзакцию, нужен доступ к базе: SSH, psql, найти PID, убедиться, что это не что-то критичное, выполнить команду. Это занимает время и усложняет реакцию в критических ситуациях.

Migration flow

1. Preflight: проверка disk space, unused indexes, compatibility

2. Create shadow table: ways_new с 122 партициями (по количеству H3 Level 0 ячеек)

3. Migrate data: batch-ами по 100K строк через INSERT ... SELECT (см. обоснование ниже)

4. Create indexes: concurrent index creation на ways_new

5. Verify: COUNT(*) должен совпадать

6. Swap: атомарный RENAME (downtime < 100ms)

7. Cleanup: DROP ways_old через 24 часа

Выбор batch size

Мы тестировали разные размеры batch-ей на staging (10% данных):

Выбрали 100K строк на batch. Это даёт хороший баланс между скоростью копирования и потреблением памяти.

Оценка времени миграции

На staging (10% данных от прода):

• копия 10% заняла 2 часа;

• пропорция: 2 * 10 = 20 часов;

• плюс запас на непредвиденное: *2 = 40 часов.

На prod прошло быстрее (~20 часов) за счёт:

• более мощное железо (32 cores vs 16 cores на staging);

• parallel workers (4 workers копировали разные партиции);

• запуск в ночь пятницы (минимум нагрузки от пользователей).

Итог: оценка 40-70 часов была консервативной, реальность — ~20 часов.

Инструменты для операционного управления миграцией

Стандартный мониторинг (Grafana) показывал метрики, но не давал инструментов для быстрой реакции. Видишь deadlock — но чтобы прибить транзакцию, нужен SSH, psql, поиск PID. В 3 часа ночи с телефона это не сделаешь.

partition-skeleton

Написали partition-skeleton — web-дашборд на HTMX с обновлением в реальном времени.

Основные возможности:

• управление миграцией: Pause/Resume/Swap/Rollback;

• Storage Analyzer: размер таблиц, распределение по партициям, missing indexes;

• Active Transactions: мониторинг и уничтожение транзакций из UI;

• Watcher: автопауза при disk space < 10%, алерты в Telegram;

• REST API + Prometheus метрики.

Вспомним реальный кейс, который был выше. На всё (бот прислал алерт, открыли дашборд с телефона → killed транзакцию) потребовалось 5 секунд. Без инструмента: 15+ минут.

Telegram алерты

Автоматические алерты несколько раз реально спасали нас:

1. 2:47 AM: Telegram: «Disk space warning: 12% free». Миграция на паузе.

2. 2:48 AM: Проверил с телефона. Увидел, что старые индексы не удалились.

3. 2:50 AM: Через dashboard удалил индексы. Возобновил миграцию.

4. 2:51 AM: Спокойно лёг спать.

Без автоматических алертов узнал бы утром, когда диск заполнился бы полностью и миграция упала с ошибкой. Откат и повторный запуск — это ещё 40+ часов.

7 неожиданных проблем при партиционировании

1. DEFAULT партиция — скрытая ловушка

При партиционировании можно создать DEFAULT партицию для «всего остального». Звучит безопасно. На практике:

CREATE TABLE ways_new (
    id BIGINT,
    h3_l0 BIGINT,
    ...
) PARTITION BY RANGE (h3_l0);

-- Создаём партиции для известных значений
CREATE TABLE ways_p_000 PARTITION OF ways_new FOR VALUES FROM (...) TO (...);
-- ...

-- DEFAULT для "всего остального" (не создавали!)
-- CREATE TABLE ways_default PARTITION OF ways_new DEFAULT;

Проблема: если h3_l0 вычисляется с ошибкой или NULL, строки попадают в DEFAULT. При добавлении новой партиции PostgreSQL сканирует весь DEFAULT, чтобы переместить подходящие строки. На 185M строк это занимает часы.

Вывод: DEFAULT партиция в идеале должна быть пустой, либо занимать не больше 5%. Если не ноль — это баг в вычислении partition key.

2. Disk space: точный расчёт

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

Исходное состояние:

• Занято на диске: 700GB (все таблицы + индексы);

• Свободно: 324GB (1TB - 700GB).

Таблица ways:

• Data: 162GB;

• Indexes: 42GB;

• Total: 204GB.

Shadow table ways_new:

• Data: 162GB (копия);

• Indexes: ~200GB (будут созданы заново, оптимизированные);

• Total: 362GB.

Пиковое потребление:

• Старый ways: 204GB (не дропаем до swap)

• Новый ways_new: 362GB

• Temp space для CREATE INDEX: ~100GB (SORT memory)

• Итого пик: 666GB

Доступно: 324GB + 204GB (если дропнуть индексы на старом) = 528GB. Не хватает!

Решение:

1. Удалить 3 неиспользуемых индекса на старой таблице (см. «Какие индексы мы удалили» выше)

2. Освобождается ~200GB

3. Итоговый доступный объём: 528GB + 200GB = 728GB > 666GB ✅

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

3. enable_partitionwise_join: по умолчанию OFF

Партиционирование сделано, запросы должны ускориться. Проверяем EXPLAIN:

EXPLAIN SELECT * FROM way_nodes wn
JOIN nodes n ON n.id = wn.node_id;

Результат: сканирует все 122 партиции (по количеству H3 Level 0 ячеек) nodes. Почему?

Параметр enable_partitionwise_join по умолчанию выключен. PostgreSQL не использует partition pruning для JOIN-ов.

Решение: добавить в postgresql.conf:

enable_partition_pruning = on
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

После этого JOIN между партиционированными таблицами работает эффективно, но только если в JOIN условии есть partition key:

-- Плохо: сканирует все партиции
SELECT * FROM way_nodes wn JOIN nodes n ON n.id = wn.node_id;

-- Хорошо: partition pruning работает
SELECT * FROM way_nodes wn JOIN nodes n ON n.id = wn.node_id AND n.h3_l0 = wn.h3_l0;

4. Sequence ownership после RENAME

После swap таблиц:

ALTER TABLE ways RENAME TO ways_old;
ALTER TABLE ways_new RENAME TO ways;

Sequence ways_id_seq остаётся привязан к ways_old:

SELECT pg_get_serial_sequence('ways', 'id');
-- NULL или ways_old

INSERT с автоинкрементом будет сломан.

Решение: явно переназначить после swap:

ALTER SEQUENCE ways_id_seq OWNED BY ways.id;

5. Дубликаты при retry batch

Batch упал после INSERT. River Queue делает retry. Строки вставляются повторно.

Решение банально: использовать ON CONFLICT DO NOTHING:

INSERT INTO ways_new (id, h3_l0, tags, linestring, ...)
SELECT id, h3_l0, tags, linestring, ...
FROM ways
WHERE id > $last_processed_id
ORDER BY id
LIMIT $batch_size
ON CONFLICT (id, h3_l0) DO NOTHING;

PRIMARY KEY партиционированной таблицы должен включать partition key — это требование PostgreSQL.

6. Foreign Keys на партиционированные таблицы

Синтаксис: FK, ссылающиеся на партиционированную таблицу, работают с PostgreSQL 12+. FK из партиционированной таблицы требуют включения partition key в constraint.

Нельзя:

ALTER TABLE way_nodes ADD CONSTRAINT fk_way
    FOREIGN KEY (way_id) REFERENCES ways(id);

Можно:

ALTER TABLE way_nodes ADD CONSTRAINT fk_way
    FOREIGN KEY (way_id, h3_l0) REFERENCES ways(id, h3_l0);

7. VACUUM и bloat после массового INSERT

После миграции 760M строк планировщик PostgreSQL не знает реальную статистику новой таблицы. Запросы могут выбирать неоптимальные планы.

Решение: запустить ANALYZE сразу после миграции данных, до создания индексов:

ANALYZE ways_new;

Важный спойлер: эти семь проблем мы преодолели в процессе партиционирования.

Что бы сделали иначе

1. Более тщательный анализ индексов перед миграцией. На продакшене столкнулись с проблемой: преждевременно удалили индексы и усложнили Preflight-этап. Пришлось переносить. Теперь используем pg_stat_user_indexes, чтобы найти неиспользуемые индексы за неделю до миграции.

2. Тестировать на staging с реальным объёмом данных. Чем больше staging похож на prod, тем лучше. У нас из-за объёмов получилось проверить только на 10% от прода 😅

3. Настроить алерты ДО начала миграции. Telegram бот, webhook в Slack, что угодно. Узнавать о проблемах сразу, а не через N часов. У нас идея про дашборд пришла позже, чем хотелось бы.

4. Подготовить чеклист для типичных проблем:

   • место на диске заканчивается — какие индексы можно удалить;

   • долгая транзакция — как определить безопасность принудительного завершения;

   • миграция зависла — как найти причину и продолжить;

   • провести knowledge sharing с коллегами, чтобы все знали, что делать в той или иной ситуации.

5. Проверить совместимость всех сервисов. У нас 3 из 4 сервисов имели hardcoded имена таблиц. После swap они бы упали. Теперь проверяем codegrep-ом до начала миграции.

Если бы мы начали раньше

Безусловно, многих этих проблем можно было бы избежать. Нормализованные OSM данные растут быстро — это очевидно. Если бы мы спроектировали партиционирование на этапе архитектуры, когда таблицы были 50GB вместо 600GB, первая миграция прошла бы без боли.

Но история сложилась именно так. Нормализованные данные росли, VACUUM занимал 6+ часов, и мы действовали по принципу «лучшее время — сейчас».

Урок на будущее: если видите, что таблица растёт к 300GB+, думайте о партиционировании раньше, чем станет больно.

Ради чего всё это было

Партиционирование 560GB таблицы заняло 20 часов. Стоило ли оно того?

Краткосрочные выгоды (получили сразу после миграции)

VACUUM сократился с 6+ часов до ~20 минут:

• Раньше: VACUUM замедлял таблицу на 6+ часов;

• Теперь: VACUUM на отдельной партиции — 20 минут, остальные партиции работают.

Запросы с фильтрацией по h3_l0 ускорились

• Пример: SELECT * FROM ways WHERE h3_l0 = 5800000000000001;

• Было: 2.3 секунды (Seq Scan по всей таблице);

• Стало: 340ms (Index Scan по одной партиции из 122).

Размер индексов уменьшился на ~40%

• Раньше: один индекс на 560GB таблицу;

• Теперь: 122 маленьких индекса (по количеству H3 Level 0 ячеек) по 4-6GB каждый;

• Экономия: ~200GB disk space.

Parallel operations

• Раньше: один VACUUM/что угодно могло блокировать всё;

• Теперь: VACUUM на 5 партициях одновременно, остальные работают.

Долгосрочная архитектура

Вот, что на самом деле важно. Партиционирование — это инвестиция в будущее:

1. Шардирование станет в N раз проще. Detach партицию → перенести на новый кластер → Attach. Zero downtime, работаем с 5GB вместо 600GB.

2. Maintenance без блокировок. VACUUM, REINDEX, ANALYZE на отдельных партициях пока остальные продолжают работать.

3. Faster recovery после сбоя. Повреждена одна партиция → восстанавливаем 5GB вместо 600GB.

4. Parallel operations. Несколько операций на разных партициях одновременно без блокировок.

5. Testing на production data. Берём одну партицию, тестируем изменения, применяем на все если работает.

6. Graceful data retention. Старые партиции можно архивировать или удалять без болезненных DELETE + VACUUM.

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

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