Audit - pg_audit

PostgreSQL Health Check

📱 Telegram: pg_audit_cases

🚨 КРИТИЧЕСКОЕ ПРЕДУПРЕЖДЕНИЕ 🚨

База данных работает всего 0.6 часов (требуется минимум 24 часа для статистически значимых данных).

ВСЕ статистические выводы (Cache Hit Ratio, Dead Tuples, Unused Indexes) имеют пометку LOW CONFIDENCE и требуют подтверждения.

Детерминированные правила (Primary Key, Foreign Key) — НЕ зависят от uptime и являются достоверными.

⚠️ Важное уведомление: Данный отчёт сформирован на основе экспертной методологии анализа метаданных PostgreSQL (размеры, счётчики, структуры). Тексты SQL-запросов, имена пользователей и содержимое данных не собирались и не анализировались, что ограничивает глубину диагностики по ряду параметров.

Все SQL-команды составлены в соответствии с лучшими практиками администрирования PostgreSQL и проверены на корректность синтаксиса. Применимость каждой рекомендации обусловлена спецификой вашей инфраструктуры — перед внедрением на Production обязательно протестируйте изменения на staging-окружении и убедитесь в наличии актуальных резервных копий.

Результаты анализа предоставляются «как есть» (as-is). Автор не несёт ответственности за сбои, потерю данных или простои, возникшие вследствие применения рекомендаций без предварительного тестирования в условиях конкретной инфраструктуры.

ℹ️ Пояснения к рекомендациям:

work_mem: Рекомендуемое значение рассчитано индивидуально под конфигурацию вашего сервера (max_connections). Формула безопасности: work_mem × max_connections ≤ 6 GB. При изменении нагрузки пересчитайте пропорционально.
Cache Hit Ratio: Порог 90% — универсальный индикатор здоровья БД. Для OLTP-систем рекомендуется ≥99%, для аналитических (DWH) допустимы отклонения.
Partial Index: Рекомендация основана на эвристическом анализе имен столбцов (флаги/статусы). Перед созданием убедитесь в неравномерном распределении данных (например, 95%/5%).
Оценка раздутия таблиц: Является приблизительной (без расширения pgstattuple). Для точной диагностики установите pgstattuple.
Общее: Рекомендации с пометкой LOW CONFIDENCE требуют дополнительного ручного анализа. Все изменения параметров через ALTER SYSTEM можно отменить через ALTER SYSTEM RESET.

Critical

High

Medium

Low

Info

🔴 Срочные действия

[CRITICAL] Нет Primary Key — Строк: 1,000,000
[CRITICAL] Autovacuum глобально отключён — current_setting('autovacuum') = 'off' — автоматическое обслуживание ВСЕХ таблиц
[HIGH] Нет Primary Key — Строк: 100
[HIGH] Нет Primary Key — Строк: 10
[HIGH] Мертвые кортежи — Доля: 51.7% | Dead: 516,957 | Live: 1,000,000

✅ Быстрые победы

-- Нет Primary Key
-- Шаг 1: Добавить колонку, если её нет (быстро, без блокировки)
ALTER TABLE "public"."pgbench_accounts" ADD COLUMN IF NOT EXISTS id BIGSERIAL;
-- Шаг 2: Создать уникальный индекс CONCURRENTLY (без блокировки записи)
CREATE UNIQUE INDEX CONCURRENTLY "pgbench_accounts_pkey" ON "public"."pgbench_accounts"(id);
-- Шаг 3: Назначить PK через готовый индекс (мгновенно)
ALTER TABLE "public"."pgbench_accounts" ADD PRIMARY KEY USING INDEX "pgbench_accounts_pkey";
-- Autovacuum глобально отключён
ALTER SYSTEM SET autovacuum = 'on';
SELECT pg_reload_conf();

📋 План исправлений

#	Приоритет	Проблема	Объект	Риск	Время
1	Срочно	Нет Primary Key	public.pgbench_accounts	🟠	~1 час
2	Срочно	Autovacuum глобально отключён	Database	🟢	~1 час
3	Рекомендуется	Нет Primary Key	public.pgbench_tellers	🟠	~30 мин
4	Рекомендуется	Нет Primary Key	public.pgbench_branches	🟠	~30 мин
5	Рекомендуется	Мертвые кортежи	public.pgbench_accounts	🟢	~30 мин
6	Рекомендуется	Много Seq Scan	public.pgbench_accounts	🟡	~30 мин
7	Рекомендуется	work_mem слишком мал	Database	🟡	~30 мин

Всего: 2 срочных, 6 рекомендуемых, 3 наблюдений | 🟢 Низкий 🟡 Средний 🟠 Высокий

📊 Бенчмарки

Метрика	Значение	Норма	Оценка
Cache Hit Ratio	98.2% норма >99%	>99%	⚠️ НЕДОСТАТОЧНО ДАННЫХ (uptime < 24ч)
work_mem	0.1MB	≥4MB (рек. ≥64MB)	Критично

⚠️ Статистика собрана за 0.6 часов работы БД. Для достоверной оценки требуется минимум 24 часа.

🔴 CRITICAL Issues 2

CRITICAL DETERMINISTIC MUST_FIX

Нет Primary Key

public.pgbench_accounts

Impact: Блокирует логическую репликацию, усложняет обслуживание

Строк: 1,000,000

Recommendation: Добавьте Primary Key

🟠 Высокий риск

-- Шаг 1: Добавить колонку, если её нет (быстро, без блокировки)
ALTER TABLE "public"."pgbench_accounts" ADD COLUMN IF NOT EXISTS id BIGSERIAL;
-- Шаг 2: Создать уникальный индекс CONCURRENTLY (без блокировки записи)
CREATE UNIQUE INDEX CONCURRENTLY "pgbench_accounts_pkey" ON "public"."pgbench_accounts"(id);
-- Шаг 3: Назначить PK через готовый индекс (мгновенно)
ALTER TABLE "public"."pgbench_accounts" ADD PRIMARY KEY USING INDEX "pgbench_accounts_pkey";

Confidence: Deterministic rule — high confidence (99%)

CRITICAL DETERMINISTIC MUST_FIX

Autovacuum глобально отключён

Database

Impact: Мёртвые кортежи не очищаются ни на одной таблице, база раздувается, XID wraparound становится реальной угрозой

current_setting('autovacuum') = 'off' — автоматическое обслуживание ВСЕХ таблиц подавлено

Recommendation: Включите autovacuum на уровне сервера

🟢 Низкий риск

ALTER SYSTEM SET autovacuum = 'on';
SELECT pg_reload_conf();

Confidence: Deterministic rule — high confidence (99%)

🟠 HIGH Issues 3

HIGH DETERMINISTIC SHOULD_FIX

Нет Primary Key

public.pgbench_tellers

Impact: Блокирует логическую репликацию, усложняет обслуживание

Строк: 100

Recommendation: Добавьте Primary Key

🟠 Высокий риск

-- Шаг 1: Добавить колонку, если её нет (быстро, без блокировки)
ALTER TABLE "public"."pgbench_tellers" ADD COLUMN IF NOT EXISTS id BIGSERIAL;
-- Шаг 2: Создать уникальный индекс CONCURRENTLY (без блокировки записи)
CREATE UNIQUE INDEX CONCURRENTLY "pgbench_tellers_pkey" ON "public"."pgbench_tellers"(id);
-- Шаг 3: Назначить PK через готовый индекс (мгновенно)
ALTER TABLE "public"."pgbench_tellers" ADD PRIMARY KEY USING INDEX "pgbench_tellers_pkey";

Confidence: Deterministic rule — high confidence (99%)

HIGH DETERMINISTIC SHOULD_FIX

Нет Primary Key

public.pgbench_branches

Impact: Блокирует логическую репликацию, усложняет обслуживание

Строк: 10

Recommendation: Добавьте Primary Key

🟠 Высокий риск

-- Шаг 1: Добавить колонку, если её нет (быстро, без блокировки)
ALTER TABLE "public"."pgbench_branches" ADD COLUMN IF NOT EXISTS id BIGSERIAL;
-- Шаг 2: Создать уникальный индекс CONCURRENTLY (без блокировки записи)
CREATE UNIQUE INDEX CONCURRENTLY "pgbench_branches_pkey" ON "public"."pgbench_branches"(id);
-- Шаг 3: Назначить PK через готовый индекс (мгновенно)
ALTER TABLE "public"."pgbench_branches" ADD PRIMARY KEY USING INDEX "pgbench_branches_pkey";

Confidence: Deterministic rule — high confidence (99%)

HIGH STATISTICAL SHOULD_FIX ⚠️ LOW CONF

Мертвые кортежи

public.pgbench_accounts

Impact: VACUUM отстает, таблица раздувается, запросы медленнее

Доля: 51.7% | Dead: 516,957 | Live: 1,000,000

Recommendation: Выполните VACUUM ANALYZE

🟢 Низкий риск

VACUUM ANALYZE "public"."pgbench_accounts";

Confidence: ⚠️ LOW CONFIDENCE: uptime 0.6h < 24h

🟡 MEDIUM Issues 3

MEDIUM STATISTICAL SHOULD_FIX ⚠️ LOW CONF

Много Seq Scan

public.pgbench_accounts

Impact: Высокая нагрузка на I/O, медленные запросы

2,000,000 строк прочитано через Seq Scan

Recommendation: Создайте индексы на столбцы фильтрации WHERE

🟡 Средний риск

-- CREATE INDEX CONCURRENTLY idx_pgbench_accounts_col ON "public"."pgbench_accounts" (col);

Confidence: ⚠️ LOW CONFIDENCE: uptime 0.6h < 24h

MEDIUM DETERMINISTIC SHOULD_FIX

work_mem слишком мал

Database

Impact: Сортировки и хэш-таблицы spills на диск, запросы медленнее

work_mem = 0.1MB (порог: 4MB) | max_connections: 100

Recommendation: Увеличьте work_mem до 64MB или более

🟡 Средний риск

ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

Confidence: Deterministic rule — high confidence (60%)

MEDIUM DETERMINISTIC SHOULD_FIX

Не установлен log_min_duration_statement

Database

Impact: Невозможно выявить медленные запросы без логирования

log_min_duration_statement = -1 (логирование медленных запросов отключено)

Recommendation: Включите логирование запросов длиннее 1 секунды

🟡 Средний риск

ALTER SYSTEM SET log_min_duration_statement = '1000';
SELECT pg_reload_conf();

Confidence: Deterministic rule — high confidence (99%)

🔵 LOW Issues 1

LOW HEURISTIC OBSERVE ⚠️ LOW CONF

Отсутствие индекса на большой таблице

public.pgbench_accounts

Impact: ~580 MB прочитано через Seq Scan

Размер: 193.3 MB | Seq Scans: 3.0

Recommendation: Создайте индекс на часто фильтруемые столбцы

🟡 Средний риск

-- CREATE INDEX CONCURRENTLY idx_pgbench_accounts_col ON "public"."pgbench_accounts" (col);

Confidence: ⚠️ LOW CONFIDENCE: uptime 0.6h < 24h

ℹ INFO Issues 2

INFO HEURISTIC OBSERVE

Информация: статистика использования индексов неполная

Database

Impact: Статистика по индексам может быть неполной после перезапуска

База работает 0.6 часов (менее 24)

Recommendation: Повторите анализ через 24 часа работы базы данных

🟢 Низкий риск

-- Повторите аудит через 24 часа непрерывной работы

Confidence: Uptime < 24h — защита от ложных срабатываний

INFO HEURISTIC OBSERVE

Информация: статистика ROI индексов неполная

Database

Impact: Статистика ROI индексов может быть неполной после перезапуска

База работает 0.6 часов (менее 24)

Recommendation: Повторите анализ через 24 часа работы базы данных

🟢 Низкий риск

-- Повторите аудит через 24 часа непрерывной работы

Confidence: Uptime < 24h — защита от ложных срабатываний

PostgreSQL Diagnostic Report

🔴 Срочные действия

✅ Быстрые победы

📋 План исправлений

📊 Бенчмарки

🔴 CRITICAL Issues 2

Нет Primary Key

Autovacuum глобально отключён

🟠 HIGH Issues 3

Нет Primary Key

Нет Primary Key

Мертвые кортежи

🟡 MEDIUM Issues 3

Много Seq Scan

work_mem слишком мал

Не установлен log_min_duration_statement

🔵 LOW Issues 1

Отсутствие индекса на большой таблице

ℹ INFO Issues 2

Информация: статистика использования индексов неполная

Информация: статистика ROI индексов неполная