PostgreSQL JSONB İş Yüklerinde GIN İndeks Darboğazları: pending_list_limit Parametresiyle Kilit Çekişmelerini Minimize Etmek

Giriş

PostgreSQL 15 ortamında çalışan 1.4 TB büyüklüğündeki orders tablosunda, saniyede ortalama 3200 UPDATE işlemi (TPS) alan bir senaryoyu düşünün. Sistemin genel yazma gecikme süresi (latency) 4-6 milisaniye bandında seyrederken, her 40-50 saniyede bir p99 gecikme metriklerinin aniden 850-1200 milisaniye aralığına fırladığına şahit oluyorsunuz. CPU kullanımında anomali yok, disk I/O kuyrukları boş, ağ katmanında paket kaybı sıfır. pg_stat_activity tablosunu incelediğinizde, düzinelerce backend process’in LWLock:Lock durumunda yığıldığını ve bağlantı havuzunu (connection pool) şişirdiğini görüyorsunuz.

Bu darboğazın temel kaynağı, PostgreSQL’in JSONB iş yüklerinde sorgu performansını artırmak için kullanılan GIN (Generalized Inverted Index) indekslerinin mimari bir dezavantajını yamamak üzere kurguladığı fastupdate mekanizmasıdır. Yazma yoğunluklu (write-heavy) sistemlerde, varsayılan yapılandırma değerleriyle bırakılan bir GIN indeksi, uygulamanız için saatli bir bombaya dönüşebilir.

Aşağıdaki analizde, GIN indekslerinin dâhili çalışma prensiplerini, pending_list_limit parametresinin arka plandaki bellek ve disk yönetimini nasıl etkilediğini ve saniyede binlerce transaction alan production ortamlarında bu kilit çekişmelerinin (lock contention) nasıl elimine edileceğini inceleyeceğiz.

İçindekiler

GIN İndeks Anatomisi ve Fast Update Mekanizması
Production Vakası: 4MB Duvarına Çarpmak
Performans ve Trade-off Analizi
Production Ortamları İçin Yapılandırma Checklist’i
Sık Sorulan Sorular
Sonuç

GIN İndeks Anatomisi ve Fast Update Mekanizması

Standart bir B-Tree indeksinde, tablodaki bir satır güncellendiğinde indekste yalnızca bir adet girdi (entry) güncellenir. Maliyet genellikle O(log N) seviyesindedir ve öngörülebilirdir. Ancak GIN, ters yüz edilmiş (inverted) bir indeks yapısıdır. JSONB tipindeki bir kolonda yer alan {"status": "shipped", "tags": ["electronics", "urgent"], "retry_count": 2} dokümanını indekslediğinizde, PostgreSQL dokümanı parçalara ayırır. Her bir anahtar (key), değer (value) ve dizi elemanı (array element) için GIN ağacına ayrı ayrı kayıt atılması gerekir. Tek bir satırın INSERT veya UPDATE işlemi, GIN indeksinde 15-20 farklı lokasyona yazma işlemi yapılması anlamına gelebilir.

PostgreSQL çekirdek geliştiricileri, bu devasa yazma maliyetini hafifletmek için fastupdate mekanizmasını geliştirmiştir (PostgreSQL 8.4’ten beri varsayılan olarak açıktır). Bu mekanizma devredeyken, gelen yeni indeks kayıtları doğrudan asıl GIN B-Tree yapısına yazılmaz. Bunun yerine, bu kayıtlar disk üzerinde yer alan, sırasız (unsorted) ve doğrusal bir liste olan pending_list içerisine eklenir. Bu sayede yazma işlemi sadece son listeye bir ekleme yapmaktan ibaret olur ve milisaniyenin altında tamamlanır.

Ancak bu ertelenmiş maliyetin bir bedeli vardır. pending_list sonsuza kadar büyüyemez. Boyutu, gin_pending_list_limit parametresi (varsayılan 4096 KB / 4MB) ile sınırlıdır. Liste bu sınıra ulaştığında, o anki byte’ı yazmaya çalışan şanssız veritabanı oturumu (backend process), işlemi durdurur ve listeyi temizlemekle görevlendirilir.

Production Vakası: 4MB Duvarına Çarpmak

Bir e-ticaret platformunun sipariş yönetim servisinde (Order State Machine) tam olarak bu senaryoyu yaşadık. Tabloda 45 milyon aktif sipariş satırı ve karmaşık logların tutulduğu JSONB tipinde bir order_state kolonu bulunuyordu. Black Friday yük testleri sırasında saniyede 4500 TPS seviyesine çıkıldığında, veritabanı sunucusunun CPU kullanımı %35 seviyelerinde olmasına rağmen throughput aniden 200 TPS’e kadar düşüp 1-2 saniye sonra tekrar 4500’e toparlanıyordu.

Sorunun tespiti için pg_locks ve pg_stat_activity tablolarını birleştiren aşağıdaki sorguyu kullandık:

SELECT
    pid,
    wait_event_type,
    wait_event,
    state,
    query,
    EXTRACT(epoch FROM (now() - query_start)) AS duration
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND query ILIKE '%UPDATE orders SET order_state%';

Analiz sonucunda, tek bir PID’nin ExclusiveLock alarak beklediğini, diğer yüzlerce PID’nin ise ShareUpdateExclusiveLock veya benzeri kilit kuyruklarında (lock queue) bu PID’yi beklediğini gördük. GIN indeksi üzerindeki bekleyen listeyi (pending list) incelemek için PostgreSQL’in dâhili pageinspect eklentisini kullandık:

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- İndeksin meta verisini okuyarak pending_list boyutunu page (8KB) cinsinden görme
SELECT pending_pages 
FROM gin_metapage_info(get_raw_page('idx_orders_state_gin', 0));

Sonuç tam olarak 512 sayfa (pages) idi. 512 * 8KB = 4096 KB (4MB). Saniyede 4500 güncelleme alan bir sistemde 4MB’lık liste yaklaşık 3 saniyede doluyordu. Listeyi temizleme görevi ise autovacuum process’inden ziyade, 4MB sınırını aşan UPDATE işlemini yapan foreground process’in üzerine kalıyordu. Bu process 4MB’lık sırasız veriyi belleğe (work_mem) alıyor, Red-Black tree mantığıyla sıralıyor ve devasa ana GIN yapısına merge ediyordu. Bu işlem disk hızına bağlı olarak 800ms ile 1200ms arasında sürüyor, bu süre zarfında indekse dokunan tüm yazma işlemleri bloke oluyordu.

Performans ve Trade-off Analizi

Bu darboğazı aşmak için masada üç farklı mimari strateji bulunuyordu. Her birinin gecikme (latency) ve verim (throughput) açısından farklı trade-off’ları mevcuttu.

Strateji 1: fastupdate Parametresini Kapatmak

İlk akla gelen çözüm, ertelenmiş yazma mekanizmasını tamamen devre dışı bırakmaktır. Bu durumda her yazma işlemi, GIN ağacını anlık olarak günceller.

ALTER INDEX idx_orders_state_gin SET (fastupdate = off);

Avantaj: 800ms’lik kilitlenme (spike) tamamen ortadan kalkar. Gecikme süreleri öngörülebilir hale gelir.
Dezavantaj: Ortalama UPDATE maliyeti 4ms’den kalıcı olarak 14ms seviyesine çıkar. GIN ağacının anlık güncellenmesi, Write-Ahead Logging (WAL) trafiğini %45 oranında artırır. Maksimum TPS kapasitesi 4500’den 2800’e düşer.

Strateji 2: Autovacuum Agresifliği Artırmak

Autovacuum process’i, tabloda ölü satır (dead tuple) temizliği yaparken aynı zamanda GIN pending list’i de temizler. Autovacuum’u tablo özelinde sıklaştırmak bir yöntemdir.

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

Avantaj: Foreground process’lerin temizlik yükünü arka plana aktarır.
Dezavantaj: Yüksek TPS altında autovacuum tetiklenme süresi yeterince hızlı olamaz. Veritabanı her 1 dakikada bir autovacuum başlatsa bile, pending list 3 saniyede dolduğu için bu strateji yüksek yazma yüklerinde matematiksel olarak başarısız olur.

Strateji 3: Limit Artırımı ve Asenkron Temizlik (Önerilen)

Production ortamında uyguladığımız asıl çözüm, limitin kapasitesini artırıp, temizlik işlemini kontrolümüz altındaki asenkron bir cron process’ine devretmek oldu.

-- Limiti 4MB'dan 64MB'a çıkar
ALTER INDEX idx_orders_state_gin SET (gin_pending_list_limit = 65536);

-- pg_cron veya uygulama katmanında her 15 saniyede bir çalışacak sorgu:
SELECT gin_clean_pending_list('idx_orders_state_gin');

Avantaj: Limit 64MB’a çıktığı için liste kolay kolay dolmaz. Arka planda çalışan gin_clean_pending_list() fonksiyonu, foreground işlemlerini bloke etmeden listeyi temizler. Ortalama gecikme süresi 4ms’de kalır, TPS kapasitesi korunur.
Dezavantaj: Uygulama mimarisine dışsal bir temizlik döngüsü (cron/worker) eklenmesi gerekir. 64MB’lık verinin merge edilmesi sırasında disk I/O kullanımı anlık olarak yükselir.

Yapılandırma Stratejisi	Ort. Gecikme (p50)	Maks. Gecikme (p99)	Desteklenen TPS (Sınır)	WAL Üretim Oranı
Varsayılan (fastupdate=on, 4MB)	4.2 ms	1150.0 ms	~3200	Düşük / Ani Patlamalı
Fastupdate Kapalı (fastupdate=off)	14.8 ms	18.5 ms	~2800	Çok Yüksek / Sürekli
Limit 64MB + Asenkron Temizlik	4.5 ms	8.2 ms	~6500	Düşük / Kontrollü

Production Ortamları İçin Yapılandırma Checklist’i

Bu metrikleri baz alarak sistemlerinizi yapılandırırken şu adımları izlemenizi öneririm:

Monitoringe gin_clean_pending_list Ekleyin: Bu fonksiyonun çalışma süresini bir metrik olarak toplayın. Eğer fonksiyonun çalışma süresi 500ms’yi aşıyorsa, cron periyodunuzu sıklaştırmanız veya work_mem parametresini (merge işlemi sırasında kullanılan RAM’i artırmak için) tabloya özel optimize etmeniz gerekir.
İndeks Bazlı Limit Belirleyin: gin_pending_list_limit parametresini postgresql.conf seviyesinde global olarak değiştirmek yerine, sadece sorun yaratan indekse özel (ALTER INDEX ile) uygulayın. Global değişiklikler, ufak tabloların indeks belleklerini gereksiz yere şişirebilir.
Fallback Stratejisi (B-Tree’ye Dönüş): Eğer JSONB kolonunda sadece 2-3 belirli anahtar (key) üzerinden sorgu yapıyorsanız, devasa bir GIN indeksi tutmak mimari bir hatadır. Bunun yerine expression index kullanarak ilgili anahtarları çıkartın ve standart B-Tree indeks kullanın. Örnek: CREATE INDEX idx_status ON orders ((order_state->>'status'));. Bu sayede pending list sorunundan tamamen kurtulursunuz.
Sorgu Bağımlılığı: pending_list ne kadar büyükse, GIN indeksini kullanan SELECT sorguları o kadar yavaşlar. Çünkü PostgreSQL indeks taraması yaparken hem asıl B-Tree yapısını hem de o anki sırasız pending_list içindeki tüm kayıtları sırayla taramak (sequential scan) zorundadır. Listeyi 256MB gibi aşırı yüksek değerlere çekmek, okuma (read) performansınızı ciddi şekilde degrade edecektir.

Sık Sorulan Sorular

gin_pending_list_limit verisi RAM’de mi tutulur, veri kaybı riski var mıdır?

Hayır, veri kaybı riski yoktur. pending_list doğrudan diske (indeks dosyasının ayrılmış sayfalarına) yazılır ve bu işlemler WAL (Write-Ahead Log) mekanizması tarafından korunur. Veritabanı çökse bile (crash), kurtarma (recovery) esnasında liste sorunsuz bir şekilde yeniden inşa edilir. Ancak bu sayfalar buffer cache’te yer kapladığı için belleği dolaylı yoldan etkiler.

work_mem parametresini artırmak pending_list merge işlemini hızlandırır mı?

Evet. PostgreSQL pending_list içeriğini asıl GIN ağacına merge etmeden önce sıralamak zorundadır. Bu sıralama işlemi için ayrılan bellek miktarı maintenance_work_mem parametresinden alınır (manuel temizlikte ve autovacuum durumunda). Yetersiz bellek durumunda sıralama diske (temp files) taşar ki bu merge süresini saniyeler seviyesine çıkarır.

PostgreSQL 16 veya 17 sürümlerine geçmek bu sorunu çözer mi?

Kısmen iyileştirir ancak temel mimari sorunu çözmez. PostgreSQL 17 sürümünde GIN indeks oluşturma ve bellek bağlamı (memory context) yönetiminde optimizasyonlar yapılmış olup, bellek sızıntıları ve CPU döngüleri azaltılmıştır. Fakat fastupdate mekanizmasının 4MB’lık sınırda foreground process’i bloke etme davranışı aynen korunmaktadır. Dolayısıyla asenkron temizlik stratejisi yeni sürümlerde de geçerliliğini korur.

Sadece GIN indeksleri mi bu problemden etkilenir, GiST veya SP-GiST indeksleri de benzer mekanizmaya sahip mi?

Bu sorun tamamen GIN indekslerine (ve dâhili yapısında benzer bir pending_list barındıran RUM eklentisine) özgüdür. GiST, SP-GiST veya standart B-Tree indekslerinde fastupdate veya pending_list konsepti bulunmaz, her kayıt anlık olarak ana ağaç yapısına entegre edilir.

Sonuç

PostgreSQL’in GIN indekslerindeki fastupdate mekanizması, düşük ve orta seviyeli iş yüklerinde hayat kurtaran harika bir özelliktir. Ancak sisteminiz saniyede binlerce yazma işlemi alan bir e-ticaret, lojistik veya finans uygulamasıysa, varsayılan 4MB sınırı sisteminizin en zayıf halkası haline gelir. P99 metriklerinizde periyodik anormallikler görüyorsanız, çözüm donanımı büyütmek (vertical scaling) değil, veritabanı motorunun kilitlenme dinamiklerini kontrol altına almaktır.

Uygulamanızın gecikme toleransına bağlı olarak; ya fastupdate parametresini kapatarak sabit ama yüksek bir gecikme profilini kabul etmeli, ya da gin_pending_list_limit değerini 64MB bandına çekip gin_clean_pending_list() fonksiyonu ile asenkron temizlik mimarisini kurgulamalısınız. Mimari kararlar her zaman trade-off’lardan ibarettir; önemli olan, kendi sisteminizin limitasyonlarını ve iş gereksinimlerini sayılarla analiz edebilmektir.