Работа с базами данных инспекций (проверок, аудитов, контроля качества) часто сопряжена с обработкой больших объемов структурированных и неструктурированных данных: фото, чек-листы, геолокация, подписи, история изменений. Когда система начинает «висеть», тормозить при открытии карточек объектов или выдавать таймауты при генерации отчетов, это парализует работу инспекторов и управленцев. В этой статье разберем основные причины зависаний и пошаговый алгоритм действий — от быстрой диагностики до стратегических решений.

Признаки того, что проблема именно в базе данных

Прежде чем винить СУБД, важно исключить проблемы сети, фронтенда или бизнес-логики приложения. Обратите внимание на следующие маркеры:

• Медленные API-запросы при минимальной логике: эндпоинт «получить список инспекций за месяц» выполняется 10+ секунд, хотя код контроллера тривиален.
• Высокая нагрузка на CPU/IO сервера БД: мониторинг (Zabbix, Prometheus, pg_stat_activity, MySQL Performance Schema) показывает 90-100% утилизацию ресурсов именно процессом СУБД.
• Деградация по мере роста данных: в начале года отчет строился за 2 секунды, сейчас — за 3 минуты. Линейная зависимость скорости от объема таблиц inspections, checklist_items, media_files.
• Проблемы при параллельности: система работает нормально ночью, но днем, когда 50+ инспекторов синхронизируют данные с планшетов, «встает колом».
• Ошибки таймаутов и дедлоков в логах приложения: Lock wait timeout exceeded, deadlock detected, connection pool exhausted.

Этап 1: Экспресс-диагностика (первые 15 минут)

Анализ текущей активности (Who is doing what?)

Подключитесь к БД и посмотрите активные сессии прямо сейчас.

PostgreSQL:

SELECT pid, usename, application_name, client_addr, state, 
 now ౼ query_start AS duration, query 
FROM pg_stat_activity 
WHERE state != 'idle' AND pid != pg_backend_pid
ORDER BY duration DESC;

MySQL / MariaDB:

SHOW FULL PROCESSLIST;

Что искать: долгие SELECT без LIMIT, ожидания Lock, куча одинаковых запросов от одного пользователя (проблема N+1 в ORM), зависшие транзакции в состоянии idle in transaction.

Включение и анализ логов медленных запросов

Если они выключены, включите временно (на продакшене осторожно, диск может забиться).

• PostgreSQL: log_min_duration_statement = 1000 (логировать запросы > 1 сек).
• MySQL: slow_query_log = 1, long_query_time = 1.

Используйте утилиты для агрегации: pgBadger (Postgres), pt-query-digest (MySQL). Они покажут ТОП-10 самых «тяжелых» по суммарному времени запросов.

Проверка плана выполнения (EXPLAIN ANALYZE)

Возьмите самый частый/медленный запрос из логов и запустите с анализом плана.

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT i.id, i.object_name, i.status, i.inspector_id, i.created_at
FROM inspections i
JOIN inspection_items ii ON ii.inspection_id = i.id
WHERE i.status = 'pending' AND i.region_id = 5
ORDER BY i.created_at DESC LIMIT 50;

Красные флаги в плане:

• Seq Scan (последовательное чтение) на больших таблицах (>100k строк) вместо Index Scan.
• Rows Removed by Filter огромное — индекс есть, но он неселективен или не используется.
• Sort Method: external merge Disk — сортировка не влезает в work_mem и уходит на диск.
• Nested Loop с большим количеством итераций на внешней таблице.

Этап 2: Типовые проблемы «Баз инспекций» и их лечение

Проблема 1: Отсутствие или неэффективные индексы

Контекст: Таблица inspections имеет миллионы строк. Фильтрация по status, region_id, inspector_id, диапазонам дат created_at.

Решение:

• Создайте составные индексы под частые паттерны WHERE + ORDER BY.
  CREATE INDEX idx_inspections_region_status_date ON inspections (region_id, status, created_at DESC);
• Для полнотекстового поиска по комментариям/описаниям используйте GIN-индексы (Postgres) или FULLTEXT (MySQL).
• Важно: Удаляйте неиспользуемые индексы (pg_stat_user_indexes / sys.schema_unused_indexes) — они тормозят INSERT/UPDATE (а инспекции пишутся постоянно).

Проблема 2: Блокировки и взаимоблокировки (Deadlocks)

Контекст: Мобильное приложение инспектора синхронизирует данные: обновляет инспекции, вставляет 50 строк чек-листа, загружает фото. Параллельно бэкенд генерирует PDF-отчет, блокируя строки на чтение (SELECT … FOR SHARE) или обновление статуса.

Типы блокировок, с которыми вы столкнетесь:

1. Row-level locks (Строчные): Стандарт для OLTP. Конфликт: два инспектора редко редактируют одну инспекцию, но частый кейс — «Инспектор сохраняет» vs «Система меняет статус на Просрочена».
2. Table-level locks: Возникают при ALTER TABLE, TRUNCATE, VACUUM FULL, или явном LOCK TABLE. Никогда не делайте DDL в рабочее время.
3. Advisory Locks / Application Locks: Если используете для распределенных задач (например, генерация уникального номера акта) — проверьте таймауты.

Как лечить дедлоки:

• Единый порядок доступа: Всегда обновляйте таблицы в одном порядке: сначала inspections, потом inspection_items, потом media.
• Короткие транзакции: Не делайте HTTP-запросы к внешним сервисам (S3, 1С, Email) внутри открытой транзакции БД. Используйте паттерн Transactional Outbox / Kafka.
• SELECT … FOR UPDATE SKIP LOCKED (Postgres 9.5+ / MySQL 8.0+): для очередей задач (например, «взять следующую инспекцию на проверку») — позволяет не ждать, а пропустить заблокированную строку.
• Изоляция Read Committed (по умолчанию) обычно достаточно. Не поднимайте до Repeatable Read / Serializable без крайней нужды, растет риск сериализационных ошибок.

Проблема 3: «Толстые» таблицы и BLOB-данные (Фото/Подписи)

Симптом: Таблица inspection_media весит 500 ГБ. SELECT * FROM inspections JOIN media... убивает память и диск.

Решения:

• Выносите бинарные данные в S3-совместимое хранилище (MinIO, AWS S3, Selectel). В БД храните только URL, хеш, метаданные (размер, MIME, ширина/высота).
• Если нельзя вынести — используйте TOAST (Postgres) / BLOB вне строки (MySQL), но лучше все-таки S3.
• Для превью генерируйте thumbnails при загрузке, не на лету при открытии карточки.

Проблема 4: Пулы соединений (Connection Pooling)

Симптом: «FATAL: remaining connection slots reserved for non-replication superuser connections» или «Pool exhausted». Приложение висит на получении коннекта.

Архитектура: 10 подов бэкенда (K8s) × 20 коннектов = 200 коннектов. Postgres по дефолту max_connections = 100.

Что делать:

• Поставьте PgBouncer (Postgres) или ProxySQL (MySQL) в режиме transaction pooling.
• Настройте пул приложения: pool_size = (cpu_cores * 2) + effective_spindle_count (обычно 10-20 на инстанс приложения).
• Включите idle_in_transaction_session_timeout = 30s (Postgres) — убьет зависшие транзакции, съевшие слот.

Проблема 5: Вакуум и фрагментация (PostgreSQL специфика)

Инспекции, это тяжелая write-нагрузка (INSERT/UPDATE/DELETE). Мертвые кортежи (dead tuples) раздувают таблицы и индексы.

• Проверьте: SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 10000;
• Настройте автовакуум агрессивнее для горячих таблиц:

  ALTER TABLE inspections SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005);

• Мониторьте bloat (раздувание) через pgstattuple или запросы из pg_extras. Если bloat > 30%, нужен pg_repack (онлайн) или плановая остановка для VACUUM FULL.

Этап 3: Архитектурные паттерны для масштабирования

Если тюнинг запросов и конфигов не помогает — пора менять архитектуру.

Репликация чтения (Read Replicas)

Отдавайте все аналитические запросы (отчеты, дашборды, экспорт в Excel, поиск по истории) на реплику. Мастер оставляем только под запись (мобильные приложения инспекторов).

• Настройте synchronous_commit = off на репликах для скорости (если допустима потеря последних секунд).
• Используйте балансировщик (HAProxy, Patroni) для прозрачного переключения.

Партиционирование таблиц

Таблица inspection_items или audit_logs растет бесконечно. Партиционируйте по времени (месяц/квартал) — PARTITION BY RANGE (created_at).

• Старые партиции можно сжимать (ALTER TABLE ... SET ACCESS EXCLUSIVE ... SET COMPRESSION в PG14+), переносить на дешевые диски или откреплять (DETACH PARTITION) для архивации;
• Запросы за последний месяц сканируют только 1 партицию вместо всей таблицы.

Кэширование (Redis / In-memory)

• Кэшируйте справочники (типы дефектов, нормативные документы, список инспекторов, регионы) — они читаются на каждый рендер формы.
• Кэшируйте агрегаты для дашбордов: «Количество просроченных инспекций по регионам» — пересчитывайте по событию (Kafka/Cron) раз в минуту, а не считайте COUNT(*) на лету.

Асинхронная обработка тяжелых задач

Генерация PDF-акта на 50 страниц, отправка в 1С, рассылка уведомлений — всё это не должно происходить в HTTP-запросе пользователя.

• Используйте очередь задач (Celery + Redis/RabbitMQ, Temporal, Kafka Consumer).
• Пользователь получает мгновенный ответ «Задача поставлена в очередь», статус опрашивается по WebSocket / Long Polling.

Чек-лист: План действий «Система висит прямо сейчас»

1. Снять дамп текущих запросов (pg_stat_activity / SHOW PROCESSLIST). Убить явно зависшие/бесполезные (DROP сессии приложения, не БД!).
2. Проверить пул коннектов: не исчерпан ли лимит? Перезапустить PgBouncer / пул приложения.
3. Посмотреть на диск/CPU/IOwait: если диск 100% утилизация, возможно, чекпоинт (checkpoint) или вакуум. Временно снизить активность (поставить реплику в read-only, отключить тяжелые кроны).
4. Найти ТОП-1 запрос в логах медленных запросов за последние 10 минут. Сделать EXPLAIN ANALYZE. Добавить недостающий индекс (CONCURRENTLY в Postgres) или переписать запрос.
5. Проверить дедлоки: в логах БД ищите «deadlock detected». Если есть — исправить порядок локов в коде приложения (горячий фикс).
6. Включить кэширование для справочников и дашбордов, если еще нет.
7. Завести тикет на постмортем: настроить алерты (Prometheus Alertmanager / Grafana) на: db_connections_usage > 80%, query_duration_p95 > 5s, deadlocks_total rate > 0, replication_lag > 10s.

База данных инспекций — это классическая OLTP-система с пиковыми нагрузками (утро: синхронизация планшетов, вечер: сдача отчетов) и растущим «хвостом» истории. Главные враги производительности здесь — отсутствие правильных составных индексов, длинные транзакции с внешними вызовами, хранение бинарников в таблицах и недостаток пулинга коннектов.

Начните с настройки мониторинга (PGWatch2, PMM, Datadog) и логов медленных запросов. 80% проблем решаются добавлением 2-3 правильных индексов, настройкой PgBouncer и выносом генерации отчетов в асинхронную очередь. Остальные 20% — это партиционирование, реплики и переход на columnar-хранилища (ClickHouse) для аналитики.

Помните: Оптимизация БД, это итеративный процесс. Измеряйте, меняйте одно, измеряйте снова. И всегда тестируйте план выполнения запроса на данных, близких к продакшн-объему (анонимизированный дамп).