Veritabanı Index Yapılarının Anatomisi: Sorgu Optimizasyonu Temelleri

Veritabanı Performansının Gizli Kahramanları: Index Yapıları

Veritabanı sorgularının yavaşlığı, pek çoğumuzun kariyerinde en az bir kere karşılaştığı bir sorun. Bazen bir raporun saatlerce sürmesi, bazen de bir API endpoint’inin kullanıcıyı bekletmesi şeklinde kendini gösteriyor. Bu performans sorunlarının kökeninde yatan en önemli faktörlerden biri, doğru index yapılarının kullanılmaması veya yanlış yapılandırılması. Bu yazıda, PostgreSQL’de en sık kullanılan index türlerini (B-tree, GIN, BRIN) ele alacak, ne zaman hangisini tercih etmemiz gerektiğini somut örneklerle inceleyeceğim. 20 yıllık saha tecrübemde gördüm ki, index yapısını doğru anlamak, sorgu optimizasyonunun temelini oluşturuyor.

Sorgu optimizasyonu denince akla ilk gelen şeylerden biri EXPLAIN ANALYZE çıktısını okumak olsa da, bu çıktının anlamlı olabilmesi için altta yatan veri yapısını bilmek şart. Bir veritabanı index’i, aslında bir kitap indeksi gibidir; aradığınız bilgiyi sayfa sayfa taramak yerine, doğrudan ilgili bölüme atlamanızı sağlar. Ancak her indekstürünün kendine göre avantajları ve dezavantajları var. Hangi veriye, hangi sıklıkla eriştiğinizi bilmeden yapılan index seçimleri, performansı artırmak yerine düşürebilir, hatta disk alanını gereksiz yere tüketebilir.

B-tree Index: Her Durumun Kurtarıcısı

B-tree (Balanced Tree), günümüz veritabanlarında en yaygın kullanılan index yapısıdır. Eşitlik (=), karşılaştırma (>, <, >=, <=) gibi operatörlerle yapılan sorgularda mükemmel performans sunar. Veri, sıralı bir şekilde ağaç yapısında saklanır. Bu sayede, arama işlemleri logaritmik zaman karmaşıklığına (O(log n)) sahip olur. Yani veri miktarı artsa bile arama süresi çok yavaş artar. Bir sorguda WHERE id = 123 gibi bir koşul olduğunda, B-tree index’i ile doğrudan ilgili veriye ulaşabiliriz.

Bir üretim ERP sisteminde, sipariş tablosundaki order_date sütununa B-tree index eklemek, belirli bir tarih aralığındaki siparişleri sorgularken (WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31') performansı dramatik şekilde artırır. Eğer bu index olmasaydı, veritabanı tüm tabloyu taramak zorunda kalırdı. Bu durum, milyonlarca satırı olan tablolarda kabul edilemez yavaşlıklara yol açardı. Örneğin, milyonlarca satırlık bir sipariş tablosunda, order_date üzerinde index olmadan yapılan bir tarih aralığı sorgusu çok yavaş kalırken, doğru yapılandırılmış bir B-tree index ile bu süre çok daha kısalır. Bu, kullanıcı deneyimi açısından devasa bir fark yaratır.

-- B-tree index oluşturma örneği
CREATE INDEX idx_orders_order_date ON orders (order_date);

-- Sorgu örneği
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

B-tree index’leri, sadece tek sütun için değil, birden çok sütun için de oluşturulabilir (composite index). Bu, WHERE column1 = 'A' AND column2 > 100 gibi sorgularda daha da etkilidir. Ancak burada sütunların sırası önemlidir. Genellikle daha seçici (daha çok farklı değer içeren) sütunlar başa konulmalıdır.

Hash ve GiST Index: Özel Durumların Araçları

B-tree ile GIN/BRIN arasında, daha az konuşulan ama yeri geldiğinde kritik iki index türü daha var: Hash ve GiST. Hash index, anahtarı bir hash fonksiyonundan geçirip değerin diskteki konumunu doğrudan işaret eder. Bu yapısı gereği yalnızca eşitlik (=) sorgularında, hatta bazen B-tree’den de hızlı çalışır; ama sıralama ve aralık sorgularını hiç desteklemez, çünkü hash veriyi sıralı tutmaz. Bir products tablosunda product_code üzerinde tam eşleşme dışında bir şey yapmıyorsanız Hash mantıklı olabilir, ancak LIKE 'XYZ%' veya > 'ABC' türü bir sorgu girdiği an işe yaramaz hale gelir. Sahada genel kural olarak B-tree’yi tercih ediyorum; hem eşitlik hem aralık desteklediği için esnekliği Hash’in marjinal hız avantajına çoğu zaman ağır basıyor.

-- Hash index (yalnızca eşitlik sorguları için)
CREATE INDEX idx_products_code_hash ON products USING HASH (product_code);

SELECT * FROM products WHERE product_code = 'XYZ123';

GiST (Generalized Search Tree) ise farklı veri tipleri için genel bir arama yapısı sunar; özellikle geometrik veriler, coğrafi konum ve hiyerarşik veriler için biçilmiş kaftandır. PostGIS gibi extension’lar GiST üzerine kuruludur. Bir CBS (coğrafi bilgi sistemi) uygulamasında belirli bir koordinatın çevresindeki noktaları bulmak gerektiğinde, coordinates sütununa eklenen bir GiST index, ST_DWithin gibi fonksiyonlarla bu sorguyu saniyenin altına indirir. GiST, B-tree’ye göre daha fazla disk alanı kaplayabilir ve yazma maliyeti daha yüksektir; verimliliği veri tipine ve index yapılandırmasına bağlıdır.

-- PostGIS ile GiST index
CREATE INDEX idx_locations_coordinates ON locations USING GIST (coordinates);

SELECT name FROM locations
WHERE ST_DWithin(coordinates, ST_MakePoint(longitude, latitude), radius_in_meters);

GIN Index: Metin Arama ve JSON Verileri İçin İdeal

GIN (Generalized Inverted Index), özellikle metin arama (tsvector, tsquery) ve JSONB gibi yapısal olmayan veya karmaşık veri tipleriyle çalışırken tercih edilir. GIN index, her bir değere karşılık gelen satırların bir listesini tutar. Örneğin, bir blog yazısının içeriğinde geçen her kelime için, o kelimenin hangi yazılarda geçtiğini gösteren bir index girdisi oluşturulur. Bu yapı, tam metin arama sorgularını çok hızlı hale getirir.

Bir e-ticaret sitesinin ürün açıklamaları üzerinde yapılan arama sorgularında GIN index kritik rol oynar. Kullanıcı “mavi kot pantolon” diye aradığında, veritabanı GIN index’i sayesinde ilgili ürünleri saniyeler içinde bulabilir. Eğer GIN index kullanılmasaydı, her arama sorgusu tüm ürün açıklamalarını taramak zorunda kalırdı ki bu da milyonlarca ürün için inanılmaz yavaş bir işlem olurdu. Kendi geliştirdiğim bir finansal analiz aracında, kullanıcıların çeşitli finansal metin girdilerini analiz ederken, jsonb sütunları üzerinde GIN index kullanarak, karmaşık sorguları çok daha hızlı hale getirdim.

PostgreSQL’de GIN index oluşturmak için genellikle gin_trgm_ops gibi operatör sınıfları kullanılır. Bu operatörler, metinler arasındaki benzerlikleri veya tam eşleşmeleri bulmak için optimize edilmiştir.

-- GIN index oluşturma örneği (PostgreSQL 12+)
CREATE INDEX idx_products_description_gin ON products USING GIN (to_tsvector('english', description));

-- Tam metin arama sorgusu
SELECT * FROM products WHERE to_tsvector('english', description) @@ to_tsquery('english', 'mavi & kot & pantolon');

JSONB verileriyle çalışırken de GIN index çok faydalıdır. Örneğin, bir sipariş detayları tablosunda items adında bir jsonb sütunu varsa ve bu sütunda ürün ID’lerini veya miktarlarını sorguluyorsanız, GIN index bu sorguları hızlandıracaktır.

Üretim ortamında tam metin aramayı kalıcı hale getirmenin en sağlam yolu, tsvector’ı ayrı bir sütunda materyalize edip bir trigger ile otomatik güncel tutmaktır. Bir CRM uygulamasında müşteri yorumlarını sakladığımız comments tablosunda bunu şöyle kurdum: yorum metninden türeyen bir comment_tsv sütunu, her INSERT/UPDATE’te kendini güncelleyen bir trigger ve bu sütun üzerinde bir GIN index. Bu sayede her sorguda to_tsvector yeniden hesaplanmıyor, arama doğrudan index üzerinden çalışıyor.

-- tsvector sütunu + otomatik güncelleme trigger'ı + GIN index
ALTER TABLE comments ADD COLUMN comment_tsv tsvector;
UPDATE comments SET comment_tsv = to_tsvector('turkish', comment_text);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON comments FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(comment_tsv, 'pg_catalog.simple', comment_text);

CREATE INDEX idx_comments_tsv ON comments USING GIN (comment_tsv);

SELECT * FROM comments WHERE comment_tsv @@ to_tsquery('turkish', 'ürün & uygun');

BRIN Index: Büyük ve Sıralı Veriler İçin Hafif Bir Seçenek

BRIN (Block Range Index), B-tree ve GIN index’lerine göre daha az yer kaplayan, ancak belirli koşullar altında oldukça etkili olabilen bir index türüdür. BRIN index, veri bloklarının aralıklarını index’ler. Yani, bir index girdisi, belirli bir veri bloğu aralığındaki değerlerin minimum ve maksimum değerlerini saklar. Bu index, verinin fiziksel olarak sıralı olduğu durumlarda en iyi performansı gösterir. Örneğin, zaman serisi verileri veya coğrafi konum verileri gibi doğal olarak sıralı veya gruplanmış verilerde etkilidir.

Bir IoT platformunda toplanan sensör verilerini düşünelim. Bu veriler genellikle zaman damgalarına göre sıralı olarak diske yazılır. Bu durumda, timestamp sütununa eklenen bir BRIN index, belirli bir zaman aralığındaki verileri sorgulamak için B-tree’ye göre çok daha az disk alanı kullanır ve benzer performans sunabilir. Örneğin, 1 milyar satırlık bir sensör veri tablosunda, timestamp sütununa B-tree index eklemek yüzlerce megabayt yer kaplayabilirken, BRIN index sadece birkaç megabayt yer kaplayabilir. Bu, özellikle depolama maliyetlerinin yüksek olduğu ortamlarda önemli bir avantajdır.

-- BRIN index oluşturma örneği
CREATE INDEX idx_sensor_data_timestamp_brin ON sensor_data USING BRIN (timestamp);

-- Sorgu örneği (zaman aralığı)
SELECT * FROM sensor_data WHERE timestamp BETWEEN '2026-05-21 00:00:00' AND '2026-05-21 23:59:59';

Ancak, veriler fiziksel olarak sıralı değilse, BRIN index’in etkinliği büyük ölçüde azalır. Örneğin, veriler sık sık eklenip siliniyor ve sıralı bir şekilde tutulmuyorsa, aynı veri bloğundaki değerlerin minimum ve maksimum değerleri birbirine çok yakın olmayacaktır. Bu da index’in arama sırasında daha fazla veri bloğunu taramasına neden olur. Bu nedenle, BRIN index kullanmadan önce verinin fiziksel sıralaması hakkında bilgi sahibi olmak önemlidir.

Index Seçiminde Trade-off’lar ve Dikkat Edilmesi Gerekenler

Index seçimi, sadece performans artışıyla ilgili değildir; aynı zamanda disk alanı kullanımı, veri ekleme/güncelleme/silme (DML) hızları ve bakım maliyetleri gibi faktörleri de içerir. Her index, veritabanına ek bir yük getirir.

Bir kurumsal yazılım geliştirme projesinde, ilk başta her sorguya bir index ekleme eğilimi olabiliyor. Ancak zamanla fark ediliyor ki, bu kadar çok index, veri girişlerini inanılmaz yavaşlatıyor. Örneğin, bir kullanıcıyı kaydederken birden fazla index’in güncellenmesi, işlem süresini gözle görülür şekilde uzatabiliyor. Bu noktada, hangi index’lerin gerçekten gerekli olduğunu belirlemek için pg_stat_user_indexes gibi sistem tablolarını düzenli olarak kontrol etmek ve kullanılmayan index’leri kaldırmak kritiktir.

Ayrıca, sadece sorgu performansını değil, uygulamanın genel iş akışını da göz önünde bulundurmak gerekir. Bir ERP sisteminde, raporlama sorguları için optimize edilmiş index’ler, veri giriş ekranlarının performansını olumsuz etkileyebilir. Bu nedenle, en iyi yaklaşım, hem okuma (SELECT) hem de yazma (INSERT, UPDATE, DELETE) operasyonlarını dengeli bir şekilde ele almaktır. EXPLAIN ANALYZE çıktısını sadece sorgu bazında değil, uygulamanın farklı senaryoları altında da analiz etmek önemlidir.

İleri Seviye Index Türleri: Partial, Expression ve Covering

Temel index türleri (B-tree/GIN/BRIN) çoğu işi görse de, gerçek performans kazançları çoğu zaman bu üç ileri tekniği doğru yerde kullanmaktan geliyor. Hepsi PostgreSQL’in standart araçları; ama az bilinir, çünkü bir sorunla yüzleşmeden ihtiyaç hissedilmez.

Partial index, tablonun yalnızca belirli bir alt kümesini index’ler. Hem index boyutunu küçültür hem yazma maliyetini düşürür. Bir müşteri projesinde iptal edilen siparişleri nadiren sorguluyorduk; milyonlarca satırlık sipariş tablosunda status = 'cancelled' koşullu sorgular yavaştı, ama iptal kayıtları tablonun sadece %1’iydi. Tüm tabloyu index’lemek yerine yalnızca o alt küme için bir partial index oluşturduk:

CREATE INDEX idx_orders_cancelled ON orders (order_id) WHERE status = 'cancelled';

Bu index yalnızca iptal siparişlerinin order_id’lerini içerdiği için çok daha küçüktü ve ilgili sorguları belirgin şekilde hızlandırdı.

Expression index, sütunun kendisi yerine sütun üzerinde çalışan bir fonksiyon/ifadenin sonucunu index’ler. Az önceki to_tsvector buna bir örnek; bir diğeri büyük/küçük harf duyarsız aramadır. Bir kullanıcı tablosunda sık sık WHERE lower(username) = 'admin' çalıştırıyorsanız, lower(username) ifadesi index’lenmeden bu sorgu her seferinde tam tarama yapar:

CREATE INDEX idx_users_lower_username ON users (lower(username));
SELECT * FROM users WHERE lower(username) = 'admin';

Covering index (PostgreSQL 11+ ile INCLUDE), sorgunun ana tabloya hiç dokunmadan yalnızca index üzerinden tamamlanmasını sağlar. Bir finansal raporlama aracında belirli bir hesap ve tarih aralığı için işlem detaylarını çekiyordum; account_id ve transaction_date üzerinde zaten bir B-tree index vardı, ama sorgu açıklamayı da getirdiği için her satırda heap’e gidiliyordu. Açıklamayı INCLUDE kısmına alarak heap erişimini sıfırladım:

CREATE INDEX idx_transactions_account_date ON transactions (account_id, transaction_date) INCLUDE (description);

Composite Index’te Sütun Sırası: Bir Saha Vakası

İleri tekniklerin teorisini bilmek yetmiyor; asıl öğretici olan saha vakaları. Geçtiğimiz aylarda bir üretim ERP’sinde, operatör ekranındaki “bekleyen üretim emirleri” listesi sabah vardiyasının başında kabul edilemez ölçüde yavaşlıyordu. Sorgu production_orders tablosunu shift_id, status = 'pending' ve bir planned_start_date aralığına göre filtreliyordu. EXPLAIN ANALYZE çıktısında Seq Scan vardı; shift_id ve planned_start_date üzerinde ayrı index’ler mevcuttu ama status üzerinde yoktu.

Kök neden, status sütununun düşük kardinalitesiydi: tablonun %80’i completed, sadece %10’u pending durumundaydı. Planlayıcı, tek başına pending üzerinden index taramasını tüm tabloyu taramaktan daha pahalı buluyordu. Ama shift_id ve planned_start_date ile birleştirildiğinde status filtresi çok seçici hale geliyordu. Çözüm, düşük kardinaliteli sütunu composite index’in en sonuna koymak oldu:

CREATE INDEX idx_production_orders_shift_date_status
ON public.production_orders (shift_id, planned_start_date, status);

Bu index’ten sonra plan Index Scan’e döndü ve sorgu süresi anında çok kısa bir düzeye indi. Çıkarılan ders net: tek başına işe yaramayan düşük kardinaliteli bir sütun, yüksek kardinaliteli sütunların ardına eklendiğinde composite index’in son filtreleme adımını index üzerinden yaptırarak çok değerli olabilir.

Sorgu Optimizasyonu İçin İleri Seviye İpuçları

Index yapılarının temellerini anladıktan sonra, sorgu optimizasyonu için daha ileri adımlar atabiliriz. Bunlardan biri, PostgreSQL’in sorgu planlayıcısının davranışını anlamak ve gerektiğinde ipuçları vermektir. ANALYZE komutu, istatistikleri güncelleyerek planlayıcının daha iyi kararlar almasına yardımcı olur.

pg_stat_statements modülü, en çok kullanılan ve en yavaş sorguları belirlemek için harika bir kaynaktır. Bu modülü etkinleştirerek, hangi sorguların optimizasyona en çok ihtiyaç duyduğunu görebilirsiniz. Bu, kaynaklarınızı en çok fayda sağlayacak alanlara odaklamanızı sağlar. Örneğin, bir finansal hesaplayıcı uygulamasında, en çok kullanılan birkaç sorguyu belirleyip bunları optimize etmek, uygulamanın genel performansını belirgin şekilde artırabilir.

Unutmamak gerekir ki, indexler sihirli değnek değildir. Bazen sorunun kökeni index yapısında değil, veritabanı tasarımı, kötü yazılmış SQL sorguları veya yetersiz donanım kaynaklarında olabilir. Bu nedenle, kapsamlı bir analiz yapmak ve sorunun tek bir nedene indirgenemeyeceğini kabul etmek önemlidir. Kendi yaptığım bir yan ürün olan Android spam engelleyici uygulamamda karşılaştığım performans sorunlarını çözerken, index optimizasyonu sadece bir parçaydı; asıl iyileştirmeyi veri modelini yeniden tasarlayarak elde ettim.

Otomatik Index Araçları Neden Yetmez: Manuel Doğrulama Akışım

Son yıllarda otomatik index öneri araçları (advisor’lar) yaygınlaştı ve devasa sorgu loglarını dakikalar içinde tarayıp aday index’ler sunabiliyorlar. Bunları bir başlangıç noktası olarak değerli buluyorum; ama nihai karar olarak asla. Çünkü bu araçlar istatistiksel modellere ve geçmiş sorgu paternlerine dayanır; üretimdeki anlık veri dağılımını, geçici yük patlamalarını ve uygulamaya özel iş mantığını göremezler. Klasik örnek: bir araç (status, order_date) yerine iki ayrı index önerir, çünkü ORDER BY içindeki sıralama yolunu bir anahtar olarak değerlendiremez. Ya da %80’i tek değere sahip bir sütuna index önerir; bu index neredeyse tüm tabloyu işaret ettiği için sorguyu yavaşlatır. Bir müşteride tam bu yüzden bir öneriyi geri aldığımızda sorgu kayda değer şekilde hızlanmıştı. Deneyimlerime göre otomatik öneriyle gelen index çoğu zaman ölçülü bir kazanç verirken, sorgunun filtreleme ve sıralama yollarını elle analiz edip kurduğum composite index çok daha yüksek bir kazanç sağlayabiliyor.

Bu yüzden her öneriyi şu yedi adımlık akıştan geçiriyorum:

1. Başlangıç noktası olarak al: Aracın önerisini bir “ipucu” sayarım, doğrudan üretime almam.
2. Sorgu planını incele: Önerilen index’in ilgili sorgunun EXPLAIN ANALYZE çıktısını nasıl değiştirdiğine bakar, index’in gerçekten kullanılıp kullanılmadığını ve maliyeti ne kadar düşürdüğünü doğrularım.
3. Veri dağılımını anla: pg_stats veya özel sorgularla index önerilen sütunun kardinalitesini ölçerim; yüksek mi, yoksa düşük kardinaliteli ama sık mı filtreleniyor?
4. Uygulama bağlamını gözet: Sorgu hangi iş akışının parçası, ne sıklıkla çalışıyor, genel performans hedefiyle nasıl örtüşüyor?
5. Trade-off’ları değerlendir: Okuma kazancını, yazma maliyeti ve disk alanı etkisiyle birlikte tartarım.
6. Gerçek dünya testi yap: Mümkünse aynı veri hacmiyle staging ortamında ölçer, olumsuz etki görürsem geri alırım.
7. İteratif optimize et: Tek seferlik bir iş değil; sistem büyüdükçe ve sorgu paternleri evrildikçe index’leri yeniden gözden geçiririm.

Otomasyonun sağladığı hızı takdir ediyorum, ama veritabanı optimizasyonu hâlâ bir muhakeme işi: nihai karar her zaman sistemin mimarının elinde olmalı.

Sonuç: Index Yapılarıyla Performansı Zirveye Taşımak

Veritabanı index yapıları, sorgu performansının temel taşıdır. B-tree, GIN ve BRIN gibi farklı index türlerinin ne zaman ve nasıl kullanılacağını bilmek, veritabanı yöneticileri ve geliştiriciler için kritik bir beceridir. Doğru index seçimi, sorgu hızını kat kat artırabilir, kullanıcı deneyimini iyileştirebilir ve altyapı maliyetlerini düşürebilir.

Bu yazıda incelediğimiz index türlerinin her birinin kendine özgü avantajları ve dezavantajları bulunmaktadır. B-tree, genel amaçlı sorgular için standart bir seçimken; GIN, metin ve yapısal olmayan veriler için, BRIN ise sıralı veriler için idealdir. Index seçimi yaparken, sadece okuma performansını değil, DML operasyonlarını ve disk alanı kullanımını da göz önünde bulundurmak gerekir. Unutmayın, en iyi index, ihtiyacınız olan indekstir; ne eksik ne de fazla.

Özetle, veritabanı performansını optimize etmek sürekli bir süreçtir. Sorgu planlarını düzenli olarak analiz etmek, istatistikleri güncel tutmak ve kullanılmayan index’leri temizlemek, sisteminizin sağlığı için hayati önem taşır. Bu bilgilerle, veritabanlarınızın potansiyelini tam olarak ortaya çıkarabileceğinize inanıyorum.