Некоторые интересные отличия PostgreSQL от MySQL

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

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

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

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

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

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

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

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

Особенности	PostgreSQL	MySQL
ANSI SQL совместимость	Близка к стандарту ANSI SQL	Следует некоторым стандартам ANSI SQL
Скорость работы	Медленнее	Быстрее
Вложенные селекты	Да	Нет
Транзакации	Да	Да, однако должен использоваться тип таблицы InnoDB
Ответ базы данных	Да	Да
Поддержка внешних ключей	Да	Нет
Представления	Да	Нет
Хранимые процедуры	Да	Нет
Триггеры	Да	Нет
Unions	Да	Нет
Полные Joins	Да	Нет
Ограничители целостности	Да	Нет
Поддержка Windows	Да	Да
Вакуум (очистка)	Да	Нет
ODBC	Да	Да
JDBC	Да	Да
Различные типы таблиц	Нет	Да

Когда использовать 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 (например, использование индексов).

Заключение

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

Открытое ПО соответствующее стандарту SQL - PostgreSQL - бесплатное ПО с открытым исходным кодом. Эта СУБД является очень мощной системой.

Большое сообщество - существует довольно большое сообщество в котором вы запросто найдёте ответы на свои вопросы

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

Расширения - существует возможность расширения функционала за счет сохранения своих процедур.

Объектность - PostrgreSQL это не только реляционная СУБД, но также и объектно-ориентированная с поддержкой наследования и много другого

Недостатки PostgreSql

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

Популярность - по своей природе, популярностью эта СУБД похвастаться не может, хотя и присутствует довольно большое сообщество.

Хостинг - в силу выше перечисленных факторов иногда довольно сложно найти хостинг с поддержкой этой СУБД.

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

Целостность данных - когда надежность и целостность данных - ваши требования, PostgreSQL будет, пожалуй, лучшим выбором

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

Интеграция - если в будущем вы планируете переход на платные СУБД, например Oracle, то сделать это с PostgreSQL будет довольно просто по сравнению с другими бесплатными СУБД

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

Когда не следует использовать PostgreSql

Скорость - если быстрое чтение для вас единственный фактор, то стоит присмотреться к другим СУБД

Простая настройка - если вам не нужна целостность данных, соответствие ACID или сложные структуры данных, то настройка PostgreSQL может изрядно потрепать вам нервы

Репликация - если вы не готовы потратить время и энергию на то, что мог бы с легкостью сделать MySQL, то наверное проще было бы на нем и остаться.

NoSql системы управления базами данных

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

Существует довольно много различных моделей и функциональных систем для NoSQL баз данных:

Хранилище ключ-значение - Redis, MemcacheDB и т.д. (обычно хранят данные в памяти)

Распределённое хранилище (Column-oriented) - Cassandra, HBase и т.д (предназначены для очень больших объёмов данных).

Документо-ориентированные СУБД - MongoDB, Couchbase и т.д. (предназначены для хранения иерархических структур данных - документов)

БД на основе графов - OrientDB, Neo4J и т.д.

Чтобы лучше понять чем отличаются все эти типы СУБД, рассмотрим их.

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

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

Какие особенности реляционных системы управления базами данных: с помощью них можно размещать данные в таблицах, связывая строки из разных таблиц. Для начала работы перед сохранением данных, необходимо создать таблицы определенного размера и указать тип данных для каждого столбца. Столбы – это поля данных, а сами данные хранятся в строках. MySQL vs Postgresql относятся к реляционным.

История разработки MySQL и PostgreSQL.

MySQL начал создаваться еще в 90-х. Внутренний выпуск произошел в 1995 году. Тогда разработкой MySQL занимались несколько компаний. Начиная с 2010 года компания Oracle владеет проектом MySQL и разрабатывает новые версии.

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

Особенности хранения данных.

В MySQL для хранения данных в таблицах используются различные движки. Движок не имеет влияния на синтаксис запросов и их выполнение. Имеется поддержка MyISAM, InnoDB, MEMORY, Berkeley DB. Их основное отличие в способе записи данных на диск и методов считывания. Как в базе данных MS SQL? PostgreSQL работает только на движке storage engine. Таблицы организованы в виде обьектов, а действия выполняются с помощью объективно ориентированных функций.

Стандарты SQL.

SQL – это стандартизированный язык выполнения запросов, который используется и MySQL и PostgreSQL. Этот стандарт имеет несколько версий и был разработан еще в 1986 году.

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

PostgreSQL поддерживает все новые стандарты SQL, из-за этого данный проект довольно сложный и не настолько популярный как MySQL.

Возможности обработки данных.

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

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

MySQL всегда был ориентирован на большую производительность, в то время как Postgresql был нацелен на большое количество настроек и стандартов. Но со временем эта ситуация поменялась и Postgre стал более производительным.

Для организации работы с базой данных в MySQL используется таблица InnoDB. А это значит, что MySQL будет значительно быстрее Postgre в случае использовании первичного ключа.

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

Поддерживаемые типы данных.

MySQL и Postgresql имеют похожий набор, который, конечно же, имеет свои отличия. В Postgre типы более разнообразны и есть свои типы полей для определенных видов данных, которых, например, нет в MySQL.

Будущее MySQL и Postgresql.

Эти проекты имеют открытый исходный код, но развиваются совсем по-разному. MySQL под руководством компании Oracle тормозит в развитии. Postgresql развивается группой программистов и несколькими компаниями. Новые версии выходят достаточно часто и имеют новые функции.

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

4595 раз(а) 1 Сегодня просмотрено раз(а)

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

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

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