Cara memanfaatkan JSON SQLite dengan kecepatan indeks penuh: kueri cepat menggunakan kolom terbuat

(dbpro.app)

11 poin oleh GN⁺ 2025-12-13 | 2 komentar | Bagikan ke WhatsApp

Memperkenalkan pendekatan untuk memanfaatkan fitur JSON SQLite dengan menyimpan dokumen JSON asli apa adanya, lalu mengekstrak field yang diperlukan ke kolom terbuat virtual (virtual generated columns) untuk diindeks
Dengan fungsi json_extract, data di dalam JSON dapat diperlakukan seperti kolom, sehingga kueri dapat dijalankan dengan kecepatan indeks B-tree
Setiap kali pola kueri baru dibutuhkan, kolom dan indeks dapat ditambahkan tanpa migrasi data
Pendekatan ini sekaligus menghadirkan fleksibilitas data tanpa skema dan kinerja basis data relasional
Ditekankan sebagai pola praktis yang memberikan struktur ringkas dan kinerja tinggi bagi pengembang yang menggunakan SQLite

Menggabungkan SQLite dan fitur JSON

SQLite mendukung fungsi dan operator JSON, sehingga data JSON bisa disimpan dan dimanipulasi secara langsung
- Dokumen JSON disimpan apa adanya dalam satu kolom, lalu hanya informasi yang diperlukan yang diekstrak sebagai kolom virtual
- Pendekatan ini memungkinkan penanganan data yang fleksibel tanpa definisi skema
Tim DB Pro telah menggunakan SQLite secara intensif selama beberapa bulan terakhir dan memverifikasi fitur ini dalam praktik kerja nyata
- Jika dikonfigurasi dengan tepat, SQLite dapat digunakan secara stabil bahkan di lingkungan produksi

Kolom terbuat virtual (Generated Columns)

Menggunakan json_extract untuk mendefinisikan nilai tertentu di dalam JSON sebagai kolom terbuat virtual
- Kolom ini tidak menyimpan data aktual, melainkan dihitung saat kueri dijalankan dan bisa langsung digunakan
- Tidak diperlukan proses backfill terpisah atau duplikasi data
Misalnya, dimungkinkan membuat struktur yang mengekstrak field tertentu dari data JSON dan memperlakukannya seperti kolom

Menambahkan indeks dan meningkatkan kinerja

Jika indeks ditambahkan ke kolom virtual, data JSON juga dapat dicari dengan kecepatan indeks B-tree seperti kolom biasa
- Kolom virtual yang telah diindeks memberikan kinerja yang sama seperti kolom dalam basis data relasional
Pendekatan ini memungkinkan pencarian cepat bahkan ketika ukuran data JSON membesar

Menambahkan pola kueri baru

Jika nanti perlu melakukan pencarian berdasarkan field baru, cukup tambahkan kolom virtual dan indeks baru
- Contoh: mengekstrak field user_id lalu membuat indeks
- Tidak perlu memodifikasi baris data yang sudah ada atau melakukan migrasi
Dengan demikian, skalabilitas kueri instan dapat diperoleh tanpa mengubah struktur data

Keunggulan dan makna pola ini

Pola ini menggabungkan fleksibilitas penyimpanan JSON tanpa skema dan kinerja indeks DB relasional
- Tidak perlu menentukan strategi pengindeksan sejak tahap desain awal
- Optimasi dapat dilakukan dengan menambahkan kolom dan indeks pada saat dibutuhkan
Disajikan sebagai cara pengolahan data yang sederhana namun kuat bagi pengembang yang memanfaatkan SQLite
DB Pro juga mengisyaratkan akan membahas lebih banyak fitur SQLite dalam artikel-artikel berikutnya

2 komentar

GN⁺ 2025-12-13

Opini Hacker News

Dokumen JSON bisa langsung dienkode sebagai B-tree yang terserialisasi
Dengan begitu, field internal bisa diakses langsung dengan kecepatan setara indeks, dan karena dokumennya sendiri sudah terindeks, parsing tidak lagi diperlukan
Format ini disebut Lite³. Ini adalah proyek yang sedang saya kerjakan sendiri
Tautan GitHub
- Keren sekali! Saya suka Rkyv, tetapi karena membutuhkan Rust, itu terasa agak berat untuk proyek kecil
  Saya terutama suka bahwa Lite³ mendukung data biner lewat lite3_val_bytes
- Saya penasaran bagaimana Lite³ berbeda dari JSONB di PostgreSQL
  JSONB dapat mengenkode panjang array dan offset bersama-sama untuk menyeimbangkan efisiensi kompresi dan kecepatan
  Lite³ memungkinkan pembaruan in-place, tetapi membutuhkan “vacuum” berkala agar data sensitif tidak tertinggal
  JSONB sulit diperbarui tanpa re-encoding, tetapi Lite³ dapat dirapikan dengan mudah hanya dengan menelusuri strukturnya
  Dari sisi kompresibilitas, JSONB sepertinya lebih baik, tetapi menurut saya desain Lite³ adalah pendekatan yang sangat cerdas
  Saya juga sedang memelihara compiler ASN.1, jadi saya sangat tertarik pada format serialisasi seperti ini. Lite³ memberi saya ide-ide baru
- Akan sangat bagus jika ada implementasi versi Rust
Saya sangat menyukai SQLite, tetapi untuk analitik saya lebih sering memakai DuckDB
DuckDB memakai satu file seperti SQLite, tetapi mampu memproses dataset besar dengan sangat cepat
Di MacBook M2, menangani 20 juta baris pun tetap sangat cepat
Misalnya, file JSON bisa langsung dibaca dengan kueri berikut
```
SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';
```
Selain itu, kita bisa memuat kolom bertipe JSON dan memakai sintaks gaya Postgres seperti col->>'$.key'
- Saya penasaran apakah kueri pertama itu mengindeks file-file JSON di file system secara langsung saat itu juga
- Jika dipakai bersama alat visualisasi pygwalker, DuckDB bisa menganalisis jutaan baris data dalam hitungan detik
  Namun membandingkannya dengan SQLite agak kurang adil. SQLite cocok untuk membangun sistem, sedangkan DuckDB memang untuk analitik
  DuckDB juga agak merepotkan saat harus didistribusikan ke berbagai platform
- Menyimpan dataset tanpa kompresi sebaiknya benar-benar dihindari. DuckDB mendukung berbagai format kompresi
Saya kira memakai Generated Column untuk performa JSON adalah cara yang umum
Di Postgres saya juga pernah memakainya untuk mempertahankan key di dalam kolom JSON sebagai foreign key. Agak terasa ‘terkutuk’, tetapi berjalan baik
- Kalau di Postgres, bukankah kita bisa langsung membuat indeks pada field internal JSONB?
  Misalnya
```
CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
```
  Blog referensi
- Tetapi dalam banyak kasus, pada akhirnya lebih baik memecahnya menjadi tabel key/value
  Jika skema JSON berubah, parsing atau migrasi bisa gagal
- Sebenarnya itu juga tidak terlalu ‘terkutuk’. Gunakan struktur relasional yang ternormalisasi untuk bagian yang perlu, dan sisanya tangani dengan jsonb
- Saya penasaran apakah kolom VIRTUAL bisa dipakai alih-alih STORED, dan contoh ini memang menggunakan VIRTUAL
Saya mengetahui teknik ini lewat contoh optimasi yang baru-baru ini disarankan Claude Code
Ini adalah bagian dari fitur baru SQLite yang saya lewatkan karena tidak mengetahuinya, dan peningkatan performanya cukup besar
Pelajarannya adalah, bahkan untuk alat yang sudah akrab pun, ada baiknya membaca ulang dokumentasi secara berkala
- Membaca ulang manual ternyata bisa memberi banyak pencerahan
Saya jadi menulis posting blog ini setelah melihat komentar bambax di HN pada 2023
Tautan komentar asli
Indeks sebenarnya bisa dibuat tanpa memproyeksikan JSON secara langsung, tetapi computed column membuat kueri lebih sederhana
Sebelum MS-SQL 2025(v17), dukungan JSON cukup terbatas sehingga cara ini praktis wajib
- Jika hanya memakai computed column tanpa mengkueri JSON secara langsung, kita tidak akan tanpa sengaja menulis kueri non-indeks
- Saya pernah mendengar fitur ini di konferensi DBA lokal, tetapi saat itu tidak terasa sebagai perubahan besar
Saya membuka artikel itu di HN dan ternyata komentar saya dikutip, bahkan komentar itulah yang menjadi topik tulisan tersebut. Pengalaman yang aneh dan menyenangkan
Saya tersenyum saat melihat kalimat “Terima kasih, bambax!”. SQLite benar-benar alat yang keren
- Komentar yang benar-benar memberi inspirasi ada di tautan ini
Menarik, tetapi bukankah kita bisa memakai "Index On Expression" di SQLite?
Misalnya CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
Hanya saja, jika sintaks JSON path berubah sedikit saja, indeks bisa jadi tidak dipakai. Sementara Virtual Generated Column selalu menjamin indeks digunakan
- Jika indeks ekspresi dipakai bersama view, kecocokan ekspresi bisa dijamin
  Contoh: proyek recordlite
- Sintaks JSON path bisa membuat indeks rusak hanya karena perbedaan kecil
  Misalnya json_extract(data, "$.type") dan data -> '$.type' dianggap berbeda
  Artinya, jika ekspresi di klausa WHERE berubah, indeks menjadi tidak berguna
- Ini solusi yang sederhana dan cepat. Keharusan agar kueri dan indeks saling cocok memang selalu benar
- Fitur indeks ekspresi adalah fitur yang relatif baru, ditambahkan sejak SQLite 3.9.0(2015-10-14)
Saya berharap para pengembang tidak menaruh semua data ke dalam kolom JSON(B) padahal mereka punya skema yang konsisten
Pengaturan indeks dan pengelolaan constraint jadi sulit, dan overhead saat penggunaan nyata juga besar
- Kolom JSON benar-benar bersinar saat menangani data yang sulit direpresentasikan sebagai tabel, seperti struktur pohon
  Misalnya, di lingkungan Haskell+TypeScript, jauh lebih praktis menserialisasi struktur nested yang kompleks sebagai JSON
- JSON(B) berguna ketika satu koleksi perlu menampung data dengan tipe yang berbeda-beda
  Contohnya: menyimpan hasil dari beberapa payment processor dalam satu tabel, atau menangani atribut yang berbeda per item di situs iklan baris
  Di C# atau JS/TS, ini juga mudah dikelola jika dipadukan dengan alat validasi tipe seperti Zod atau OpenAPI
- Untuk JSON sederhana, normalisasi memang lebih baik, tetapi respons API yang kompleks akan berubah menjadi neraka JOIN jika dipetakan ke tabel
  Pada akhirnya ini soal menyeimbangkan maintainability dan performa. Inti tulisan ini adalah bahwa JSON pun bisa diindeks dengan mudah
- Untuk kasus seperti data sensor, di mana seluruh struktur pohon diproses sekaligus, kolom JSON jauh lebih sederhana
  Performa baca bisa cukup ditopang dengan indeks
- Normalisasi penuh sering kali tidak efisien
  Misalnya, dalam sistem penetapan harga produk, aturan diskon khusus per pelanggan jauh lebih fleksibel jika diekspresikan sebagai JSON
Jika memakai XML alih-alih JSON, ini sebenarnya model yang sama dengan document store pada era 90-an hingga 2000-an
Data diparsing saat insert/pembaruan, lalu saat kueri hanya indeks yang diakses
Fakta bahwa SQLite menyediakan kemampuan seperti ini secara bawaan benar-benar menarik

iolothebard 2025-12-14

Pada akhir abad ke-20… pernah ada yang namanya universal database… (kalau sekarang benar, tapi waktu itu salah.)