Популярные расширения для PostgreSQL: как установить и для чего использовать

Kate · 7 Окт 2021

Облачные базы данных Selectel поддерживают 40 расширений для PostgreSQL. Некоторые добавляют небольшие радости оптимизации баз данных, другие — заменяют отдельные модули разработки на стороне приложения. На данный момент расширениями пользуются 26% пользователей DBaaS. Мы узнали, какие экстеншены наиболее популярны у клиентов и где они их применяют.

Если вы опытный DBA, вы точно нужны в комментариях — расскажите, какие расширения используете и как они решают ваши задачи.

PostGIS

Расширение предназначено для работы с геоданными.

Почему его выбирают

Поддерживает пространственные индексы R-Tree/GiST и функции обработки геоданных.
Оперирует такими геометрическими объектами, как точка, линия, полигон, мультиточка, мультилиния, мультиполигон и геометрическая коллекция. Они определены в формате Well Known Text Open GIS (с расширениями XYZ, XYM, XYZM).
В PostGIS входят другие экстеншены по работе с геокодингом: address_standardizer; address_standardizer_data_us; postgis_tiger_geocoder, postgis_topology.
Позволяет создавать запросы, совмещающие тесты на попадание объекта в охват и заданный радиус.

Пример: Вы развиваете сеть пекарен с домашними пирожками, а в пандемию начали работать на доставку. Вы хотите создать мобильное приложение, которое поддерживает определение всех близко расположенных от человека пекарен. Такую фичу можно реализовать с помощью PostGIS.

TimescaleDB

Расширение позволяет хранить временные ряды (time series-данные) и управлять ими.

В облачных базах данных Selectel расширение представлено отдельным типом БД для PostgreSQL (в качестве расширения TimescaleDB не добавляется). К созданной базе можно подключать дополнительные экстеншены. Например, расширения pg_stat_statements или postgres_fdw, которые мы рассмотрим отдельно.

Почему его выбирают

Оптимизирует хранение time series-данных: время вставки новых значений не увеличивается при увеличении количества данных.
Не нужно переходить на сторонние решения, заточенные под time series-данные, — ClickHouse, InfluxDB и другие.
Работа в одном программном стеке. Можно хранить как временные ряды, так и другие типы данных на одной платформе.

Пример: У вас highload-проект — масштабный маркетплейс, похожий на Авито. Помимо хранения большого количества реляционных данных и их обработки, вам важно контролировать статус состояния инфраструктуры. Сведения с данными состояния снимаются раз в полчаса и хранятся в базе данных с расширением TimescaleDB. Новые данные не перезаписываются, а хранятся необходимое время для потенциального ретроспективного анализа. При этом остальные бизнес-данные у вас также размещены в БД PostgreSQL.

uuid-ossp

Расширение генерирует уникальный идентификатор UUID вместо обычного ID.

Почему его выбирают

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

pg_stat_statements

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

Почему его выбирают

Можно выследить запрос, снижающий производительность базы данных, и оптимизировать запросы.

Клиентам облачных баз данных Selectel экстеншен подключается по умолчанию.

postgres_fdw

Расширение позволяет обращаться к внешним СУБД, файлам и веб-сервисам.

Почему его выбирают

Можно получать данные из нескольких баз, не используя сторонние инструменты.
Пригодится для шардинга, где нужно поделить одну большую базу данных на несколько инстансов в вертикальной или горизонтальной логике.
Поможет провести бесшовную миграцию с одной базы данных на другую или объединить несколько БД.
Готовые FDW (foreign-data wrappers) есть у MySQL, Redis, MongoDB, ClickHouse, Kafka и других СУБД.

hstore

Расширение позволяет объединять в одной базе данных значения с разными атрибутами.

Почему его выбирают

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

Пример: У вас сайт товарами для школьников. В базе данных у вас объекты с разными атрибутам: книги — атрибут «жанр», «год издания»; ручки — атрибут «подтип», «страна производства» и т.д. С hstore можно привести их к единому знаменателю, добившись более «чистой» базы данных: две-три колонки вместо десяти.

pgcrypto

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

Почему его выбирают

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

pg_trgm

Расширение позволяет искать текстовые документы по триграммам (последовательность из трех букв, входящая в индексируемый текст).

Почему его выбирают

Поиск с pg_trgm нечувствителен к опечаткам. Даже если пользователь ошибся в фамилии, заполняя форму с контактами, или запрос написан с ошибкой, вы получите нужный документ. Функция максимально полезна в сочетании с полнотекстовым поиском.
Помогает ускорять LIKE/ILIKE-запросы, если нужно запросить данные с использованием метода сопоставления с образцом.

citext

Расширение адаптирует данные для регистронезависимой проверки.

Почему его выбирают

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

btree_gist

Расширение позволяет работать сразу с двумя индексами PostgreSQL — btree и GiST.

Почему его выбирают

Подходит для типов данных, где не работает жесткая семантика сравнения — «больше», «меньше» или «равно», характерная для btree.
Предоставляет оператор «не равно» и оператор расстояния для поиска ближайших соседей с использованием индексов GiST.
Полезно для баз данных, где часть полей индексируется только с GiST, а другая — представляет собой простые типы данных.

Пример: У вас сервис по бронированию отелей. Индекс данных по брони номеров будет представлен в двух типах — timestamp (дата заезда), который индексируется только с GiST, и простом типе varchar (номер отеля).

Как установить расширения в облачных базах данных Selectel

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

Если у вас нет базы данных, создайте ее по инструкции.

Зайдите в раздел Облачная платформа и выберите Базы данных.

Выберите нужный кластер и на его странице откройте вкладку Базы данных.

В карточке нужной базы данных в блоке Расширения нажмите кнопку Добавить. Выберите в списке расширение и сохраните изменения.

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

С полным списком поддерживаемых расширений ознакомьтесь здесь.