Друзья, представляем вашему вниманию вторую часть перевода «Чем PostgreSQL лучше?». Надеемся, она вызовет такое же горячее обсуждение в комментариях, как и первая часть. А также с радостью продолжим с вами дискуссию лично на PG Day'16 Russia, до которой осталось совсем немного!

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

Индексирование

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

Частичные индексы

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

-- создание индекса только для платящих пользователей
CREATE INDEX paying_accounts_idx ON accounts (account_id)  
WHERE account_type <> 'test';

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

Функциональные индексы

Функциональные индексы (или индексы на основе выражений) могут быть созданы с помощью любой функции, чтобы предварительно вычислить столбец для индексирования. Новые значения индексируются и рассматриваются как константы для выполнения запросов, а не вычисляются всякий раз, когда запускается запрос. Например, если у вас есть веб-журнал кликов, который собирает клики по URL в каком бы формате они не поступали, вы можете захотеть создать индекс, приводящий ссылки к нижнему регистру для нормализации данных (PostgreSQL чувствителен к регистру: compose.io и Compose.io будут считаться разными результатами):

-- создание индекса для URL в нижнем регистре
CREATE INDEX webhits_lower_urls_idx ON webhits (lower(url));  

GIST и GIN (а также и BRIN!)

GiST (Generalized Search Tree, Обобщенное Дерево Поиска) позволяет объединять B-дерево, R-дерево и определяемые пользователем типы индексов для создания индивидуального индекса с расширенными возможностями запросов. GiST используется в PostGIS (который мы сделали стандартным для всех инсталляций PostgreSQL с января) и OpenFTS (полнотекстовой поисковый движок с открытым исходным кодом). Постгрес также поддерживает SP-GiST, который позволяет создавать партицированные поисковые индексы для невероятно быстрого поиска.

GIN (Generalized Inverted Index, Обобщенный Инвертированный Индекс) позволяет индексировать составные типы данных, которые дают возможность объединять другие типы данных разными способами для создания чего-то полностью индивидуального. Подробное описание составных типов данных вы можете найти в первой части этой серии.

Синтаксис для создания GIST и GIN индексов будет следующим: CREATE INDEX… ON… USING GIST|GIN.... Очень просто!

В PostgreSQL 9.5 был представлен BRIN (Block Range Index), который позволяет разбивать большие таблицы на диапазоны на основании столбца для индексирования. Это значит, что планировщик запросов может сканировать только диапазон, указанный в запросе. Также, при индексировании диапазонов необходимое для индексирования место на диске будет существенно меньше, чем при стандартном B-Tree индексе.

Для сравнения

Другие рассматриваемые нами SQL базы данных сокращают разрыв, когда речь заходит о функциональных индексах. В MySQL 5.7.6 были представлены генерируемые столбцы, которые можно использовать как функциональные индексы. В MariaDB виртуальные (также известные как «генерируемые» или «вычисляемые») столбцы появились в версии 5.2, но поддерживают только использование встроенных функций для создания столбцов (определяемые пользователем функции отсутствуют). В версии 2.0 Firebird было представлено индексирование выражений с помощью вычисляемых столбцов. Тем не менее, ни одна из этих баз данных не поддерживает частичные, GiST или GIN индексы. Кроме того, мы упоминали в первой части, что нативные типы данных JSON не могут быть проиндексированы в этих базах данных.

Когда вы настроите все индексы и захотите проанализировать их производительность, не забудьте прочитать статью Мэтта Барра из mySidewalk «Простая проверка индексов в PostgreSQL».

Функции виртуальных таблиц

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

CTEs и рекурсия

Постгрес поддерживает Common Table Expressions (CTE) с использованием выражения WITH. Мы демонстрировали эту функцию в статье PostgreSQL — Series, Random and With. CTE позволяют создавать виртуальные таблицы прямо в вашем запросе, выражая логическую последовательность операций. Таким образом, их гораздо проще читать и тестировать, нежели виртуальные таблицы, созданные с помощью вложенных запросов где-то в другой части запроса. CTE в PostgreSQL также могут быть использованы рекурсивно. Эта удобная возможность позволяет пройти насквозь через иерархию с многократно ссылающимся на самого себя запросом, пока не останется больше уровней данных, которые можно вернуть. Вот пример рекурсивного CTE, которое идентифицирует уровни, темы и родительские отношения в систематике темы:

-- запрос с рекурсией
CTE WITH RECURSIVE topic_taxonomy_recursive  
(level, parent_topic_name, topic_name)
AS (  
     SELECT 1, tt.parent_topic_name, tt.topic_name
     FROM topic_taxonomy tt
     WHERE tt.parent_topic_name = 'All Topics'
     UNION ALL
     SELECT ttr.level + 1, tt.parent_topic_name, tt.topic_name
     FROM topic_taxonomy_recursive ttr, topic_taxonomy tt
     WHERE ttr.topic_name = tt.parent_topic_name
) SELECT level, parent_topic_name, topic_name
  FROM topic_taxonomy_recursive;

MySQL и MariaDB не используют условие WITH и, следовательно, формально не поддерживают CTE. Хотя вы и можете с помощью вложенных запросов создать в этих базах данных производные таблицы, они не позволяют делать рекурсию. Кроме того, несмотря на то, что оптимизатор запросов в MySQL был усовершенствован со времен релиза версии 5.6, вложенные запросы в этой базе данных известны своей проблемностью и могут существенно повлиять на производительность. Firebird в этом вопросе опережает MySQL и MariaDB и совпадает по функциональности с Постгресом: поддерживает CTE с помощью WITH и предоставляет возможность рекурсии.

Материализованные представления

Материализованные представления (Materialized views) — это ещё одна удобная функция виртуальных таблиц, поддерживаемая PostgreSQL. Они, как и обычные views, представляют результат запроса, который вы будете часто использовать, но разница в том, что результат хранится на диске, как обычная таблица. Материализованные представления могут быть проиндексированы. Кроме того, в отличие от обычных представлений, которые пересоздаются каждый раз, когда их вызывают, представления с хранимым результатом фиксируются во времени. Они не обновляются, если не делать это намеренно. Это может существенно увеличить скорость, с которой осуществляются запросы, использующие materialized views. Вместо использования обычных представлений или необходимости совершать сложные объединения таблиц или выполнять группирующие функции в запросе, используйте materialized views, где все необходимые данные уже подготовлены и ждут на диске. Когда вам понадобится обновить данные в материализованном представлении с хранимым результатом, это можно будет сделать по требованию с помощью команды REFRESH. Приведём пример materialized view, которое выдаёт сводные данные о доходах:

-- создание материализованного представления, содержащего сводные данные о доходах
CREATE MATERIALIZED VIEW aggregatedMonthlyRevenue  
(year, month, total_revenue)
AS (  
     SELECT date_part('year', date) AS year,
          date_part('month', date) AS month,
          SUM(revenue) AS total_revenue
     FROM revenue
     WHERE date >= '2014-01-01'
     GROUP BY date_part('year', date),
          date_part('month', date)
     ORDER BY date_part('year', date),
          date_part('month', date)
);

-- обновление представления, при необходимости
REFRESH MATERIALIZED VIEW aggregatedMonthlyRevenue;  

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

Возможности запросов

Возможности запросов в Постгресе обширны.

Мы уже немного говорили о WITH в предыдущем разделе. Давайте рассмотрим ещё пару дополнительных функций, которые могут быть использованы в запросах SELECT.

Объединение запросов

PostgreSQL предоставляет условия UNION, INTERSECT и EXCEPT для взаимодействия между запросами SELECT. UNION добавит результаты второго запроса SELECT к результатам первого. INTERSECT возвращает только те строки, которые соответствуют обоим запросам SELECT. EXCEPT возвращает только те строки из первого запроса SELECT, которые не совпадают со строками из второго запроса SELECT. Давайте рассмотрим пример с использованием EXCEPT, где мы хотим вернуть контактную информацию пользователей за исключением тех случаев, когда пользователь получил email в течение прошлой недели и ответил на него.

/*
запрос для выборки инфо
о пользователях, которые не получали
писем за последнюю неделю
*/
SELECT c.lastName, c.firstName, c.email  
FROM customers c  
EXCEPT  
SELECT e.lastName, e.firstName, e.email  
FROM email_log e  
WHERE e.email_date > current_date - interval '7 days'  
     AND e.email_action_date > current_date - interval '7 days'
     AND email_action_type = 'response';

Хотя MySQL, MariaDB, и Firebird поддерживают UNION, ни одна из них не поддерживает ни INTERSECT, ни EXCEPT. Тем не менее, используя в запросе объединения и условие EXISTS, можно получить тот же результат, что и в PostgreSQL. Но запрос при этом выйдет более сложным.

Оконные функции

Оконные функции, которые представляют собой агрегатные функции поверх некоторых строк результата (предоставляя «окно» в подмножество), могут быть чрезвычайно полезными. По сути, они позволяют произвести итерацию по строкам в секции, которые относятся к текущей строке, чтобы выполнить функцию. Стандартные функции включают ROW_NUMBER(), RANK(), DENSE_RANK() и PERCENT_RANK(). Ключевое слово OVER, опционально используемое с PARTITION BY and ORDER BY, показывает, что используется оконная функция. В качестве примера в параграфе «Функции и не только» ниже мы использовали оконную функцию с ROW_NUMBER() OVER..., чтобы определить медиану в серии числовых значений. Заметьте, что условие WINDOW в запросах с оконными функциями не является обязательным, но позволяет создавать и именовать окна для сохранения порядка.

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

Латеральные вложенные запросы

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

-- запрос с использованием LATERAL во вложенном запросе
SELECT s.firstName, s.LastName, x.topic_name  
FROM students s  
JOIN content_log c ON c.student_id = s.id  
LEFT OUTER JOIN LATERAL (  
     SELECT t.topic_name
     FROM content_topics t
     WHERE t.parent_topic_name = 'Technology'
          AND t.id = c.topic_id
          AND c.date > current_date - interval '30 days'
) x ON true;

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

И ещё кое-что на заметку: MySQL и MariaDB не поддерживают FULL OUTER JOIN, но можно использовать обходной путь с использованием UNION ALL, чтобы объединить все строки двух таблиц.

Функции и не только

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

Постгрес поддерживает 4 вида определяемых пользователем функций: язык запроса, процедурный язык, язык C и внутренний. Каждый вид может брать и возвращать как базовые, так и составные типы данных. Заметьте, что в PostgreSQL команда CREATE FUNCTION используется не только для создания функций, но и хранимых процедур.

Давайте рассмотрим пример создания функции, которая возвращает составной тип данных:

-- создаем новый составной тип под названием "datetext"
CREATE TYPE datetext AS (  
     date date,
     date_as_text text
);

/*
создаем функцию, которая принимает значение даты
и возвращает дату и соответствующий ей datetext
*/
CREATE FUNCTION show_date_as_text(date)  
RETURNS datetext -- this is our composite type  
AS  
$$ SELECT $1, to_char($1, 'FMMonth FMDD, YYYY') $$
LANGUAGE SQL;

-- запрос с применением функции
SELECT show_date_as_text('2015-01-01');

-- Возвращает: (2015-01-01,"January 1, 2015")

А вот пример реальной функции для нахождения медианы в серии числовых данных:

-- создаем функцию, которая находит медиану в серии числовых данных
CREATE FUNCTION median(numeric[])  
RETURNS numeric  
AS  
$$ SELECT AVG(x.result)
   FROM (
        SELECT result, 
             ROW_NUMBER() OVER (ORDER BY val) as ra,
             ROW_NUMBER() OVER (ORDER BY val DESC) as rd
        FROM unnest($1) result -- notice the use of array "unnest"
        ) AS x
   WHERE x.ra BETWEEN x.rd - 1 AND x.rd + 1;
$$
LANGUAGE SQL;

-- запрос с применением функции
SELECT median(ARRAY[1,2,3,4,5,6,7]);

-- Возвращает: 4

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

Возможности индивидуализации Постгреса и не имеют равных среди MySQL, MariaDB и Firebird.

Языковые расширения

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

В Compose мы поддерживаем только доверяемые (trusted) расширения языка для PostgreSQL, чтобы обеспечить безопасность ваших инсталляций. Мы добавили поддержку PL/Perl в феврале и PL/v8 (процедурный язык на основе JavaScript) — в августе. Эти языковые расширения, которые имеют больше встроенных функций, чем встроенный язык PL/pgSQL на основе SQL (также доступный в инсталляциях Compose), позволяют создавать изысканные скрипты для манипуляций и обработки данных на сервере.

Подводя итог

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

Несмотря на то, что мы рассказали вам лишь о небольшом множестве возможностей, которые выделяют Постгрес на фоне других SQL решений с открытым исходным кодом, на деле их гораздо больше (и ещё больше появилось в версии 9.5!). Мы надеемся, что эта серия из двух статей обеспечила убедительный обзор причин, по которым вам стоит выбрать PostgreSQL.