Выбор между MySQL и PostgreSQL — это решение, которое должен принять каждый разработчик веб-приложений, который выбирает между различными Open-Source СУБД. Оба решения проверены временем и оба заслуживают пристального внимания. СУБД MySQL задумывалась как более быстрая но менее функциональная, в то время как разработчики PostgreSQL сосредоточились на большем количестве функционально полезных примочек чтобы как можно ближе соответствовать стандартам Oracle. MySQL очень популярен среди Web разработчиков по причине его высокой скорости и простоты использования. PosgreSQL менее популярен, так как удобен и просто только для тех разработчиков, которые ранее использовали Oracle или другие похожие базы данных. Но все скоростные характеристики MySQL по сравнению с PostgreSQL постепенно таят, так как с каждым обновлением PostgreSQL заметно прибавляет в скорости. Теперь рассмотрим эти две СУБД более пристально.

Архитектура

PostgreSQL уникальная СУБД с единым сервером хранения данных (СХД). MySQL имеет два слоя в своей архитектуре. Первый слой — это набор серверов хранения данных. По этому при сравнении обычно указывают какой именно сервер хранения данных MySQL использовался. Каждый сервер отличают и параметры быстродейстсвия и его функциональные возможности. Самый распространенный из них — InnoDB. Он отличается от остальных полной поддержкой модели ACID и высокой производительностью. Прямым конкурентом InnoDB считается MyISAM, который годится для обработки и хранения сравнительно небольших объемов данных, которые преимущественно считываются из БД, а не записываются. А также когда не критично отсутствие ACID. Приложения, которые работают с MySQL, могут одновременно использовать несколько СХД для обеспечения наилучшего набора функциональности и производительности.

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

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

Ориентированность СУБД

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

Скоростные характеристики

PostgreSQL

PostgreSQL предоставляет функционал, который может увеличить производительность для некоторых видов SQL запросов. Из этих функций стоит выделить:
— частичное индексирование;
— сжатие данных;
— объем данных, хранимых в оперативной памяти;
— кэш запросов;

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

PostgreSQL поддерживает только один единственный СХД. Он входит в стандартную поставку PostgreSQL.

MySQL:ядро

MySQL 5.1 поддерживает 9 различных СХД:

1. MyISAM
2. InnoDB
3. NDB Cluster
4. MERGE
5. MEMORY (HEAP)
6. FEDERATED
7. ARCHIVE
8. BLACKHOLE

Однако, Federated и Blackhole не являются серверами «хранения» данных (например, Blackhole ничего не хранит). InnoDB разработан сторонней компанией InnoBase, которая была основана компанией Oracle. InnoDB поддерживает транзакции и занимает лидирующее место из всех поставляемых с MySQL серверов хранения данных.

MySQL планирует представить новые СХД Maria и Falcon в предстоящей версии 6.x. На них возложена задача заменить существующие движки MyISAM и InnoDB соответственно.

Существуют и другие СХД, разработанные сторонними компаниями и группами разработчиков. Наиболее популярные из них:

• SolidDB
• NitroEDB
• BrightHouse

MySQL MyISAM имеет примитивный кэш, который перед выполнением каждого запроса на выборку сверяет простым сравнением строку запроса с предыдущими выполненными запросами и если есть совпадение то выдает ранее выбранный результат. Данный подход годится для систем, где подавляющей операцией является считывание данных, потому как только любая из таблиц изменит свое состояние (INSERT, UPDATE, DELETE) весь кэш очищается и соответственно теряется преимущество в производительности.

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

MySQL также поддерживает сетевой протокол сжатия данных, который включается и выключается на стороне клиента (если конечно позволяет сервер). Данная функция сжимает все данные которые идут от сервера к клиенту.

MySQL:MyISAM

MyISAM это традиционный СХД для MySQL и предлагает лучшее соотношение производительности и функциональности для баз данных предназначенных преимущественно для выборки данных (select). MySQL MyISAM обрабатывает запросы на выборку быстрее чем PostgreSQL, если речь идет о простых запросах. MyISAM не поддерживает транзакций и внешних ключей.

MyISAM поддерживает лидирующий префикс сжатия для ключей и сжатых таблиц предназначенных только для чтения.

Системный таблицы MySQL всегда используют СХД MyISAM. Это сделано для того, чтобы исключить даже потенциальную возможность потери данных.

MySQL:InnoDB

InnoDB это полно функциональная ACID транзакционная система (сервер) хранения данных (СХД), которая использует технологию MVCC. Это хороший выбор для современных приложений MySQL.

InnoDB хранит данные с первичным ключами, так что поиск по первичным ключам происходит очень быстро. Данный подход очень полезен для физической оптимизации БД. В случае когда он не применим и дает низкую производительность, то явно его можно не использовать и тогда система создаст невидимый внутренний первичный ключ типа Integer(15) и будет проводить индексацию по нему.

InnoDB автоматически создает хэш индексы когда обрабатывает запросы на выборку (SELECT). Эта функция может быть отключена при необходимости. Некоторые процессы работают быстрее без этой функции.

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

Если InnoDB установлен как плагин к MySQL 5.1, он поддерживает сжатие таблиц налету. Вы можете использовать атрибуты ROW_FORMAT=COMPRESSED или KEY_BLOCK_SIZE в запросах CREATE TABLE и ALTER TABLE, чтобы дать команду InnoDB сжать каждую страницу в 1K, 2K, 4K, 8K или 16K байт.

Innobase Oy, это компания, которая занимается разработкой InnoDB. Она была куплена компанией Oracle в октябре 2005 года. С тех пор InnoDB все больше и больше заимствует у СУБД Oracle. А теперь, с покупкой компании MySQL компанией SUN эти и без того тесные взаимоотношения между Oracle и MySQL стали еще крепче. MySQL выходит на качественно новый уровень возможностей.

MySQL:NDB Cluster

NDB это высокопроизводительный преимущественно располагающиеся в Оперативной Памяти (RAM) СХД. Не индексируемые атрибуты могут сохраняться на жестком диске. Данные и логи также периодически синхронизируются на жесткий диск, чтобы избежать потери данных в случае внезапного «падения» кластера. NDB преимущественно используется в сфере телекоммуникаций, где аптайм и производительность в реальном масштабе времени критичны. NDB прозрачно собирает данные в фрагменты которые регулярно доставляет во все участки кластера. NDB использует внутреннюю синхронную репликацию чтобы записи были распространенны по меньшей мере на двух участках кластера прежде чем совершать фиксацию на жесткий диск. NDB также поддерживает автоматическое восстановление «упавших» элементов кластера (нодов). Из слабых сторон NDB стоит отметить очень плохую поддержку сложных запросов с использованием операторов JOIN.

В PostgreSQL похожих решений на данный момент просто нет.

MySQL:Archive

MySQL поддерживает сжатие на лету с версии 5.0, когда в дистрибутив попал СХД ARCHIVE. Archive — это СХД, который позволяет делать одновременно только 1 запись и множество чтений. Разработан специально для архивных баз данных, где запись в БД осуществляется гораздо реже и преимущественно одним источником данных. Сила сжатия достигает 90%. Archive не поддерживает индексы. В версии MySQL 5.1 Archive может работать с несколькими разделами (партициями).

MySQL:Falcon

Falcon это ACID транзакционный СХД, который был разработан самой MySQL. В данный момент он находится в зачаточном состоянии и доступен для тестирования в бета-ветке MySQL 6.0. Falcon поддерживает сжатие данных налету. Данные, сохраненные в таблицах Falcon, сжимаются на жестком диске, но в оперативной памяти хранятся не в сжатом ввиде. Сжатие происходит автоматически, когда данные синхронизируются на жесткий диск.

MySQL:Maria

Maria это ACID СХД, который был разработан Монти Вайденисом (Monty Widenius) и командой разработчиков MySQL.

Мультпроцессность

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

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

Ассинхронный Ввод/Вывод

PostgreSQL полностью поддерживает асинхронный API для использования в клиентских приложениях. Данная возможность позволяет увеличить производительность в некоторых случаях до 40%. В MySQL отсутствует поддержка асинхронного режима. Но существует несколько драйверов, которые предоставляют эту возможность. Например MySQL драйверы для perl и ruby.

COUNT(*)

Транзакционные версионные СУБД, которые построены на модели MVCC, например такие как PostgreSQL и InnoDB, выполняют COUNT(*) очень медленно в сравнении с не транзакционными СХД или транзакционными не версионными СХД, например такими как MyISAM. MyISAM в MySQL использует сканирование индексов для обработки COUNT(*) и также кэширует полученный результат, данный подход гораздо эффективнее. PostgreSQL и InnoDB требуют полного сканирования таблицы на предмет учета всех видимых полей. MVCC-совместимые СХД выполняют операцию COUNT(*) данным способом потому, что MVCC сохраняет информацию о видимости или не видимости транзакции в данных самой записи (ROW). Во всех MVCC-совместимых СХД кэширование результатов операции COUNT(*) приведет к возврату некорректных данных. PostgreSQL Count() работает медленнее чем COUNT(*) в InnoDB.

Тесты производительности

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

Модель ACID

ACID следует понимать как Atomicity, Consistency, Isolation и Durability (Валентность, Последовательность, Изоляция и Длительность). Данная модель используется для обеспечения целостности данных средствами СУБД. Многие СУБД достигают соглашений ACID путем использования транзакций.

PostgreSQL и MySQL использующий InnoDB, Cluster и Falcon СХД полностью соответствуют всем принципам и канонам модели ACID.
Возможности

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

Простота использования

Готча (Gotcha) — это функция или функции которые работают так как описаны но не так как ожидалось (http://sql-info.de/mysql/gotchas.html). Поклонники PostgreSQL настаивают на том, что MySQL имеет больше готчей чем PostgreSQL.

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

Insert Ignore / Replace

MySQL поддерживает операторы ‘INSERT IGNORE’ и ‘REPLACE’ которые вставляют запись если ее не существует и ничего не делают в другом случае или заменяют текущую запись соответственно.

PostgreSQL не поддерживает ничего из выше перечисленного и советует использовать сохраненные процедуры для достижения такого эффекта. Однако существует одно «НО». В одно и тоже время может быть вставлено только одно значение. Данное условие накладывает серьезные ограничения на производительность и вызывает определенные сложности. INSERT IGNORE и REPLACE обрабатывают вставку нескольких значений и их запись симпатичнее чем процедура.

Еше одна полезная запись в MySQL: INSERT … ON DUPLICATE UPDATE также напрочь отсутствует в PostgreSQL и для реализации своими руками требует написания сохраненных процедур, которые могут обрабатывать только 1 запись в один момент времени.

Ограничители

Обе СУБД PostgreSQL и MySQL поддерживают ограничители Not-Null, Unique, Primary Key и Foreign Key. Однако MySQL не поддерживает ограничение Check когда PostgreSQL поддерживает его уже довольно продолжительное время.

Таблицы InnoDB поддерживают проверки внешних ключей. Для остальных СХД MySQL разбирает и игнорирует FOREIGN KEY и REFERENCES в директиве CREATE TABLE.

Для движков MySQL не поддерживающих внешние ключи могут быть применены тригеры.

Значения по умолчанию

PostgreSQL позволяет для колонок использовать в качестве значения по умолчанию любую функцию помеченную как IMMUTABLE или STABLE. В MySQL, на данный момент, лишь функция Now() может быть использована как значение по умолчанию для колонки.

Сохраненные процедуры

И PostgreSQL и MySQL поддерживают сохраненные процедуры.

Главный язык запросов в PostgreSQL — PL/pgSQL, он похож на PL/SQL в Oracle. PostgreSQL также поддерживает SQL:2003 PSM сохраненные процедуры также как и другие популярные языки программирования Perl (PL/Perl), Python (PL/Python), TCL (PL/Tcl), Java (PL/Java) и даже C (PL/C).

MySQL следует синтаксису SQL:2003 для сохраненных процедур, который также используется в IBM DB2.

MySQL поддерживает другие языки программирования сохраненный процедур с помощью плагинов. Из самых популярных стоит отметить Java, Perl, XML-RPC.

Триггеры

И PostgreSQL и MySQL поддерживают триггеры. Триггеры в PostgreSQL могут выполнять любые пользовательские функции из любого процедурного языка.

Триггеры в MySQL активируют только SQL запросами. Они не активируются изменениями в таблицах, которые делаются при помощи MySQL API и не выполняют никаких SQL запросов.

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

Синтаксис для определения триггеров в PostgreSQL не так силен как в MySQL. PostgreSQL требует раздельного определения функции с специальным типом возвращаемого значения.

Репликация и Высокая Доступность

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

PostgreSQL

PostgreSQL поддерживает репликацию на уровне подключаемых модулей. Существует несколько модулей, которые позволяют осуществлять репликацию данных СУБД PostgreSQL:
— PGCluster
— Slony-I
— DBBalancer
— pgpool
— PostgreSQL table comparator
— SkyTools
— Sequoia
— Bucardo
— Mammoth Replicator
— Cubercluster
— GridSQL (shared-nothing)

Существует заблуждение о том, что эти модули третих производителей как-то плохо интегрируются и выполняют свою работу не надлежащим образом. Например Slony, был спроектирован и разработан Жаном Вейком (Jan Weick), членом команды разработчиков PostgreSQL при участии множества людей из сообщества PostgreSQL. Однако, Slony, гараздо медленнее и использует больше ресурсов чем встроенный репликатор в MySQL. Но в версии PostgreSQL 8.4 вышел встроенный репликатор.

Слабые места репликации в PostgreSQL

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

2 сервера: MySQL: 2 = 2 PostgreSQL: 2*2^2 = 8
4 сервера: MySQL: 4 = 4 PostgreSQL: 2*4^2 = 32
12 серверов: MySQL: 12 = 12 PostgreSQL: 2*12^2 = 288.

Пока Slony-I адекватен для высокой доступности с использованием двух серверов. Slony-I очень тяжело администрировать.

Bucardo написан на языке Perl и интенсивно использует триггеры PL/PGSQL и PL/PERLU.

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

MySQL

MySQL поставляется с поддержкой асинхронной репликации данных. Начиная с версии 5.1, MySQL поддерживает два формата репликации. SBR отслеживает SQL запросы, которые вносят изменения в БД, в специальный бинарный логфайл, на который подписаны все дочерние сервера. Соответственно при изменениях в главной БД, все остальные БД получают копии данных. RBR отслеживает инкрементальные изменения записей и записывает их в бинарный лог-файл, из которого получают данные все дочерние БД. RBR используется автоматически, когда выполняется определенный запрос в главной БД. Некоторые СХД как NDB, Falcon и в некоторых случаях InnoDB для репликации используют только RBR.

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

Типы данных

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

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

MySQL не имеет типа данных IP Адрес, который присутствует в PostgreSQL но для преобразований из и в IPV4 используются функции INET_ATON() и INET_NTOA(). В БД хранится как Integer.

Подзапросы

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

Расширенное индексирование

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

Множественные индексы.

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

Полнотекстовые индексы.

MySQL идет с поддержкой полнотекстового поиска, но поиск может быть использован только на СХД MyISAM. Также MySQL поддерживает внешние модули для организации полнотекстового поиска — Sphinx Fulltext Search Engine. Данный плагин добавляет поддержку полнотекстового поиска для СХД InnoDB.

PostgreSQL начиная с версии 8.2 имеет в своем распоряжении полнотекстовый поиск в модуле tsearch2. Начиная с версии 8.3 модуль tsearch2 интегрирован в ядро PostgreSQL.

Частичное индексирование

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

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

Порционирование

MySQL поддерживает несколько форм горизонтального порционирования:
— RANGE
— LIST
— HASH
— KEY
— Композитное порционирование используя RANGE или LIST с HASH или KEY

PostgreSQL поддерживает только RANGE и LIST. HASH порционирование поддерживается с помощью внешних функций. Также PostgreSQL поддерживает композитные решения.

Лицензирование

PostgreSQL распространяется по лицензии BSD, в которой отмечены пункты Free Software Definition и Open Source Definition и стандарт Copyfree.

MySQL доступен по лицензии GNU General Public License, в которой также отмечены пункты Free Software Definition и Open Source Definition но не по стандарту Copyfree. А это значит что если будет издан продукт, в котором используются исходники MySQL вам придется либо заплатить за коммерческую копию MySQL AB либо открыть исходные коды для общего пользования.

Разработка

MySQL владеет и спонсирует одна профилирующая компания MySQL AB. Правда теперь она является частью Sun Microsystems, которая в свою очередь была приобретена компанией Oracle. MySQL AB держит копирайты и исходники MySQL.

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

MySQL — это ПРОДУКТ с открытым исходным кодом.
PostgreSQL — это ПРОЕКТ с открытым исходным кодом.

Происхождение названия

Когда был создан стандарт ANSI SQL, его автор сказал, что официально SQL произносится как «ess queue ell». Имена и MySQL и PostgreSQL отражают в своих именах этот стандарт. MySQL официально произносится как «my ess queue ell». Однако многие произносят MySQL как «my sequel».

Так как MySQL является собственностью компании MySQL AB, то компания была вынуждена сохранить копирайты на имя своего детища и по этому название скорее всего останется неизменным.

PostgreSQL произносится как «пост гресс ку элл». Название сформировано из старого названия Postgres (так называлась СУБД на базе которой был создан PostgreSQL) и SQL. Некоторые люди называют Постгрескуэл как пиджискуэл (pgsql). Ходят слухи, что PostgreSQL будет переименован назад в Postgres. На данный момент идут активные дебаты по этому поводу.

Популярность

MySQL широко известен и применяется в большинстве открытых (open-source) разработок по всему миру. Наиболее часто используется СХД MyISAM. И с уверенностью можно сказать, что MySQL самая популярная СУБД для вебразработок по всему миру.

Основаниями для такой популярности служит то, что MySQL легче использовать, чем другие СУБД, в частности PostgreSQL. Это утверждение бытует уже много лет. Но фактически несколько лет назад PostgreSQL произвел серьезные изменения в своей структуре и может по праву считаться более легким в использовании нежели MySQL. Но вопрос по прежнему открыт. Что же выбрать? MySQL или PostgreSQL?

Версия статьи

Приведенное ниже сравнение проводилось в MySQL AB. Я старался быть как можно более точными и объективными, однако, зная MySQL, я не мог похвастаться таким же знанием возможностей PostgreSQL, поэтому в чем-то мог и ошибиться.

Прежде всего хотелось бы отметить, что PostgreSQL и MySQL являются широко используемыми программными продуктами, которые разрабатывались с разными целями (хотя создатели обоих и стремятся довести их до полной совместимости со стандартом ANSI SQL). Это значит, что для решения одних задач больше подходит MySQL, для других же - PostgreSQL. Выбирая СУБД, проверьте, соответствуют ли ее возможности требованиям, предъявляемым решаемой задачей. Если требуется максимальная скорость работы, лучше всего, вероятно, будет остановить свой выбор на MySQL Server. Если же вам необходимы дополнительные возможности, имеющиеся только у PostgreSQL, этой СУБД и стоит пользоваться.

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

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

Сравнение возможностей MySQL и PostgreSQL

MySQL обладает следующими преимуществами перед PostgreSQL:

· MySQL обычно намного превосходит PostgreSQL по скорости работы. Кроме того, в MySQL 4.0 реализован кэш запросов. Он позволяет во много раз увеличить скорость обработки запросов для сайтов, на которых преобладают неоднократно повторяющиеся запросы на чтение.

· По количеству пользователей MySQL также намного превосходит PostgreSQL. Поэтому код тестируется значительно более придирчиво и опытным путем доказана большая его надежность, нежели у PostgreSQL. MySQL чаще, чем PostgreSQL, используется на производстве, в основном потому, что компания MySQL AB (ранее - TCX DataKonsult AB) предоставляет высококачественную коммерческую техническую поддержку MySQL с момента появления этой системы на рынке, а у PostgreSQL до самого последнего времени никакой поддержки не было.

· MySQL работает в среде Windows лучше, чем PostgreSQL. MySQL Server запускается как настоящее (родное) Windows-приложение (в NT/2000/XP - сервис), в то время как PostgreSQL запускается в среде эмуляции, Cygwin. Доводилось слышать о недостаточной стабильности работы PostgreSQL в среде Windows, но самостоятельно эти сведения до сих пор проверить не мог.

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

· MySQL работает на высоконадежных промышленных системах 24/7 (включенных 24 часа в сутки 7 дней в неделю). В большинстве случаев никаких "чисток" в MySQL производить не требуется. PostgreSQL же пока что не может работать в таких системах, так как иногда приходится запускать VACUUM для освобождения занятого последствиями работы команд UPDATE и DELETE пространства и проводить статистический анализ, необходимый для достижения максимальной производительности PostgreSQL. Запускать VACUUM необходимо и после каждого добавления к таблице нескольких столбцов. На напряженно работающих системах VACUUM нужно запускать более часто, в худших случаях - по несколько раз в день. А ведь во время работы VACUUM (а ее работа может продолжаться часы, если база данных достаточно велика) база практически "мертва". Впрочем, в PostgreSQL версии 7.2 выполнение основных функций этой программы больше не приводит к блокировке базы, и пользователи могут продолжать нормально работать с ней. Новая команда VACUUM FULL берется за дело более серьезно: она, как и в старых версиях, блокирует таблицу и сжимает копию таблицы на диске.

· Книг о MySQL вышло значительно больше, нежели о PostgreSQL. Книги о MySQL выпустили издательства O"Reilly, SAMS, Que и New Riders. Все возможности MySQL детально описаны в документации, так как это является обязательным условием включения новых возможностей в код.

· MySQL обладает значительно более мощной реализацией ALTER TABLE.

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

· MySQL может работать с двумя поддерживающими транзакции обработчиками таблиц, а именно - InnoDB и BerkeleyDB. Так как все системы поддержки транзакций в разных условиях работают по-разному, это дает разработчику возможность найти наилучшее решение для условий, в которых будет работать его система. See section 7 Типы таблиц MySQL.

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

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

· В MySQL реализован полнотекстовый поиск.

· Имеется возможность работы с несколькими базами через одно соединение(разумеется, в зависимости от привилегий пользователя).

· Система MySQL с самого начала разрабатывалась в расчете на многопоточность, а PostgreSQL использует процессы. Переключение контекстов и доступ к общим данным несколькими потоками осуществляется значительно быстрее, нежели отдельными процессами. Таким образом MySQL Server в многопользовательских приложениях получает неплохое преимущество в производительности, а кроме того, таким образом MySQL Server удается значительно эффективней пользоваться преимуществами, предоставляемыми симметричными мультипроцессорными системами (SMP).

· В MySQL реализована значительно более мощная система привилегий, нежели в PostgreSQL. В то время как PostgreSQL обеспечивает лишь привилегии INSERT, SELECT и UPDATE/DELETE над базой или таблицей, MySQL предоставляет возможность определения полного набора разнообразных привилегий на уровне базы, таблицы и столбца. Кроме того, MySQL позволяет задавать привилегии для комбинаций хост/пользователь.

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

· Все типы таблиц в MySQL (кроме InnoDB) реализованы в виде файлов (по одной таблице в файле), что значительно облегчает создание резервных копий, перенос, удаление и даже создание символьных ссылок между базами данных и таблицами, даже если сервер находится в нерабочем состоянии.

· Обновление (апгрейд) MySQL проходит совершенно "безболезненно". При модернизации MySQL нет нужды в копировании/восстановлении данных, что приходится делать при установке большинства обновлений PostgreSQL.

Ниже перечислены преимущества PostgreSQL по сравнению с MySQL на сегодняшний день.

Другие причины, по которым можно предпочесть PostgreSQL:

· В некоторых случаях PostgreSQL оказывается ближе к ANSI SQL.

· Работу PostgreSQL можно ускорить, выполняя код в виде хранимых процедур.

· При хранении географических данных R-деревья дают PostgreSQL преимущество перед MySQL (примечание: в MySQL версии 4.1 для таблиц MyISAM реализована поддержка R-деревьев).

· Команда разработчиков PostgreSQL, пишущих код для сервера, больше.

Недостатки PostgreSQL по сравнению с MySQL:

· VACUUM затрудняет использование PostgreSQL в постоянно работающих системах.

· Наличие только транзакционных таблиц.

· Значительно более медленная работа команд INSERT, DELETE и UPDATE.

Заключение

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

В связи со стремительной девальвацией рубля, покупать СУБД Microsoft SQL стало очень дорого, а для некоторых компаний стоимость этих лицензий стала совсем «неподъемной». В данный момент чтобы развернуть сервер Microsoft SQL для 20 пользователей необходимо купить такие лицензии:

1 лицензия на операционную систему (WinSvrStd 2012R2)

20 лицензий на подключение к серверу (WinSvrCAL 2012)

1 лицензия на сервер СУБД (SQLSvrStd 2014)

20 лицензий на подключение к СУБД (SQLCAL 2014)

Ориентировочная стоимость такого пакета 275 000 руб., что для компании, в которой всего 20 человек достаточно дорого. Данных затрат можно избежать, если создать сервер СУБД на свободном ПО. Поставить операционную систему семейства Linux и бесплатную версию СУБД - PostgreSQL . На таком сервере без проблем можно развернуть сервер 1С предприятия, а также другие роли, которые потенциально могут быть совмещены c ролью баз данных, например WebServer или файловое хранилище.

Так как использовать свободное ПО очень привлекательно с финансовой точки зрения, было решено проверить, на сколько это хорошо с точки зрения производительности.

Тестирование производительности 1С:

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

Таблица 1. Тестовые стенды

Для начала был выполнен «тест Гилева», который показал незначительное преимущество стенда номер 2, против стенда со свободным ПО.

Результаты смотрим ниже, разница в значениях получилась всего 3%.

Для информации: «тест Гилева» - популярный синтетический тест 1С, который выполняет ряд стандартных операций – чем быстрее тест выполняется, тем выше оценка. Оценка выполняется в условных единицах. Полученную оценку можно сравнить с прилагаемой к тесту шкале, которая покажет на сколько высока производительность текущей системы.

Рисунок 1. Результат теста Гилева. Стенд №2 СУБД MS SQL

Рисунок 2. Результат теста Гилева. Стенд №1 СУБД PostgreSQL

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

Для этого взяли реальную рабочую базу одной из самых тяжелых конфигураций 1С, характеристики базы указаны в таблице №2.

Таблица 2. Характеристики тестовой базы

Замерялось время выполнения 7-ми стандартных операций с объектами в базе. Каждый тест выполнялся 10 раз и выводилось среднее значение. Замеры проводились с использованием толстого клиента через локальную сеть. Клиент устанавливался на рабочую станцию под управлением Windows 7. Тесты также пробовали запускать с клиента установленного на Ubuntu Linux , но он работал не стабильно и все тесты решено было выполнять только с клиента на Windows .

Таблица 3. Результаты APDEX

В среднем наша реальная база при использовании MSSQL работала на 45% быстрее, чем на стенде со свободным ПО. На некоторых тестах отрыв был очень значителен, а на таких как, например проведение нового документа составлял всего 11%.

Вывод:

1C на СУБД MSSQL работает примерно в 1,5 быстрее, чем на PostgreSQL. Соответственно, если есть возможность купить или арендовать лицензии MSSQL, лучше использовать его для более высокой производительности. Для небольших и ненагруженных баз можно попробовать использовать версию MSSQL Express. Тестов с ней мы не проводили, поэтому она может показать себя по производительности как лучше так и хуже PostgreSQL. Данная редакция ограничена использованием 1 процессора и 1 Гб ОЗУ, также не работает с базами более 10Гб. Если база дорастет до такого размера, то она остановиться и перестанет работать полностью, но как показывает практика, если в базе работает 15-20 пользователей, то комфортно можно работать при размере базы 4-5ГБ, далее база начинает сильно тормозить.

Оценка «тестом Гилева» показывает крайне незначительное превосходство MSSQL, что позволяет сделать предположение о том, что другие базы 1С могут работать на PostgreSQL так же хорошо, как и на MSSQL, а возможно и быстрее. Перед выбором СУБД рекомендуем провести тесты на своей конкретной базе и сравнить полученные результаты.

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

В конце 2017 года мы провели новые тесты и опубликовали их в очередной статье .

Ещё одним способом сэкономить при использовании 1С является решение - взять сервер 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

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

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

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

В PostgreSQL вы можете использовать курсоры . Представьте, что некоторый запрос возвращает гигабайт данных. Вы вынуждены передать весь этот гигабайт по сети (если СУБД работает на отдельном сервере) и сохранить его в памяти перед тем, как что-то с ним делать. Даже если используемый вами драйвер поддерживает функции типа fetch_next_row, в действительности он все равно сначала кладет весь результат выполнения запроса в память. С помощью курсоров вы можете не только забирать данные кусками, тем самым обрабатывая их в постоянном объеме памяти, но и свободно перемещаться по ним в разные стороны. Например, вы можете прочитать первые 100 строк, потом посмотреть 10001-ую, и в зависимости от ее значения перейти к последней строке или вообще закрыть курсор.

Не менее интересным механизмом являются функциональные индексы . Допустим, вам нужно хранить имя и фамилию пользователя в отдельных столбцах и с учетом регистра, однако поиск пользователей при этом происходит по полному имени и без учета регистра. Если СУБД не поддерживает функциональные индексы, вы вынуждены создать в таблице дополнительное поле со значением LOWER (first_name || " " || last_name) , построить по нему индекс и поддерживать в этом поле правильное значение. Если такого рода вариантов запросов десять, вам понадобится десять дополнительных столбцов. Функциональные индексы, как и следует из названия, позволяют построить индекс по произвольной функции, избежав тем самым всех описанных проблем. Например, вы можете эффективно выполнять запросы с условиями вроде WHERE sin(x) > 0.45 AND sin(x) < 0.46 .

PostgreSQL может строить индексы только по части строк в таблице. Этот механизм называется частичными индексами . Например, если вы ходите в базу с запросами вроде SELECT * FROM logs WHERE ip > inet "192.168.0.0" AND ip < inet "192.168.0.255" AND level = "error" , то можете построить индекс по полю ip только для строк, значение поля level которых равно "error" . Это имеет смысл, если логов много, а строк со значением level = "error" мало. С помощью частичных индексов вы получаете более быстрые запросы и меньшие накладные расходы (место на диске, время добавления новых строк), чем в случае использования обычных индексов.