Битва титанов: что лучше — PostgreSQL или MySQL?

Сегодня разбираемся, в чем разница у этих двух систем и что лучше выбрать для вашей конкретной задачи.

Что такое PostgreSQL?

PostgreSQL — мощная система управления объектно-реляционными базами данных с открытым исходным кодом (СУОРБД).

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

В рейтинге DB-Engines PostgreSQL занимает четвертое место.

История PostgreSQL

PostgreSQL разработана на основе пакета POSTGRES, написанного в Калифорнийском университете в Беркли.

Реализация POSTGRES началась в 1986 году. Проектом управлял профессор Майкл Стоунбрейкер (Michael Stonebraker). POSTGRES использовалась для научных и бизнес-проектов, а также для обучения в нескольких университетах.

В 1993 году сообщество внешних пользователей выросло почти вдвое и поддерживать проект стало сложно. Поэтому история POSTGRES закончилась на версии 4.2.

В 1994 году Эндрю Ю (Andrew Yu) и Джолли Чен (Jolly Chen) добавили в POSTGRES интерпретатор языка SQL. Под новым именем Postgres95 продукт был опубликован онлайн и стал потомком первоначального POSTGRES из Беркли, но уже с открытым исходным кодом.

В 1996 году название Postgres95 утратило актуальность, и продукт переименовали в PostgreSQL, чтобы подчеркнуть связь между исходным POSTGRES и более новыми возможностями с поддержкой SQL.

Возможности PostgreSQL

• Поддерживает большинство требований стандарта SQLверсия 15 реализует 170 из 179 обязательных возможностей SQL:2016 Core, при этом предоставляя много современных функций. В том числе можно писать сложные запросы, именованные подзапросы, внешние ключи, триггеры, настраиваемые представления, оконные функции, поддержку версий и так далее.
• Поддерживает расширение за счет создания типов данных, функций и операторов, а также процедурных языков.
• Пользовательские функции можно писать на SQL и C. При этом в дистрибутив включено четыре процедурных языка: PL/pgSQL, PL/Tcl, PL/Perl и PL/Python.

Можно использовать и другие языки со сторонней поддержкой, а также реализовать свой процедурный язык.

• Таблицы в PostgreSQL представлены в виде объектов.
• PostgreSQL соответствует требованиям ACIDатомарность, согласованность, изолированность и долговечность.
• Хранит данные в объектах, поддерживает наследование.
• Поддерживает базы данных без ограничения размера.
• Поддерживает как реляционные запросы и данные SQL, так и нереляционные запросы и данные в формате JSON.
• CTECommon Table Expression дает возможность использовать именованные подзапросы в составе основного запроса, а также использовать их рекурсивно. Это помогает составлять сложные запросы. Эта возможность отсутствует в MySQL.
• Работает на многих платформах: Linux (все новые дистрибутивы), Windows (XP и выше), FreeBSD, OpenBSD, NetBSD, macOS, AIX, HP/UX и Solaris. Другие линуксоподобные системы также могут подойти, но они не протестированы.

Характеристики и преимущества PostgreSQL

Подробные характеристики PostgreSQL можно просмотреть в матрице характеристик. Ниже кратко рассмотрим основные из них.

В системе доступны такие типы данных:

Примитивы	Integer, Numeric, String, Boolean
Структурированные	Date/Time, Array, Range / Multirange, UUID
Документы	JSON/JSONB, XML, Key-value (Hstore)
Геометрические	Point, Line, Circle, Polygon
Пользовательские	составные, пользовательские типы

Целостность данных в системе можно реализовать, используя:

• UNIQUE, NOT NULL;
• первичные ключи;
• внешние ключи;
• ограничения исключения;
• явные блокировки, рекомендательные блокировки.

Также доступны функции, реализующие параллелизм и повышающие производительность:

• индексирование: Б-дерево, по нескольким столбцам, с использованием выражений, частичное;
• расширенное индексирование: GiST, SP-Gist, KNN Gist, GIN, BRIN, покрывающие индексы, фильтр Блума;
• сложный планировщик/оптимизатор запросов, сканирование только по индексам, статистика по нескольким столбцам;
• транзакции, в том числе вложенные;
• управление параллельным доступом посредством многоверсионности (Multi-Version concurrency Control, MVCC);
• параллелизация запросов на чтение и создания индексов с использованием Б-дерева;
• сегментирование таблиц;
• все уровни изоляции транзакций, определенные в стандарте SQL, в том числе Serializable;
• компиляция выражений в режиме реального времени (Just-in-time, JIT).

Для надежности работы системы, включая аварийное восстановление, в систему встроены:

• журнал предзаписи (Write-ahead Logging, WAL);
• репликация: асинхронная, синхронная, логическая;
• восстановление на момент времени (Point-in-time-recovery, PITR), активный режим ожидания;
• табличные пространства.

Также в целях безопасности PostgreSQL включает аутентификацию с помощью GSSAPI, SSPI, LDAP, SCRAM-SHA-256 или сертификата, надежная систему контроля доступа, безопасность на уровне столбцов и строк и многофакторную аутентификацию с использованием сертификатов и дополнительного метода.

Расширяемость системы реализована с помощью:

• Хранимых функций и процедур.
• Процедурных языков: PL/pgSQL, Perl, Python и Tcl. Расширения дают возможность использовать другие языки, такие как Java, JavaScript (V8), R, Lua и Rust.
• Выражения пути SQL/JSON.
• Обертки сторонних данных: подключения к другим базам или потокам данных с использованием стандартного интерфейса SQL.
• Настраиваемого интерфейса хранения для таблиц.
• Многих других расширений, которые предоставляют дополнительную функциональность, включая PostGIS.

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

Зачем использовать PostgreSQL?

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

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

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

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

Недостатки использования PostgreSQL

Напоследок расскажем, какие проблемы могут возникнуть при использовании системы:

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

Перейдем к сопернику PostgreSQL — MySQL.

Что такое MySQL?

MySQL — одна из самых популярных в мире систем управления реляционными базами данных (СУРБД) с открытым исходным кодом.

В нее включено множество функций, разработанных за более чем 25 лет. В рейтинге DB-Engines она занимает второе место, уступая лишь Oracle Database.

История MySQL

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

В результате был разработан новый SQL-интерфейс для базы данных почти с таким же API, как и у mSQL. Этот API должен был упростить портирование кода, написанного для mSQL, для использования с MySQL.

MySQL названа в честь дочери соучредителя MySQL AB Микаэла «Монти» Видениуса (Michael Widenius) — Мю.

В мае 1996 года была выпущена версия MySQL 1.0 для ограниченного круга пользователей, а публичный релиз версии 3.33.1 состоялся в октябре 1996 года. В него был включен только бинарный дистрибутив для Solaris. Месяцем позже был выпущен код и двоичный релиз для Linux.

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

В 2008 году компания Sun Microsystems приобрела MySQL AB. Через два года, в 2010 году компанию Sun Microsystems приобрела Oracle, которая владеет MySQL до сих пор.

Возможности MySQL

• Двойное лицензирование. MySQL можно использовать как продукт с открытым исходным кодом согласно условиям лицензии GNU General Public License или приобрести у Oracle стандартную коммерческую лицензию.
• Протестирована с широким спектром различных компиляторов.
• Поддерживается большинством CMS и веб-фреймворков (даже без дополнительных модулей).
• Очень быстро выполняет операции чтения.
• Достаточно просто осваивается, устанавливается и настраивается.
• Проста в обслуживании. Вы можете обслуживать ее без администратора.

Характеристики и преимущества MySQL

MySQL написана на C и C++ и работает на многих платформах, включая Oracle Linux/Red Hat/ CentOS, Oracle Solaris, Ubuntu, SUSE, Debian и другие дистрибутивы Linux, Microsoft Windows Server, Microsoft Windows, macOS. Для портативности система настраивается с использованием CMake.

Последняя версия проекта включает:

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

Кроме прочего, MySQL упрощает добавление других механизмов хранения. Это полезно, если вы хотите предоставить интерфейс SQL для внутренней базы данных.

При работе с системой вам доступно множество типов данных, включая целые числа со знаком/без знака длиной 1, 2, 3, 4 и 8 байт, FLOAT, DOUBLE, CHAR, VARCHAR, BINARY, VARBINARY, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM и пространственные типы OpenGIS.

Кроме того вы можете работать со строками фиксированной и переменной длины.

Список операторов и функций, поддерживаемых MySQL:

• операторы и функции в списке SELECT и предложении WHERE;
• GROUP BY и ORDER BY, а также групповые функции COUNT(), AVG()и другие;
• LEFT OUTER JOIN и RIGHT OUTER JOIN со стандартным синтаксисом SQL и ODBC;
• псевдонимы для таблиц и столбцов, как того требует стандартный SQL;
• DELETE, INSERT, REPLACE и UPDATE для возврата количества строк, которые были изменены (затронуты), или для возврата количества совпадающих строк путем установки флага при подключении к серверу;
• специфичные для MySQL операторы SHOW, которые извлекают информацию о базах данных, механизмах хранения, таблицах и индексах;
• INFORMATION_SCHEMA, реализованная по стандарту SQL;
• оператор EXPLAIN, показывающий, как оптимизатор интерпретирует запрос.

Обратите внимание! В MySQL имена функций не зависят от имен таблиц или столбцов.

Например, ABS является допустимым именем столбца. Единственное ограничение состоит в том, что для вызова функции не допускаются пробелы между именем функции и следующим за ним символом (.

Также MySQL позволяет ссылаться на таблицы из разных баз данных в одном операторе.

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

Система поддерживает большие базы данных, но имеет ограничения в количестве индексов на таблицу — до 64. Также есть такие ограничения:

• каждый индекс может состоять из 1-16 столбцов или частей столбцов;
• максимальная ширина индекса для таблиц InnoDB составляет 767 или 3072 байта;
• максимальная ширина индекса для таблиц MyISAM составляет 1000 байт.

К MySQL можно подключиться с использованием нескольких протоколов:

• С помощью сокетов TCP/IP на любой платформе.
• В Windows можно подключиться с использованием именованных каналов, если сервер запущен со включенной переменной named_pipe. Также возможны подключения с разделяемой памятью, если включена системная переменная shared_memory. Такое подключение выполняется с помощью опции --protocol=memory.
• В системах Unix клиенты могут подключаться с помощью файлов сокетов доменов.

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

Также у системы есть API для C, C++, Eiffel, Java, Perl, PHP, Python, Ruby и Tcl, а интерфейс Connector/ODBC (MyODBC) обеспечивает поддержку MySQL для клиентов, использующих соединения ODBC (Open Database Connectivity).

Интерфейс Connector/J обеспечивает поддержку MySQL для клиентских программ Java, которые используют соединения JDBC. Клиенты можно запускать на Windows и Unix. MySQL Connector/NET позволяет разработчикам с легкостью создавать приложения .NET с подключением к MySQL.

В MySQL включено несколько клиентских и служебных программ. Это как программы для командной строки, такие как mysqldump и mysqladmin, так и программы с графическим интерфейсом, такие как MySQL Workbench.

Зачем использовать MySQL?

Многие крупнейшие и быстрорастущие компании во всем мире, в том числе Facebook, Twitter, Booking.com и Verizon, пользуются MySQL. Эта СУБД обеспечивает значительную экономию времени и денег для их сайтов.

Большинство веб-разработчиков предпочитают MySQL благодаря простоте базы данных и обучения работе с ней.

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

Недостатки использования MySQL

Главный недостаток системы — неэффективность работы с очень большими базами данных. Остальные минусы достаточно специфические, например:

• иребования ACID поддерживаются только при использовании с InnoDB и NDB Cluster Storage;
• сложно отлаживать хранимые процедуры из-за отсутствия хороших инструментов разработки и отладки, которые есть у других СУБД;
• версии ниже 5.0 не поддерживают ROLE, COMMIT и хранимые процедуры;
• слабые хранимые процедуры;
• функция GROUP BY не всегда работает не так, как ожидается.

Кроме того, MySQL не поддерживает SQL в полном объеме. Поэтому разработчикам бывает сложно научиться использовать синтаксис MySQL. Например, эта СУБД не поддерживает ограничения CHECK.

Разница между MySQL и PostgreSQL

Рассмотрим основные различия этих двух систем.

Соответствие стандартам

PostgreSQL поддерживает большинство требований стандарта SQL.

MySQL Server расходится со стандартами ANSI SQL и ODBC SQL в некоторых случаях.

Например, права доступа к таблице в MySQL не отменяются автоматически при ее удалении. Нужно явно вызывать REVOKE. Функция CAST() не поддерживает преобразование в REAL и BIGINT.

Администрирование

Администрировать PostgreSQL намного сложнее, чем MySQL, потому что в PostgreSQL намного больше возможностей.

MySQL подойдет для небольших проектов и если вы не можете себе позволить администратора, в то время как для управления сложными таблицами в PostgreSQL потребуется квалифицированный персонал.

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

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

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

Документация PostgreSQL намного полнее, чем документация MySQL. Последняя часто сложна для понимания, и приходится искать объяснения в сторонних источниках.

Что лучше: MySQL или PostgreSQL?

Ответ на этот вопрос зависит от характеристик проекта, опыта команды и сроков.

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

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

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

Если у вас небольшой проект, который не будет масштабироваться, и есть опыт работы с MySQL, то выбирайте MySQL. А если у вас крупный проект со сложными данными, если предвидится, что он будет расширяться, то воспользуйтесь PostgreSQL.