PostgreSQL'de JSONB: Operator'ler, SQL/JSON Function'ları, GIN Index ve Ne Zaman Kullanmamalı?

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı) üzerinde, 02'de kurduğumuz Bookstore lab'ında, şema yeniden yüklendikten hemen sonra çalıştırılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) bir tedarikçiden kitap bilgisi alıyor ve tedarikçinin servisi bazen aynı anahtarı iki kez gönderiyor. Aynı metni PostgreSQL'in iki JSON tipine çevirdik ve küçük bir belgenin diskte ne kadar yer tuttuğuna baktık:

SELECT '{"title": "Dune", "pages": 412, "pages": 896}'::json AS as_json,
       '{"title": "Dune", "pages": 412, "pages": 896}'::jsonb AS as_jsonb;

SELECT pg_column_size('{"format": "ebook", "pages": 412, "language": "en"}'::json) AS json_bytes,
       pg_column_size('{"format": "ebook", "pages": 412, "language": "en"}'::jsonb) AS jsonb_bytes;

Hangisi iki pages anahtarını da tutar, hangisi birini atar? Atan hangisini tutar: ilkini mi, sonuncusunu mu? "Binary" denince akla gelen ilk şey daha küçük olmasıysa, jsonb kaç byte tutar?

                    as_json                    |            as_jsonb
-----------------------------------------------+---------------------------------
 {"title": "Dune", "pages": 412, "pages": 896} | {"pages": 896, "title": "Dune"}

 json_bytes | jsonb_bytes
------------+-------------
         55 |          67

json metni olduğu gibi sakladı: iki anahtar da, boşluklar da, sıra da yerinde. jsonb ise metni ayrıştırıp kendi yapısına çevirdi: tekrar eden anahtardan sonuncusunu tuttu ve anahtarları kendi sırasına göre dizdi. Üstelik küçük belgede daha büyük. JSONB (JSON'ın ayrıştırılıp binary yapıda saklandığı tip) disk tasarrufu için değil, sorgulamak ve index'lemek için var. Bu yazıda books tablosuna eklediğimiz bir JSONB kolonu üzerinden operator'lere, SQL/JSON fonksiyonlarına, GIN (Generalized Inverted Index; bir satırdaki çok sayıda değeri ayrı ayrı index'leyen yapı) index'e ve JSONB'nin yanlış tercih olduğu durumlara bakıyoruz.

json mu jsonb mi?

json girdiyi yalnızca doğrular ve metin olarak saklar; her okumada yeniden ayrıştırılır. jsonb yazarken bir kez ayrıştırılır; anahtar sırası, boşluklar ve tekrar eden anahtarlar korunmaz, ama okuması hızlıdır, içerik operator'leri vardır ve index'lenebilir. Neredeyse her durumda doğru seçim jsonb. json'ın tek gerçek kullanımı, gelen metnin byte'ı byte'ına korunması gereken yerler: imzası doğrulanacak bir webhook gövdesi ya da denetim için olduğu gibi saklanan bir istek.

SQL Server 2025 ile gelen native json tipi de veriyi binary biçimde saklıyor ve geçerliliği tipin kendisi garanti ediyor; SQL Temelleri serisinin SQL Server 2025 yazısında bu tipi görmüştük. Farklar index tarafında: SQL Server 2025'te CREATE JSON INDEX yazım tarihinde hâlâ preview, PostgreSQL'in GIN index'i ise yıllardır production'da kullanılıyor.

Bookstore'da books.attributes

Kitabın formatına göre değişen bilgiler JSONB için iyi bir örnek: basılı kitabın sayfa sayısı, ciltli kitabın boyutları, e-kitabın dosya formatları ve DRM (dijital kopya koruması) durumu, sesli kitabın süresi ve seslendireni. Her biri için ayrı kolon açmak çoğu satırda NULL dolu bir tablo, ayrı tablo açmak ise her ekranda birkaç join demek. Bu yazının script'i books'a deterministik içerikli bir attributes kolonu ekliyor:

docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/05-jsonb.sql

SELECT book_id, title, attributes FROM books WHERE book_id IN (1, 6, 8, 9) ORDER BY book_id;

 book_id |     title     |                                                 attributes
---------+---------------+------------------------------------------------------------------------------------------------------------
       1 | Emma #1       | {"pages": 388, "format": "paperback", "language": "en"}
       6 | Persuasion #6 | {"pages": 121, "format": "hardcover", "language": "en", "dimensions": {"width_mm": 151, "height_mm": 231}}
       8 | Moby-Dick #8  | {"drm": true, "pages": 302, "format": "ebook", "language": "en", "file_formats": ["epub", "pdf"]}
       9 | Jane Eyre #9  | {"format": "audiobook", "language": "en", "narrator": "Linus Torvalds", "duration_minutes": 531}

40.000 kitabın 20.000'i paperback, 8.000'i ebook, 8.000'i hardcover, 4.000'i audiobook. Fiyat, başlık ve yazar gibi her kitapta olan ve sık sorgulanan alanlar ise kolon olarak kalıyor; bu ayrımın neden önemli olduğuna tuzaklar bölümünde döneceğiz.

Operator'ler: ->, ->>, #>>, @>, ? ve @?

SELECT attributes -> 'pages' AS pages_jsonb, pg_typeof(attributes -> 'pages') AS t1,
       attributes ->> 'pages' AS pages_text, pg_typeof(attributes ->> 'pages') AS t2,
       attributes #>> '{dimensions,width_mm}' AS width
FROM books WHERE book_id = 6;

 pages_jsonb |  t1   | pages_text |  t2  | width
-------------+-------+------------+------+-------
 121         | jsonb | 121        | text | 151

-> değeri jsonb olarak, ->> ise text olarak döndürür; iç içe yollar için #> ve #>> var. Asıl güç filtreleme operator'lerinde:

SELECT count(*) AS turkish_ebooks FROM books WHERE attributes @> '{"format": "ebook", "language": "tr"}';
SELECT count(*) AS with_pdf FROM books WHERE attributes -> 'file_formats' ? 'pdf';
SELECT count(*) AS has_narrator FROM books WHERE attributes ? 'narrator';
SELECT count(*) AS long_books FROM books WHERE (attributes ->> 'pages')::int > 800;
SELECT count(*) AS long_books_path FROM books WHERE attributes @? '$.pages ? (@ > 800)';

 turkish_ebooks
----------------
           1610

 with_pdf
----------
     2592

 has_narrator
--------------
         4000

 long_books
------------
        931

 long_books_path
-----------------
             931

@> (containment) "sağdaki belge soldakinin içinde var mı" sorusunu sorar ve birden fazla alanı tek seferde karşılaştırır. ? bir anahtarın ya da array'de bir string'in varlığına bakar. @? ise bir jsonpath ifadesinin sonuç üretip üretmediğine bakar; SQL standardının JSON için tanımladığı yol dili bu ve aşağıda SQL/JSON fonksiyonlarında da kullanacağız. Son iki sorgu aynı sonucu veriyor; farkı index'leme bölümünde göreceğiz.

Belgeyi Değiştirmek: jsonb_set, || ve -

BEGIN;
UPDATE books SET attributes = jsonb_set(attributes, '{pages}', '420') WHERE book_id = 1
RETURNING old.attributes AS before, new.attributes AS after;
UPDATE books SET attributes = attributes || '{"signed": true}' WHERE book_id = 1 RETURNING new.attributes;
UPDATE books SET attributes = attributes - 'signed' WHERE book_id = 1 RETURNING new.attributes;
ROLLBACK;

                         before                          |                          after
---------------------------------------------------------+---------------------------------------------------------
 {"pages": 388, "format": "paperback", "language": "en"} | {"pages": 420, "format": "paperback", "language": "en"}

                               attributes
-------------------------------------------------------------------------
 {"pages": 420, "format": "paperback", "signed": true, "language": "en"}

                       attributes
---------------------------------------------------------
 {"pages": 420, "format": "paperback", "language": "en"}

jsonb_set bir yoldaki değeri değiştirir, || iki belgeyi birleştirir (aynı anahtarda sağdaki kazanır), - anahtar siler. Sözdizimi "yalnızca pages'i değiştir" gibi görünse de PostgreSQL her seferinde yeni bir belge üretip satırın tamamını yeniden yazar; MVCC'de (her güncellemede satırın yeni bir sürümünün yazıldığı eşzamanlılık modeli) satır güncellemesi zaten böyle çalışıyor ve büyük belgelerde bunun bedelini üretim bölümünde ölçeceğiz.

SQL/JSON: JSON_VALUE, JSON_QUERY, JSON_EXISTS ve JSON_TABLE

PostgreSQL'e özgü operator'lerin yanında, SQL standardının SQL/JSON fonksiyonları da var: JSON_EXISTS, JSON_VALUE ve JSON_QUERY 17 ile geldi. SQL Server'dan gelen biri için tanıdık isimler:

SELECT JSON_VALUE(attributes, '$.pages' RETURNING int) AS pages,
       JSON_QUERY(attributes, '$.file_formats') AS formats,
       JSON_EXISTS(attributes, '$.drm ? (@ == true)') AS has_drm,
       JSON_VALUE(attributes, '$.narrator' DEFAULT 'n/a' ON EMPTY) AS narrator
FROM books WHERE book_id IN (8, 9) ORDER BY book_id;

 pages |     formats     | has_drm |    narrator
-------+-----------------+---------+----------------
   302 | ["epub", "pdf"] | t       | n/a
       |                 | f       | Linus Torvalds

JSON_VALUE skaler bir değeri istenen tipe çevirerek döndürür, JSON_QUERY bir nesne ya da array döndürür, JSON_EXISTS boolean. Hata davranışı da standarda göre: varsayılan "lax" mod, dönüşmeyen değerde hata yerine NULL verir.

SELECT JSON_VALUE('{"pages": "many"}'::jsonb, '$.pages' RETURNING int) AS pages;
SELECT JSON_VALUE('{"pages": "many"}'::jsonb, '$.pages' RETURNING int ERROR ON ERROR) AS pages;
SELECT ('{"pages": "many"}'::jsonb ->> 'pages')::int AS pages;

 pages
-------


ERROR:  invalid input syntax for type integer: "many"
ERROR:  invalid input syntax for type integer: "many"

Rapor sorgusunda tek bir bozuk belgenin tüm sorguyu düşürmesini istemiyorsan varsayılan davranış işe yarar; veri kalitesini kontrol eden bir sorguda ise ERROR ON ERROR ile hatayı görmek istersin. ->> ve cast ise her zaman hata verir.

17 ile gelen JSON_TABLE, belgeyi satır ve kolonlara açıyor; SQL Server'daki OPENJSON ... WITH'in standart karşılığı. NESTED PATH ile array elemanları ayrı satırlara açılıyor:

SELECT b.book_id, b.title, jt.*
FROM books AS b,
     JSON_TABLE(b.attributes, '$'
         COLUMNS (
             format  text PATH '$.format',
             pages   int  PATH '$.pages',
             minutes int  PATH '$.duration_minutes',
             NESTED PATH '$.file_formats[*]' COLUMNS (file_format text PATH '$')
         )) AS jt
WHERE b.book_id BETWEEN 6 AND 9
ORDER BY b.book_id;

 book_id |        title        |  format   | pages | minutes | file_format
---------+---------------------+-----------+-------+---------+-------------
       6 | Persuasion #6       | hardcover |   121 |         |
       7 | The Time Machine #7 | ebook     |   694 |         | epub
       8 | Moby-Dick #8        | ebook     |   302 |         | epub
       8 | Moby-Dick #8        | ebook     |   302 |         | pdf
       9 | Jane Eyre #9        | audiobook |       |     531 |

Index'leme: GIN jsonb_ops, jsonb_path_ops ve Expression Index

JSONB için iki GIN operator class'ı var. Varsayılan jsonb_ops her anahtarı ve değeri ayrı ayrı index'ler; jsonb_path_ops ise yalnızca "yol + değer" çiftlerinin hash'ini tutar. İkisini aynı kolona kurup boyutlarına baktık:

CREATE INDEX ix_books_attributes_gin ON books USING gin (attributes);
CREATE INDEX ix_books_attributes_path ON books USING gin (attributes jsonb_path_ops);

       indexrelname       |  size
--------------------------+--------
 ix_books_attributes_gin  | 832 kB
 ix_books_attributes_path | 584 kB

Sonra her seferinde yalnızca birini bırakıp aynı sorguların planına baktık. jsonb_ops ile:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM books WHERE attributes @> '{"format": "ebook", "language": "tr"}';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM books WHERE attributes ? 'narrator';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM books WHERE attributes ->> 'format' = 'audiobook';

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=1610.00 loops=1)
         Recheck Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
         Heap Blocks: exact=792
         ->  Bitmap Index Scan on ix_books_attributes_gin (actual rows=1610.00 loops=1)
               Index Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
               Index Searches: 1

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=4000.00 loops=1)
         Recheck Cond: (attributes ? 'narrator'::text)
         Heap Blocks: exact=923
         ->  Bitmap Index Scan on ix_books_attributes_gin (actual rows=4000.00 loops=1)
               Index Cond: (attributes ? 'narrator'::text)
               Index Searches: 1

 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on books (actual rows=4000.00 loops=1)
         Filter: ((attributes ->> 'format'::text) = 'audiobook'::text)
         Rows Removed by Filter: 36000

jsonb_path_ops ile aynı containment sorgusu yine index'i kullandı, ama ? sorgusu Seq Scan'e düştü:

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=1610.00 loops=1)
         Recheck Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
         Heap Blocks: exact=792
         ->  Bitmap Index Scan on ix_books_attributes_path (actual rows=1610.00 loops=1)
               Index Cond: (attributes @> '{"format": "ebook", "language": "tr"}'::jsonb)
               Index Searches: 1

 Aggregate (actual rows=1.00 loops=1)
   ->  Seq Scan on books (actual rows=4000.00 loops=1)
         Filter: (attributes ? 'narrator'::text)
         Rows Removed by Filter: 36000

Kısaca: GIN, operator'e göre çalışır, kolona göre değil. Üç kural yeterli:

Tek bir alana eşitlik ya da aralık sorgusu yapılıyorsa en iyi seçenek o ifadeye B-tree index:

CREATE INDEX ix_books_format ON books ((attributes ->> 'format'));
CREATE INDEX ix_books_pages ON books (((attributes ->> 'pages')::int));

 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on books (actual rows=931.00 loops=1)
         Recheck Cond: (((attributes ->> 'pages'::text))::integer > 800)
         Heap Blocks: exact=572
         ->  Bitmap Index Scan on ix_books_pages (actual rows=931.00 loops=1)
               Index Cond: (((attributes ->> 'pages'::text))::integer > 800)
               Index Searches: 1

  indexrelname   |  size
-----------------+--------
 ix_books_format | 296 kB
 ix_books_pages  | 296 kB

Expression index'in sorguyla eşleşmesi için sorgudaki ifadenin index tanımıyla birebir aynı olması gerekiyor: (attributes ->> 'pages')::int ile tanımlanan index, attributes @? '$.pages ? (@ > 800)' sorgusuna yardım etmez. Index türlerinin genel karşılaştırması bir sonraki yazının konusu.

Üretimde Dikkat

Büyük belgeler: satır yaklaşık 2 KB'ı aşınca büyük değerler önce sıkıştırılır, gerekirse TOAST (The Oversized-Attribute Storage Technique; büyük değerleri ayrı bir tabloda parça parça saklayan mekanizma) tablosuna taşınır. JSONB'de belgenin bir kısmını güncellemek, tüm değerin yeniden yazılması demek. 2.000 yorumlu bir belgede tek bir sayacı artırıp ürettiği WAL (Write-Ahead Log; değişikliklerin önce yazıldığı log) miktarını, aynı sayacı normal bir kolonda artırmakla karşılaştırdık:

CREATE TABLE book_pages (book_id int PRIMARY KEY, view_count int NOT NULL DEFAULT 0, doc jsonb NOT NULL);
INSERT INTO book_pages (book_id, doc)
SELECT 1, jsonb_build_object(
    'view_count', 0,
    'reviews', (SELECT jsonb_agg(jsonb_build_object('customer_id', g, 'rating', 1 + g % 5,
                       'text', 'Review ' || g || ' ' || md5(g::text) || md5((g * 7)::text)))
                FROM generate_series(1, 2000) AS g));
SELECT pg_column_size(doc) AS stored_bytes, octet_length(doc::text) AS text_bytes,
       pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_size
FROM book_pages, pg_class WHERE pg_class.oid = 'book_pages'::regclass;

SELECT pg_current_wal_lsn() AS l0 \gset
UPDATE book_pages SET doc = jsonb_set(doc, '{view_count}', to_jsonb((doc ->> 'view_count')::int + 1)) WHERE book_id = 1;
SELECT pg_current_wal_lsn() AS l1 \gset
UPDATE book_pages SET view_count = view_count + 1 WHERE book_id = 1;
SELECT pg_current_wal_lsn() AS l2 \gset
SELECT pg_size_pretty(pg_wal_lsn_diff(:'l1', :'l0')) AS wal_jsonb_set,
       pg_size_pretty(pg_wal_lsn_diff(:'l2', :'l1')) AS wal_plain_column;
SELECT pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_size_after FROM pg_class WHERE oid = 'book_pages'::regclass;

 stored_bytes | text_bytes | toast_size
--------------+------------+------------
       155392 |     245816 | 160 kB

 wal_jsonb_set | wal_plain_column
---------------+------------------
 175 kB        | 168 bytes

 toast_size_after
------------------
 312 kB

Tek bir sayıyı değiştirmek 175 kB WAL üretti ve TOAST tablosu 160 kB'tan 312 kB'a çıktı (eski sürüm VACUUM'a kadar duruyor); aynı iş normal kolonda 168 byte. Sık değişen alanları (sayaç, durum, son görülme zamanı) büyük bir belgenin içine koyma; kolon olarak tut.

İstatistikler: planner bir JSONB kolonunun içini bilmez. attributes ->> 'format' = 'audiobook' için tahmini 200 satır (varsayılan %0,5 seçicilik), gerçek 4.000:

EXPLAIN SELECT count(*) FROM books WHERE attributes ->> 'format' = 'audiobook';
CREATE STATISTICS st_books_format ON (attributes ->> 'format') FROM books;
ANALYZE books;
EXPLAIN SELECT count(*) FROM books WHERE attributes ->> 'format' = 'audiobook';

   ->  Seq Scan on books  (cost=0.00..1966.00 rows=200 width=0)
...
   ->  Seq Scan on books  (cost=0.00..1966.00 rows=3915 width=0)

İfadeye istatistik ekleyince (PostgreSQL 14'ten beri CREATE STATISTICS ifade alabiliyor) tahmin gerçeğe yaklaştı; ANALYZE örnekleme yaptığı için sayı her seferinde biraz değişir. Expression index de aynı istatistiği kendiliğinden toplar. Bu fark, JSONB alanı başka tablolarla join'lenen sorgularda yanlış join algoritması seçilmesine yol açar; EXPLAIN'i 07'de ayrıntılı okuyacağız.

Şema doğrulaması: PostgreSQL'de yerleşik bir JSON Schema doğrulayıcısı yok. Zorunlu alanları ve tipleri CHECK (attributes ? 'format' AND jsonb_typeof(attributes -> 'pages') IN ('number', 'null')) gibi constraint'lerle ya da uygulama katmanında kontrol et; aksi halde bir gün "pages": "many" gelir.

Sık Düşülen Tuzaklar

Her şeyi JSONB'ye koymak: fiyat, stok, yazar gibi her satırda olan, join'lenen ve foreign key isteyen alanları belgenin içine koymak, ilişkisel modelin verdiği constraint'leri, tipleri ve istatistikleri kaybetmek demek. Data Modeling ve Normalizasyon serisinin modeling anti-pattern'leri yazısında anlattığımız EAV (Entity-Attribute-Value; her özelliği ayrı bir satırda anahtar-değer olarak tutan model) sorununun JSON'lu hâli bu. Kural: yapısı satırdan satıra değişen, nadiren filtrelenen ve kendi başına anlamı olmayan bilgiler JSONB'ye; geri kalanı kolona.

->> ile sayısal karşılaştırma: ->> metin döndürür ve metin karşılaştırması alfabetiktir.

SELECT '{"pages": 90}'::jsonb ->> 'pages' > '800' AS text_compare,
       ('{"pages": 90}'::jsonb ->> 'pages')::int > 800 AS int_compare;
SELECT count(*) AS pages_as_string FROM books WHERE attributes @> '{"pages": "388"}';
SELECT count(*) AS pages_as_number FROM books WHERE attributes @> '{"pages": 388}';

 text_compare | int_compare
--------------+-------------
 t            | f

 pages_as_string
-----------------
               0

 pages_as_number
-----------------
              56

"90" metin olarak "800"den büyük. Containment'ta da tip önemli: JSON'da 388 sayı, "388" string; uygulamanın parametreyi string olarak göndermesi, hata vermeden sıfır satır döndürür.

JSON null ile SQL NULL'ı karıştırmak: {"narrator": null} belgesinde -> SQL NULL değil, JSON null döndürür; ->> ise SQL NULL döndürür. "Anahtar yok" ile "anahtar var, değeri null" farklı durumlar; sorguda hangisini kastettiğini ? ile açıkça belirt.

Küçük Bir Deneme

★☆☆ JSON_TABLE ile her formatın ortalama sayfa sayısını ve sesli kitapların toplam süresini saat cinsinden hesaplayan bir sorgu yaz.

★★☆ Türkçe e-kitapları bulan sorguyu üç biçimde yaz: @>, iki ->> eşitliği ve JSON_VALUE. Her biri için hangi index'in kullanılabileceğini önce tahmin et, sonra EXPLAIN ile doğrula.

★★★ attributes kolonuna, format anahtarının zorunlu ve dört değerden biri olmasını, pages'in varsa pozitif bir sayı olmasını sağlayan bir CHECK constraint ekle. Constraint'i NOT VALID ile ekle, mevcut 40.000 satırdan kaçının kuralı geçmediğini bir sorguyla say ve sonra VALIDATE CONSTRAINT çalıştır.

Bir sonraki yazıda index türlerinin hepsini yan yana koyuyoruz: B-tree ve 18'in skip scan'i, GIN, GiST, bölümlenmiş arama ağaçları (SP-GiST), BRIN (Block Range Index; blok aralıklarının özetini tutan index), Hash, partial ve expression index, CREATE INDEX CONCURRENTLY ve kullanılmayan index'leri bulmak.