PostgreSQL vs MySQL — что выбрать?
Что такое системы управления базами данных (СУБД)
Данные любых информационных систем (проектов, компаний, сайтов, приложений и т.д.) хранятся в структурированном виде в базах данных (БД). Существуют программы, которые позволяют создавать БД, манипулировать данными в этой базе и в целом администрировать БД. Такие программы называются системами управления базами данных или СУБД (Database Management System, DBMS).
Функции СУБД:
- управление данными в оперативной памяти с использованием дискового кэша
- управление данными во внешней памяти (на дисках)
- сохранение истории (журналирование изменений)
- резервное копирование
- восстановление БД после сбоев
- поддержка языка определения данных
- поддержка языка манипулирования данными
Любая СУБД базируется на определенной модели, задающей структуру обработки данных, по которой классифицируется и сама БД, и ее система управления.
По модели БД и СУБД различают:
- управление данными в оперативной памяти с использованием дискового кэша
- управление данными во внешней памяти (на дисках)
- сохранение истории (журналирование изменений)
- резервное копирование
- восстановление БД после сбоев
- поддержка языка определения данных
- поддержка языка манипулирования данными
Мы посмотрим на различия между реляционной MySQL и объектно-реляционной PostgreSQL, на преимущества и недостатки обеих СУБД и постараемся понять, что лучше выбрать для своего проекта: PostgreSQL или MySQL?
Описание и общие характеристики MySQL и PostgreSQL
MySQL
Это реляционная система управления базами данных, РСУБД. Представляет собо ПО с открытым исходным кодом, но есть и лицензионная часть. Реляционные (от relation — «связь», «зависимость») означает, что такие системы работают с данными, как с набором записей с уникальным ключом и атрибутов, расположенных в двумерных таблицах и имеющих между собой связи. Записи — это строки, а атрибуты — столбцы. Помимо простых манипуляций с данными (например, выбирать, обновлять, вставлять, удалять), их можно логически объединять из разных таблиц и разных столбцов таблиц. Например, из трех таблиц о сотрудниках «личные данные», «зарплата» и «отработанные дни» (с большим количеством столбцов в каждой) нужно выбрать и показать только столбцы с ФИО, зарплатой за год и отработанными днями за определенный месяц. Реляционная СУБД справится с этим легко и быстро при любом размере БД. Реляционные базы данных и системы управления ими, в частности MySQL, уже несколько лет наиболее популярны при создании веб-приложений и сервисов разной нагруженности. MySQL поддерживает большинство традиционных типов данных: символы, строки, даты и пр. Считается, что MySQL хороша для веб-сайтов, небольших и средних приложений и онлайн-транзакций. Она простая, надежная, быстрая и не перегруженная функциями, чем и объясняется ее популярность.
PostgreSQL
Это объектно-реляционная система управления базами данных, ОРСУБД. Полностью свободное ПО. В отношении модели данных от предыдущей она отличается тем, что поддерживает некоторые технологии объектно-ориентированного подхода. В этом подходе программа представлена в виде совокупности объектов, относящихся каждый к своему классу, а классы выстроены в иерархию наследования. Объекты, классы и наследование реализованы и в структуре баз данных, и в языке запросов ОРСУД. PostgreSQL помимо традиционных типов данных также поддерживает неструктурированные и перечисляемые. Считается, что PostgreSQL хорошо подходит для работы с большими данными и сложной аналитики. Она многофункциональна, из-за чего работает медленней, чем MySQL, поэтому менее популярна.
Краткая история MySQL и PostgreSQL
MySQL была официально запущена в 1996 году компанией Oracle, которая и сейчас ее разрабатывает и поддерживает. Однако основным ее автором является программист Майкл (Монти) Видениус с 1979 года, работавший тогда в шведской компании TcX, а позже создавший MySQL AB. В результате серии поглощений права на эту СУБД последовательно переходили от MySQL AB к Sun Microsystems и затем к Oracle. Сейчас MySQL распространяется под общественной лицензией GNU General Public License и под коммерческой лицензией (платная поддержка).
PostgreSQL была разработана в 1996 году. Ее предшественницей была открытая Postgres, разработку которой начал в Беркли в 1986 году Майкл Стоунбрейкер — Тьюринговский лауреат и пионер в области технологий БД. Проект Postgres тогда спонсировали научные фонды и Министерство обороны США. После 8 лет разработки проект разделился на коммерческую БД IIlustra и свободный проект PostgreSQL, разрабатываемый энтузиастами сообщества. Сегодня PostgreSQL называют самой развитой СУБД с открытым исходным кодом.
PostgreSQL была разработана в 1996 году. Ее предшественницей была открытая Postgres, разработку которой начал в Беркли в 1986 году Майкл Стоунбрейкер — Тьюринговский лауреат и пионер в области технологий БД. Проект Postgres тогда спонсировали научные фонды и Министерство обороны США. После 8 лет разработки проект разделился на коммерческую БД IIlustra и свободный проект PostgreSQL, разрабатываемый энтузиастами сообщества. Сегодня PostgreSQL называют самой развитой СУБД с открытым исходным кодом.
Стандарт SQL в MySQL и PostgreSQL
Для управления реляционными БД используется стандартизированный структурированный язык запросов (Structured Query Language, сокращенно SQL). Этот язык был разработан в 1974 году специально для создания, изменения и управления данными в реляционной БД, управляемой соответствующей СУБД. Изначально язык представлял собой способ работы с БД пользователя, но позже усложнился, обогатился и стал больше похож на язык программирования. Но, пожалуй, правильней всего его будет назвать лингвистическим средством для взаимодействия прикладного ПО с БД.
К 80-м годам прошлого века было разработано несколько СУБД со своей реализацией языка запросов. Поэтому было принято решение утвердить единый языковой стандарт, который бы гарантировал этим СУБД переносимость ПО с одной СУБД на другую, если они будут поддерживать стандарт. В итоге с SQL не все так гладко, как планировалось, но это уже тема другой статьи. Здесь лишь отметим, что соответствие стандарту SQL отражено в названиях наших двух СУБД, поскольку обе они поддерживают SQL, но не полностью. Вернее, PostgreSQL полностью, а MySQL нет, потому что разработчики этой СУБД не хотят ее усложнять, чтобы не терять преимущество в производительности.
К 80-м годам прошлого века было разработано несколько СУБД со своей реализацией языка запросов. Поэтому было принято решение утвердить единый языковой стандарт, который бы гарантировал этим СУБД переносимость ПО с одной СУБД на другую, если они будут поддерживать стандарт. В итоге с SQL не все так гладко, как планировалось, но это уже тема другой статьи. Здесь лишь отметим, что соответствие стандарту SQL отражено в названиях наших двух СУБД, поскольку обе они поддерживают SQL, но не полностью. Вернее, PostgreSQL полностью, а MySQL нет, потому что разработчики этой СУБД не хотят ее усложнять, чтобы не терять преимущество в производительности.
Функции MySQL и PostgreSQL
MySQL Это система управления реляционными базами данных, где данные, связанные друг с другом посредством ключей, располагают в таблицах.
- Структурированные типы данных.
- Различные типы таблиц.
- Графический пользовательский интерфейс (GUI) — Workbench.
- Инициирование нового соединения рассматривается как новый поток.
- Следует некоторым стандартам ANSI SQL.
- Поддерживает транзакции, однако должен использоваться тип таблицы InnoDB.
- Ответ базы данных.
- ODBC.
- JDBC.
- Обобщенные табличные выражения (CTE).
- Декларативное секционирование.
- Полнотекстовый поиск.
- Логическая и полусинхронная репликация.
- Оконные функции.
- GIS и SRS.
PostgreSQL Это система управления объектно-реляционными базами данных, то есть можно создавать таблицы, соответствующие принципам объектно-ориентированного программирования (классы, наследование и т.д).
- Вместе со структурированными поддерживает также неструктурированные и перечисляемые типы данных.
- Графический пользовательский интерфейс (GUI) — pgAdmin.
- Инициирование нового соединения рассматривается как новый процесс.
- Близка к стандарту ANSI SQL.
- Вложенные селекты.
- Транзакции.
- Ответ базы данных.
- Внешние ключи.
- Представления.
- Хранимые процедуры.
- Триггеры.
- Unions.
- Полные Joins.
- Ограничители целостности.
- Вакуум (очистка).
- ODBC.
- JDBC.
- Обобщенные табличные выражения (CTE).
- Декларативное секционирование.
- Полнотекстовый поиск.
- Логическая и полусинхронная репликация.
- Оконные функции.
- GIS и SRS.
Хранение данных в MySQL и PostgreSQL
Для хранения данных в БД используются подсистемы хранения таблиц, которые еще называют движками. Они различаются способом записи данных на диск и методом считывания. PostgreSQL поддерживает только один движок: Storage Engine. MySQL поддерживает разные движки: InnoDB, MyISAM, Memory, CSV, Archive, Blackhole, NDB/NDBCLUSTER, Merge, Federated, Example.
Возможности обработки в MySQL и PostgreSQL
Заметная разница между MySQL и PostgreSQL заключается в обработке данных и ограничениях в возможностях обработки. MySQL во время выполнения запроса загружает весь ответ сервера в память клиента. Вот почему при работе с большими объемами данных эта СУБД неудобна. PostgreSQL для перемещения по полученным данным применяет указатели (курсоры) на данные, а сам ответ хранится в памяти сервера БД. Курсоры можно сохранять между сеансами. Поддерживаются регулярные выражения в запросах, рекурсивных запросах и наследовании таблиц.
Есть возможность строить индексы разного типа (для поиска по тексту, для работы с городами и пр.) для нескольких столбцов таблицы. Есть и ограничения, например новое поле можно добавить лишь в конце таблицы.
Есть возможность строить индексы разного типа (для поиска по тексту, для работы с городами и пр.) для нескольких столбцов таблицы. Есть и ограничения, например новое поле можно добавить лишь в конце таблицы.
Производительность MySQL или PostgreSQL: что быстрее?
Часто скорость работы MySQL и PostgreSQL при их сравнении во время выбора для проекта — это самый важный критерий. Доступ к данным в 2021 году должен быть быстрым, ведь бизнес не будет ждать полдня ответа на свой запрос к своей же БД. Здесь мы акцентируем внимание на том, что главное отличие MySQL от PostgreSQL именно в скорости работы, MySQL быстрее. Но напомним, что иногда бывает важна не столько скорость доступа к БД, сколько качество самих данных и результатов их анализа. Поэтому более медленная по причине своей многофункциональности PostgreSQL хорошо подходит для аналитики и работы с Big Data, а более быстрая MySQL — для сайтов и приложений.
Какие языки программирования поддерживают MySQL и PostgreSQL
MySQL поддерживает языки: PHP, C/C++, Delphi, Java, Node.js, Perl, Erlang, Go, Lisp, R.
PostgreSQL поддерживает языки: C/C++, Delphi, Java, JavaScript, Erlang, Go, Lisp, .Net, Python, R, Tcl, Ruby, Scheme, Lua, LOLCODE, Perl, sh.
С какими операционными системами работают MySQL и PostgreSQL
Совместимость MySQL с операционными системами. Возможна локальная установка и облачная поддержка в Windows, MacOS, Linux-подобных ОС (Oracle, Ubuntu, Debian, Fedora, Red Hat Enterprises, Generic, SUSE Linux Enterprise Server), FreeBSD, Oracle Solaris.
Совместимость PostgreSQL с операционными системами. Локальная установка и облачная поддержка осуществляется преимущественно на Linux-серверах. Также доступен веб-сервер PostgREST для работы с БД через программные интерфейсы REST API, который превращает вашу БД PostgreSQL в RESTful API. Доступность: MacOS,Windows, Linux-подобные ОС (Red Hat Linux, CentOS, Oracle Fedora, Scientific, Debian GNU, Ubuntu, SuSE, OpenSuSE и другие дистрибутивы и производные), BSD (FreeBSD, OpenBSD), Oracle Solaris.
Поддерживаемые типы данных в MySQL и PostgreSQL
MySQL
MySQL PostgreSQL
Как MySQL и PostgreSQL индексируют данные
Индексирование данных позволяет системам работать быстрее, без сильной нагрузки. У наших двух СУБД типы индексирования немного отличаются.
Типы индексирования MySQL
- Индексы в R-деревьях (например, для пространственных типов данных).
- Индексы в B-деревьях (INDEX, UNIQUE, PRIMARY KEY, FULLTEXT).
- Хэш-индексы и инвертированные списки при применении FULLTEXT.
Типы индексирования PostgreSQL
- Индексы в B-деревьях.
- Хэш-индексы.
- Индексы выражений (вычисляемые не из значений в столбцах, а из функций).
- Частичные индексы (упорядочивают информацию не из всей таблицы, а из ее части).
Поддержка пользователей в MySQL и PostgreSQL
При сравнении PostgreSQL и MySQL стоит отметить, что вокруг обеих СУБД сформировались крупные сообщества, где готовы помочь бесплатно советом, рекомендацией или кейсом из своей практики.
Поддержка пользователей MySQL
MySQL — это самая популярная СУБД с открытым исходным кодом, поэтому в Сети можно найти огромное количество опытных специалистов, готовых помочь в любой проблеме безвозмездно. Да, официальную документацию считают недостаточной, но существует также разные руководства, справочники и даже бесплатные книги по MySQL, с помощью которых можно самостоятельно разобраться в большинстве вопросов. Искать профессиональное сообщество по этой СУБД лучше всего на сайтах MySQL и Percona. Кроме того, существует версия MySQL Enterprise Edition, которая распространяется по подписке и, помимо нескольких полезных инструментов, включает круглосуточную техподдержку от Oracle.
Поддержка пользователей PostgreSQL
У этой полностью открытой СУБД сообщество менее обширное, но все же большое. Оно также оказывает разнообразную бесплатную поддержку пользователям и разработчикам на профильных форумах, а также через списки рассылки и по IRC. Но с учетом того, что экспертов по сложной PostgreSQL меньше, чем по простой MySQL, получить поддержку по ней несколько сложнее. Однако по PostgreSQL есть множество бесплатных книг и инструкций.
Поддержка пользователей MySQL
MySQL — это самая популярная СУБД с открытым исходным кодом, поэтому в Сети можно найти огромное количество опытных специалистов, готовых помочь в любой проблеме безвозмездно. Да, официальную документацию считают недостаточной, но существует также разные руководства, справочники и даже бесплатные книги по MySQL, с помощью которых можно самостоятельно разобраться в большинстве вопросов. Искать профессиональное сообщество по этой СУБД лучше всего на сайтах MySQL и Percona. Кроме того, существует версия MySQL Enterprise Edition, которая распространяется по подписке и, помимо нескольких полезных инструментов, включает круглосуточную техподдержку от Oracle.
Поддержка пользователей PostgreSQL
У этой полностью открытой СУБД сообщество менее обширное, но все же большое. Оно также оказывает разнообразную бесплатную поддержку пользователям и разработчикам на профильных форумах, а также через списки рассылки и по IRC. Но с учетом того, что экспертов по сложной PostgreSQL меньше, чем по простой MySQL, получить поддержку по ней несколько сложнее. Однако по PostgreSQL есть множество бесплатных книг и инструкций.
Разработка MySQL и PostgreSQL
Чем отличаются PostgreSQL от MySQL в отношение разработки обеих СУБД, так это тем, что развиваются они по-разному, хоть и имеют обе открытый исходный код. Этого не было заявлено официально, но пользователи считают, что компания Oracle, которая разрабатывает MySQL, намеренно не развивает СУБД активно, потому что хочет сохранить главное конкурентное преимущество системы — скорость. Кстати, создается довольно много форков MySQL. В отличие от нее разработчики PostgreSQL, а это совет нескольких компаний и независимые программисты, активно разрабатывают систему, наполняя ее все большим количеством функций.
Чем отличается программирование под каждую СУБД
Разница между MySQL и PostgreSQL в части программирования в трех вещах: наборах символов и строки по умолчанию, функциях IF и IFNULL и чувствительности к регистру.
Наборы символов и строки по умолчанию
В MySQL нужно преобразовать наборы символов и строки в кодировку UTF-8 (но не во всех версиях). PostgreSQL этого не требует и даже не допускает использования UTF-8.
Функции IF и IFNULL вместо CASE
В PostgreSQL вместо операторов IF и IFNULL надо использовать CASE. А в MySQL их вполне можно использовать.
Чувствительность к регистру
Для PostgreSQL регистр важен. Строки запросов должны точно совпадать с полями в БД. Если этого не будет, запрос не выполнится. Для MySQL регистр не важен, поэтому запросы со строками в БД не обязаны совпадать по регистру.
Наборы символов и строки по умолчанию
В MySQL нужно преобразовать наборы символов и строки в кодировку UTF-8 (но не во всех версиях). PostgreSQL этого не требует и даже не допускает использования UTF-8.
Функции IF и IFNULL вместо CASE
В PostgreSQL вместо операторов IF и IFNULL надо использовать CASE. А в MySQL их вполне можно использовать.
Чувствительность к регистру
Для PostgreSQL регистр важен. Строки запросов должны точно совпадать с полями в БД. Если этого не будет, запрос не выполнится. Для MySQL регистр не важен, поэтому запросы со строками в БД не обязаны совпадать по регистру.
Когда разработчики выбирают MySQL, а когда PostgreSQL
Основные преимущества MySQL для разработчиков
Это высокоэффективная и высоконадежная СУБД в web-среде. Ее стоит использовать для сайтов и веб-приложений, в которых надо выполнять простые транзакции, поскольку она работает быстро и стабильно с небольшими и средними объемами данных. MySQL стоит использовать, если предстоит проводить распределенные операции; если нужна легкая установка и настройка СУБД; если проект кастомный и возможны специфические отчеты; если часто нужно только чтение, то есть высокопараллельные операции без записи в базу данных.
Зачем использовать PostgreSQL?
Если в приоритете — целостность данных при выполнении непростых процедур. Она подходит для работы с очень большими наборами данных и в ситуациях со сложными операциями записи-чтения с одновременной валидацией данных (например, в машинном обучении).
- Надежность, благодаря частым обновлениям, касающимся функций и безопасности.
Поддерживает JSON. - Минимальное проведение операций для получения данных с диска.
- Удобная работа с кастомной разработкой (можно дописать базовый набор функций, создать интеграции с другими приложениями, делать специфические отчеты по запросам клиентов).
- Легкое масштабирование системы.
- Возможность установки дополнительных приложений для расширения функционала.
- Может функционировать с любым языком программирования.
- Легкая интеграция с CMS, CMF.
- Много вариантов настройки и оптимизации сервера MySQL через настройку переменных.
- Можно добавить графический интерфейс.
- Если использовать движок InnoDB, то доступно управление параллельным доступом через многоверсионность (MVCC).
- Поддерживает функцию управления параллелизмом нескольких версий.
Полная совместимость с SQL и ACID. - Удобство работы за счет разных индексов.
- Легкость миграции на другое решение.
- Поддерживает JSON и другие функции NoSQL.
- Надежность и целостность данных (например, в ней сложно разрушить таблицы).
- Есть возможность сохранения произведенных процедур, за счет чего возможно расширение опций.
- Можно разработать собственные плагины и использовать нестандартные функции, написанные на других языках программирования.
- Функции не ограничивают размер БД, даже если она исчисляется петабайтами.
- Высокая производительность и управляемость за счет MVCC.
- Поддержка отзывчивого сообщества.
Это высокоэффективная и высоконадежная СУБД в web-среде. Ее стоит использовать для сайтов и веб-приложений, в которых надо выполнять простые транзакции, поскольку она работает быстро и стабильно с небольшими и средними объемами данных. MySQL стоит использовать, если предстоит проводить распределенные операции; если нужна легкая установка и настройка СУБД; если проект кастомный и возможны специфические отчеты; если часто нужно только чтение, то есть высокопараллельные операции без записи в базу данных.
Зачем использовать PostgreSQL?
Если в приоритете — целостность данных при выполнении непростых процедур. Она подходит для работы с очень большими наборами данных и в ситуациях со сложными операциями записи-чтения с одновременной валидацией данных (например, в машинном обучении).
Недостатки MySQL
У этой СУБД профессионалы отмечают некорректность, нестабильность работы и сильную регрессию производительности при осуществлении репликаций БД. Также есть проблемы с транзакциями между таблицами разных подсистем хранения. Нет механизма, обеспечивающего блокировку во время транзакций. Совместимость с ACID только при использовании InnoDB и NDB. Поддерживает только некоторые функции SQL. не поддерживает другие функции NoSQL, хотя поддерживает JSON. Если планируется работа на SSD-дисках со скоростью примерно 20 Мбайт в секунду, то эта СУБД будет тормозить. Менять структуру данных тоже будет сложно. MySQL чувствительна к серверу с нестабильным поведением. Есть шанс сломать таблицы при неправильном завершении, тогда восстановить БД будет возможно только из полного бэкапа.
Также недостатком отмечают скудную официальную документацию.
Когда не стоит использовать MySQL
В высоконагруженных проектах, при работе с большими данными, в сложной аналитике. При многопоточных процессах. Если требуется многофункциональная система.
Также недостатком отмечают скудную официальную документацию.
Когда не стоит использовать MySQL
В высоконагруженных проектах, при работе с большими данными, в сложной аналитике. При многопоточных процессах. Если требуется многофункциональная система.
Недостатки PostgreSQL
В простых операциях чтения производительность этой СУБД уступает другим. Также недостатком можно считать сложность системы, ее настройки. Если у вас несложный проект с относительно небольшой базой данных, по которой вы не планируете проводить сложную аналитику, PostgreSQL лучше не использовать. А если вы планируете интегрировать ее с какими-то внешними компонентами, то готовьтесь к тому, что ваша кривая обучения будет высокой. Также у PostgreSQL нет надежного способа обновления до основных выпусков. Эта СУБД не для новичков. Может замедлять сервер, под нее сложно найти хостинг.
Когда не стоит использовать PostgreSQL
Когда важна скорость обработки данных. А также если у вас простые, ненагруженные проекты, в которых нет требований повышенной надёжности данных, поддержки ACID и т.д.
Когда не стоит использовать PostgreSQL
Когда важна скорость обработки данных. А также если у вас простые, ненагруженные проекты, в которых нет требований повышенной надёжности данных, поддержки ACID и т.д.
Какую же СУБД лучше выбрать: MySQL vs PostgreSQL?
Мы видим, что в разных ситуациях каждая из этих СУБД может подходить лучше другой. Различия MySQL и PostgreSQL довольно заметны, хотя во многом их функционал и схож.
Для сайтов и приложений хороша MySQL, поскольку она работает быстро и не перегружена функциями. Она хорошо работает в системах OLAP и OLTP. Подходит тем специалистам, которые не хотят или не имеют возможности долго настраивать СУБД «под себя». Ее легко устанавливать, поэтому она отлично подходит новичкам. Также подходит тем, кто планирует управлять данными структурного типа, размер которых не превышает 2 Гб. Под нее легко найти опытного администратора БД.
Для аналитики больших данных лучше подходит PostgreSQL. Она хорошо работает при выполнении сложных запросов, потому что у нее множество функций и полное соответствие стандарту SQL. Она хороша для чувствительных данных, к которым есть повышенные требования безопасности. PostgreSQL, тогда как MySQL.
Для сайтов и приложений хороша MySQL, поскольку она работает быстро и не перегружена функциями. Она хорошо работает в системах OLAP и OLTP. Подходит тем специалистам, которые не хотят или не имеют возможности долго настраивать СУБД «под себя». Ее легко устанавливать, поэтому она отлично подходит новичкам. Также подходит тем, кто планирует управлять данными структурного типа, размер которых не превышает 2 Гб. Под нее легко найти опытного администратора БД.
Для аналитики больших данных лучше подходит PostgreSQL. Она хорошо работает при выполнении сложных запросов, потому что у нее множество функций и полное соответствие стандарту SQL. Она хороша для чувствительных данных, к которым есть повышенные требования безопасности. PostgreSQL, тогда как MySQL.
Не пропустите последние новости. Подписывайтесь!