B-tree, GIN ve GiST indeksleri: PostgreSQL için pratik rehber

Bir indeks seçerken gerçekte neyi seçiyorsunuz

Çoğu PostgreSQL indeks problemi benzer bir şekilde başlar: bir liste görünümü 1.000 satırdayken hızlı hisseder, sonra 1.000.000 olduğunda yavaşlar. Ya da testte çalışan bir arama kutusu üretimde saniyeler süren duraklamaya dönüşür. Bu olduğunda, “Hangi indeks en iyisi?” diye sormak cazip gelir. Daha iyi bir soru şudur: “Bu ekran veritabanına ne yapmasını söylüyor?”

Aynı tablo farklı ekranlar için farklı indeks türleri gerektirebilir çünkü ekranlar veriyi farklı şekillerde okur. Bir görünüm tek bir duruma göre filtreleyip created_at ile sıralayabilir. Başka bir ekran tam metin araması yapar. Başka biri bir JSON alanının belirli bir anahtarı içerip içermediğini kontrol eder. Başkası haritadaki bir noktaya yakın öğeleri bulur. Bunlar farklı erişim desenleridir; dolayısıyla tek bir indeks türü her yerde kazanmaz.

Bir indeksi seçtiğinizde aslında uygulamanın veriye nasıl eriştiğini seçiyorsunuz. Çoğunlukla tam eşleşmeler, aralıklar ve sıralamalar mı yapıyorsunuz? Belgeler veya diziler içinde arama mı yapıyorsunuz? “Bu konuma yakın olan nedir?” ya da “bu aralıkla örtüşen nedir?” gibi sorular mı soruyorsunuz? Yanıt, B-tree, GIN veya GiST'in hangisinin uygun olduğunu belirler.

B-tree, GIN ve GiST basitçe

İndeks seçmek sütun türünden çok sorguların ne yaptığıyla ilgilidir. PostgreSQL, sütunun "text" veya "json" olup olmadığına göre değil =, <, @> veya @@ gibi operatörlere göre indeks kullanır. Bu yüzden aynı alan farklı ekranlarda farklı indeksler gerektirebilir.

B-tree: sıralı aramalar için hızlı

B-tree varsayılandır ve en yaygın seçenektir. Tam değere göre filtreleme, aralık filtreleme veya belirli bir sırada sonuçlara ihtiyaç olduğunda iyidir.

Tipik örnek bir yönetici listesidir: durumla filtrelenip created_at ile sıralanır. (status, created_at) üzerinde bir B-tree indeksi hem filtreye hem sıralamaya yardımcı olabilir. B-tree ayrıca unique (benzersizlik) için de yaygın araçtır.

GIN: her satırın birçok aranabilir anahtarı olduğu durumlarda hızlı

GIN, bir satırın birçok anahtarı eşleyebileceği “bu satır bu terimi/değeri içeriyor mu?” soruları için tasarlanmıştır. Yaygın örnekler tam metin arama (bir belge kelimeler içerir) ve JSONB/dizi üyeliğidir (JSON bir anahtar/değer içerir).

Müşteri kaydında bir JSONB preferences nesnesi ve bir ekranın {\"newsletter\": true} gibi tercihi olan kullanıcıları filtrelediğini düşünün. Bu GIN tarzı bir aramadır.

GiST: aralıklar, coğrafya ve benzerlik için esnek

GiST, basit sıralamaya uymayan veri tipleri için genel bir çerçevedir. Aralıklar (örtüşme, içerme), geometrik ve coğrafi sorgular (yakın, içinde) ve bazı benzerlik aramaları için doğal bir eşleşmedir.

B-tree vs GIN vs GiST arasında karar verirken, en çok hangi operatörleri kullanan ekranlarınız olduğunu yazın. Doğru indeks genellikle ondan sonra daha net olur.

Yaygın ekranlar için karar tablosu (filtreler, arama, JSON, geo)

Çoğu uygulama sadece birkaç indeks desenine ihtiyaç duyar. Püf nokta ekran davranışını sorgularınızın kullandığı operatörlerle eşleştirmektir.

Farklı uç noktalar olduğunda iki indeks bir arada bulunabilir. Örneğin bir yönetici listesi hızlı sıralama için (tenant_id, created_at) üzerinde B-tree, arama sayfası ise @@ için GIN gerektirebilir. Her iki sorgu deseni de yaygınsa her ikisini de tutun.

Kararsızsanız, önce operatöre bakın. İndeksler veritabanının tablonun büyük bölümünü atlayabilmesini sağladığında yardımcı olur.

Filtreler ve sıralama: B-tree'in genelde kazandığı yerler

Günlük ekranlar için B-tree sıkıcı ama işe yarayan seçimdir. Sorgunuz “bir sütun belirli bir değere eşitse, belki sırala, sonra sayfa 1 göster” gibiyse B-tree genelde ilk denenmesi gereken şeydir.

Eşitlik filtreleri klasik örnektir. status, user_id, account_id, type veya tenant_id gibi sütunlar panolarda ve yönetici panellerinde sürekli görünür. B-tree indeksi eşleşen değerlere doğrudan atlayabilir.

Aralık filtreleri de B-tree ile iyi uyum sağlar. Zaman veya sayısal aralıklarda created_at >= ..., price BETWEEN ..., id > ... gibi ifadeler ordered yapı sayesinde verimlidir. UI’nız “Son 7 gün” veya “$50 ile $100 arası” sunuyorsa B-tree tam olarak istediğinizi yapar.

Sıralama ve sayfalama B-tree'in en çok işinizi görebileceği yerlerdir. İndeks sıralaması ORDER BY ile eşleşirse PostgreSQL sıralamayı önbellekten döndürebilir ve büyük bir kümede bellek içi sıralama yapmak zorunda kalmaz.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Bileşik indeksler basit bir kurala uyar: PostgreSQL yalnızca indeksin baş kısmını verimli kullanabilir. Sol'dan sağa düşünün. (user_id, status, created_at) ile user_id ile filtreleyen (ve isteğe bağlı olarak status) sorgular fayda görür. Sadece status ile filtreleyen bir sorgu genelde fayda görmez.

Kısmi indeksler, ekranınız verinin sadece bir dilimini önemsiyorsa güçlü bir yükseltmedir. Yaygın dilimler “sadece aktif satırlar”, “soft-delete olmayanlar” veya “son etkinlik”tir. Bunlar indeksi küçük ve hızlı tutar.

Yüksek kardinaliteli sütunlar ve ekstra indekslerin maliyeti

Yüksek kardinaliteli sütunlar birçok benzersiz değere sahiptir: user_id, order_id, email veya saniye hassasiyetinde created_at gibi. İndeksler burada parlayabilir çünkü filtre tablonun çok küçük bir kısmını hızla daraltır.

Düşük kardinaliteli sütunlar tersinedir: booleanlar ve küçük enumlar (is_active, status IN ('open','closed'), plan IN ('free','pro')). Bu tür sütunlara indeks genelde hayal kırıklığı yaratır çünkü her değer çok sayıda satıra uyar ve PostgreSQL doğru olarak sıralı taramayı seçebilir.

Bir diğer ince maliyet satırların getirilmesidir. İndeks eşleşen ID'leri hızlı bulsa bile veritabanı geri kalan sütunlar için tabloyu ziyaret etmek zorunda kalabilir. Sorgunuz yalnızca birkaç alana ihtiyaç duyuyorsa covering index (örtücü indeks) yardımcı olabilir, ama bu indeksin de daha büyük ve bakım açısından pahalı olacağı anlamına gelir.

Her ek indeksin bir yazma fiyatı vardır. Insertler her indeks için yazma yapmalıdır. İndekslenmiş sütunları değiştiren updateler de o indeksleri güncellemek zorundadır. “İhtimal için” indeks eklemek tüm uygulamayı yavaşlatabilir, sadece tek bir ekranı değil.

Pratik rehber:

• Yoğun tablolar için 1-2 temel indeksle başlayın; bunları gerçek filtreler ve sıralamalara göre belirleyin.
• WHERE ve ORDER BY içinde kullanılan yüksek kardinaliteli sütunları tercih edin.
• Booleanlar ve küçük enumları indekslerken dikkatli olun; başka seçici bir sütunla birleşmedikçe genelde işe yaramazlar.
• Yeni bir indeks eklemeden önce hangi sorguyu hızlandıracağını net olarak isimlendirebilmelisiniz.

Örnek: assignee_id (yüksek kardinalite) ile filtrelenen destek bileti listesi bir indeksten fayda görürken, yalnızca is_archived = false genelde görmez.

Arama ekranları: tam metin, önekler ve “içerir”

Arama kutuları basit görünür ama kullanıcılar çok şey bekler: birden çok kelime, farklı kelime formları ve makul sıralama. PostgreSQL’de bu genelde tam metin aramadır: tsvector (hazırlanmış metin) saklanır ve kullanıcı girdisi tsquerye (terimlere ayrılmış) dönüştürülüp sorgulanır.

Tam metin arama için GIN yaygın varsayılandır çünkü “bu belge bu terimleri içeriyor mu?” sorusuna karşılık verirken hızlıdır. Dezavantajı yazma tarafında daha ağır olmasıdır: insert ve updateler daha maliyetli olabilir.

GiST de tam metin aramada kullanılabilir. Genelde daha küçük ve güncellenmesi daha ucuzdur, ama okumalar için GIN kadar hızlı olmayabilir. Verileriniz sürekli değişiyorsa (ör. event tarzı tablolar), okuma-yazma dengesi önemli olabilir.

Önek arama tam metin değildir

Önek arama “ile başlıyor” anlamındadır, örneğin e-posta için bir önek araması. Bu tam metin aramanın alanına girmez. Önek desenleri için B-tree indeksi (doğru operator class ile) yardımcı olabilir çünkü string sıralamasıyla uyumludur.

ILIKE '%error%' gibi “içerir” aramalar için B-tree genelde yardımcı olamaz. Bu durumda trigram indeksleme veya farklı bir arama yaklaşımı gündeme gelir.

Kullanıcılar filtre + metin araması istediğinde

Gerçek dünyada ekranlar genelde arama ile filtreleri birlikte kullanır: durum, atanan kişi, tarih aralığı, tenant vb. Pratik bir kurulum genelde şudur:

• tsvector sütunu için GIN (veya bazen GiST) indeksi.
• En seçici filtreler için B-tree indeksleri (account_id, status, created_at gibi).
• Çok fazla indeks oluşturmamaya yönelik katı bir kural; zira fazla indeks yazmaları yavaşlatır.

Örnek: "refund delayed" araması yapan ve status = 'open' ile belirli bir account_id filtreleyen bir destek bileti ekranı. Tam metin alakalı satırları getirir, B-tree ise PostgreSQL'in doğru hesap ve durum aralığını hızla daraltmasına yardımcı olur.

JSONB alanları: GIN mi yoksa hedeflenmiş B-tree mi?

JSONB esneklik sağlar, fakat onu normal bir sütun gibi kullanmak yavaş sorgulara yol açabilir. Temel karar basittir: JSON içinde “her yerde arama” mı yapıyorsunuz yoksa birkaç belirli path üzerinde sık mı filtreliyorsunuz?

metadata @> '{"plan":"pro"}' gibi kapsama sorguları için GIN genelde ilk tercihtir. Bu, belgenin belirli bir şekli içerip içermediğini hızlıca cevaplamak için tasarlanmıştır ve ?, ?|, ?& gibi anahtar varlık kontrollerini de destekler.

Uygulamanız çoğunlukla bir veya iki JSON alanı üzerinde filtre yapıyorsa hedeflenmiş B-tree ifade indeksi daha hızlı ve daha küçük olur. Ayrıca çıkarılan değerler üzerinde sıralama veya sayısal karşılaştırma gerektiğinde işe yarar.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Genel bir kural:

• Kullanıcılar birden çok anahtar, etiket veya iç içe yapıyı arıyorsa GIN kullanın.
• Kullanıcılar belirli yollar üzerinde sık filtre yapıyorsa B-tree ifade indeksleri tercih edin.
• Gerçek ekranlarda görünenleri indeksleyin, her şeyi değil.
• Performans birkaç JSON anahtarına bağlıysa, bu alanları gerçek sütunlara taşımayı düşünün.

Örnek: Bir destek ekranı metadata->>'priority' ile filtreleyip created_at ile sıralıyorsa JSON priority path ve normal created_at sütununu indeksleyin. Kullanıcılar etiketler veya iç içe özellikleri de arıyorsa geniş GIN indeksi eklemeyi değerlendirin.

Geo ve aralık sorguları: GiST'in en uygun olduğu yerler

Geo ve aralık ekranları GiST'in genelde belirgin hale geldiği yerlerdir. GiST “bu şey bununla örtüşüyor mu, içinde mi, ya da buna yakın mı?” sorularını hızlıca cevaplayacak şekilde tasarlanmıştır; basit bir değerin eşitliği değil.

Geo veriler genelde nokta (mağaza konumu), çizgi (rota) veya çokgen (teslimat bölgesi) olur. Yaygın ekranlar “bana yakın mağazalar”, “10 km içindeki işler”, “bu harita kutusundaki öğeleri göster” veya “bu adres hizmet alanımızın içinde mi?” gibi sorgulardır. GiST indeksi (çoğunlukla PostGIS geometry/geography tipleri aracılığıyla) bu mekansal operatörleri hızlandırır ve veritabanının çoğu satırı atlamasını sağlar.

Aralıklar da benzerdir. PostgreSQL daterange ve int4range gibi range tiplerine sahiptir; tipik soru örtüşme üzerinedir: “bu rezervasyon mevcut bir rezervasyonla çakışıyor mu?” veya “bu hafta aktif olan abonelikleri göster.” GiST örtüşme ve içerme operatörlerini verimli destekler, bu yüzden takvim, planlama ve kullanılabilirlik kontrollerinde yaygındır.

Geo-benzeri ekranlarda B-tree yine önem kazanabilir. Birçok sayfa önce tenant, durum veya zaman ile filtreleyip sonra mekansal koşulu uygular ve ardından sıralar. Örneğin: “sadece şirketimin teslimatları, son 7 güne ait, en yakına göre.” GiST mekansal kısmı halleder, ama B-tree seçici filtreler ve sıralama için yardımcı olur.

Adım adım indeks seçimi nasıl yapılır

İndeks seçimi operatörden çok sütun adına bağlıdır. Aynı sütun =, >, LIKE 'prefix%', tam metin arama, JSONB kapsama veya geo mesafe gibi farklı operatörlerle kullanıldığında farklı indekslere ihtiyaç duyabilir.

Sorguyu şu kontrol listesi gibi okuyun: WHERE hangi satırların uygun olduğunu belirler, JOIN tabloların nasıl bağlandığını, ORDER BY çıktı sırasını, LIMIT ise aslında kaç satıra ihtiyacınız olduğunu belirler. En iyi indeks genelde ilk 20 satırı hızlıca bulana yardımcı olandır.

Çoğu uygulama ekranı için işe yarayan basit süreç:

1. Ekranınızın kullandığı tam operatörleri yazın (örnek: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. En seçici filtreye veya varsayılan sıralamaya uyan bir indeksle başlayın. Ekran created_at DESC ile sıralıyorsa oradan başlayın.
3. Bileşik indeks ekleyin yalnızca aynı filtrelerin sıkça birlikte kullanıldığını gördüğünüzde. Eşitlik sütunlarını önce, sonra aralık sütununu, sonra sıralama anahtarını koyun.
4. Ekran hep belirli bir altkümeyle ilgileniyorsa kısmi indeks kullanın; hesaplanan değer sorgulanıyorsa ifade indeksi kullanın (ör. lower(email) için büyük/küçük harf duyarsız aramalar).
5. EXPLAIN ANALYZE ile doğrulayın. Eğer yürütme süresini ve okunan satır sayısını ciddi şekilde azaltıyorsa tutun.

Somut örnek: Bir destek panosu status ile filtreleyip en yeniye göre sıralıyorsa (status, created_at DESC) üzerinde B-tree güçlü bir ilk denemedir. Aynı ekran meta @> '{"vip": true}' gibi bir JSONB bayrağına da filtre uyguluyorsa bu farklı bir operatördür ve genelde ayrı JSON odaklı bir indeks gerektirir.

Zaman kaybettiren yaygın hatalar (ve yazmaları yavaşlatanlar)

Yanlış operatör için “doğru” indeks türünü seçmek tatsız sonuç verir. PostgreSQL bir indeksi yalnızca sorgu o indeksi cevaplayacak şekilde kullanabiliyorsa kullanır. Eğer uygulama ILIKE '%term%' kullanıyorsa, o metin sütununda düz bir B-tree indeksi kullanılmaz ve tablo yine taranır.

Bir diğer tuzak büyük çok sütunlu indeksler oluşturmak “her ihtimale karşı”. Güvenli görünürler ama bakım maliyeti yüksektir ve genelde gerçek sorgu desenleriyle eşleşmezler. Sol baştaki sütunlar filtrede kullanılmıyorsa indeksin geri kalanı yardımcı olmayabilir.

Düşük seçicili sütunlara gereğinden fazla indeks vermek de kolaydır. is_active gibi bir boolean veya birkaç değerli durum için B-tree genelde işe yaramaz; bunun yerine kısmi indeks veya başka kombinasyonlar düşünülmelidir.

JSONB ayrı tuzaklar getirir. Geniş bir GIN indeksi esnek filtreler için harika olabilir, ama birçok JSONB yol kontrolü çıkarılan değerler üzerinde ifade indeksi ile daha hızlı çalışır. Ekranınız hep payload->>'customer_id' ile filtreliyorsa o ifadeyi indekslemek tüm belgeyi indekslemeden daha küçük ve hızlıdır.

Son olarak, her ekstra indeks yazmaları yükler. Sık güncellenen tablolar (biletler veya siparişler gibi) her insert ve update ile tüm indeksleri güncellemek zorundadır.

İndeks eklemeden önce durup kontrol edin:

• İndeks sorgunuzun kullandığı tam operatörü karşılıyor mu?
• Geniş bir çok-sütunlu indeksi bir veya iki odaklı indeksle değiştirebilir misiniz?
• Kısmi indeksle düşük seçicili gürültüyü azaltabilir misiniz?
• JSONB için ifade indeksi ekranı daha iyi karşılar mı?
• Tablo yazma açısından yeterince ağır mı ki indeks maliyeti okumadaki kazançtan fazla olsun?

Bir indeksi eklemeden (veya tutmadan) önce hızlı kontroller

Yeni bir indeks oluşturmadan önce uygulamanın gerçekten ne yaptığını netleştirin. “Olsa iyi olur” indeksi genelde yazmaları yavaşlatan ve depolama maliyetini artıran bir şeye dönüşür.

En yoğun üç ekranınızı (veya API endpointinizi) alın ve tam WHERE ve ORDER BY kalıplarını yazın (yön ve NULL davranışı dahil). Birçok “indeks problemi” aslında “belirsiz sorgu problemi”dir; insanlar B-tree vs GIN vs GiST tartışırken operatörü adlandırmıyor olabilir.

Basit bir kontrol listesi:

• 3 gerçek ekran seçin ve bunların tam WHERE ve ORDER BY kalıplarını listeleyin.
• Operator tipini doğrulayın: eşitlik (=), aralık (>, BETWEEN), önek, içerme, örtüşme veya mesafe.
• Her yaygın ekran deseni için bir indeks seçin, test edin ve yalnızca zaman veya okuma sayısını ölçülebilir şekilde azaltanları tutun.
• Tablo yazma ağırlıklıysa katı olun: ekstra indeksler yazma maliyetini çarpar ve vacuum baskısını artırır.
• Özellik değişikliklerinden sonra yeniden kontrol edin. Yeni bir filtre, yeni varsayılan sıralama veya “başlayan” yerine “içeren” aramaya geçiş eski indeksi geçersiz kılabilir.

Örnek: Bir pano yeni varsayılan sıralama last_activity DESC eklerse, sadece status indeksine sahip olmanız filtreyi hızlı tutabilir ama sıralama şimdi ekstra iş getirebilir.

Örnek: gerçek uygulama ekranlarını doğru indekse eşleme

Karar tablosu, gönderdiğiniz gerçek ekranlara eşlendiğinde yardımcı olur. İşte üç yaygın ekran ve bunlara uygun indeksler.

Uygulamaya uygulamanın pratik yolu UI'dan başlamaktır:

• Sonuçları daraltan her kontrolü listeleyin (filtreler).
• Varsayılan sıralamayı not edin.
• Arama davranışında spesifik olun (tam metin mi, önek mi, içerme mi?).
• "Özel" alanları (JSONB, geo, aralıklar) belirtin.

Sonraki adımlar: indekslemeyi geliştirme sürecinizin bir parçası yapın

İyi indeksler ekranlarınızı takip eder: kullanıcıların tıkladığı filtreler, bekledikleri sıralama ve gerçekten kullandıkları arama kutusu. Geliştirme sırasında indekslemeyi alışkanlık haline getirin, böylece çoğu performans sürprizinden kaçınırsınız.

Tekrarlanabilir tutun: bir ekranın çalıştırdığı 1-3 sorguyu belirleyin, onları karşılayan en küçük indeksi ekleyin, gerçekçi verilerle test edin ve işe yaramayanları kaldırın.

Eğer dahili araç veya müşteri portalı inşa ediyorsanız, indeks ihtiyaçlarını erken planlayın çünkü bu tür uygulamalar genelde daha fazla filtre ve liste ekranı ekleyerek büyür. AppMaster markasını kullanıyorsanız, her ekranın filtre ve sıralama yapılandırmasını somut bir sorgu deseni olarak ele alıp yalnızca bu gerçek tıklamaları destekleyen indeksleri eklemek yardımcı olur.