Alışılmadık PostgreSQL optimizasyon teknikleri

• PostgreSQL sorgu performansını artırmak için mevcut kalıpların dışına çıkan optimizasyon yöntemleri olarak, indeks ekleme ya da sorguyu yeniden yazma yerine 3 yaratıcı yaklaşım tanıtılıyor
• Check constraint tabanlı tam tablo taramasını ortadan kaldırma, fonksiyon tabanlı indekslerle düşük kardinalite optimizasyonu, Hash indeksleriyle unique constraint uygulaması
• 1. constraint_exclusion ayarını kullanarak hatalı koşullar içeren sorgularda gereksiz taramaları önleme
• 2. Fonksiyon tabanlı indeksler ve sanal üretilmiş sütunlar (virtual generated column) ile indeks boyutunu küçültüp sorgu tutarlılığını sağlama
• 3. Hash indeksleri ile exclusion constraint birleştirilerek büyük metin sütunlarında unique constraint'in verimli biçimde uygulanması ve depolama alanından büyük tasarruf sağlanması

---

Check constraint tabanlı tam tablo taramasını ortadan kaldırma

• plan sütununda yalnızca 'free', 'pro' değerlerine izin veren bir CHECK constraint olmasına rağmen, 'Pro' ile hatalı bir sorgu çalıştırıldığında PostgreSQL tüm tabloyu tarar
  • Çalıştırma planında 100 bin satırın tamamı okunur, gerçek sonuç ise 0 satırdır
• constraint_exclusion parametresi 'on' olarak ayarlanırsa PostgreSQL constraint'i dikkate alıp taramayı tamamen atlar
  • Çalışma süresi 7.4ms'den 0.008ms'ye düşer
• Varsayılan değer 'partition'dır ve basit sorgularda planning overhead daha da artabilir
  • Ancak BI ve raporlama ortamlarında kullanıcılar sık sık hatalı koşullar girdiği için 'on' ayarı faydalı olabilir

Fonksiyon tabanlı indekslerle düşük kardinalite optimizasyonu

• 10 milyon satış verisi içeren sale tablosunda günlük ciro toplulaştırma sorgusu çalıştırıldığında tam tarama 627ms sürer
• sold_at sütununa B-Tree indeksi eklenirse süre 187ms'ye iner, ancak indeks boyutu 214MB'a çıkar
• date_trunc('day', sold_at) ifadesi üzerinde fonksiyon tabanlı indeks oluşturulursa boyut 66MB'a düşer ve çalışma süresi 145ms ile daha da hızlanır
  • Düşük kardinalite sayesinde indeks deduplication mümkün olur
• Ancak sorgu ifadesinin indeks tanımıyla tam olarak eşleşmesi gerektiğinden ifade tutarlılığını korumak gerekir
  • Bunun için aynı ifadeyi içeren bir VIEW oluşturulabilir veya
  • PostgreSQL 18 ile desteklenen sanal üretilmiş sütun (virtual generated column) eklenerek bu tutarlılık otomatikleştirilebilir
• Sanal üretilmiş sütun kullanıldığında indeks otomatik olarak devreye girer ve küçük indeks, hızlı sorgu, ifade tutarlılığı aynı anda elde edilir
• Ancak PostgreSQL 18'de sanal sütun üzerinde indeks oluşturma henüz desteklenmiyor; bunun PostgreSQL 19'da gelmesi bekleniyor

Hash indeksleriyle unique constraint uygulaması

• Uzun URL'leri saklayan urls tablosunda tekrarlanan URL'leri engellemek için B-Tree tabanlı unique indeks oluşturulduğunda indeks boyutu 154MB'a ulaşır
• Hash indeksi gerçek değeri değil yalnızca hash değerini sakladığı için çok daha küçüktür
  • PostgreSQL varsayılan olarak unique Hash indeksi desteklemez, ancak
  • EXCLUDE USING HASH (url WITH =) biçiminde exclusion constraint kullanılarak unique constraint dolaylı biçimde uygulanabilir
• Bu yöntemde de yinelenen eklemelerde hata oluşur ve sorgu performansı da B-Tree'den daha iyidir (0.022ms vs 0.046ms)
• İndeks boyutu 32MB'tır; bu, B-Tree'ye kıyasla 5 kattan fazla daha küçüktür
• Dezavantajları:
  • Foreign key referansı verilemez (REFERENCES constraint kullanılamaz)
  • INSERT ... ON CONFLICT sözdizimiyle uyumluluk sınırlıdır
  • Bunun yerine ON CONFLICT ON CONSTRAINT ya da MERGE kullanılabilir
• Hash indeksleri, büyük metin sütunlarında benzersizliği garanti etmek için uygundur ve foreign key gerekmeyen durumlarda alan verimliliği yüksek bir alternatif sunar

[GN⁺]

Hacker News yorumları

• İndeks 214MB, yani tüm tablonun yaklaşık yarısı kadar
  Analist açısından iyi, ancak yazma performansı açısından write amplification sorunu yaratıyor
  İndeksler okuma/yazma oranına göre farklı tasarlanır; veri ambarı veya read replica kullanılmasının sebeplerinden biri de budur
  Çok fazla kullanıcıya hizmet veriliyorsa, OLTP DB üzerinde BI/OLAP indeksleri bulundurmamak daha iyidir

  • PostgreSQL'in clustered index'i (Oracle'ın Index Organized Table'ı) desteklemesi güzel olurdu diye düşünüyorum
    Tablo erişim örüntüsü tutarlıysa, tablonun kendisi indeks haline gelip write amplification olmadan verim sağlayabilir

• İlk örnekte Planı enum type olarak tanımlamanın daha iyi olduğunu düşünüyorum
  Metinden daha hafiftir ve yanlış filtre girdisi geldiğinde boş sonuç yerine hata vererek daha güvenli davranır

• Harika bir yazıydı. PostgreSQL ve MySQL'i onlarca yıldır kullanıyorum ama bu yazıyı görünce hâlâ sadece olasılıkların küçük bir kısmını bildiğimi hissettim

  • Ben de 10 yılı aşkın süredir Postgres kullanıyorum ama belgelere her baktığımda hâlâ sadece yüzeyi kazıyormuşum gibi geliyor. Gerçekten çok güçlü bir sistem
  • PostgreSQL adeta Emacs gibi. Dışarıdan basit görünüyor ama aslında işletim sistemi düzeyinde esnekliğe sahip

• Yazının sonunda bahsedilen MERGE sözdizimi en ilginç kısımdı
  Normalde upsert için INSERT ... ON CONFLICT DO UPDATE kullanıyorum ama MERGE daha güçlü görünüyor ve daha çeşitli durumlarda işe yarayabilir

  • MERGE uzun zamandır SQL standardında var, ancak Postgres bunu MVCC modelindeki atomik olmama sorunu nedeniyle geciktirdi
    pganalyze blog yazısı bunu da açıklıyor
    Kişisel olarak INSERT ... ON CONFLICT tercih ediyorum; MERGE'i ise gerçekten gerektiğinde kullanıyor ve hata yönetimini dikkatle ele alıyorum
  • Eşzamanlılık açısından INSERT ... ON CONFLICT daha öngörülebilir
    modern-sql.com karşılaştırma yazısına bakılabilir
  • Büyük hacimli batch insert için COPY INTOyu binary format ile kullanmak en hızlısı. Sunucu tarafı ek yük neredeyse yok

• Yazıda ele alınmayan BRIN index ilginçti
  Veri monoton artıyorsa çok küçük ve hızlı bir indeks olarak ideal

  • Verinin tamamen monoton olması da şart değil. Çoğunlukla monoton olması bile yeterince iyi çalışır
    Örneğin sunucuda alınan timestamp verisi gibi sıralamanın biraz bozulduğu durumlarda da iyidir
    UUIDv7 için pages_per_range ayarlamak gerekebilir

• hash index üzerinde unique constraint tanımlanamaması hep üzücü geliyor
  Sadece exclusion constraint'e dönüştüren bir glue kodu olsa çözülecekmiş gibi, neden hâlâ yok merak ediyorum

• Hash tabanlı benzersizlik doğrulaması çakışma yönetimi yapamadığı için indekslerde desteklenmiyor
  Önerilen çözüm de aynı sorunu yaşıyor

  • Bu hiç doğru değil. İndeks yalnızca hash'i saklıyor ama tabloda tam değer tutuluyor
    Postgres ancak hem hash hem de gerçek değer eşleşirse bunu tekrar olarak kabul ediyor
  • dbfiddle örneğinde de görülebilir

• Yazının içeriği tazeydi. Virtual column ve hash index ilginç, ancak henüz ekosisteme tamamen entegre olamamış gibiler

  • Virtual column neredeyse tamamlandı. PostgreSQL 18 ile büyük ölçüde uygulanmış durumda
    Hash index uzun süre birçok kısıta sahipti ama giderek iyileşiyor; geriye kalan konu otomatik unique constraint desteği

• stored generated column kullanılırsa doğrudan indeks oluşturulamaz mı diye düşündüm

  • Yazı bu yöntemden neden kaçınıldığını açıklıyor
    PostgreSQL 14'ten beri destekleniyor ama sonuç fiziksel olarak saklandığı ve ek depolama alanı kullandığı için
  • İfade tabanlı bir partial index oluşturmak da mümkün olmaz mı diye merak ettim
  • Sonuçta depolama alanını artırdığı için, yazıdaki örnekte kaçınılmak istenen yaklaşım bu

• Cloud'a taşındıktan sonra sabit sunucu ortamında pgsql ile doğrudan uğraşma işi azaldı
  Yazıdaki SQL syntax highlighting'ın yerleşik bir özellik mi yoksa ayrı bir araç mı olduğunu merak ettim

  • Ben pgcli kullanıyorum. Transaction durumu gösterimi, otomatik tamamlama, highlighting gibi birçok kullanım kolaylığı var
    Yalnız uzun sorguları kopyalarken satır sonlarından sonra otomatik boşluk eklemesi can sıkıcı
  • IntelliJ gibi IDE'ler ile syntax highlighting ve otomatik tamamlama birlikte kullanılabilir