Привет! На связи вновь команда Геосервисов. Как вы помните, в прошлой статье я делился нашим опытом партиционирования и выводами, к которым мы пришли. Но на этом история не закончилась. Что же было дальше?

Партиционирование завершилось успешно. VACUUM сократился с 6+ часов до ~20 минут. Запросы ускорились. Мы думали, что всё позади. Через неделю после swap проверили реплику — и обнаружили, что она пуста.

Обнаружение: реплика пустая

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

SELECT COUNT(*) FROM nodes;

На реплике: 0 строк. На мастере: 760M строк.

Всё стало понятно: все наши партиционированные таблицы — nodes, ways, way_nodes — не реплицировались. Реплика не получала данные.

Проверили pg_class.relpersistence:

SELECT relname, relpersistence FROM pg_class WHERE relname LIKE 'nodes%';

 relname     | relpersistence
-------------+----------------
 nodes       | u
 nodes_p_000 | u
 nodes_p_001 | u
 ...

'u' = UNLOGGED. Все таблицы.

Что такое UNLOGGED

UNLOGGED таблицы в PostgreSQL — это таблицы, данные которых не записываются в WAL (Write-AHEAD LOG). Это даёт прирост производительности на запись, но:

1. Данные не реплицируются — реплики остаются пустыми.

2. Данные теряются при crash — если сервер падает, UNLOGGED таблицы обнуляются.

3. Используются только для кэша/временных данных — не для production данных.

Как таблицы стали UNLOGGED

Во время миграции мы создавали партиции так:

-- Шаг 1: Создаём главную таблицу (изначально UNLOGGED для скорости!)
CREATE TABLE nodes_new (...) UNLOGGED PARTITION BY RANGE (h3_l0);

-- Шаг 2: Создаём партиции (они наследуют UNLOGGED)
CREATE TABLE nodes_p_000 PARTITION OF nodes_new FOR VALUES FROM (...);
-- ... 122 партиции (по количеству H3 Level 0 ячеек)

Почему UNLOGGED? Чтобы ускорить INSERT операций во время миграции. Данные временные, реплика не нужна.

После завершения миграции и swap, мы попытались перевести в LOGGED:

-- Шаг 3: Переводим в LOGGED (по плану)
ALTER TABLE nodes SET LOGGED;  -- SUCCESS, но партиции не перевелись!

PostgreSQL вернул SUCCESS, но не перевёл партиции. Мы проверили pg_class.relpersistence только через неделю.

PostgreSQL Bug: SET LOGGED не работает для партиций

Проблема: SET LOGGED на главной таблице не распространяется на партиции в PostgreSQL < 17.

PostgreSQL:

• возвращает SUCCESS о выполнении, но партиции остаются UNLOGGED;

• не переводит партиции в LOGGED режим;

• не выдаёт warnings или errors;

• pg_class.relpersistence по-прежнему показывает 'u'.

Почитать про баг можно тут.

В PostgreSQL 17 поведение изменили: ALTER TABLE SET LOGGED теперь распространяется на партиции. В PostgreSQL 18 дополнительно улучшили обработку UNLOGGED partitioned tables — теперь поведение более предсказуемое и надежное.

Но у нас PostgreSQL 14. Единственный способ исправить:

1. Создать новую партиционированную таблицу в LOGGED режиме.

2. Перелить все данные туда.

3. Удалить старую таблицу.

Это именно то, что нам пришлось сделать. Но не на том же кластере.

Почему не исправить на месте

Общий объём данных:

• nodes → 760M строк (349GB data + 170GB indexes)

• ways → 800M строк (162GB data + 42GB indexes)

• way_nodes → 5B строк (~150GB)

• Итого: ~600GB data + ~250GB indexes = ~850GB

Время копирования при оптимальных условиях: около 40-70 часов.

Варианты:

1. Копирование в фоне — хорошо, но нужен double storage (850GB+ свободного места). У нас не было.

2. Копирование с throttle — увеличивает время до 100+ часов. Нельзя грузить прод операциями копирования так долго.

3. Cross-cluster copy — наш выбор!

Cross-cluster миграция

Идея: Поднимаем новый кластер (PG17) и переливаем данные туда.

Преимущества:

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

• можно параллелить по всем партициям;

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

• проверка данных до переключения трафика.

Инфраструктура:

• source: PG14 prod (read-only mode для пользователей);

• target: PG17 новый кластер (пустой, готов к приёму данных);

• HAProxy между ними для переключения.

Давайте искать решение!

Вариант 1: pg_dump / pg_restore

Самая большая партиция ~80GB. Попытка сделать pg_dump одной партиции:

pg_dump -h source-host -t nodes_p_058 | pg_restore -h target-host

Результат: Timeout через 2-3 часа (наш HAProxy разрывает соединения, нестабильно поддерживает длительные соединения).

Вывод: pg_dump / pg_restore не подходит для партиций > 50GB в нашей инфраструктуре.

Вариант 2: pgcopydb

Проблемы:

1. Нет поддержки PostGIS типов — наши таблицы содержат geometry и hstore колонки.

2. Плохая поддержка партиций — не понимает партиционированные таблицы как единую сущность.

3. Отсутствие DDL маппинга — нужно полностью идентичное schema (схема).

Результат теста:

ERROR: column "geom" has unsupported type: geometry

Вывод: pgcopydb не готов для production с PostGIS.

Вариант 3: самописный pg-cross-cluster-migrator

pg-cross-cluster-migrator — самописная Go-утилита с custom binary COPY для PostGIS типов.

Архитектура:

• dual-pool design (source RO, target RW);

• partition-aware copy: каждая партиция отдельно;

• data verification: row count + schema diff + min/max ID.

Общая библиотека для cross-cluster-migration сейчас в процессе разработки, пишите в комментариях, если она для вас актуальна, ускорим процесс 😊

Трудности с DDL маппингом

Source (PG14) и Target (PG17) имели различия в schema.

Решение: Автоматический DDL маппинг при создании schema.

Результат

• Время миграции: ~20 часов

• Параллельных workers: 4

• Batch size: 10,000 rows

• Метод верификации: Row count + Schema diff + Min/Max ID + MD5Hash

• Переключение: Zero downtime (atomic HAProxy переключение)

Данные верифицированы, реплика работает, все таблицы LOGGED.

Ключевые уроки из этой истории

1. Никогда не доверяйте неявным преобразованиям (Never trust implicit conversions) — всегда проверяйте результат ALTER TABLE ... SET LOGGED.

2. Проверяйте реплику после партиционирования — SELECT COUNT(*) FROM nodes; на реплике не должен быть 0.

3. UNLOGGED + partitions = очень опасное комбо в PostgreSQL < 17.

4. Cross-cluster migration лучше, чем in-place для больших объёмов.

5. Custom tools лучше, чем generic tools для специфичных задач (PostGIS).

Также хочется выразить огромную благодарность 🤗 команде PostgreSQL за быструю помощь в диагностике и разъяснение бага.

Заключение

Партиционирование 600GB базы заняло около 20 часов. За это время мы столкнулись с нехваткой места, зависшими транзакциями и неожиданными блокировками. Стандартный мониторинг показывал проблемы, но не давал инструментов для быстрой реакции.

Результаты: после партиционирования VACUUM на таблице ways сократился с 6+ часов до ~20 минут.

Запросы с фильтрацией по h3_l0 ускорились благодаря partition pruning:

-- Было (до партиционирования):
EXPLAIN ANALYZE SELECT * FROM ways WHERE h3_l0 = 5800000000000001;

-- Time: 2340.123 ms
-- Seq Scan on ways  (cost=0.00..89000.00 rows=185000000 width=200)
--   Filter: (h3_l0 = 5800000000000001::bigint)

-- Стало (после):
EXPLAIN ANALYZE SELECT * FROM ways WHERE h3_l0 = 5800000000000001;

-- Time: 340.456 ms
-- Index Scan using ways_p_058_idx_h3_l0 on ways_p_058  (cost=0.42..12345.67 rows=1500000 width=200)
--   Index Cond: (h3_l0 = 5800000000000001::bigint)

Мы написали partition-skeleton: Web dashboard с мониторингом в реальном времени, управлением миграцией и автоматическими алертами. Это не замена Grafana, а дополнение с фокусом на операционное управление долгоживущими процессами.

А так же поняли, что shadow copy — совсем не лучший вариант для партиционирования существующих данных, и безопаснее всего выбирать cross-cluster миграцию 🙂

Ключевые уроки:

• DEFAULT партиция в идеале должна быть пустой;

• планируйте 2x disk space: это необходимость, не перестраховка;

• enable_partitionwise_join = on включайте до миграции;

• автоматические алерты настраивайте заранее: в 3 часа ночи SSH с телефона — не вариант.

Partition-skeleton можно использовать как есть или адаптировать под свои таблицы — код открыт и документирован.

План отката

Сценарий: после swap обнаружили regression в запросах или данные повреждены.

Действия:

1. Pause трафик на сервисы (или переключить на read-only mode).

2. Восстановить ways_old из бэкапа (если уже удалили).

3. Обратный swap:

BEGIN;
  ALTER TABLE ways RENAME TO ways_new_broken;
  ALTER TABLE ways_old RENAME TO ways;
COMMIT;

4. Время отката: ~5 минут (если ways_old не удалён)

Чек-лист для вашей миграции

За 2 недели:

• Проверить размер таблицы: SELECT pg_size_pretty(pg_total_relation_size('ways')).

• Проверить индексы: найти неиспользуемые через pg_stat_user_indexes.

• Определить partition key (H3? дата? регион?).

• Подготовить staging с реальным объёмом данных.

За 1 неделю:

• Удалить неиспользуемые индексы (если безопасно).

• Настроить alerts: disk space, deadlocks, migration progress.

• Деплой partition-skeleton (или аналог).

• Прогнать dry-run на staging.

За 1 день:

• Сделать бэкап БД.

• Запустить full vacuum на таблице (опционально).

• Убедиться, что disk space >= 2x table size.

В день миграции:

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

• Мониторить dashboard каждые 30 минут.

• При алертах — реагировать в течение 5 минут.

• После swap — запустить ANALYZE.

• Следить за production 24 часа.

Полезные ссылки

• PostgreSQL Partitioning

• H3: Uber's Hexagonal Hierarchical Spatial Index

• River Queue: PostgreSQL job queue

• enable_partitionwise_join