Что быстрее mysql или postgresql. Какую базу данных выбрать: MySQL vs PostgreSQL

Меня часто спрашивают, «Что вы предпочитаете, PostgreSQL или MySQL?» Мой ответ всегда один и тот же: «Это – вопрос предпочтения». Вы можете задать множеству других разработчиков тот же самый вопрос, и их ответы будут весьма различного толка.

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

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

Однако, некоторые из конфигураций этих типов таблиц весьма сложны. Не многие разработчики сети или программисты используют дополнительные типы таблиц, существующие в MySQL. А теперь давайте сравним эти два продукта.

Список особенностей и возможностей

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

Из таблицы мы видим, что PostgreSQL предлагает полные особенности и возможности традиционных приложений баз данных, в то время как MySQL сосредотачивается на более быстром выполнении (работе) для веб приложений. Развитие индустрии «открытых исходников» принесет большее количество особенностей и возможностей в последующих версиях обеих баз данных.

Таблица A: сравнение MySQL и PostgreSQL

Когда использовать MySQL

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

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

Позвольте мне описать некоторые плюсы использования MySQL:

• MySQL относительно быстрее PostgreSQL.
• Дизайн и планирование базы данных несколько проще.
• Можно создать простой веб сайт с использованием базы.
• Ответы на запросы MySQL были хорошо протестированны.
• Нет нужды использовать методы очистки (вакуум).

Когда использовать PostgreSQL

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

Cуществует немало разработчиков, которые предпочитают богатые функциональные возможности SQL команд PostgreSQL. Одно из наиболее ощутимых различий между MySQL и PostgreSQL – невозможность создания вложенных подзапросов (селектов) в MySQL. PostgreSQL соответствует многими SQL стандартам ANSI, таким образом позволяя создание сложных команд SQL.

Несколько причин использовать PostgreSQL:

• Сложный дизайн базы данных.
• Переезд с Oracle, Sybase или MSSQL.
• Сложные наборы правил.
• Использование процедурных языков на сервере.
• Транзакации
• Использование хранимых процедур.
• Использование географичеких данных.
• R-Trees (например, использование индексов).

Заключение

Вы должны будете выбрать, взвесив все плюсы и минусы, какая база данных является «совершенной» для вашего приложения или сайта. А может быть и такое, что вы захотите использовать обе базы (бывают и такие случаи). Мое заключение – одна база не обязательно лучше другой, и каждая из них занимает свою определеную нишу в мире баз данных с открытым исходным кодом.

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

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

В PostgreSQL вы можете использовать курсоры . Представьте, что некоторый запрос возвращает гигабайт данных. Вы вынуждены передать весь этот гигабайт по сети (если СУБД работает на отдельном сервере) и сохранить его в памяти перед тем, как что-то с ним делать. Даже если используемый вами драйвер поддерживает функции типа fetch_next_row, в действительности он все равно сначала кладет весь результат выполнения запроса в память. С помощью курсоров вы можете не только забирать данные кусками, тем самым обрабатывая их в постоянном объеме памяти, но и свободно перемещаться по ним в разные стороны. Например, вы можете прочитать первые 100 строк, потом посмотреть 10001-ую, и в зависимости от ее значения перейти к последней строке или вообще закрыть курсор.

Не менее интересным механизмом являются функциональные индексы . Допустим, вам нужно хранить имя и фамилию пользователя в отдельных столбцах и с учетом регистра, однако поиск пользователей при этом происходит по полному имени и без учета регистра. Если СУБД не поддерживает функциональные индексы, вы вынуждены создать в таблице дополнительное поле со значением LOWER (first_name || " " || last_name) , построить по нему индекс и поддерживать в этом поле правильное значение. Если такого рода вариантов запросов десять, вам понадобится десять дополнительных столбцов. Функциональные индексы, как и следует из названия, позволяют построить индекс по произвольной функции, избежав тем самым всех описанных проблем. Например, вы можете эффективно выполнять запросы с условиями вроде WHERE sin(x) > 0.45 AND sin(x) < 0.46 .

PostgreSQL может строить индексы только по части строк в таблице. Этот механизм называется частичными индексами . Например, если вы ходите в базу с запросами вроде SELECT * FROM logs WHERE ip > inet "192.168.0.0" AND ip < inet "192.168.0.255" AND level = "error" , то можете построить индекс по полю ip только для строк, значение поля level которых равно "error" . Это имеет смысл, если логов много, а строк со значением level = "error" мало. С помощью частичных индексов вы получаете более быстрые запросы и меньшие накладные расходы (место на диске, время добавления новых строк), чем в случае использования обычных индексов.

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

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

Базы данных предназначены для структурированного хранения и быстрого доступа к различным данным. Каждая база данных, кроме самих данных, должна иметь определенную модель работы, по которой будет выполняться обработка данных. Для управления базами данных используются СУБД или системы управления базами данных, именно к таким программам относятся MySQL и Postgresql.

Реляционные системы управления базами данных позволяют размещать данные в таблицах, связывая строки из разных таблиц и, таким образом, связывая разные, объединенные логически данные. Перед тем, как вы сможете сохранять данные, необходимо создать таблицы определенного размера и указать тип данных для каждого столбца. Столбы представляют поля данных, а сами данные размещены в строках. Обе системы управления базами данных, и MySQL vs Postgresql принадлежат к реляционным. Дальше мы рассмотрим подробнее чем отличаются обе программы. А теперь перейдем к более детальному рассмотрению.

Краткая история

MySQL

Разработка MySQL началась еще в 90х годах. Первый внутренний выпуск базы данных состоялся в 1995 году. За это время разработкой программы занимались несколько компаний. Разработка была начата шведской компанией MySQL AB, которую приобрела Sun Microsystems, которая, собственно перешла в собственность Oracle. На данный момент, начиная с 2010 года, разработкой занимается Oracle.

Postgresql

Разработка Postrgresql началась в далеком 1986 году в стенах Калифорнийского университета Беркли. Разработка длилась почти восемь лет, затем проект разделился на две части коммерческую базу данных IIlustra и полностью свободный проект Postrgesql, который разрабатывается энтузиастами.

Хранение данных

MySQL

MySQL - это реляционная база данных, для хранения данных в таблицах используются различные движки, но работа с движками спрятана в самой системе. На синтаксис запросов и их выполнение движок не влияет. Поддерживаются такие основные движки MyISAM, InnoDB, MEMORY, Berkeley DB. Они отличаются между собой способом записи данных на диск, а также методами считывания.

Postgresql

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

Стандарт SQL

Независимо от используемой системы управления базами данных, SQL - это стандартизированный язык выполнения запросов. И он поддерживается всеми решениями, даже MySQL или Postgresql. Стандарт SQL был разработан в 1986 году и за это время уже вышло нескольких версий.

MySQL

MySQL поддерживает далеко не все новые возможности стандарта SQL. Разработчики выбрали именно этот путь развития, чтобы сохранить MySQL простым для использования. Компания пытается соответствовать стандартам, но не в ущерб простоте. Если какая-то возможность может улучшить удобство, то разработчики могут реализовать ее в виде своего расширения не обращая внимания на стандарт.

Postgresql

Postgresql - это проект с открытым исходным кодом, он разрабатывается командой энтузиастов, и разработчики пытаются максимально соответствовать стандарту SQL и реализуют все самые новые стандарты. Но все это приводит к ущербу простоты. Postgresql очень сложный и из-за этого он не настолько популярен как MySQL.

Возможности обработки

Из предыдущего пункта выплывают и другие отличия postgresql от mysql, это возможности обработки данных и ограничения. Естественно, соответствие более новым стандартам дает более новые возможности.

MySQL

При выполнении запроса MySQL загружает весь ответ сервера в память клиента, при больших объемах данных это может быть не совсем удобно. В основном по функциям Postgresql превосходит Mysql, дальше рассмотрим в каких именно.

Postgresql

Postgresql поддерживает использование курсоров для перемещения по полученным данным. Вы получаете только указатель, весь ответ хранится в памяти сервера баз данных. Этот указатель можно сохранять между сеансами. Здесь поддерживается построение индексов сразу для нескольких столбцов таблицы. Кроме того, индексы могут быть различных типов, кроме hash и b-tree доступны GiST и SP-GiST для работы с городами, GIN для поиска по тексту, BRIN и Bloom.

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

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

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

MySQL

В большинстве случаев для организации работы с базой данных в MySQL используется таблица InnoDB, эта таблица представляет из себя B-дерево с индексами. Индексы позволяют очень быстро получить данные из диска, и для этого будет нужно меньше дисковых операций. Но сканирование дерева требует нахождения двух индексов, а это уже медленно. Все это значит что MySQL будет быстрее Postgresql только при использовании первичного ключа.

Postgresql

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

В целом PostgreSQL работает быстрее, за исключениям использования первичных ключей. Давайте рассмотрим несколько тестов с различными операциями:

Типы данных

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

MySQL

MySQL поддерживает такие типы данных:

• TINYINT : очень маленькое целое.;
• SMALLINT: маленькое целое;
• MEDIUMINT: целое среднего размера;
• INT: целое нормального размера;
• BIGINT: большое целое;
• FLOAT: знаковое число с плавающей запятой одинарной точности;
• DOUBLE, DOUBLE PRECISION, REAL: знаковое число с плавающей запятой двойной точности
• DECIMAL, NUMERIC: знаковое число с плавающей запятой;
• DATE: дата;
  DATETIME: комбинация даты и времени;
• TIMESTAMP: отметка времени;
• TIME: время;
  YEAR: год в формате YY или YYYY;
• CHAR : строка фиксированного размера, дополняемая справа пробелами до максимальной длины;
• VARCHAR: строка переменной длины;
• TINYBLOB, TINYTEXT: двоичные или текстовые данные максимальной длиной 255 символов;
• BLOB, TEXT : двоичные или текстовые данные максимальной длиной 65535 символов;
• MEDIUMBLOB, MEDIUMTEXT: текст или двоичные данные;
• LONGBLOB, LONGTEXT: текст или двоичные максимальной данные длиной 4294967295 символов;
• ENUM: перечисление;
• SET: множества.

Postgresql

Поддерживаемые типы полей в Postgresql достаточно сильно отличаются, но позволяют записывать точно те же данные:

• bigint: знаковое 8-байтовое целое;
• bigserial : автоматически увеличиваемое 8-байтовое целое;
• bit: двоичная строка фиксированной длины;
• bit varying: двоичная строка переменной длины;
• boolean: флаг;
• box: прямоугольник на плоскости;
• byte : бинарные данные;
• character varying: строка символов фиксированной длины;
• character:
• cidr: сетевой адрес IPv4 или IPv6;
• circle: круг на плоскости;
• date : дата в календаре;
• double precision: число с плавающей запятой двойной точности;
• inet: адрес интернет IPv4 или IPv6;
• integer : знаковое 4-байтное целое число;
• interval: временной промежуток;
• line: бесконечная прямая на плоскости;
• lseg: отрезок на плоскости;
• macaddr: MAC-адрес;
• money: денежная величина;
• path: геометрический путь на плоскости;
• point: геометрическая точка на плоскости;
• polygon: многоугольник на плоскости;
• real: число с плавающей точкой одинарной точности;
• smallint: двухбайтовое целое число;
• serial: автоматически увеличиваемое четырехбитное целое число;
• text: строка символов переменной длины;
• time: время суток;
• timestamp: дата и время;
• tsquery : запрос текстового поиска;
• tsvector: документ текстового поиска;
• uuid : уникальный идентификатор;
• xml: XML-данные.

Как видите, типов данных в Postgresql больше и они более разнообразны, есть свои типы полей для определенных видов данных, которых нет MySQL. Отличие MySQL от Postgresql очевидно.

Разработка

Оба проекта имеют открытый исходный код, но развиваются по-разному. Развитие MySQL нравится далеко не всем. И в этом сравнение mysql и postgresql дает много отличий.

MySQL

База данных MySQL разрабатывается компанией Oracle и ходят слухи, что компания намерено тормозит развитие движка. Было создано очень много форков проекта, в том числе форк MariaDB от разработчика оригинальной MySQL. Но все же развитие остается медленным.

Postgresql

Как было сказано в начале статьи разработка началась в университете Беркли. Затем перешла в коммерческую компанию. Сейчас программа разрабатывается независимой группой программистов и советом нескольких компаний. Новые версии выпускаются достаточно активно и получают все новые и новые функции.

Выводы

В этой статье мы выполнили сравнение mysql и postgresql, рассмотрели основные отличия обоих систем управления базами данных и попытались понять что лучше postgresql или mysql. В общем результате лучшим по возможностях получается Postgresql, но он сложен и не везде его можно применять. MySQL проще, но не поддерживает некоторых интересных функций. А какую базу данных вы выберите для своего проекта? Почему именно ее? Напишите в комментариях!

На завершение видео с описанием возможностей и перспектив 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? Тут всё чётко.