← назад к разделу

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

Разберёмся, что это за механизм, какие расширения стоит знать и где они помогают.

Что такое расширение и как его подключить

Расширение — это пакет SQL-объектов (функций, типов, операторов, индексных методов), которые добавляются в конкретную базу данных одной командой:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Ключевое слово IF NOT EXISTS защищает от ошибки, если расширение уже установлено. Сама операция безопасна и быстра.

Посмотреть, что уже установлено:

SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

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

Три расширения, которые стоит включить на любом проекте

Есть три расширения, которые почти ничего не стоят по ресурсам, но регулярно нужны:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

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

pg_stat_statements — видеть, какие запросы тормозят

Без этого расширения найти медленный запрос в продакшне крайне сложно: PostgreSQL не хранит историю запросов по умолчанию. pg_stat_statements исправляет это: он накапливает статистику по каждому уникальному запросу — сколько раз выполнялся, сколько суммарно занял, сколько данных прочитал.

Особенность: расширение требует одну дополнительную настройку в postgresql.conf и перезапуск кластера:

shared_preload_libraries = 'pg_stat_statements'

После перезапуска — подключить расширение и можно смотреть статистику:

CREATE EXTENSION pg_stat_statements;

-- топ-10 запросов по суммарному времени выполнения
SELECT query, calls, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Это первый инструмент при разборе проблем с производительностью.

pgcrypto — UUID, хэши паролей, криптографические функции

Расширение pgcrypto добавляет криптографические функции прямо в SQL.

Генерация UUID. Начиная с PostgreSQL 13, функция gen_random_uuid() доступна без расширения, но для совместимости со старыми версиями удобнее иметь pgcrypto:

SELECT gen_random_uuid();
-- e.g. a9f1a2b3-1c2d-4e5f-8a9b-0c1d2e3f4a5b

Хэширование паролей. pgcrypto умеет bcrypt — алгоритм, специально предназначенный для хранения паролей (медленный намеренно, чтобы затруднить перебор):

-- сохранить пароль
INSERT INTO account (email, password_hash)
VALUES ('user@example.com', crypt('plaintext', gen_salt('bf', 10)));

-- проверить пароль при входе
SELECT id FROM account
WHERE email = 'user@example.com'
  AND password_hash = crypt('plaintext', password_hash);

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

Хэши данных. Можно посчитать SHA-256 и другие хэши прямо в запросе:

SELECT digest('some text', 'sha256');

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

pg_trgm — поиск по подстроке и нечёткий поиск

Обычный LIKE '%иван%' в PostgreSQL не использует индексы — база вынуждена перебрать каждую строку таблицы. На больших таблицах это очень медленно.

pg_trgm решает задачу через триграммы: текст разбивается на трёхсимвольные фрагменты, и по ним строится специальный GIN-индекс. После этого поиск по подстроке начинает использовать индекс и работает быстро:

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- теперь этот запрос идёт через индекс
SELECT * FROM customer WHERE full_name ILIKE '%иван%';

Дополнительная возможность — нечёткий поиск с допуском опечаток. Функция similarity возвращает число от 0 до 1: чем ближе к 1, тем больше совпадение:

-- найти похожие имена, даже если есть опечатка
SELECT full_name, similarity(full_name, 'иванв') AS score
FROM customer
WHERE similarity(full_name, 'иванв') > 0.4
ORDER BY score DESC
LIMIT 10;

Это полезно для форм с автодополнением и нечёткого поиска по справочникам.

btree_gist — ограничение непересечения

В PostgreSQL есть механизм EXCLUDE: это ограничение целостности (constraint), которое запрещает строкам «пересекаться» по заданному условию. Классический пример — бронирование: одну комнату нельзя забронировать дважды на пересекающийся период.

По умолчанию EXCLUDE работает только с геометрическими и диапазонными типами через GiST-индекс. btree_gist добавляет поддержку обычных скалярных типов (целых чисел, текста) в тот же индекс, что позволяет комбинировать их с диапазонами:

CREATE EXTENSION btree_gist;

CREATE TABLE booking (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period  tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

Это ограничение на уровне базы гарантирует: для одной и той же room_id не будет двух записей с пересекающимся period. Никакой логики в приложении — база сама не пропустит конфликт.

citext — текст без учёта регистра

Типичная проблема с email-адресами: пользователь зарегистрировался как User@Example.com, а входит как user@example.com. Чтобы сравнение работало корректно, обычно приходится везде писать LOWER(email) = LOWER($1) или хранить адрес в нижнем регистре принудительно.

citext (case-insensitive text) — это тип данных, который делает сравнение без учёта регистра автоматически:

CREATE EXTENSION citext;

CREATE TABLE account (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  citext NOT NULL UNIQUE
);

-- оба запроса вернут одну и ту же строку
SELECT * FROM account WHERE email = 'user@example.com';
SELECT * FROM account WHERE email = 'USER@EXAMPLE.COM';

Уникальный индекс тоже работает с учётом регистра: User@Example.com и user@example.com будут считаться одним значением.

pgstattuple — сколько места занимает мусор

Со временем в таблицах PostgreSQL накапливаются «мёртвые» строки — удалённые или изменённые записи, которые ещё не убрал VACUUM. Это занимает место на диске и замедляет запросы.

pgstattuple позволяет точно измерить, насколько «раздута» таблица или индекс:

CREATE EXTENSION pgstattuple;

-- статистика по таблице: сколько живых строк, сколько мёртвых, сколько свободного места
SELECT * FROM pgstattuple('orders');

-- статистика по индексу: плотность листовых страниц
SELECT * FROM pgstatindex('ix_orders_customer');

-- быстрая приближённая оценка (не читает всю таблицу)
SELECT * FROM pgstattuple_approx('orders');

Когда dead_tuple_percent высокий или avg_leaf_density у индекса низкая — пора запускать VACUUM или задуматься о перестройке.

unaccent — поиск без учёта диакритики

Диакритические знаки (accent marks) — это умляуты, акценты и похожие символы в европейских языках: é, ü, ñ. unaccent убирает их, приводя текст к базовому ASCII:

CREATE EXTENSION unaccent;

SELECT unaccent('Naïve café résumé');
-- результат: Naive cafe resume

Это полезно, когда нужен поиск, который находит cafe по запросу café и наоборот. Расширение обычно используют вместе с полнотекстовым поиском:

SELECT * FROM article
WHERE search_doc @@ plainto_tsquery('russian', unaccent($1));

pg_partman — автоматизация партиций

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

pg_partman автоматизирует процесс: он сам создаёт будущие партиции заранее и при необходимости удаляет старые:

CREATE EXTENSION pg_partman;

-- настроить автоматическое партиционирование по месяцам
SELECT partman.create_parent(
    p_parent_table => 'public.event_log',
    p_control      => 'occurred_at',
    p_type         => 'native',
    p_interval     => '1 month',
    p_premake      => 4   -- создать 4 партиции вперёд
);

-- запускать по расписанию (например, через pg_cron)
SELECT partman.run_maintenance('public.event_log');

Без pg_partman то же самое требует ручного DDL или написания скриптов обслуживания.

pg_repack — реорганизация таблицы без остановки базы

VACUUM FULL убирает раздувание таблицы, перестраивая её полностью — но при этом удерживает эксклюзивную блокировку. Для большой таблицы это означает несколько минут, когда никто не может читать или писать.

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

pg_repack -d mydb -t order_doc        # реорганизовать таблицу
pg_repack -d mydb -i ix_order_status  # перестроить индекс

Это внешняя утилита, которую устанавливают отдельно, но она стоит того для баз, которые нельзя надолго останавливать.

Частые ошибки

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

Использовать hstore в новом коде. hstore — устаревший тип для хранения пар ключ-значение, появившийся до JSONB. Для нового кода всегда берите jsonb — он мощнее и лучше поддерживается.

Использовать uuid_generate_v4() из uuid-ossp. Это расширение было стандартом до PostgreSQL 13. Сейчас для UUID v4 используйте gen_random_uuid() из pgcrypto (или встроенную функцию в PG 13+). Если нужен UUID v7 (с временной сортировкой), генерируйте его на стороне приложения.

Добавить pg_stat_statements без shared_preload_libraries. Без этой настройки расширение создастся, но работать не будет — оно молча ничего не собирает до перезапуска с правильным конфигом.

Коротко

  • Расширения подключаются командой CREATE EXTENSION IF NOT EXISTS <name> и добавляют новые функции, типы и индексные алгоритмы в конкретную базу.
  • На любом проекте стоит включить pg_stat_statements (видеть медленные запросы), pgcrypto (UUID, bcrypt) и pg_trgm (поиск по подстроке с индексом).
  • pg_stat_statements требует shared_preload_libraries в конфиге и перезапуск кластера — сделать один раз при старте проекта.
  • btree_gist нужен для EXCLUDE-ограничений, которые комбинируют скалярные поля с диапазонами (например, бронирование).
  • citext избавляет от постоянного LOWER() при сравнении email и логинов.
  • pg_partman автоматизирует создание партиций по расписанию.
  • pg_repack реорганизует раздутые таблицы и индексы без блокировки — в отличие от VACUUM FULL.
  • hstore и uuid-ossp — устаревшие; для нового кода берите jsonb и gen_random_uuid().

Что почитать дальше

  • Типы индексов в PostgreSQL — как pg_trgm и btree_gist связаны с GIN и GiST.
  • Партиционирование — как работают партиции и когда они нужны.
  • VACUUM и раздувание таблиц — pgstattuple и pg_repack в контексте обслуживания.
  • Мониторинг медленных запросов — pg_stat_statements подробнее.