Як неіндексована колонка вбила нашу базу даних

Один відсутній індекс перетворив API зі швидкістю 8 мс на 8-секундний кошмар.

Ми провели навантажувальне тестування перед великим релізом. Локальна розробка працювала чудово на 100 записах. Потім ми симулювали 500 користувачів на 1,5 мільйонах рядків у даних обсягом, подібним до продуктивного.

Результати були жахливими:

  • Час відповіді API зріс з 45 мс до 8 000 мс.
  • Завантаження CPU бази даних сягнуло 100%.
  • Пули з'єднань вичерпалися, що призвело до помилок тайм-ауту.

Ми перевірили slow-query log і знайшли ендпоінт, який отримував історію замовлень за ID користувача та статусом.

Запуск EXPLAIN ANALYZE у PostgreSQL показав Sequential Scan. Рушій зчитував усі 1,5 мільйона рядків для кожного запиту, оскільки для user_id не було індексу.

При 100 одночасних запитах він сканував 150 мільйонів рядків за раз.

Виправлення зайняло п'ять хвилин.

Замість простого індексу на user_id, ми створили складений індекс на (user_id, status, created_at DESC). Це дозволило базі даних:

  • Фільтрувати за user_id.
  • Фільтрувати за status.
  • Одразу повертати найновіші рядки.
  • Пропускати зайві кроки сортування.

Ми використали CREATE INDEX CONCURRENTLY, щоб таблиця залишалася розблокованою під час операції.

Після виправлення:

  • Час запиту впав з 8 150 мс до 0,14 мс.
  • Затримка API знизилася з 8 секунд до 12 мс.
  • Використання CPU впало зі 100% до менш ніж 8%.

Висновки:

  • Локальне тестування вводить в оману; 100 рядків не репрезентують мільйон.
  • Індексуйте свої зовнішні ключі — більшість ORM цього не роблять.
  • Запускайте EXPLAIN ANALYZE перед тим, як купувати нове обладнання.
  • Проектуйте індекси під запити, які ви насправді виконуєте.

Не масштабуйте спочатку сервери. Масштабуйте запити.

Джерело: https://dev.to/mia_keller_ffd2584c046ecb/how-an-unindexed-column-silently-killed-our-database-under-load-and-the-5-minute-fix-m32