Veritabanı Indexleri: Her Sorgu İçin Gerekli mi?

Geçenlerde bir üretim ERP’sinde, sipariş takip ekranlarının yavaş çalıştığına dair bir şikayet aldık. Herkesin ilk aklına gelen “index eksikliği” oldu ve “şu kolona da bir index atalım” önerileri havada uçuşmaya başladı. Ancak benim deneyimim, indexlerin her zaman sihirli bir değnek olmadığı, hatta yanlış kullanıldığında sistemi daha da kötüye götürebileceği yönünde. Veritabanı indexleri, çoğu zaman bir mühendislik trade-off’u gerektirir: okuma hızını artırırken, yazma maliyetini ve disk alanını artırırsınız.

Bu yazıda, indexlerin ne zaman gerçekten gerekli olduğunu, ne zaman gereksiz yük oluşturduğunu ve production ortamında nasıl bir strateji izlediğimi anlatacağım. Her sorgu için index eklemek, genellikle sorunları çözmekten çok yenilerini yaratır, bunu defalarca gördüm. Amacım, bu konuda daha pragmatik bir bakış açısı sunmak.

Indexlerin Temel Amacı ve Görünmez Maliyeti

Indexler, veritabanının belirli sütunlardaki verileri daha hızlı bulmasını sağlayan özel veri yapılarıdır. Tıpkı bir kitabın sonundaki dizin gibi, aradığınız bilgiyi tüm kitabı baştan sona okumadan bulmanızı sağlarlar. PostgreSQL’de en sık karşılaştığımız index türleri B-tree, GIN ve BRIN’dir. B-tree, eşitlik (=), aralık (>, <), sıralama (ORDER BY) işlemleri için harikadır. GIN (Generalized Inverted Index) ise array veya JSONB gibi karmaşık veri tipleri üzerinde tam metin aramaları için kullanılırken, BRIN (Block Range Index) büyük tablolarda sıralı veya neredeyse sıralı veriler üzerinde çalışır.

Peki, neden her şeye index eklemiyoruz? İşte burada işin maliyet boyutu devreye giriyor. Her index, diskte yer kaplar. Tabloya yeni bir satır eklendiğinde (INSERT), mevcut bir satır güncellendiğinde (UPDATE) veya bir satır silindiğinde (DELETE), veritabanının sadece ana tablo verisini değil, o tabloyla ilişkili her index’i de güncellemesi gerekir. Bu da yazma operasyonlarının maliyetini artırır. Çok sayıda index olan bir tabloda küçük bir UPDATE bile, CPU ve I/O yükünü önemli ölçüde artırabilir.

Örneğin, 10 milyon satırlık bir products tablomuz olduğunu varsayalım. Sadece name ve description sütunları üzerinde tam metin arama için bir GIN index’i eklemek, tablonun boyutunu kayda değer ölçüde artırabilir. Ayrıca, her INSERT veya UPDATE işlemi, bu GIN index’i de güncellemek zorunda kalır. Aşağıdaki basit senaryoda, bir tablonun indexsiz ve indexli hali arasındaki INSERT performansı farkını görebilirsiniz:

-- Indexsiz tablo
CREATE TABLE products_no_index (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexli tablo
CREATE TABLE products_with_index (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_products_name_description ON products_with_index USING GIN (to_tsvector('english', name || ' ' || description));
CREATE INDEX idx_products_price ON products_with_index (price);

-- Performans testi (Gerçek bir senaryoda daha fazla veri ve tekrar ile yapılmalı)
-- products_no_index tablosuna 100000 kayıt ekleme
INSERT INTO products_no_index (name, description, price)
SELECT
    'Product ' || generate_series(1, 100000),
    'Description for product ' || generate_series(1, 100000),
    (random() * 1000)::NUMERIC(10, 2);
-- Süre ortama göre değişir; indexsiz tablo belirgin şekilde daha hızlı eklenir

-- products_with_index tablosuna 100000 kayıt ekleme
INSERT INTO products_with_index (name, description, price)
SELECT
    'Product ' || generate_series(1, 100000),
    'Description for product ' || generate_series(1, 100000),
    (random() * 1000)::NUMERIC(10, 2);
-- Süre ortama göre değişir; GIN index'in ek maliyeti nedeniyle gözle görülür şekilde daha uzun sürer

Gördüğünüz gibi, sadece iki index bile INSERT süresini katlayabilir. Büyük veri setleri ve yoğun yazma yükü altında bu fark, sistemin genel yanıt süresini felç edebilir. Benim için bu tür basit testler, index kararı vermeden önce her zaman bir başlangıç noktası olmuştur.

Sorgu Türlerine Göre Index Stratejileri

Her sorgu index gerektirmez, hatta bazıları için index kullanmak performansı düşürebilir. Önemli olan, sorgunun ne tür bir işlem yaptığını anlamak ve buna uygun index stratejisi geliştirmektir.

1. Nokta Aramaları (Point Lookups) ve Aralık Aramaları (Range Scans):

   • WHERE id = 123 veya WHERE email = '[email protected]' gibi sorgular için B-tree index’ler vazgeçilmezdir. Primary key’ler otomatik olarak B-tree index’lidir.
   • WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31' gibi aralık sorguları da B-tree index’lerden faydalanır.
   • Bir ERP sisteminde, müşteri numarasına göre sipariş aramak veya ürün koduna göre stok sorgulamak bu kategoriye girer.

2. Sıralama (ORDER BY) ve Gruplama (GROUP BY):

   • ORDER BY column_name içeren sorgular, ilgili sütunda bir B-tree index varsa, veritabanının filesort yapmasını (yani sonuç setini bellekte veya diskte sıralamasını) engeller. Bu, özellikle büyük veri setlerinde ciddi performans kazancı sağlar.
   • GROUP BY ifadeleri de benzer şekilde indexlerden faydalanabilir, eğer gruplama sütunları index’liyse.
   • Bir raporlama ekranında, son 30 günün en çok satan ürünlerini fiyata göre azalan sırada listelerken bu indexler hayat kurtarır.

3. LIKE Operatörü ve Tam Metin Aramaları:

   • WHERE name LIKE 'Mustafa%' gibi önden başlayan aramalar için B-tree index’ler kullanılabilir.
   • Ancak WHERE name LIKE '%Mustafa%' gibi içeriden veya sondan başlayan aramalar için B-tree index’ler genellikle işe yaramaz. Bu durumda PostgreSQL bir sequential scan yapmak zorunda kalır.
   • Tam metin arama için to_tsvector ve to_tsquery fonksiyonları ile GIN veya GiST index’ler kullanılır. Bunlar, çok daha karmaşık ve esnek arama yetenekleri sunar.
   • Kullanıcıların serbest metinle arama yaptığı içerik tablolarında, GIN index’ler bu tür esnek aramaları hızlandırmak için doğru tercihtir.

Aşağıdaki EXPLAIN ANALYZE çıktısı, basit bir sorgunun index ile nasıl hızlandığını gösteriyor.

-- products_with_index tablosu üzerinde indexin etkisini gözlemleyelim
-- Önce indexsiz bir sorgu çalıştıralım (varsayımsal olarak, index olmasaydı)
EXPLAIN ANALYZE SELECT * FROM products_no_index WHERE price > 990;
-- Plan: Seq Scan on products_no_index (Filter: price > 990)
-- Tablonun tamamı taranır; çıktıdaki süreler ortama göre değişir

-- Şimdi indexli tablo üzerinde aynı sorguyu çalıştıralım
EXPLAIN ANALYZE SELECT * FROM products_with_index WHERE price > 990;
-- Plan: Index Scan using idx_products_price (Index Cond: price > 990)
-- Yalnızca eşleşen satırlar okunur; çıktıdaki süreler ortama göre değişir

Gördüğünüz gibi, idx_products_price index’i sayesinde plan Seq Scan’den Index Scan’e dönüşüyor ve seçici bir sorguda yürütme süresi belirgin şekilde düşüyor. Ancak bu, price sütunundaki verinin dağılımına ve sorgunun seçiciliğine bağlı. Eğer sorgu, tablonun %80’inden fazlasını döndürseydi, optimizer muhtemelen index kullanmak yerine bir sequential scan yapmayı tercih ederdi, çünkü index’i okuyup sonra ana tabloya gidip veriyi getirmek, tablonun tamamını okumaktan daha pahalı hale gelebilirdi. Bu tür durumlarda, benim deneyimimde, veritabanı optimizer’ının kararına güvenmek genellikle en iyisidir. PostgreSQL Optimizer İçgörüleri

Over-indexing ve Gizli Performans Kayıpları

Bir tablodaki her sütuna index eklemek, yeni başlayanların sıkça düştüğü bir tuzaktır. “Ne kadar çok index, o kadar hızlı sorgu” mantığı, teoride kulağa hoş gelse de, pratikte çoğu zaman tam tersi etki yaratır. Her raporun hızlı çalışması için her olası filtre sütununa index eklenmiş raporlama altyapılarında tekrar tekrar gördüğüm tablo şu: okuma tarafı bir nebze rahatlarken, sıcak bir transactions tablosuna yapılan basit bir INSERT işlemi belirgin şekilde yavaşlar, çünkü her satır yazımı tüm indexleri de güncellemek zorundadır.

Over-indexing’in yol açtığı bazı gizli performans kayıpları şunlardır:

• Disk Alanı Şişmesi (Bloat): Her index, diskte yer kaplar. Çok sayıda index, özellikle büyük tablolarda, terabaytlarca ek disk alanı anlamına gelebilir. Bu da yedekleme sürelerini uzatır ve disk maliyetlerini artırır.
• Yazma Operasyonlarının Yavaşlaması: Daha önce bahsettiğim gibi, her INSERT, UPDATE, DELETE işlemi, ilgili tüm indexleri de güncellemek zorundadır. Index sayısı arttıkça bu maliyet katlanarak artar.
• VACUUM Yükü: PostgreSQL’in MVCC (Multi-Version Concurrency Control) mimarisi gereği, UPDATE ve DELETE işlemleri “ölü tuple”lar bırakır. Bu ölü tuple’lar ve onlara işaret eden index girdileri VACUUM tarafından temizlenir. Çok fazla index, VACUUM işleminin daha uzun sürmesine ve sistem kaynaklarını daha fazla tüketmesine neden olur.
• Optimizer Karar Verme Süresi: PostgreSQL sorgu optimizer’ı, bir sorgu için en iyi yürütme planını bulmak amacıyla birçok olası index kombinasyonunu değerlendirmek zorundadır. Index sayısı arttıkça bu karar verme süresi de uzar. Karmaşık sorgularda bu ek yük, göz ardı edilemez boyutlara ulaşabilir.
• Yanlış Index Seçimi: Bazen veritabanı optimizer’ı, yanlış index’i seçebilir. Özellikle veri dağılımı çarpık olduğunda (skewed data) veya istatistikler güncel olmadığında bu durum yaşanabilir. Index sayısı arttıkça, yanlış seçimin olasılığı da artar.

Bir üretim firmasının ERP’sinde, stock_movements tablosunda 7 farklı index vardı ve basit bir stok girişi işlemi (INSERT) belirgin şekilde yavaşlamıştı. pg_stat_user_indexes tablosunu incelediğimde, bu indexlerden 4 tanesinin neredeyse hiç kullanılmadığını gördüm. Kullanılmayan indexleri kaldırdığımızda, aynı INSERT işlemi gözle görülür biçimde hızlandı. Bu, basit bir index temizliğinin bile performansı nasıl etkileyebileceğine dair somut bir örnektir.

Index Seçiminde Veri Dağılımının Rolü

Indexlerin etkinliği, büyük ölçüde verinin dağılımına (cardinality) bağlıdır. Cardinality, bir sütundaki benzersiz değerlerin sayısıdır.

• Yüksek Cardinality: Bir sütundaki benzersiz değer sayısı ne kadar yüksekse (örneğin e-posta adresleri, TC kimlik numaraları, GUID’ler), o sütuna eklenen index o kadar etkili olur. Çünkü index, sorguyu doğrudan az sayıda satıra yönlendirebilir. WHERE email = '...' sorgusu, yüksek cardinality’ye sahip bir sütun üzerinde çalıştığı için çok hızlıdır.
• Düşük Cardinality: Bir sütundaki benzersiz değer sayısı ne kadar düşükse (örneğin status sütunu: ‘active’, ‘passive’, ‘deleted’), index o kadar az etkili olur. Eğer bir sorgu WHERE status = 'active' ile tablonun %80’ini döndürüyorsa, veritabanı muhtemelen index kullanmak yerine sequential scan yapmayı tercih edecektir. Çünkü index’i okuyup her satır için ana tabloya gitmek, tüm tabloyu okumaktan daha maliyetli hale gelebilir.

Bu durumu bir örnekle açıklayalım:

-- products tablomuzda bir 'category' sütunu ekleyelim
ALTER TABLE products_with_index ADD COLUMN category TEXT;
UPDATE products_with_index SET category = 'Electronics' WHERE id % 3 = 0;
UPDATE products_with_index SET category = 'Books' WHERE id % 3 = 1;
UPDATE products_with_index SET category = 'Home Goods' WHERE id % 3 = 2;

-- Şimdi 'category' sütununa bir index ekleyelim
CREATE INDEX idx_products_category ON products_with_index (category);

-- Düşük seçicilikli bir sorgu
EXPLAIN ANALYZE SELECT * FROM products_with_index WHERE category = 'Electronics';
-- Output: Muhtemelen bir Sequential Scan veya Bitmap Heap Scan (index'i kullanır ama yine de tablonun çoğunu okur)

Bu senaryoda, category sütunu sadece 3 farklı değere sahip. Electronics kategorisindeki ürünler tablonun yaklaşık üçte birini oluşturuyor. PostgreSQL optimizer’ı, bu kadar büyük bir subset için index kullanmak yerine sequential scan yapmayı daha verimli bulabilir. Benim gördüğüm kadarıyla, bir indexin seçiciliği tablonun %5-10’undan azını döndürüyorsa işe yarar, aksi takdirde maliyet-fayda dengesi bozulur.

Partial Indexes: Akıllı Çözüm

Bazı durumlarda, bir sütundaki verinin sadece belirli bir alt kümesi üzerinde hızlı arama yapmak isteyebilirsiniz. İşte burada partial index’ler devreye girer. Partial index’ler, sadece WHERE koşulunu sağlayan satırlar için index girdileri oluşturur. Bu, hem disk alanından tasarruf sağlar hem de yazma maliyetini düşürür.

Production Ortamında Index Yönetimi ve İzleme

Indexlerinizi bir kez oluşturup unutmak, üretim ortamında sorunlara davetiye çıkarmaktır. Sürekli izleme ve bakım, index stratejisinin önemli bir parçasıdır.

1. Kullanılmayan Indexleri Tespit Etme: PostgreSQL, hangi indexlerin kullanıldığını pg_stat_user_indexes görünümü aracılığıyla takip eder. idx_scan, idx_tup_read, idx_tup_fetch gibi sütunlar, bir indexin kaç kez kullanıldığını ve kaç tuple okuduğunu gösterir. Eğer bir index uzun süre sıfır kullanım gösteriyorsa, muhtemelen gereksizdir ve kaldırılması düşünülmelidir.

   SELECT
       relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       'DROP INDEX ' || indexrelname || ';' AS drop_command
   FROM
       pg_stat_user_indexes
   WHERE
       idx_scan = 0
       AND schemaname = 'public' -- Varsayılan şema
   ORDER BY
       pg_relation_size(indexrelid) DESC;

   Bu sorgu, üretimde sıkça kullandığım bir araçtır. Hiç taranmamış ama diskte ciddi yer kaplayan indexleri bu şekilde tespit edip kaldırmak, hem disk alanından tasarruf sağlar hem de ilgili tablodaki yazma işlemlerini hızlandırır.

2. pg_stat_statements ile Sorgu Analizi: Hangi sorguların yavaş çalıştığını ve hangi sütunlar üzerinde index’e ihtiyaç duyulduğunu anlamak için pg_stat_statements modülü paha biçilmezdir. Bu modül, en çok zaman harcayan sorguları, çağrı sayılarını ve ortalama yürütme sürelerini gösterir. Yavaş sorguları belirledikten sonra EXPLAIN ANALYZE ile sorgu planlarını inceleyerek index eksikliklerini veya yanlış index kullanımını tespit edebiliriz.

3. Index Yeniden Oluşturma (REINDEX CONCURRENTLY): Indexler zamanla parçalanabilir (fragmentation) ve performansları düşebilir. Özellikle yoğun UPDATE ve DELETE işlemlerinden sonra bu durum sık görülür. REINDEX CONCURRENTLY, index’i yeniden oluştururken tabloya kilit koymadığı için üretim ortamında güvenle kullanılabilir. Ancak bu işlemin çift disk alanı gerektirdiğini ve uzun sürebileceğini unutmamak gerekir.

4. Otomatik Index Tavsiyeleri: hypopg gibi araçlar, mevcut sorgu yükünü analiz ederek hipotetik indexler oluşturabilir ve bunların sorgu performansı üzerindeki etkisini EXPLAIN ile simüle edebilir. Bu tür araçlar, yeni indexler için bir başlangıç noktası olabilir ancak her zaman manuel doğrulama ve test gerektirir. Benim deneyimimde, bu araçlar “olası index” listesi sunsa da, nihai kararı her zaman ben veririm, çünkü tool’lar trade-off’ları tam olarak değerlendiremez.

”Neden Index Kullanılmadı?” Krizleri ve Çözümleri

Bazen, bir sütuna index eklediğiniz halde sorgunun hala yavaş çalıştığını ve EXPLAIN ANALYZE çıktısında indexin kullanılmadığını görürsünüz. Bu durum, genellikle bazı sinsi nedenlerden kaynaklanır:

1. Veri Tipi Uyuşmazlıkları (Implicit Casts): Sorguda, indexli sütunun veri tipi ile karşılaştırma yapılan değerin veri tipi uyuşmuyorsa, PostgreSQL bir implicit cast (örtük tür dönüştürme) yapar. Bu cast işlemi, indexin kullanılmasını engeller. Örneğin, VARCHAR bir sütuna index ekleyip WHERE my_column = 123 şeklinde sorgu yaparsanız, veritabanı VARCHAR değeri INTEGER’a dönüştürmeye çalışır ve index kullanılmaz. Doğru çözüm, karşılaştırma yapılan değerin tipini indexli sütununkine eşleştirmektir: WHERE my_column = '123'. Bu, bir keresinde bir müşteri projesinde, tarih sütunlarında sıkça yaşadığım bir sorundu; timestamp indexli bir sütunu date ile karşılaştırmak, indexi öldürüyordu.

2. Indexli Sütun Üzerinde Fonksiyon Çağrıları: WHERE function(indexed_column) = 'value' gibi sorgular, indexin kullanılmasını engeller. Çünkü index, indexed_column’ın orijinal değerleri üzerinde oluşturulmuştur, fonksiyonun döndürdüğü değer üzerinde değil.

   -- Yanlış: Index kullanılmaz
   SELECT * FROM users WHERE lower(email) = '[email protected]';
   
   -- Doğru: Index kullanılabilir (eğer functional index varsa veya doğrudan email sütununu kullanıyorsanız)
   -- Functional index oluşturulabilir: CREATE INDEX idx_users_lower_email ON users (lower(email));
   -- Ya da sorguyu değiştirmek: SELECT * FROM users WHERE email = '[email protected]';

   Eğer lower(email) üzerinde sıkça arama yapılıyorsa, bu fonksiyonel index’i oluşturmak bir çözümdür.

3. Optimizer’ın Tahmini Yanlış Yapması (Stale Statistics): PostgreSQL optimizer’ı, sorgu planlarını oluştururken tablonun ve indexlerin istatistiklerini kullanır. Eğer veri dağılımı önemli ölçüde değiştiyse ve istatistikler güncel değilse (yani ANALYZE işlemi uzun süredir çalışmadıysa), optimizer yanlış bir plan seçebilir ve index kullanmaktan kaçınabilir. ANALYZE TABLE table_name; komutunu çalıştırmak genellikle bu sorunu çözer.

4. Çok Küçük Tablolar: Çok küçük tablolarda (genellikle birkaç bin satırın altında), veritabanı sequential scan yapmayı index kullanmaktan daha hızlı bulabilir. Index’i okuma ve ana tabloya gitme maliyeti, tüm tabloyu baştan sona okuma maliyetinden daha yüksek olabilir. Bu “olur o kadar” bir durumdur; küçük tablolar için index takıntısı genellikle gereksizdir.

5. Karmaşık Koşullar ve ORM Tuzakları: Bazen ORM’ler (Object-Relational Mappers) karmaşık ve verimsiz sorgular üretebilir. OR operatörü ile birleştirilmiş koşullar, birden fazla indexi aynı anda kullanamayabilir ve sequential scan’e yol açabilir. Bu gibi durumlarda, sorguyu manuel olarak optimize etmek veya UNION ALL kullanarak sorguyu parçalara ayırmak gerekebilir. Özellikle N+1 sorgu problemi, ORM’lerin sıkça düştüğü bir tuzaktır ve bu, sorgu planını doğrudan etkilemese de, toplamda çok sayıda küçük ve pahalı sorgu çalışmasına neden olur. Daha önce ORM Tuzakları ve Çözümleri yazımda detaylıca bahsetmiştim.

Sonuç: Index’i Refleksle Değil, Ölçümle Kullanmak

Index’ler doğru kullanıldığında bir sorguyu saniyelerden milisaniyelere indiren güçlü araçlardır. Ama bu yazı boyunca göstermeye çalıştığım gibi, “yavaşsa index ekle” refleksi çoğu zaman sorunu çözmez, sadece yerini değiştirir: okuma hızını alırken yazma maliyetini, disk alanını ve VACUUM yükünü geri ödersiniz. Üretim ortamında defalarca gördüğüm tablo şu: gereğinden fazla index, gereğinden az index kadar zararlıdır.

Yıllar içinde oturttuğum pratik yaklaşım birkaç ilkeye dayanıyor:

• Önce ölç, sonra ekle. Her index kararından önce EXPLAIN ANALYZE ve pg_stat_statements ile gerçek darboğazı görün. Tahmine değil, sorgu planına güvenin.
• Seçiciliği hesaba katın. Bir index, sorgunun tablonun %5-10’undan azını döndürdüğü durumlarda parlar. Düşük cardinality’li sütunlarda (status, type gibi) çoğu zaman bir partial index ya da hiç index, tam index’ten daha iyidir.
• Yazma yükünü unutmayın. INSERT/UPDATE ağırlıklı tablolarda her index bir vergidir. stock_movements örneğinde olduğu gibi, kullanılmayan dört index’i kaldırmak bazen yapabileceğiniz en iyi “performans iyileştirmesi”dir.
• Index’leri izleyin ve budayın. pg_stat_user_indexes ile idx_scan = 0 olanları düzenli olarak avlayın. Bir index’i eklemek kadar, gereksizleştiğinde kaldırmak da işin bir parçasıdır.
• Tuzakların farkında olun. Implicit cast, indexli sütun üzerinde fonksiyon çağrısı ve eski istatistikler, “index neden kullanılmadı?” krizlerinin büyük çoğunluğunun kaynağıdır; bunlar genellikle index eksikliği değil, sorgu ya da bakım sorunudur.

Başlıktaki soruya dönecek olursam — “her sorgu için index gerekli mi?” — cevabım net bir “hayır.” Index bir kontrol listesi maddesi değil, ölçüme dayalı bir mühendislik kararıdır. Doğru sütuna, doğru türde ve doğru gerekçeyle konulan bir index altın değerindedir; refleksle serpiştirilen index’ler ise er ya da geç faturayı yazma yolunda ve bir gece yarısı autovacuum alarmında keser. Pragmatik olun, planı okuyun ve veritabanının size ne söylediğini dinleyin.