Indexing Bertahap untuk Tabel Event yang Tumbuh Tanpa Henti

Ketika tabel event atau log terus menerima data baru, masalah performa biasanya muncul bukan karena satu query yang buruk, tetapi karena kombinasi beberapa hal: filter yang tidak konsisten, ORDER BY yang mahal, pagination yang makin lambat, dan index yang ditambahkan terlalu banyak atau terlalu cepat. Indexing bertahap adalah pendekatan yang lebih aman: tambahkan index per segmen kebutuhan, ukur dampaknya, lalu lanjutkan hanya jika hasilnya benar-benar membantu.

Ada analogi ringan yang cukup pas. Infrastruktur rel surya modular dibangun per segmen agar risiko konstruksi, validasi beban, dan dampak operasional tetap terkendali. Prinsip yang sama berlaku pada database produksi: index sebaiknya dipasang, divalidasi, dan dievaluasi bertahap, bukan sekaligus, karena setiap index baru mempercepat baca tetapi juga menambah biaya tulis, ruang simpan, dan beban pemeliharaan.

Kapan tabel event perlu strategi indexing bertahap?

Tabel event biasanya memiliki pola seperti ini:

Volume tulis tinggi dan terus bertambah.
Query baca didominasi pencarian berdasarkan rentang waktu, tipe event, status, tenant, atau user tertentu.
Ada feed operasional atau halaman admin yang menggunakan ORDER BY created_at DESC.
Ada kebutuhan investigasi insiden dengan filter yang berubah-ubah.

Gejala yang sering muncul:

Query yang dulu cepat sekarang melambat seiring pertumbuhan data.
CPU dan I/O database naik saat halaman daftar event dibuka.
Pagination halaman belakang semakin mahal di nomor halaman besar.
Query planner mulai memilih sequential scan atau filesort untuk query penting.
Penambahan satu index tampak membantu satu endpoint, tetapi memperlambat insert global.

Jika gejalanya seperti di atas, jangan langsung membuat banyak index. Mulailah dengan audit pola query yang benar-benar dominan.

Audit pola WHERE dan ORDER BY sebelum membuat index

Kesalahan umum adalah mendesain index berdasarkan dugaan. Padahal index harus mengikuti pola akses aktual. Ambil sampel query dari log aplikasi, slow query log, APM, atau statistik query database. Kelompokkan query berdasarkan bentuk, bukan hanya berdasarkan endpoint.

Contoh pola query yang sering muncul

-- Feed event global terbaru
SELECT id, event_type, tenant_id, created_at
FROM events
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- Feed per tenant
SELECT id, event_type, status, created_at
FROM events
WHERE tenant_id = 42
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 100;

-- Audit per tenant dan tipe event
SELECT id, user_id, status, created_at
FROM events
WHERE tenant_id = 42
  AND event_type = 'payment.failed'
  AND created_at >= '2026-07-01'
  AND created_at < '2026-08-01'
ORDER BY created_at DESC
LIMIT 100;

-- Backoffice mencari event gagal terbaru
SELECT id, tenant_id, event_type, created_at
FROM events
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

Dari contoh di atas, beberapa hal langsung terlihat:

created_at hampir selalu terlibat.
tenant_id mungkin lebih selektif daripada status.
ORDER BY created_at DESC sering muncul.
Tidak semua kombinasi filter layak dibuatkan index sendiri.

Tujuan audit adalah menjawab tiga pertanyaan:

Query mana yang paling sering dan paling mahal?
Kolom filter mana yang paling selektif?
Apakah urutan sort bisa dipenuhi dari index agar database tidak perlu menyortir ulang?

Memilih urutan kolom pada composite index

Pada tabel event besar, urutan kolom di dalam composite index jauh lebih penting daripada sekadar “kolom apa saja yang di-index”. Aturan praktis yang sering berguna:

Mulai dari kolom yang paling sering dipakai sebagai filter yang membatasi data secara signifikan.
Letakkan kolom untuk pengurutan setelah kolom filter yang stabil.
Jangan mencampur terlalu banyak kolom “untuk berjaga-jaga”. Composite index yang terlalu lebar mahal saat write dan belum tentu dipakai planner.

Contoh pemilihan index yang masuk akal

Jika query dominan adalah:

SELECT id, event_type, status, created_at
FROM events
WHERE tenant_id = 42
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 100;

Index berikut biasanya lebih masuk akal daripada index tunggal terpisah:

CREATE INDEX idx_events_tenant_created_at
ON events (tenant_id, created_at DESC);

Mengapa?

tenant_id mempersempit ruang pencarian lebih dulu.
created_at membantu rentang waktu sekaligus urutan hasil.
Untuk query feed tenant terbaru, database berpeluang membaca bagian index yang relevan tanpa sortir tambahan.

Untuk query yang juga sering memfilter event_type:

CREATE INDEX idx_events_tenant_type_created_at
ON events (tenant_id, event_type, created_at DESC);

Namun jangan terburu-buru membuat kedua index di atas jika distribusi query belum jelas. Index (tenant_id, created_at) mungkin sudah cukup membantu banyak kasus, sedangkan index yang lebih spesifik hanya perlu ditambah jika audit membuktikan manfaatnya.

Kesalahan umum dalam urutan kolom

Membuat (created_at, tenant_id) padahal hampir semua query selalu memfilter tenant_id lebih dulu.
Membuat index terpisah (tenant_id) dan (created_at) lalu berharap performanya setara dengan composite index.
Menaruh kolom dengan selektivitas rendah di depan, misalnya status jika nilainya hanya beberapa jenis dan distribusinya timpang.

Index tunggal tetap ada gunanya, tetapi untuk query event yang memiliki filter gabungan dan sort konsisten, composite index biasanya lebih relevan.

Strategi indexing bertahap yang aman di produksi

Pendekatan bertahap berarti Anda tidak langsung menambah lima index sekaligus. Tambahkan satu index yang paling berdampak, ukur hasilnya, lalu putuskan langkah berikutnya.

Langkah rollout yang disarankan

Identifikasi 1-2 query prioritas yang paling mahal atau paling sering.
Rancang satu index yang menargetkan pola query tersebut, bukan semua query sekaligus.
Uji di staging dengan data yang mendekati produksi jika memungkinkan.
Lihat EXPLAIN sebelum dan sesudah penambahan index.
Rollout saat trafik rendah atau gunakan mekanisme pembuatan index yang meminimalkan lock sesuai kemampuan database Anda.
Pantau metrik: latensi query, throughput insert, CPU, I/O, ukuran index, dan dampak ke replication lag jika ada.
Evaluasi setelah stabil. Jika query target membaik tanpa penalti write yang berlebihan, baru pertimbangkan index berikutnya.

Catatan: detail lock dan sintaks pembuatan index berbeda antar database. Pastikan membaca dokumentasi engine yang Anda pakai sebelum menjalankan DDL di produksi.

Contoh urutan rollout

Misalkan masalah utama ada pada feed per tenant. Jangan langsung menambah:

(tenant_id)
(created_at)
(tenant_id, created_at)
(tenant_id, event_type, created_at)
(status, created_at)

Itu menambah biaya write secara cepat dan membuat evaluasi sulit. Lebih aman mulai dari:

CREATE INDEX idx_events_tenant_created_at
ON events (tenant_id, created_at DESC);

Setelah itu ukur apakah query berikut membaik:

SELECT id, event_type, status, created_at
FROM events
WHERE tenant_id = ?
ORDER BY created_at DESC
LIMIT 100;

Jika query audit dengan event_type masih mahal dan volumenya memang signifikan, baru pertimbangkan index tambahan yang lebih spesifik.

Cara membaca EXPLAIN untuk tabel event besar

EXPLAIN membantu Anda melihat apakah query benar-benar memakai index yang diharapkan dan seberapa besar data yang tetap harus dipindai. Istilah detail berbeda antar database, tetapi prinsip bacanya serupa.

Apa yang perlu diperhatikan

Apakah index yang tepat dipilih? Cari petunjuk bahwa planner menggunakan index atau range scan, bukan full scan.
Berapa estimasi baris yang dibaca? Jika masih sangat besar, index mungkin tidak cukup selektif.
Apakah ada operasi sort tambahan? Jika masih ada sort mahal setelah filtering, urutan index mungkin belum cocok dengan ORDER BY.
Apakah ada filter sisa setelah index? Kadang index membantu sebagian, tetapi query tetap mahal karena kondisi lain tidak ter-cover dengan baik.

Contoh interpretasi sederhana

Sebelum ada index composite, query ini:

SELECT id, event_type, created_at
FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 100;

bisa saja menunjukkan pola seperti:

scan banyak baris untuk tenant tertentu,
lalu sort hasilnya,
baru ambil 100 teratas.

Setelah menambah index (tenant_id, created_at DESC), planner bisa membaca index dalam urutan yang sudah sesuai, lalu berhenti lebih cepat setelah menemukan 100 baris yang dibutuhkan. Inilah alasan query feed sering membaik drastis dengan index yang benar: bukan hanya filtering lebih cepat, tetapi juga menghindari sort dan membaca lebih sedikit baris.

Debugging jika index tidak dipakai

Pastikan bentuk query sesuai dengan bentuk index.
Periksa apakah fungsi pada kolom membuat index sulit dimanfaatkan, misalnya membungkus created_at dengan transformasi yang memaksa evaluasi per baris.
Lihat apakah kondisi terlalu tidak selektif, sehingga planner menilai scan lain lebih murah.
Pastikan statistik database cukup mutakhir agar planner tidak mengambil keputusan dari estimasi usang.

Dampak ke pagination feed dan halaman admin

Tabel event yang besar hampir selalu punya UI daftar data: feed, admin, audit log, atau halaman investigasi. Di sinilah desain index sangat terasa.

Masalah OFFSET pada data besar

Pagination seperti ini mudah ditulis:

SELECT id, tenant_id, event_type, created_at
FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Masalahnya, semakin besar OFFSET, semakin banyak baris yang harus dilewati. Walaupun ada index, database sering tetap perlu membaca dan membuang banyak entri sebelum mencapai halaman yang diminta.

Alternatif: keyset pagination

Untuk feed event, pendekatan yang lebih stabil biasanya menggunakan penanda baris terakhir:

SELECT id, tenant_id, event_type, created_at
FROM events
WHERE tenant_id = 42
  AND created_at < '2026-08-01 10:00:00'
ORDER BY created_at DESC
LIMIT 50;

Jika ada kemungkinan nilai created_at sama, tambahkan tie-breaker seperti id agar urutan stabil. Dengan pola ini, index seperti (tenant_id, created_at DESC, id DESC) bisa membantu feed yang men-scroll ke bawah tanpa biaya OFFSET yang terus membengkak.

Untuk halaman admin, kebutuhan sering berbeda. Admin kadang butuh kombinasi filter yang banyak dan pagination lompat ke halaman tertentu. Di sini, tidak semua query bisa dibuat sangat cepat hanya dengan index. Anda perlu memilih prioritas: apakah mengoptimalkan feed operasional yang sering dipakai, atau laporan investigasi yang kompleks tetapi jarang dijalankan.

Trade-off write amplification dan biaya operasional

Setiap index baru menambah pekerjaan saat INSERT, UPDATE, dan kadang DELETE. Pada tabel event dengan volume tulis tinggi, ini bukan detail kecil. Semakin banyak index, semakin besar:

latensi write,
penggunaan disk,
I/O acak,
waktu pemeliharaan seperti vacuum, compaction, atau rebuild tergantung engine,
potensi dampak ke replikasi.

Karena itu, strategi indexing bertahap menekankan disiplin: setiap index harus punya alasan bisnis dan bukti teknis. Jangan biarkan index lama menumpuk hanya karena pernah berguna beberapa bulan lalu.

Kapan index baru justru tidak membantu?

Query mengambil porsi data yang sangat besar, sehingga scan tetap lebih murah.
Kolom filter memiliki selektivitas rendah, misalnya hampir semua baris berstatus sama.
Query memakai fungsi atau ekspresi yang membuat pencarian tidak cocok dengan index yang ada.
Urutan kolom dalam composite index tidak sejalan dengan pola WHERE dan ORDER BY.
Masalah sebenarnya ada pada desain query, pagination OFFSET besar, atau kebutuhan data yang terlalu lebar.

Index bukan pengganti desain query yang baik. Jika halaman admin meminta terlalu banyak kolom besar atau melakukan join yang mahal, index pada tabel event saja mungkin tidak cukup.

Contoh metrik sebelum dan sesudah yang perlu dicatat

Daripada mengejar angka benchmark generik, catat metrik di sistem Anda sendiri sebelum dan sesudah rollout. Minimal ukur:

latensi p50, p95, dan p99 query target,
jumlah baris yang dipindai versus yang dikembalikan,
throughput insert ke tabel event,
CPU dan I/O database,
ukuran total index tabel terkait,
dampak ke job admin, feed, dan replication lag jika ada replika baca.

Format evaluasi sederhana:

Sebelum index: query feed tenant lambat saat jam sibuk dan planner masih melakukan sort.
Sesudah index (tenant_id, created_at DESC): planner membaca index yang sesuai, sort berkurang atau hilang, jumlah baris yang disentuh menurun.
Dampak samping: ukuran storage naik dan latensi insert sedikit berubah. Jika masih dalam batas aman, index dianggap layak dipertahankan.

Dengan format seperti ini, keputusan penambahan index berikutnya menjadi berbasis data, bukan asumsi.

Checklist praktis untuk indexing bertahap

Ambil query paling mahal dan paling sering dari workload nyata.
Kelompokkan berdasarkan pola WHERE dan ORDER BY.
Pilih satu composite index yang menargetkan pola dominan.
Gunakan EXPLAIN untuk memastikan planner benar-benar terbantu.
Ukur dampak ke query baca dan throughput write.
Perbaiki pagination feed dengan keyset jika OFFSET mulai mahal.
Hapus atau evaluasi ulang index yang redundan setelah beberapa waktu.

Penutup

Indexing bertahap untuk tabel event yang tumbuh tanpa henti bukan sekadar teknik optimasi, melainkan cara kerja yang lebih aman untuk sistem produksi. Seperti infrastruktur modular yang dipasang per segmen lalu diuji sebelum diperluas, index pada tabel event sebaiknya ditambahkan satu per satu, dibuktikan manfaatnya dengan EXPLAIN dan metrik nyata, lalu dievaluasi dampaknya ke write path.

Jika Anda mulai dari audit query, memilih urutan kolom composite index dengan benar, dan disiplin mengukur sebelum-sesudah, Anda bisa mempercepat feed, admin, dan audit log tanpa menjadikan database korban dari terlalu banyak index yang tidak benar-benar berguna.

Quick Search

shopping_cart Keranjang