чем постгрес лучше mysql

MySQL против PostgreSQL

Вот – сравнение баз данных 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:

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

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

Cуществует немало разработчиков, которые предпочитают богатые функциональные возможности SQL команд PostgreSQL. Одно из наиболее ощутимых различий между MySQL и PostgreSQL – невозможность создания вложенных подзапросов (селектов) в MySQL. PostgreSQL соответствует многими SQL стандартам ANSI, таким образом позволяя создание сложных команд SQL.

Несколько причин использовать PostgreSQL:

Заключение

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

Источник

SQLite, MySQL и PostgreSQL: сравниваем популярные реляционные СУБД

Авторизуйтесь

SQLite, MySQL и PostgreSQL: сравниваем популярные реляционные СУБД

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

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

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

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

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

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

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

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

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

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

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

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

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

SQLite

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

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

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

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

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

Недостатки

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

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

MySQL

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

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

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

Недостатки

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

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

PostgreSQL

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

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

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

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

Источник

Сравнение MySQL и PostgreSQL с точки зрения разработчика

Аннотация

В статье представлен сравнительный анализ двух бесплатных свободных систем управления базами данных (СУБД): MySQL и PostgreSQL. Анализ ведётся с точки зрения использования этих СУБД в мало- и средненагруженных приложениях. Не рассматриваются вопросы масштабирования и оптимизации под проекты с многомиллионными аудиториями. Не приводятся данные сравнения производительности. Рассматриваются MySQL 5.1 и PostgreSQL 8.3.

Типы данных

Первое с чем приходится сталкиваться разработчику — это доступные типы данных. Проведём сравнение доступных типов данных.

Целые числа и числа с плавающей точкой

Я не буду указывать диапазоны возможных значений, однако укажу информационную ёмкость в байтах.

Как видно из таблицы, типы данных практически идентичны для двух СУБД. Различия состоят в том, что MySQL позволяет более детально использовать доступную память, но при этом работа с числами в символьном представлении ограничена 65 цифрами. Я не вижу ни одного практического применения числам с таким количеством знаков, потому можно считать что возможности MySQL и PostgreSQL в данном разделе идентичны.

Строки и данные

Размеры указываются в байтах. Не забывайте, что для одного символа UTF-8 может использоваться от 1 до 4 байт.

* ограничение вызвано максимальной длиной строки, равной 65535 байтам, но в реальности максимальная длинна гораздо меньше.

Из таблицы видно, что по ёмкости строковые типы данных в двух СУБД практически не различаются, и снова MySQL позволяет более детально контролировать формат хранения данных на жёстком диске. Однако эта гибкость MySQL вводит две небольшие проблемы на этапе проектирования: сумятицу в типах и размерах данных.

Чтобы не быть голословным приведу пример — хранение данных пользователя. Предположим, что нам потребовалось хранить о пользователе не только его имя, фамилию и отчество, но адрес и телефоны, да мало ли что мы можем предложить хранить пользователю в своём профиле. С точки зрения SQL для этого должны использоваться типы CHAR и VARCHAR. И вот тут в MySQL приходится решать какая максимальная длинна у фамилии, какая максимальная длинна у имени, какая максимальная длинна у адреса, ибо на всё про всё дано 65535 байт. В то же время в PostgreSQL мы просто указываем в качестве типа для всех столбцов таблицы VARCHAR, куда мы в случае необходимости можем уложить гораздо больше данных, чем нам позволяет MySQL. (Попрошу не предлагать использовать TEXT в MySQL для этих целей.)

Дата и время

Типы даты и времени для двух баз практически идентичны и проблем как правило не вызывают.

Нестандартные типы

Для желающих PostgreSQL предлагает целую группу типов данных для работы, которые напрочь отстутствуют в MySQL: массивы, структуры, типы для хранения IP и MAC адресов, и даже типы для хранения параметров геометрических фигур. Желающие могут самостоятельно ознакомиться с типами данных PostgreSQL.
UPD В MySQL, оказывается, есть типы данных для геометрических фигур подробности в комментариях

Выводы по типам данных

1) Типы данных, предлагаемые двумя СУБД, с функциональной точки зрения идентичны.
2) При помощи этих типов можно хранить данные в любой из СУБД, однако в MySQL разработчик вынужден на самом начальном этапе проектирования искуственно ограничивать длинну строковых данных, что не сказывается положительно на удобстве пользования системой.
3) Использование нестандартных типов в PostgreSQL позволяет довольно сильно упростить разработку, однако усложнит переход на другую СУБД.
4) MySQL позволяет точно контролировать структуру хранимых данных, однако при этом жертвуется удобством разработчика.

Возможности управления данными

Здесь я хочу сравнить две СУБД с точки зрения дополнительного функционала предлагаемого разработчику. Часть этого функционала включена в стандарт SQL.

Хранимые процедуры

Начнём с простейшего — хранимые процедуры. Грубо говоря, в MySQL вообще нет функционала хранимых процедур. Если выражаться более точно, то вообще они есть, но довольно условны. Так, например, при включённой репликации хранимые процедуры могут быть только readonly. Так что довольно популярная схема ограничения прав пользователя через хранимые процедуры вовсе не реализуема на MySQL.

Индексы и ключи

На этом фронте MySQL тоже не блещет своими возможностями. Ограничение в 1000 байт на размер ключа — куда это годится? Допустим, я разрешаю своим пользователям создавать учётные записи на любом языке (UTF-8). В качестве максимальной длинны логина я выбираю 512 символов. Так как логины должны быть уникальными, прихожу к выводу, что нужно наложить уникальный ключ на столбец, и, как выясняется, не могу, ибо ключ не вписывается в 1000 байт. Приходится идти на уступки и делать уникальность только по первым 333 символам. Кто не верит, может самостоятельно посмотреть на результат create table t( t varchar(512), key(t)) character set = utf8.
PostgreSQL такими комплексами не страдает, а просто делает уникальный ключ необходимого размера.

Проверка данных на этапе добавления

В стандарте SQL была предусмотрена инструкция CHECK, которая задаёт выражение, которому должны удовлетворять данные добавляемые в таблицу. В руководстве MySQL об этой инструкции сказано предельно просто «The CHECK clause is parsed but ignored by all storage engines.» Больше добавить мне к этому нечего, в постгре, как и ожидалось, всё в порядке.

Транзакции и внешние ключи

По умолчанию в MySQL для таблиц используется движок MyISAM, который не поддерживает ни транзакций ни внешних ключей. Это сложилось исторически и у такого подхода есть оправдание, если рассматривать работу с БД с точки производительности. Можно просто заметить, что, если вам нужны транзакции и внешние ключи, то использование storage engine InnoDB обязательно. Естественно в PostgreSQL транзакции и вешние ключи полностью функциональны.

Выводы

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

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

Источник

Чем PostgreSQL лучше других SQL баз данных с открытым исходным кодом. Часть 1

Сегодня давайте поговорим о преимуществах Postgres перед другими системами с открытым кодом. Эту тему мы обязательно раскроем более подробно на PG Day’16 Russia, до которой осталось всего два месяца.

Возможно, вы спрашиваете себя: «Почему PostgreSQL?» Ведь есть и другие варианты реляционных баз данных с открытым исходным кодом (в рамках этой статьи мы рассматривали MySQL, MariaDB и Firebird), так что же Постгрес может предложить такого, чего нет у них? В слогане PostgreSQL заявляется, что это «Самая продвинутая база данных с открытым исходным кодом в мире». Мы приведем несколько причин, почему Постгрес делает такие заявления.

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

Модель данных

PostgreSQL не просто реляционная, а объектно-реляционная СУБД. Это даёт ему некоторые преимущества над другими SQL базами данных с открытым исходным кодом, такими как MySQL, MariaDB и Firebird.

Фундаментальная характеристика объектно-реляционной базы данных — это поддержка пользовательских объектов и их поведения, включая типы данных, функции, операции, домены и индексы. Это делает Постгрес невероятно гибким и надежным. Среди прочего, он умеет создавать, хранить и извлекать сложные структуры данных. В некоторых примерах ниже вы увидите вложенные и составные конструкции, которые не поддерживаются стандартными РСУБД.

Структуры и типы данных

Существует обширный список типов данных, которые поддерживает Постгрес. Кроме числовых, с плавающей точкой, текстовых, булевых и других ожидаемых типов данных (а также множества их вариаций), PostgreSQL может похвастаться поддержкой uuid, денежного, перечисляемого, геометрического, бинарного типов, сетевых адресов, битовых строк, текстового поиска, xml, json, массивов, композитных типов и диапазонов, а также некоторых внутренних типов для идентификации объектов и местоположения логов. Справедливости ради стоит сказать, что MySQL, MariaDB и Firebird тоже имеют некоторые из этих типов данных, но только Постгрес поддерживает их все.

Давайте рассмотрим подробнее некоторые из них:

Сетевые адреса

У MySQL и MariaDB тоже есть INET функции для конвертации сетевых адресов, но они не предоставляют типы данных для внутреннего хранения сетевых адресов. У Firebird тоже нет типов для хранения сетевых адресов.

Многомерные массивы

Поскольку Постгрес — это объектно-реляционная база данных, массивы значений могут храниться для большинства существующих типов данных. Сделать это можно путём добавления квадратных скобок к спецификации типа данных для столбца или с помощью выражения ARRAY. Размер массива может быть задан, но это необязательно. Давайте рассмотрим меню праздничного пикника для демонстрации использования массивов:

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

Геометрические данные

Геоданные быстро становятся основным требованием для многих приложений. PostgreSQL уже давно поддерживает множество геометрических типов данных, таких как точки, линии, круги и многоугольники. Один из этих типов – PATH, он состоит из множества последовательно расположенных точек и может быть открытым (начальная и конечная точки не связаны) или закрытым (начальная и конечная точки связаны). Давайте рассмотрим в качестве примера туристическую тропу. В данном случае туристическая тропа — это петля, поэтому начальная и конечная точки связаны, и, значит, мой путь является закрытым. Круглые скобки вокруг набора координат указывают на закрытый путь, а квадратные — на открытый.

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

Заметьте, что в MySQL 5.7.8 и в MariaDB, начиная с версии 5.3.3, были добавлены расширения типов данных для поддержки стандарта географической информации OpenGIS. Эта версия MySQL и последующие версии MariaDB предлагают хранение типов данных, аналогичное штатным геоданным Постгреса. Тем не менее, в MySQL и MariaDB значения данных сначала должны быть сконвертированы в геометрический формат простыми командами перед тем, как будут вставлены в таблицу. Firebird на данный момент не поддерживает геометрические типы данных.

Поддержка JSON

Поддержка JSON в PostgreSQL позволяет вам перейти к хранению schema-less данных в SQL базе данных. Это может быть полезно, когда структура данных требует определённой гибкости: например, если в процессе разработки структура всё ещё меняется или неизвестно, какие поля будет содержать объект данных.

Тип данных JSON обеспечивает проверку корректности JSON, который позволяет использовать специализированные JSON операторы и функции, встроенные в Постгрес для выполнения запросов и манипулирования данными. Также доступен тип JSONB — двоичная разновидность формата JSON, у которой пробелы удаляются, сортировка объектов не сохраняется, вместо этого они хранятся наиболее оптимальным образом, и сохраняется только последнее значение для ключей-дубликатов. JSONB обычно является предпочтительным форматом, поскольку требует меньше места для объектов, может быть проиндексирован и обрабатывается быстрее, так как не требует повторного синтаксического анализа.

В MySQL 5.7.8 и MariaDB 10.0.1 была добавлена поддержка встроенных объектов JSON. Но, хотя существует множество функций и операторов для JSON, которые теперь доступны в этих базах данных, они не индексируются так, как JSONB в PostgreSQL. Firebird пока что не присоединился к тренду и поддерживает объекты JSON только в виде текста.

Создание нового типа

Если вдруг так случится, что обширного списка типов данных Постгреса вам окажется недостаточно, вы можете использовать команду CREATE TYPE, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:

Поскольку они не являются объектно-реляционными, MySQL, MariaDB и Firebird не предоставляют такую мощную функциональность.

Размеры данных

PostgreSQL может обрабатывать много данных. Текущие опубликованные ограничения перечислены ниже:

В Compose [прим. пер.: организация, в которой трудится автор оригинальной статьи] мы автоматически масштабируем вашу инсталляцию, чтобы вам не приходилось волноваться о росте количества данных. Но, как известно любому администратору баз данных, стоит с опаской относиться к слишком большим и неограниченным возможностям. Мы советуем руководствоваться здравым смыслом при создании таблиц и добавлении индексов.

Для сравнения, MySQL и MariaDB печально известны ограничением размера строк в 65 535 байт. Firebird также предлагает всего лишь 64Кб в качестве максимального размера строки. Обычно объём данных ограничивается максимальным размером файлов операционной системы. Поскольку PostgreSQL умеет хранить табличные данные в множестве файлов меньшего размера, он может обойти это ограничение. Но стоит отметить, что слишком большое количество файлов может негативно сказаться на производительности. MySQL и MariaDB поддерживают большее количество столбцов в таблице (до 4,096 в зависимости от типа данных) и большие индивидуальные размеры таблицы, чем PostgreSQL, но необходимость превысить существующие ограничения Постгреса возникает лишь в крайне редких случаях.

Целостность данных

Постгрес стремится соответствовать стандарту ANSI-SQL:2008, отвечает требованиям ACID (атомарность, согласованность, изолированность и надежность) и известен своей ссылочной и транзакционной целостностью. Первичные ключи, ограничивающие и каскадные внешние ключи, уникальные ограничения, ограничения NOT NULL, проверочные ограничения и другие функции обеспечения целостности данных дают уверенность, что только корректные данные будут сохранены.

MySQL и MariaDB больше работают на то, чтобы соответствовать стандарту SQL с движками таблиц InnoDB/XtraDB. Теперь они предлагают опцию STRICT с использованием режимов SQL, которая устанавливает проверки корректности используемых данных. Несмотря на это, в зависимости от того, какой режим вы используете, недостоверные и даже урезанные без вашего ведома данные могут быть вставлены или созданы при обновлении. Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения. Кроме того, у них существует множество особенностей в отношении ограничений ссылочной целостности по внешним ключам. В дополнение к вышесказанному, целостность данных может существенно пострадать в зависимости от выбранного движка хранения. MySQL (и fork MariaDB) не делают секрета из того, что променяли целостность и соответствие стандартам на скорость и эффективность.

Подводя итоги

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

Если вам кажется, что PostgreSQL не соответствует вашим потребностям, или вы предпочитаете “стрелять от бедра”, тогда вам стоит обратить внимание на NoSQL базы данных, которые мы предлагаем в Compose, или подумать о других SQL базах данных, которые мы упоминали. У каждой из них есть свои преимущества. Compose твёрдо уверен, что очень важно выбрать правильную базу данных для конкретной задачи… иногда это означает, что нужно выбрать несколько баз данных!

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

Источник