PostgreSQL 8.2: перевели учёт рабочего времени с MySQL 4.1 и не пожалели

PostgreSQL 8.2 вышел в декабре прошлого года, но у нас дошли руки только сейчас - выбрали подходящий момент и перевели на него одну из внутренних систем. Речь о самописном учёте рабочего времени: несколько таблиц, ежемесячные отчёты для HR, периодические сводки по проектам. MySQL 4.1 тянул это без особых претензий почти три года. Решение мигрировать было скорее принципиальным, чем вынужденным - хотелось разобраться что PostgreSQL даёт реально, а не на уровне форумных холиваров.

Почему вообще задумались

MySQL 4.1 - рабочая лошадка, мы с ним давно знакомы. Но в этой системе у нас скопилось несколько неудобств, которые MySQL решал плохо или никак:

• Триггеры. В MySQL 4.1 их нет. Логику аудита изменений мы держали на стороне приложения, и это периодически приводило к расхождениям - когда кто-то лез в базу напрямую через phpMyAdmin.
• DDL внутри транзакции. ALTER TABLE в MySQL неявно коммитит всё вокруг. Несколько раз ловили ситуацию когда «накатить схему» заканчивалось частично применёнными изменениями после ошибки.
• Аналитические запросы HR. Несколько ежемесячных отчётов считались через многоуровневые подзапросы с GROUP BY и нарастающими итогами. Работало, но медленно, и SQL выглядел как головоломка.

Плюс нас давно интересовало как база ведёт себя под нагрузкой при более сложных запросах - хотелось живого сравнения.

Что дал PostgreSQL 8.2

Триггеры заработали как надо. Написали простой триггер на INSERT/UPDATE в основную таблицу табелей - он пишет в audit_log кто, что и когда изменил. Теперь не важно откуда пришло изменение - из приложения, из psql или от скрипта. Запись всегда есть. На MySQL это пришлось бы тащить через обёртки в коде.

Транзакционный DDL - отдельная радость. При накатке миграций теперь пишем BEGIN; ALTER TABLE ...; ALTER TABLE ...; COMMIT; - и либо всё применяется, либо откатывается целиком. Несколько раз во время тестовых прогонов специально роняли транзакцию в середине: база возвращалась в исходное состояние без ручного вмешательства. Мелочь, но нервов сохраняет много.

Window-функции переписали HR-отчёты. Вот это оказалось самым ощутимым. Отчёт «нарастающие часы по сотруднику помесячно» раньше делался через подзапрос с коррелированным COUNT() - MySQL честно перебирал таблицу для каждой строки внешнего запроса. На PostgreSQL 8.2 то же самое переписалось через SUM(...) OVER (PARTITION BY employee_id ORDER BY month) и стало читаться как текст, а работать - заметно быстрее. Замеряли на тестовой копии данных: разница в скорости примерно четырёхкратная на типовых HR-запросах. Точные цифры зависят от данных, но порядок именно такой.

Улучшенный планировщик 8.2 тоже не пустые слова. На нескольких запросах с JOIN по трём-четырём таблицам PostgreSQL выбирал план лучше чем MySQL - видно было через EXPLAIN ANALYZE. Особенно заметно на запросах с неравномерным распределением данных: планировщик учитывает статистику по столбцам, а не работает «по умолчанию».

Про миграцию

Сам перенос занял около двух дней вместе с тестированием. Основные затраты времени:

• Синтаксис. MySQL и PostgreSQL в деталях расходятся. AUTO_INCREMENT становится SERIAL, кавычки - одинарные вместо обратных, NOW() работает одинаково, а вот GROUP BY в PostgreSQL строже: нельзя выбирать столбец который не в агрегатной функции и не в GROUP BY. Несколько запросов пришлось переписать.
• Кодировки. PostgreSQL при создании базы просит явно указать encoding и collation. Мы сделали UTF-8 с Russian locale - и потом не пришлось разбираться с тем, что сортировка работает не так как ожидается.
• phpMyAdmin -> pgAdmin. Для разработчиков которые привыкли к визуальному интерфейсу - небольшой порог переключения. pgAdmin 3 вполне рабочий инструмент, но выглядит иначе.

Advisory locks, которые в 8.2 появились, пока не трогали - в этой системе они не нужны. Но идея хороша: блокировать на уровне приложения через базу, не создавая файлы или отдельные таблицы-семафоры.

Что дальше

По итогам этого проекта решили: для новых задач с нетривиальной аналитикой или сложной схемой PostgreSQL идёт первым кандидатом. MySQL остаётся там где он уже стоит и работает, и для простых веб-каталогов - там его возможностей хватает. Но для внутренних систем, где важны транзакции, аудит и нормальный SQL - PostgreSQL убедительнее.

Интеграция и миграция между системами - то, с чем мы регулярно работаем, и каждый раз убеждаемся: основные затраты времени не в переносе данных, а в разнице семантики между платформами. Лучше закладывать на это заранее.