Неделю назад я инициировал обсуждение на одном сайтике на тему миграции с MySQL на PostgreSQL. В процессе обсуждения плавно сменили тему на сравнение этих двух популярных СУБД. В результате мне показалось, что почитать это обсуждение будет интересно и полезно многим программистам. В обсуждении участвовал известный программист PHP, автор двух книг по PHP, разработчик сервиса moikrug.ru Дмитрий Котеров.

Вот обсуждение, немного обработанное с вырезанными лишними постами.

from MySQL to PostgreSQL

Валерий Леонтьев, программист PHP/MySQL

Вопрос тем, кто уже прошел такой путь: на сколько сложен переход для программиста, на сколько велики отличия, сколько % запросов обычно надо переписать для сайта на MySQL. Все рассматривается в контексте программирвоания на PHP.

Илья Бутыльский, web-developer

Не сильно сложно. Взвесьте все «за» и «против». Может и не стоит переходить.

Валерий Леонтьев, программист PHP/MySQL

Уже взвесил. Скорость постгри при нагрузке в разы выше, чем у MySQL. При этом, предел отказоустойчивости тоже выше. Т.е. переводить мелкие проекты смысла нет, а вот крупные стоило бы.

Михаил Талисов, Магистрант, Java EE developer

У меня тоже возникала год назад необходимость переводить приложение с MySQL на PostgreSQL. Особых трудностей не возникло.

Но все же некоторые вещи пересмотреть пришлось: в PostgreSQL нет автоинкрементных полей (для автоматического инкремента ПК используется специальный тип SERIAL). И для хранения значения счетчика используются специальные конструкции — sequence. Для получения текущего значения счетчика используется нечто вроде:

«select currval(‘task_id_seq’)»

Вроде больше особых отличий нету, остальное — детали.

Дмитрий Кадыков, web-программист Интернет-гипермаркета 003.ru

Мне приходилось делать обратную операцию: перевести часть кода с Postgre на MySQL. Пришлось заменить TO_CHAR(date,’MM.YYYY’) на DATE_FORMAT(date, ‘%m.%Y\’), и в принципе всё.

Валерий Леонтьев, программист PHP/MySQL

Re: Дмитрий Кадыков

А по какой причине так пришлось сделать, если не секрет?

Антон Полумисков, PHP программист

А на каком MySQL проект находится сейчас? У постгри приемуществ конечно хватает, но может проще будет перейти на MySQL5, если проект конечно не под 5 работает )), он и быстрее чем предшественники и возможностей стало больше. Но и самое главное — возникнет значительно меньше проблем с переходом.

Валерий Леонтьев, программист PHP/MySQL

Re: Антон Полумисков

Понимаешь, тут дело такое: если бы не 5 версия MySQL, то я бы уже давно перешел на ту же PostgreSQL или что-то другое. Версии до 5 — это полупустые СУБД. Там кроме тормозючести еще и половины фишек нет, которые в других СУБД давно есть и являются стандартом.

Евгений Ненаглядов, Вэб-разработчик

А мне из мускула не хватает REPLACE. В постгресе сие реализуется только триггером на INSERT, а я еще не освоил программирование процедур.

Переводил довольно крупный проект. Нудно, но не критично.

Из-за особенностей работы с автоинкреметными полями, реализовал замену функции mysql_insert_id().

Дмитрий Котеров, МойКруг.ру: сооснователь, рук. разработки

Скорость постгри при нагрузке в разы выше, чем у MySQL

Мой опыт показывает, что это совершенно неверное заявление. Все в точности да наоборот. Вы какой тип таблиц используете? Если MyISAM, то все понятно — они под нагрузкой плохо живут, если операции записи производятся сравнительно часто. Попробуйте InnoDB, они работают примерно так же, как таблицы в PostgreSQL (версионник с блокировкой строк). И попробуйте еще настройки MySQL потюнить — от них многое зависит.

Для крупных высоконагруженных проектов важное значение имеет репликация. В MySQL она встроенная и, главное, работает отлично — практически нет запаздывания при обновлении реплики. Для PostgreSQL есть Slony, однако у него: (а) раз в 10 бОльший «барьер на вход» (т.е. если с репликацией MySQL Вы в продакшене освоитесь, скажем, за неделю, то в Slony — за 10 недель), и (б) конструктивно значительный лаг при обновлении реплик, а значит, Вам придется строить приложение с учетом этого лага, что не так-то просто (встроенных средств в Slony для отслеживания этого лага [пока] нет).

Кроме того, если Вы рассчитываете с переходом на PostgreSQL обеспечить лучшую сохранность данных, то для обеспечения высокой производительности совместно с высокой надежностью нужно обязательно использовать аппаратный дисковый контроллер со встроенной кэш-памятью записи значительного размера и батарейкой (а такие контроллеры недешевы). Без аппаратного решения Вы «упретесь» в операцию fsync, которая, собственно, и обеспечивает сохранность данных, и которая несовместима с интенсивным обновлением данных без аппаратного решения.

Так что, если у Вас проект прекрасно работает на MySQL, возможностей MySQL ему хватает, то я бы категорически не рекомендовал переходить на PostgreSQL. PostgreSQL нужен для действительно сложных проектов, когда возможностей MySQL (даже 5-й версии) не хватает, когда приходится применять нереляционный подход, — например, для МойКруг. По крайней мере, в стратегическом (долгосрочном) плане повышения быстродействия при переходе с MySQL на PostgreSQL Вы точно не добьетесь.

Валерий Леонтьев, программист PHP/MySQL

Re: Дмитрий Котеров

Спасибо за столь подробный ответ. Мое «заявление» на самом деле основывалось не на личном опыте (как понятно из темы — я с постгри не работал), а не статьях других программистов. Последняя статья на эту тему, которую я прочитал была опубликована в журнале «Системный администратор» кажется за август (я не обратил внимания на номер). И как раз там у них по результатам опытов разница получилась в разы.

В MySQL медленнее работают сложные запросы: с джойнами и подзапросами. На этой почве у меня даже развилась некая фобия таких запросов. А если эх будет много в скрипте, да плюс нагрузка пиковая большая, то MySQL может и в ступор впасть. Один раз я такой ступор уже наблюдал. Правда там причина была в том, что проект был написан неграмотно (не мной) и не стояли индексы. Когда я открыл show processes, я там наблюдал кучу сложных висящих запросов. И загрузка ЦП деманом мускуля 99%. Так как времени на оптимизацию не было, да и двиг этот скоро уйдет в корзину, я не менял запросы, но просто сделал для них «правильные индексы», проблема частично решилась. Таких ступоров больше не было. И вот сложилось по статьям у меня впечатление, что с постгри такого не будет, а если и будет, то при много большей нагрузке. Тип таблиц в данном скрипте действительно MyISAM.

Александр Лещинский, Мизантропичный админ

Re: Дмитрий Котеров

Мой опыт показывает, что это совершенно неверное заявление.

нечасто я соглашаюсь с Дмитрием, но тут поддержу из всех стволов. Безмозглое неграмотное мудачье, не знающее матчасти, делает «абы как» своими руками, растущими из жопы, а потом удивляется «а шо, нам обещали что будет все шоколадно, а все совсем не так»? Любой инструмент, даже самый хороший, не отменяет необходимости думать… головой!!! а не тем, чем привыкли так называемые «работники» — очком.

Re: Валерий Леонтьев

Про журнальчик этот вобще цензурных слов нет… большего сборища ламерья я еще не встречал… Писать и проектировать надо аккуратно, чтобы не накрывал БП

Дмитрий Кадыков, web-программист Интернет-гипермаркета 003.ru

Re: Валерий Леонтьев

Да просто новому начальнику не понравилось, что одна часть проекта работает по Postgre, а всё остальное — под MySQL. Кстати, сразу же хотелось бы задать вопрос всем: может ли считаться оправданным совмещение двух СУБД в рамках одного проекта и в каких случаях стоит так делать?

Валерий Леонтьев, программист PHP/MySQL

Re: Дмитрий Котеров

Кстати, как я понял, Дмитрий Котеров повсеместно предпочитает использовать InnoDB. А как с fulltext-поиском? Используете ли Вы его? Или ищите по каким-то другим алгоритмам?

Валерий Леонтьев, программист PHP/MySQL

Re: Дмитрий Кадыков

Логически поразмышляв, пришел к выводу, что совмещение СУБД оправдано быть не может. Обычно, из 2х СУБД одна лучше. Тогда зачем делить, логичнее все делать на той, которая лучше в данной ситуации. Возможно, деление оправдано в крайне редких случаях, когда разные элементы проекта требуют функционал, доступный только в разных СУБД. Не знаю бывает ли такое вообще, но если и бывает, то крайне редко (может 1 сайт из 1000).

Александр Лещинский, Мизантропичный админ

Re: Валерий Леонтьев

Логически поразмышляв, пришел к выводу, что совмещение СУБД оправдано быть не может.

А практики говорят, что резоны могут быть…

Валерий Леонтьев, программист PHP/MySQL

Ну так у любого правила есть исключения. Я и пример даже привел.

Евгений Неверов, Я — человек!

Если же вернуться к сути вопроса, то структуру из одной БД нужно будет перенести заранее ручками (проще это), а вот данные скорее всего перенесутся без проблем.

Был у меня один проект, который я переносил с MySQL на PostgreSQL, так вот там по ходу дела пришлось сменить структуру таблиц. Чтобы импортировать данные я написал около 20 регулярных выражений, которые преобразовывали INSERT-запросы к нужному формату. Но в результате всё получилось очень хорошо.

Ответить

Виктор Куряшкин, Semantic Web specialist, Sun Microsystems

На постгрес с мускула имеет смысл переходить, если вы будете использовать возможности постгреса.

Просто «переписать запросы», сделать автоинкремент сиквенсом и все — это не аргумент.

Если вы не планируете усложнять логику на уровне субд — то точно переходить не стоит. Просто провозитесь и получите прибавку к производительности 5%, а то и потеряете в некоторых случаях.

Дмитрий Котеров, МойКруг.ру: сооснователь, рук. разработки

В MySQL медленнее работают сложные запросы: с джойнами и подзапросами

Не совсем так. Скорость выполнения запроса зависит во многом от того, «попадает» ли он, грубо говоря, в индексы. Если запрос «не попал» в индексы в MySQL и «не попал» в PostgreSQL, неверно говорить, что «в PostgreSQL этот запрос будет работать быстрее» — он в обоих случаях работает недопустимо долго.

Другое дело, что в PostgreSQL мощный планировщик и оптимизатор запросов (правда, он при этом и очень сложен, а также часто «ошибается», т.е. на его приструнение и изучение нужно тратить много времени). Кроме того, в PostgreSQL есть всякие там Hash Join и т.д. Так что, действительно, у написанного «от балды» сложного многотабличного запроса в PostgreSQL вероятность выполниться быстро выше, чем у того же запроса в MySQL. Но речь-то сейчас не о вероятностях, а о том, что запросы нужно строить грамотно, смотреть план их выполнения и «подгонять» друг под друга запрос, его план и индексы. А если все подогнанно, то и там, и там выполняется быстро, и на передний план выходит уже скорость апдейтов и простота репликации.

Кстати, как я понял, Дмитрий Котеров повсеместно предпочитает использовать InnoDB. А как с fulltext-поиском? Используете ли Вы его? Или ищите по каким-то другим алгоритмам?

Собственно, на MойКруге PostgreSQL во многом как раз из-за того, что в нем есть отличный полнотекстовый поиск, который и не снился MySQL. (А в 8.3 он будет еще лучше.) В InnoDB, к сожалению, полнотекстовых индексов и правда не бывает. Но я слышал, что есть масса внешних решений для индексации таблиц — в этом случае индекс строится с задержкой и по нему не так удобно искать, конечно, но — это лучше, чем ничего. Лично мне с ними работать не приходилось.

Опять же, если строить систему с нуля, и если в системе планируется много апдейтов данных, то лучше идти в сторону как раз раздельного хранения полнотекстового индекса и данных. Ну или хотя бы считать полнотекстовый индекс асинхронно (с задержкой) — как сделано здесь.

Насчет систем с двумя различными СУБД — почему бы и нет? Мы, например, используем и MySQL тоже (как раз там, где мало селектов, но много инсертов и апдейтов). Да, на администрирование дополнительной базы должно тратиться больше времени, однако тут есть два «но»:

1. Админ, работая с двумя базами сразу, здорово повышает свою квалификацию. Кроме того, появляется возможность сравнивать те или иные вещи.

2. MySQL очень неприхотлива и нетребовательна в администрировании (по моим приблизительным оценкам — там раз в 10 меньше подводных камней, чем в PostgreSQL), поэтому затраты на администрирование и MySQL тоже крайне малы.

Поучаствовать в обсуждении можно здесь: http://moikrug.ru/topics/216367527/

Вывод: прежде чем переходить на PostgreSQL нужно хорошо обдумать, стоит ли оно того, чтобы потом не пришлось откатывать все назад.