TL; DR: JSONB dapat menyederhanakan pengembangan skema database tanpa mengorbankan kinerja kueri.
pengenalan
Mari kita berikan contoh klasik dari salah satu kasus penggunaan tertua di dunia database relasional (database): kita memiliki sebuah entitas, dan kita perlu menyimpan properti (atribut) tertentu dari entitas ini. Namun tidak semua instance memiliki kumpulan properti yang sama, dan properti lainnya mungkin ditambahkan di masa mendatang.
Cara termudah untuk mengatasi masalah ini adalah dengan membuat kolom di tabel database untuk setiap nilai properti, dan cukup mengisi kolom yang diperlukan untuk instance entitas tertentu. Besar! Masalah terpecahkan... hingga tabel Anda berisi jutaan catatan dan Anda perlu menambahkan catatan baru.
Pertimbangkan pola EAV (), hal ini cukup sering terjadi. Satu tabel berisi entitas (catatan), tabel lain berisi nama properti (atribut), dan tabel ketiga mengaitkan entitas dengan atributnya dan berisi nilai atribut tersebut untuk entitas saat ini. Ini memberi Anda kemampuan untuk memiliki kumpulan properti berbeda untuk objek berbeda, dan juga menambahkan properti dengan cepat tanpa mengubah struktur database.
Namun, saya tidak akan menulis postingan ini jika tidak ada kelemahan pada pendekatan EVA. Jadi, misalnya, untuk mendapatkan satu atau lebih entitas yang masing-masing memiliki 1 atribut, diperlukan 2 gabungan dalam kueri: yang pertama adalah gabungan dengan tabel atribut, yang kedua adalah gabungan dengan tabel nilai. Jika suatu entitas memiliki 2 atribut, maka diperlukan 4 gabungan! Selain itu, semua atribut biasanya disimpan sebagai string, yang menghasilkan pengecoran tipe untuk hasil dan klausa WHERE. Jika Anda menulis banyak pertanyaan, maka ini cukup boros dalam hal penggunaan sumber daya.
Terlepas dari kekurangan yang jelas ini, EAV telah lama digunakan untuk memecahkan masalah seperti ini. Ini adalah kekurangan yang tidak bisa dihindari, dan tidak ada alternatif lain yang lebih baik.
Tapi kemudian "teknologi" baru muncul di PostgreSQL...
Dimulai dengan PostgreSQL 9.4, tipe data JSONB ditambahkan untuk menyimpan data biner JSON. Meskipun menyimpan JSON dalam format ini biasanya membutuhkan lebih banyak ruang dan waktu dibandingkan JSON teks biasa, melakukan operasi pada JSON jauh lebih cepat. JSONB juga mendukung pengindeksan, yang membuat kueri menjadi lebih cepat.
Tipe data JSONB memungkinkan kita mengganti pola EAV yang rumit dengan menambahkan hanya satu kolom JSONB ke tabel entitas, sehingga sangat menyederhanakan desain database. Namun banyak yang berpendapat bahwa hal ini harus dibarengi dengan penurunan produktivitas... Itu sebabnya saya menulis artikel ini.
Menyiapkan database pengujian
Untuk perbandingan ini, saya membuat database pada instalasi baru PostgreSQL 9.5 pada build $80 Ubuntu 14.04 Setelah mengkonfigurasi beberapa parameter di postgresql.conf, saya menjalankan skrip menggunakan psql. Tabel berikut dibuat untuk menyajikan data dalam bentuk EAV:
CREATE TABLE entity (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT
);
CREATE TABLE entity_attribute (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE entity_attribute_value (
id SERIAL PRIMARY KEY,
entity_id INT REFERENCES entity(id),
entity_attribute_id INT REFERENCES entity_attribute(id),
value TEXT
);
Di bawah ini adalah tabel tempat data yang sama akan disimpan, tetapi dengan atribut di kolom tipe JSONB – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Terlihat jauh lebih sederhana, bukan? Kemudian ditambahkan ke tabel entitas (entitas & entitas_jsonb) 10 juta catatan, dan karenanya, tabel diisi dengan data yang sama menggunakan pola EAV dan pendekatan dengan kolom JSONB - entitas_jsonb.properti. Jadi, kami mendapatkan beberapa tipe data berbeda di antara seluruh kumpulan properti. Contoh datanya:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Jadi sekarang kami memiliki data yang sama untuk kedua opsi. Mari kita mulai membandingkan implementasi di tempat kerja!
Sederhanakan desain Anda
Telah dinyatakan sebelumnya bahwa desain database sangat disederhanakan: satu tabel, dengan menggunakan kolom JSONB untuk properti, daripada menggunakan tiga tabel untuk EAV. Namun bagaimana hal ini tercermin dalam permintaan? Memperbarui satu properti entitas terlihat seperti ini:
-- EAV
UPDATE entity_attribute_value
SET value = 'blue'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blue"')
WHERE id = 120;
Seperti yang Anda lihat, permintaan terakhir tidak terlihat sederhana. Untuk memperbarui nilai properti di objek JSONB kita harus menggunakan fungsi tersebut , dan harus meneruskan nilai baru kita sebagai objek JSONB. Namun, kita tidak perlu mengetahui pengenal apa pun sebelumnya. Melihat contoh EAV, kita perlu mengetahui entitas_id dan entitas_atribut_id untuk melakukan pembaruan. Jika Anda ingin memperbarui properti di kolom JSONB berdasarkan nama objek, semuanya dilakukan dalam satu baris sederhana.
Sekarang mari pilih entitas yang baru saja kita perbarui berdasarkan warna barunya:
-- EAV
SELECT e.name
FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blue';
Saya pikir kita bisa sepakat bahwa yang kedua lebih pendek (jangan gabung!), dan karena itu lebih mudah dibaca. JSONB menang di sini! Kami menggunakan operator JSON ->> untuk mendapatkan warna sebagai nilai teks dari objek JSONB. Ada juga cara kedua untuk mencapai hasil yang sama dalam model JSONB menggunakan operator @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Ini sedikit lebih rumit: kita memeriksa apakah objek JSON di kolom propertinya berisi objek yang berada di sebelah kanan operator @>. Kurang mudah dibaca, lebih produktif (lihat di bawah).
Mari jadikan penggunaan JSONB lebih mudah saat Anda perlu memilih beberapa properti sekaligus. Di sinilah pendekatan JSONB benar-benar berguna: kita cukup memilih properti sebagai kolom tambahan di kumpulan hasil kita tanpa memerlukan penggabungan:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Dengan EAV Anda memerlukan 2 gabungan untuk setiap properti yang ingin Anda tanyakan. Menurut pendapat saya, pertanyaan di atas menunjukkan penyederhanaan besar dalam desain database. Lihat lebih banyak contoh cara menulis kueri JSONB, juga di pos.
Sekarang saatnya berbicara tentang kinerja.
Performa
Untuk membandingkan kinerja saya menggunakan dalam kueri, untuk menghitung waktu eksekusi. Setiap kueri dieksekusi setidaknya tiga kali karena perencana kueri memerlukan waktu lebih lama untuk pertama kalinya. Pertama saya menjalankan kueri tanpa indeks apa pun. Jelas, ini merupakan keuntungan dari JSONB, karena gabungan yang diperlukan untuk EAV tidak dapat menggunakan indeks (bidang kunci asing tidak diindeks). Setelah ini saya membuat indeks pada 2 kolom kunci asing dari tabel nilai EAV, serta indeks untuk kolom JSONB.
Pembaruan data menunjukkan hasil sebagai berikut dalam hal waktu (dalam ms). Perhatikan bahwa skalanya adalah logaritmik:
Kami melihat bahwa JSONB jauh (> 50000-x) lebih cepat daripada EAV jika Anda tidak menggunakan indeks, karena alasan yang disebutkan di atas. Saat kami mengindeks kolom dengan kunci utama, perbedaannya hampir hilang, namun JSONB masih 1,3 kali lebih cepat dibandingkan EAV. Perhatikan bahwa indeks pada kolom JSONB tidak berpengaruh di sini karena kami tidak menggunakan kolom properti dalam kriteria evaluasi.
Untuk pemilihan data berdasarkan nilai properti, diperoleh hasil sebagai berikut (skala normal):
Anda dapat melihat bahwa JSONB kembali bekerja lebih cepat daripada EAV tanpa indeks, tetapi ketika EAV dengan indeks, ia masih bekerja lebih cepat daripada JSONB. Tapi kemudian saya melihat bahwa waktu untuk kueri JSONB sama, ini mendorong saya pada fakta bahwa indeks GIN tidak berfungsi. Rupanya ketika Anda menggunakan indeks GIN pada kolom dengan properti terisi, itu hanya berlaku ketika menggunakan operator penyertaan @>. Saya menggunakan ini dalam pengujian baru dan ini berdampak besar pada waktu: hanya 0,153 md! Ini 15000 kali lebih cepat dari EAV dan 25000 kali lebih cepat dari ->> operator.
Menurutku itu cukup cepat!
Ukuran tabel basis data
Mari kita bandingkan ukuran tabel untuk kedua pendekatan. Di psql kita dapat menampilkan ukuran semua tabel dan indeks menggunakan perintah dti+
Untuk pendekatan EAV, ukuran tabel sekitar 3068 MB dan indeks hingga 3427 MB dengan total 6,43 GB. Pendekatan JSONB menggunakan 1817 MB untuk tabel dan 318 MB untuk indeks, yaitu 2,08 GB. Ternyata 3 kali lebih sedikit! Fakta ini sedikit mengejutkan saya karena kami menyimpan nama properti di setiap objek JSONB.
Namun tetap saja, angka-angka tersebut berbicara sendiri: di EAV kami menyimpan 2 kunci asing bilangan bulat per nilai atribut, menghasilkan 8 byte data tambahan. Selain itu, EAV menyimpan semua nilai properti sebagai teks, sementara JSONB akan menggunakan nilai numerik dan boolean secara internal jika memungkinkan, sehingga menghasilkan jejak yang lebih kecil.
Hasil
Secara keseluruhan, menurut saya menyimpan properti entitas dalam format JSONB dapat membuat perancangan dan pemeliharaan database Anda menjadi lebih mudah. Jika Anda menjalankan banyak kueri, menyimpan semuanya dalam tabel yang sama dengan entitas akan bekerja lebih efisien. Dan fakta bahwa ini menyederhanakan interaksi antar data sudah merupakan nilai tambah, tetapi volume database yang dihasilkan 3 kali lebih kecil.
Selain itu, berdasarkan pengujian yang dilakukan, kami dapat menyimpulkan bahwa penurunan kinerja sangat kecil. Dalam beberapa kasus, JSONB bahkan lebih cepat daripada EAV, sehingga menjadikannya lebih baik. Namun tolok ukur ini tentu saja tidak mencakup semua aspek (misalnya entitas dengan jumlah properti yang sangat banyak, peningkatan jumlah properti data yang ada secara signifikan,...), jadi jika Anda memiliki saran tentang cara memperbaikinya , silakan tinggalkan di komentar!
Sumber: www.habr.com
