Журнал Mail.ru Cloud Solutions
об IT-бизнесе, технологиях и цифровой трансформации

PostgreSQL или MySQL: какая из этих реляционных СУБД лучше впишется в ваш проект Mail.ru Cloud Solutions
Mail.ru Cloud Solutions
  • 30 марта
  • Технологии

PostgreSQL или MySQL: какая из этих реляционных СУБД лучше впишется в ваш проект

Автор: Анатолий Ализар
Популярное

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

Функции СУБД MySQL и PostgreSQL

Практически любой разработчик скажет, что MySQL лучше подходит для веб-сайтов и онлайн-транзакций, а PostgreSQL — для больших и сложных аналитических процессов. Он также добавит, что PostgreSQL поставляется с «множеством отличных функций», таких как расширяемость и нативный функционал NoSQL, что помогает в управлении сложной БД. Наконец, он напомнит вам, что MySQL не перегружена функциями, потому что ставит приоритетом «скорость и надежность».

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

Сравнение функций

PostgreSQL 10

MySQL 8

Обобщенные табличные выражения (CTE)

Есть

Есть (недавно добавили)

Декларативное секционирование

Есть (недавно добавили)

Есть

Полнотекстовый поиск

Есть

Есть

Геоинформационные системы (GIS) и идентификаторы системы координат (SRS)

Есть

Есть (обновлена)

JSON

Есть

Есть (обновлена)

Логическая репликация

Есть (недавно добавили)

Есть

Полусинхронная репликация

Есть (недавно добавили)

Есть

Оконные функции

Есть

Есть (недавно добавили)

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

Общий обзор MySQL и PostgreSQL

MySQL: общие характеристики

Что такое MySQL? Это быстрая, надежная и универсальная реляционная система управления базами данных (СУБД), самая популярная в мире: по итогам прошлого года ее использовали 39% разработчиков. Хотя ей не хватает обширных возможностей PostgreSQL, она отлично подходит для широкого спектра приложений, особенно для веба.

MySQL — идеальный выбор для масштабируемых веб-приложений. Эта СУБД входит в стандартный стек LAMP — набор веб-приложений с открытым исходным кодом: Linux, Apache HTTP Server, MySQL и PHP. Кроме того, на MySQL работают популярные системы управления контентом сайтов, такие как Drupal, Joomla и WordPress.

MySQL используют многие сайты в интернете

Вот некоторые характеристики MySQL:

  1. Открытый исходный код: MySQL — это свободная и открытая реляционная СУБД (РСУБД).
  2. Долгая история: MySQL доступна с 1995 года.
  3. Отзывчивое сообщество: преданное сообщество энтузиастов всегда поможет с решением проблем и устранением неполадок.
  4. Стабильность и надежность: все согласны, что MySQL является очень стабильной РСУБД, пока базы данных остаются «компактными», а вы не забываете о регулярном обслуживании.
  5. Функции MVCC: MySQL теперь предлагает управление параллельным доступом посредством многоверсионности (MVCC). Это известная функция PostgreSQL (обсудим ее ниже).
  6. Частые обновления: MySQL часто обновляется, выпуская новые функции и улучшения безопасности.
  7. Высокая оценка пользователей: у MySQL рейтинг 4,4 звезды (из пяти) по итогам 1420 отзывов на G2 Crowd.
«MySQL — открытая, бесплатная, стабильная система управления базами данных, которая подходит для продакшна. Эту легковесную СУБД можно ставить и на серверах в продакшне с большими нагруженными приложениями, и на рабочих компьютерах разработчиков. Поддерживаются все платформы, включая Windows, Linux и Mac. Система безопасна, а все уязвимости закрыты».

Отзыв о MySQL от обозревателя на G2 Crowd

Среди пользователей MySQL: Facebook, Google, Flickr, GitHub, NASA, Netflix, Spotify, Tesla, Twitter, Uber, ВМФ США, WeChat, Википедия, YouTube, Zappos, Zendesk.

PostgreSQL: описание и общие характеристики

PostgreSQL считается подходящим решением для сложных операций с большими объемами данных. Причина в том, что она лучше справляется с чрезвычайными ситуациями (обсудим это ниже).

Считается, что у PostgreSQL больше функций, чем в других СУБД (пользователи это подтверждают). Кроме того, PostgreSQL — расширяемая система, ее работа базируется на каталогах (подход catalog-driven). Другими словами, она хранит информацию не только о таблицах и столбцах, но и о типах данных, типах индексов, функциональных языках и так далее. Пользователи могут изменять всю эту информацию в системных каталогах, которые выглядят как обычные таблицы.

Другие отличительные черты базы данных PostgreSQL — это объектно-реляционная система, которая совместима с принципами ACID:

  • atomicity — атомарность;
  • consistency — согласованность;
  • isolation — изолированность;
  • durability — cтойкость.

Также она поддерживает высокопараллельные вычисления и NoSQL (справедливости ради, MySQL также поддерживает NoSQL с версии 8.0).

Вот некоторые характеристики PostgreSQL:

  1. Открытый исходный код: PostgreSQL — это свободная и открытая объектно-реляционная система управления базами данных (ОРСУБД). В отличие от обычных РСУБД. Она позволяет использовать как объектно-ориентированные, так и реляционные базы данных.
  2. Расширенные настройки: вы можете разработать собственные плагины и настроить PostgreSQL под свои нужды. PostgreSQL также позволяет активировать нестандартные функции, написанные на других языках программирования, таких как C/C++, Java и других.
  3. Долгая история: PostgreSQL развивается с 1988 года.
    • Частые обновления: так, последним обновлением на март 2020 года в PostgreSQL является версия 12.2 от 13 февраля 2020 года.
  4. Либеральная открытая лицензия: у PostgreSQL щедрая опенсорсная лицензия, которая позволяет использовать, изменять и распространять СУБД как вам угодно.
  5. Функции MVCC: PostgreSQL была первой СУБД, которая реализовала функции управления параллельным доступом посредством многоверсионности (MVCC).
  6. Отзывчивое сообщество: преданное сообщество разработчиков и активистов всегда готово помочь. Кроме того, можно воспользоваться платной поддержкой от сторонних компаний. Сообщество поддерживает PostgreSQL и обновляет платформу через группу PostgreSQL Global Development Group.
  7. Высокая оценка пользователей: у PostgreSQL рейтинг 4,4 звезды (из пяти) по итогам 452 отзывов на G2 Crowd.
«PostgreSQL — одна из самых интересных РСУБД с открытым исходным кодом. Она бесплатная, кроме того, предлагает много продвинутых опций. На сегодняшний день PostgreSQL считается самой продвинутой системой управления базами данных. При совершении транзакции тут не нужно ставить блокировки чтения, что дает лучшую масштабируемость. Также этот инструмент управляется не человеком или компанией, а сообществом разработчиков».

Отзыв о PostgreSQL от рецензента на G2 Crowd

Среди пользователей PostgreSQL: Apple, BioPharm, Cisco, Debian, Etsy, Facebook, Fujitsu, IMDB, Instagram, Macworld, Red Hat, Skype, Spotify, Sun Microsystem, Yahoo.

Когда разработчики выбирают MySQL, а когда PostgreSQL

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

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

Основные преимущества MySQL для разработчиков

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

  • InnoDB
  • MyISAM
  • Memory
  • CSV
  • Archive
  • Blackhole
  • NDB/NDBCLUSTER
  • Merge
  • Federated
  • Example

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

Варианты оптимизации сервера: MySQL предлагает множество вариантов настройки и оптимизации вашего сервера MySQL путем настройки переменных, таких как sort_buffer_size, read_buffer_size, max_allowed_packet и так далее.

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

Кроме того, ряд сервисов фронтенда — такие как Adminer, MySQL Workbench, HeidiSQL и dbForge Studio, добавляют к MySQL графический интерфейс, более удобный и простой, чем работа из командной строки.

Облачная СУБД: MySQL Database хорошо подходит для использования в облаке, многие облачные платформы предлагают соответствующие платные услуги: они готовы установить и поддерживать вашу базу данных.

Управление параллельным доступом посредством многоверсионности (MVCC) и соответствие ACID с движком InnoDB: в текущих версиях MySQL движок по умолчанию — это InnoDB. Он обеспечивает функциональность MVCC и соответствие требованиям ACID. Однако из-за формата таблиц MyISAM в InnoDB на MySQL все равно могут возникнуть проблемы с поврежденными таблицами.

Согласно документации MySQL, «несмотря на то что формат таблицы MyISAM очень надежен (все изменения в таблице, сделанные инструкцией SQL, записываются до возвращения инструкции), вы все равно можете столкнуться с повреждением таблиц». Переход на другой движок не спасет ситуацию: хуже того, он может привести к потере соответствия MVCC и ACID.

Основные преимущества PostgreSQL для разработчиков

ОРСУБД, а не просто РСУБД. Что такое PostgreSQL? Это объектно-реляционная система, а ее программирование представляет собой своеобразный мостик между объектно-ориентированным и реляционным/процедурным программированием (как C++). Это позволяет определять объекты и наследование таблиц, что порождает более сложные структуры данных. ОРСУБД великолепно подходит для данных, которые не вписываются в строго реляционную модель.

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

Поддержка NoSQL и большое разнообразие типов данных: PostgreSQL является популярным выбором для функций NoSQL. Она изначально поддерживает большое разнообразие типов данных, включая JSON, hstore и XML. Можете также настроить исходные типы данных и задействовать нестандартные функции.

Спроектирована для управления очень большими БД: функции PostgreSQL не ограничивают размер ваших баз данных. Например, по словам администратора баз данных на Adjust.com, у них PostgreSQL управляет базой «примерно на четыре петабайта». Далее он утверждает, что «окружение обрабатывает, а затем записывает в журнал, от 100 до 250 тысяч внешних запросов в секунду». Это действительно тяжелая нагрузка!

Управление параллельным доступом посредством многоверсионности (MVCC): это одна из главных причин, почему компании выбирают PostgreSQL. MVCC предоставляет одновременный доступ к БД множеству агентов на чтение и запись. Это устраняет необходимость каждый раз блокировать чтение-запись, когда кто-то взаимодействует с данными. Таким образом, значительно повышается эффективность управления СУБД и ее производительность.

MVCC обеспечивает такую функциональность через «изоляцию снапшотов» (как ее называет Oracle). Моментальные снимки (снапшоты) представляют состояние данных в определенный момент времени.

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

СУБД PostgreSQL также просто развернуть в облаке: в Mail.ru Cloud Solutions доступно развертывание из шаблона, сервис автоматической миграции данных, аудит работы с данными.

Поддержка пользователей в MySQL и PostgreSQL

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

Поддержка пользователей MySQL

Как проект с открытым исходным кодом, у MySQL большое сообщество активистов, готовых бесплатно помочь советами и рекомендациями. Лучший способ получить такую поддержку — обратиться на сайты MySQL и Percona.

Вот что один IT-специалист на G2 Crowd говорит о пользовательской поддержке MySQL:

«Мне больше всего понравилось, что для MySQL, как проекта с открытым исходным кодом, в интернете можно найти массу советов и получить помощь совершенно бесплатно. Это особенно полезно для проблем, которые затруднительно решить своими силами. Одно из главных преимуществ MySQL — большая популярность этой СУБД, так что вы найдете огромное сообщество людей, готовых помочь в любом вопросе».

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

Поддержка пользователей PostgreSQL

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

Вот что говорит на G2 Crowd один администратор баз данных о поддержке PostgreSQL:

«…самую лучшую поддержку обеспечивает сообщество на форумах, где отвечают на вопросы».

Другой рецензент на G2 Crowd сказал следующее:

«Лично мне показалось, что здесь немного сложнее получить поддержку сообщества или загуглить проблему. Но по мере роста популярности PostgreSQL поддержка сообщества становится лучше».

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

  • для настройки и использования СУБД требуется больше технических знаний;
  • экспертов по PostgreSQL меньше, чем экспертов по MySQL.

Что быстрее: MySQL или PostgreSQL

И MySQL, и PostgreSQL считаются одними из самых быстрых СУБД. Не совсем понятно, какая из них быстрее.

По словам Скотта Нойеса из TechTarget:

«В зависимости от аппаратного обеспечения и конфигурации легко найти бенчмарки, в которых будет побеждать та или иная СУБД. Одна может лучше работать на одноядерной машине с небольшим объемом памяти, а другая лучше масштабируется на несколько процессоров. Одна выигрывает по скорости операций на чтение, а другая — на запись».

Тесты производительности дают противоречивые результаты. Например, Windows Skills отдает предпочтение MySQL, а Benchw говорит, что PostgreSQL быстрее. В итоге скорость зависит от того, как именно вы используете базу данных. Известно, что PostgreSQL быстрее при обработке массивных наборов данных, сложных запросов и операций чтения-записи. Между тем, MySQL Database считается эффективнее в операциях только на чтение.

Какие языки программирования поддерживают MySQL и PostgreSQL

Поддерживаемые языки MySQL: C/C++, Delphi, Erlang, Go, Java, Lisp, Node.js, Perl, PHP, R.

Поддерживаемые языки PostgreSQL. Она поддерживает несколько более широкий набор языков: C/C++, Delphi, Erlang, Go, Java, JavaScript, Lisp, .Net, Python, R, Tcl и другие языки программирования.

С какими операционными системами работают MySQL и PostgreSQL

Рассмотрим, чем отличаются требования к операционной системе MySQL и PostgreSQL.

Совместимость MySQL с операционными системами

СУБД MySQL предлагает облачную поддержку и локальную установку в следующих операционных системах и форматах:

  • Windows
  • MacOS
  • Linux (Ubuntu, Debian, Generic, SUSE Linux Enterprise Server, Red Hat Enterprises, Oracle, Fedora)
  • Oracle Solaris
  • FreeBSD
  • Исходный код

Совместимость PostgreSQL с операционными системами

СУБД PostgreSQL предлагает облачную поддержку и локальную установку, обычно ее устанавливают на серверах Linux. Кроме того, доступен веб-сервер PostgREST для работы с базой данной через программные интерфейсы REST API.

Как сказано на сайте PostgreSQL:

«PostgREST является автономным веб-сервером, который превращает вашу базу данных PostgreSQL непосредственно в RESTful API. Конечные точки API и операции определяются структурными ограничениями и разрешениями в базе данных».

PostgreSQL доступна для следующих операционных систем:

  • MacOS
  • Solaris
  • Windows
  • BSD (FreeBSD, OpenBSD)
  • Linux (семейство Red Hat Linux, включая варианты CentOS/Fedora/Scientific/Oracle, Debian GNU/Linux и производные, Ubuntu Linux и производные, SuSE и OpenSuSE, другие дистрибутивы Linux)

Как MySQL и PostgreSQL индексируют данные

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

Типы индексирования MySQL

  1. Индексы в B-деревьях, таких как INDEX, FULLTEXT, PRIMARY KEY и UNIQUE.
  2. Индексы в R-деревьях, например, индексы для пространственных типов данных.
  3. Хэш-индексы и инвертированные списки при использовании индексов FULLTEXT.

Типы индексирования PostgreSQL

  1. Хэш-индексы и B-деревья.
  2. Частичные индексы, которые упорядочивают информацию только из части таблицы.
  3. Индексы выражений, которые вычисляются из функций, а не из значений в столбцах таблицы.

Чем отличается программирование под каждую СУБД

Нужно знать о трех различиях в программировании БД MySQL и PostgreSQL:

  1. Чувствительность к регистру.
  2. Наборы символов и строки по умолчанию.
  3. Функции IF и IFNULL вместо CASE.

Чувствительность к регистру

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

Наборы символов и строки по умолчанию

В некоторых версиях MySQL необходимо преобразовать наборы символов и строки в UTF-8. В PostgreSQL нет такой необходимости. Более того, в PostgreSQL вообще не допускается синтаксис UTF-8.

Функции IF и IFNULL вместо CASE

В MySQL можно спокойно использовать операторы IF и IFNULL. В PostgreSQL они не работают, а вместо них необходимо использовать оператор CASE.

Что выбрать: MySQL vs PostgreSQL

Выбор между MySQL и PostgreSQL часто сводится к следующим вопросам:

  1. Вам нужна многофункциональная СУБД, способная обрабатывать сложные запросы и поддерживать массивные базы данных? Тогда выбирайте PostgreSQL.
  2. Нужна более легкая БД, относительно простая в настройке и управлении, быстрая, надежная и понятная? Выбирайте MySQL.

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

Ссылка скопирована!

Что еще почитать про ИТ-бизнес