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

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

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

Установка и управление расширениями

Так как для каждой версии PostgreSQL содержание пакета contrib различается, для начала стоит посмотреть, какие функции доступны в пакете в вашей версии.

Просмотр списка стандартных расширений

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

SELECT * FROM pg_available_extensions

Важно: некоторые возможности доступны только с учетной записи суперпользователя или с учетной записи, имеющей права на установку. Суперпользователь – это такой всемогущий администратор сервера базы данных (в PostgreSQL по умолчанию имеет имя postgres).

Установка

Давайте подробно разберем команду, с помощью которой можно установить любые PostgreSQL расширения:

CREATE EXTENSION IF NOT EXISTS extension_name
WITH SCHEMA schema_name
VERSION version
CASCADE

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

• IF NOT EXIST: проверка существования расширения с таким именем.
• WITH SCHEMA: имя схемы, для которой будет произведена установка. Если не указано, то установится в текущую схему.
• VERSION: версия для установки. Если не указано, то будет установлена последняя.
• CASCADE: автоматическая установка всех дополнительных расширений, необходимых для работы.

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

Обновление

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

ALTER EXTENSION extension_name
UPDATE TO version

Если не указать параметр version, будет установлена последняя версия.

Удаление

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

DROP EXTENSION IF EXISTS extension_name
CASCADE | RESTRICT

Дополнительные параметры:

•       IF EXIST: проверка существования расширения.
•       CASCADE: автоматическое удаление всего, что зависит от этого расширения.
•       RESTRICT: не удалять расширение, если от него зависят другие объекты.

 Топ самых полезных расширений для PostgreSQL

1.     pg_stat_statements

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

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

Команда показывает SQL запрос (query), время его выполнения (total_min), среднее время выполнения запроса в миллисекундах (avg_ms), а также количество вызовов запроса (calls).

SELECT query, (total_exec_time / 1000 / 60) as total_min, mean_exec_time as avg_ms, calls
FROM pg_stat_statements
ORDER BY 1
DESC LIMIT 10;

2. pgcrypto

Если вас интересует, как выполнить в PostgreSQL шифрование данных, то вот нужное расширение. Оно использует криптографические функции для шифрования данных (например, паролей). В стандартной конфигурации поддерживаются следующие алгоритмы шифрования: md5, sha1, sha224, sha256, sha384 и sha512. Список алгоритмов можно расширить, сменив значение «по умолчанию» на использование OpenSSL в настройках инструмента.

3. btree_gist

Для того чтобы использовать разные индексы базы данных PostgreSQL (B-tree и GiST), нужно установить расширение btree_gist. Оно пригодится, если в базе есть пространственные данные, например, координаты города или магазина. Что это за индексы и для чего их использовать?

• Тип индекса B-tree используется по умолчанию и позволяет проиндексировать любые данные, которые могут быть отсортированы (например, числа и даты). Этот тип индекса хорош и практически универсален, но совсем не подходит для работы с данными, которые невозможно отсортировать.
• Тип индекса GiST умеет распределять данные любого типа, в том числе геоданные.

Важно: В дополнение к типичным операторам поиска для индекса B-tree, в btree_gist также поддерживается индекс PostgreSQL «не равно».

4. Timescaledb

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

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

Timescaledb позволяет хранить временные данные и работать с ними. Для работы скачайте нужную версию на официальном сайте и следуйте инструкции для установки. Затем добавьте расширение функцией CREATE EXTENSION, о которой мы рассказывали выше.

5. hstore

Это расширение базы данных позволяет хранить пары ключей и значений в одном поле данных в PostgreSQL. Подобный тип данных встречается в объектно-ориентированных языках программирования, например в Python.  

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

Пример использования:

1. Создадим таблицу с полем типа hstore:

CREATE TABLE books (
id serial PRIMARY KEY,
name varchar,
attributes hstore
);

2.     Заполним значения в таблице:

INSERT INTO books (name, attributes) VALUES (
'Harry Potter and the Philosophers Stone',
'author => "J. K. Rowling",
pages     => 223,
series => "Harry Potter"'
);

3.     Выведем данные по книгам из серии «Гарри Поттер»:

SELECT name, attributes->'author' as author
FROM books
WHERE attributes->'series' = 'Harry Potter'

Результат

Все атрибуты для отдельной книги будут выглядеть вот так:

SELECT * FROM books WHERE attributes->'series' = 'Harry Potter'

Мы рассмотрели только самые популярные инструменты, но на самом деле их гораздо больше. Поделитесь вашими советами в комментариях!