Veritabanı Sorgu Optimizasyonu: Dizinler, Yürütme Planları ve Bölümleme

Tek bir eksik dizin, 2 milisaniyelik bir sorguyu 20 saniyelik bir tablo taramasına dönüştürebilir. Veritabanınız binlerce satırdan milyonlarca satıra çıktıkça, optimize edilmiş ve optimize edilmemiş bir sorgu arasındaki fark, duyarlı bir uygulama ile yük altında zaman aşımına uğrayan bir uygulama arasındaki farktır. Veritabanı optimizasyonu, yapabileceğiniz herhangi bir performans işi arasında mühendislik süresinden en yüksek getiriyi sağlar.

Önemli Çıkarımlar

• ANALİZİ AÇIKLAYIN en güçlü teşhis aracınızdır - herhangi bir şeyi optimize etmeden önce yürütme planlarını okumayı öğrenin
• Dizin türlerini stratejik olarak seçin: Eşitlik ve aralık için B ağacı, tam metin ve JSONB için GIN, filtrelenmiş alt kümeler için kısmi dizinler
• N+1 sorgu, ORM tabanlı uygulamalarda en yaygın performans öldürücüdür; bunları sorgu günlüğüyle erken tespit edin
• Tablolar 10-50 milyon satırı aştığında tablo bölümleme zorunlu hale gelir, sorgu planlama süresini azaltır ve verimli veri yaşam döngüsü yönetimine olanak tanır

---

EXPLAIN ANALYZE ile Yürütme Planlarını Okumak

Herhangi bir sorguyu optimize etmeden önce PostgreSQL'in onu şu anda nasıl yürüttüğünü anlamalısınız. EXPLAIN ANALYZE sorguyu çalıştırır ve gerçek yürütme planını gerçek zamanlama verileriyle gösterir.

Temel bir AÇIKLAMA ANALİZİ çıktısı size planlayıcının seçtiği stratejiyi, tahmini ve gerçek satır sayılarını ve her adımda harcanan zamanı gösterir. Odaklanılacak temel metrikler şunlardır:

• Sıralı Tarama -- veritabanı tablodaki her satırı okur. Küçük tablolar (10.000 satırın altında) için kabul edilebilir ancak daha büyük tablolar için bir tehlike işaretidir.
• Dizin Taraması -- veritabanı, eşleşen satırları verimli bir şekilde bulmak için bir dizin kullanır. Büyük tablolardaki filtrelenmiş sorgular için istediğiniz şey budur.
• Yalnızca Dizin Taraması -- veritabanı, sorguyu tabloya dokunmadan tamamen dizinden yanıtlar. En hızlı tarama türü.
• İç İçe Döngü -- iç tabloyu dış tablodaki satır başına bir kez tarayarak tabloları birleştirir. İç tarama bir dizin kullandığında etkilidir.
• Hash join -- birleştirmenin bir tarafından bir karma tablosu oluşturur ve ardından diğer tarafla onu inceler. Daha büyük sonuç kümeleri için etkilidir.
• Sırala -- genellikle ORDER BY için açık bir sıralama adımı. Diske yayılan sıralamalara dikkat edin ("Sıralama Yöntemi: harici birleştirme" ile gösterilir).

Nelere Bakılmalı?

Bir yürütme planındaki en önemli sinyal, tahmini ve gerçek satırlar arasındaki boşluktur. PostgreSQL 10 satırı tahmin edip 100.000 satırı bulduğunda yanlış planı seçmiştir. Bu, tablo istatistikleri eski olduğunda meydana gelir; bunları güncellemek için tabloda ANALYZE komutunu çalıştırın.

Büyük tablolarda sıralı taramaları, dizinsiz sıralamaları ve iç tablodaki sıralı taramalarla iç içe döngüleri izleyin. Bu kalıpların her biri eksik bir dizini veya yeniden yazılması gereken bir sorguyu gösterir.

---

Dizin Türleri ve Ne Zaman Kullanılacağı

PostgreSQL, her biri farklı sorgu kalıpları için optimize edilmiş çeşitli dizin türleri sunar. Doğru türü seçmek kritik öneme sahiptir; yalnızca eşitlik kontrolüne ihtiyaç duyan bir sütundaki GIN dizini, depolamayı boşa harcar ve okumaları iyileştirmeden yazma işlemlerini yavaşlatır.

B-ağacı Dizinleri

B-ağacı varsayılan ve en çok yönlü dizin türüdür. Eşitliği (=), aralığı (<, >, ARASINDA), sıralamayı (ORDER BY) ve önek modeli eşleşmesini (LIKE 'abc%') destekler. WHERE, JOIN ve ORDER BY cümleciklerindeki çoğu sütun için B ağacı dizini doğru seçimdir.

Bileşik dizinler birden fazla sütunu tek bir B ağacında birleştirir. Sütun sırası önemlidir: (status, created_at) üzerindeki dizin, yalnızca durum veya hem durum hem de created_at üzerinde filtreleme yapan sorguları etkili bir şekilde destekler, ancak yalnızca created_at üzerinde filtreleme yapmaz. En seçici sütunu ilk sıraya ve aralık filtreleme için kullanılan sütunu en sona yerleştirin.

GIN Endeksleri

GIN endeksleri bileşik değerler içinde arama yapma konusunda mükemmeldir. Tam metin araması (tsvector sütunları), JSONB kapsama sorguları (@>, ?) ve dizi çakışma sorguları (&&, @>) için gereklidirler. GIN dizinleri, B-ağacı dizinlerinden daha büyük ve güncellenmesi daha yavaştır; bu nedenle bunları yalnızca B-ağacının sorgu modelini sunamadığı durumlarda kullanın.

Esnek nitelikleri saklayan JSONB sütunları için, sütunun tamamındaki bir GIN dizini, herhangi bir anahtar tabanlı sorguyu destekler. Yalnızca belirli anahtarları sorguladığınız sütunlar için, oluşturulan bir sütun veya ifadedeki B ağacı dizini daha verimlidir.

Kısmi Dizinler

Kısmi dizinler yalnızca WHERE koşuluyla eşleşen satırları dizine ekler. Sorguların küçük bir veri alt kümesini tutarlı bir şekilde filtrelediği tablolar için güçlüdürler.

Örneğin, siparişler tablonuzda 10 milyon satır varsa ancak neredeyse yalnızca etkin siparişleri sorguluyorsanız (tablonun %5'i), (customer_id, created_at) WHERE status = 'active' üzerindeki kısmi dizin, tam dizinden 20 kat daha küçüktür ve gerçek sorgularınız için aynı hızdadır.

---

N+1 Sorguyu Algılama ve Düzeltme

N+1 sorgu sorunu, ORM kullanan uygulamalarda en yaygın performans sorunudur. Kod, N kayıttan oluşan bir liste yüklediğinde ve ardından ilgili verileri yüklemek için kayıt başına bir ek sorgu çalıştırdığında ortaya çıkar ve sonuçta 1-2 yerine N+1 toplam sorgu elde edilir.

N+1 Sorgu Nasıl Gerçekleşir?

Müşteri adlarının yer aldığı bir sipariş listesi yüklemeyi düşünün. Saf bir uygulama, sipariş listesini (1 sorgu) yükler, ardından her sipariş için müşteriyi (N sorgu) yükler. 100 siparişle bu, 101 veritabanı gidiş dönüşünü oluşturur. Sorgu başına 1 ms, yani 101 ms'dir; ancak bağlantı havuzu çekişmesiyle eş zamanlı yük altında bu süre kolaylıkla 500 ms veya daha fazla olabilir.

Tespit Yöntemleri

1. Sorgu günlüğü -- PostgreSQL sorgu günlüğünü geçici olarak etkinleştirin ve farklı parametre değerleriyle tekrarlanan aynı sorguları arayın
2. ORM düzeyinde günlük kaydı -- Drizzle ORM, Prisma ve TypeORM'nin tümü, yürütülen her SQL ifadesini gösteren sorgu günlüğünü destekler
3. APM araçları -- Datadog, New Relic ve Sentry, sorguları uç noktaya göre gruplayabilir ve N+1 modellerini otomatik olarak vurgulayabilir
4. pg_stat_statements -- bu PostgreSQL uzantısı sorgu yürütme istatistiklerini izler ve sıklıkla yürütülen aynı sorgu şablonlarını ortaya çıkarır

N+1 Sorguyu Düzeltme

Düzeltme, ORM'nize ve sorgu düzeninize bağlıdır:

• İstekli yükleme -- ORM'ye, JOIN'leri kullanarak ilk sorgudaki ilgili verileri yüklemesini söyleyin. Drizzle'da sorgu oluşturucularda with seçeneğini kullanın.
• Toplu yükleme -- tüm yabancı anahtar kimliklerini toplayın, ardından ilgili kayıtları tek bir WHERE id IN (...) sorgusuna yükleyin. Bu DataLoader modelidir.
• Denormalizasyon -- yoğun okuma kullanım durumları için, ilgili verileri doğrudan ana kayıtta depolayın. Okuma performansı için yazma karmaşıklığını değiştirin.

---

Sorgu Yeniden Yazma Teknikleri

Bazen yalnızca daha iyi dizinlerin değil, sorgunun kendisinin de yeniden yapılandırılması gerekir.

JOIN Dönüşümü için Alt Sorgu

İlişkili alt sorgular, dış sorgudaki satır başına bir kez yürütülür. Bunları JOIN'lere dönüştürmek PostgreSQL'in daha verimli birleştirme stratejileri kullanmasına olanak tanır.

Müşteri başına en son sipariş tarihini arayan bir alt sorguyla siparişleri seçmek yerine, bunu türetilmiş bir tablo veya pencere işleviyle bir JOIN olarak yeniden yazın. JOIN sürümü, PostgreSQL'in veri dağıtımına dayalı olarak iç içe döngü, karma birleştirme ve birleştirme birleştirme arasında seçim yapmasına olanak tanır.

Ortak Tablo İfadeleri (CTE'ler)

PostgreSQL 12 ve sonraki sürümlerde, CTE'ler varsayılan olarak satır içidir; bu, optimize edicinin yüklemleri bunlara aktarabileceği anlamına gelir. Performans çitleri konusunda endişelenmeden okunabilirlik için CTE'leri kullanın. Açıkça somutlaştırma istediğiniz durumlar için (pahalı alt sorguların yeniden yürütülmesini önlemek için), MATERIALIZED anahtar sözcüğünü ekleyin.

Pencere İşlevleri ve GROUP BY

Hem ayrıntı satırlarına hem de toplamalara ihtiyaç duyduğunuzda, pencere işlevleri kendi kendine birleştirme veya alt sorgu ihtiyacını ortadan kaldırır. Değişen toplamı hesaplamak, gruplar içinde sıralama yapmak veya her satırı grup ortalamasıyla karşılaştırmak, pencere işlevleriyle ilişkili alt sorgulara göre daha verimlidir.

---

Tablo Bölümleme Stratejileri

Tablolar 10-50 milyon satırın üzerine çıktığında, iyi indekslenmiş sorgular bile indeks derinliği, boşluk yükü ve planlayıcının karmaşıklığı nedeniyle yavaşlar. Bölümleme, tek bir mantıksal tablo arayüzünü korurken büyük bir tabloyu daha küçük fiziksel parçalara böler.

Bölüm Türleri

Tarihe Göre Aralık Bölümleme

En yaygın model, zaman damgası sütununa göre aylık bölümlemedir. Her ayın verileri kendi bölümünde bulunur. Tarihe göre filtreleyen sorgular yalnızca ilgili bölümleri otomatik olarak tarar (bölüm düzeltme).

Zamana dayalı bölümlemenin faydaları:

• Sorgu performansı -- son verilere yönelik sorgular yalnızca son bölümleri tarar
• Bakım -- VACUUM ve ANALYZE daha küçük bölümlerde daha hızlı çalışır
• Veri yaşam döngüsü -- eski bölümlerin silinmesi, milyonlarca satırın silinmesine kıyasla anında gerçekleşir
• Yedekleme verimliliği -- belirli bir noktaya kurtarma için yalnızca son bölümleri yedekleyin

Bölümlendirmeyle İlgili Hususlar

Bölümleme karmaşıklığı artırır. Bölüm düzeltmenin çalışması için her sorgunun WHERE yan tümcesinde bölüm anahtarını içermesi gerekir. Benzersiz kısıtlamalar bölüm anahtarını içermelidir. Bölümlenmiş tablolara başvuran yabancı anahtarların sınırlamaları vardır. Bölümlemeye yalnızca tablo boyutunun performans düşüşüne neden olduğunu ölçtüğünüzde başlayın.

---

PostgreSQL Yapılandırma Ayarı

Varsayılan PostgreSQL yapılandırması muhafazakardır ve minimum donanımla çalışacak şekilde tasarlanmıştır. Üretim iş yükleri, temel parametrelerin ayarlanmasından yararlanır.

Bu ayarların özel donanımınıza ve iş yükünüze göre ayarlanması gerekir. Değişikliklerin performansı iyileştirdiğini doğrulamak için pg_stat_bgwriter, pg_stat_activity ve pg_stat_user_tables'ı izleyin.

---

Sıkça Sorulan Sorular

Bir tablonun kaç dizini olmalıdır?

Sabit bir sınır yoktur, ancak her dizin INSERT, UPDATE ve DELETE işlemlerini yavaşlatır çünkü dizinin korunması gerekir. En sık yaptığınız sorguların WHERE, JOIN ON ve ORDER BY cümleciklerinde görünen sütunlar için dizinler oluşturmak iyi bir genel kuraldır. Bırakılabilecek kullanılmayan dizinleri bulmak için pg_stat_user_indexes kullanın.

Performans için UUID veya tamsayı birincil anahtarları kullanmalı mıyım?

Tamsayı birincil anahtarları (BIGSERIAL), daha küçük olduklarından (8 bayta karşı 16 bayta) ve doğal olarak sıralandığından birleştirme ve indeksleme için daha hızlıdır. UUID'ler, dağıtılmış sistemler için önemli olan koordinasyon olmadan küresel benzersizlik sağlar. Çoğu uygulamada, harici tanımlayıcılar için UUID'leri ve dahili birleştirmeler için tamsayıları kullanın.

Replikaları okumak için ne zaman tek bir veritabanından geçiş yapmalıyım?

Okuma iş yükünüz veritabanınızın kapasitesinin %70-80'ini aştığında veya raporlama sorguları kaynaklar için işlemsel sorgularla rekabet ettiğinde. Okuma kopyaları okuma yükünü üstlenirken birincil yazma işlemlerine odaklanır. Bu, tipik bir web uygulaması için genellikle 5.000-10.000 eşzamanlı kullanıcı için gereklidir.

Üretimde yavaş sorguları kesinti olmadan nasıl halledebilirim?

Tablonun kilitlenmesini önlemek için EŞZAMANLI seçeneğiyle dizinler oluşturun. En yavaş sorguları belirlemek için pg_stat_statements'ı kullanın. Özellik bayraklarının arkasında sorgu iyileştirmelerini dağıtın. Tabloları yeniden yazan şema değişiklikleri için pg_repack gibi araçları kullanarak tabloları kilitlemeden yeniden düzenleyin.

---

Sırada Ne Var

Veritabanı optimizasyonu platform performansının temelidir. pg_stat_statements'ı etkinleştirerek başlayın, en yavaş sorgularınızı tanımlayın ve EXPLAIN ANALYZE ile bunlar üzerinde sistematik olarak çalışın. Eksik dizinleri ekleyin, N+1 kalıplarını düzeltin ve en büyük tablolarınız için bölümlendirmeyi düşünün.

Daha geniş bir performans tablosu için iş platformunuzu startup'tan kurumsala ölçeklendirme hakkındaki temel kılavuzumuza bakın. Optimizasyonun bir sonraki katmanı hakkında bilgi edinmek için Redis, CDN ve HTTP önbelleğe alma ile önbelleğe alma stratejileri hakkındaki kılavuzumuzu okuyun.

ECOSIRE, Odoo ERP ve özel uygulamalar dahil olmak üzere PostgreSQL destekli platformlar için uzman veritabanı optimizasyonu sağlar. Veritabanı performansı denetimi için bize ulaşın.

---

ECOSIRE tarafından yayınlandı — işletmelerin Odoo ERP, Shopify eCommerce ve OpenClaw AI genelinde yapay zeka destekli çözümlerle ölçeklenmesine yardımcı oluyor.