Записки программиста

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' мало. С помощью частичных индексов вы получаете более быстрые запросы и меньшие накладные расходы (место на диске, время добавления новых строк), чем в случае использования обычных индексов.

PostgreSQL поддерживает bitmap scans. Например, вам нужно выполнить запрос с условием WHERE a = 1 AND b = 2. В MySQL вы можете построить индекс по нескольким столбцам, что позволяет эффективно выполнять этот запрос, а также запросы WHERE a = 1, WHERE a > 1, но не WHERE b = 2 или WHERE b > 10 AND b < 20. Для эффективного выполнения двух последних запросов понадобится дополнительный индекс по полю b. PostgreSQL также поддерживает индексы по нескольким столбцам. Однако, благодаря поддержке bitmap scans, в PostgreSQL вы можете построить отдельный индекс по столбцу a, отдельный индекс по столбцу b и использовать эти индексы во всех приведенных запросах. Помимо одновременного использования нескольких индексов bitmap scans позволяет эффективно выполнять запросы с условиями вроде WHERE a = 4 OR a = 8 OR a = 15 OR a = 16. Работает это очень просто. Запрос разбивается на несколько подзапросов, каждый из которых выполняется с использованием подходящих индексов, а потом результаты выполнения этих запросов объединяются OR’ом или AND’ом.

В PostgreSQL есть поддержка огромного количества типов данных на все случаи жизни. Помимо традиционных типов, таких, как целые числа и строки (кстати, размер varchar в PostgreSQL не ограничен 65536-ю символами, как в MySQL), в вашем распоряжении есть UUID, IP- и MAC-адреса, точки, круги и другие геометрические фигуры, XML и JSON, а также массивы и диапазоны значений. Если встроенных типов недостаточно, вы можете объявить собственные типы и функции для работы с ними.

PostgreSQL предоставляет различные типы индексов помимо классических Hash и B-Tree индексов. Например, с помощью GiST и SP-GiST индексов можно найти все точки, находящиеся внутри заданного круга. GiST индексы также позволяют, например, сортировать заправки по расстоянию до ваших текущих координат и делать прочие операции с геоданными. GIN индексы предназначены для работы с типами, которые могут содержать более одного значения. Например, с помощью индексов этого типа вы можете найти все массивы, содержащие заданный элемент. Как GIN, так и GiST индексы могут использоваться для полнотекстового поиска. Притом, в PostgreSQL полнотекстовый поиск работает ничем не хуже, чем в условном Sphinx, чего про MySQL сказать нельзя. Еще из интересных видов индексов следует отметить индексы BRIN (не очень быстрый индекс, зато супер компактный) и Bloom (фильтр Блума).

Из прочих, но при этом не менее важных, отличий MySQL от PostgreSQL хотелось бы отметить поддержку в последнем регулярных выражений, savepoint’ов (это почти что вложенные транзакции), рекурсивных запросов, наследования таблиц, возможность написания триггеров и хранимых процедур на Tcl, Perl и Python, а также логирования медленных запросов с указанием времени с точностью до миллисекунд. Разумеется, отличия на этом не заканчиваются.

Нельзя не обратить внимание и на несколько моментов, не имеющих отношения к функционалу PostgreSQL и MySQL. В качестве преимуществ PostgreSQL над прочими РСУБД нередко отмечается надежность кода. Однажды я слушал доклад Константина Осипова, на котором, помимо прочего, он упомянул один занятный эксперимент. Был написан генератор случайных неправильных SQL-запросов в стиле WHERE * FROM SELECT. Большинству людей никогда не придет в голову писать такое. Эти запросы скармливались MySQL. В результате был найден десяток запросов, роняющих СУБД. Спрашивается, если в MySQL так скверно написан парсер запросов, что же творится во всей остальной СУБД? Я бы точно не доверил ей хранить информацию о моем банковском счете.

Второй важный момент заключается в том, что с 2008 года MySQL развивается компанией Oracle. Бытует небезосновательное мнение, что Oracle намеренно тормозит развитие MySQL. В настоящее время ведутся работы над множеством форков MySQL, наиболее интересным среди которых, видимо, является MariaDB. Недавно на эту СУБД переехала Wikipedia. В Slackware, Arch Linux, OpenSUSE, Fedora и RHEL также по умолчанию вместо MySQL используется MariaDB.

Разумеется, у PostgreSQL есть свои нюансы. Например, если у вас есть таблица (id, login, pass), вы можете ALERT’нуть ее и получить (id, login, pass, email), но не (id, login, email, pass). То есть, добавлять столбцы можно только в конец. Конечно, если только вы не создадите совершенно новую таблицу. Впрочем, обычно это не представляет собой большую проблему. Также в PostgreSQL нельзя написать запрос вроде INSERT INTO blablabla ON DUPLICATE KEY UPDATE blablabla (дополнение: в PostgreSQL 9.5 это исправили). Казалось бы, в одной транзакции можно писать DELETE, а затем INSERT, но это не работает. Правильное решение заключается в том, чтобы написать merge-функцию. Код таких merge-функций нетрудно генерировать автоматически, так что это тоже не представляет собой большую проблему.

Надеюсь, после прочтения статьи вам захочется присмотреться к PostgreSQL повнимательней.

Метки: PostgreSQL, СУБД.