Популярные расширения PostgreSQL: pg_trgm, uuid-ossp, hstore и другие

PostgreSQL расширяем как никакая другая СУБД. Сотни расширений добавляют новые типы данных, функции, индексы и операторы. Разберём самые полезные из тех, что входят в стандартную поставку.

Управление расширениями

-- Установить расширение
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Список установленных расширений
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

-- Все доступные расширения
SELECT name, comment FROM pg_available_extensions ORDER BY name;

-- Удалить расширение
DROP EXTENSION pg_trgm;

pg_trgm: нечёткий поиск и LIKE по подстрокам

CREATE EXTENSION pg_trgm;

-- GIN-индекс для быстрого LIKE/ILIKE
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- LIKE '%текст%' теперь использует индекс!
SELECT * FROM products WHERE name ILIKE '%ноутбук%';

-- Схожесть строк (0 — разные, 1 — одинаковые)
SELECT similarity('PostgreSQL', 'PostreSQL');  -- 0.73 (опечатка)

-- Поиск похожих товаров
SELECT name, similarity(name, 'ноутбок') AS sim
FROM products
WHERE name % 'ноутбок'  -- оператор: similarity > threshold (дефолт 0.3)
ORDER BY sim DESC;

-- Настроить порог схожести
SET pg_trgm.similarity_threshold = 0.4;

Типичный кейс: поиск с опечатками, поиск по подстроке в больших таблицах.

uuid-ossp: генерация UUID

CREATE EXTENSION "uuid-ossp";

SELECT uuid_generate_v4();  -- случайный UUID
-- d7c5f3a1-8b2e-4c9d-a0e7-1f3b5d6e8c2a

SELECT uuid_generate_v1();  -- UUID на основе MAC + timestamp

-- В PostgreSQL 13+ встроен gen_random_uuid() без расширения
SELECT gen_random_uuid();

hstore: хранилище ключ-значение

CREATE EXTENSION hstore;

CREATE TABLE products (
    id      SERIAL PRIMARY KEY,
    name    TEXT,
    attrs   hstore  -- произвольные атрибуты
);

INSERT INTO products (name, attrs) VALUES
    ('Ноутбук', 'ram => 16GB, storage => 512GB, color => black'),
    ('Мышь',    'dpi => 1600, buttons => 7, wireless => true');

-- Получить значение по ключу
SELECT name, attrs -> 'ram' AS ram FROM products;
SELECT name, attrs -> 'color' AS color FROM products WHERE attrs ? 'color';

-- Фильтр по ключу и значению
SELECT * FROM products WHERE attrs @> 'wireless => true';

-- Все ключи
SELECT name, akeys(attrs) AS keys FROM products;

-- Конвертировать в JSON
SELECT name, hstore_to_json(attrs) FROM products;

Когда: атрибуты товаров с разными свойствами. Сегодня лучше использовать JSONB — мощнее и поддерживает больше типов данных.

pg_stat_statements: анализ медленных запросов

CREATE EXTENSION pg_stat_statements;

-- В postgresql.conf добавить:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Топ-10 самых медленных запросов
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    calls,
    round(rows::numeric / calls, 1) AS avg_rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Самые часто вызываемые
SELECT calls, round(mean_exec_time::numeric, 2) AS avg_ms, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Сбросить статистику
SELECT pg_stat_statements_reset();

Незаменим для поиска узких мест производительности.

pgcrypto: шифрование и хеширование

CREATE EXTENSION pgcrypto;

-- Хеш пароля (bcrypt)
SELECT crypt('my_password', gen_salt('bf', 8));
-- $2a$08$...

-- Проверка пароля
SELECT crypt('my_password', stored_hash) = stored_hash AS is_valid;

-- MD5 (не для паролей! только для чексумм)
SELECT MD5('text');

-- SHA-256
SELECT encode(digest('text', 'sha256'), 'hex');

-- Симметричное шифрование
SELECT pgp_sym_encrypt('secret data', 'encryption_key');
SELECT pgp_sym_decrypt(encrypted_data, 'encryption_key');

-- Генерация случайных данных
SELECT encode(gen_random_bytes(16), 'hex') AS random_token;

ltree: иерархические данные

CREATE EXTENSION ltree;

CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    path ltree NOT NULL  -- 'Electronics.Computers.Laptops'
);

CREATE INDEX idx_categories_path ON categories USING gist(path);

INSERT INTO categories (path) VALUES
    ('Electronics'),
    ('Electronics.Computers'),
    ('Electronics.Computers.Laptops'),
    ('Electronics.Phones');

-- Найти всех потомков Electronics
SELECT * FROM categories WHERE path <@ 'Electronics';

-- Найти всех предков Laptops
SELECT * FROM categories WHERE path @> 'Electronics.Computers.Laptops';

-- Сопоставление по шаблону
SELECT * FROM categories WHERE path ~ 'Electronics.*{1}';  -- прямые потомки

-- Уровень в иерархии
SELECT path, nlevel(path) AS depth FROM categories;

Когда: иерархические данные с частыми запросами на поиск предков/потомков — значительно быстрее рекурсивных CTE.

tablefunc: CROSSTAB (pivot)

CREATE EXTENSION tablefunc;

-- Преобразовать строки в столбцы (pivot)
SELECT * FROM crosstab(
    'SELECT user_id, metric_name, value
     FROM metrics
     ORDER BY 1, 2',
    'SELECT DISTINCT metric_name FROM metrics ORDER BY 1'
) AS ct(
    user_id INTEGER,
    dau NUMERIC,
    revenue NUMERIC,
    sessions INTEGER
);

intarray: операции с массивами целых чисел

CREATE EXTENSION intarray;

-- Быстрые операции с INTEGER[]
SELECT ARRAY[1,2,3] | ARRAY[2,3,4];   -- объединение: {1,2,3,4}
SELECT ARRAY[1,2,3] & ARRAY[2,3,4];   -- пересечение: {2,3}
SELECT ARRAY[1,2,3,4] - ARRAY[2,4];   -- разность: {1,3}
SELECT sort(ARRAY[3,1,2]);             -- сортировка: {1,2,3}

-- GIN/GIST индексы для целочисленных массивов
CREATE INDEX ON posts USING gin(tag_ids gin__int_ops);

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

CREATE EXTENSION citext;

-- Тип citext всегда сравнивается без учёта регистра
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email CITEXT UNIQUE  -- 'Alice@EXAMPLE.COM' = 'alice@example.com'
);

INSERT INTO users (email) VALUES ('Alice@Example.com');

-- Найти без LOWER():
SELECT * FROM users WHERE email = 'alice@example.com';  -- найдёт!

Альтернатива функциональному индексу CREATE INDEX ON users (LOWER(email)).

Итог: когда какое расширение