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

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

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

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

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

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

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

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

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

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

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

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

Среди пользователей MySQL DB: 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 года.
4. Частые обновления: так, последним обновлением на март 2020 года в PostgreSQL является версия 12.2 от 13 февраля 2020 года.
5. Либеральная открытая лицензия: у базы данных PostgreSQL щедрая опенсорсная лицензия, которая позволяет использовать, изменять и распространять СУБД как вам угодно.
6. Функции MVCC: база данных PostgreSQL была первой СУБД, которая реализовала функции управления параллельным доступом посредством многоверсионности (MVCC).
7. Отзывчивое сообщество: преданное сообщество разработчиков и активистов всегда готово помочь. Кроме того, можно воспользоваться платной поддержкой от сторонних компаний. Сообщество поддерживает PostgreSQL и обновляет платформу через группу PostgreSQL Global Development Group.
8. Высокая оценка пользователей: у PostgreSQL рейтинг 4,4 звезды (из пяти) по итогам 452 отзывов на 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 database server путем настройки переменных, таких как 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 все равно могут возникнуть проблемы с поврежденными таблицами.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Тесты производительности дают противоречивые результаты. Например, 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 доступна для следующих операционных систем:

• 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.

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