Wawancara Data: Mendiagnosis Hambatan Memori dan I/O dengan EXPLAIN PostgreSQL 18

Permintaan dan konteks

Kueri yang sama berjalan cepat dalam pengujian namun lambat di produksi. Rancang rencana diagnosis PostgreSQL 18 menggunakan EXPLAIN (ANALYZE, BUFFERS) beserta detail tambahan memori, disk, dan I/O-nya untuk membedakan antara rencana eksekusi yang buruk, tumpahan pengurutan (sort spill), cache miss, dan latensi penyimpanan.

Hal ini sesuai untuk peran rekayasa data (data engineering), backend, dan operasional basis data. PostgreSQL 18 memperluas EXPLAIN dengan detail memori dan disk untuk lebih banyak node serta menampilkan detail akses buffer eksekusi; panduan resmi EXPLAIN mendefinisikan estimasi, baris aktual, perulangan (loops), BUFFERS, dan ANALYZE. Artikel ini didasarkan pada dokumentasi publik, bukan klaim tentang bank soal wawancara perusahaan tertentu.

Apa yang dievaluasi pewawancara

Pewawancara menginginkan rantai bukti dari rencana eksekusi daripada rekomendasi indeks yang diberikan secara otomatis. Jawaban yang kuat membedakan kesalahan estimasi dari kehabisan sumber daya, menjelaskan shared hit/read/dirtied/written, memori pengurutan atau hash, serta waktu I/O, dan mencakup pengambilan sampel di produksi, izin akses, serta prosedur rollback.

Pertanyaan klarifikasi

• Bisakah kueri diputar ulang (replay) pada replika baca (read replica) atau data yang telah dide-identifikasi?
• Apakah regresi tersebut merupakan latensi rata-rata, latensi ekor (tail latency), atau pemilihan rencana spesifik parameter?
• Berapa overhead eksekusi yang dapat diterima untuk EXPLAIN ANALYZE di produksi?
• Apakah sidik jari kueri (query fingerprints), pembaruan statistik, dan metrik disk/cache tersedia untuk korelasi?

Jawaban 30 detik

"Pertama, simpan parameter produksi dan sidik jari kueri. Pada replika, jalankan EXPLAIN (ANALYZE, BUFFERS, VERBOSE) dan bandingkan baris estimasi versus aktual, perulangan (loops), hit/read buffer, serta kolom memori/disk PostgreSQL 18. Kesalahan estimasi yang besar mengarah pada masalah statistik; tumpahan sort atau hash mengarah pada memori kerja (work memory), konkurensi, atau ketimpangan data (skew); pembacaan yang tinggi memerlukan bukti cache dan penyimpanan. Validasi perubahan indeks, statistik, atau parameter pada replika, lalu lakukan peluncuran canary sambil memantau tail latency."

Solusi langkah demi langkah

Tetapkan sampel terlebih dahulu. Catat SQL, parameter terikat, waktu perencanaan, waktu eksekusi, jumlah baris, dan versi basis data; parameter yang berbeda dapat memilih rencana yang berbeda. EXPLAIN memperkirakan tanpa mengeksekusi, sedangkan ANALYZE menjalankan pernyataan tersebut. Untuk operasi penulisan, gunakan replika baca, transaksi baca-saja jika memungkinkan, atau rollback yang aman agar diagnosis tidak mengubah data.

Baca rencana dengan membandingkan urutan besaran baris estimasi dan aktual, kemudian perhitungkan loops. BUFFERS memisahkan halaman shared hit, read, dirtied, dan written. Jumlah hit yang tinggi tidak membuktikan kueri berjalan cepat; pembacaan membutuhkan konteks volume data dan latensi penyimpanan. PostgreSQL 18 menambahkan detail penggunaan memori dan disk ke lebih banyak node, membantu mengidentifikasi set kerja (working set) dari sort, agregat window, CTE, dan node Materialize.

Jika sort atau hash menggunakan disk, tentukan apakah work_mem terlalu kecil, konkurensi terlalu tinggi, atau data timpang. Jangan menaikkannya secara global karena setiap operator dan sesi konkuren mengonsumsi memori. Jika pembacaan buffer dan latensi I/O tinggi, periksa kapasitas cache, penggelembungan tabel (bloat), selektivitas indeks, dan penyimpanan. Pembacaan dengan latensi rendah mungkin hanya mencerminkan cold cache; konfirmasikan dengan pemutaran ulang yang stabil dan sampel berulang.

Kesalahan estimasi sering kali mengindikasikan statistik yang usang, kurangnya statistik yang diperluas untuk kolom yang berkorelasi, sensitivitas parameter, atau perubahan distribusi data. Uji ANALYZE, statistik yang diperluas, atau penulisan ulang kueri sebelum memaksakan urutan join. Perubahan indeks harus dievaluasi untuk amplifikasi penulisan, biaya pemeliharaan, dan cakupan; rencana yang lebih baik tidak menjamin throughput keseluruhan yang lebih baik.

Diagnosis produksi membutuhkan batasan pengambilan sampel dan izin. Batasi frekuensi dan konkurensi EXPLAIN ANALYZE, lakukan de-identifikasi literal dan hasil, serta agregasikan sidik jari dengan pg_stat_statements. Hubungkan metrik rencana, memori, buffer, dan I/O dengan latensi p95/p99. Lakukan canary pada perubahan dan segera batalkan (revert) jika terjadi regresi pada antrean kunci (lock waits), tekanan memori, atau tail latency.

Contoh jawaban

Saya akan memutar ulang parameter tetap pada replika baca dan mengumpulkan baris estimasi/aktual, perulangan, buffer hit/read/dirtied/written, serta kolom memori/disk node PostgreSQL 18. Perbedaan estimasi yang besar mengarah pada perbaikan statistik; tumpahan sort/hash mengarah pada analisis work_mem, konkurensi, dan skew; pembacaan yang tinggi mengarah pada pengumpulan bukti cache dan penyimpanan. Validasi perubahan indeks, statistik, atau parameter pada replika, lalu lakukan canary dan pantau p99, lock waits, memori, dan I/O.

Kesalahan umum

• Kesalahan → menjalankan EXPLAIN ANALYZE langsung di server utama (primary); Penyebab gagal → perintah tersebut mengeksekusi pernyataan sebenarnya dan menambah beban atau efek samping; Solusi → gunakan replika, transaksi baca-saja, atau rollback yang aman.
• Kesalahan → langsung menambahkan indeks setelah melihat adanya pembacaan buffer; Penyebab gagal → pembacaan bisa disebabkan oleh cold cache, kesalahan statistik, atau latensi penyimpanan; Solusi → korelasikan sampel berulang dengan metrik I/O.
• Kesalahan → menyetel work_mem global terlalu tinggi; Penyebab gagal → setiap operator dan sesi konkuren mengonsumsinya; Solusi → hitung anggaran konkurensi dan uji setelan sesi/kueri secara canary.
• Kesalahan → hanya membandingkan waktu eksekusi; Penyebab gagal → tail latency, amplifikasi penulisan, dan stabilitas rencana menjadi tersembunyi; Solusi → evaluasi p99, metrik sumber daya, dan sampel regresi secara bersamaan.

Pertanyaan lanjutan

Mengapa kueri bisa lambat dengan jumlah shared hit yang tinggi?

Hit berarti halaman berasal dari shared buffers; ini tidak membuat CPU, pengurutan, lock waits, atau pemrosesan operator menjadi murah. Gabungkan perulangan, memori/disk node, distribusi waktu eksekusi, dan wait events untuk menemukan titik hambatannya.

Mengapa tidak mengatur work_mem ke setengah dari memori fisik?

Satu kueri dapat memiliki beberapa operator dan banyak sesi konkuren, yang masing-masing mengonsumsi work_mem. Pecahan sederhana dapat melampaui anggaran puncak dan memicu OOM. Hitung berdasarkan konkurensi, jumlah operator, ukuran pool, dan anggaran node, lalu validasi dengan pemantauan.

Kapan Anda harus menyegarkan statistik daripada menulis ulang SQL?

Jika estimasi tetap jauh dari distribusi aktual karena data berubah atau kolom yang berkorelasi tidak memiliki statistik, segarkan atau tambahkan statistik yang diperluas terlebih dahulu. Tulis ulang atau buat indeks hanya setelah estimasi dapat dipercaya dan pemilihan operator masih gagal.