MySQL vs PostgreSQL

MySQL vs 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 нужно хорошо обдумать, стоит ли оно того, чтобы потом не пришлось откатывать все назад.

Об авторе Валера Леонтьев

Программист PHP/MySQL.

Запись опубликована в рубрике MySQL, Web с метками , . Добавьте в закладки постоянную ссылку.

3 комментария на «MySQL vs PostgreSQL»

  1. Очередной holy war поднимаем? :-)
    Столько уже раз обсуждалось, столько можно найти гуглом-яндексом…

    MySQL до сих пор не заслужил называться реляционной СУБД. Вот глобальные причины, почему не стоит его использовать:
    — миф о производительности (просто погуглите на тему сравнения с постгресом, есть картинки);
    — «размазывание» фич по разным типам движка (вкратце: да, у MySQL многое есть, но попробуйте достичь всего функционала — скажем, полнотекста и ACID — в рамках одной таблицы!);
    — наплевательское отношение к базовым принципам реляционной теории (прежде всего, ACID);
    — сырость enterprise-level функциональности (хранимые процедуры, триггеры, механизмы управления транзакциями);
    — несвобода (во-первых, всем заправляет одна компания; если вы сделали патч — должны «подарить» все права ей, в лучшем случае футболку пришлют; действия Оракл всем известны; MySQL AB всё дальше идёт с разделением коммерческой и open source лицензий);
    — GOTCHAS!!! (см. гугл «mysql gotchas»).

    Категорически рекомендую Постгрес. Вам помогут. Приходите в рассылку Постгреса (см. http://postgresql.org), на форум sql.ru. Если хотите коммерческой поддержки — http://postgresmen.ru

  2. admin говорит:

    Попробую подвести резюме темы. Во-первых, подтвердилось: сколько людей — столько мнений :) Тоже самое примерно творится и в гугле. некого единогомнения нет. Но опыт людей набирается на конкретных проектах. А так как для разных проектов могут лучше подойти разные СУБД, неудивительно, что каждый ссылаясь на свой опыт, каждый хвалит разные СУБД.

    В защиту MySQL скажу то, что она проще, чем PostgreSQL, намного популярнее (ее знают все), проще и дешевле найти программистов для нее, и производительность у нее неплохая. При этом для 80% (условно) сайтов ее функционала хватает.

    А вот для этих 20%, для которых не хватает MySQL ну никак, и используется PostgreSQL.

    Ну и ЗЫ. Можно сравнивать Postgre и Oracle. И окажется, что последняя в разы мощнее, надежнее, функциональнее. но это не значит, что все сайты надо делать на ней. Это лишь означает, что для каждой задачи нужно выбирать свои, наиболее подходящие средства.

    ЗЫ.ЗЫ. Буду благодарен за ответы в этом обсуждении: MySQL: MyIsam vs innoDB

  3. ivanovsd говорит:

    Работаю в сети городских порталов. Пришел работать в организацию, в которой использовали до меня MySQL, в которой уже работал веб-программист и были свои устои. Всеми силами бился за переход на PostgreSQL — всё было бесполезно. Те, кто уже привык к MySQL — программисты очень консервативные и упертые. Сам же владею прекрасно несколькими СУБД, так что знаю прекрасно как сравнивать производительность, и однозначно говорю, производительность PostgreSQL выше чем у MySQL, как ее не тюнить и не чудить с ней.
    Так вот, убедительным доводом за переход на PostgreSQL стал момент, когда я реализовал чудесный поиск на этой СУБД, используя разработку полноценного поиска со словарями с версии PostgreSQL 8.3, убедил, что таких возможностей для поиска в MySQL нет. Сейчас в команде у нас три программиста, и все осознаем, что решение о переходе на PostgreSQL было верным. Раньше на одном сервере крутилось два города и уже притормаживало, теперь на одном крутится 12 городов, и всё нормально. Конечно при переходе было решено масса задач по оптимизации запросов, но и сама СУБД дала явный прирост.
    PS. MySQL я обожаю, но для маленьких проектов. Мое сравнение MySQL vs PostgreSQL и почему нужно переходить на PostgreSQL тут: http://ivanovsd.ru/pgsql.pl

Добавить комментарий