Saat latensi Postgres naik, diskusi sering cepat mengarah ke tuning kernel: scheduler, model preemption, CPU affinity, atau parameter low-level lain. Masalahnya, pendekatan itu sering datang terlalu dini. Dalam banyak insiden backend, latensi Postgres yang memburuk lebih sering disebabkan oleh masalah di level aplikasi atau database seperti query yang membesar, pool koneksi yang jenuh, autovacuum tertunda, atau lock contention, bukan oleh perubahan PREEMPT model kernel.

Artikel ini membahas studi kasus debugging backend dengan pola yang umum terjadi: gejala awal terlihat seperti masalah sistem operasi, tetapi setelah divalidasi dengan metrik yang tepat, akar masalahnya ternyata ada di atas layer kernel. Fokusnya bukan debat scheduler Linux, melainkan bagaimana developer backend bisa membuktikan atau menolak hipotesis dengan data.

Studi Kasus: Latensi Naik Setelah Perubahan Infrastruktur

Bayangkan sebuah layanan API yang memakai Postgres untuk transaksi utama. Setelah deployment infrastruktur dan diskusi internal tentang tuning Linux scheduler, tim melihat gejala berikut:

  • p95 dan p99 latency endpoint naik signifikan
  • waktu eksekusi query tertentu terlihat lebih lama dari biasanya
  • throughput API turun walaupun CPU host belum terlihat penuh
  • sebagian request timeout saat traffic puncak

Karena insiden muncul berdekatan dengan perubahan host dan ada pembahasan soal kernel tuning, hipotesis awal langsung mengarah ke sistem operasi: apakah scheduler berubah, apakah model preemption memengaruhi wakeup latency, atau apakah kernel baru membuat Postgres kurang responsif.

Hipotesis itu terdengar masuk akal, tetapi ada masalah klasik: korelasi waktu belum berarti kausalitas. Sebelum menyentuh kernel, kita perlu menjawab pertanyaan yang lebih mendasar:

  • Apakah database benar-benar kekurangan CPU?
  • Apakah ada tanda bottleneck I/O?
  • Apakah query lambat karena menunggu CPU, disk, lock, atau koneksi?
  • Apakah lonjakan ada di semua query, atau hanya subset tertentu?
  • Apakah delay terjadi di Postgres, di connection pool, atau di aplikasi sebelum query dikirim?

Gejala yang Menyesatkan: Kenapa Kernel Sering Jadi Tersangka Dini

Masalah performa database memang bisa dipengaruhi sistem operasi. Namun dalam praktik produksi, tuning kernel adalah lapisan optimasi yang hanya relevan setelah bottleneck utama terbukti ada di sana. Ada beberapa alasan kenapa tim sering salah arah:

  • Perubahan terakhir terlihat di infrastruktur, sehingga mudah menjadi kambing hitam.
  • Grafik CPU terlihat “normal” secara rata-rata, padahal bottleneck bisa berupa lock wait atau queueing.
  • Latency tinggi terasa acak, sehingga orang menduga scheduling issue.
  • Kurang observability di level query dan pool, jadi penyebab riil tidak langsung terlihat.

Masalahnya, jika akar persoalan sebenarnya adalah query plan yang memburuk atau pool koneksi yang jenuh, tuning kernel tidak akan menyelesaikan apa pun. Paling baik, efeknya nol. Paling buruk, tim menghabiskan waktu pada area yang salah saat insiden masih berlangsung.

Langkah Verifikasi: Mulai dari Metrik yang Bisa Membantah Hipotesis

Tujuan awal debugging bukan membuktikan hipotesis favorit, melainkan membuang hipotesis yang tidak didukung data. Untuk itu, kumpulkan metrik dari beberapa layer sekaligus.

1. Cek CPU, iowait, dan run queue di host database

Jika dugaan Anda adalah scheduler kernel atau preemption, seharusnya ada sinyal yang konsisten di metrik sistem. Perhatikan:

  • CPU user/system: apakah benar host CPU-bound?
  • iowait: apakah proses menunggu disk, bukan CPU?
  • load average dan run queue: apakah runnable task menumpuk?
  • context switch dan steal time: terutama jika berjalan di VM

Contoh perintah awal:

vmstat 1
mpstat -P ALL 1
iostat -xz 1
sar -q 1

Interpretasinya harus hati-hati. Misalnya:

  • CPU rendah dan iowait rendah biasanya melemahkan dugaan bottleneck scheduler CPU.
  • Run queue tidak tinggi berarti proses tidak banyak menunggu giliran eksekusi CPU.
  • Disk latency normal melemahkan dugaan storage bottleneck.

Kalau semua ini relatif sehat, kecil kemungkinan perubahan PREEMPT model kernel menjadi penyebab utama latensi Postgres.

2. Pisahkan waktu tunggu di aplikasi, pool, dan database

Banyak tim hanya melihat “query time” dari APM, padahal angka itu sering sudah tercampur dengan waktu menunggu koneksi. Pada insiden nyata, connection pool jenuh bisa membuat gejala terlihat seperti Postgres melambat, padahal database belum tentu sibuk.

Metrik yang perlu ada:

  • waktu tunggu mendapatkan koneksi dari pool
  • jumlah koneksi aktif, idle, dan waiting
  • timeout saat acquire connection
  • durasi query di database
  • durasi total request di aplikasi

Kalau wait time di pool naik, tetapi query execution time di Postgres tidak naik sebanding, masalah utamanya bukan scheduler kernel. Itu tanda adanya queueing sebelum query dijalankan.

3. Cek query timing dan query yang paling banyak mengonsumsi waktu

Di Postgres, fokus pada dua pertanyaan:

  1. Query mana yang paling sering dipanggil?
  2. Query mana yang total waktunya paling besar selama insiden?

Jika tersedia, ekstensi seperti pg_stat_statements sangat membantu untuk melihat pola ini. Contoh query investigasi:

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Nama kolom dapat sedikit berbeda antar versi, jadi sesuaikan dengan sistem Anda. Yang penting adalah prinsipnya: cari query yang menyumbang akumulasi waktu terbesar, bukan hanya query tunggal yang paling lambat sekali.

4. Cek lock wait dan sesi yang saling menunggu

Lock contention sering menghasilkan gejala “latensi database” yang sangat mirip dengan masalah sistem. CPU bisa tampak biasa saja, disk normal, tetapi request tetap lambat karena sesi menunggu lock.

Contoh pemeriksaan:

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  now() - query_start AS query_age,
  query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start ASC;

Perhatikan apakah banyak sesi menunggu pada:

  • Lock
  • LWLock
  • transaksi lama yang menahan lock
  • DDL atau update besar yang memblokir workload lain

5. Cek autovacuum dan bloat

Autovacuum yang tertunda bisa memperburuk performa secara bertahap. Gejalanya sering muncul sebagai query yang dulunya cepat lalu makin mahal, terutama pada tabel yang sering di-update/delete. Jika vacuum tertahan oleh transaksi panjang, ukuran tabel dan indeks bisa membengkak, statistik menjadi kurang akurat, dan planner memilih rencana yang lebih buruk.

Hal yang perlu dicek:

  • apakah ada tabel dengan dead tuple tinggi
  • apakah autovacuum berjalan lambat atau jarang menyentuh tabel panas
  • apakah ada transaksi lama yang menghambat cleanup

Isolasi Masalah: Dari Dugaan Kernel ke Akar Masalah Sebenarnya

Dalam studi kasus ini, setelah metrik dikumpulkan, hasilnya kira-kira seperti berikut:

  • CPU host database tidak penuh
  • iowait rendah, disk tidak menunjukkan saturasi berarti
  • run queue normal, tidak ada indikasi kuat bottleneck scheduler
  • pool koneksi di aplikasi sering kehabisan slot saat traffic naik
  • beberapa query memiliki lonjakan mean execution time setelah perubahan kode
  • terlihat lock wait sporadis pada tabel transaksi

Dari sini, dugaan kernel mulai melemah. Langkah berikutnya adalah mengisolasi perubahan aplikasi dan pola query.

Skenario akar masalah 1: Query memburuk setelah perubahan fitur

Misalnya ada endpoint baru yang menambahkan filter opsional dan sorting dinamis. Di kode aplikasi, query builder berubah dari pola yang tadinya memanfaatkan indeks menjadi pola yang memaksa scan lebih mahal pada tabel besar.

Contoh pola yang sering bermasalah:

SELECT id, user_id, status, created_at
FROM orders
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Jika sebelumnya ada indeks yang sesuai dengan pola filter dan sort, query ini bisa cepat. Tetapi jika aplikasi menambahkan kondisi baru yang mengubah selektivitas, atau indeks yang tepat belum ada, planner mungkin memilih jalur yang lebih mahal. Validasi dilakukan dengan EXPLAIN atau EXPLAIN ANALYZE pada query yang benar-benar dieksekusi aplikasi.

Catatan: Jalankan EXPLAIN ANALYZE dengan hati-hati di produksi, terutama untuk query berat atau write query. Gunakan replika, sampling, atau maintenance window bila perlu.

Skenario akar masalah 2: Connection pool jenuh, bukan Postgres lambat

Ini sangat umum pada layanan backend. Misalnya ukuran pool terlalu kecil untuk pola concurrency aplikasi, atau ada handler yang menahan koneksi terlalu lama karena melakukan pekerjaan non-DB di dalam blok transaksi.

Contoh antipola:

BEGIN;
-- ambil data
-- panggil service eksternal
-- lakukan serialisasi besar di aplikasi
-- update hasil
COMMIT;

Walaupun query individual tidak terlalu mahal, koneksi tertahan lebih lama dari yang perlu. Saat traffic naik, request lain menunggu koneksi, latency melonjak, dan orang menyalahkan Postgres.

Tanda-tandanya:

  • waktu acquire connection naik
  • jumlah waiting client di pool bertambah
  • durasi transaksi lebih panjang dari durasi query aktif

Skenario akar masalah 3: Lock contention akibat pola update

Misalnya ada job background yang memperbarui baris-baris di tabel yang sama dengan request online. Saat job berjalan, request interaktif menunggu lock. Dari sisi aplikasi, gejalanya tampak seperti “database sedang lambat”. Dari sisi kernel, hampir tidak ada sinyal berarti.

Pemicu umum:

  • update batch besar tanpa chunking
  • transaksi panjang yang mencakup banyak baris
  • DDL dijalankan saat traffic aktif
  • urutan akses tabel tidak konsisten antar kode path

Skenario akar masalah 4: Autovacuum tertunda dan query makin mahal

Pada tabel dengan churn tinggi, dead tuple yang menumpuk bisa membuat pembacaan dan pemeliharaan indeks lebih mahal. Jika statistik stale, planner juga bisa salah memperkirakan cardinality. Efeknya muncul sebagai latensi bertahap, sering disalahartikan sebagai masalah host atau kernel setelah upgrade.

Perbaikan yang Dilakukan

Setelah akar masalah dibuktikan, perbaikannya biasanya jauh lebih membumi daripada tuning kernel.

1. Perbaiki query dan indeks berdasarkan workload nyata

Gunakan query yang benar-benar dominan di pg_stat_statements, lalu cek rencananya. Tindakan yang masuk akal:

  • tambahkan atau sesuaikan indeks agar cocok dengan filter dan sort paling sering
  • hindari predicate yang membuat indeks sulit dipakai
  • kurangi pengambilan kolom yang tidak perlu
  • pecah query kompleks jika planner consistently memilih plan buruk

Yang penting, jangan menambah indeks secara membabi buta. Setiap indeks punya biaya write, storage, dan vacuum.

2. Kurangi waktu memegang koneksi dan transaksi

Untuk kasus pool jenuh:

  • jangan lakukan operasi jaringan atau komputasi berat saat koneksi sedang dipegang
  • sempitkan scope transaksi
  • pastikan koneksi selalu dilepas cepat setelah query selesai
  • ukur waktu acquire dan hold time per endpoint

Jika ingin menambah ukuran pool, lakukan dengan hati-hati. Pool yang terlalu besar bisa memindahkan antrean dari aplikasi ke database dan membuat contention memburuk.

3. Tangani lock contention dengan perubahan pola write

  • pecah batch besar menjadi chunk yang lebih kecil
  • hindari transaksi panjang
  • jalankan DDL di waktu sepi atau dengan strategi rollout yang aman
  • konsistenkan urutan akses resource untuk mengurangi deadlock dan wait chain

4. Pastikan autovacuum tidak tertinggal

  • identifikasi tabel dengan churn tinggi
  • pantau transaksi lama yang menghambat vacuum
  • review pengaturan vacuum bila memang terbukti tertinggal pada workload tertentu
  • pastikan statistik tabel cukup segar untuk planner

Intinya, lakukan penyesuaian karena ada bukti bahwa vacuum atau statistik adalah masalah, bukan sekadar asumsi.

Checklist Debugging Latensi Postgres untuk Developer Backend

Jika insiden serupa terjadi, gunakan urutan berikut agar tidak terjebak pada tuning kernel terlalu cepat:

  1. Tentukan lokasi delay: di aplikasi, saat menunggu pool, atau saat query berjalan di Postgres.
  2. Cek kesehatan host: CPU, iowait, run queue, disk latency.
  3. Lihat query dominan: total waktu, frekuensi, mean time, dan perubahan pola setelah deployment.
  4. Cek sesi aktif dan wait event: apakah menunggu lock, I/O, atau hal lain.
  5. Periksa pool koneksi: active, idle, waiting, timeout acquire.
  6. Identifikasi transaksi panjang: baik dari request API maupun job background.
  7. Bandingkan sebelum dan sesudah perubahan: query shape, indeks, traffic pattern, dan rollout aplikasi.
  8. Baru evaluasi kernel tuning jika ada bukti kuat bottleneck benar-benar di layer OS.

Kesalahan Umum Saat Men-debug Latensi Postgres

Mengandalkan satu grafik CPU

CPU rata-rata yang normal tidak berarti sistem sehat. Queueing, lock wait, dan pool saturation bisa merusak latency tanpa membuat CPU tampak penuh.

Menganggap semua “database time” adalah waktu eksekusi query

Sering kali angka yang terlihat di APM mencampur waktu antre koneksi dan waktu tunggu lock. Tanpa pemisahan ini, diagnosis mudah meleset.

Terlalu cepat mengubah parameter low-level

Kernel tuning tanpa bukti bisa menambah variabel baru saat insiden berlangsung. Ini membuat rollback dan analisis jadi lebih sulit.

Tidak menghubungkan perubahan aplikasi dengan perubahan performa

Perubahan kecil di ORM, query builder, atau pola transaksi bisa memberi dampak besar. Karena efeknya muncul bersamaan dengan perubahan host, orang sering salah menyimpulkan sebabnya.

Kapan Tuning Kernel Layak Dipertimbangkan?

Bukan berarti kernel tuning tidak pernah relevan. Ia layak dibahas jika Anda sudah punya bukti seperti:

  • run queue tinggi secara konsisten
  • CPU saturation nyata pada workload database
  • latensi scheduler atau contention di level OS terlihat jelas
  • hasil profiling menunjukkan bottleneck di luar Postgres dan aplikasi

Namun untuk mayoritas kasus backend sehari-hari, peluang menemukan akar masalah di query, pool, lock, atau maintenance database jauh lebih besar. Itulah sebabnya debugging harus dimulai dari observability yang dekat dengan workload nyata.

Penutup

Pelajaran terpenting dari insiden seperti ini sederhana: jangan mulai dari layer yang paling sulit diubah sebelum layer yang paling mungkin terbukti bersalah diperiksa. Saat latensi Postgres naik, diskusi tentang PREEMPT model kernel mungkin menarik, tetapi sering bukan titik ungkit terbesar untuk developer backend.

Mulailah dari pemisahan waktu tunggu, cek pool koneksi, lihat query yang dominan, periksa lock wait, dan pastikan autovacuum tidak tertinggal. Jika semua sinyal itu sehat dan baru kemudian metrik host menunjukkan bukti bottleneck OS, barulah tuning kernel layak naik prioritas. Dengan urutan ini, Anda mengurangi bias investigasi dan mempercepat jalan menuju akar masalah yang benar.