Audit SQL untuk Tabel Insiden: Index, Feed, dan Query Forensik

Audit SQL untuk tabel insiden menjadi penting ketika tim keamanan atau forensik menyimpan lonjakan event, artefak teks, dan hasil pencarian ke database yang sama. Pola yang paling sering bermasalah bukan query yang rumit, melainkan query harian yang terlihat sederhana: filter berdasarkan tenant, status, dan waktu; mengambil feed terbaru; lalu melakukan pagination untuk investigasi.

Jika query yang tadinya cepat mendadak lambat, akar masalahnya biasanya kombinasi dari index yang tidak sesuai pola akses, sort yang mahal, pagination berbasis offset, dan tabel yang tumbuh tanpa strategi retensi. Artikel ini fokus pada audit praktis: gejala bottleneck SQL, cara membaca EXPLAIN, memilih composite index, memahami dampak low-selectivity index, serta strategi arsip atau partisi ringan yang realistis.

Masalah nyata pada tabel insiden yang tumbuh cepat

Dalam sistem keamanan, satu insiden sering dikaitkan dengan banyak data turunan: event mentah, ringkasan, penanda status, artefak teks, skor, dan hasil pencarian. Saat ada lonjakan data, tabel insiden atau tabel feed turunannya bisa membesar jauh lebih cepat daripada asumsi awal aplikasi.

Pola query yang umum muncul adalah:

Daftar insiden per tenant dalam rentang waktu tertentu.
Feed terbaru untuk dashboard atau triase.
Filter berdasarkan status, severity, assignee, atau source.
Pencarian investigasi yang harus stabil saat dipaginasi.
Join ke tabel pendukung untuk menampilkan metadata.

Masalah performa biasanya muncul ketika:

Filter tidak cocok dengan urutan kolom index.
Query melakukan ORDER BY created_at DESC tanpa index yang mendukung.
Offset besar dipakai untuk halaman dalam.
Kolom boolean atau status di-index sendiri padahal selektivitasnya rendah.
Tabel menyimpan data lama tanpa arsip, sehingga ukuran index ikut membengkak.

Contoh skema dasar untuk audit SQL

Berikut contoh skema generik yang cukup mewakili banyak sistem insiden. SQL ini tidak bergantung pada satu engine tertentu, tetapi sengaja dibuat sederhana agar pola auditnya jelas.

CREATE TABLE incidents (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  status VARCHAR(32) NOT NULL,
  severity VARCHAR(16) NOT NULL,
  source VARCHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL,
  artifact_text TEXT,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  archived_at TIMESTAMP NULL
);

Query yang biasanya menjadi pusat masalah:

SELECT id, tenant_id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = ?
  AND status IN ('open', 'triaged')
  AND created_at >= ?
  AND created_at < ?
ORDER BY created_at DESC, id DESC
LIMIT 50;

Untuk feed terbaru lintas sumber dalam satu tenant, query sering mirip seperti ini:

SELECT id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = ?
  AND archived_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Dua query di atas tampak biasa, tetapi jika tabel tumbuh besar dan index tidak tepat, database bisa melakukan scan dan sort yang jauh lebih mahal daripada yang diperkirakan.

Gejala bottleneck SQL yang perlu dicurigai

1. Query feed terbaru melambat walau LIMIT kecil

LIMIT 50 tidak otomatis murah. Jika database harus memindai jutaan baris lalu mengurutkan hasil sebelum mengambil 50 teratas, bottleneck tetap besar. Gejala ini umum pada query ORDER BY created_at DESC yang tidak ditopang index yang cocok dengan filter.

2. Halaman investigasi makin lambat di page besar

Jika page 1 cepat tetapi page 200 sangat lambat, hampir pasti ada masalah pada offset pagination. Database harus melewati banyak baris sebelum mengambil hasil yang diminta.

3. CPU database tinggi saat ada lonjakan event

Lonjakan ingest sering berbarengan dengan lonjakan baca. Jika index terlalu banyak, write menjadi mahal. Jika index tidak tepat, read menjadi mahal. Tabel insiden sering terkena keduanya sekaligus.

4. Query menggunakan index, tetapi tetap lambat

Ini jebakan umum. Fakta bahwa query “menggunakan index” belum berarti index tersebut efisien. Bisa jadi database tetap membaca terlalu banyak baris karena kolom awal pada index selektivitasnya rendah atau urutan kolomnya tidak cocok.

Cara membaca EXPLAIN secara praktis

Setiap engine SQL memiliki format EXPLAIN yang berbeda, tetapi tujuan auditnya sama: memahami bagaimana query dijalankan, apakah memakai index yang tepat, berapa banyak baris yang diperkirakan dibaca, dan apakah ada operasi sort atau scan besar.

Hal yang perlu Anda cari saat membaca EXPLAIN:

Access path: apakah query melakukan full table scan, index scan, atau range scan.
Estimated rows: apakah jumlah baris yang dibaca masuk akal untuk filter Anda.
Sort step: apakah ada sort eksplisit yang mahal karena index tidak mendukung ORDER BY.
Filter pushdown: apakah kondisi WHERE disaring sedini mungkin atau justru setelah pembacaan besar.
Join order: jika ada join, apakah tabel paling selektif dibaca lebih dulu.

Contoh audit sederhana:

EXPLAIN
SELECT id, tenant_id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = 42
  AND status IN ('open', 'triaged')
  AND created_at >= '2026-07-01 00:00:00'
  AND created_at < '2026-07-02 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Interpretasi praktis:

Jika planner menunjukkan scan besar pada seluruh tenant atau seluruh tabel, index belum tepat.
Jika ada langkah sort terpisah setelah filter, coba cek apakah index bisa mengikuti urutan filter dan ORDER BY.
Jika estimasi baris jauh lebih besar daripada hasil nyata, statistik optimizer mungkin usang atau distribusi datanya timpang.

Catatan: Jangan hanya melihat apakah index dipakai. Lihat juga berapa banyak baris yang harus dibaca sebelum mendapatkan 50 hasil. Query bisa tetap lambat walau planner memilih index.

Memilih composite index yang sesuai pola query

Prinsip dasar

Untuk tabel insiden, index tunggal pada tenant_id, status, atau created_at sering tidak cukup. Query nyata hampir selalu menggabungkan beberapa kondisi sekaligus. Karena itu, composite index biasanya lebih berguna daripada banyak index tunggal.

Untuk query ini:

SELECT id, tenant_id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = ?
  AND status IN ('open', 'triaged')
  AND created_at >= ?
  AND created_at < ?
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pola index yang layak dipertimbangkan:

CREATE INDEX idx_incidents_tenant_status_created_id
ON incidents (tenant_id, status, created_at, id);

Mengapa urutan ini masuk akal?

tenant_id biasanya paling penting karena memotong ruang pencarian secara logis per pelanggan atau organisasi.
status dipakai sebagai filter tambahan.
created_at mendukung range waktu sekaligus urutan feed terbaru.
id membantu tie-break untuk urutan stabil saat beberapa baris memiliki timestamp sama.

Namun, urutan index tidak bersifat universal. Jika hampir semua query utama selalu memfilter tenant_id dan rentang waktu, tetapi status opsional atau sering berubah-ubah, index berikut bisa lebih cocok:

CREATE INDEX idx_incidents_tenant_created_id
ON incidents (tenant_id, created_at, id);

Lalu untuk query dengan status, Anda bisa mengevaluasi apakah tambahan filter status masih cukup murah setelah range tenant+waktu dipersempit.

Kapan status di awal index justru buruk

Kolom seperti status atau archived_at IS NULL sering punya selektivitas rendah. Jika sebagian besar data berstatus open atau NULL, menaruh kolom itu di awal index sering tidak membantu banyak. Database tetap harus membaca banyak baris sebelum menemukan hasil yang cocok.

Contoh anti-pattern:

CREATE INDEX idx_incidents_status ON incidents (status);
CREATE INDEX idx_incidents_archived_at ON incidents (archived_at);

Index seperti ini kadang berguna untuk query tertentu, tetapi pada tabel insiden multi-tenant dengan feed terbaru, nilainya sering kecil dibanding biaya write dan storage yang ditambahkan.

Index untuk feed terbaru

Untuk query feed per tenant:

SELECT id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = ?
  AND archived_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index yang lebih relevan biasanya mengikuti pola filter utama lalu urutan hasil:

CREATE INDEX idx_incidents_tenant_archived_created_id
ON incidents (tenant_id, archived_at, created_at, id);

Tetapi jika archived_at IS NULL berlaku untuk hampir semua baris aktif, Anda perlu menguji apakah kolom itu benar-benar membantu. Dalam beberapa sistem, index yang lebih ramping seperti ini justru lebih efektif:

CREATE INDEX idx_incidents_tenant_created_id
ON incidents (tenant_id, created_at, id);

Kuncinya: ukur berdasarkan query dominan dan distribusi data nyata, bukan asumsi.

Dampak low-selectivity index yang sering disalahpahami

Low selectivity berarti suatu kolom hanya memiliki sedikit variasi nilai relatif terhadap jumlah baris total. Contoh umum: status, severity tingkat kasar, flag boolean, atau archived_at yang mayoritas NULL.

Masalahnya:

Index pada kolom low-selectivity sering tidak mengurangi jumlah baris yang dibaca secara signifikan.
Planner bisa saja memilih index tersebut, lalu tetap melakukan banyak lookup atau filter lanjutan.
Setiap index tambahan memperberat operasi insert, update, dan maintenance.

Itulah sebabnya audit index bukan soal “semakin banyak semakin baik”. Pada workload insiden yang ingest-nya tinggi, terlalu banyak index bisa memperlambat sistem secara keseluruhan.

Offset vs keyset pagination untuk query forensik

Masalah pada offset pagination

Offset mudah dipakai:

SELECT id, tenant_id, status, created_at
FROM incidents
WHERE tenant_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 5000;

Tetapi pada page besar, database tetap harus melewati ribuan baris sebelum mengembalikan 50 hasil. Selain itu, hasil bisa tidak stabil jika data baru masuk di antara dua permintaan halaman.

Dalam konteks investigasi forensik, ketidakstabilan ini berbahaya karena analis bisa melihat duplikasi atau kehilangan item antar halaman.

Mengapa keyset pagination lebih stabil

Keyset pagination memakai nilai baris terakhir dari halaman sebelumnya sebagai penanda lanjut. Ini lebih cocok untuk feed terbaru dan investigasi berurutan.

SELECT id, tenant_id, status, created_at
FROM incidents
WHERE tenant_id = ?
  AND (
    created_at < ? OR
    (created_at = ? AND id < ?)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Query ini bekerja baik jika didukung index yang konsisten dengan urutan sort, misalnya:

CREATE INDEX idx_incidents_tenant_created_id
ON incidents (tenant_id, created_at, id);

Keuntungan keyset:

Lebih stabil saat data baru terus masuk.
Tidak perlu melewati offset besar.
Lebih sesuai untuk feed kronologis.

Kekurangannya:

Lebih sulit untuk langsung lompat ke page ke-100.
UI dan API perlu membawa cursor, biasanya kombinasi created_at dan id.

Untuk dashboard umum, offset kadang masih cukup. Untuk query forensik yang harus konsisten dan bisa menelusuri kronologi, keyset biasanya pilihan yang lebih aman.

Anti-pattern umum pada audit SQL tabel insiden

1. Mengandalkan index tunggal untuk query multi-kondisi

Misalnya hanya ada index pada tenant_id dan created_at secara terpisah, lalu berharap database selalu menggabungkannya secara efisien. Dalam banyak kasus, hasilnya masih kalah dibanding satu composite index yang sesuai pola query utama.

2. ORDER BY tidak konsisten dengan index

Jika query selalu mengurutkan created_at DESC, id DESC, tetapi index berakhir di status tanpa mendukung urutan ini, database mungkin tetap perlu sort tambahan.

3. Mengambil kolom terlalu banyak di feed

Feed terbaru sering hanya butuh metadata ringkas. Jika Anda ikut mengambil artifact_text atau payload besar di query awal, I/O membengkak. Pisahkan query daftar dan query detail bila perlu.

4. Fungsi pada kolom yang mematikan penggunaan index

Contoh:

WHERE DATE(created_at) = ?

Lebih baik tulis rentang waktu eksplisit:

WHERE created_at >= ? AND created_at < ?

Ini menjaga query tetap ramah index.

5. OR yang terlalu longgar tanpa evaluasi planner

Kondisi OR bisa membuat optimizer memilih rencana yang mahal. Jika satu query sangat penting dan sering dijalankan, kadang lebih baik memecahnya menjadi beberapa query yang lebih terarah, lalu menggabungkan hasil di aplikasi atau lewat UNION ALL yang terukur.

6. Menambahkan index tanpa menghapus yang tidak berguna

Setelah beberapa iterasi, tabel sering dipenuhi index sisa eksperimen. Audit berkala perlu mengecek index yang jarang dipakai karena biayanya tetap dibayar pada write path.

Strategi arsip dan partisi ringan

Jika tabel insiden menyimpan data panas dan data historis sangat lama dalam satu tempat, performa feed dan investigasi harian akan terus menurun. Solusi tidak selalu harus partisi kompleks. Sering kali pendekatan ringan sudah cukup.

1. Arsipkan data lama ke tabel terpisah

Pisahkan data aktif dan historis:

incidents untuk data aktif.
incidents_archive untuk data lama atau yang sudah closed dalam periode tertentu.

Keuntungannya sederhana: tabel kerja harian lebih kecil, index lebih padat, dan query utama lebih fokus. Kekurangannya, query lintas seluruh sejarah harus mengakses dua sumber data.

2. Retensi berbasis waktu

Jika investigasi operasional umumnya hanya menyentuh 30-90 hari terakhir, pertimbangkan memindahkan data lebih lama secara berkala. Ini sering lebih mudah diterapkan daripada partisi penuh di awal.

3. Partisi ringan bila memang didukung dan dibutuhkan

Partisi berbasis waktu bisa membantu pada tabel yang sangat besar, terutama untuk pemangkasan data dan pembacaan range tertentu. Tetapi partisi bukan obat universal. Jika query Anda tidak memfilter waktu dengan jelas, manfaatnya bisa terbatas. Selain itu, partisi menambah kompleksitas operasional: pembuatan partisi baru, monitoring, dan strategi query lintas partisi.

Prinsip praktis: lakukan arsip sederhana lebih dulu jika bottleneck utama ada pada ukuran tabel aktif. Partisi layak dipertimbangkan ketika retensi panjang tidak bisa dihindari dan query dominan memang berbasis waktu.

Pola implementasi query yang lebih aman

Query feed aktif

SELECT id, tenant_id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = ?
  AND created_at >= ?
  AND archived_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 50;

Gunakan jika feed memang selalu scoped per tenant dan waktu. Jangan tarik kolom teks besar jika belum diperlukan.

Query detail setelah pengguna membuka satu insiden

SELECT id, tenant_id, status, severity, source, title, artifact_text, created_at, updated_at
FROM incidents
WHERE id = ? AND tenant_id = ?;

Pisahkan query daftar dan detail agar daftar tetap ringan.

Pagination berbasis cursor

SELECT id, tenant_id, status, severity, source, title, created_at
FROM incidents
WHERE tenant_id = ?
  AND archived_at IS NULL
  AND (
    created_at < ? OR
    (created_at = ? AND id < ?)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pastikan API mengembalikan cursor eksplisit, misalnya pasangan last_created_at dan last_id.

Checklist audit performa SQL yang bisa langsung dipakai

Daftar query terpenting: identifikasi 3-5 query insiden yang paling sering dipanggil atau paling kritikal.
Cek pola filter: tenant, status, waktu, archived flag, source, severity.
Cek pola sort: apakah selalu created_at DESC, id DESC atau ada variasi lain.
Bandingkan index dengan query nyata: apakah urutan kolom pada composite index sesuai urutan filter dominan dan sort.
Jalankan EXPLAIN: cari scan besar, sort tambahan, dan estimasi baris yang terlalu tinggi.
Evaluasi low-selectivity index: apakah index pada status/flag benar-benar membantu atau hanya menambah overhead write.
Audit SELECT list: jangan ambil kolom besar di feed jika tidak dipakai.
Ganti fungsi pada kolom waktu: gunakan rentang waktu, bukan transformasi seperti DATE().
Uji offset besar: jika page dalam melambat, migrasikan query investigasi ke keyset pagination.
Periksa kestabilan urutan: selalu gunakan tie-break seperti id bersama created_at.
Audit index berlebih: hapus index yang redundan atau jarang dipakai.
Pertimbangkan arsip: pisahkan data aktif dan historis jika tabel aktif sudah terlalu besar.
Perbarui statistik optimizer: jika estimasi planner aneh, cek apakah statistik tabel masih relevan.
Uji pada distribusi data nyata: jangan menilai dari dataset kecil atau seragam.

Penutup

Audit SQL untuk tabel insiden bukan sekadar menambahkan index baru setiap kali query lambat. Yang lebih penting adalah mencocokkan desain index dengan pola investigasi yang benar-benar terjadi: filter per tenant, status, dan waktu; feed terbaru dengan urutan stabil; serta pagination yang tahan terhadap data yang terus masuk.

Mulailah dari query yang paling sering dipakai, baca EXPLAIN dengan fokus pada jumlah baris yang dibaca dan langkah sort, lalu bangun composite index yang mengikuti pola akses. Setelah itu, kurangi offset pagination untuk alur investigasi, arsipkan data lama secara disiplin, dan audit index low-selectivity yang tidak memberi nilai nyata. Dengan pendekatan ini, query forensik tetap responsif walau volume event meningkat tajam.

Quick Search

shopping_cart Keranjang