Сравнение субд mysql postgresql mssql. Когда не следует использовать PostgreSql

Серия контента:

1. История развития MySQL и PostgreSQL

История MySQL начинается в 1979 г., у ее истоков стояла небольшая компания во главе с Monty Widenius. В 1996 г. появился первый релиз 3.11 под солярис с публичной лицензией. Затем MySQL была портирована под другие операционные системы, появилась специальная коммерческая лицензия. В 2000 г., после добавления интерфейса, аналогичного Berkeley DB, база стала транзакционной. Примерно тогда же была добавлена репликация. В 2001 г. в версии 4.0 был добавлен движок InnoDB к уже имеющемуся MyISAM, в результате чего появилось кеширование и возросла производительность. В 2004 г. вышла версия 4.1, в которой появились подзапросы, парциальная индексация для MyISAM, юникод. В версии 5.0 в 2005 г. появились хранимые процедуры, курсоры, триггеры, представления (views). В MySQL развиваются коммерческие тенденции: в 2009 г. MySQL стала торговой маркой компании Oracle.

История постгрес началась в 1977 г. с базы данных Ingress.

В 1986 г. в университете Беркли, Калифорния, она была переименована в PostgreSQL.

В 1995 г. постгрес стала открытой базой данных. Появился интерактивный psql.

В 1996 г. Postgres95 была переименована в PostgreSQL версии 6.0.

У постгреса несколько сотен разработчиков по всему миру.

2. Архитектура MySQL и PostgreSQL

PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.

Для каждого клиента на сервере создается новый процесс (не поток!). Для работы с такими клиентскими процессами сервер использует семафоры.

Клиентский запрос проходит следующие стадии.

  1. Коннект.
  2. Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
  3. Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
  4. Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
  5. Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.

Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.

MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.

Реализован стандарт SQL92.

С модульной точки зрения код MySQL можно разделить на следующие модули.

  1. Инициализация сервера.
  2. Менеджер коннектов.
  3. Менеджер потоков.
  4. Обработчик команд.
  5. Аутентификация.
  6. Парсер.
  7. Оптимизатор.
  8. Табличный менеджер.
  9. Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
  10. Логирование.
  11. Репликация.
  12. Сетевое API.
  13. API ядра.

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

Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.

Клиентские запросы в зависимости от их типа на верхнем уровне обрабатываются четвертым модулем (dispatcher). Запросы будут залогированы 11-м модулем. Команда передается парсеру, проверяется кеш. Далее запрос может попасть в оптимизатор, табличный модуль, модуль репликации, и т.д. В результате данные возвращаются клиенту через менеджер потоков.

Наиболее важный код находится в файле sql/mysqld.cc. В нем находятся базовые функции, которые не меняются со времен версии 3.22: init_common_variables() init_thread_environment() init_server_components() grant_init() // sql/sql_acl.cc init_slave() // sql/slave.cc get_options() handle_connections_sockets() create_new_thread() handle_one_connection() check_connection() acl_check_host() // sql/sql_acl.cc create_random_string() // sql/password.cc check_user() // sql/sql_parse.cc mysql_parse() // sql/sql_parse.cc dispatch_command() Query_cache::store_query() // sql/sql_cache.cc JOIN::optimize() // sql/sql_select.cc open_table() // sql/sql_base.cc mysql_update() // sql/sql_update.cc mysql_check_table() // sql/sql_table.cc

В хидере sql/sql_class.h определяются базовые классы: Query_arena, Statement, Security_context, Open_tables_state classes, THD. Обьект класса THD представляет собой дескриптор потока и является аргументом большого количества функций.

3. Сравнение MySQL и PostgreSQL: сходство и различия

ACID-стандарт

Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.

Производительность (performance)

Базы данных часто оптимизируются в зависимости от окружения, в котором они работают. Обе базы имеют различные технологии для улучшения производительности. Исторически так сложилось, что MySQL начинала разрабатываться с прицелом на скорость, а PostgreSQL с самого начала разрабатывалась как база с большим числом настроек и соответствием стандарту. PostgreSQL имеет ряд настроек, которые повышают скорость доступа:

  • парциальные индексы;
  • компрессия данных;
  • выделение памяти;
  • улучшенный кеш.

MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.

Компрессия

PostgreSQL лучше сжимает и разжимает данные, позволяя сохранить больше данных на дисковом пространстве. При этом компрессионные данные читаются быстрее с диска.

MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.

На мульти-процессорности PostgreSQL имеет преимущество над MySQL. Даже сами разработчики MySQL признают, что их движок в этом плане не так хорош.

Типы данных

MySQL: для хранения бинарных данных использует типы TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, которые отличаются размером (до 4 ГБ).

Character: четыре типа – TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: поддерживает механизм пользовательских данных с помощью команды CREATE TYPE, тип BOOLEAN, геометрические типы.

Character: TEXT (ограничение – max row size).

Для хранения бинарных данных есть тип BLOB, который хранится в файловой системе. Столбцы таблицы могут быть определены как многомерный массив переменной длины. Обьектно-реляционное расширение: структура таблицы может быть унаследована от другой таблицы.

Хранимые процедуры

И PostgreSQL , и MySQL поддерживают хранимые процедуры. PostgreSQL придерживается стандарта Oracle PL/SQL, MySQL – IBM DB2. MySQL поддерживает extend SQL для написания функций на языке C/C++ с версии 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C для написания хранимых процедур.

Ключи

И PostgreSQL , и MySQL поддерживают уникальность Primary Key и Foreign Key. MySQL не поддерживает check constraint плюс вторичные ключи реализованы частично. PostgreSQL: полная реализация плюс поддержка ON DELETE CASCADE и ON UPDATE CASCADE.

Триггеры

MySQL: рудиментарная поддержка. PostgreSQL: декларативные триггеры: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; процедурные триггеры: CONSTRAINT TRIGGER. События: BEFORE или AFTER на INSERT, DELETE , UPDATE.

Автоинкремент

MySQL: в таблице может быть только один такой столбец, который должен быть проиндексирован. PostgreSQL: SERIAL data type.

Репликации

Поддерживаются и в MySQL, и в PostgreSQL. PostgreSQL имеет модульную архитектуру, и репликация входит в отдельные модули:

  • Slony-I – основной механизм репликации в постгресе, производительность падает в квадратичной зависимости от числа серверов;

Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.

В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:

  • SBR – statement based replication;
  • RBR – row based replication.

Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 5.5, в MySQL поддерживается так называемая полусинхронная репликация, при которой основной сервер (master) делает сброс данных на другой сервер (slave) при каждом коммите. Движок NDB делает полную синхронную двухфазную репликацию.

Транзакции

MySQL: только для для InnoDB. Поддержка SAVEPOINT, ROLLBACK TO SAVEPOINT. Уровни блокировки: table level (MyISAM). PostgreSQL: поддерживается плюс read committed и уровни изоляции. Поддержка ROLLBACK, ROLLBACK TO SAVEPOINT. Уровни блокировки: row level, table level.

Уровни привилегий

PostgreSQL: для пользователя или группы пользователей могут быть назначены привилегии.

Экспорт-импорт данных

MySQL: набор утилит для экспорта: mysqldump, mysqlhotcopy, mysqlsnapshot. Импорт из текстовых файлов, html, dbf. PostgreSQL: экспорт – утилита pg_dump. Импорт между базами данных и файловой системой.

Вложенные запросы

Есть и в MySQL, и в PostgreSQL, но в MySQL могут работать непроизводительно.

Индексация

Хэширование индексов: в MySQL– частичное, в PostgreSQL – полное. Полнотекстовый поиск: в MySQL– частичный, в PostgreSQL – полный. Парциальные индексы: в MySQL не поддерживаются, в PostgreSQL поддерживаются. Многостолбцовые индексы: в MySQL ограничение 16 столбцов, в PostgreSQL – 32. Expression-индексы: в MySQL– эмуляция, в PostgreSQL – полное. Неблокирующий create index: в MySQL – частичное, в PostgreSQL – полное.

Партиционирование (Partitioning)

MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.

Автоматическое восстановление после сбоев

MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:

  • MyISAM – используется для хранения системных таблиц;
  • InnoDB – максимальное соответствие ACID, хранит данные с первичными ключами, кэширует инсерты, поддерживает компрессию, начиная с версии 5.1 – см. атрибут ROW_FORMAT=COMPRESSED;
  • NDB Cluster – движок, ориентированный на работу с памятью, кластерная архитектура, использующая синхронную репликацию;
  • ARCHIVE – поддерживает компрессию, не использует индексы;
  • а также: MERGE, MEMORY (HEAP), CSV.

InnoDB разрабатывается компанией InnoBase, являющейся дочерней компанией Oracle. В 6-й версии должны появиться два движка – Maria и Falcon. Falcon – движок, основанный на ACID-транзакциях.

Лицензирование

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License) или Commercial. MySQL – это open-source продукт. Postgres – это open-source проект.

Заключение

Подводя итоги, можно сказать следующее: MySQL и PostgreSQL – две наиболее популярные open-source базы данных в мире. Каждая база имеет свои особенности и отличия. Если вам нужно быстрое хранилище для простых запросов с минимальной настройкой, я бы порекомендовал MySQL. Если вам нужно надежное хранилище для большого объема данных с возможностью расширения, репликации, полностью соответствующее современным стандартам языка SQL, я бы предложил использовать PostgreSQL.

Мы обсудим вопросы настройки MySQL и PostgreSQL.

Ресурсы для скачивания

static.content.url=http://www.сайт/developerworks/js/artrating/

Zone=Open source, Linux

ArticleID=779830

ArticleTitle=MySQL & PostgreSQL: Часть 1. Сравнительный анализ

Отличный вопрос для холивара. Но не будем. Опубликована автор которой рассматривает обе СУБД с позиции бережного отношения к данным и приходит к выводу, что для серьезных проектов подходит PostgreSQL, а MySQL - вообще не вариант.

Пример превосходства PosgreSQL над MySQL

Например, создадим таблицу и для одного из полей зададим тип numeric(4, 2) - четыре разряда под хранение всего числа и два разряда после запятой. А потом попытаемся вставить число, которое не соответствует описанию. И что вы думаете? Нет проблем!

В ручном режиме мы увидим (возможно) предупреждение (1 warning) попытаемся догадаться, о чем оно и восстановить прежнее значение. Но предупреждение легко пропустить. Если вы пишете приложение, имеющее дело с хранением финансовых данных, такие ошибки будут стоить очень дорого.

PostgreSQL бережет ваши данные и ведет себя более серьезно:

INSERT INTO DATA VALUES (1 , 1234.5678 ) ; ERROR: NUMERIC FIELD overflow DETAIL: A FIELD WITH PRECISION 4 , scale 2 must round TO an absolute VALUE less than 10 ^2 .

Нам просто не дадут сделать ошибку. Более того, в сообщении будет содержаться детализация: какое именно ограничение мы нарушили и какой максимальный размер числа задан. Так-то вот.

А теперь немного мистики. Помните, мы описали, что поле id для MySQL не должно быть NULL? Пробуем:

Удивительно, но несмотря на явный запрет (NOT NULL), СУБД MySQL разрешила-таки обновить поле, причем установила его значение в 0, хотя о нуле мы вообще ничего не говорили. 0 и NULL - совершенно разные сущности. 0 - это число. NULL - это особый индикатор, говорящий нам о том, что мы не знаем, какое число содержится в этом поле. И совсем необязательно это 0. Таким образом, СУБД опять приписала от себя случайное значение в нашу базу. Если в вашем приложении будет такая же ошибка, то MySQL поможет этой ошибке долго оставаться необнаруженной. А что же PostgreSQL? Тут всё чётко.

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

Прежде всего хотелось бы отметить, что PostgreSQL и MySQL являются широко используемыми программными продуктами, которые разрабатывались с разными целями (хотя создатели обоих и стремятся довести их до полной совместимости со стандартом ANSI SQL). Это значит, что для решения одних задач больше подходит MySQL, для других же - PostgreSQL. Выбирая СУБД, проверьте, соответствуют ли ее возможности требованиям, предъявляемым решаемой задачей. Если требуется максимальная скорость работы, лучше всего, вероятно, будет остановить свой выбор на MySQL Server. Если же вам необходимы дополнительные возможности, имеющиеся только у PostgreSQL, этой СУБД и стоит пользоваться.

Значительное отличие MySQL от PostgreSQL заключается в том, что практически весь содержащийся в MySQL код создан разработчиками, работающими в MySQL AB и постоянно занятыми совершенствованием кода сервера. Исключением из этого правила являются системы транзакций и библиотека регулярных выражений regexp.

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

Сравнение возможностей MySQL и PostgreSQL

MySQL обладает следующими преимуществами перед PostgreSQL:

· MySQL обычно намного превосходит PostgreSQL по скорости работы. Кроме того, в MySQL 4.0 реализован кэш запросов. Он позволяет во много раз увеличить скорость обработки запросов для сайтов, на которых преобладают неоднократно повторяющиеся запросы на чтение.

· По количеству пользователей MySQL также намного превосходит PostgreSQL. Поэтому код тестируется значительно более придирчиво и опытным путем доказана большая его надежность, нежели у PostgreSQL. MySQL чаще, чем PostgreSQL, используется на производстве, в основном потому, что компания MySQL AB (ранее - TCX DataKonsult AB) предоставляет высококачественную коммерческую техническую поддержку MySQL с момента появления этой системы на рынке, а у PostgreSQL до самого последнего времени никакой поддержки не было.

· MySQL работает в среде Windows лучше, чем PostgreSQL. MySQL Server запускается как настоящее (родное) Windows-приложение (в NT/2000/XP - сервис), в то время как PostgreSQL запускается в среде эмуляции, Cygwin. Доводилось слышать о недостаточной стабильности работы PostgreSQL в среде Windows, но самостоятельно эти сведения до сих пор проверить не мог.

· MySQL оснащен большим количеством API для других языков и поддерживается большим количеством существующих программ, нежели PostgreSQL.

· MySQL работает на высоконадежных промышленных системах 24/7 (включенных 24 часа в сутки 7 дней в неделю). В большинстве случаев никаких "чисток" в MySQL производить не требуется. PostgreSQL же пока что не может работать в таких системах, так как иногда приходится запускать VACUUM для освобождения занятого последствиями работы команд UPDATE и DELETE пространства и проводить статистический анализ, необходимый для достижения максимальной производительности PostgreSQL. Запускать VACUUM необходимо и после каждого добавления к таблице нескольких столбцов. На напряженно работающих системах VACUUM нужно запускать более часто, в худших случаях - по несколько раз в день. А ведь во время работы VACUUM (а ее работа может продолжаться часы, если база данных достаточно велика) база практически "мертва". Впрочем, в PostgreSQL версии 7.2 выполнение основных функций этой программы больше не приводит к блокировке базы, и пользователи могут продолжать нормально работать с ней. Новая команда VACUUM FULL берется за дело более серьезно: она, как и в старых версиях, блокирует таблицу и сжимает копию таблицы на диске.

· Книг о MySQL вышло значительно больше, нежели о PostgreSQL. Книги о MySQL выпустили издательства O"Reilly, SAMS, Que и New Riders. Все возможности MySQL детально описаны в документации, так как это является обязательным условием включения новых возможностей в код.

· MySQL обладает значительно более мощной реализацией ALTER TABLE.

· В MySQL предусмотрена возможность создания таблиц без транзакций, что необходимо приложениям, требующим максимально возможной скорости работы.

· MySQL может работать с двумя поддерживающими транзакции обработчиками таблиц, а именно - InnoDB и BerkeleyDB. Так как все системы поддержки транзакций в разных условиях работают по-разному, это дает разработчику возможность найти наилучшее решение для условий, в которых будет работать его система. See section 7 Типы таблиц MySQL.

· Команда слияния таблиц MERGE предоставляет в ваше распоряжение уникальную возможность создать представление нескольких идентичных таблиц и работать с ними как с одной. Это особенно удобно для работы с журналами, разбитыми, например, по месяцам.

· Возможность сжатия доступных только для чтения таблиц, не отменяющая прямого доступа к их записям, повышает производительность системы, снижая количество операций считывания с диска. Это особенно полезно при архивировании.

· В MySQL реализован полнотекстовый поиск.

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

· Система MySQL с самого начала разрабатывалась в расчете на многопоточность, а PostgreSQL использует процессы. Переключение контекстов и доступ к общим данным несколькими потоками осуществляется значительно быстрее, нежели отдельными процессами. Таким образом MySQL Server в многопользовательских приложениях получает неплохое преимущество в производительности, а кроме того, таким образом MySQL Server удается значительно эффективней пользоваться преимуществами, предоставляемыми симметричными мультипроцессорными системами (SMP).

· В MySQL реализована значительно более мощная система привилегий, нежели в PostgreSQL. В то время как PostgreSQL обеспечивает лишь привилегии INSERT, SELECT и UPDATE/DELETE над базой или таблицей, MySQL предоставляет возможность определения полного набора разнообразных привилегий на уровне базы, таблицы и столбца. Кроме того, MySQL позволяет задавать привилегии для комбинаций хост/пользователь.

· В MySQL используется протокол связи между клиентом и сервером со сжатием данных, что увеличивает производительность системы в условиях низкоскоростных каналов связи.

· Все типы таблиц в MySQL (кроме InnoDB) реализованы в виде файлов (по одной таблице в файле), что значительно облегчает создание резервных копий, перенос, удаление и даже создание символьных ссылок между базами данных и таблицами, даже если сервер находится в нерабочем состоянии.

· Обновление (апгрейд) MySQL проходит совершенно "безболезненно". При модернизации MySQL нет нужды в копировании/восстановлении данных, что приходится делать при установке большинства обновлений PostgreSQL.

Ниже перечислены преимущества PostgreSQL по сравнению с MySQL на сегодняшний день.

Другие причины, по которым можно предпочесть PostgreSQL:

· В некоторых случаях PostgreSQL оказывается ближе к ANSI SQL.

· Работу PostgreSQL можно ускорить, выполняя код в виде хранимых процедур.

· При хранении географических данных R-деревья дают PostgreSQL преимущество перед MySQL (примечание: в MySQL версии 4.1 для таблиц MyISAM реализована поддержка R-деревьев).

· Команда разработчиков PostgreSQL, пишущих код для сервера, больше.

Недостатки PostgreSQL по сравнению с MySQL:

· VACUUM затрудняет использование PostgreSQL в постоянно работающих системах.

· Наличие только транзакционных таблиц.

· Значительно более медленная работа команд INSERT, DELETE и UPDATE.

Заключение

Единственные существующие на сегодня тесты, позволяющие сравнить MySQL Server и PostgreSQL, которые любой может загрузить и запустить, - это тесты из комплекта MySQL. И поэтому свой выбор я останавливаю на MySQL

  • Блог компании Mail.ru Group
  • В преддверии своего доклада на конференции PGCONF.RUSSIA 2015 я поделюсь некоторыми наблюдениями о важных различиях между СУБД MySQL и PostgreSQL. Этот материал будет полезен всем тем, кого уже не устраивают возможности и особенности MySQL, а также тем, кто делает первые шаги в Postgres. Конечно, не стоит рассматривать этот пост как исчерпывающий список различий, но для принятия решения в пользу той или иной СУБД его будет вполне достаточно.

    Репликация

    Тема моего доклада «Асинхронная репликация без цензуры, или почему PostgreSQL завоюет мир», и репликация одна из самых больных тем для нагруженных проектов использующих MySQL. Проблем много - корректность работы, стабильность работы, производительность - и на первый взгляд они выглядят несвязанными. Если же посмотреть в историческом контексте, то мы получаем интересный вывод: MySQL репликация имеет столько проблем потому, что она не была продумана, а точкой невозврата была поддержка storage engine (подключаемых движков) без ответов на вопросы «как быть с журналом?» и «как различным storage engine участвовать в репликации». В 2004 году в PostgreSQL рассылке пользователь пытался «найти» storage engine в исходном коде PostgreSQL и сильно удивился, что их нет. В процессе дискуссии кто-то предложил добавить эту возможность PostgreSQL, и один из разработчиков ответил «Ребята, если мы так сделаем, у нас будут проблемы с репликацией и с транзакциями между движками».
    The problem is that many storage management systems… often do their own WAL and PITR. Some do their own buffer management, locking and replication/load management too. So, as you say, its hard say where an interface should be
    abstracted.
    ссылка на это письмо в postgresql mailing list

    Прошло более 10 лет, и что мы видим? В MySQL есть раздражающие проблемы с транзакциями между таблицами разных storage engine и у MySQL проблемы с репликацией. За эти десять лет у PostgreSQL появились подключаемые типы данных и индексы, а также есть репликация - т. е. преимущество MySQL было нивелировано, в то время как архитектурные проблемы MySQL остались и мешают жить. В MySQL 5.7 попытались решить проблему производительности репликации, распараллелив её. Поскольку проект на работе очень чувствителен к производительности репликации в силу своего масштаба, я попытался протестировать, стало ли лучше. Я нашёл, что параллельная репликация в 5.7 работает медленней однопоточной в 5.5, и лишь в отдельных случаях - примерно также. Если вы сейчас используете MySQL 5.5 и хотите перейти на более свежую версию, то учтите, что для высоконагруженных проектов миграция невозможна, поскольку репликация просто перестанет успевать выполняться.

    После доклада на highload, в Oracle приняли к сведению разработанный мной тест и сообщили, что попытаются исправить проблему; недавно мне даже написали, что смогли увидеть параллелизм на своих тестах, и выслали настройки. Если не ошибаюсь, при 16 потоках появилось незначительное ускорение по сравнению с однопоточной версией. К сожалению, до сих пор не повторил свои тесты на предоставленных настройках - в частности потому, что с такими результатами наши проблемы всё равно остаются актуальными.

    Точные причины такой регрессии производительности неизвестны. Было несколько предположений - например, Кристиан Нельсен, один из разработчиков MariaDB, у себя в блоге писал о том, что могут быть проблемы с перфоманс-схемой, с синхронизацией тредов. Из-за этого наблюдается регрессия в 40%, которая видна на обычных тестах. Oracle-разработчики это опровергают, и меня даже убедили, что её нет, видимо, я вижу какую-то другую проблему (и сколько же их всего?).

    В MySQL репликации проблемы со storage engine усугубляются выбранным уровнем репликации - они логические, в то время как в PostgreSQL - физические. В принципе, у логической репликации есть свои преимущества, она позволяет сделать больше всяких интересных штук, об этом в докладе я тоже упомяну. Но PostgreSQL даже в рамках своей физической репликации уже сводит все эти преимущества на нет. Иными словами, почти все, что есть в MySQL, уже можно сделать и в PostgreSQL (либо будет можно в ближайшем будущем).

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

    В MySQL ситуация другая. У нас есть отдельный журнал InnoDB и журнал репликации, и нужно коммитить и туда, и туда. А это two-phase commit между журналами, который по определению работает медленно. То есть мы не можем просто взять и сказать, что мы повторяем транзакцию из InnoDB-журнала - приходится разбираться, что за запрос, запускать его заново. Если даже это логическая репликация, на уровне строчек, то эти строчки нужно искать в индексе. И мало того, что приходится сделать большое количество работы, чтобы выполнить запрос - он при этом снова будет писаться в свой InnoDB-журнал уже на реплике, что для производительности явно нехорошо.

    В PostgreSQL в этом смысле архитектура на порядок продуманней и лучше реализована. Недавно в нём анонсировали возможность под названием Logical Decoding - которая позволяет сделать всякие интересные штуки, которые очень тяжело сделать в рамках физического журнала. В PostgreSQL это надстройка сверху, logical decoding позволяет работать с физическим журналом так, будто он логический. Именно эта функциональность скоро уберёт все преимущества MySQL репликации, кроме, возможно, размера журнала - statement-based репликация MySQL будет выигрывать - но у statement-based репликации MySQL есть совершенно дикие проблемы в самых неожиданных местах, и не стоит считать её хорошим решением (про это всё я тоже буду говорить в докладе).

    Кроме того, для PostgreSQL есть триггерная репликация - это Tungsten, который позволяет делать то же самое. Триггерная репликация работает следующим образом: ставятся триггеры, они заполняют таблицы или пишут файлы, результат отправляется на реплику и там применяется. Именно через Tungsten, насколько я знаю, делают миграцию из MySQL в PostgreSQL и наоборот. В MySQL же логическая репликация работает прямо на уровне движка, и другой ее сделать сейчас уже нельзя.

    Документация

    У PostgreSQL документация гораздо лучше. В MySQL она формально вроде даже есть, но смысл отдельных опций понять бывает тяжело. Вроде написано, что они делают, но чтобы понять, как их правильно настраивать, нужно использовать неофициальную документацию, искать статьи на эти тему. Часто нужно понимать архитектуру MySQL, без этого понимания настройки выглядят какой-то магией.

    Например, так «выстрелила» компания Percona: они вели MySQL Performance Blog, и в этом блоге было множество статей, в которых рассматривались отдельные моменты эксплуатации MySQL. Это принесло бешеную популярность, привело клиентов в консалтинг, позволило привлечь ресурсы для запуска разработки собственного форка Percona-Server. Существование и востребованность MySQL Performance Blog доказывают, что официальной документации просто недостаточно.

    У PostgreSQL фактически все ответы есть в документации. С другой стороны, я слышал много критики при сравнении документации PostgreSQL со «взрослой» Oracle. Но это, на самом деле, очень важный показатель. MySQL с взрослым Oracle никто не пытается сравнивать вообще - это было бы смешно и нелепо - а PostgreSQL уже начинают сравнивать вполне серьезно, PostgreSQL-коммьюнити эту критику слышит и работает над улучшением продукта. Это говорит о том, что он по своим возможностям и производительности начинает конкурировать со столь мощной системой как Oracle, на которой работают мобильные операторы и банки, в то время как MySQL остаётся сидеть в нише веб-сайтов. И проекты-гиганты, доросшие до большого количества данных и пользователей, хлебают горе с MySQL большой ложкой, постоянно упираясь в его ограничения и архитектурные проблемы, которые невозможно исправить, затратив разумное количество сил и времени.

    Примером таких крупных проектов на PostgreSQL является 1C: PostgreSQL идёт как опция вместо Microsoft SQL, а Microsoft SQL действительно фантастическая СУБД, одна из самых мощных. PostgreSQL может заместить MS SQL, а попытка заместить его MySQL… давайте опустим завесу жалости над этой сценой, как писал Марк Твен.

    Стандарты

    PostgreSQL соответствует стандартам SQL-92, SQL-98, SQL-2003 (реализованы все его разумные части) и уже работает над SQL-2011. Это очень круто. Для сравнения, MySQL не поддерживает даже SQL-92. Кто-то скажет, что в MySQL такая цель просто не ставилась разработчиками. Но нужно понимать, что разница между версиями стандарта заключается не в мелких изменениях - это новые функциональные возможности. То есть в тот момент, когда MySQL говорил: «Мы не будем следовать стандарту», они не просто вносили какие-то мелкие различия, из-за которых MySQL тяжело поддержать, они еще закрывали дорогу к реализации многих нужных и важных возможностей. Там до сих пор нет нормально оптимизатора. То, что там называется оптимизацией, в PostgreSQL называется «парсер» плюс нормализации. В MySQL это лишь план выполнения запросов, без разделения. И MySQL к поддержке стандартов придут еще очень нескоро, поскольку на них давит груз обратной совместимости. Да, они хотят, но лет через пять, может, что-нибудь у них появится. В PostgreSQL есть уже все и сейчас.

    Производительность и сложность администрирования

    С точки зрения простоты администрирования сравнение не в пользу PostgreSQL. MySQL администрировать гораздо проще. И не потому, что в этом смысле он лучше продуман, а просто гораздо меньше умеет делать. Соответственно, и настраивать его проще.

    У MySQL есть проблема со сложными запросами. Например, MySQL не умеет спускать группировку в отдельные части union all. Разница между двумя запросами - на нашем примере группировка по отдельным таблицам и union all сверху работала в 15 раз быстрее, чем union all и потом группировка, хотя оптимизатор должен оба запроса приводить в одинаковый, эффективный план выполнения запроса. Нам придется делать генерацию таких запросов руками - т. е. тратить время разработчиков на то, что должна делать база.

    «Простота» MySQL вытекает, как можно увидеть выше, из крайне бедных возможностей - MySQL работает просто хуже и требует больше времени и усилий во время разработки. В противоположность этому, у PostrgreSQL есть гистограммы и нормальный оптимизатор, и он выполнит такие запросы эффективно. Но если есть гистограммы, значит, есть их настройки - как минимум bucket size. Про настройки нужно знать и в отдельных случаях их менять - следовательно, нужно понимать, что это за настройка, за что она отвечает, уметь распознавать такие ситуации, увидеть выбрать оптимальные параметры.

    Изредка случается, что умелость PostrgreSQL может помешать, а не помочь. В 95% случаев все хорошо работает - лучше, чем MySQL, - а какой-то один дурацкий запрос работает гораздо медленнее. Или всё работает хорошо, а потом внезапно (с точки зрения пользователя) по мере роста проекта некоторые запросы стали работать плохо (стало больше данных, стал выбираться другой план выполнения запроса). Скорее всего, для исправления достаточно запустить analyze или немножко покрутить настройки. Но нужно знать, что делать и как это делать. Как минимум, нужно прочитать документацию PostgreSQL на эту тему, а читать документацию почему-то не любят. Может потому, что в MySQL от неё мало помощи? :)

    Подчеркну, что PostgreSQL в этом смысле не хуже, просто он позволяет отложить проблемы, а MySQL сразу их вываливает и приходится тратить время и деньги на их решение. В этом смысле MySQL работает всегда стабильно плохо, и еще на этапе разработки люди эти особенности учитывают: делают все максимально простым способом. Это относится только к производительности, точнее, к способам её достижения и к её прогнозируемости. В плане корректности и удобства PostgreSQL на голову выше MySQL.

    Так что же выбрать?

    Чтобы определиться с выбором между MySQL и PostgreSQL для конкретного проекта, прежде всего нужно ответить на другие вопросы.

    Во-первых, какой опыт есть у команды? Если вся команда имеет 10 лет опыта работы с MySQL и нужно запуститься как можно быстрее, то не факт, что стоит менять знакомый инструмент на незнакомый. Но если сроки не критичны, то стоит попробовать PostgreSQL.

    Во-вторых, нужно не забывать про проблемы эксплуатации. Если у вас не высоконагруженный проект, то с точки зрения производительности разницы между этими двумя СУБД нет. Зато у PostgreSQL есть другое важное преимущество: он более строгий, делает больше проверок за вас, дает меньше возможности ошибиться, и это в перспективе огромное преимущество. Например, в MySQL приходится писать собственные инструменты для верификации обычной ссылочной целостности базы. И даже с этим могут быть проблемы. В этом смысле PostgreSQL инструмент более мощный, более гибкий, разрабатывать на нем приятнее. Но это во многом зависит от опыта разработчика.

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