Memilih JSONB vs Kolom Relational

-- Percobaan adil: data yang SAMA disimpan dengan dua skema berbeda.
-- produk_rel: kolom relational dengan FK, CHECK, dan tipe yang tepat.
-- produk_json: satu kolom detail jsonb menampung semua atribut.
CREATE TABLE kategori (
    id   integer PRIMARY KEY,
    nama text NOT NULL UNIQUE
);

INSERT INTO kategori (id, nama) VALUES
    (1, 'elektronik'),
    (2, 'pakaian'),
    (3, 'makanan'),
    (4, 'olahraga');

CREATE TABLE produk_rel (
    id          integer PRIMARY KEY,
    nama        text NOT NULL,
    kategori_id integer NOT NULL REFERENCES kategori(id),
    merek       text NOT NULL,
    harga       numeric(10,2) NOT NULL CHECK (harga > 0),
    berat_gram  integer NOT NULL
);

CREATE TABLE produk_json (
    id     integer PRIMARY KEY,
    nama   text NOT NULL,
    detail jsonb NOT NULL
);

INSERT INTO produk_rel (id, nama, kategori_id, merek, harga, berat_gram)
SELECT g,
       'Produk ' || lpad(g::text, 5, '0'),
       1 + (g % 4),
       (ARRAY['Elang','Nusantara','Gunung Jaya'])[1 + (g % 3)],
       ((g % 40) + 1) * 2500,
       (g % 500) + 100
FROM generate_series(1, 20000) AS g;

INSERT INTO produk_json (id, nama, detail)
SELECT g,
       'Produk ' || lpad(g::text, 5, '0'),
       jsonb_build_object(
           'kategori', (ARRAY['elektronik','pakaian','makanan','olahraga'])[1 + (g % 4)],
           'merek',    (ARRAY['Elang','Nusantara','Gunung Jaya'])[1 + (g % 3)],
           'harga',    ((g % 40) + 1) * 2500,
           'berat_gram', (g % 500) + 100)
FROM generate_series(1, 20000) AS g;

ANALYZE produk_rel;
ANALYZE produk_json;

-- Jumlah baris keduanya identik.
SELECT count(*) AS baris_rel,
       (SELECT count(*) FROM produk_json) AS baris_json
FROM produk_rel;

-- Wujud data yang sama di dua skema.
SELECT id, nama, kategori_id, merek, harga, berat_gram
FROM produk_rel
WHERE id IN (1, 2)
ORDER BY id;

SELECT id, nama, detail
FROM produk_json
WHERE id IN (1, 2)
ORDER BY id;

CREATE TABLE
INSERT 0 4
CREATE TABLE
CREATE TABLE
INSERT 0 20000
INSERT 0 20000
ANALYZE
ANALYZE
 baris_rel | baris_json 
-----------+------------
     20000 |      20000
(1 row)

 id |     nama     | kategori_id |    merek    |  harga  | berat_gram 
----+--------------+-------------+-------------+---------+------------
  1 | Produk 00001 |           2 | Nusantara   | 5000.00 |        101
  2 | Produk 00002 |           3 | Gunung Jaya | 7500.00 |        102
(2 rows)

 id |     nama     |                                      detail                                       
----+--------------+-----------------------------------------------------------------------------------
  1 | Produk 00001 | {"harga": 5000, "merek": "Nusantara", "kategori": "pakaian", "berat_gram": 101}
  2 | Produk 00002 | {"harga": 7500, "merek": "Gunung Jaya", "kategori": "makanan", "berat_gram": 102}
(2 rows)

-- Laporan yang sama, dua penulisan: kolom vs operator jsonb.

-- Versi kolom: JOIN ke tabel kategori, agregat kolom biasa.
SELECT k.nama AS kategori,
       count(*) AS jumlah,
       sum(r.harga) AS total_nilai
FROM produk_rel r
JOIN kategori k ON k.id = r.kategori_id
GROUP BY k.nama
ORDER BY k.nama;

-- Versi jsonb: kategori dan harga diekstrak dari dokumen.
SELECT detail ->> 'kategori' AS kategori,
       count(*) AS jumlah,
       sum((detail ->> 'harga')::numeric) AS total_nilai
FROM produk_json
GROUP BY detail ->> 'kategori'
ORDER BY detail ->> 'kategori';

  kategori  | jumlah | total_nilai  
------------+--------+--------------
 elektronik |   5000 | 237500000.00
 makanan    |   5000 | 262500000.00
 olahraga   |   5000 | 275000000.00
 pakaian    |   5000 | 250000000.00
(4 rows)

  kategori  | jumlah | total_nilai 
------------+--------+-------------
 elektronik |   5000 |   237500000
 makanan    |   5000 |   262500000
 olahraga   |   5000 |   275000000
 pakaian    |   5000 |   250000000
(4 rows)

-- Filter yang sama pada dua skema. EXPLAIN ANALYZE memperlihatkan
-- bedanya: bentuk rencana sama (Seq Scan), tetapi setiap baris versi
-- jsonb menanggung biaya parse dokumen + ekstraksi teks + cast.
-- Angka waktu bervariasi antar mesin; perbandingan biaya dan
-- kecenderungannya yang stabil.

EXPLAIN ANALYZE SELECT count(*)
FROM produk_rel
WHERE kategori_id = 2 AND harga > 50000;

EXPLAIN ANALYZE SELECT count(*)
FROM produk_json
WHERE detail ->> 'kategori' = 'pakaian'
  AND (detail ->> 'harga')::numeric > 50000;

                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=492.25..492.26 rows=1 width=8) (actual time=2.425..2.425 rows=1 loops=1)
   ->  Seq Scan on produk_rel  (cost=0.00..486.00 rows=2500 width=0) (actual time=0.016..2.312 rows=2500 loops=1)
         Filter: ((harga > '50000'::numeric) AND (kategori_id = 2))
         Rows Removed by Filter: 17500
 Planning Time: 0.227 ms
 Execution Time: 2.454 ms
(6 rows)

                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1256.11..1256.12 rows=1 width=8) (actual time=5.121..5.122 rows=1 loops=1)
   ->  Seq Scan on produk_json  (cost=0.00..1256.03 rows=33 width=0) (actual time=0.454..5.003 rows=2500 loops=1)
         Filter: (((detail ->> 'kategori'::text) = 'pakaian'::text) AND (((detail ->> 'harga'::text))::numeric > '50000'::numeric))
         Rows Removed by Filter: 17501
 Planning Time: 0.127 ms
 Execution Time: 5.140 ms
(6 rows)

-- Kolom menolak data yang melanggar constraint sejak pintu masuk.
INSERT INTO produk_rel (id, nama, kategori_id, merek, harga, berat_gram)
VALUES (20001, 'Produk Rusak', 1, 'Elang', -500, 200);

psql:harga-invalid.sql:3: ERROR:  new row for relation "produk_rel" violates check constraint "produk_rel_harga_check"
DETAIL:  Failing row contains (20001, Produk Rusak, 1, Elang, -500.00, 200).

-- Dokumen jsonb menerima nilai yang sama tanpa protes --
-- harga negatif dan kategori yang salah ketik sama-sama lolos.
INSERT INTO produk_json (id, nama, detail)
VALUES (20001, 'Produk Rusak',
        '{"kategori": "elektronikk", "merek": "Elang",
          "harga": -500, "berat_gram": 200}');

SELECT id, nama,
       detail ->> 'kategori' AS kategori,
       (detail ->> 'harga')::numeric AS harga
FROM produk_json
WHERE id = 20001;

-- Fragmentasi senyap: "elektronikk" kini menjadi kategori baru
-- yang tidak akan pernah cocok dengan laporan "elektronik".
SELECT detail ->> 'kategori' AS kategori, count(*) AS jumlah
FROM produk_json
GROUP BY detail ->> 'kategori'
ORDER BY detail ->> 'kategori';

INSERT 0 1
  id   |     nama     |  kategori   | harga 
-------+--------------+-------------+-------
 20001 | Produk Rusak | elektronikk |  -500
(1 row)

  kategori   | jumlah 
-------------+--------
 elektronik  |   5000
 elektronikk |      1
 makanan     |   5000
 olahraga    |   5000
 pakaian     |   5000
(5 rows)

-- Pola produksi: kolom inti yang dijamin + atribut jsonb untuk ekor panjang.
CREATE TABLE produk_toko (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    kategori text NOT NULL,
    harga    numeric NOT NULL CHECK (harga > 0),
    atribut  jsonb NOT NULL DEFAULT '{}'::jsonb
);

-- Tiap produk membawa atribut ekor panjang yang berbeda-beda;
-- produk tanpa atribut cukup membawa {}.
INSERT INTO produk_toko (id, nama, kategori, harga, atribut) VALUES
    (1, 'Sepatu Lari Pagi', 'olahraga', 750000,
     '{"ukuran": [40, 41, 42], "warna": "merah"}'),
    (2, 'Kemeja Batik Kasih', 'pakaian', 320000,
     '{"bahan": "katun", "ukuran": ["S", "M", "L"]}'),
    (3, 'Tas Ransel Gunung', 'olahraga', 450000,
     '{"kapasitas_liter": 30, "tahan_air": true}'),
    (4, 'Madu Hutan 500ml', 'makanan', 25000, '{}');

-- Jalur panas: filter dan sort pada kolom inti,
-- plus CHECK yang menjaga harga tetap positif.
SELECT id, nama, harga
FROM produk_toko
WHERE kategori = 'olahraga' AND harga <= 500000
ORDER BY id;

-- Ekor panjang: dibaca hanya bila relevan;
-- produk tanpa atribut itu mendapat NULL dengan tenang.
SELECT id, nama,
       atribut ->> 'kapasitas_liter' AS kapasitas,
       atribut -> 'ukuran' AS ukuran
FROM produk_toko
WHERE kategori = 'olahraga'
ORDER BY id;

-- Atribut tetap bisa disaring dengan operator jsonb bila perlu,
-- dan siap diindeks GIN seperti chapter sebelumnya.
SELECT id, nama
FROM produk_toko
WHERE atribut @> '{"tahan_air": true}'
ORDER BY id;

CREATE TABLE
INSERT 0 4
 id |       nama        | harga  
----+-------------------+--------
  3 | Tas Ransel Gunung | 450000
(1 row)

 id |       nama        | kapasitas |    ukuran    
----+-------------------+-----------+--------------
  1 | Sepatu Lari Pagi  |           | [40, 41, 42]
  3 | Tas Ransel Gunung | 30        | 
(2 rows)

 id |       nama        
----+-------------------
  3 | Tas Ransel Gunung
(1 row)

-- Jalur migrasi: promosi path jsonb menjadi kolom asli.
-- Dipicu saat sebuah path berubah status -- dari atribut sesekali
-- menjadi field yang rutin difilter, diurutkan, atau dijoin.

-- 1. Tambahkan kolom baru (tabel tetap bisa dipakai selama proses).
ALTER TABLE produk_json ADD COLUMN harga numeric;

-- 2. Isi (backfill) dari dokumen; WHERE menjaga baris tanpa kunci harga
--    tetap NULL alih-alih error cast.
UPDATE produk_json
SET harga = (detail ->> 'harga')::numeric
WHERE detail ? 'harga';

-- 3. Beri index seperti kolom biasa, lalu perbarui statistik.
CREATE INDEX idx_json_harga ON produk_json (harga);
ANALYZE produk_json;

-- 4. Query harga kini menulis nama kolomnya langsung.
SELECT count(*) AS di_atas_50rb
FROM produk_json
WHERE harga > 50000;

-- Dokumen lama tetap utuh: dua sumber hidup berdampingan
-- sampai aplikasi bermigrasi dan path lama dibuang.
SELECT id,
       detail ->> 'harga' AS harga_dokumen,
       harga AS harga_kolom
FROM produk_json
WHERE id IN (1, 20001)
ORDER BY id;

ALTER TABLE
UPDATE 20001
CREATE INDEX
ANALYZE
 di_atas_50rb 
--------------
        10000
(1 row)

  id   | harga_dokumen | harga_kolom 
-------+---------------+-------------
     1 | 5000          |        5000
 20001 | -500          |        -500
(2 rows)