Абдулхамид Эшмаматов

VIEW — Виртуальная таблица

VIEW — именованный запрос, сохранённый в базе данных. При каждом обращении он выполняет исходный SQL заново — кешированного результата нет. Представления идеальны для упрощения сложных JOIN-ов, разграничения доступа и создания переиспользуемой логики запросов.

CREATE VIEW active_users AS
  SELECT id, username, email
  FROM users
  WHERE is_active = TRUE;

Применение: инкапсуляция бизнес-логики; дополнительное дисковое пространство не используется. Каждый запрос к представлению повторно выполняет SQL.

MATERIALIZED VIEW — Кешированные результаты запроса

MATERIALIZED VIEW физически сохраняет результат запроса на диске. В отличие от обычного представления, оно не выполняется заново при каждом обращении — что делает его значительно быстрее для тяжёлых агрегаций. В UZINFOCOM мы достигли ускорения запросов в 100×, заменив медленные подзапросы на аналитических дашбордах материализованными представлениями. Компромисс: данные нужно обновлять явно через REFRESH MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT DATE_TRUNC('month', created_at) AS month,
         SUM(amount) AS total
  FROM orders
  GROUP BY 1;

-- Обновление без блокировки чтений:
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Совет: используйте CONCURRENTLY, чтобы избежать блокировок при обновлении; требуется уникальный индекс на материализованном представлении.

INDEX — Быстрый поиск

Индексы позволяют PostgreSQL находить строки без полного сканирования таблицы. Стандартный индекс B-Tree обрабатывает запросы на равенство и диапазоны. Другие типы: GIN (полнотекстовый поиск, JSONB), GiST (геометрические / диапазонные типы), BRIN (очень большие, физически упорядоченные таблицы) и Hash (только равенство).

-- Стандартный B-Tree
CREATE INDEX idx_orders_user ON orders(user_id);

-- Частичный индекс (только активные строки)
CREATE INDEX idx_active_orders ON orders(user_id)
  WHERE status = 'active';

-- Составной индекс
CREATE INDEX idx_user_date ON orders(user_id, created_at DESC);

-- GIN для JSONB
CREATE INDEX idx_meta ON events USING GIN (metadata);

Избыточное индексирование замедляет запись. Проверяйте неиспользуемые индексы через pg_stat_user_indexes.

VACUUM — Очистка мёртвых кортежей

PostgreSQL использует MVCC (многоверсионное управление параллельным доступом): старые версии строк (мёртвые кортежи) остаются на диске после UPDATE и DELETE. VACUUM освобождает это пространство и обновляет карты видимости. VACUUM FULL полностью переписывает таблицу (блокирует её — избегайте в продакшене без окна обслуживания). AUTOVACUUM делает это автоматически; настраивайте его для таблиц с интенсивной записью.

-- Стандартный vacuum (без блокировки)
VACUUM orders;

-- С обновлением статистики
VACUUM ANALYZE orders;

-- Проверить раздутость таблиц
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

РЕПЛИКАЦИЯ — Высокая доступность

PostgreSQL поддерживает потоковую репликацию (физическая, побайтная копия WAL-логов) и логическую репликацию (изменения на уровне строк, выборочная по таблицам, межверсионная). В продакшн-системах UZINFOCOM мы используем схему primary + standby с синхронным коммитом для критических путей записи и асинхронным для реплик чтения, обслуживающих аналитику.

-- primary: postgresql.conf
wal_level = replica
max_wal_senders = 5
synchronous_standby_names = 'standby1'

-- standby: recovery.conf / postgresql.conf
primary_conninfo = 'host=primary port=5432 user=replicator'
hot_standby = on

-- Логическая репликация
CREATE PUBLICATION my_pub FOR TABLE orders, users;
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary dbname=app user=replicator'
  PUBLICATION my_pub;

Мониторинг задержки репликации: SELECT * FROM pg_stat_replication;

EXPLAIN / EXPLAIN ANALYZE — Анализ плана запроса

EXPLAIN показывает план выполнения запроса, который будет использовать PostgreSQL (оценочные затраты, количество строк, стратегии соединений). EXPLAIN ANALYZE фактически выполняет запрос и добавляет реальные данные о времени — незаменим для выявления медленных последовательных сканирований, плохих оценок строк или nested loop на больших наборах данных.

-- Только план (без выполнения)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- План + реальное время (выполняет запрос)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT u.username, COUNT(o.id)
  FROM users u
  JOIN orders o ON o.user_id = u.id
  GROUP BY u.username
  ORDER BY 2 DESC;

Ключевые узлы для анализа: Seq Scan (нет индекса?), Hash Join против Nested Loop, высокое значение rows removed by filter. Используйте explain.dalibo.com для визуализации планов.