Optimasi Pencarian %keyword% Data di ClickHouse dengan Index

Salah satu tantangan terbesar dalam sistem analitik berbasis teks adalah pencarian substring seperti %keyword%.

ClickHouse sebagai database kolumnar memang sangat cepat dalam agregasi dan filter numerik, namun pencarian teks mentah (full-text search) memerlukan optimasi tambahan.

Beruntung, ClickHouse menyediakan beberapa jenis index khusus, seperti:

• minmax

• set

• bloom_filter

• ngrambf_v1

yang bisa mempercepat pencarian data string — bahkan untuk pola %keyword%, jika dirancang dengan benar.

1. Mengenal Index di ClickHouse

Berbeda dengan index di MySQL atau PostgreSQL yang disimpan terpisah, index di ClickHouse disebut “data skipping index”, artinya index ini membantu melewati blok data yang tidak relevan.

Setiap tabel MergeTree di ClickHouse terdiri dari:

• Beberapa partisi

• Setiap partisi memiliki banyak granule (blok kecil)
  Biasanya berisi sekitar 8.000 baris per blok.

Index di ClickHouse bekerja di tingkat granule — jadi semakin kecil granule, semakin presisi index-nya.

2. Jenis Index yang Dapat Digunakan untuk Pencarian Teks

3. Contoh Data Sederhana

Misalkan kita punya tabel log dengan jutaan entri:

CREATE TABLE logs
(
    id UInt64,
    event_time DateTime,
    level String,
    message String
)
ENGINE = MergeTree()
ORDER BY (event_time, id);

Contoh data:

4. Membuat Index untuk Mempercepat Pencarian

Jika kita sering mencari teks seperti “error” atau “timeout” di kolom message, maka kita bisa menambahkan index seperti berikut:

✅ Menggunakan nGram Bloom Filter (paling efektif untuk %keyword%)

ALTER TABLE logs
ADD INDEX message_ngram_idx message TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 2;

Penjelasan parameter:

• 3 → n-gram size (pecah string menjadi potongan 3 karakter, misal “err”, “rro”, “ror”)

• 256 → ukuran bitmask

• 2 → jumlah hash function

• 0 → seed hash

• GRANULARITY 2 → setiap 2 granule (sekitar 16.000 baris) dibuat 1 index

🔍 Contoh Query dengan Index

SELECT *
FROM logs
WHERE message LIKE '%error%';

Dengan index ngrambf_v1, ClickHouse hanya akan memeriksa blok data yang kemungkinan besar mengandung kata “error”, melewati blok lain yang tidak relevan.

5. Perbandingan Performa: Tanpa vs Dengan Index

⚡ Hasil uji menunjukkan peningkatan kecepatan 10–20x lipat, tergantung distribusi kata dan ukuran data.

6. Index Tambahan: bloom_filter untuk Kata Spesifik

Jika kita hanya butuh pencarian LIKE 'error%' (prefix) atau pencarian eksak kata tertentu, gunakan:

ALTER TABLE logs
ADD INDEX bf_message message TYPE bloom_filter(0.01) GRANULARITY 2;

Parameter 0.01 = probabilitas kesalahan (false positive) 1%.
Index ini tidak cocok untuk substring tengah (%error%), tapi sangat cepat untuk pencarian awalan.

Contoh:

SELECT * FROM logs WHERE message LIKE 'Error%';

Hasil bisa sampai 30x lebih cepat dibanding tanpa index.

7. Verifikasi Index Digunakan

Untuk memastikan index benar-benar dipakai oleh query:

EXPLAIN indexes = 1
SELECT * FROM logs WHERE message LIKE '%error%';

Output akan menunjukkan index mana yang digunakan:

8. Kombinasi Index dan Fungsi Optimasi

Gunakan kombinasi fungsi untuk hasil maksimal:

SELECT *
FROM logs
WHERE match(message, 'error|fail|timeout')
  AND event_time > now() - INTERVAL 1 DAY;

• match() mengoptimalkan pencarian pola.

• Filter waktu (event_time) membatasi blok yang perlu dipindai.

• Index ngrambf_v1 mempercepat seleksi substring.

9. Tips Praktis Pembuatan Index

1. Buat index hanya di kolom yang sering dicari.
   Terlalu banyak index = overhead saat insert.

2. Gunakan granularity kecil (1–2) untuk kolom teks panjang.

3. Uji dengan dataset nyata.
   Efektivitas index bergantung pada pola kata dalam data.

4. Monitor statistik index.
   Gunakan:

   SELECT * FROM system.data_skipping_indices WHERE table = 'logs';
   

10. Manfaat Nyata Penggunaan Index di ClickHouse

Kesimpulan

Meskipun ClickHouse bukan mesin pencarian teks penuh seperti Elasticsearch, ia memiliki kemampuan yang sangat kuat jika kita tahu cara menggunakannya.

Dengan kombinasi index ngrambf_v1 dan bloom_filter, serta fungsi seperti match() dan positionCaseInsensitive(), pencarian teks dengan pola %keyword% dapat berjalan:

• 10–20x lebih cepat,

• dengan konsumsi CPU lebih rendah,

• dan tetap hemat ruang penyimpanan.

Rangkuman Strategi Optimasi Pencarian Teks di ClickHouse

Fakta Nyata di Produksi

Beberapa perusahaan besar seperti Yandex, Cloudflare, dan Uber menggunakan ClickHouse untuk log dan analitik teks besar.
Mereka melaporkan hasil nyata:

• Query log 5 miliar baris bisa dijawab < 1 detik.

• Index Bloom Filter menurunkan I/O hingga 85%.

• Cluster ClickHouse mampu melayani ribuan pencarian paralel tanpa degradasi performa.