Bagaimana Cara Mendiagnosis dan Mengoptimalkan Kueri PostgreSQL yang Lambat?

Konteks Soal dan Penerapan

Tabel orders berisi 200 juta baris dan menopang 3.000 operasi penyisipan atau pembaruan status per detik. Sekitar 2% dari semua pesanan berstatus pending, meskipun proporsi tersebut bervariasi secara materi di seluruh tenant. Sebuah dasbor operasi menjalankan kueri berikut sebanyak 40 kali per detik. Kueri ini meminta 50 pesanan tertunda terbaru untuk satu tenant dalam 30 hari terakhir:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Tabel tersebut sudah memiliki dua indeks B-tree kolom tunggal, orders(tenant_id) dan orders(created_at). Pemantauan aplikasi menunjukkan bahwa p95 kueri naik dari 120 ms menjadi 2,8 detik sementara CPU, memori, dan jumlah koneksi PostgreSQL tetap berada di bawah kapasitas. Sebuah rencana yang diambil pada replika skala produksi mengestimasi 8.000 baris tetapi menghasilkan 420.000 baris sebelum pengurutan, menyentuh sekitar 120.000 shared buffers, dan akhirnya menerapkan pengurutan top-N untuk mengembalikan 50 baris.

Pertanyaan ini menargetkan PostgreSQL 18. Ukuran tabel, throughput, dan angka-angka rencana adalah asumsi wawancara yang digunakan untuk membuat penalaran dapat diuji. Tujuannya adalah untuk meningkatkan pembacaan frekuensi tinggi ini sambil mengendalikan risiko pembuatan indeks, penyimpanan, dan amplifikasi penulisan (write amplification). Sharding, caching, dan ekspansi perangkat keras berada di luar cakupan tahap pertama.

Apa yang Dievaluasi oleh Pewawancara

Sinyal pertama adalah konfirmasi beban kerja sebelum melakukan perubahan SQL. Jawaban yang kuat memisahkan "eksekusi tunggal paling lambat" dari "biaya kumulatif terbesar." Kueri yang rata-rata membutuhkan waktu 80 ms pada 1.000 panggilan per detik mungkin lebih layak mendapat perhatian daripada kueri lima detik yang terjadi sesekali. pg_stat_statements menyediakan jumlah panggilan, total waktu eksekusi, dan rata-rata waktu eksekusi. Pemantauan atau penelusuran (tracing) aplikasi tetap harus menyediakan p95 dan persentil spesifik tiap tenant.

Sinyal kedua adalah membaca rencana eksekusi sebagai rantai sebab-akibat. Bukti yang relevan mencakup kesenjangan kardinalitas antara 8.000 dan 420.000, baris yang dikeluarkan oleh node pemindaian, loops tiap node, aktivitas buffer, metode pengurutan, dan apakah predikat muncul dalam kondisi indeks atau Filter pasca-pemindaian. Melihat Seq Scan atau melihat bahwa suatu indeks digunakan tidak serta-merta membuktikan apakah rencananya baik.

Sinyal ketiga adalah menurunkan urutan kunci dari bentuk kueri. tenant_id adalah predikat kesetaraan (equality). created_at adalah predikat rentang (range) sekaligus pengurutan yang diminta. status='pending' adalah status bisnis yang tetap dan jarang terjadi. Indeks yang sesuai harus masuk ke rentang tertunda milik satu tenant, membaca dalam urutan timestamp, dan berhenti begitu 50 baris ditemukan.

Terakhir, pewawancara mencari disiplin validasi dan peluncuran (rollout). Sebuah indeks mengonsumsi penyimpanan dan I/O pembuatan sambil menambah beban kerja pada operasi penyisipan dan transisi status. Jawaban yang lengkap membandingkan rencana pada data skala produksi, cache dingin dan hangat, berbagai ukuran tenant, penulisan bersamaan (concurrent writes), dan ambang batas rollback yang eksplisit. Satu eksekusi lokal yang lebih cepat bukanlah bukti yang cukup.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

• Metrik latensi mana yang mengalami regresi? p95 per tenant, p95 global, latensi rata-rata, dan total waktu basis data menyiratkan prioritas yang berbeda. Pastikan kapan regresi dimulai dan apakah ini sejalan dengan pertumbuhan data, distribusi parameter, deployment, atau perubahan statistik.
• Berapa rasio pesanan tertunda dan bagaimana distribusi tenant? Indeks parsial sangat menarik jika pending tetap berada di kisaran 1% hingga 2% dari seluruh baris. Keunggulan ukurannya menyusut jika separuh tabel berstatus tertunda. Nilai rata-rata juga menyembunyikan ketimpangan antara tenant yang sangat besar dan yang kecil.
• Apakah kueri selalu memuat nilai literal status='pending'? Indeks parsial hanya dapat digunakan jika perencana (planner) dapat membuktikan bahwa kondisi kueri mengimplikasikan predikat indeks. Parameter status yang umum (generic) dapat mencegah pembuktian tersebut.
• Kolom mana dan jaminan konsistensi apa yang diperlukan? Mengembalikan teks besar, JSON, atau sepuluh tabel gabungan (join) akan cepat membuat indeks penutup (covering index) membengkak (bloat). Konfirmasikan kolom mana yang benar-benar dibutuhkan oleh endpoint daftar ini.
• Seberapa berat beban penulisan tabel tersebut, dan peluncuran seperti apa yang diizinkan? Pada 3.000 penulisan per detik, lebar indeks dan biaya transisi harus diukur. Produksi mungkin memerlukan CREATE INDEX CONCURRENTLY, bersama dengan jendela pembuatan yang lebih panjang, pemindaian ekstra, dan prosedur untuk membersihkan indeks yang tidak valid setelah terjadi kegagalan.
• Bisakah rencana aktual dijalankan pada replika skala produksi? EXPLAIN ANALYZE mengeksekusi pernyataan tersebut. Bahkan SELECT dapat menciptakan beban material, sementara pernyataan yang mengubah data melakukan efek sampingnya. Gunakan replika, parameter berbatas, atau EXPLAIN biasa terlebih dahulu.

Kerangka Jawaban 30 Detik

"Pertama, saya akan mengorelasikan p95 aplikasi dengan panggilan pg_stat_statements, total waktu basis data, dan tenant yang lambat, sambil menyingkirkan kemungkinan antrean kunci (lock waits) dan dependensi eksternal. Kemudian saya akan menjalankan EXPLAIN (ANALYZE, BUFFERS) dengan parameter representatif pada replika skala produksi dan memeriksa estimasi versus baris aktual, loop, buffer, dan node pengurutan. Di sini, dua indeks kolom tunggal masih menghasilkan 420.000 kandidat sebelum pengurutan. Karena kueri selalu menargetkan status tertunda yang langka, saya akan menguji indeks penutup parsial pada (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending', yang dapat membaca 50 baris pertama secara berurutan. Jika status harus diparameterisasi, saya akan membandingkannya dengan indeks (tenant_id, status, created_at DESC) penuh. Saya akan memvalidasi p95, kerja buffer, ukuran indeks, dan latensi penulisan di berbagai ukuran tenant, cache dingin dan hangat, serta penulisan bersamaan sebelum membuatnya secara concurrent dengan ambang batas rollback."

Pembahasan Mendalam Langkah demi Langkah

Langkah 1: Tentukan prioritas dari beban kerja nyata

Petakan rute, tenant, rentang parameter, dan p95 aplikasi ke kueri basis data yang dinormalisasi. Jika pg_stat_statements diaktifkan, mulailah dengan konsumsi sumber daya kumulatif:

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

total_exec_time menemukan waktu basis data yang terakumulasi melalui panggilan yang sering, mean_exec_time menyoroti eksekusi individual yang mahal, dan calls menunjukkan faktor pengali. Tampilan ini tidak memperlihatkan p95 atau menjelaskan mengapa tenant atau parameter tertentu lambat, jadi pertahankan persentil di sisi aplikasi dan kohort parameter. Jika latensi terutama disebabkan oleh menunggu kunci (lock waiting), antrean koneksi, jaringan, atau panggilan downstream, mengubah rencana kueri saja tidak akan memperbaiki latensi ujung-ke-ujung (end-to-end).

Langkah 2: Kumpulkan bukti eksekusi aktual dengan aman

Periksa bentuknya dengan EXPLAIN biasa terlebih dahulu. Kemudian, pada replika skala produksi atau lingkungan terkendali, jalankan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Baca dari bawah ke atas mulai dari node terdalam yang memiliki waktu aktual substansial. Perlakukan actual rows × loops sebagai bagian dari total kerja suatu node. Buffers: shared read mencatat blok yang harus dibaca dari penyimpanan; shared hit berarti blok tersebut sudah ada di shared buffers, tetapi hit tersebut tetap mengonsumsi bandwidth CPU dan memori. Pengurutan berbasis disk melaporkan pengurutan eksternal dan I/O blok sementara, yang mendorong perlunya investigasi terhadap ukuran input dan anggaran memorinya.

Estimasi 8.000 baris versus 420.000 baris aktual merupakan kesalahan 52,5 kali lipat. Statistik mungkin sudah usang, atau statistik kolom tunggal gagal merepresentasikan korelasi antara tenant_id dan status. Jalankan ANALYZE dengan cakupan yang sesuai dan ukur kembali. Jika korelasi yang stabil memengaruhi rencana secara material, uji statistik yang diperluas (extended statistics) untuk kolom-kolom tersebut. Statistik yang diperluas membawa biaya pengumpulan dan perencanaan, jadi buatlah hanya untuk kolom yang sangat terkait yang dapat meningkatkan estimasi penting.

Langkah 3: Turunkan indeks dari bentuk kueri

Perencana mungkin menggabungkan indeks kolom tunggal yang ada dengan BitmapAnd, tetapi hasil bitmap tidak mempertahankan pengurutan B-tree tunggal. Rencana ini masih dapat mengunjungi banyak halaman heap dan melakukan pengurutan. Sebagai alternatif, perencana dapat memilih satu indeks dan memfilter predikat lainnya setelahnya. Memiliki kedua indeks hanya menciptakan jalur kandidat; ini tidak menghasilkan jalur yang dibentuk khusus untuk WHERE + ORDER BY + LIMIT.

Untuk status tertunda yang tetap dan langka, bandingkan indeks parsial terlebih dahulu:

CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

Indeks pertama hanya menyimpan pesanan tertunda dan oleh karena itu harusnya berukuran lebih kecil di bawah distribusi yang disebutkan. Setelah menemukan kesetaraan tenant, created_at membatasi rentang 30 hari sekaligus menyediakan urutan menurun (descending), memungkinkan pemindaian berhenti setelah 50 baris. id dan total_cents adalah kolom muatan (payload) di INCLUDE; kolom ini tidak berpartisipasi dalam pencarian atau pengurutan dan hanya memungkinkan terjadinya pembacaan index-only scan.

Indeks multikolom penuh cocok untuk status yang diparameterisasi atau beberapa status yang menggunakan pola kueri yang sama. Kolom kesetaraan B-tree di depan mempersempit rentang sebelum rentang waktu dan pengurutan. Aturan "selalu letakkan kolom paling selektif di awal" terlalu sederhana; kesetaraan, rentang, pengurutan, dan penggunaan kembali di berbagai template kueri nyata bersama-sama menentukan urutan kunci.

Langkah 4: Nyatakan batasan indeks parsial dan penutup (covering)

Indeks parsial hanya dapat digunakan jika perencana dapat membuktikan bahwa kondisi kueri mencakup status='pending'. Dalam prepared statement generik yang ditulis sebagai status = $2, parameter tersebut tidak dapat mengimplikasikan predikat untuk setiap nilai yang mungkin, sehingga perencana dapat mengabaikan indeks parsial. Kueri operasi khusus dapat mempertahankan nilai literal tersebut, atau rancangan dapat menggunakan indeks multikolom penuh. Verifikasi keputusan tersebut dengan template kueri aktual alih-alih menyimpulkannya hanya dari definisi indeks.

INCLUDE tidak menjamin Index Only Scan pada setiap eksekusi. PostgreSQL tetap harus memverifikasi visibilitas MVCC. Ketika sebuah halaman heap tidak memiliki bit all-visible, pemindaian akan mengunjungi heap. Penyisipan yang sering dan perubahan status pada tabel yang panas (hot table) membuat kunjungan tersebut lebih sering terjadi. Jika rencana masih melaporkan banyak Heap Fetches, bandingkan dengan indeks yang lebih sempit tanpa kolom muatan. Indeks yang lebar juga meningkatkan penggunaan disk dan cache serta menambah beban pemeliharaan pada setiap penulisan yang terpengaruh.

Langkah 5: Validasi manfaat dan biaya

Bandingkan rencana sebelum dan sesudah menggunakan parameter representatif yang sama: tenant yang sangat besar, median, dan kecil; tenant dengan banyak baris tertunda dan yang hampir tidak ada; cache dingin dan cache hangat. Catat distribusi latensi, baris aktual, buffer, perilaku pengurutan, I/O sementara, Heap Fetches, dan ukuran indeks. Satu kali waktu berlalu (elapsed time) sangat sensitif terhadap cache dan konkurensi, sedangkan beban kerja rencana menjelaskan mengapa hasilnya berubah.

Kemudian lakukan uji beban (load test) pada penyisipan dan transisi pending → paid pada rasio produksi. Pesanan yang selesai akan menghapus entri dari indeks parsial; indeks penuh akan memperbarui kunci statusnya. Keduanya membebankan kerja penulisan. Kriteria penerimaan dapat mensyaratkan p95 baca berada dalam target tanpa regresi p99 yang material, pengurangan besar dalam baris kandidat dan kerja blok bersama, serta p95 tulis, volume WAL, penyimpanan, dan jeda replikasi (replication lag) tetap berada dalam batas anggaran.

Sebelum peluncuran, pastikan ketersediaan ruang disk dan pemantauan untuk pembuatan indeks bersamaan (concurrent build). CREATE INDEX CONCURRENTLY memungkinkan penyisipan, pembaruan, dan penghapusan terus berjalan, tetapi prosesnya memakan waktu lebih lama dan dapat meninggalkan indeks yang tidak valid setelah terjadi kegagalan. Setelah dibangun, verifikasi bahwa template kueri produksi benar-benar memilih jalur baru dan amati selama satu siklus puncak penuh. Jika latensi tulis atau replikasi melampaui ambang batasnya, hapus jalur kueri baru dan drop indeks baru sesuai dengan prosedur operasional. Pertahankan indeks lama sampai penggantinya melewati masa stabil dan tidak ada beban kerja lain yang bergantung padanya.

Contoh Jawaban Berkualitas Tinggi

"Pertama, saya akan memastikan bahwa SQL ini layak diprioritaskan. Pemantauan aplikasi memberi saya data p95 dan tenant yang lambat, sementara pg_stat_statements memberikan jumlah panggilan, total waktu eksekusi, dan rata-rata waktu eksekusi. Jika kueri ini berjalan 40 kali per detik dan berperingkat tinggi dalam waktu basis data kumulatif, saya akan menangkap template kueri nyata dan tenant yang representatif, lalu menjalankan EXPLAIN (ANALYZE, BUFFERS) pada replika skala produksi.

Masalah utama dalam rencana ini adalah pemindaian yang lebar: perencana mengestimasi 8.000 baris, 420.000 baris benar-benar mencapai pengurutan top-N, dan kueri menyentuh sekitar 120.000 buffer. Indeks kolom tunggal mungkin menggabungkan filter, tetapi mereka tidak langsung membuat rentang berurutan untuk tenant_id + pending + created_at DESC. Saya akan memperbarui statistik dan mengukurnya kembali. Jika korelasi tenant dan status terus menyebabkan kesalahan estimasi, saya akan menguji statistik yang diperluas.

Karena kueri operasi selalu meminta status tertunda yang langka, saya akan menguji indeks parsial pada (tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending'. Ini membatasi populasi yang diindeks, masuk ke rentang waktu berurutan milik satu tenant, dan dapat berhenti setelah 50 baris. Jika aplikasi memparameterisasi status dan mengueri beberapa nilai, saya akan membandingkannya dengan indeks (tenant_id, status, created_at DESC) penuh. INCLUDE hanya memungkinkan potensi Index Only Scan; halaman yang aktif diperbarui mungkin masih memerlukan kunjungan ke heap, jadi saya akan memeriksa Heap Fetches.

Validasi akan mencakup tenant besar, menengah, dan kecil, cache dingin dan hangat, serta penulisan bersamaan. Saya akan membandingkan p95 dan p99, baris kandidat, buffer, I/O sementara, ukuran indeks, WAL, latensi tulis, dan jeda replikasi. Saya akan membangun indeks secara concurrent di produksi, mengonfirmasi bahwa template nyata menggunakan rencana baru, dan memantaunya selama satu siklus puncak penuh. Jika peningkatan baca lemah atau jalur tulis melebihi anggaran, saya akan mencabut jalur tersebut dan menghapus indeks baru daripada menyembunyikan rencana yang tidak jelas di balik penambahan perangkat keras."

Kesalahan Umum

• Menambahkan indeks begitu SQL terlihat lambat → Frekuensi, parameter, dan jenis waktu tunggu (wait type) belum diketahui, sehingga tim dapat mengoptimalkan kueri dengan prioritas rendah → Korelasikan persentil aplikasi, pg_stat_statements, dan parameter nyata terlebih dahulu.
• Memperlakukan setiap Seq Scan sebagai cacat/masalah → Pemindaian sekuensial bisa lebih murah untuk tabel kecil atau kueri yang mengembalikan sebagian besar data tabel → Bandingkan baris aktual, buffer, dan total biaya terhadap alternatif lain.
• Hanya memeriksa apakah rencana menunjukkan Index Scan → Pemindaian indeks masih dapat membaca ratusan ribu entri dan berulang kali mengunjungi heap → Periksa actual rows × loops, Filter, Buffers, dan Heap Fetches.
• Mengabaikan kesenjangan baris antara estimasi dan aktual → Kardinalitas yang salah dapat menyebabkan join, pemindaian, dan pengurutan yang buruk → Perbarui statistik dan uji statistik yang diperluas untuk kolom berkorelasi yang stabil.
• Mengasumsikan beberapa indeks kolom tunggal sama dengan satu indeks multikolom → Kombinasi bitmap umumnya kehilangan pengurutan yang diperlukan dan dapat mengunjungi banyak halaman heap → Turunkan kunci dari kesetaraan, rentang, pengurutan, dan LIMIT.
• Menempatkan setiap kolom yang dikembalikan ke dalam INCLUDE → Pembengkakan (bloat) indeks mengurangi efisiensi cache dan memperbesar biaya tulis → Hanya cakup kolom sempit yang dibutuhkan oleh kueri bernilai tinggi.
• Membangun indeks parsial tanpa menguji template kueri → Predikat yang diparameterisasi mungkin tidak mengimplikasikan predikat indeks pada saat perencanaan → Lakukan EXPLAIN pada bentuk prepared statement yang sama dengan yang digunakan di produksi.
• Menjalankan EXPLAIN ANALYZE pada SQL basis data utama secara sembarangan → Perintah ini mengeksekusi pernyataan; pembacaan berat menciptakan beban dan penulisan melakukan efek samping perubahan data → Gunakan EXPLAIN biasa terlebih dahulu dan dapatkan bukti aktual pada replika atau di dalam transaksi yang terkendali.
• Melaporkan satu pengujian yang turun dari 2,8 detik ke angka yang lebih rendah → Cache, parameter, dan konkurensi dapat menciptakan hasil positif palsu yang kebetulan → Bandingkan distribusi, kerja rencana, dan siklus puncak penuh.

Pertanyaan Lanjutan dan Tanggapan

Lanjutan 1: Mengapa pemindaian sekuensial bisa lebih cepat daripada pemindaian indeks?

Ketika kueri membaca sebagian besar dari sebuah tabel, akses sekuensial menghindari banyak akses acak (random access) yang terjadi saat menelusuri indeks dan mengambil halaman heap yang tersebar. Tabel kecil mungkin hanya menempati beberapa halaman, membuat pemindaian langsung juga menjadi lebih murah. Bandingkan buffer dan total waktu yang berlalu pada data nyata daripada menilai suatu rencana hanya berdasarkan nama node.

Lanjutan 2: Mengapa indeks (tenant_id) dan (created_at) yang terpisah tidak mencukupi?

Perencana dapat memilih satu indeks dan memfilter setelahnya atau menggabungkan keduanya dengan BitmapAnd. Bitmap mengumpulkan lokasi tuple kandidat dan tidak mempertahankan pengurutan B-tree dari created_at, sehingga umumnya membaca banyak halaman heap dan kemudian melakukan pengurutan. Indeks multikolom menempatkan kesetaraan tenant, rentang waktu, dan pengurutan pada satu jalur akses yang teratur, memungkinkan LIMIT 50 berhenti lebih awal.

Lanjutan 3: Mengapa PostgreSQL mungkin tidak pernah menggunakan indeks parsial?

Perencana harus mengenali selama proses perencanaan bahwa kondisi kueri mengimplikasikan predikat indeks. status='pending' cocok secara langsung; status=$2 tidak dapat menjamin kecocokan untuk setiap parameter. Ekspresi yang ditulis secara berbeda, perubahan distribusi data, atau estimasi biaya yang membuat akses heap terlihat mahal juga dapat memilih jalur lain. Lakukan EXPLAIN pada template prepared statement aktual, dan gunakan indeks multikolom penuh ketika status harus tetap generik.

Lanjutan 4: Bagaimana jika estimasi tetap salah hingga 50 kali lipat setelah ANALYZE?

Periksa cakupan pengambilan sampel (sampling coverage), target statistik per kolom, dan apakah data berubah secara tiba-tiba. Jika tenant_id dan status sangat berkorelasi, statistik kolom tunggal menganggap predikat-predikat tersebut saling independen. Uji statistik ketergantungan atau MCV yang diperluas untuk kelompok kolom tersebut. Statistik yang diperluas meningkatkan estimasi; mereka tidak menciptakan jalur akses yang hilang, jadi validasi indeks dan bentuk kueri secara terpisah.

Lanjutan 5: Pembacaan membaik, tetapi p95 penulisan meningkat. Bagaimana Anda memutuskannya?

Kembalilah ke SLO dan total beban kerja: ukur waktu basis data yang dihemat pada pembacaan, pengguna yang terpengaruh oleh regresi penulisan, serta perubahan dalam WAL, penyimpanan, dan jeda replikasi. Jika hanya satu status langka yang memerlukan percepatan, indeks parsial yang sempit dapat mengungguli indeks penutup penuh. Jika kolom payload menyebabkan pembengkakan, hapus INCLUDE dan terima beberapa akses heap. Ketika anggaran penulisan terlampaui, batalkan rencana indeks tersebut dan cari jalur akses yang lebih kecil melalui cakupan kueri, jaminan paginasi, atau model data.