Debug API Timeout: Pool PostgreSQL Habis akibat Transaksi Menggantung

API timeout tidak selalu disebabkan oleh query lambat. Dalam kasus ini, jalur exception meninggalkan transaksi PostgreSQL tanpa COMMIT, ROLLBACK, maupun pelepasan koneksi. Koneksi kemudian bertahan dalam status idle in transaction, kapasitas pool berangsur habis, dan request baru harus mengantre sampai akhirnya timeout.

Perbaikan utamanya adalah memastikan setiap transaksi memiliki satu jalur finalisasi yang selalu dijalankan: commit ketika berhasil, rollback ketika gagal, dan release dalam blok finally. Namun, diagnosis tetap perlu dilakukan secara menyeluruh agar transaksi menggantung tidak keliru dianggap sebagai query lambat atau pool yang terlalu kecil.

Studi kasus: latensi API naik sebelum seluruh endpoint timeout

Insiden bermula dari endpoint penulisan data yang menjalankan beberapa query dalam satu transaksi. Pada jalur normal, transaksi selesai dengan COMMIT dan koneksi dikembalikan ke pool. Pada jalur exception tertentu, fungsi langsung melempar error sebelum rollback dan release dijalankan.

Gejala awalnya tidak langsung terlihat sebagai kegagalan database:

Latensi persentil tinggi naik lebih dahulu, sedangkan sebagian request masih berhasil.
Jumlah koneksi pool yang sedang dipakai terus bertambah dan tidak kembali turun.
Jumlah request yang menunggu koneksi meningkat.
Endpoint yang tidak berkaitan dengan jalur error ikut melambat karena menggunakan pool yang sama.
Log aplikasi mulai menampilkan timeout saat memperoleh koneksi atau timeout request, bukan selalu error SQL.

Dampaknya meluas ke endpoint baca sederhana. Walaupun query seperti pencarian berdasarkan primary key seharusnya cepat, query tersebut tidak dapat dimulai sebelum aplikasi memperoleh koneksi. Dengan kata lain, waktu habis di antrean pool, bukan saat PostgreSQL mengeksekusi SQL.

Timeout di sisi HTTP hanya menunjukkan tempat request menyerah. Penyebab sebenarnya dapat terjadi lebih awal, misalnya saat menunggu koneksi pool yang tidak pernah dikembalikan.

Mengapa idle in transaction berbahaya?

Status idle in transaction berarti sebuah sesi telah membuka transaksi, tetapi saat ini tidak sedang mengeksekusi query. Selain menahan slot koneksi pool, transaksi yang lama dapat mempertahankan lock dan snapshot. Dampak detailnya bergantung pada operasi yang telah dilakukan, tetapi secara umum transaksi seperti ini dapat menghambat operasi lain dan mengganggu pemeliharaan versi baris oleh PostgreSQL.

Diagnosis API timeout dan pool PostgreSQL yang habis

1. Periksa metrik connection pool

Metrik minimum yang perlu tersedia adalah jumlah koneksi total, koneksi aktif atau dipinjam, koneksi idle di pool, dan request yang menunggu koneksi. Nama metrik berbeda antar-library, tetapi hubungan antarmetriknya lebih penting daripada nama persisnya.

Pada node-postgres, misalnya, pool menyediakan totalCount, idleCount, dan waitingCount. Nilai tersebut dapat diekspor ke sistem metrik secara periodik:

function collectPoolMetrics(pool, metrics) {
  metrics.gauge('db_pool_connections_total', pool.totalCount);
  metrics.gauge('db_pool_connections_idle', pool.idleCount);
  metrics.gauge('db_pool_requests_waiting', pool.waitingCount);
}

Pola yang mengarah ke kebocoran transaksi adalah koneksi idle di pool mendekati nol, koneksi yang dipinjam tetap tinggi, dan antrean terus bertambah walaupun throughput tidak naik secara sebanding. Tambahkan histogram durasi checkout koneksi serta durasi transaksi agar kebocoran dapat dibedakan dari lonjakan trafik normal.

Jangan hanya memantau ukuran pool per proses. Jika aplikasi memiliki banyak instance, hitung juga total batas koneksi seluruh instance dan sisakan kapasitas PostgreSQL untuk migrasi, pekerjaan administratif, worker, dan layanan lain.

2. Cari sesi idle in transaction melalui pg_stat_activity

Jalankan query berikut dari akun yang memiliki visibilitas statistik yang memadai:

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    xact_start,
    query_start,
    state_change,
    wait_event_type,
    wait_event,
    left(query, 200) AS last_query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state = 'idle in transaction'
ORDER BY xact_start NULLS LAST;

xact_start menunjukkan kapan transaksi dimulai, sedangkan state_change membantu memperkirakan sejak kapan sesi berada pada status sekarang. Kolom query pada sesi idle menampilkan query terakhir, sehingga tidak boleh langsung dianggap sebagai query yang menyebabkan exception.

Jika detail sesi lain disamarkan, akun diagnosis mungkin memerlukan hak statistik tambahan sesuai kebijakan keamanan organisasi. Hindari menyimpan teks query lengkap tanpa penyaringan karena parameter atau komentar SQL dapat memuat data sensitif.

3. Korelasikan request ID, transaksi, dan backend PostgreSQL

Setiap request perlu memiliki request ID yang diteruskan melalui middleware, log aplikasi, dan panggilan internal. Saat transaksi dimulai, catat request ID, nama operasi, serta PID backend PostgreSQL. PID dapat diperoleh dengan SELECT pg_backend_pid(); pertimbangkan overhead satu round-trip tambahan atau ambil informasi serupa dari driver jika tersedia secara resmi.

{
  "level": "info",
  "event": "db_transaction_started",
  "request_id": "req-7d91",
  "operation": "create_order",
  "db_pid": 18442
}

Ketika PID yang sama muncul sebagai idle in transaction pada pg_stat_activity, request ID dapat digunakan untuk menemukan log exception dan jalur kode yang tidak menyelesaikan transaksi. Catat pula event committed, rolled_back, dan released beserta durasinya. Log harus terstruktur agar dapat dicari berdasarkan request ID, PID, atau nama operasi.

Root cause: exception melewati rollback dan release

Berikut pola bermasalah menggunakan node-postgres. Kode terlihat benar pada jalur sukses, tetapi error dari salah satu query akan melewati COMMIT dan release():

async function createOrder(pool, input) {
  const client = await pool.connect();
  await client.query('BEGIN');

  const order = await client.query(
    'INSERT INTO orders(customer_id) VALUES ($1) RETURNING id',
    [input.customerId]
  );

  await client.query(
    'INSERT INTO order_items(order_id, product_id, quantity) VALUES ($1, $2, $3)',
    [order.rows[0].id, input.productId, input.quantity]
  );

  await client.query('COMMIT');
  client.release();
  return order.rows[0];
}

Contohnya, pelanggaran constraint pada insert kedua akan melempar exception. Transaksi PostgreSQL tetap terbuka dalam kondisi gagal sampai menerima ROLLBACK, sementara objek koneksi masih dipegang fungsi yang sudah berhenti. Jika pola ini berulang, pool akan habis.

Perbaikan dengan transaction helper

Transaction helper memusatkan aturan finalisasi sehingga setiap pemanggil tidak perlu mengulang pola rawan kesalahan. Contoh berikut melakukan rollback saat terjadi error dan selalu melepaskan koneksi:

async function withTransaction(pool, logger, work) {
  const client = await pool.connect();
  let transactionOpen = false;
  let discardReason;

  try {
    await client.query('BEGIN');
    transactionOpen = true;

    const result = await work(client);

    await client.query('COMMIT');
    transactionOpen = false;
    return result;
  } catch (error) {
    if (transactionOpen) {
      try {
        await client.query('ROLLBACK');
        transactionOpen = false;
      } catch (rollbackError) {
        discardReason = rollbackError;
        logger.error({
          event: 'db_rollback_failed',
          error: rollbackError.message
        });
      }
    }
    throw error;
  } finally {
    client.release(discardReason);
  }
}

async function createOrder(pool, logger, input) {
  return withTransaction(pool, logger, async (client) => {
    const order = await client.query(
      'INSERT INTO orders(customer_id) VALUES ($1) RETURNING id',
      [input.customerId]
    );

    await client.query(
      'INSERT INTO order_items(order_id, product_id, quantity) VALUES ($1, $2, $3)',
      [order.rows[0].id, input.productId, input.quantity]
    );

    return order.rows[0];
  });
}

Jika rollback gagal karena koneksi telah rusak, contoh tersebut memberikan error kepada release() agar client tidak digunakan kembali. Semantik pelepasan koneksi rusak berbeda antar-driver, jadi sesuaikan bagian ini dengan API resmi library yang digunakan.

Bila ORM atau framework menyediakan helper transaksi resmi, gunakan helper tersebut daripada membuat abstraksi baru. Pastikan callback benar-benar di-await, error tidak ditelan, dan perilaku transaksi bersarang dipahami. Beberapa implementasi menggunakan savepoint, sedangkan yang lain hanya memakai transaksi terluar.

Pola try-catch-finally tanpa helper

Jika helper belum memungkinkan, aturan minimumnya adalah:

Ambil koneksi sebelum blok transaksi.
Jalankan BEGIN di dalam try.
Lakukan COMMIT hanya setelah seluruh operasi berhasil.
Jalankan ROLLBACK dalam catch jika transaksi sudah dimulai.
Jalankan release dalam finally, termasuk ketika rollback gagal.

Jangan mengandalkan timeout HTTP untuk membersihkan transaksi. Pembatalan request atau penolakan Promise tidak otomatis menghentikan query, melakukan rollback, atau mengembalikan koneksi ke pool.

Timeout, alert, dan graceful degradation

Batas waktu transaksi dan query

Gunakan beberapa lapisan batas waktu karena masing-masing melindungi kegagalan yang berbeda:

Timeout checkout pool: membatasi waktu request menunggu koneksi sehingga kegagalan terjadi cepat dan terukur.
statement_timeout: membatasi lama eksekusi statement, tetapi tidak menyelesaikan sesi yang sedang idle di dalam transaksi.
idle_in_transaction_session_timeout: memutus sesi yang terlalu lama diam saat transaksi masih terbuka.
Deadline aplikasi: membatasi durasi operasi bisnis dan harus diikuti pembatalan query jika driver mendukungnya, rollback, serta release.
Transaction timeout: jika versi PostgreSQL atau framework yang digunakan mendukung batas total transaksi, evaluasi sebagai perlindungan tambahan. Verifikasi dukungan dan semantiknya pada versi yang digunakan.

Contoh guardrail untuk role aplikasi:

ALTER ROLE api_user
SET idle_in_transaction_session_timeout = '30s';

Nilai 30s hanya contoh, bukan rekomendasi universal. Tentukan berdasarkan transaksi terlama yang sah, karakter beban kerja, dan margin operasional. Ketika timeout server memutus sesi, koneksi tersebut tidak boleh dikembalikan sebagai koneksi sehat; pool atau driver harus menggantinya.

Guardrail database membatasi dampak, tetapi bukan pengganti perbaikan kode. Nilai yang terlalu agresif dapat memutus proses batch, migrasi, atau alur interaktif yang memang membutuhkan transaksi panjang. Sebaiknya gunakan role terpisah agar kebijakan API tidak diterapkan tanpa sengaja ke semua workload.

Alert yang relevan

Buat alert berdasarkan kondisi yang bertahan selama suatu jendela waktu, bukan satu sampel sesaat. Sinyal yang berguna meliputi:

Rasio koneksi pool yang dipinjam mendekati batas.
Request yang menunggu koneksi lebih dari nol secara berkelanjutan.
Durasi checkout koneksi dan durasi transaksi meningkat.
Jumlah atau umur sesi idle in transaction melewati ambang operasional.
Timeout API naik bersamaan dengan kejenuhan pool.
Event rollback gagal atau koneksi dibuang meningkat.

Alert gabungan mengurangi false positive. Pool yang penuh selama lonjakan singkat belum tentu insiden jika tidak ada antrean dan durasi transaksi tetap rendah.

Graceful degradation

Saat pool jenuh, hindari membiarkan semua request menunggu sampai timeout HTTP. Batasi antrean, gagal cepat dengan respons yang sesuai, dan lindungi endpoint kritis. Endpoint nonkritis dapat menggunakan cache atau data yang sedikit usang jika semantik bisnis mengizinkan.

Retry harus dibatasi, memakai backoff dan jitter, serta hanya dilakukan untuk kegagalan yang memang sementara. Jangan otomatis mengulang operasi mutasi yang belum memiliki idempotency key karena hasil transaksi sebelumnya mungkin tidak diketahui. Circuit breaker dapat mencegah penumpukan request, tetapi tidak akan memperbaiki koneksi yang bocor.

Membedakan transaksi menggantung, query lambat, dan pool terlalu kecil

Transaksi menggantung

pg_stat_activity.state menunjukkan idle in transaction.
xact_start sudah lama, tetapi tidak ada query yang sedang aktif.
Koneksi aplikasi tetap dipinjam dan antrean pool meningkat.
Masalah sering berkorelasi dengan exception atau pembatalan request tertentu.

Query lambat

Sesi umumnya berstatus active, dengan query_start yang sudah lama.
Terlihat wait event, konsumsi CPU, I/O, lock wait, atau rencana eksekusi yang tidak efisien.
Diagnosis berfokus pada execution plan, indeks, statistik tabel, lock, jumlah data, dan pola query.
Menambah pool dapat memperburuk beban jika database sudah menjadi bottleneck.

Kapasitas pool memang kurang

Transaksi selesai dengan cepat dan tidak ada akumulasi idle in transaction.
Kejenuhan berkorelasi dengan throughput yang sah dan meningkat.
Database masih memiliki kapasitas CPU, I/O, memori, serta slot koneksi.
Perhitungan mencakup batas pool dikalikan jumlah instance aplikasi.

Menaikkan ukuran pool sebelum menemukan root cause hanya menunda insiden. Jika setiap exception membocorkan satu koneksi, pool yang lebih besar tetap akan habis dan sekaligus meningkatkan tekanan pada PostgreSQL.

Pengujian regresi dan verifikasi setelah deployment

Uji jalur exception dengan pool kecil

Buat pengujian integrasi yang sengaja memicu error di tengah transaksi, misalnya pelanggaran constraint. Gunakan pool berukuran satu atau sangat kecil agar kebocoran koneksi terlihat deterministik.

Jalankan operasi yang memulai transaksi lalu gagal pada query berikutnya.
Pastikan Promise atau handler mengembalikan error yang diharapkan.
Jalankan query sehat menggunakan pool yang sama.
Pastikan query sehat memperoleh koneksi tanpa menunggu sampai timeout.
Verifikasi tidak ada sesi idle in transaction yang tertinggal.
Ulangi skenario berkali-kali untuk mendeteksi kebocoran kumulatif.

Tambahkan kasus pembatalan request, error saat commit, dan simulasi rollback gagal jika infrastruktur pengujian memungkinkan. Jangan hanya menguji happy path karena kebocoran justru berada pada jalur kegagalan.

Checklist verifikasi produksi

Setelah deployment, lakukan verifikasi bertahap:

Pantau koneksi aktif, idle, antrean pool, dan durasi checkout selama canary atau rollout awal.
Pastikan event transaksi selalu berakhir dengan commit atau rollback, lalu release.
Periksa pg_stat_activity untuk memastikan sesi idle in transaction tidak bertambah.
Korelasikan request ID pada error baru dengan PID backend dan event finalisasi transaksi.
Bandingkan latensi endpoint terdampak dan endpoint lain yang memakai pool serupa.
Pastikan timeout server tidak menghasilkan lonjakan koneksi rusak atau retry storm.
Jalankan probe transaksi gagal secara terkendali dan pastikan request berikutnya tetap berhasil.

Keberhasilan perbaikan bukan hanya ditandai oleh turunnya timeout. Pool harus kembali ke kondisi seimbang setelah lonjakan, antrean koneksi harus pulih, dan tidak boleh ada pertumbuhan umur transaksi idle. Kombinasi finalisasi transaksi yang benar, observability, guardrail timeout, dan pengujian jalur exception mencegah insiden yang sama muncul kembali dalam bentuk berbeda.

Quick Search

shopping_cart Keranjang