Pada migrasi batch data skala besar, tim kami mengalami timeout pada connection pool database yang menghambat pemrosesan seluruh batch. Gejala muncul sebagai peningkatan latensi query di monitoring, antrean query menumpuk, dan akhirnya koneksi baru tidak bisa didapatkan. Artikel ini membahas langkah-langkah debugging dari observability hingga perbaikan, sehingga Anda bisa langsung menerapkan langkah serupa di sistem Anda.

Gejala Awal di Monitoring dan Alat Observability

Gejala pertama terlihat dari metrik pool DB: active connections mendekati batas maksimum, sedangkan wait queue melonjak dan sebagian request mendapatkan exception timeout. Dashboard APM menunjukkan latency normal di awal migrasi, lalu menanjak secara eksponensial seiring batch berikutnya.

Catat metrik berikut:

  • Pool usage (maks/min/avg)
  • Query duration rata-rata per endpoint migrasi
  • Errors per minute terkait timeout atau koneksi ditolak

Log Database menunjukkan format log query waktu panjang dan tidak ada penutupan transaksi setelah commit, menandai potensi connection leak atau transaction lock.

Mencari Root Cause: Query Panjang dan Connection Leak

Pusat migrasi kami adalah service batch yang membaca data dari beberapa tabel, melakukan transformasi, lalu menyimpan hasil. Investigasi menunjukkan dua masalah utama:

  • Query SELECT besar tanpa batas pagination, menghasilkan full table scan dan menahan connection terlalu lama.
  • Transaksi tidak selalu ditutup saat terjadi error; connection tetap dalam pool dalam status in-use.

Pendeteksian dilakukan melalui:

  • Log PostgreSQL pg_stat_activity yang memperlihatkan banyak session aktif dengan query yang sama lebih dari satu menit.
  • Tracing distribusi dari service batch yang menunjukkan ada retry otomatis sebelum commit.

Langkah Reproduksi Lokal

Untuk mereproduksi, jalankan migrasi dalam lingkungan dev dengan data volumetrik serupa. Fokus pada skenario berikut:

  1. Jalankan batch migrasi dengan skrip SQL yang menggunakan LIMIT/OFFSET kecil agar pool tertekan.
  2. Sisipkan fault injection untuk membuat satu batch throw exception sebelum commit, lalu amati apakah connection kembali ke pool.

Penting memantau pool menggunakan CLI:

SELECT pid, state, query, query_start
FROM pg_stat_activity
WHERE datname = 'nama_db' AND state <> 'idle';

Dan gunakan profiler pool (misalnya HikariCP atau PgBouncer) untuk melihat rincian connection leak.

Tindakan Perbaikan

1. Optimasi Query dan Batched Commit

Ubah SELECT besar menjadi iterasi bertahap dengan batas ID atau timestamp. Contoh sederhana:

WITH batch AS (
  SELECT *
  FROM sumber
  WHERE id > :last_id
  ORDER BY id
  LIMIT 500
)
INSERT INTO target (...) SELECT ... FROM batch;

Setelah setiap batch, commit segera dan simpan state terakhir.

2. Perbaiki Flow Transaksi

Pastikan semua transaksi ditutup dengan try/finally atau pattern using agar connection dilepaskan walau exception terjadi. Contoh pseudo-code:

try {
  connection.setAutoCommit(false);
  // operasi
  connection.commit();
} catch (Exception ex) {
  connection.rollback();
  throw ex;
} finally {
  connection.close();
}

Kalau menggunakan ORM, cek apakah ada EntityManager atau session yang wajib ditutup manual.

3. Tuning Pool Database

Setelah optimasi, tinjau konfigurasi pool:

  • maksimum koneksi cukup untuk batch rate, tapi tidak berlebihan agar DB tidak tertekan.
  • connection timeout ditetapkan agar koneksi idle kembali ke pool dan tidak menunggu forever.
  • leak detection diaktifkan (misal HikariCP leakDetectionThreshold), untuk memperingati jika koneksi tidak dikembalikan dalam waktu wajar.

Tips Preventif dan Pengujian Regresi

Preventif:

  • Monitor pool usage dan latency secara kontinu dengan alert threshold.
  • Gunakan observability tracing untuk mendeteksi connection leak sejak dini.
  • Dokumentasikan pola migrasi batch, termasuk batch size, timeout, dan fallback plan.

Pengujian regresi:

  • Tambahkan integration test yang menjalankan migrasi mock dengan data besar untuk memastikan batch size tidak memicu timeout.
  • Gunakan chaos testing ringan (misalnya mematikan instance database paling tidak 1x/bulan) untuk memastikan mekanisme rollback dan retry tetap sehat.
  • Validasi bahwa setiap error path menutup transaksi dengan unit test/Mock framework untuk connection manager.

Simpulan

Timeout pool database selama migrasi batch merupakan kombinasi dari query berat, connection leak, dan konfigurasi pool yang tidak sesuai. Dengan mengandalkan data monitoring, menelusuri query yang menumpuk, mereproduksi secara lokal, serta menerapkan optimasi query, batched commit, dan tuning pool, masalah dapat diselesaikan sekaligus dicegah untuk migrasi berikutnya. Lengkapi langkah tersebut dengan observability berkelanjutan dan pengujian regresi untuk menjaga kestabilan layanan.