Yönetici filtrelerini hızlandırmak için PostgreSQL üretilen sütunlar

Yönetici ekranları neden hızla yavaşlar ve karmaşıklaşır

Yönetici ekranları genellikle basit başlar: bir tablo, birkaç filtre, belki "en yeniler"e göre sıralama. Sonra gerçek işler gelir. Destek, müşteri aramasının isim, e‑posta ve telefonla çalışmasını ister. Satış, “son aktivite”ye göre sıralama ister. Finans, “vadesi geçmiş bakiye” ister. Her istek koşullar, join'ler ve ekstra hesaplamalar ekler.

Çoğu yönetici listesi aynı nedenle yavaşlar: her tık sorguyu değiştirir. Filtreleme ve sıralama, özellikle sorgunun hangi satırın eşleştiğine karar vermeden önce her satır için bir değeri hesaplaması gerektiğinde veritabanını çok sayıda satırı taramaya zorlayabilir.

Dönüm noktası genellikle WHERE ve ORDER BY ifadelerinin dolmasıdır. Düz bir sütuna filtrelemek yerine lower(email), date_trunc('day', last_seen_at) veya birden fazla durumu tek bir “kovaya” eşleyen bir CASE ifadesi ile filtrelersiniz. Bu ifadeler sadece daha yavaş değildir. SQL'i okunması zor, indekslenmesi güç ve hataya açık hale getirirler.

Karmaşık yönetici SQL'i genellikle birkaç tekrar eden desenden gelir:

• Birden fazla alanı farklı kurallarla kontrol eden tek bir “arama” girişi
• Türetilmiş bir değere göre sıralama (tam isim, öncelik skoru, “son anlamlı olay”)
• Ekranlar arasında kopyalanan iş kuralları (aktif vs pasif, ödenmiş vs vadesi geçmiş)
• Her yerde serpiştirilmiş küçük “yardımcı” düzeltmeler (trim, lower, coalesce)
• Aynı hesaplanan değerin liste, filtreler ve sıralamada tekrar kullanılması

Ekipler genellikle bunu uygulama katmanında gizlemeye çalışır: dinamik sorgu oluşturucular, koşullu join'ler veya kodda ön-hesaplama. Bu işe yarayabilir, ama mantığı UI ile veritabanı arasında böler; bu da yavaş sorguları debug etmeyi acı verici hale getirir.

Amaç basit: okunabilir kalan hızlı sorgular. Hesaplanan bir değer yönetici ekranlarında tekrar tekrar görünüyorsa, PostgreSQL üretilen sütunlar kuralı tek bir yerde tutarken veritabanının onu optimize etmesine izin verebilir.

Üretilen sütunlar basitçe

Üretilen sütun, değeri diğer sütunlardan hesaplanan normal bir tablo sütunudur. Değeri kendiniz yazmazsınız. PostgreSQL, tanımladığınız ifadeyi kullanarak onu doldurur.

PostgreSQL'de üretilen sütunlar saklanır (STORED). PostgreSQL, bir satır eklendiğinde veya güncellendiğinde değeri hesaplar, sonra disk üzerinde normal bir sütun gibi saklar. Yönetici ekranları için bu genellikle istediğiniz davranıştır: hızlı okuma ve hesaplanan değeri indeksleyebilme.

Bu, aynı hesaplamayı her sorgunun içinde yapmaktan farklıdır. Sürekli WHERE lower(email) = lower($1) yazmaya veya last_name || ', ' || first_name ile sıralamaya devam ederseniz, maliyeti tekrar tekrar ödersiniz ve SQL'iniz gürültülüleşir. Bir üretilen sütun, tekrar eden hesaplamayı tablo tanımına taşır. Sorgularınız daha basit olur ve sonuç her yerde tutarlı olur.

Kaynak veri değiştiğinde PostgreSQL, o satır için üretilen değeri otomatik olarak günceller. Uygulamanızın bunu senkronize etmeyi hatırlaması gerekmez.

Faydalı bir zihinsel model:

• Formülü bir kere tanımlayın.
• PostgreSQL onu yazılarda hesaplar.
• Sorgular normal bir sütun gibi okur.
• Saklandığı için indeksleyebilirsiniz.

Daha sonra formülü değiştirirseniz, bu bir şema değişikliği gerektirir. Mevcut satırların yeni ifadeye göre güncelleneceğini planlayın.

Filtreleme ve sıralama için hesaplanan alanların iyi kullanımları

Üretilen sütunlar, değer her zaman diğer sütunlardan türetiliyorsa ve sıkça filtreleme veya sıralama yapıyorsanız parıldar. Tek seferlik raporlar için o kadar yardımcı değildirler.

İnsanların gerçekten kullandığı arama-dostu alanlar

Yönetici araması nadiren “saf” aramadır. İnsanlar kutunun dağınık metin, tutarsız büyük/küçük harf ve ekstra boşluklarla başa çıkmasını bekler. Zaten normalleştirilmiş bir “arama anahtarı” saklarsanız, WHERE ifadeniz okunaklı kalır ve ekranlar arasında aynı davranışı gösterir.

İyi adaylar arasında birleşik tam isim, küçük harfe çevrilmiş ve kırpılmış arama metni, boşlukları daraltılmış temizlenmiş versiyon veya birden fazla alandan türetilmiş bir durum etiketi bulunur.

Örnek: Her sorguda lower(trim(first_name || ' ' || last_name)) tekrar etmek yerine full_name_key'i bir kez üreterek ona göre filtreleyin.

İnsanların sıraladığı şekilde eşleşen sıralama anahtarları

Sıralama, hesaplanan alanların en hızlı geri dönüş sağladığı yerdir, çünkü sıralama PostgreSQL'in ifadeleri birçok satır için değerlendirmesine zorlayabilir.

Yaygın sıralama anahtarları arasında sayısal sıralama (plan katmanı 1, 2, 3'e eşlenmiş), tek "en son aktivite" zaman damgası (iki zaman damgasının maksimumu gibi) veya metin olarak doğru sıralayan doldurulmuş bir kod bulunur.

Sıralama anahtarı düz bir indeksli sütun olduğunda, ORDER BY çok daha ucuz olur.

Hızlı filtreler için türetilmiş bayraklar

Yönetici kullanıcıları “Vadesi geçmiş” veya “Yüksek değer” gibi onay kutularını sever. Mantık stabil ve yalnızca satır verisine dayanıyorsa bunlar üretilen sütun olarak iyi çalışır.

Örneğin, müşteri listesi “Okunmamış mesaj var mı” ve “Vadesi geçmiş mi” gerektiriyorsa, unread_count > 0'den türetilmiş bir has_unread boolean ve due_date < now() ve paid_at is null gibi mantıktan türetilmiş bir is_overdue ile UI filtreleri basit koşullara indirgenir.

Üretilen sütunlar, indeksler ve diğer seçenekler arasından seçim yapmak

Yönetici ekranları üç şeye ihtiyaç duyar: hızlı filtreleme, hızlı sıralama ve aylar sonra bile okunabilir kalan SQL. Gerçek karar, hesaplamanın nerede yapılması gerektiğidir: tabloda, indeks içinde, görünümde (view) veya uygulama kodunda.

Üretilen sütunlar değerin gerçek bir sütun gibi davranmasını istediğinizde uygundur: referans verilmesi kolay, select'lerde görünür ve yeni filtreler eklerken unutulması zor. Normal indekslerle doğal olarak iyi eşleşirler.

İfade indeksleri (expression indexes) tablo tanımını değiştirmeden eklenebildiği için daha hızlı eklenebilir. Sadece hız önemsizse ve daha çirkin SQL kabul edilebilirse genellikle yeterlidir. Dezavantajı okunabilirliğin azalması ve planner'ın ifadenizi tam olarak eşlemesine bağımlı olmanızdır.

Görünümler (views) verinin paylaşılan bir “şeklini” istediğinizde yardımcı olur, özellikle yönetici listesi birden fazla tabloyu join ediyorsa. Ancak karmaşık view'lar pahalı işleri gizleyebilir ve debug için ikinci bir yer ekleyebilir.

Triggerlar normal bir sütunu senkronize tutabilir, ama hareketli parçalar ekler. Toplu güncellemeleri yavaşlatabilir ve hata ayıklamada gözden kaçabilirler.

Bazen en iyi seçenek uygulama tarafından doldurulan normal bir sütundur. Kullanıcılar bunu düzenleyebiliyorsa veya formül iş kararlarına göre sık sık değişiyorsa (sadece satır verisine dayanmayıp), açık tutmak daha net olabilir.

Kısa bir seçim yolu:

• Okunabilir sorgular ve yalnızca satır verisine dayanan stabil bir formül mü istiyorsunuz? Üretilen sütun kullanın.
• Tek bir filtre için hız istiyor ve gürültülü SQL umrunuzda değilse? İfade indeksi kullanın.
• Birçok yerde yeniden kullanılan, join'li, rapor gibi bir şekle mi ihtiyacınız var? Görünümü düşünün.
• Tablo-ötesi mantık veya yan etkiler mi gerekli? Önce uygulama mantığını, son çare olarak trigger'ı tercih edin.

Adım adım: üretilen sütun ekleyin ve sorguda kullanın

UI'de yavaş hissettiğiniz tek bir yönetici liste sorgusuyla başlayın. Ekranın en çok hangi filtreleri ve sıralamayı kullandığını yazın. Önce o sorguyu iyileştirin.

Tekrarlanan işi kaldıran bir hesaplanan alan seçin ve onu snake_case ile açıkça adlandırın, böylece başkaları ifadeyi tekrar okumadan ne tuttuğunu tahmin edebilir.

1) Üretilen sütunu (STORED) ekleyin

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

Gerçek satırlarda doğrulayın ve sonra indeks ekleyin:

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

Çıktı yanlışsa ifadeyi şimdi düzeltin. STORED PostgreSQL'in ekleme ve güncelleme sırasında değeri tutacağı anlamına gelir.

2) Yönetici ekranınıza uyan indeksi ekleyin

Ekranınız duruma göre filtreleyip isme göre sıralıyorsa, o kalıbı indeksleyin:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Yönetici sorgusunu yeni sütunu kullanacak şekilde güncelleyin

Önceden karmaşık bir ORDER BY'niz olabilir. Sonrasında açık olur:

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

Üretilen sütunları her gün kullanılan filtre ve sıralama parçaları için kullanın, nadir ekranlar için değil.

Gerçekçi örnek: hızlı kalan bir müşteri yönetici listesi

Tipik bir müşteri yönetici sayfası düşünün: bir arama kutusu, birkaç filtre (pasif, bakiye aralığı) ve sıralanabilen “Son görüldü” sütunu. Zamanla SQL LOWER(), TRIM(), COALESCE(), tarih hesapları ve tekrar eden CASE blokları ile okunması zor hale gelir.

Tekrarlanan ifadeleri üretilen sütunlara taşımak, onu hızlı ve okunabilir tutmanın bir yoludur.

Tablo ve üretilen sütunlar

customers tablosunda name, email, last_seen ve balance olduğunu varsayın. Üç hesaplanan alan ekleyin:

• search_key: basit aramalar için normalleştirilmiş bir metin
• is_inactive: tarih mantığını tekrar etmeden filtreleyebileceğiniz boolean
• balance_bucket: hızlı segmentasyon için bir etiket

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

Şimdi yönetici sorgusu UI gibi okunuyor.

Okunabilir filtre + sıralama

“Pasif müşteriler, en son aktivite önce” şöyle olur:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Basit bir arama ise:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Gerçek kazanç tutarlılıktadır. Aynı alanlar birden çok ekranı destekler:

• Müşteri liste arama kutusu search_key kullanır
• “Pasif müşteriler” sekmesi is_inactive kullanır
• Bakiye filtre düğmeleri balance_bucket kullanır

Yaygın hatalar ve tuzaklar

Üretilen sütunlar kolay bir zafer gibi görünebilir: matematiği tabloya koyun ve sorgularınızı temiz tutun. Sadece ekranın nasıl filtreleyip sıraladığına uyduklarında ve doğru indeksi eklediğinizde yardımcı olurlar.

En yaygın hatalar:

• İndekslemeden hızlanacağını varsaymak. Hesaplanan değer büyük ölçeklerde hızlı filtreleme veya sıralama için hala bir indekse ihtiyaç duyar.
• Çok fazla mantığı tek bir alana sıkıştırmak. Bir üretilen sütun mini bir program haline gelirse, insanlar ona güvenmeyi bırakır. Kısa tutun ve açık isim verin.
• Değişmeyen olmayan (non-immutable) fonksiyonlar kullanmak. PostgreSQL, saklanan üretilen sütun ifadesinin immutable olmasını ister. now() ve random() gibi şeyler beklentileri bozar ve genellikle izin verilmez.
• Yazma maliyetini görmezden gelmek. Insert ve update'ler hesaplanan değeri korumak zorundadır. Okumalar için hızlanma, importlar ve entegrasyonlar çok yavaşlarsa değmez.
• Yakın kopyalar oluşturmak. Bir veya iki standart desen (örneğin tek bir normalleştirilmiş anahtar) tutun; beş benzer sütun biriktirmeyin.

Eğer yönetici listeniz contains aramaları (ILIKE '%ann%') yapıyorsa, yalnızca bir üretilen sütun bunu kurtarmaz. Farklı bir arama yaklaşımına ihtiyacınız olabilir. Ancak günlük “filtrele ve sırala” iş yükleri için üretilen sütunlar ve doğru indeks performansı genellikle daha öngörülebilir hale getirir.

Yayına almadan önce hızlı kontrol listesi

Değişiklikleri yönetici listesine göndermeden önce hesaplanan değer, sorgu ve indeksin uyduğundan emin olun.

• Formül stabil ve bir cümleyle kolayca açıklanabiliyor mu?
• Sorgunuz gerçekten WHERE ve/veya ORDER BY içinde üretilen sütunu kullanıyor mu?
• İndeks gerçek kullanıma mı uyuyor, tek seferlik teste mi?
• Eski mantıkla kenar durumlarda (NULL, boş string, garip boşluklar, karışık harf) sonuçları karşılaştırdınız mı?
• Tablo meşgulse yazma performansını test ettiniz mi (importlar, arka plan güncellemeleri, entegrasyonlar)?

Sonraki adımlar: bunu yönetici ekranlarınıza uygulayın

Küçük, yüksek etkili bir başlangıç noktası seçin: insanların gün boyunca açtığı 2-3 ekran (siparişler, müşteriler, destek biletleri). Yavaş hissettirenleri not alın (tarih aralığı filtresi, “son aktivite”ye göre sıralama, birleşik isimle arama, durum etiketiyle filtreleme). Sonra ekranlar arasında yeniden kullanılabilecek kısa bir dizi hesaplanan alan standardize edin.

Kolay ölçülebilir ve geri alınabilir bir dağıtım planı:

• Açık isimlerle üretilen sütun(lar) ekleyin.
• Eskisini kısa süreli yan yana çalıştırın eğer mevcut mantığı değiştiriyorsanız.
• Ekranın ana filtre ve sıralamasına uyan indeksi ekleyin.
• Ekranın sorgusunu yeni sütunu kullanacak şekilde değiştirin.
• Öncesi ve sonrası ölçün (sorgu süresi ve taranan satırlar), sonra eski çözümü kaldırın.

AppMaster (appmaster.io) ile dahili yönetici araçları inşa ediyorsanız, bu hesaplanan alanlar paylaşılan bir veri modeline güzelce oturur: veritabanı kuralı taşır ve UI filtreleri ifadeleri tekrar etmek yerine doğrudan anlaşılır bir alan adına işaret eder.