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

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

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. Расширения нужно добавлять для каждой существующей базы данных.

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

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

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

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

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