Bagaimana Kolom Tanpa Indeks Menghancurkan Database Kami

Satu indeks yang hilang mengubah API 8 ms menjadi mimpi buruk selama 8 detik.

Kami menjalankan uji beban (load test) sebelum rilis besar. Pengembangan lokal berjalan lancar dengan 100 baris data. Kemudian kami mensimulasikan 500 pengguna terhadap 1,5 juta baris data seukuran produksi.

Hasilnya buruk:

  • Waktu respons API melonjak dari 45 ms menjadi 8.000 ms.
  • CPU Database mencapai 100 %.
  • Connection pool habis, menyebabkan error timeout.

Kami memeriksa log slow-query dan menemukan sebuah endpoint yang mengambil riwayat pesanan berdasarkan user ID dan status.

Menjalankan EXPLAIN ANALYZE pada PostgreSQL menunjukkan adanya Sequential Scan. Mesin membaca seluruh 1,5 juta baris untuk setiap permintaan karena tidak ada indeks pada user_id.

Dengan 100 permintaan konkuren, sistem memindai 150 juta baris sekaligus.

Perbaikannya hanya memakan waktu lima menit.

Alih-alih hanya menggunakan indeks sederhana pada user_id, kami membuat composite index pada (user_id, status, created_at DESC). Hal ini memungkinkan database untuk:

  • Memfilter berdasarkan user_id.
  • Memfilter berdasarkan status.
  • Mengembalikan baris terbaru secara instan.
  • Melewati langkah pengurutan tambahan.

Kami menggunakan CREATE INDEX CONCURRENTLY agar tabel tetap tidak terkunci selama operasi berlangsung.

Setelah perbaikan:

  • Waktu query turun dari 8.150 ms menjadi 0,14 ms.
  • Latensi API turun dari 8 detik menjadi 12 ms.
  • Penggunaan CPU turun dari 100 % menjadi di bawah 8 %.

Pelajaran yang dipetik:

  • Pengujian lokal bisa menyesatkan; 100 baris tidak mewakili satu juta baris.
  • Berikan indeks pada foreign key Anda—kebanyakan ORM melewatkan hal ini.
  • Jalankan EXPLAIN ANALYZE sebelum membeli perangkat keras tambahan.
  • Rancang indeks berdasarkan query yang benar-benar Anda jalankan.

Jangan skalakan server terlebih dahulu. Skalakan query Anda.

Sumber: https://dev.to/mia_keller_ffd2584c046ecb/how-an-unindexed-column-silently-killed-our-database-under-load-and-the-5-minute-fix-m32