Базовые различия при работе с базами данными MySQL и PostgreSQL Дилетантский обзор

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

Реляционная система управления базами данных (РСУБД)

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

Типы данных и отношений между ними

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

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

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

SQLite

Легко встраиваемая в приложения база данных. Так как это система базируется на файлах, то она предоставляет довольно широкий набор инструментов для работы с ней, по сравнению с сетевыми СУБД. При работе с этой СУБД обращения происходят напрямую к файлам (в эти файлах хранятся данные), вместо портов и сокетов в сетевых СУБД. Именно поэтому SQLite очень быстая, а также мощная благодаря технологиям обслуживающих библиотек.

Типы данных SQLite

NULL – значение NULL
INTEGER – знаковое целочисленное значение, использует 1, 2, 3, 4, 6, или 8 байт в зависимости от порядка числа
REAL – число с плавающей точкой, занимает 8 байт для хранения числа в формате IEEE
TEXT – текстовая строка, при хранении используются кодировки UTF-8, UTF-16BE или UTF-16LE
BLOB – тип данных BLOB, массив двоичных данных (предназначенный, в первую очередь, для хранения изображений, аудио и видео).

Заметка: более подробно о типах данных SQLite в официальной документации < :target="_blank" >.

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

Файловая структура – вся база данных состоит из одного файла, поэтому её очень легко переносить на разные машины
Используемые стандарты – хотя может показаться, что эта СУБД примитивная, но она использует SQL. Некоторые особенности опущенны (RIGHT OUTER JOIN или FOR EACH STATEMENT), но основные все-таки поддерживаются
Отличная при разработке и тестировании – в процессе разработки приложений часто появляется необходимость масштабирования. SQLite предлагает всё что необходимо для этих целей, так как состоит всего из одного файла и библиотеки написанной на языке C.

Недостатки SQLite

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

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

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

Когда отказаться от SQLite

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

MySQL

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

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

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

Типы данных MySQL

TINYINT – очень малые целочисленные значения
SMALLINT – малые целочисленные значения
MEDIUMINT – средние целочисленные значения
INT или INTEGER – стандартные целочисленные значения
BIGINT – большие целочисленные значения
FLOAT – маленькие значения с плавающей точкой (точность до одного значения после точки). Всегда знаковые значения
DOUBLE, BOUBLE PRECISION, REAL – Стандартные значения с плавающей точкой. Всегда знаковые
DECIMAL, NUMERIC – распакованное значение с плавающей точкой, всегда знаковое.
DATE – дата
DATETIME – дата и время в одном значении
TIMESTAMP – временная отметка timestamp
TIME – время
YEAR – год, 2 или 4 числа (4 – по-умолчанию)
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, 4294967296 (2^32 – 1) символов – максимальныя длина
ENUM – перечисление
SET – множество

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

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

Недостатки MySQL

Известные ограничения – по задумке в MySQL заложены некоторые ограничения функционала, которые иногда необходимы в особо требовательных приложениях.
Проблемы с надежностью – из-за некоторых способов обработки данных MySQL (связи, транзакции, аудиты) иногда уступает другим СУБД по надежности.
Медленная разработка – Хотя MySQL технически открытое ПО, существуют жалобы на процесс разработки. Стоит заметить, что существуют другие довольно успешные СУБД созданные на базе MySQL, например MariaDB.

Читать еще: Как восстановить стертые файлы с жесткого диска?

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

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

Когда лучше отказаться от MySQL

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

PostgreSQL

PostgreSQL является самым профессиональным из всех трех рассмотренных нами СУБД. Она свободно распространяемая и максимально соответствует стандартам SQL. PostgreSQL или Postgres стараются полностью применять ANSI/ISO SQL стандарты своевременно с выходом новых версий.

От других СУБД PostgreSQL отличается поддержкой востребованного объектно-ориентированного и/или реляционного подхода к базам данных. Например, полная поддержка надежных транзакций, т.е. атомарность, последовательность, изоляционность, прочность (Atomicity, Consistency, Isolation, Durability (ACID).) Благодаря мощным технологиям Postgre очень производительна. Параллельность достигнута не за счет блокировки операций чтения, а благодаря реализации управления многовариантным параллелизмом (MVCC), что также обеспечивает соответствие ACID. PostgreSQL очень легко расширять своими процедурами, которые называются хранимые процедуры. Эти функции упрощают использование постоянно повторяемых операций.

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

Часть 1. Сравнительный анализ

Серия контента:

Этот контент является частью # из серии # статей: MySQL & PostgreSQL

Этот контент является частью серии: MySQL & PostgreSQL

Следите за выходом новых статей этой серии.

1. История развития MySQL и PostgreSQL

История MySQL начинается в 1979 г., у ее истоков стояла небольшая компания во главе с Monty Widenius. В 1996 г. появился первый релиз 3.11 под солярис с публичной лицензией. Затем MySQL была портирована под другие операционные системы, появилась специальная коммерческая лицензия. В 2000 г., после добавления интерфейса, аналогичного Berkeley DB, база стала транзакционной. Примерно тогда же была добавлена репликация. В 2001 г. в версии 4.0 был добавлен движок InnoDB к уже имеющемуся MyISAM, в результате чего появилось кеширование и возросла производительность. В 2004 г. вышла версия 4.1, в которой появились подзапросы, парциальная индексация для MyISAM, юникод. В версии 5.0 в 2005 г. появились хранимые процедуры, курсоры, триггеры, представления (views). В MySQL развиваются коммерческие тенденции: в 2009 г. MySQL стала торговой маркой компании Oracle.

История постгрес началась в 1977 г. с базы данных Ingress.

В 1986 г. в университете Беркли, Калифорния, она была переименована в PostgreSQL.

В 1995 г. постгрес стала открытой базой данных. Появился интерактивный psql.

В 1996 г. Postgres95 была переименована в PostgreSQL версии 6.0.

У постгреса несколько сотен разработчиков по всему миру.

2. Архитектура MySQL и PostgreSQL

PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.

Для каждого клиента на сервере создается новый процесс (не поток !). Для работы с такими клиентскими процессами сервер использует семафоры.

Клиентский запрос проходит следующие стадии.

Коннект.
Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.

Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.

MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.

Реализован стандарт SQL92.

С модульной точки зрения код MySQL можно разделить на следующие модули.

Инициализация сервера.
Менеджер коннектов.
Менеджер потоков.
Обработчик команд.
Аутентификация.
Парсер.
Кеш.
Оптимизатор.
Табличный менеджер.
Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
Логирование.
Репликация.
Сетевое API.
API ядра.

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

Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.

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

Наиболее важный код находится в файле sql/mysqld.cc. В нем находятся базовые функции, которые не меняются со времен версии 3.22: init_common_variables() init_thread_environment() init_server_components() grant_init() // sql/sql_acl.cc init_slave() // sql/slave.cc get_options() handle_connections_sockets() create_new_thread() handle_one_connection() check_connection() acl_check_host() // sql/sql_acl.cc create_random_string() // sql/password.cc check_user() // sql/sql_parse.cc mysql_parse() // sql/sql_parse.cc dispatch_command() Query_cache::store_query() // sql/sql_cache.cc JOIN::optimize() // sql/sql_select.cc open_table() // sql/sql_base.cc mysql_update() // sql/sql_update.cc mysql_check_table() // sql/sql_table.cc

В хидере sql/sql_class.h определяются базовые классы: Query_arena, Statement, Security_context, Open_tables_state classes, THD. Обьект класса THD представляет собой дескриптор потока и является аргументом большого количества функций.

3. Сравнение MySQL и PostgreSQL: сходство и различия

ACID-стандарт

Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.

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

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

парциальные индексы;
компрессия данных;
выделение памяти;
улучшенный кеш.

Читать еще: Отключается видеодрайвер и восстанавливается

MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.

Компрессия

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

MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.

SMP

На мульти-процессорности PostgreSQL имеет преимущество над MySQL. Даже сами разработчики MySQL признают, что их движок в этом плане не так хорош.

Типы данных

MySQL: для хранения бинарных данных использует типы TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, которые отличаются размером (до 4 ГБ).

Character: четыре типа – TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: поддерживает механизм пользовательских данных с помощью команды CREATE TYPE, тип BOOLEAN, геометрические типы.

Character: TEXT (ограничение – max row size).

Для хранения бинарных данных есть тип BLOB, который хранится в файловой системе. Столбцы таблицы могут быть определены как многомерный массив переменной длины. Обьектно-реляционное расширение: структура таблицы может быть унаследована от другой таблицы.

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

И PostgreSQL , и MySQL поддерживают хранимые процедуры. PostgreSQL придерживается стандарта Oracle PL/SQL, MySQL – IBM DB2. MySQL поддерживает extend SQL для написания функций на языке C/C++ с версии 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C для написания хранимых процедур.

Ключи

И PostgreSQL , и MySQL поддерживают уникальность Primary Key и Foreign Key. MySQL не поддерживает check constraint плюс вторичные ключи реализованы частично. PostgreSQL: полная реализация плюс поддержка ON DELETE CASCADE и ON UPDATE CASCADE.

Триггеры

MySQL: рудиментарная поддержка. PostgreSQL: декларативные триггеры: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; процедурные триггеры: CONSTRAINT TRIGGER. События: BEFORE или AFTER на INSERT, DELETE , UPDATE.

Автоинкремент

MySQL: в таблице может быть только один такой столбец, который должен быть проиндексирован. PostgreSQL: SERIAL data type.

Репликации

Поддерживаются и в MySQL, и в PostgreSQL. PostgreSQL имеет модульную архитектуру, и репликация входит в отдельные модули:

Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.

В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:

SBR – statement based replication;
RBR – row based replication.

Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 5.5, в MySQL поддерживается так называемая полусинхронная репликация, при которой основной сервер (master) делает сброс данных на другой сервер (slave) при каждом коммите. Движок NDB делает полную синхронную двухфазную репликацию.

Транзакции

MySQL: только для для InnoDB. Поддержка SAVEPOINT, ROLLBACK TO SAVEPOINT. Уровни блокировки: table level (MyISAM). PostgreSQL: поддерживается плюс read committed и уровни изоляции. Поддержка ROLLBACK, ROLLBACK TO SAVEPOINT. Уровни блокировки: row level, table level.

Уровни привилегий

PostgreSQL: для пользователя или группы пользователей могут быть назначены привилегии.

Экспорт-импорт данных

MySQL: набор утилит для экспорта: mysqldump, mysqlhotcopy, mysqlsnapshot. Импорт из текстовых файлов, html, dbf. PostgreSQL: экспорт – утилита pg_dump. Импорт между базами данных и файловой системой.

Вложенные запросы

Есть и в MySQL, и в PostgreSQL, но в MySQL могут работать непроизводительно.

Индексация

Хэширование индексов: в MySQL– частичное, в PostgreSQL – полное. Полнотекстовый поиск: в MySQL– частичный, в PostgreSQL – полный. Парциальные индексы: в MySQL не поддерживаются, в PostgreSQL поддерживаются. Многостолбцовые индексы: в MySQL ограничение 16 столбцов, в PostgreSQL – 32. Expression-индексы: в MySQL– эмуляция, в PostgreSQL – полное. Неблокирующий create index: в MySQL – частичное, в PostgreSQL – полное.

Партиционирование (Partitioning)

MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.

Автоматическое восстановление после сбоев

MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:

MyISAM – используется для хранения системных таблиц;
InnoDB – максимальное соответствие ACID, хранит данные с первичными ключами, кэширует инсерты, поддерживает компрессию, начиная с версии 5.1 – см. атрибут ROW_FORMAT=COMPRESSED;
NDB Cluster – движок, ориентированный на работу с памятью, кластерная архитектура, использующая синхронную репликацию;
ARCHIVE – поддерживает компрессию, не использует индексы;
а также: MERGE, MEMORY (HEAP), CSV.

InnoDB разрабатывается компанией InnoBase, являющейся дочерней компанией Oracle. В 6-й версии должны появиться два движка – Maria и Falcon. Falcon – движок, основанный на ACID-транзакциях.

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

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License) или Commercial. MySQL – это open-source продукт. Postgres – это open-source проект.

Заключение

Подводя итоги, можно сказать следующее: MySQL и PostgreSQL – две наиболее популярные open-source базы данных в мире. Каждая база имеет свои особенности и отличия. Если вам нужно быстрое хранилище для простых запросов с минимальной настройкой, я бы порекомендовал MySQL. Если вам нужно надежное хранилище для большого объема данных с возможностью расширения, репликации, полностью соответствующее современным стандартам языка SQL, я бы предложил использовать PostgreSQL.

Далее мы обсудим вопросы настройки MySQL и PostgreSQL.

Сравнение MySQL и PostgreSQL

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

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. Они отличаются между собой способом записи данных на диск, а также методами считывания.

Читать еще: SciDAVis: программа для визуализации данных

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, рассмотрели основные отличия обоих систем управления базами данных и попытались понять что лучше postgresql или mysql. В общем результате лучшим по возможностях получается Postgresql, но он сложен и не везде его можно применять. MySQL проще, но не поддерживает некоторых интересных функций. А какую базу данных вы выберите для своего проекта? Почему именно ее? Напишите в комментариях!

На завершение видео с описанием возможностей и перспектив Postgresql:

голоса

Рейтинг статьи

Базовые различия при работе с базами данными MySQL и PostgreSQL Дилетантский обзор