PostgreSQL 9.2: переводим клиента с MySQL 5.1 ради каскадной репликации и JSON

PostgreSQL 9.2 вышел в сентябре, и несколько новых фич сразу попали в поле зрения: index-only scans, каскадная репликация standby-to-standby и нативный тип JSON. Месяца не прошло, как один из клиентов - интернет-магазин с продуктовым каталогом на несколько десятков тысяч позиций - пришёл с проблемой, которую MySQL 5.1 решить не умел.

Ситуация была такая. Продуктовый каталог хранит атрибуты товаров в ширину: сотни колонок под разные категории, большинство NULL. Попытка нормализовать EAV-сеткой уже делалась год назад - закончилась болью при джойнах и просьбой «сделайте как было». Параллельно клиенту понадобилась реплика для отчётов в другом дата-центре, и это оказалось последней каплей: MySQL 5.1 умеет только один уровень репликации, каскад там невозможен без костылей вроде нескольких master-slave цепочек вручную.

Суммарно три требования: гибкое хранение атрибутов, каскадная репликация, производительность на read-heavy нагрузке. PostgreSQL 9.2 закрывает все три.

Что реально изменилось в 9.2

Index-only scans - пожалуй, самое ощутимое для нас. До этого Postgres всегда ходил в heap-таблицу за visibility check, даже если индекс содержал все нужные данные. Теперь нет. На каталоге с covering-индексами по category + price + stock несколько запросов для листинга перестали трогать основную таблицу вообще - видно по EXPLAIN (ANALYZE, BUFFERS).

Каскадная репликация работает через streaming replication: standby-узел сам может быть источником для других standby. Схема у клиента получилась: primary в основном ДЦ -> standby 1 в том же ДЦ (failover) -> standby 2 в удалённом ДЦ (отчёты). Primary отдаёт WAL только первому standby, сетевую нагрузку между датацентрами несёт уже промежуточный узел. В MySQL 5.1 это потребовало бы либо двойной нагрузки на primary, либо самодельного relay.

Тип JSON пока простой - хранит и валидирует текст как JSON, возвращает его обратно. Функции для разбора скромные: json_array_length, array_to_json, row_to_json. Операторов для point-access по ключу из коробки нет - это больно, обходим через pl/pgsql-функцию. Тем не менее для каталога это уже лучше, чем wide-table: атрибуты конкретной категории идут одним JSON-полем, схема не пухнет.

Разница в синтаксисе и инструментах

Часть команды знала MySQL как родной, поэтому зафиксируем нетривиальные моменты.

AUTO_INCREMENT -> SERIAL / SEQUENCE. В MySQL id INT AUTO_INCREMENT, в Postgres id SERIAL (или BIGSERIAL). Последовательность живёт отдельным объектом, currval/nextval - через неё.

LIMIT/OFFSET - синтаксис тот же, но OFFSET без LIMIT Postgres принимает, MySQL нет. Мелочь, но напоролись в двух запросах при переносе.

Строковые кавычки - в Postgres одинарные для строк, двойные для идентификаторов. В MySQL по дефолту двойные сходят за строки. Пришлось прогнать все запросы через sed и перепроверить руками.

SHOW TABLES / SHOW COLUMNS - в Postgres это \dt и \d tablename в psql, либо через information_schema. Скрипты мониторинга правили отдельно.

Инструменты бэкапа. mysqldump меняется на pg_dump. Логика похожая, форматы разные. pg_dump -Fc даёт custom-формат с параллельным восстановлением через pg_restore -j - приятная деталь для больших баз.

pgAdmin III vs MySQL Workbench - пересаживать разработчиков на pgAdmin не стали, попросили работать через DBeaver. Меньше трений.

Где споткнулись

Триггеры на MySQL в синтаксисе FOR EACH ROW BEGIN ... END частично переехали без боли, частично пришлось переписать - Postgres требует отдельную trigger-функцию на pl/pgsql, а не inline-тело. На это ушло несколько часов дольше, чем рассчитывали.

Кодировка: клиентская база была latin1 с utf8-данными внутри (классика). Конвертация через iconv с ручной чисткой - отдельная история, не PostgreSQL виноват.

Промежуточный итог

Миграция в основном завершена, продакшн работает вторую неделю. Каскадная репликация стабильна, WAL-лаг на удалённом standby держится в разумных пределах. JSON-поля в каталоге работают, хотя без удобного key-access приходится таскать весь объект в приложение и разбирать там - надеемся, что следующие версии добавят операторы. Index-only scans на листинге заметны, хотя точных замеров до/после нет - не настроили baseline заранее, что было ошибкой.

Если у вас похожая ситуация с разнородными атрибутами товаров и необходимостью строить репликацию в несколько уровней - задача решаема. Помогаем с переносом баз и настройкой аналитической инфраструктуры.