ClickHouse jadi lebih malas dan lebih cepat — menghadirkan optimasi lazy materialization

• ClickHouse memperkenalkan teknik optimasi baru, lazy materialization, untuk meningkatkan performa kueri Top N hingga 1.500x
• Strategi membaca data kolom hanya saat dibutuhkan meminimalkan disk I/O
• Bersama teknik yang sudah ada seperti penyimpanan kolumnar, indeks, dan PREWHERE, ini membentuk stack optimasi I/O berlapis
• Dengan menunda pemuatan data kolom sesuai rencana eksekusi kueri, dampaknya sangat besar terutama pada kueri dengan klausa LIMIT
• Karena aktif secara default, peningkatan performa bisa diperoleh tanpa perubahan kode

---

Strategi optimasi tertunda ClickHouse: Lazy Materialization

Konsep inti

• ClickHouse memaksimalkan performa dengan tidak membaca data yang tidak perlu
• lazy materialization adalah pendekatan yang memuat data kolom hanya pada saat benar-benar dibutuhkan selama eksekusi kueri
• Teknik ini bekerja secara independen dari teknik optimasi I/O yang sudah ada, sekaligus memberi peningkatan performa yang saling melengkapi

Teknologi optimasi I/O yang sudah ada

• Penyimpanan berbasis kolom: hanya membaca kolom yang diperlukan
• Sparse Index / Skipping Index / Projections: hanya membaca granule yang sesuai dengan kondisi filter
• PREWHERE: memfilter lebih awal pada kolom non-indeks
• Query Condition Cache: menyimpan hasil kueri berulang dalam cache untuk menghindari pemrosesan ulang granule yang sama

---

Cara kerja Lazy Materialization

• Jika teknik sebelumnya berfokus pada pengurangan I/O melalui filtering, lazy materialization menunda pembacaan hingga saat komputasi benar-benar memerlukannya
• Hanya kolom yang dibutuhkan oleh tahap kueri berikutnya yang langsung dibaca, sementara sisanya dibaca setelah LIMIT jika memang diperlukan
• Terutama pada kueri Top N, karena hanya sebagian kolom yang dipakai, kolom teks berukuran besar dan sejenisnya hampir tidak perlu dibaca

Optimasi ini dimungkinkan karena penyimpanan kolom yang independen, dan merupakan pendekatan yang tidak mungkin dilakukan pada DB berbasis baris

---

Contoh nyata: dataset ulasan Amazon

• 150M rows, 70GB tidak terkompresi, 30GB terkompresi

• Contoh kueri Top N:

  SELECT helpful_votes  
  FROM amazon.amazon_reviews  
  ORDER BY helpful_votes DESC  
  LIMIT 3;  
  

  • Waktu eksekusi: 0,07 detik
  • Pemrosesan cepat karena hanya membaca satu kolom

• Contoh pembacaan kolom teks berukuran besar:

  SELECT review_body  
  FROM amazon.amazon_reviews  
  FORMAT Null;  
  

  • Waktu eksekusi: 176 detik
  • Meski hanya satu kolom, ukurannya 56GB sehingga bottleneck terjadi pada disk I/O

---

Perbandingan performa menurut lapisan optimasi yang diterapkan

1. Tanpa optimasi (Baseline)

• Waktu eksekusi: 219 detik
• Throughput: 72GB, 150M rows
• Membaca dan mengurutkan semua kolom secara penuh

2. Primary Key Index diterapkan

• Waktu eksekusi: 96 detik
• Throughput: 28GB, 53M rows
• Filtering granule berbasis PK memangkas waktu lebih dari 50%

3. PREWHERE ditambahkan

• Waktu eksekusi: 61 detik
• Throughput: 16GB
• Kondisi filter non-indeks juga diterapkan untuk menurunkan I/O lebih lanjut

4. Lazy Materialization diaktifkan

• Waktu eksekusi: 0,18 detik
• Throughput: 807MB
• Pada akhirnya hanya 3 row yang benar-benar diperlukan yang dimuat dari kolom besar

Total peningkatan performa lebih dari 1.200x, dan penggunaan memori berkurang lebih dari 150x

---

Tetap efektif untuk kueri Top N tanpa filter

• Pada kueri pengurutan penuh tanpa filter:

  SELECT helpful_votes, product_title, review_headline, review_body  
  FROM amazon.amazon_reviews  
  ORDER BY helpful_votes DESC  
  LIMIT 3;  
  

• Sebelum lazy materialization: 219 detik

• Setelah lazy materialization: 0,139 detik

• Percepatan 1.576x, I/O berkurang 40x, penggunaan memori berkurang 300x

---

Memeriksa execution plan

EXPLAIN actions = 1  
SELECT helpful_votes, product_title, review_headline, review_body  
FROM amazon.amazon_reviews  
ORDER BY helpful_votes DESC  
LIMIT 3  
SETTINGS query_plan_optimize_lazy_materialization = true;  

• Hasil:

Lazily read columns: review_headline, review_body, product_title   
  Limit                    
    Sorting                             
      ReadFromMergeTree  

• Kolom berukuran besar dimuat hanya setelah sorting dan LIMIT

---

Kesimpulan

• Stack optimasi I/O ClickHouse kini lengkap: Index → PREWHERE → Lazy Materialization
• Tanpa perubahan kode, performa bisa meningkat ratusan hingga ribuan kali hanya lewat cara eksekusi kueri
• Sangat ideal terutama untuk pola Top N, kolom berukuran besar, dan kueri LIMIT
• Karena aktif secara default, pengguna tidak perlu melakukan konfigurasi terpisah agar ini diterapkan otomatis

SQL yang sama, mesin yang sama, hasil yang berbeda
cepat = membaca lebih sedikit = ClickHouse

[zihado]

Penasaran apakah ada yang pernah membandingkan ClickHouse dan StarRocks, beberapa bulan lalu performa join StarRocks tampak lebih baik.
https://d2.naver.com/helloworld/1168674

[GN⁺]

Komentar Hacker News

• Optimisasi ini akan memberikan peningkatan kecepatan yang dramatis terutama saat mengambil sampel acak dari kumpulan data besar, khususnya bila kolom yang diinginkan dapat berisi nilai besar

  • Resep SQL dasarnya menggunakan klausa LIMIT untuk menentukan baris yang akan dimasukkan ke dalam sampel
  • Optimisasi baru ini menjanjikan penundaan pembacaan kolom besar sampai klausa LIMIT memfilter kumpulan data menjadi sejumlah kecil baris
  • Penasaran apakah ada yang bisa memverifikasi apakah optimisasi ini memang mempercepat kueri semacam ini di ClickHouse

• Sangat suka dengan ClickHouse

  • Baru menemukannya belakangan ini, dan rasanya seperti angin segar dibanding solusi yang tidak efisien untuk analitik
  • Sangat cepat dan CLI-nya juga menyenangkan untuk digunakan

• Tidak paham dengan situs web yang tidak bisa di-scroll

  • Sedikit scroll lalu melompat kembali ke atas sehingga tidak bisa digunakan

• Late materialization, 19 tahun kemudian

  • Menyertakan tautan terkait

• Tidak terkait dengan opsi materialization baru, tapi bagian ini menonjol

  • Kueri yang mengurutkan 150 juta nilai dan mengembalikan 3 teratas membutuhkan 70 milidetik
  • Perlu memperbarui model mental tentang kueri lambat di perangkat keras dan perangkat lunak modern
  • Mengurutkan 150 juta bilangan bulat dalam 70 milidetik bukan hal yang mengejutkan
  • Penggunaan memori puncaknya 3.59 MiB
  • Artikelnya sangat bagus, dijelaskan dengan jelas dan disertai diagram yang bagus

• ClickHouse mungkin akan lebih populer daripada DuckDB jika punya rilis native Windows yang tidak memerlukan WSL atau mesin virtual Linux

  • Salah satu alasan MySQL lebih populer daripada PostgreSQL adalah karena MySQL memiliki installer Windows

• Sedang merencanakan liburan pantai meski ada drama bandara

  • Informasi teknis dan diagramnya kelas atas, tapi kehadiran cerita membuatnya jadi lebih baik lagi

• ClickHouse adalah mahakarya rekayasa modern

  • Perhatian terhadap performa benar-benar total

• Penasaran apakah ada yang pernah membandingkan ClickHouse dan StarRocks

  • Beberapa bulan lalu performa join StarRocks tampak lebih baik

• Menarik bahwa database seperti ini menunjukkan semua hal yang keliru dari database berbasis baris

  • Dengan struktur indeks btree, mustahil mendekati kecepatan seperti ini
  • Mengejutkan melihat seberapa cepat mesin modern
  • Rasanya kumpulan datanya bahkan belum dikompresi dengan benar
  • Membaca data lebih lambat daripada mendekompresinya
  • Mengingatkan pada artikel Cloudflare, tentang gagasan bahwa enkripsi itu gratis
  • Menarik bahwa yang digunakan adalah mesin komputasi (chdb)