Mendeteksi Bottleneck SQL pada Tabel Audit yang Terus Membesar

Mendeteksi bottleneck SQL pada tabel audit yang terus membesar bukan sekadar soal menambah indeks. Pada praktiknya, query melambat karena kombinasi volume data, pola akses yang berubah, penggunaan OFFSET besar, indeks yang tidak sesuai urutan filter, serta biaya tulis yang meningkat akibat terlalu banyak indeks.

Dalam konteks data audit atau log, kebutuhan investigasi integritas biasanya membuat tim mulai menjalankan pencarian historis yang lebih berat: rentang waktu panjang, filter multi-kolom, urutan terbaru, dan ekspor data. Jika tabel audit awalnya hanya dipakai sebagai append-only log, pola ini dapat mengubah beban kerja database secara drastis. Fokus artikel ini adalah cara mengenali gejalanya, mengukur baseline, membaca rencana eksekusi, lalu memilih perbaikan yang paling masuk akal tanpa merusak performa write.

Catatan: tabel audit berbeda dari tabel transaksi utama. Ia cenderung write-heavy, bertumbuh cepat, jarang di-update, tetapi sesekali menerima query baca yang mahal saat audit, forensic, debugging, atau compliance. Strategi tuning harus mengikuti karakter ini.

Gejala Bottleneck pada Tabel Audit yang Membesar

Sebelum mengubah skema atau menambah indeks, pastikan Anda mengenali gejala yang benar-benar menunjukkan bottleneck SQL, bukan hanya lonjakan beban sesaat.

Gejala yang paling sering muncul

• Query daftar audit terbaru makin lambat walau hanya menampilkan 50-100 baris.

• Pencarian berdasarkan entity_id, actor_id, atau rentang waktu memicu lonjakan I/O.

• Halaman admin atau endpoint investigasi timeout saat masuk ke halaman tinggi, misalnya page 1000.

• Autovacuum, checkpoint, atau background maintenance mulai mengganggu latency write pada sistem yang sangat aktif.

• Ukuran indeks membesar mendekati atau melampaui ukuran data, tetapi query tetap lambat.

• Insert ke tabel audit melambat karena setiap insert harus memperbarui terlalu banyak indeks.

Pola query yang biasanya menjadi sumber masalah

• Menyaring berdasarkan waktu lalu mengurutkan descending: WHERE created_at >= ? ORDER BY created_at DESC LIMIT 100

• Mencari histori satu entitas: WHERE entity_type = ? AND entity_id = ? ORDER BY created_at DESC

• Pencarian aktor: WHERE actor_id = ? AND created_at BETWEEN ? AND ?

• Pagination dengan OFFSET besar.

• Query yang mengambil terlalu banyak kolom dari payload audit yang besar, misalnya JSON penuh padahal daftar hanya perlu beberapa field.

Mulai dari Baseline Metrik, Bukan Tebakan

Tuning tanpa baseline sering berakhir pada perubahan yang terasa benar tetapi tidak menyelesaikan akar masalah. Ukur dulu apa yang lambat, kapan melambat, dan query mana yang paling mahal.

Metrik minimum yang perlu dikumpulkan

1. Frekuensi query: query apa yang paling sering dipanggil.

2. Latency: p50, p95, p99 bila tersedia.

3. Rows examined vs rows returned: apakah database memindai terlalu banyak baris untuk hasil kecil.

4. Buffer/cache hit: apakah query mostly memory-bound atau disk-bound.

5. Ukuran tabel dan indeks: pertumbuhan harian/mingguan.

6. Laju insert: penting untuk menilai dampak indeks tambahan.

Pertanyaan investigasi yang membantu

• Query lambat terjadi pada semua filter atau hanya kombinasi tertentu?

• Apakah query lambat saat cold cache atau juga saat warm cache?

• Apakah bottleneck dominan di CPU, I/O, lock, atau sort temporary?

• Apakah latensi naik seiring nomor halaman? Jika ya, curigai OFFSET.

• Apakah insert audit memburuk setelah penambahan indeks baru?

Contoh baseline query yang perlu diaudit

-- Daftar audit terbaru untuk satu entitas
SELECT id, created_at, actor_id, action, entity_type, entity_id
FROM audit_log
WHERE entity_type = 'order'
  AND entity_id = 12345
ORDER BY created_at DESC
LIMIT 50;

-- Pencarian audit rentang waktu
SELECT id, created_at, actor_id, action
FROM audit_log
WHERE created_at >= '2026-05-01'
  AND created_at < '2026-06-01'
ORDER BY created_at DESC
LIMIT 100;

-- Pagination tradisional yang sering bermasalah
SELECT id, created_at, actor_id, action
FROM audit_log
ORDER BY created_at DESC
LIMIT 100 OFFSET 500000;

Jika query ketiga jauh lebih lambat daripada query halaman awal, Anda hampir pasti sedang membayar biaya skip baris yang besar.

Menggunakan EXPLAIN dan ANALYZE untuk Menemukan Akar Masalah

Jangan menebak apakah indeks dipakai. Gunakan EXPLAIN, dan bila aman di lingkungan yang tepat, gunakan EXPLAIN ANALYZE untuk melihat eksekusi nyata.

Apa yang perlu dibaca dari rencana eksekusi

• Apakah terjadi full table scan saat seharusnya bisa memakai indeks.

• Urutan operasi filter dan sort: apakah database menyaring dulu lalu mengurutkan sedikit data, atau justru harus mengurutkan hasil besar.

• Rows estimated vs actual: perbedaan besar bisa menandakan statistik tidak akurat.

• Biaya sort: terutama bila ORDER BY tidak didukung indeks yang sesuai.

• Heap/table lookup: bila indeks dipakai untuk mencari, tetapi engine masih harus membaca banyak baris dari tabel utama.

Contoh investigasi query bermasalah

EXPLAIN ANALYZE
SELECT id, created_at, actor_id, action
FROM audit_log
WHERE entity_type = 'order'
  AND entity_id = 12345
ORDER BY created_at DESC
LIMIT 50;

Jika hasil menunjukkan scan besar lalu sort, kemungkinan indeks yang ada hanya pada entity_id atau created_at secara terpisah, tetapi tidak mendukung kombinasi filter dan urutan. Pada tabel audit, itu sangat umum.

Masalah klasik: indeks ada, tapi tetap tidak efektif

Misalnya Anda punya indeks tunggal:

CREATE INDEX idx_audit_entity_id ON audit_log(entity_id);
CREATE INDEX idx_audit_created_at ON audit_log(created_at);

Untuk query:

SELECT id, created_at, actor_id, action
FROM audit_log
WHERE entity_type = 'order'
  AND entity_id = 12345
ORDER BY created_at DESC
LIMIT 50;

Database mungkin tetap harus membaca banyak baris yang cocok di entity_id, lalu memfilter entity_type, lalu mengurutkan berdasarkan created_at. Solusinya sering kali adalah indeks komposit yang mengikuti pola filter dan sort.

Indeks Komposit, Covering Index, dan Kenapa Urutannya Penting

Pada tabel audit, indeks komposit biasanya lebih berguna daripada menumpuk banyak indeks tunggal. Kuncinya bukan sekadar kolom apa yang diindeks, tetapi juga urutannya.

Contoh indeks komposit yang lebih sesuai

CREATE INDEX idx_audit_entity_lookup
ON audit_log(entity_type, entity_id, created_at DESC);

Indeks ini membantu query histori per entitas karena:

• Filter paling selektif diletakkan di depan: entity_type dan entity_id.

• created_at DESC membantu urutan hasil terbaru tanpa sort tambahan yang mahal.

• LIMIT 50 memungkinkan engine berhenti lebih cepat setelah cukup baris ditemukan.

Sebelum dan sesudah

Sebelum, query sering bergantung pada scan dan sort:

SELECT id, created_at, actor_id, action
FROM audit_log
WHERE entity_type = 'order'
  AND entity_id = 12345
ORDER BY created_at DESC
LIMIT 50;

Sesudah, query yang sama bisa memanfaatkan indeks komposit dengan akses lebih terarah. SQL query tidak berubah, tetapi struktur indeks membuat rencana eksekusi jauh lebih efisien.

Kapan covering index berguna

Covering index berguna saat daftar audit hanya membutuhkan beberapa kolom kecil dan sering dipanggil. Tujuannya agar engine bisa menjawab query langsung dari indeks tanpa bolak-balik membaca tabel utama sebanyak mungkin.

-- Bentuk konseptual; dukungan detail berbeda antar database
CREATE INDEX idx_audit_entity_covering
ON audit_log(entity_type, entity_id, created_at DESC, id, actor_id, action);

Manfaatnya:

• Mengurangi lookup tambahan ke tabel utama.

• Cocok untuk endpoint daftar atau timeline audit yang kolomnya tetap.

Trade-off-nya:

• Ukuran indeks bisa membesar signifikan.

• Setiap insert menjadi lebih mahal karena indeks lebih lebar.

• Tidak cocok jika Anda memasukkan kolom payload besar, misalnya JSON lengkap.

Prinsip praktis: buat indeks untuk pola query yang benar-benar dominan, bukan untuk semua kemungkinan filter. Tabel audit sering memiliki banyak pola pencarian ad hoc; jika Anda mencoba mengoptimalkan semuanya dengan indeks, performa write akan memburuk dan maintenance menjadi mahal.

Kesalahan umum saat memilih indeks

• Mengindeks kolom ber-cardinality rendah sendirian, misalnya action, lalu berharap query menjadi cepat.

• Membuat banyak indeks mirip yang overlap kuat.

• Menambahkan indeks pada kolom JSON atau teks besar tanpa use case yang jelas.

• Tidak menyesuaikan indeks dengan ORDER BY.

• Mengambil terlalu banyak kolom sehingga keuntungan covering index hilang.

Masalah OFFSET Besar dan Kenapa Keyset Pagination Biasanya Lebih Tepat

Pagination tradisional dengan LIMIT ... OFFSET ... sering menjadi penyebab utama query lambat di tabel audit besar. Penyebabnya sederhana: untuk mencapai halaman jauh, database tetap harus melewati banyak baris terlebih dahulu.

Contoh query yang buruk pada data besar

SELECT id, created_at, actor_id, action
FROM audit_log
ORDER BY created_at DESC, id DESC
LIMIT 100 OFFSET 500000;

Walau hasil akhirnya hanya 100 baris, engine mungkin harus memproses ratusan ribu baris untuk membuang yang berada sebelum offset tersebut.

Gunakan keyset pagination

Alih-alih mengatakan “lompat ke baris ke-500001”, keyset pagination mengatakan “ambil 100 baris setelah item terakhir yang sudah saya lihat”. Ini jauh lebih stabil untuk tabel audit yang terus bertambah.

-- Halaman pertama
SELECT id, created_at, actor_id, action
FROM audit_log
ORDER BY created_at DESC, id DESC
LIMIT 100;

-- Halaman berikutnya: gunakan cursor dari baris terakhir halaman sebelumnya
SELECT id, created_at, actor_id, action
FROM audit_log
WHERE (created_at, id) < ('2026-06-01 10:15:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 100;

Kenapa keyset pagination bekerja lebih baik

• Engine bisa melanjutkan dari posisi indeks, bukan menghitung dan membuang offset besar.

• Latency lebih konsisten walau pengguna berjalan jauh ke data historis.

• Lebih cocok untuk data append-only seperti audit log.

Indeks yang mendukung keyset pagination

CREATE INDEX idx_audit_created_id_desc
ON audit_log(created_at DESC, id DESC);

Jika daftar audit juga selalu difilter, masukkan filter utama ke depan indeks. Contoh untuk histori per entitas:

CREATE INDEX idx_audit_entity_created_id_desc
ON audit_log(entity_type, entity_id, created_at DESC, id DESC);

Keterbatasan keyset pagination

• Tidak cocok jika UI benar-benar membutuhkan loncat langsung ke nomor halaman arbitrer.

• Perlu cursor stabil, biasanya kombinasi kolom waktu dan primary key.

• Harus hati-hati pada data dengan nilai waktu sama; karena itu id sering dipakai sebagai tie-breaker.

Partisi: Relevan Bila Skala dan Pola Aksesnya Tepat

Partisi bisa membantu tabel audit besar, tetapi bukan obat universal. Ia paling berguna saat query sangat bergantung pada rentang waktu, retensi data jelas, dan maintenance tabel tunggal sudah berat.

Kapan partisi layak dipertimbangkan

• Data audit tumbuh sangat cepat dan hampir semua query memfilter berdasarkan waktu.

• Ada kebijakan retensi, misalnya per bulan atau per kuartal.

• Proses penghapusan data lama terlalu mahal bila dilakukan pada tabel tunggal besar.

• Maintenance seperti reindex, vacuum, atau archiving pada tabel tunggal sudah menyulitkan.

Bentuk partisi yang paling umum

Untuk audit log, partisi berdasarkan rentang waktu biasanya paling masuk akal, misalnya per bulan. Keuntungannya:

• Partition pruning: query rentang waktu sempit hanya menyentuh partisi relevan.

• Retensi lebih mudah: hapus atau arsipkan partisi lama.

• Ukuran indeks per partisi lebih kecil dan lebih mudah dikelola.

Namun ada trade-off penting

• Desain skema dan operasi maintenance menjadi lebih kompleks.

• Query tanpa filter waktu tetap bisa menyentuh banyak partisi.

• Indeks harus dipikirkan ulang di level partisi.

• Partisi yang terlalu banyak dapat menciptakan overhead baru.

Aturan praktis: gunakan partisi jika masalah utama memang berasal dari skala waktu dan retensi. Jika bottleneck Anda adalah OFFSET besar atau indeks yang salah, partisi saja tidak akan menyelesaikan masalah.

Write Amplification: Kapan Indeks Justru Merugikan

Pada tabel audit, setiap event baru biasanya melakukan insert. Setiap indeks tambahan berarti kerja tambahan saat write. Inilah sebabnya optimasi baca yang agresif sering memukul throughput tulis.

Bagaimana write amplification terjadi

• Setiap insert harus menulis ke tabel utama dan semua indeks terkait.

• Indeks lebar membutuhkan lebih banyak ruang dan I/O.

• Fragmentasi dan maintenance indeks meningkat seiring pertumbuhan data.

• Cache bisa terisi oleh indeks yang jarang dipakai, mengusir halaman data yang lebih penting.

Kapan indeks sebaiknya tidak ditambahkan

• Query target sangat jarang dipakai dan bukan bagian dari jalur kritis.

• Kolom yang diindeks sangat tidak selektif.

• Indeks baru overlap hampir sepenuhnya dengan indeks yang sudah ada.

• Biaya write lebih penting daripada akselerasi query investigasi yang sesekali dijalankan.

• Masalah sebenarnya ada pada payload besar, desain query, atau penggunaan SELECT *, bukan pada kurangnya indeks.

Pendekatan yang lebih aman

• Buat indeks berdasarkan 1-3 pola query teratas yang memang sering dan mahal.

• Pisahkan kebutuhan daftar ringkas dari kebutuhan detail penuh.

• Simpan payload audit besar, tetapi jangan ikutkan dalam indeks untuk query daftar.

• Pertimbangkan arsip atau storage terpisah untuk data lama yang jarang diakses.

Contoh Perbaikan Query Secara Praktis

Kasus 1: histori per entitas lambat

Query awal:

SELECT *
FROM audit_log
WHERE entity_type = 'invoice'
  AND entity_id = 9001
ORDER BY created_at DESC
LIMIT 50;

Masalah:

• SELECT * mengambil payload besar yang tidak dibutuhkan di daftar.

• Belum tentu ada indeks komposit yang sesuai.

Perbaikan:

CREATE INDEX idx_audit_invoice_lookup
ON audit_log(entity_type, entity_id, created_at DESC, id DESC);

SELECT id, created_at, actor_id, action, status
FROM audit_log
WHERE entity_type = 'invoice'
  AND entity_id = 9001
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kenapa lebih baik:

• Indeks mengikuti filter dan urutan.

• Kolom yang diambil dipersempit.

• Urutan deterministik dengan id sebagai tie-breaker.

Kasus 2: halaman audit global makin lambat di page tinggi

Sebelum:

SELECT id, created_at, actor_id, action
FROM audit_log
ORDER BY created_at DESC
LIMIT 100 OFFSET 200000;

Sesudah dengan keyset pagination:

CREATE INDEX idx_audit_feed_desc
ON audit_log(created_at DESC, id DESC);

SELECT id, created_at, actor_id, action
FROM audit_log
WHERE (created_at, id) < ('2026-06-01 10:15:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 100;

Kenapa lebih baik:

• Tidak perlu membuang ratusan ribu baris.

• Lebih konsisten untuk data yang terus bertambah.

Kasus 3: pencarian rentang waktu masih berat

Query:

SELECT id, created_at, actor_id, action
FROM audit_log
WHERE created_at >= '2026-05-01'
  AND created_at < '2026-06-01'
ORDER BY created_at DESC, id DESC
LIMIT 100;

Jika query seperti ini sangat dominan dan tabel benar-benar besar, opsi yang sering masuk akal adalah:

• Indeks pada (created_at DESC, id DESC).

• Partisi berdasarkan waktu jika query hampir selalu time-bounded dan retensi jelas.

• Pisahkan query daftar dari query detail agar payload besar tidak selalu dibaca.

Checklist Investigasi Bottleneck SQL pada Tabel Audit

1. Identifikasi 3-5 query audit paling mahal berdasarkan latency dan frekuensi.

2. Bandingkan rows examined dengan rows returned.

3. Jalankan EXPLAIN atau EXPLAIN ANALYZE untuk query teratas.

4. Cek apakah ada sort mahal yang seharusnya bisa dihindari dengan indeks.

5. Cek apakah query memakai OFFSET besar.

6. Periksa apakah query memakai SELECT * padahal daftar hanya butuh kolom ringkas.

7. Audit indeks yang sudah ada: mana yang overlap, mana yang benar-benar dipakai.

8. Ukur dampak indeks baru ke throughput insert dan ukuran storage.

9. Pertimbangkan keyset pagination untuk daftar audit yang terus di-scroll.

10. Pertimbangkan partisi hanya jika pola query dan retensi memang mendukung.

Debugging Tips yang Sering Menyelamatkan Waktu

• Jangan hanya uji di data kecil. Query audit sering terlihat cepat di staging karena volume belum realistis.

• Gunakan contoh parameter nyata. Rencana eksekusi bisa berbeda untuk filter selektif vs non-selektif.

• Perhatikan statistik database. Optimizer bisa memilih rencana buruk jika statistik tidak akurat.

• Pisahkan endpoint daftar dan detail. Daftar sebaiknya ringan; detail boleh mengambil payload penuh saat pengguna membuka satu item.

• Jangan optimalkan query investigasi yang sangat jarang tanpa alasan kuat. Mungkin lebih tepat dijalankan di replika, warehouse, atau jalur analitik terpisah.

Penutup

Mendeteksi bottleneck SQL pada tabel audit yang terus membesar membutuhkan pendekatan yang disiplin: ukur baseline, baca rencana eksekusi, cocokkan indeks dengan pola filter dan sort, hindari OFFSET besar, dan pertimbangkan partisi hanya bila benar-benar relevan. Pada banyak kasus, perbaikan terbesar justru datang dari hal yang sederhana tetapi tepat: mengganti pagination menjadi keyset, membuat indeks komposit yang sesuai, dan berhenti memakai SELECT * untuk daftar audit.

Yang paling penting, jangan menilai optimasi hanya dari sisi baca. Tabel audit adalah jalur write yang aktif. Indeks yang membantu query investigasi hari ini bisa menjadi penyebab write amplification besok. Tuning yang baik selalu menyeimbangkan kebutuhan forensic, integritas data, dan skala operasional.