Hologres：Penyimpanan Berorientasi Kolom untuk Data Berformat JSONB

Aktifkan penyimpanan berorientasi kolom untuk tipe data JSONB

Eksekusi pernyataan berikut untuk mengaktifkan penyimpanan berorientasi kolom untuk kolom tipe data JSONB dalam sebuah tabel.

-- Aktifkan penyimpanan berorientasi kolom untuk kolom tipe data JSONB dalam sebuah tabel.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

Parameter table_name menentukan nama tabel, dan parameter column_name menentukan nama kolom.

Aktifkan inferensi tipe DECIMAL

Hologres V2.0.11 dan versi lebih baru mendukung penyimpanan berorientasi kolom untuk data tipe DECIMAL. Contoh data JSON:

{
  "name":"Mike",
  "statistical_period":"2023-01-01 00:00:00+08",
  "balance":123.45
}

Setelah Anda mengaktifkan inferensi tipe DECIMAL untuk data balance, penyimpanan berorientasi kolom didukung untuk data tersebut. Contoh pernyataan:

-- Aktifkan penyimpanan berorientasi kolom untuk kolom tipe data DECIMAL dalam sebuah tabel.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

Parameter table_name menentukan nama tabel, dan parameter column_name menentukan nama kolom.

Periksa mode penyimpanan tipe data JSONB

Pernyataan yang digunakan untuk memeriksa mode penyimpanan tipe data JSONB berbeda berdasarkan versi Hologres.

• Untuk Hologres V1.3.37 dan versi lebih baru, eksekusi pernyataan berikut:

  null

  Dalam Hologres V2.0.17 dan versi lebih lama, pernyataan dapat digunakan untuk memeriksa mode penyimpanan hanya tabel dalam skema public. Dalam Hologres V2.0.18 dan versi lebih baru, pernyataan dapat digunakan untuk memeriksa mode penyimpanan tabel dalam semua skema.

  -- Dalam Hologres V2.0.17 dan versi lebih lama, pernyataan dapat digunakan untuk memeriksa mode penyimpanan hanya tabel dalam skema public. Dalam Hologres V2.0.18 dan versi lebih baru, pernyataan dapat digunakan untuk memeriksa mode penyimpanan tabel dalam semua skema.
  SELECT * FROM hologres.hg_column_options WHERE schema_name='<schema_name>' AND table_name = '<table_name>';

  Parameter schema_name menentukan nama skema, dan parameter table_name menentukan nama tabel.

• Untuk Hologres V1.3.10 hingga V1.1.36, eksekusi pernyataan berikut:

  SELECT DISTINCT
      a.attnum as num,
      a.attname as name,
      format_type(a.atttypid, a.atttypmod) as type,
      a.attnotnull as notnull, 
      com.description as comment,
      coalesce(i.indisprimary,false) as primary_key,
      def.adsrc as default,
      a.attoptions
  FROM pg_attribute a 
  JOIN pg_class pgc ON pgc.oid = a.attrelid
  LEFT JOIN pg_index i ON 
      (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
  LEFT JOIN pg_description com on 
      (pgc.oid = com.objoid AND a.attnum = com.objsubid)
  LEFT JOIN pg_attrdef def ON 
      (a.attrelid = def.adrelid AND a.attnum = def.adnum)
  WHERE a.attnum > 0 AND pgc.oid = a.attrelid
  AND pg_table_is_visible(pgc.oid)
  AND NOT a.attisdropped
  AND pgc.relname = '<table_name>' 
  ORDER BY a.attnum;

  Parameter table_name menentukan nama tabel.

• Contoh Hasil

  Dalam gambar berikut, properti attoptions atau option dari sebuah kolom adalah enable_columnar_type = ON, yang menunjukkan bahwa penyimpanan berorientasi kolom diaktifkan untuk kolom ini.

  

Nonaktifkan penyimpanan berorientasi kolom untuk tipe data JSONB

Eksekusi pernyataan berikut untuk menonaktifkan penyimpanan berorientasi kolom untuk kolom tipe data JSONB dalam sebuah tabel.

-- Nonaktifkan penyimpanan berorientasi kolom untuk kolom tipe data JSONB dalam sebuah tabel.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

Parameter table_name menentukan nama tabel, dan parameter column_name menentukan nama kolom.

Nonaktifkan inferensi tipe DECIMAL

Untuk menonaktifkan inferensi tipe DECIMAL untuk kolom sebuah tabel, eksekusi pernyataan berikut:

-- Nonaktifkan penyimpanan berorientasi kolom untuk kolom tipe data DECIMAL dalam sebuah tabel.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

Parameter table_name menentukan nama tabel, dan parameter column_name menentukan nama kolom.

Buat indeks bitmap

Di Hologres, properti bitmap_columns menentukan indeks bitmap. Properti ini menggunakan skema indeks yang independen dari penyimpanan data. Anda dapat menggunakan properti bitmap_columns untuk mempercepat perbandingan ekuivalen berdasarkan struktur vektor bitmap. Indeks bitmap dapat membantu menyaring data yang sama dengan nilai tertentu dalam file yang disimpan. Oleh karena itu, properti bitmap_columns berlaku untuk kueri titik. Di Hologres V2.0 dan versi lebih baru, Anda dapat membuat indeks bitmap untuk kolom dengan data berformat JSONB yang disimpan dalam mode penyimpanan berorientasi kolom. Setelah penyimpanan berorientasi kolom diaktifkan untuk tipe data JSONB, sistem dapat mengurai data dari tipe data berikut: INT, INT[], BIGINT, BIGINT[], TEXT, TEXT[], dan JSONB. Setelah fitur indeks bitmap diaktifkan, sistem membuat indeks bitmap untuk kolom dengan data terurai dari tipe data INT, INT[], BIGINT, BIGINT[], TEXT, dan TEXT[].

Sintaks:

call set_table_property('<table_name>', 'bitmap_columns', '[<columnName>{:[on|off]}[,...]]');

Tabel berikut menjelaskan parameter dalam sintaks sebelumnya.

Contoh

1. Buat tabel.

   DROP TABLE IF EXISTS user_tags;
   
   -- Buat tabel.
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds timestamptz,
       tags jsonb
   );
   COMMIT;

2. Aktifkan penyimpanan berorientasi kolom untuk kolom tags tipe data JSONB.

   ALTER TABLE user_tags ALTER COLUMN tags SET (enable_columnar_type = ON);

3. Lihat mode penyimpanan kolom tags.

   select * from hologres.hg_column_options where table_name = 'user_tags';

   Dalam hasil yang dikembalikan berikut, properti options dari kolom tags adalah enable_columnar_type = on, yang menunjukkan bahwa penyimpanan berorientasi kolom diaktifkan.

    schema_name | table_name | column_id | column_name |       column_type        | notnull | comment | default |          options
   -------------+------------+-----------+-------------+--------------------------+---------+---------+---------+---------------------------
    public      | user_tags  |         1 | ds          | timestamp with time zone | f       |         |         |
    public      | user_tags  |         2 | tags        | jsonb                    | f       |         |         | {enable_columnar_type=on}
   (2 rows)

4. Impor data.

   INSERT INTO user_tags (ds, tags)
   SELECT
       '2022-01-01 00:00:00+08'
       , ('{"id":' || i || ',"first_name" :"Sig",  "gender" :"Male"}')::jsonb
   FROM
       generate_series(1, 10001) i;

5. Opsional. Tulis data secara paksa ke disk.

   Ketika data ditulis ke disk, Hologres mengaktifkan penyimpanan berorientasi kolom untuk data berformat JSONB. Untuk melihat efek penyimpanan sesegera mungkin, Anda dapat mengeksekusi pernyataan berikut untuk menulis data secara paksa ke disk:

   VACUUM user_tags;

6. Kueri data dari tabel.

   Eksekusi pernyataan berikut untuk mengkueri data dalam kolom first_name yang nilainya dalam kolom id adalah 10:

   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

7. Periksa apakah penyimpanan berorientasi kolom diaktifkan untuk tipe data JSONB berdasarkan rencana eksekusi.

   -- Kueri statistik rinci.
   SET hg_experimental_show_execution_statistics_in_explain = ON;
   -- Kueri rencana eksekusi.
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   Dalam gambar berikut, columnar_access_used dikembalikan, yang menunjukkan bahwa penyimpanan berorientasi kolom diaktifkan untuk tipe data JSONB.

   

8. Untuk kueri di Langkah 6, Anda dapat membuat indeks bitmap untuk kolom tags untuk meningkatkan efisiensi kueri titik berbasis kunci. Contoh pernyataan:

   call set_table_property('user_tags', 'bitmap_columns', 'tags');

9. Lihat rencana eksekusi untuk memeriksa apakah indeks bitmap digunakan.

   -- Lihat rencana eksekusi.
   EXPLAIN ANALYZE
   SELECT
       (tags -> 'first_name')::text AS first_name
   FROM
       user_tags
   WHERE (tags -> 'id')::int = 10;

   Gambar berikut menunjukkan hasil yang dikembalikan.

   Hasil yang dikembalikan berisi bitmap_used, yang menunjukkan bahwa indeks bitmap digunakan.

Kueri yang memerlukan kolom lengkap tipe data JSONB

Penyimpanan berorientasi kolom untuk tipe data JSONB yang disediakan oleh Hologres memberikan kinerja yang lebih baik dalam sebagian besar skenario. Namun, untuk kueri yang memerlukan kolom lengkap tipe data JSONB, mode penyimpanan asli data berformat JSONB memberikan kinerja yang lebih baik. Contoh pernyataan SQL:

-- Eksekusi pernyataan bahasa definisi data (DDL) berikut untuk membuat tabel:
CREATE TABLE TBL(key int, json_data jsonb); 
SELECT json_data FROM TBL WHERE key = 123;
SELECT * FROM TBL limit 10;

Penyimpanan berorientasi kolom untuk tipe data JSONB diimplementasikan pada lapisan bawah. Jika Anda ingin mendapatkan kolom lengkap tipe data JSONB, sistem perlu mengonversi data dalam penyimpanan berorientasi kolom menjadi data dalam format JSONB asli.

Proses konversi menghasilkan sejumlah besar operasi I/O dan menimbulkan overhead tinggi. Konversi dapat menyebabkan kinerja menurun jika melibatkan sejumlah besar kolom dan jumlah data yang besar. Kami menyarankan Anda tidak mengaktifkan penyimpanan berorientasi kolom untuk tipe data JSONB dalam skenario ini.

Penyimpanan data tipe data JSONB yang sangat jarang

Jika data berformat JSONB yang ingin Anda simpan dalam penyimpanan berorientasi kolom berisi bidang yang jarang, Hologres menempatkan bidang yang jarang dalam kolom bernama holo.remaining untuk mencegah terciptanya jumlah kolom yang berlebihan. Jika semua bidang berformat JSONB jarang, misalnya, setiap bidang hanya muncul sekali, penyimpanan berorientasi kolom tidak berfungsi. Ini karena semua bidang ditempatkan dalam kolom holo.remaining. Dalam hal ini, penyimpanan berorientasi kolom untuk tipe data JSONB tidak meningkatkan kinerja kueri.

Penyimpanan data berformat JSONB dengan struktur bersarang kompleks

Dalam contoh ini, simpul akar data berformat JSONB adalah array, yang berisi data berformat JSONB dengan struktur berbeda. Hologres menyimpan data dengan struktur bersarang kompleks dalam satu kolom. Dalam hal ini, penyimpanan berorientasi kolom untuk tipe data JSONB tidak meningkatkan kinerja kueri.

'[
  {"key1": "value1"}, 
  {"key2": 123},
  {"key3": 123.01}
]'

Diagnosis kueri lambat

Jika kinerja kueri menurun setelah Anda mengaktifkan penyimpanan berorientasi kolom untuk tipe data JSONB, periksa apakah kueri memerlukan kolom lengkap tipe data JSONB. Untuk pernyataan SQL yang kompleks, Anda dapat mengeksekusi pernyataan Explain Analyze untuk memeriksa apakah kueri memerlukan kolom lengkap tipe data JSONB. Contoh pernyataan:

CREATE TABLE TBL(key int, json_data json); -- Buat tabel.
ALTER TABLE TBL ALTER COLUMN json_data SET (enable_columnar_type = on);
Explain Analyze SELECT json_data FROM TBL WHERE key = 123;

Nilai pengembalian pernyataan Explain Analyze berisi kata kunci Hint. Jika kata kunci Hint berisi informasi berikut, kueri memerlukan kolom lengkap tipe data JSONB. Akibatnya, kinerja kueri menurun.

Column 'json_data' has enabled columnar jsonb, but the query scanned the entire Jsonb value

Sintaks SQL dengan kinerja lebih baik

• Data berformat JSONB dapat dikonversi ke format TEXT menggunakan sintaks yang berbeda. Dalam contoh ini, operator ->> memberikan kinerja yang lebih baik jika Anda ingin mendapatkan atribut name dari kolom json_data.

  -- Kinerja lebih baik
  SELECT json_data->>'name' FROM tbl; 
  -- Kinerja biasa
  SELECT (json_data->'name')::text FROM tbl;

• Jika bidang berformat JSONB berisi array TEXT dan Anda ingin memeriksa apakah array TEXT berisi nilai yang ditentukan, kami menyarankan Anda menggunakan sintaks berikut:

  SELECT key FROM tbl WHERE jsonb_to_textarray(json_data->'phones') && ARRAY['123456'];