- PostgreSQL indeksleri, veri erişim hızını artırmak için kullanılan temel yapılardır; diskten okunması gereken veri miktarını azaltarak sorgu performansını iyileştirir
- İndeksler Btree, Hash, BRIN, GIN, GiST, SP-GiST gibi çeşitli biçimlerde sunulur ve her biri farklı veri özellikleri ile sorgu kalıplarına göre optimize edilmiştir
- İndeksler; disk alanı, yazma performansı, sorgu planlayıcısının karmaşıklığı, bellek kullanımı gibi çeşitli maliyetler de getirir
- Kısmi indeks, çok sütunlu indeks, covering index, ifade indeksi gibi gelişmiş özelliklerle belirli durumlarda verimlilik en üst düzeye çıkarılabilir
- Uygun indeks seçimi ve yönetiminin, PostgreSQL performans optimizasyonunun temel unsurlarından biri olduğu vurgulanır
İndekslerin temel kavramı
- İndeks, veritabanının diskten okuduğu veri miktarını azaltarak sorgu hızını artıran bir yapıdır
- Birincil anahtar, benzersiz anahtar ve dışlayıcı kısıtlar gibi yapılar da indeksler aracılığıyla uygulanır
- Sorgu sonucu tüm tablonun %15~20'sinden az olduğunda indeksler etkilidir; bunun üzerindeyse sıralı tarama daha verimli olabilir
- PostgreSQL varsayılan olarak 6 tür indeks sunar ve eklentilerle daha fazla tür kullanılabilir
- Her indeks, anahtar değerleri ile ilgili veri konumunu (TID) birbirine bağlar
Diskte saklanan veri yapısı
- PostgreSQL tabloları heap dosyaları olarak saklanır ve 8KB sayfalardan oluşur
- Her satır (tuple) belirli bir sıra olmadan saklanır; iç adresi ctid (current tuple id) ile tanımlanır
- Örnek:
(0,1) sayfa 0'daki ilk tuple anlamına gelir
- İndeksler bu heap konumlarını (ctid) ağaç yapısıyla bağlayarak hızlı aramayı destekler
İndekslerin veri erişimini hızlandırma biçimi
- İndeks olmadığında PostgreSQL, tüm sayfaları okuyan bir sıralı tarama gerçekleştirir
- Örnek sorguda
name='Ronaldo' aranırken 6272 sayfa okunur ve 265ms sürer
- İndeks eklendiğinde işlem Index Scan'e dönüşür; yalnızca 4 sayfa okunur ve 0.077ms'de tamamlanır
- İndeks, değer ile ctid arasında eşleme kurarak yalnızca gerekli satırları hızlıca bulur
- İndeks dosyası boyutu tablo boyutuna yakın olabilir (ör. 30MB tablo → 30MB indeks)
İndekslerin maliyet unsurları
- İndeksler performans artışının yanında çeşitli yükler de getirir
Disk alanı
- İndeksler ayrı depolama alanı kullanır ve tablodan daha büyük olabilir
- Yedekleme, replikasyon ve felaket kurtarma sırasında ek maliyet oluşturur
- Kısmi indeksler, çok sütunlu indeksler, BRIN gibi yöntemlerle alan verimliliği artırılabilir
Yazma işlemleri
UPDATE, INSERT, DELETE sırasında indeksli sütunlar değişirse indeks güncelleme ek yükü oluşur
Sorgu planlayıcısı
- İndeks sayısı arttıkça, planlayıcının değerlendirmesi gereken seçenekler çoğalır ve sorgu planı oluşturma süresi uzar
Bellek kullanımı
- İndeks sayfaları shared buffer içine yüklenip önbelleğe alınır; indeks sayısı arttıkça bellek yükü de artar
- btree düğüm boyutu sınırı nedeniyle sütun büyüdükçe ağacın derinliği artar
- Sıralama, çok sütunlu tarama, vacuum, reindex gibi işlemlerde de ek work memory kullanılır
Başlıca indeks türleri
Btree
- PostgreSQL'in varsayılan indeks yapısıdır ve çoğu DBMS'te kullanılan genel amaçlı indeks türüdür
- O(log n) zaman karmaşıklığıyla hızlı arama sağlar
- Tüm yaprak düğümlerin aynı derinlikte olduğu dengeli ağaç yapısına sahiptir
- ORDER BY, JOIN işlemlerinde avantaj sağlar ve birincil anahtar / benzersiz anahtar kısıtları için kullanılır
- İç düğümler alt düğüm işaretçilerini, yaprak düğümler ise anahtar ve heap işaretçilerini saklar
- Sol ve sağ düğüm işaretçileri sayesinde çift yönlü gezinme mümkündür
Birden fazla indeks kullanımı
- PostgreSQL, birden fazla indeksi bitmap AND/OR işlemleriyle birleştirerek bileşik koşulları işler
- Örnek:
age=30 AND login_count=100 koşulunda iki indeksin bitmap'i birleştirilir
Çok sütunlu indeksler
- Birden fazla sütun tek bir indeks altında birleştirilerek alan tasarrufu ve hız artışı sağlanabilir
- Ancak sütun sırası önemlidir; yalnızca soldan başlayan eşleşen koşullar indeksi kullanabilir
Kısmi indeksler
- Koşul ifadesi kullanılarak yalnızca belirli satırlar indekslenir
- İndeks boyutunu küçültür, RAM'e sığma olasılığını artırır ve sorgu hızını iyileştirir
- Örnek:
create index on rules(status) where status='enabled';
- Değer dağılımı dengesiz olduğunda faydalıdır (
status <> 'TODO' gibi)
Covering index
- Sorgunun ihtiyaç duyduğu tüm sütunlar indeks içinde yer alıyorsa, heap erişimi olmadan sonuç döndürülebilir (index-only scan)
create index abc_cov_idx on bar(a, b) including c;- Çok sütunlu indekslere göre alan açısından daha verimlidir
İfade indeksleri
- Sütun değerinin kendisi yerine fonksiyon veya ifade sonucu indekslenir
- Örnek:
CREATE INDEX idx_lower_name ON customers (lower(name));
LOWER(name) gibi dönüştürülmüş değerlerle arama yapılırken kullanışlıdır- Yalnızca immutable fonksiyonlar kullanılabilir
Hash
- Hash map yapısı tabanlı bir indeks olup, uzun dizeler veya UUID gibi durumlarda alan açısından verimlidir
- 32 bit hash kodu saklayarak boyutu azaltır
- Yalnızca eşitlik karşılaştırması (
=) destekler; sıralama veya çok sütunlu indeks mümkün değildir
- Hash dağılımı dengeliyse Btree'den daha hızlı okuma performansı sağlayabilir
- Resmî belgelere göre hash indeksleri, bucket sayfalarına doğrudan erişim sayesinde büyük tablolarda I/O'yu azaltır
BRIN (Block Range Index)
- Her blok aralığı için yalnızca minimum ve maksimum değerleri saklayan bir indeks türüdür
- Son derece sıkıştırılmış ve cache dostudur
- Büyük ölçekli, append-only, zaman serisi verileri için uygundur
- Satırlar sık güncelleniyorsa, MVCC kaynaklı yinelenen saklama nedeniyle verim düşebilir
pages_per_range ayarıyla doğruluk ve boyut arasındaki trade-off düzenlenebilir
GIN (Generalized Inverted Index)
- Bileşik veri aramaları için uygun bir indeks türüdür
- Metin, dizi, JSONB gibi yapılarda belirli öğelerin aranmasını destekler
- Veri türüne özel strateji sınıfları (opclass) kullanır
- JSON için JSONB sütunu, metin içinse tsvector veya pg_trgm eklentisi ile birlikte kullanılması önerilir
GiST & SP-GiST
- Genelleştirilmiş arama ağacı (GiST) ve uzay bölmeli ağaç (SP-GiST), belirli veri türleri için indeks uygulama çerçeveleridir
- GiST dengeli ağaç, SP-GiST ise dengesiz yapı desteği sunar
- Coğrafi veri, inet, aralık, metin vektörü gibi alanlarda kullanılır
- GIN daha hızlı sorgulama, GiST ise daha düşük kurulum ve bakım maliyeti sunar
- Tam metin araması yapılırken ihtiyaçlara göre iki yaklaşımdan biri seçilebilir
Sonuç
- İndeksler, PostgreSQL performans optimizasyonunun merkezindedir; okuma hızındaki artış ile yazma ve depolama maliyetleri arasında denge kurmak önemlidir
- Veri özellikleri ve sorgu kalıplarına uygun indeks türü seçildiğinde hızlı ve verimli bir veritabanı işletimi mümkündür
- Doğru indeks tasarımı, büyük ölçekli sistemlerde ölçeklenebilirlik ve kararlılığı sağlamak için vazgeçilmezdir
1 yorum
Hacker News yorumları
PostgreSQL resmi dokümantasyonu gerçekten çok iyi yazılmış ve okuması da keyifli, o yüzden paylaşıyorum
PostgreSQL Indexes giriş dokümanı
Çok sütunlu indeks bölümü benim öğrendiğim yöntemle neredeyse aynı
Ama bunun en güncel PostgreSQL sürümlerinde hâlâ böyle olup olmadığını merak etmiştim
Eskiden üçüncü örneğe benzer bir sorguda bitmap index scan kullanıldığını görmüştüm; o zamandan beri mevcut "yerleşik doğruları" yeniden düşünmeye başladım
Bu arada indeksler konusunda Use The Index, Luke sitesinin ve kitabının tüm ekibin okumasına değer birer klasik kaynak olduğunu düşünüyorum
Önceki sürümlerde de bu mümkündü, ama tüm indeksi taramak gerektiği için verimsizdi
İlgili video: YouTube bağlantısı
PostgreSQL'in incremental view maintenance için yerleşik destek sunması iyi olurdu diye düşünüyorum
Bu, indeksler gibi temel veri değiştiğinde otomatik güncellenen, ancak belirli bir görünümle sınırlı olmayıp herhangi bir görünüme uygulanabilen bir kavram
Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL gibi ilgili birçok proje var
PostgreSQL tarafında yakın zamanda pg_ivm adlı bir eklenti bu problemi ele almaya başladı
B-tree vs Hash indeksleri tartışması ilginç
Birçok kişi ID sütunlarında hash'in daha iyi olduğunu düşünür, ama pratikte varsayılan B-tree daha verimlidir
Özellikle neredeyse sıralı değer eklemelerinde ağaç yapısı daha avantajlıdır
Ancak bu kez bahsedilen blog yazısında, kıyaslamalarda hash'in kazandığı söyleniyor
Bu yazının zamanlaması iyiydi
Çok sütunlu indekslerdeki leading column kuralı hep kafa karıştırıcıydı, ama bitmap index scan sayesinde eskisi kadar kritik olmaktan çıktı
PostgreSQL 18'in skip scan özelliği mevcut kabulleri ciddi biçimde değiştiriyor; bu yüzden eski sürümler üzerinden öğrenenlerin zihinsel modelini güncellemesi gerekiyor
PostgreSQL için gerçekten harika bir kaynak olduğunu düşünüyorum
B-tree indeksleri konusunda da uzun zamandır Use The Index, Luke kaynağına sık sık başvuruyorum
Bence mutlaka okunmalı
Sadece basit bir giriş yazısının ötesine geçiyor; derinlikli ama iç yapıları ele almadığı sürece yeterince kolay okunuyor
Bu basit ve mütevazı yazım tarzını seviyorum
Bilgiyi doğrudan aktaran yaklaşım hoşuma gidiyor