Расширения PostgreSQL: pg_trgm, pgcrypto, pg_stat_statements и другие

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

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: файлы уже лежат на сервере, и остаётся только подключить их в нужной базе.

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

Есть три расширения, которые сами по себе почти ничего не стоят — они лишь добавляют в базу функции, операторы и типы индексов, — но регулярно нужны: pg_stat_statements, чтобы найти медленный запрос на рабочем сервере; pgcrypto ради UUID и хешей паролей; pg_trgm для поиска по подстроке. Каждое разобрано ниже, а подключить их лучше сразу, потом не придётся вспоминать в нужный момент:

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:

-- в PostgreSQL 13 и новее работает и без расширения
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);

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

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

-- хэш считается на стороне базы
SELECT digest('some text', 'sha256');

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

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

Поиск по куску строки — LIKE '%tit%' — в PostgreSQL не может опереться на B-tree-индекс: тот упорядочен по началу строки, а искомый кусок стоит где угодно. База вынуждена перебрать каждую строку таблицы, и на больших таблицах это очень медленно. Сам запрос при этом выглядит невинно:

живой пример

SELECT id, first_name, last_name
FROM customer
WHERE last_name ILIKE '%tit%';

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

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_last_name_trgm
    ON customer USING gin (last_name gin_trgm_ops);

Само расширение бесплатное, а вот индекс — нет, и это стоит знать заранее. Триграммный GIN хранит ключ на каждую тройку символов из каждой строки, так что на текстовой колонке он легко выходит крупнее самой таблицы — не «процентов на десять больше», а в разы. Запись тоже дорожает: на каждой вставке и на каждом изменении этой колонки база режет новое значение на тройки и правит индекс по каждой из них. На справочнике фамилий это незаметно, на таблице с миллионами описаний товаров — вполне. Поэтому размер смотрят сразу после сборки:

SELECT pg_size_pretty(pg_relation_size('ix_customer_last_name_trgm'));

Ещё пять, о которых стоит знать по именам

pgvector — хранение и поиск векторов. Это расширение, ради которого сегодня чаще всего трогают PostgreSQL: оно даёт тип vector, операторы расстояния (<->, <=>) и индексы приближённого поиска (HNSW, IVFFlat). Под него ложатся поиск по смыслу, похожие товары и подбор фрагментов для ответов языковой модели. Если такая задача есть, отдельное хранилище векторов часто оказывается лишним: пока векторов миллионы, а не сотни миллионов, база справляется, и данные не приходится синхронизировать между двумя системами.

auto_explain — план запроса, который уже отработал. pg_stat_statements отвечает, какие запросы съели больше всего времени, но планов не хранит; auto_explain пишет в журнал план каждого запроса дольше заданного порога, в том числе ночного и с теми самыми параметрами. Требует shared_preload_libraries и перезапуска, разбирается подробно в статье про EXPLAIN.

pg_cron — расписание внутри базы. Задание описывается строкой вида cron.schedule('nightly-refresh', '0 3 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales') и выполняется самим сервером. Это ответ сразу на две соседние задачи: обновление материализованных представлений и создание новых частей у разбитых таблиц через pg_partman, который без планировщика не работает. Важно: задания принадлежат конкретной базе, выполняются от имени создавшей их роли, а на переключении мастера выполняются там, где сейчас основной сервер, — то есть дублирования не возникает, но и на реплике ничего не идёт.

postgres_fdw и dblink — обращение к другой базе. Первое подключает чужую таблицу как свою (внешняя таблица), второе выполняет запрос и возвращает результат. Удобно для разовых сверок и переездов; для постоянной работы — осторожно: план строится по чужой статистике, а сетевой круг превращает обычный отчёт в медленный.

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

Расширения в управляемой базе

Всё вышеописанное предполагает, что сервер ваш. В управляемой базе (RDS, облачные PostgreSQL) это не так, и порядок другой.

Список доступных расширений закрыт и определяется поставщиком: CREATE EXTENSION для того, чего нет в списке, просто не выполнится, и поставить его некуда — доступа к файловой системе сервера нет. Проверяют список заранее:

живой пример

SELECT name, default_version, installed_version FROM pg_available_extensions ORDER BY name;

shared_preload_libraries правится не в postgresql.conf, а через параметры кластера в панели управления, и почти всегда требует перезапуска — то есть окна обслуживания. Именно так включают pg_stat_statements и auto_explain.

А внешние утилиты вроде pg_repack могут быть недоступны вовсе: это не расширение в чистом виде, а расширение плюс программа, которой нужно подключение с повышенными правами. В управляемых базах его иногда предлагают как отдельную возможность, иногда нет, и тогда перестройку таблицы делают другими средствами — например, переливом в новую таблицу.

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

Жизненный цикл расширения

Три эксплуатационных момента, которые всплывают позже установки.

Обновление. Версия расширения живёт отдельно от версии сервера. После обновления сервера или пакетов файлы новой версии уже на диске, а база продолжает использовать старую, пока ей не скажут: ALTER EXTENSION pg_stat_statements UPDATE;. Что установлено сейчас и что доступно, показывают installed_version и default_version в pg_available_extensions.

Копии и перенос. pg_dump не выгружает содержимое расширения — он пишет только строку CREATE EXTENSION. Значит, при восстановлении расширение должно быть доступно на целевом сервере, иначе восстановление упадёт на первой же зависимой таблице. То же при мажорном обновлении: сначала ставят расширения нужных версий, потом переносят данные.

Отдельная схема. Ставить расширение стоит не в public, а в отдельную схему: CREATE EXTENSION pg_trgm SCHEMA ext;. Тогда его объекты не смешиваются с вашими таблицами, их видно отдельно в дереве клиента, а права выдаются на схему целиком. Плата — ext надо добавить в search_path, иначе функции придётся называть полностью.

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

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

Использовать hstore в новом коде. hstore появился до JSONB и хранит только плоские пары «строка — строка»: ни вложенности, ни чисел, ни массивов. Расширение живо и поддерживается, но для нового кода берите jsonb — он умеет больше.

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

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

Глубже: pg_trgm — арифметика триграмм и similarityрасширенное

Индекс построен, а дальше о том, что именно расширение кладёт в него и как считает похожесть.

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

import java.util.LinkedHashSet;
import java.util.Locale;
import java.util.Set;

public class Trigram {

    static Set<String> split(String text) {
        String padded = "  " + text.toLowerCase(Locale.ROOT) + " ";
        Set<String> parts = new LinkedHashSet<>();
        for (int i = 0; i + 3 <= padded.length(); i++) {
            parts.add(padded.substring(i, i + 3));
        }
        return parts;
    }

    static double similarity(String a, String b) {
        Set<String> shared = new LinkedHashSet<>(split(a));
        shared.retainAll(split(b));
        Set<String> all = new LinkedHashSet<>(split(a));
        all.addAll(split(b));
        return (double) shared.size() / all.size();
    }

    public static void main(String[] args) {
        System.out.println("ivanov -> " + split("ivanov"));
        System.out.printf(Locale.ROOT, "ivanov ~ ivannov = %.2f%n", similarity("ivanov", "ivannov"));
        System.out.printf(Locale.ROOT, "ivanov ~ petrov  = %.2f%n", similarity("ivanov", "petrov"));
    }
}

package main

import (
	"fmt"
	"strings"
)

func split(text string) []string {
	padded := []rune("  " + strings.ToLower(text) + " ")
	var parts []string
	seen := map[string]bool{}
	for i := 0; i+3 <= len(padded); i++ {
		part := string(padded[i : i+3])
		if !seen[part] {
			seen[part] = true
			parts = append(parts, part)
		}
	}
	return parts
}

func similarity(a, b string) float64 {
	all := map[string]bool{}
	shared := 0
	for _, p := range split(a) {
		all[p] = true
	}
	for _, p := range split(b) {
		if all[p] {
			shared++
		}
		all[p] = true
	}
	return float64(shared) / float64(len(all))
}

func main() {
	fmt.Printf("ivanov -> %q\n", split("ivanov"))
	fmt.Printf("ivanov ~ ivannov = %.2f\n", similarity("ivanov", "ivannov"))
	fmt.Printf("ivanov ~ petrov  = %.2f\n", similarity("ivanov", "petrov"))
}

function split(text) {
  const padded = "  " + text.toLowerCase() + " ";
  const parts = new Set();
  for (let i = 0; i + 3 <= padded.length; i++) parts.add(padded.slice(i, i + 3));
  return parts;
}

function similarity(a, b) {
  const first = split(a);
  const second = split(b);
  const shared = [...first].filter((p) => second.has(p)).length;
  const all = new Set([...first, ...second]).size;
  return shared / all;
}

console.log("ivanov ->", [...split("ivanov")]);
console.log("ivanov ~ ivannov =", similarity("ivanov", "ivannov").toFixed(2));
console.log("ivanov ~ petrov  =", similarity("ivanov", "petrov").toFixed(2));

def split(text):
    padded = "  " + text.lower() + " "
    return list(dict.fromkeys(padded[i:i + 3] for i in range(len(padded) - 2)))


def similarity(a, b):
    first, second = set(split(a)), set(split(b))
    return len(first & second) / len(first | second)


print("ivanov ->", split("ivanov"))
print(f"ivanov ~ ivannov = {similarity('ivanov', 'ivannov'):.2f}")
print(f"ivanov ~ petrov  = {similarity('ivanov', 'petrov'):.2f}")

Печатается набор [ i, iv, iva, van, ano, nov, ov ] и две оценки: 0.67 для опечатки и 0.08 для чужой фамилии. Ровно это и делает функция similarity — возвращает число от 0 до 1, где 1 — полное совпадение. Те же тройки индекс хранит как ключи GIN.

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

Это полезно для форм с автодополнением и нечёткого поиска по справочникам. Есть и короткая запись — оператор %, у которого порог похожести берётся из параметра pg_trgm.similarity_threshold (по умолчанию 0.3).

Глубже: btree_gist — ограничение непересечениярасширенное

Одну комнату забронировали дважды на пересекающиеся даты: проверку в коде обошёл второй параллельный запрос, который прошёл её на секунду позже первого. В базе для этого есть ограничение целостности EXCLUDE: оно запрещает строкам «пересекаться» по заданному условию, и обойти его нельзя.

Само по себе EXCLUDE к GiST не привязано: с обычным равенством оно прекрасно работает и через B-tree — EXCLUDE USING btree (a WITH =) это просто длинная запись UNIQUE. Загвоздка появляется, когда в одном ограничении нужно смешать две разные проверки: room_id сравнивается на равенство, а period — на пересечение. Пересечение диапазонов умеет только GiST, значит и индекс обязан быть GiST. А вот равенства для обычных чисел и текста GiST из коробки не знает — не его задача. Эту дырку и закрывает btree_gist: он учит 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 считаются одним значением, и второй такой адрес в таблицу уже не вставить.

Оговорка, без которой совет устарел: с PostgreSQL 12 ту же задачу решают правила сравнения без учёта регистра (недетерминированные ICU-коллации), и в новых схемах обычно берут их, а не citext. У самого расширения есть углы: поведение LIKE с ним отличается от ожидаемого (шаблон сравнивается уже без учёта регистра, и привычный ILIKE становится избыточным), результат зависит от локали базы, а сменить правила сравнения у готовой колонки нельзя — только пересоздать. Подробный разбор с примерами — в статье про строковые типы.

Глубже: 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 или задуматься о перестройке.

Две оговорки, без которых на рабочем сервере выйдет неловко. Первая: эти функции по умолчанию закрыты — вызвать их может владелец базы и роли, входящие в pg_stat_scan_tables; обычной роли приложения права придётся выдать отдельно. Вторая важнее. Слово «точно измерить» надо понимать буквально: pgstattuple('orders') считает не по статистике, а честным чтением — проходит таблицу целиком. На таблице в 200 гигабайт это не «измерить раздувание», а прочитать 200 гигабайт и заодно вытеснить из кэша всё остальное. Поэтому на проде берут pgstattuple_approx: он пропускает страницы, про которые база и так знает, что мёртвых строк там нет, и отвечает быстро. Точный вариант остаётся для небольших таблиц и для копии базы.

Глубже: unaccent — поиск без учёта диакритикирасширенное

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

CREATE EXTENSION unaccent;

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

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

живой пример

SELECT id, title FROM products
WHERE search @@ plainto_tsquery('russian', unaccent('наушники'));

Глубже: pg_partman — автоматизация партицийрасширенное

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

pg_partman автоматизирует процесс: он сам создаёт будущие партиции заранее и при необходимости удаляет старые. В версии 5 остались только нативные партиции, а у create_parent обязательных параметров три — таблица, колонка и интервал:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

SELECT partman.create_parent(
    p_parent_table => 'public.event_log',
    p_control      => 'occurred_at',
    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  # перестроить индекс

Это внешняя утилита: её ставят на сервер отдельно.

И у неё два условия, про которые лучше узнать заранее, а не из сообщения об ошибке в три часа ночи. Первое: таблице нужен первичный ключ или хотя бы уникальный индекс по колонке NOT NULL. Без него pg_repack не сможет сопоставить строки копии с оригиналом и просто откажется работать. Второе: пока идёт реорганизация, на диске лежат обе копии таблицы вместе с индексами — свободного места нужно примерно вдвое больше её размера. Раздутую таблицу на 300 гигабайт на диске, где свободна сотня, перепаковать не выйдет.

Глубже: база тормозит прямо сейчас: порядок действийрасширенное

pg_stat_statements показывает, что было плохо в среднем. Когда плохо в эту минуту, нужен другой порядок.

Первое, что происходит сейчас: pg_stat_activity. Смотрят активные сессии, кто ждёт (wait_event_type = 'Lock') и кто давно в транзакции.

живой пример

SELECT pid, now() - xact_start AS in_tx, state, wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY xact_start;

Второе, кто виноват: pg_blocking_pids(pid) для тех, кто ждёт, а для тяжёлых запросов верх pg_stat_statements по total_exec_time за последние минуты (сбросив статистику pg_stat_statements_reset() перед наблюдением, если хочется видеть только текущее). Третье, что делать: pg_cancel_backend(pid) прерывает запрос, оставляя сессию, pg_terminate_backend(pid) обрывает сессию целиком, это для idle in transaction, которая держит замки.

Отдельный сценарий, когда соединения кончились и в базу не войти. max_connections исчерпан, каждый новый вход отвечает too many clients. На этот случай PostgreSQL держит superuser_reserved_connections (по умолчанию три): суперпользователь войдёт, когда обычные роли уже нет, и сможет снять лишние сессии. Поэтому от суперпользователя приложение не ходит никогда: иначе резерв съест оно же.

Чтобы всё это случалось реже, таймауты ставят политикой, а не разовой настройкой в миграции. statement_timeout для роли приложения (секунды) обрывает запрос, который побежал не по индексу; idle_in_transaction_session_timeout (десятки секунд) закрывает транзакции, забытые приложением; lock_timeout не даёт миграции стоять в очереди за замком. Аналитику, которая по ночам укладывает базу, выводят в отдельную роль со своим statement_timeout и work_mem и, когда возможно, на реплику:

ALTER ROLE shop_app SET statement_timeout = '5s';
ALTER ROLE shop_app SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE reports SET statement_timeout = '10min';
ALTER ROLE reports SET work_mem = '256MB';

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

Коротко

• Расширение — пакет SQL-объектов, который подключается командой CREATE EXTENSION IF NOT EXISTS <name> и действует на одну базу, а не на весь кластер. Что подключено и что доступно, показывает pg_available_extensions (installed_version против default_version); ставить лучше в отдельную схему.
• На любом проекте стоит сразу включить три: pg_stat_statements (найти медленный запрос на рабочем сервере), pgcrypto (UUID, bcrypt) и pg_trgm (поиск по подстроке с индексом).
• pg_stat_statements требует shared_preload_libraries и перезапуск кластера (в управляемой базе — через панель), иначе расширение создастся и молча ничего не соберёт. Первый запрос при разборе производительности — топ по total_exec_time DESC; план уже случившегося запроса даёт auto_explain, включаемый тем же способом.
• pgcrypto даёт gen_random_uuid() (в PostgreSQL 13+ есть и без расширения), bcrypt через crypt(..., gen_salt('bf', 10)) и digest(..., 'sha256'). Шифровать колонки через pgp_sym_encrypt не стоит: база тогда знает ключи.
• pg_trgm режет текст на тройки символов и кладёт их в GIN (gin_trgm_ops): LIKE '%tit%' начинает пользоваться индексом без переписывания запроса; similarity — доля общих троек, оператор % сравнивает её с порогом 0.3. Сам индекс не бесплатный — в разы крупнее колонки и удорожает запись.
• btree_gist нужен для EXCLUDE USING gist (room_id WITH =, period WITH &&); citext избавляет от LOWER() для почты и логинов, но в новых схемах его заменяют правилами сравнения без учёта регистра.
• pgstattuple измеряет раздувание честным чтением всей таблицы, на проде берут pgstattuple_approx; pg_repack реорганизует раздутую таблицу без длительной блокировки в отличие от VACUUM FULL, но требует первичного ключа и вдвое больше свободного места; pg_partman создаёт партиции по расписанию.
• Не включать всё «про запас»; hstore и uuid_generate_v4() из uuid-ossp в новом коде не нужны. По именам стоит знать ещё pgvector (векторный поиск), pg_cron (расписание внутри базы, нужен pg_partman и обновлению представлений), postgres_fdw и amcheck.
• «Тормозит сейчас»: pg_stat_activity (кто ждёт, кто давно в транзакции), pg_blocking_pids, верх pg_stat_statements, потом pg_cancel_backend или pg_terminate_backend; резерв superuser_reserved_connections спасает вход, когда соединения кончились. Таймауты при этом задают политикой на роль: statement_timeout и idle_in_transaction_session_timeout приложению, отдельная роль с большим лимитом и work_mem отчётам.
• В управляемой базе список расширений закрыт, а внешние утилиты вроде pg_repack могут быть недоступны: список проверяют до старта проекта. Версию расширения после обновления сервера поднимают ALTER EXTENSION … UPDATE, а pg_dump переносит только строку CREATE EXTENSION.

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

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