PostgreSQL İndekslerinizin Gereksiz Yüklenmesini Önleyin: Kolay İzleme Aracı

PostgreSQL’in pg_stat_user_indexes görüntüleme komutuna aşina olanlar, indekslerinizin ne kadar sık kullanıldığını görebilirsiniz. Ancak bu veriler, yavaş çalışan bir sorgu için eklediğiniz indeksin gerçekten kullanılıp kullanılmadığını ya da boş yere bakım yapılan indekslerinizi göstermez. Üç dosyalık basit bir analiz aracıyla, gereksiz indeks yükünü ortadan kaldırmanın yolunu keşfedin.

PostgreSQL’in Eksikliği: Ayrıntılı İndeks İzleme

PostgreSQL’in pg_stat_user_indexes tablosuna göz atmak, indekslerinizin performansını anlamak için yeterli değildir. Bu tablo sadece:

• idx_scan – indeksin kaç kez taranmış olduğunu
• idx_tup_read – indeks üzerinden kaç kayıt okunduğunu
• idx_tup_fetch – indeksten alınan kayıtların heap’tan getirildiğini gösterir.

Ancak bu verilerden yoksundur:

• Hangi sorguların hangi indeksleri kullandığını
• Yavaş çalışan bir sorgu için oluşturduğunuz indeksin gerçekten kullanılıp kullanılmadığını
• Kullanılmayan indekslerin her INSERT işlemi için ne kadar ek yük oluşturduğunu
• Planlayıcının kompozit indeksleri tercih edip tek sütunlu indeksleri gereksiz hale getirdiğini
• Aynı sorgunun farklı indeksler üzerinden çalışmaya başladığını

Bu boşluk, özellikle yoğun veritabanlarında çalışırken tahmin yürütmekten başka bir şey değildir.

Üç Dosyalık İzleme Aracı: Nasıl Çalışır?

Üç bileşenden oluşan basit bir araçla, PostgreSQL’in standart istatistiklerinin ötesine geçebilirsiniz. Bu araç, sorguları doğrudan çalıştırmak yerine sararak planlarını, performansını ve kullanılan indeksleri kaydeder. İşte nasıl çalıştığı:

1. Sorguları Saran Kapsayıcı: query-logger.ts

Temel fikir, sorguları doğrudan çalıştırmak yerine bir kılıf içinde çalıştırmaktır. Bu kılıf, sorgunun planını yakalar, çalışma süresini ölçer ve sonuçları asenkron olarak kaydeder. Bu yaklaşımın temel avantajları şunlardır:

• pg_stat_statements veya auto_explain gibi yerleşik çözümlerden farklı olarak, hem sorgunun performansını hem de kullanılan indeksleri tek bir yerde toplar.
• Kullanıcı arayüzünde gecikme yaşanmasını engellemek için kayıt işlemi arka planda gerçekleşir.

#### Neden EXPLAIN (FORMAT JSON)?

EXPLAIN (FORMAT JSON) sorgunun planını çalıştırmadan elde etmenizi sağlar. Bu, çoğu sorguda milisaniyeler içinde tamamlanır ve planlayıcının hangi indeksi seçtiğini gösteren bir ağaç yapısı sunar. Örneğin:

async function getPlan(sql: string, params: unknown[]) {
  const result = await db.query(`EXPLAIN (FORMAT JSON) ${sql}`, params);
  return result.rows[0]["QUERY PLAN"][0];
}

function extractIndexes(plan: any): string[] {
  const indexes: string[] = [];
  const walk = (node: any) => {
    if (node["Index Name"]) indexes.push(node["Index Name"]);
    (node["Plans"] ?? []).forEach(walk);
  };
  walk(plan["Plan"]);
  return indexes;
}

Bu fonksiyonlar, sorgunun hangi indeksleri kullandığını ve tam tarama (seq scan) yapıp yapmadığını belirlemenize olanak tanır.

#### Kullanıcı Tanımlı Kapsayıcı Fonksiyonu

Aşağıdaki gibi bir loggedQuery fonksiyonu, sorguları izler ve sonuçları query_logs tablosuna kaydeder:

import { db } from "@/lib/db";
import { supabaseAdmin } from "@/lib/supabase/admin";

interface QueryMeta {
  endpoint: string;
  tableName: string;
  storeId?: string;
}

export async function loggedQuery(
  sql: string,
  params: unknown[],
  meta: QueryMeta
) {
  const plan = await getPlan(sql, params);
  const start = Date.now();
  const result = await db.query(sql, params);
  const duration = Date.now() - start;

  supabaseAdmin
    .from("query_logs")
    .insert({
      query_hash: hash(sql),
      sql_preview: sql.slice(0, 200),
      table_name: meta.tableName,
      endpoint: meta.endpoint,
      store_id: meta.storeId,
      indexes_used: extractIndexes(plan),
      seq_scan: planContainsSeqScan(plan),
      planning_ms: plan["Planning Time"],
      execution_ms: duration,
      rows_returned: result.rowCount,
    })
    .then(() => {})
    .catch(() => {});

  return result;
}

Bu fonksiyonun en önemli özelliği, kayıt işleminin asenkron olarak gerçekleşmesidir. Kullanıcı deneyimini etkilememek için herhangi bir hata durumunda bile sorgunun çalışmaya devam etmesi sağlanır.

2. Veri Tabanı Tablosu: query_logs

Bu tablo, sorguların performans verilerini ve kullanılan indeksleri saklar. Temel sütunları şunlardır:

• id – benzersiz tanımlayıcı
• query_hash – sorgunun benzersiz özeti (SHA-1)
• sql_preview – sorgunun ilk 200 karakteri
• table_name – sorgunun çalıştığı tablo
• endpoint – sorgunun tetiklendiği API uç noktası
• store_id – çoklu kullanıcılı sistemlerde mağaza kimliği
• indexes_used – kullanılan indekslerin listesi
• seq_scan – tam tarama yapılıp yapılmadığını belirten bayrak
• planning_ms – planlama süresi
• execution_ms – çalışma süresi
• rows_returned – döndürülen kayıt sayısı

3. Verilerin Görselleştirilmesi

Kaydedilen veriler, indeks kullanımını analiz etmek ve gereksiz bakım yükünü tespit etmek için kullanılabilir. Örneğin:

• Kullanılmayan indeksler: idx_scan değeri sıfır olan indeksler, performans açısından gereksizdir ve kaldırılabilir.
• Yavaş sorgular: uzun execution_ms değerine sahip sorguların optimize edilmesi gerekir.
• Tam tarama yapılan sorgular: seq_scan değeri true olan sorgular, indeks eksikliğinden kaynaklanıyor olabilir.

İzleme Aracının Avantajları

Bu basit araçla elde edebileceğiniz faydalar şunlardır:

• Veritabanı performansının şeffaflığı: Hangi indekslerin kullanılmadığını ve hangi sorguların yavaş çalıştığını net bir şekilde görürsünüz.
• İndeks yönetiminin kolaylığı: Gereksiz indeksleri kaldırarak depolama ve performans maliyetlerini düşürürsünüz.
• Sorgu optimizasyonunun rehberliği: Yavaş sorguları belirleyerek, planlayıcının neden belirli bir indeksi tercih ettiğini analiz edebilirsiniz.

PostgreSQL’in standart istatistikleri, yalnızca yüzeysel bir bakış sunar. Ancak bu basit araçla, veritabanı performansını daha derinlemesine anlamak ve gereksiz yüklerden kurtulmak mümkün hale gelir. Üç dosyalık bu çözüm, hem geliştirme hem de üretim ortamlarında etkili bir izleme aracı olarak kullanılabilir.

Veritabanı performansınızı iyileştirmek için bu basit adımları uygulayarak, indekslerinizi optimize edebilir ve sistem kaynaklarını daha verimli kullanabilirsiniz.