Расширения, которые часто нужны — pgcrypto, pg_trgm, btree_gist

Опирается на правила: PG-EXT-001 … PG-EXT-153 из PostgreSQL Style Guide → раздел Расширения.

Важно знать

• На любой проект: pg_stat_statements, pgcrypto, pg_trgm.
• pg_stat_statements требует shared_preload_libraries + рестарт.
• pgcrypto — gen_random_uuid(), crypt(bcrypt), digest().
• pg_trgm — триграммы для LIKE '%X%' и опечаток.
• btree_gist — composite GiST (scalar, range) для EXCLUDE.
• citext — case-insensitive text без LOWER() в каждом запросе.
• pg_partman — автоматизация партиций.
• pg_repack — реорганизация без блокировки (vs VACUUM FULL).
• hstore — legacy, для нового кода jsonb.
• uuid-ossp устарел — gen_random_uuid() из pgcrypto или клиентский UUID v7.

PostgreSQL имеет богатую экосистему расширений. UCP формулирует — что включать на любой проект.

Дефолтный набор

PG-EXT-001:

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

Почти ничего не стоят, всегда пригодятся.

pg_stat_statements

PG-EXT-010: мониторинг.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Требует рестарт кластера один раз.

CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Топ запросов по времени, числу вызовов, IO. Подробно — Мониторинг и slow queries.

pgcrypto

PG-EXT-020..022:

-- UUID v4 (с PG13+ можно без pgcrypto, но для совместимости проще)
SELECT gen_random_uuid();

-- 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);

-- хэши
SELECT digest('text', 'sha256');

Хэширование паролей в БД через pgcrypto работает, но обычно хэши делаются в коде (Spring Security BCryptPasswordEncoder) — пароль не уходит на сервер в plaintext.

Шифрование колонок (pgp_sym_encrypt) — антипаттерн. Лучше — шифровать на стороне приложения или TDE на уровне диска. БД не должна знать ключи.

pg_trgm

PG-EXT-030: триграммы.

CREATE EXTENSION pg_trgm;

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

-- LIKE '%X%' с индексом
SELECT * FROM customer WHERE full_name ILIKE '%иван%';

-- similarity для опечаток
SELECT * FROM customer
WHERE similarity(full_name, 'иванв') > 0.4
ORDER BY similarity(full_name, 'иванв') DESC LIMIT 10;

Подробно — Типы индексов, FTS.

btree_gist

PG-EXT-040: composite GiST для скалярных типов.

CREATE EXTENSION btree_gist;

-- EXCLUDE для непересечения интервалов с группировкой
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 &&)
);

-- composite для PostGIS
CREATE INDEX ix_shop_active_loc ON shop USING gist (is_active, location);

Подробно — Массивы и range, PostGIS.

citext

PG-EXT-050: 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';

Подробно — Строковые типы.

pgstattuple

PG-EXT-060: bloat-статистика.

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len, tuple_count, dead_tuple_count, dead_tuple_percent, free_space

SELECT * FROM pgstatindex('ix_orders_customer');
-- avg_leaf_density (низкая = bloated)

SELECT * FROM pgstattuple_approx('orders');   -- быстрее, приближённо

Точно, но медленно (читает всю таблицу). Подробно — VACUUM.

pg_partman

PG-EXT-070: автоматизация партиций.

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
);

-- cron:
SELECT partman.run_maintenance('public.event_log');

Без pg_partman — cron + ручной DDL. С ним — одна команда. Подробно — Партиционирование.

unaccent

PG-EXT-080: диакритика.

CREATE EXTENSION unaccent;

SELECT unaccent('Naïve café résumé');
-- Naive cafe resume

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

Применяется при индексации и в запросе.

hstore — legacy

PG-EXT-090..091:

CREATE EXTENSION hstore;
CREATE TABLE product (id bigint, attrs hstore);
INSERT INTO product VALUES (1, 'color => red, size => XL'::hstore);
SELECT * FROM product WHERE attrs->'color' = 'red';

Старый key-value, до JSONB. Для нового кода — jsonb, не hstore. Hstore только если поддерживаешь legacy.

uuid-ossp — устарел

PG-EXT-100..101:

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

Был стандартом до PG13. Для нового кода:

• UUID v4 — gen_random_uuid() из pgcrypto.
• UUID v7 — клиентская генерация (см. UUID).

tablefunc — crosstab pivot

PG-EXT-110:

CREATE EXTENSION tablefunc;

SELECT * FROM crosstab(
    'SELECT region, year, sum(amount) FROM sales GROUP BY 1, 2 ORDER BY 1, 2',
    'SELECT DISTINCT year FROM sales ORDER BY 1'
) AS ct (region text, y2024 numeric, y2025 numeric, y2026 numeric);

Pivot-таблицы (строки → колонки) для отчётов.

pg_repack

PG-EXT-120: реорганизация без блокировки.

pg_repack -d mydb -t order_doc        # таблица
pg_repack -d mydb -i ix_order_status  # индекс

Альтернатива VACUUM FULL без даунтайма. Подробно — VACUUM.

Что запрещено

Антипаттерн	Правило	Что взамен
Включать всё «на всякий случай»	PG-EXT-150	только нужные
Расширения без проверки доступности	PG-EXT-151	проверка в dev/prod образе
hstore в новом коде	PG-EXT-152	jsonb
pgp_sym_encrypt / шифрование на БД	PG-EXT-153	приложение или TDE
uuid_generate_v4() для нового	PG-EXT-101	gen_random_uuid() или клиентский v7
pg_stat_statements без shared_preload_libraries	PG-EXT-010	requires рестарт

Куда дальше

• PG → Расширения — нормативные формулировки.
• Типы индексов — pg_trgm, btree_gist.
• Массивы и range — EXCLUDE с btree_gist.
• PostGIS — postgis, btree_gist.
• Партиционирование — pg_partman.
• VACUUM — pgstattuple, pg_repack.
• Мониторинг — pg_stat_statements.
• Строковые типы — citext.
• UUID — gen_random_uuid().