Некоторые интересные отличия PostgreSQL от MySQL. Какую базу данных выбрать: MySQL vs PostgreSQL

Открытое ПО соответствующее стандарту 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 и т.д.

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

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

Чтобы лучше разобраться в СУБД, ознакомьтесь с .

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

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

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

Отношения и типы данных

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

Каждый элемент, формирующий запись, должен удовлетворять определённому типу данных (целое число, дата и т.д.). Различные РСУБД используют разные типы данные, которые не всегда взаимозаменяемы.

Такого рода ограничения обычны для реляционных баз данных. Фактически, они и формируют суть отношений.

Популярные РСУБД

В этой статье мы расскажем о 3 наиболее популярных РСУБД:

• SQLite: очень мощная встраиваемая РСУБД.
• MySQL: самая популярная и часто используемая РСУБД.
• PostgreSQL: самая продвинутая и гибкая РСУБД.

SQLite

SQLite - это изумительная библиотека, встраиваемая в приложение, которое её использует. Будучи файловой БД, она предоставляет отличный набор инструментов для более простой (в сравнении с серверными БД) обработки любых видов данных.

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

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

• NULL: NULL-значение.

• INTEGER: целое со знаком, хранящееся в 1, 2, 3, 4, 6, или 8 байтах.

• REAL: число с плавающей запятой, хранящееся в 8-байтовом формате IEEE.

• TEXT: текстовая строка с кодировкойUTF-8, UTF-16BE или UTF-16LE.

• BLOB: тип данных, хранящийся точно в таком же виде, в каком и был получен.

Note: для получения более подробной информации ознакомьтесь с документацией .

Преимущества

• Файловая: вся база данных хранится в одном файле, что облегчает перемещение.

• Стандартизированная: SQLite использует SQL; некоторые функции опущены (RIGHT OUTER JOIN или FOR EACH STATEMENT), однако, есть и некоторые новые.

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

Недостатки

• Отсутствие пользовательского управления: продвинутые БД предоставляют пользователям возможность управлять связями в таблицах в соответствии с привилегиями, но у SQLite такой функции нет.

• Невозможность дополнительной настройки: опять-таки, SQLite нельзя сделать более производительной, поковырявшись в настройках - так уж она устроена.

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

• Встроенные приложения: все портируемые не предназначенные для масштабирования приложения - например, локальные однопользовательские приложения, мобильные приложения или игры.

• Система доступа к дисковой памяти: в большинстве случаев приложения, часто производящие прямые операции чтения/записи на диск, можно перевести на SQLite для повышения производительности.

• Тестирование: отлично подойдёт для большинства приложений, частью функционала которых является тестирование бизнес-логики.

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

• Многопользовательские приложения: если вы работаете над приложением, доступом к БД в котором будут одновременно пользоваться несколько человек, лучше выбрать полнофункциональную РСУБД - например, MySQL.

• Приложения, записывающие большие объёмы данных: одним из ограничений SQLite являются операции записи. Эта РСУБД допускает единовременное исполнение лишь одной операции записи.

MySQL

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

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

• TINYINT: очень маленькое целое.

• SMALLINT: маленькое целое.

• MEDIUMINT: целое среднего размера.

• INT или INTEGER: целое нормального размера.

• BIGINT: большое целое.

• FLOAT: знаковое число с плавающей запятой одинарной точности.

• DOUBLE, DOUBLE PRECISION, REAL: знаковое число с плавающей запятой двойной точности.

• DECIMAL, NUMERIC: знаковое число с плавающей запятой.

• DATE: дата.

• DATETIME: комбинация даты и времени.

• TIMESTAMP: отметка времени.

• TIME: время.

• YEAR: год в формате YY или YYYY.

• CHAR: строка фиксированного размера, дополняемая справа пробелами до максимальной длины.

• VARCHAR: строка переменной длины.

• TINYBLOB, TINYTEXT: BLOB- или TEXT-столбец длиной максимум 255 (2^8 — 1) символов.

• BLOB, TEXT: BLOB- или TEXT-столбец длиной максимум 65535 (2^16 — 1) символов.

• MEDIUMBLOB, MEDIUMTEXT: BLOB- или TEXT-столбец длиной максимум 16777215 (2^24 — 1) символов.

• LONGBLOB, LONGTEXT: BLOB- или TEXT-столбец длиной максимум 4294967295 (2^32 — 1) символов.

• ENUM: перечисление.

• SET: множества.

Преимущества

• Простота: MySQL легко устанавливается. Существует много сторонних инструментов, включая визуальные, облегчающих начало работы с БД.

• Много функций: MySQL поддерживает большую часть функционала SQL.

• Безопасность: в MySQL встроено много функций безопасности.

• Мощность и масштабируемость: MySQL может работать с действительно большими объёмами данных, и неплохо походит для масштабируемых приложений.

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

Недостатки

• Известные ограничения: по определению, MySQL не может сделать всё, что угодно, и в ней присутствуют определённые ограничения функциональности.

• Вопросы надёжности: некоторые операции реализованы менее надёжно, чем в других РСУБД.

• Застой в разработке: хотя MySQL и является open-source продуктом, работа над ней сильно заторможена. Тем не менее, существует несколько БД, полностью основанных на MySQL (например, MariaDB). Кстати, подробнее о родстве MariaDB и MySQL можно из нашего с создателем обеих РСУБД - Джеймсом Боттомли.

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

• Распределённые операции: когда вам нужен функционал бо́льший, чем может предоставить SQLite, стоит использовать MySQL.

• Высокая безопасность: функции безопасности MySQL предоставляют надёжную защиту доступа и использования данных.

• Веб-сайты и приложения: большая часть веб-ресурсов вполне может работать с MySQL, несмотря на ограничения. Этот инструмент весьма гибок и прост в обращении, что только на руку в длительной перспективе.

• Кастомные решения: если вы работаете над очень специфичным продуктом, MySQL подстроится под ваши потребности благодаря широкому спектру настроек и режимов работы.

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

• SQL-совместимость: поскольку MySQL не пытается полностью реализовать стандарты SQL, она не является полностью совместимой с SQL. Из-за этого могут возникнуть проблемы при интеграции с другими РСУБД.

• Конкурентность: хотя MySQL неплохо справляется с операциями чтения, одновременные операции чтения-записи могут вызвать проблемы.

• Недостаток функций: в зависимости от выбора движка MySQL может недоставать некоторых функций.

PostgreSQL

PostgreSQL - это самая продвинутая РСУБД, ориентирующаяся в первую очередь на полное соответствие стандартам и расширяемость. PostgreSQL, или Postgres, пытается полностью соответствовать SQL-стандартам ANSI/ISO.

PostgreSQL отличается от других РСУБД тем, что обладает объектно-ориентированным функционалом, в том числе полной поддержкой концепта ACID (Atomicity, Consistency, Isolation, Durability).

Будучи основанным на мощной технологии Postgres отлично справляется с одновременной обработкой нескольких заданий. Поддержка конкурентности реализована с использованием MVCC (Multiversion Concurrency Control), что также обеспечивает совместимость с ACID.

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

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

• bigint: знаковое 8-байтное целое.

• bigserial: автоматически инкрементируемое 8-битное целое.

• bit [(n)]: битовая строка фиксированной длины.

• bit varying [(n)]: битовая строка переменной длины.

• boolean: булевская величина.

• box: прямоугольник на плоскости.

• bytea: бинарные данные.

• character varying [(n)]: строка символов фиксированной длины.

• character [(n)]:

• cidr: сетевой адрес IPv4 или IPv6.

• circle: круг на плоскости.

• date: календарная дата.

• double precision: число с плавающей запятой двойной точности.

• inet: адрес хоста IPv4 или IPv6.

• integer: знаковое 4-байтное целое.

• interval [(p)]: временной промежуток.

• line: бесконечная прямая на плоскости.

• lseg: отрезок на плоскости.

• macaddr: MAC-адрес.

• money: денежная величина.

• path: геометрический путь на плоскости.

• point: геометрическая точка на плоскости.

• polygon: многоугольник на плоскости.

• real: число с плавающей запятой одинарной точности.

• smallint: знаковое 2-байтное целое.

• serial: автоматически инкрементируемое 4-битное целое.

• text: строка символов переменной длины.

• time [(p)] : время суток (без часового пояса).

• time [(p)] with time zone: время суток (с часовым поясом).

• timestamp [(p)] : дата ивремя (без часового пояса).

• timestamp [(p)] with time zone: дата и время (с часовым поясом).

• tsquery: запрос текстового поиска.

• tsvector: документ текстового поиска.

• txid_snapshot: снэпшот ID пользовательской транзакции.

• uuid: уникальный идентификатор.

• xml: XML-данные.

Преимущества

• Полная SQL-совместимость .

• Сообщество: PostgreSQL поддерживается опытным сообществом 24/7.

• Поддержка сторонними организациями: несмотря на очень продвинутые функции, PostgreSQL используется в многих инструментах, связанных с РСУБД.

• Расширяемость: PostgreSQL можно программно расширить за счёт хранимых процедур.

• Объектно-ориентированность: PostgreSQL - не только реляционная, но и объектно-ориентированная СУБД.

Недостатки

• Производительность: В простых операциях чтения PostgreSQL может уступать своим соперникам.

• Популярность: из-за своей сложности инструмент не очень популярен.

• Хостинг: из-за вышеперечисленных факторов проблематично найти подходящего провайдера.

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

• Целостность данных: если приоритет стоит на надёжность и целостность данных, PostgreSQL - лучший выбор.

• Сложные процедуры: если ваша БД должна выполнять сложные процедуры, стоит выбрать PostgreSQL в силу её расширяемости.

• Интеграция: если в будущем вам предстоит перемещать всю базу на другое решение, меньше всего проблем возникнет с PostgreSQL.

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

• Скорость: если всё, что нужно - это быстрые операции чтения, не стоит использовать PostgreSQL.

• Простые ситуации: если вам не требуется повышенная надёжность, поддержка ACID и всё такое, использование PostgreSQL - это стрельба из пушки по мухам.

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

Пример превосходства PosgreSQL над MySQL

Например, создадим таблицу и для одного из полей зададим тип numeric(4, 2) - четыре разряда под хранение всего числа и два разряда после запятой. А потом попытаемся вставить число, которое не соответствует описанию. И что вы думаете? Нет проблем!

В ручном режиме мы увидим (возможно) предупреждение (1 warning) попытаемся догадаться, о чем оно и восстановить прежнее значение. Но предупреждение легко пропустить. Если вы пишете приложение, имеющее дело с хранением финансовых данных, такие ошибки будут стоить очень дорого.

PostgreSQL бережет ваши данные и ведет себя более серьезно:

INSERT INTO DATA VALUES (1 , 1234.5678 ) ; ERROR: NUMERIC FIELD overflow DETAIL: A FIELD WITH PRECISION 4 , scale 2 must round TO an absolute VALUE less than 10 ^2 .

Нам просто не дадут сделать ошибку. Более того, в сообщении будет содержаться детализация: какое именно ограничение мы нарушили и какой максимальный размер числа задан. Так-то вот.

А теперь немного мистики. Помните, мы описали, что поле id для MySQL не должно быть NULL? Пробуем:

Удивительно, но несмотря на явный запрет (NOT NULL), СУБД MySQL разрешила-таки обновить поле, причем установила его значение в 0, хотя о нуле мы вообще ничего не говорили. 0 и NULL - совершенно разные сущности. 0 - это число. NULL - это особый индикатор, говорящий нам о том, что мы не знаем, какое число содержится в этом поле. И совсем необязательно это 0. Таким образом, СУБД опять приписала от себя случайное значение в нашу базу. Если в вашем приложении будет такая же ошибка, то MySQL поможет этой ошибке долго оставаться необнаруженной. А что же PostgreSQL? Тут всё чётко.

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

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

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

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

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

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

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

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

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

Заключение

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