PostgreSQL atau MySQL untuk JSON: indeks mengubah hasil hingga tiga kali lipat

Untuk pencarian yang sering menyaring isi dokumen JSON, PostgreSQL dengan JSONB dan indeks yang sesuai layak menjadi pilihan awal. Dalam benchmark Leaper atas satu juta baris dengan dokumen sekitar 500 byte, pencarian terindeks mencapai 42.600 kueri per detik pada PostgreSQL dengan GIN dan 12.800 pada MySQL dengan indeks multi nilai. Selisihnya sekitar 3,3 kali lipat untuk konfigurasi yang diuji.

Hasil itu belum berarti setiap filter JSON akan berjalan tiga kali lebih cepat di PostgreSQL. Indeks GIN dan indeks multi nilai melayani bentuk pencarian yang berbeda. Bila aplikasi terutama memfilter beberapa nilai skalar pada jalur JSON yang tetap, MySQL juga dapat mengindeks hasil ekstraksinya; pilihan yang berguna bergantung pada kueri dominan dan seberapa sering dokumen ditulis ulang.

Format penyimpanan bukan penjelasan tunggal

PostgreSQL menyediakan tipe json dan jsonb dengan konsekuensi berbeda. Dokumentasi JSON PostgreSQL menjelaskan bahwa json menyimpan salinan teks masukan, sedangkan jsonb menyimpan bentuk biner terurai yang tidak perlu diurai ulang ketika diproses dan dapat diindeks. Konversi saat data masuk membuat jsonb sedikit lebih lambat untuk ditulis. Format itu juga tidak mempertahankan spasi, urutan kunci, atau kunci duplikat dari masukan.

MySQL pun menyimpan kolom JSON dalam representasi internal biner. Menurut dokumentasi JSON MySQL, elemen bersarang dapat dicari berdasarkan kunci atau posisi array tanpa mengurai kembali seluruh dokumen dari teks. Karena itu, perbedaan hasil pencarian tidak dapat dijelaskan dengan anggapan bahwa MySQL selalu membaca ulang JSON mentah. Dokumentasi yang sama membedakan indeks atas nilai skalar hasil ekstraksi dari indeks multi nilai, yang ditujukan untuk elemen array.

Indeks mengikuti bentuk filter

GIN pada PostgreSQL berguna ketika kueri menanyakan keberadaan kunci atau apakah dokumen memuat pasangan kunci dan nilai tertentu. Kelas indeks bawaan jsonb_ops melayani kedua pola itu serta operator pencarian jsonpath. Kelas jsonb_path_ops mendukung lebih sedikit operator, tetapi biasanya menghasilkan indeks lebih kecil dan pencarian containment yang lebih terarah. Perbedaannya penting: indeks yang lebih ringkas tidak membantu kueri keberadaan kunci jika operator tersebut tidak didukung.

Indeks atas seluruh dokumen memberi keluwesan ketika banyak jalur JSON perlu dicari, sekaligus menyimpan entri untuk banyak bagian dokumen. Jika filter selalu mengarah ke satu jalur, PostgreSQL juga dapat mengindeks ekspresi pada jalur tersebut. Dengan demikian, perbandingan yang adil untuk filter skalar tetap mencakup indeks terarah di kedua produk, bukan hanya GIN atas seluruh JSONB melawan satu indeks MySQL.

Pada MySQL, generated column dapat mengambil nilai dari jalur JSON dan memiliki indeks tersendiri. Aturan pengoptimal MySQL mensyaratkan ekspresi kueri cocok dengan definisi kolom hasil dan mempunyai tipe hasil yang sama agar indeks itu dapat dikenali tanpa menyebut kolom hasil secara langsung. Untuk ekstraksi string yang menghasilkan tanda kutip JSON, definisi kolom perlu memakai JSON_UNQUOTE(). Indeks yang tersedia tidak otomatis terpakai bila aplikasi menulis ekspresi filter yang berbeda.

Pilihan indeks karena itu mengikuti pertanyaan yang diajukan aplikasi. Pencarian containment pada beragam atribut bersarang dapat memanfaatkan cakupan GIN; filter berulang pada atribut skalar tertentu dapat memakai indeks yang lebih terarah. Indeks multi nilai MySQL relevan untuk pencarian anggota array, bukan pengganti umum bagi indeks atas setiap jalur skalar. Pada kedua produk, selektivitas nilai dan jumlah baris yang cocok tetap memengaruhi rencana eksekusi.

Membaca selisih throughput sebagai keputusan

Pembagian hasil pencarian terindeks dalam benchmark tadi menghasilkan rasio sekitar 3,33 banding satu. Itu mengukur throughput dua konfigurasi lengkap, bukan percepatan yang dihasilkan indeks secara terpisah. Pengujian tersebut juga membedakan ekstraksi nilai, filter kunci bersarang, agregasi, dan pencarian terindeks. Mencampur hasil operasi itu menjadi satu angka “kinerja JSON” akan menyembunyikan pekerjaan yang sebenarnya dilakukan basis data.

Lingkungan pengujian memakai perangkat keras yang sama, cache yang telah dihangatkan, dan pengulangan pengukuran. Namun, rincian SQL serta definisi indeks untuk pencarian JSON terindeks tidak cukup untuk memastikan kedua kueri mempunyai semantik dan selektivitas yang identik. Keterangan bahwa MySQL memakai indeks multi nilai perlu dibaca bersama batas kegunaannya pada array. Selisih throughput tersebut berguna sebagai titik pembanding, tetapi belum dapat dipindahkan langsung menjadi perkiraan kapasitas aplikasi yang mencari nilai skalar.

Perhitungan keputusan juga perlu memasukkan porsi kueri JSON dalam keseluruhan layanan. Bila sebagian besar waktu aplikasi dipakai untuk operasi lain, selisih besar pada satu jenis pencarian mungkin hanya sedikit mengubah kinerja total. Sebaliknya, pada layanan yang berulang kali menyaring dokumen bersarang, biaya filter dapat menjadi penentu. Kedua kemungkinan itu bergantung pada profil kerja aplikasi, bukan reputasi umum salah satu produk.

Kecepatan baca dibayar saat menulis

Indeks menambah pekerjaan ketika baris dimasukkan atau nilai yang diindeks berubah. Dokumentasi indeks generated column MySQL menerangkan bahwa nilai kolom virtual yang diindeks dihitung dan dimasukkan ke indeks selama INSERT atau UPDATE. Generated column yang disimpan secara fisik menambah kebutuhan ruang tabel. Pilihan antara keduanya memengaruhi biaya tulis, ruang simpan, dan perhitungan saat membaca.

PostgreSQL juga perlu memelihara indeks GIN ketika isi dokumen yang diindeks berubah, selain menanggung konversi JSONB saat data masuk. Jika aplikasi menerima banyak pembaruan tetapi jarang menyaring isi dokumen, indeks luas atas seluruh JSONB dapat memakan ruang dan pekerjaan tulis tanpa manfaat baca yang sepadan. Sebaliknya, menghilangkan indeks demi ingest yang cepat dapat membuat filter yang sering dipakai harus memeriksa jauh lebih banyak baris.

Lembar uji untuk beban aplikasi

Perbandingan yang dapat dipakai untuk memilih basis data perlu mengukur kueri dan perubahan data yang benar-benar terjadi. Catatan pengujian berikut menjaga agar perbedaan hasil dapat ditelusuri ke rancangan indeks, bukan ke mesin atau isi tabel yang tidak sebanding.

1. Samakan perangkat keras, media simpan, jumlah koneksi, kondisi cache, serta pengaturan memori yang relevan. Catat versi kedua basis data agar hasil dapat diulang.
2. Gunakan dokumen dengan ukuran, kedalaman, nilai kosong, dan distribusi kunci yang menyerupai data aplikasi. Ukur pula pada ukuran tabel yang diperkirakan setelah data bertambah.
3. Pisahkan ekstraksi, filter skalar, pencarian anggota array, containment, agregasi, serta INSERT dan UPDATE. Untuk pasangan kueri yang dibandingkan, samakan makna filter, selektivitas, dan baris hasil.
4. Uji tanpa indeks dan dengan indeks yang cocok untuk setiap predikat: GIN atau indeks ekspresi pada PostgreSQL; generated column atau indeks multi nilai yang relevan pada MySQL. Simpan rencana eksekusi untuk melihat apakah indeks benar-benar dipakai.
5. Catat throughput, latensi saat beban tinggi, laju penulisan, ukuran indeks, dan waktu membangunnya. Samakan batas transaksi serta jumlah penulis serentak, karena keduanya mengubah biaya yang dirasakan aplikasi.

Untuk jalur skalar yang tetap, hasil uji paling informatif datang dari perbandingan indeks terarah di kedua produk. Untuk kueri pada banyak bagian dokumen, ukur manfaat cakupan GIN bersama ukuran dan biaya pemeliharaannya. Pilihan basis data kemudian mengikuti campuran pencarian dan penulisan yang akan dijalankan, bukan satu angka throughput terindeks.

Baca juga:

• Buffer atau Hootsuite: satu kursi bisa mengubah biaya sepuluh kali lipat
• Deepgram atau Whisper: audio nyata dapat membalik hasil akurasi
• Reksa dana aktif atau indeks: biaya kecil menggerus hasil bertahun-tahun