Veritabanı Optimizasyonu: PostgreSQL Autovacuum ve Bloat Kontrolü

Veritabanı optimizasyonu için önce bloat ve vacuum borcunu ölçün

PostgreSQL'de UPDATE veya DELETE, eski satırı yerinde değiştirmez; eski sürüm dead tuple olarak kalır ve yeni sürüm eklenir. Autovacuum bu sürümleri temizleyene kadar heap sayfa sayısı, indeks boyutu ve cache miss oranı büyür. Tahminle ayar değiştirmeden önce pg_stat_user_tables görünümündeki dead tuple sayısını, vacuum gecikmesini ve tablo tarama davranışını toplayın. Bu yaklaşım, veritabanı eğitimi sırasında sık atlanan bir ayrıntıyı görünür kılar: n_dead_tup kesin bir dead row sayısı değil, ANALYZE ile yenilenen istatistiksel tahmindir.

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  analyze_count
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 30;

Ölçümü en az bir normal trafik periyodu boyunca tekrarlayın ve sonuçları zaman serisine yazın. Ardından aday bir sorguda EXPLAIN (ANALYZE, BUFFERS, WAL) çalıştırın. BUFFERS içindeki yüksek shared read değeri diskten sayfa okunduğunu, WAL ise değişikliğin yazma günlüğü maliyetini gösterir. Aynı parametrelerle alınmış before-after planında yalnızca toplam süreyi değil, Heap Fetches, shared read ve satır tahmin hatasını karşılaştırın; sıcak cache'de tek seferlik süre ölçümü yeterli değildir.

Veritabanı performans ayarlama: tablo bazlı autovacuum eşikleri

Varsayılan tetikleme hesabı kabaca autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples şeklindedir. Örneğin 80 milyon satırlık bir olay tablosunda scale_factor=0.2, vacuum başlamadan yaklaşık 16 milyon dead tuple oluşmasına izin verir. Sık UPDATE edilen büyük tabloda bu eşik, indeks ve heap büyümesini uzun süre taşımak anlamına gelir. Cluster geneli yerine yalnızca yazma yoğun tablolarda daha düşük eşik kullanın; aksi halde küçük ve seyrek güncellenen tablolarda gereksiz worker baskısı oluşabilir.

ALTER TABLE billing.invoice
  SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_analyze_threshold = 2000,
    autovacuum_vacuum_cost_limit = 2000
  );

SELECT relname, reloptions
FROM pg_class
WHERE oid = 'billing.invoice'::regclass;

Bu değişiklikten sonra PostgreSQL loglarında log_autovacuum_min_duration = 0 ile tamamlanan vacuum kayıtlarını geçici olarak görünür yapın ve pg_stat_progress_vacuum üzerinden aktif işi izleyin. Before aşamasında dead_pct, sorgunun shared read değeri ve tablo-İndeks boyutunu kaydedin; after aşamasında aynı pencere için bunları karşılaştırın. Autovacuum'un bir tabloyu vakumlaması fiziksel dosyayı çoğu durumda küçültmez, boş alanı yeni INSERT ve UPDATE'ler için tekrar kullanılabilir yapar. Disk alanını işletim sistemine geri vermek için VACUUM FULL gerekir, ancak bu komut tabloyu yeniden yazar ve uzun süreli ACCESS EXCLUSIVE kilidi alır; üretim tablosunda körlemesine çalıştırmak yaygın ve pahalı bir hatadır.

Database indexleme ile bloat ilişkisini plan üzerinden inceleyin

Database indexleme yalnızca okuma maliyeti değildir. Her UPDATE, değişen sütun bir indekste yer alıyorsa yeni indeks girdileri üretir; eski girdiler vacuum bekler. PostgreSQL'de HOT update, indekslenmiş hiçbir sütun değişmiyorsa aynı heap sayfasındaki boş alana yeni satır sürümünü koyabilir ve indeks güncellemesini atlayabilir. Bu nedenle her durum sütununa indeks eklemek, seçicilik faydası sağlamadığı halde write amplification ve indeks bloat üretebilir.

SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_update_pct
FROM pg_stat_user_tables
WHERE relname = 'invoice';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM billing.invoice
WHERE customer_id = 42
  AND status = 'OPEN'
ORDER BY created_at DESC
LIMIT 50;

Düşük hot_update_pct gördüğünüzde önce güncellenen sütunları ve indeks tanımlarını eşleştirin. Sorgu planı gerçekten customer_id, status, created_at sıralamasını kullanıyorsa, kapsayan bir indeks aday olabilir; fakat indeks eklemeden önce pg_stat_user_indexes.idx_scan ile mevcut indeksin kullanımını doğrulayın. İndeks sadece sonuçtaki total_amount için heap'e dönüyorsa ve tablo çok okunuyorsa INCLUDE (total_amount) değerlendirilebilir. Bunun çalışması için ilgili heap sayfalarının visibility map'te all-visible olması gerekir; vacuum geride kaldığında index-only scan planı seçilse bile Heap Fetches yükselir. Yani index-only scan sonucu, autovacuum sağlığıyla doğrudan bağlantılıdır.

SQL eğitimi ve SQL Server eğitimi açısından aynı problemi ayırın

İleri seviye sql eğitimi, her veritabanı motorunda aynı bakım komutunun bulunduğunu varsaymamalıdır. PostgreSQL'in MVCC sürüm temizliği autovacuum ile yürürken, SQL Server tarafında row-versioning kullanılan iş yüklerinde version store baskısı tempdb üzerinde ayrıca gözlenir. SQL Server eğitimi kapsamında ilk teşhis için Query Store ve DMV'leri birlikte kullanın; örneğin indeks fiziksel istatistiğini tek başına yeniden oluşturma kararı olarak değil, sorgu CPU ve logical read değişimiyle bağlayın.

SELECT
  OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
  OBJECT_NAME(ips.object_id) AS table_name,
  i.name AS index_name,
  ips.avg_fragmentation_in_percent,
  ips.page_count
FROM sys.dm_db_index_physical_stats
  (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
  ON i.object_id = ips.object_id
 AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Bu DMV çıktısında yüzde yüksek diye otomatik REBUILD yapmak doğru değildir: az sayfalı bir indeks için ölçüm gürültülü olabilir, çevrimiçi olmayan bakım kilit yaratabilir ve log dosyası büyüyebilir. Önce Query Store'da hedef sorgunun avg_logical_io_reads ve süre dağılımını bakım öncesi-sonrası aynı parametre sınıfında karşılaştırın. PostgreSQL'deki bloat için de SQL Server'daki fragmentation için de doğru soru 'oran kaç' değil, 'bu fiziksel yapı hangi sorgunun kaç logical read yapmasına neden oluyor' olmalıdır.

NoSQL eğitimi perspektifi: TTL silmeleri ve tombstone birikimi

Nosql eğitimi alan ekipler PostgreSQL autovacuum mantığını MongoDB veya Cassandra'ya doğrudan taşıyamaz. Cassandra'da TTL ve DELETE işlemleri tombstone üretir; tombstone'lar compaction ile hemen kaybolmaz ve replica onarımı için gc_grace_seconds penceresinde tutulur. Bu pencere dolmadan agresif temizleme yapmak, uzun süredir kapalı bir replica geri geldiğinde silinmiş verinin yeniden görünmesine yol açabilir. Bu edge case, tombstone sayısını azaltmak uğruna veri tutarlılığını bozan klasik bir operasyondur.

Cassandra'da somut başlangıç ölçümü olarak nodetool tablestats keyspace.table çıktısındaki SSTable sayısını, disk alanını ve read latency değerlerini alın; ardından TTL ağırlıklı tabloda TimeWindowCompactionStrategy kullanıp aynı zaman aralığında karşılaştırın. MongoDB'de ise db.collection.stats({ indexDetails: true }) ile indeks boyutunu ve db.collection.aggregate([{ $indexStats: {} }]) ile erişim sayacını inceleyin. Kullanılmayan bir indeks hem yazma yoluna ek maliyet koyar hem de disk çalışma setini büyütür; motor farklı olsa da ölçüm ilkesi PostgreSQL'deki database indexleme kararına benzer.

Sık Sorulan Sorular

Veritabanı optimizasyonu için PostgreSQL autovacuum ne zaman tablo bazında ayarlanmalı?

Yüksek UPDATE veya DELETE alan büyük tablolarda n_dead_tup oranı düzenli yükseliyor ve last_autovacuum uzun süre geride kalıyorsa tablo bazlı ayar yapın. Önce pg_stat_user_tables, pg_stat_progress_vacuum ve EXPLAIN (ANALYZE, BUFFERS) ile baz çizgisi alın. Sonra autovacuum_vacuum_scale_factor değerini örneğin 0.2'den 0.01'e indirip aynı trafik penceresinde dead_pct, shared read ve tablo boyutunu karşılaştırın.

SQL eğitimi sırasında VACUUM FULL neden üretimde risklidir?

VACUUM FULL tabloyu yeniden yazar ve ACCESS EXCLUSIVE kilidi alır. Bu kilit, hedef tabloya gelen SELECT dahil birçok işlemi bekletebilir. Amacınız yalnızca dead tuple alanını yeni yazılar için kullanılabilir yapmaksa normal VACUUM yeterlidir. Gerçek disk alanı iadesi gerekiyorsa bakım penceresi planlayın veya pg_repack gibi çevrimiçi yeniden paketleme araçlarını kilit davranışı ve ek disk gereksinimiyle birlikte değerlendirin.

Database indexleme HOT update oranını nasıl etkiler?

UPDATE edilen bir sütun herhangi bir indeksin anahtarında bulunuyorsa PostgreSQL genellikle HOT update yapamaz ve indeks girdilerini de günceller. pg_stat_user_tables içindeki n_tup_hot_upd ile n_tup_upd oranını izleyin. Düşük oran gördüğünüzde, yalnızca nadir kullanılan filtreler için eklenmiş indeksleri pg_stat_user_indexes.idx_scan üzerinden doğrulayın; kullanılmayan indeksi kaldırmak write amplification'ı azaltabilir.

SQL Server eğitimi için indeks fragmentation eşiği tek başına yeterli mi?

Hayır. sys.dm_db_index_physical_stats çıktısındaki fragmentation oranını page_count ile birlikte okuyun, ardından Query Store'da hedef sorgunun logical read, CPU ve süre metriklerini bakım öncesi-sonrası karşılaştırın. Yüksek fragmentation ama değişmeyen logical read, bakımın uygulama gecikmesine ölçülebilir katkı vermediğini gösterebilir.

NoSQL eğitimi kapsamında Cassandra tombstone temizliği nasıl izlenir?

nodetool tablestats ile SSTable sayısı, disk kullanımı ve okuma gecikmesini kaydedin; ayrıca uygulama metriklerinde tombstone-heavy sorguları izleyin. TTL ağırlıklı veride TimeWindowCompactionStrategy zaman pencerelerine göre SSTable'ları gruplayarak eski verinin birlikte temizlenmesini kolaylaştırır. gc_grace_seconds değerini replica repair düzeniniz doğrulanmadan düşürmeyin.