Database Indexleme: PostgreSQL JSONB GIN Indekslerinin Gizli Maliyeti

Veritabanı optimizasyonu için JSONB sorgu yükünü önce ölçün

Bir veritabanı eğitimi içinde JSONB indeksini yalnızca EXPLAIN çıktısında Index Scan gördüğünüz için başarılı saymak hatadır. SQL eğitimi pratiğinde önce sorgunun çağrı sayısını, ortalama süresini, blok okumalarını ve dönen satır sayısını ayırmak gerekir. PostgreSQL'de pg_stat_statements, uygulama trafiğindeki aday sorguyu bulmak için doğrudan kullanılabilir. Aşağıdaki sorguda yüksek shared_blks_read değeri diskten okuma, yüksek temp_blks_written değeri ise planın ara sonucu diske taşıdığı anlamına gelir.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  temp_blks_written,
  query
FROM pg_stat_statements
WHERE query ILIKE '%events%'
  AND query ILIKE '%payload%'
ORDER BY total_exec_time DESC
LIMIT 10;

Ölçülecek sorguyu bind parametresiyle üretin; metne sabit JSON gömmek, uygulamanın hazırlıklı ifade davranışından farklı bir plan seçilmesine yol açabilir. Örneğin olay verisi için hedef sorgu şudur: WHERE tenant_id = $1 AND payload @> $2::jsonb. Ardından gerçekçi tenant ve filtre seçiciliğiyle EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) çalıştırın. Heap Blocks: exact sayısı yüksekse GIN eşleşmesinden sonra çok sayıda heap satırı doğrulanıyordur; yalnızca indeks boyutuna bakmak bu maliyeti gizler.

Database indexleme: jsonb_ops ve jsonb_path_ops seçimi

JSONB için varsayılan jsonb_ops, containment yanında anahtar varlığı operatörlerini de destekler: ?, ?| ve ?&. Sorgularınız sadece containment, yani @>, veya JSONPath eşleşmeleri @? ve @@ kullanıyorsa jsonb_path_ops genellikle daha az indeks girdisi üretir. Mekanizma şudur: jsonb_ops her anahtar ve değere ilişkin daha genel token'lar tutar; jsonb_path_ops ise path-value çiftlerini daha dar bir temsille indeksler. Bunun karşılığında payload ? 'campaign' sorgusu path_ops indeksinden yararlanamaz.

-- Mevcut sorgular sadece @> kullanıyorsa önce staging ortamında karşılaştırın.
CREATE INDEX CONCURRENTLY events_payload_path_gin
  ON events USING gin (payload jsonb_path_ops);

ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS, WAL)
SELECT id, occurred_at
FROM events
WHERE tenant_id = 42
  AND payload @> '{"type":"payment_failed","country":"TR"}'::jsonb
ORDER BY occurred_at DESC
LIMIT 100;

CREATE INDEX CONCURRENTLY transaction block içinde çalışmaz; migration framework'ünüz tüm migration dosyasını otomatik transaction'a alıyorsa bu komut başarısız olur. Ayrıca yeni GIN indeksini oluşturmak eski indeksi otomatik olarak kullanımdan kaldırmaz. Canlıda önce pg_stat_user_indexes.idx_scan ile iki indeksin gerçekten tarandığını, sonra sorgu logları veya pg_stat_statements ile ? ailesinin bulunmadığını doğrulayın. Bu kontrol yapılmadan jsonb_ops indeksini silmek, nadir çalışan yönetim sorgularını sequential scan'e düşürebilir.

GIN pending list yazma gecikmesini görünür yapın

GIN'in fastupdate seçeneği, her INSERT sırasında ana indeks ağacını güncellemek yerine girişleri pending list'e ekler. Küçük INSERT işlemleri ucuzlar; ancak liste temizlendiğinde tek bir yazma işlemi çok sayıda indeks sayfasını değiştirir, WAL üretir ve tail latency yükselir. Bu nedenle veritabanı performans ayarlama çalışmasında yalnızca ortalama INSERT süresini değil, pending list sayfa ve tuple sayısını da izleyin. pgstattuple eklentisindeki pgstatginindex bu gözlemi sağlar.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT *
FROM pgstatginindex('events_payload_path_gin'::regclass);

-- Bakım penceresinde, temizlemenin yazma etkisini kontrollü ölçmek için:
SELECT gin_clean_pending_list('events_payload_path_gin'::regclass);

-- Yeni yazılar için davranışı değiştirir; mevcut pending list'i kendiliğinden temizlemez.
ALTER INDEX events_payload_path_gin SET (fastupdate = off);

fastupdate = off evrensel bir çözüm değildir. Sürekli INSERT alan, çoğunlukla append-only bir olay tablosunda her satırın doğrudan GIN ağacına yazılması toplam WAL ve CPU maliyetini artırabilir. Buna karşılık kullanıcı isteğiyle yapılan tekil yazılarda, periyodik pending-list temizliğinin oluşturduğu sıçramalar SLO'yu bozuyorsa mantıklı olabilir. Aynı yükü iki ayrı testte çalıştırın: ilkinde varsayılan ayar, ikincisinde fastupdate = off. Her iki durumda pgstatginindex, pg_stat_wal.wal_bytes ve uygulama tarafı p95 INSERT gecikmesini kaydedin.

Önce-sonra karşılaştırmasını tekrarlanabilir hale getirin

İndeks değişikliğini doğrulamak için tek bir sıcak-cache EXPLAIN ANALYZE yeterli değildir. PostgreSQL'in plan ve cache etkisini ayırmak için aynı veri anlık görüntüsünde, aynı eşzamanlılıkla ve aynı parametre dağılımıyla iki koşu yapın. pgbench custom script'i tenant dağılımını ve JSONB filtresini tekrarlanabilir kılar. Testten önce istatistikleri sıfırlamak, yalnızca o koşunun sayaçlarını okumanızı sağlar.

-- jsonb-read.sql
\set tenant random(1, 500)
SELECT id, occurred_at
FROM events
WHERE tenant_id = :tenant
  AND payload @> '{"type":"payment_failed"}'::jsonb
ORDER BY occurred_at DESC
LIMIT 50;

-- Her koşudan hemen önce:
SELECT pg_stat_statements_reset();

pgbench -n -c 32 -j 8 -T 300 -f jsonb-read.sql appdb

-- Koşu sonunda sorgunun ortalama, sapma ve fiziksel okumasını alın:
SELECT calls, mean_exec_time, stddev_exec_time,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE 'SELECT id, occurred_at%';

Karar tablosunda en az şu dört alanı saklayın: pgbench TPS, mean_exec_time, stddev_exec_time ve shared_blks_read / calls. Örneğin path_ops sonrası ortalama süre düşerken stddev_exec_time yükseliyorsa, pending-list cleanup veya checkpoint anları daha seyrek fakat pahalı gecikmeler yaratıyor olabilir. Bu durumda ikinci bir koşuyu eşzamanlı INSERT ile yapın. Salt okuma benchmark'ı GIN'in üretim maliyetinin önemli kısmını, yani yazma ve WAL yolunu ölçmez.

SQL Server eğitimi ve NoSQL eğitimi için aynı desenin karşılığı

SQL Server eğitimi bağlamında JSON belgesinin tamamını indekslemeye çalışmak yerine, yüksek seçicilikte ve sık filtrelenen alanı persisted computed column olarak çıkarmak daha öngörülebilir bir plandır. JSON_VALUE varsayılan olarak geniş bir karakter değeri döndürebildiğinden, indeks anahtarının uzunluğu kontrol edilmelidir. Aşağıdaki CONVERT(varchar(32), ...) hem anahtar boyutunu sınırlar hem de uygulamanın filtre ifadesiyle aynı dönüşümü kullanmasını zorunlu kılar.

ALTER TABLE dbo.Events ADD event_type AS
  CONVERT(varchar(32), JSON_VALUE(payload, '$.type')) PERSISTED;
GO
CREATE INDEX IX_Events_tenant_type_occurred
  ON dbo.Events(tenant_id, event_type, occurred_at DESC)
  INCLUDE (id);
GO
SET STATISTICS IO, TIME ON;
SELECT TOP (100) id, occurred_at
FROM dbo.Events
WHERE tenant_id = @tenant_id
  AND event_type = 'payment_failed'
ORDER BY occurred_at DESC;

NoSQL eğitimi tarafında MongoDB için eşdeğer kontrol explain('executionStats') ile yapılır. totalKeysExamined ve totalDocsExamined değerleri dönen belge sayısından çok büyükse indeks sırası veya filtre seçiciliği yanlıştır. Tenant alanını bileşik indeksin başına koymak, tenant izolasyonlu sorgularda taranan anahtar aralığını sınırlar; tek başına wildcard indeks kullanmak ise daha geniş yazma maliyeti ve daha zayıf selectivity üretebilir.

db.events.createIndex({
  tenantId: 1,
  'payload.type': 1,
  occurredAt: -1
});

db.events.find({
  tenantId: 42,
  'payload.type': 'payment_failed'
}).sort({ occurredAt: -1 }).limit(100)
  .explain('executionStats');

Sık Sorulan Sorular

Database indexleme sırasında jsonb_ops yerine jsonb_path_ops ne zaman seçilmeli?

Sorgu envanteriniz containment için @>, ya da JSONPath için @? ve @@ kullanıyorsa path_ops'u staging ortamında test edin. Uygulamada payload ? 'key', ?| veya ?& sorguları varsa jsonb_ops gerekir. Kararı EXPLAIN (ANALYZE, BUFFERS) ile heap blokları ve pg_relation_size ile indeks boyutunu iki operatör sınıfında karşılaştırarak verin.

Veritabanı performans ayarlama için GIN fastupdate kapatılmalı mı?

Önce pgstatginindex ile pending_pages ve pending_tuples değerini, pg_stat_wal ile WAL farkını ölçün. fastupdate kapalıyken her INSERT ana GIN ağacına doğrudan gider; cleanup sıçramalarını azaltabilir fakat sürekli yazma yükünde CPU ve WAL maliyetini büyütebilir. Aynı pgbench INSERT senaryosunu iki ayarla en az 5 dakika çalıştırıp p95 uygulama gecikmesini karşılaştırın.

SQL eğitimi kapsamında JSON alanı için SQL Server indeksi nasıl tasarlanır?

Sık filtrelenen JSON yolunu PERSISTED computed column olarak çıkarın ve sorguda aynı kolonu kullanın. Örneğin JSON_VALUE(payload, '$.type') sonucunu varchar(32) değerine dönüştürüp tenant_id ile bileşik indeksleyin. SET STATISTICS IO, TIME çıktısındaki logical reads ve actual execution plan'daki Index Seek işlemini, indeks öncesi Table Scan ile karşılaştırın.

NoSQL eğitimi için MongoDB JSON indeksinin doğru çalıştığı nasıl doğrulanır?

find().explain('executionStats') çıktısında winningPlan altında IXSCAN arayın; ardından totalKeysExamined, totalDocsExamined ve nReturned oranlarını kaydedin. tenantId, payload.type ve occurredAt sıralamasıyla oluşturulan bileşik indeks, tenant filtreli ve tarih sıralı sorguda ayrı sort aşamasını ortadan kaldırmalıdır.