PostgreSQL VACUUM ve Autovacuum: Bloat Nasıl Ölçülür?

VACUUM ve autovacuum nedir? Hızlı kontrol sorguları

-- En çok ölü satırı olan tablolar
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Temizliği engelleyebilecek uzun süredir açık transaction'lar
SELECT pid, state, xact_start, now() - xact_start AS sure
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Autovacuum ne zaman çalışır? Bir tablo için ölü satır sayısı autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × satır sayısı değerini aştığında (varsayılanlar 50 ve 0,2). Analyze için benzer eşik autovacuum_analyze_threshold (50) ve autovacuum_analyze_scale_factor (0,1) ile hesaplanır. Büyük tablolarda yüzde 20 çok geç kalabildiği için tablo bazında daha düşük değerler verilir.

VACUUM mu, VACUUM FULL mu? Normal VACUUM tabloyu kilitlemeden çalışır ve alanı tablo içinde yeniden kullanılabilir yapar. VACUUM FULL tabloyu baştan yazar, alanı işletim sistemine geri verir; ancak süresince tabloya erişimi tamamen kilitler, bu yüzden bakım penceresinde planlanmalıdır.

Yazma tarafındaki gecikmeler için WAL ve checkpoint analizi, indeks tasarımı için indeksleme rehberi yazılarımıza bakabilirsiniz.

İlgili okumalar: Veritabanı ve SQL Eğitimi konu sayfası · Hibernate ve JPA Eğitimi

Veritabanı optimizasyonu için bloat'u ölçerek başlayın

İyi bir veritabanı eğitimi veya sql eğitimi, yüksek disk kullanımı ile gerçek bloat'u ayırmayı öğretmelidir. PostgreSQL'de pg_stat_user_tables içindeki n_dead_tup değeri tahmindir; autovacuum henüz analyze çalıştırmadıysa yanıltıcı olabilir. İlk envanteri son vacuum zamanları, canlı satır tahmini ve ölü satır tahmini ile çıkarın. n_dead_tup / n_live_tup oranı tek başına karar kriteri değildir: 20 milyon satırlı bir tabloda yüzde 2 ölü satır bile yüz binlerce gereksiz heap tuple ve ek disk okuması demektir.

SELECT
    relid::regclass AS tablo,
    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
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 20;

Tahmini doğrulamak için bakım penceresinde pgstattuple eklentisinin yaklaşık taramasını kullanın. pgstattuple_approx, tüm tabloyu satır satır taramak yerine sınırlı sayıda blok örneklediği için büyük production tablolarında daha güvenlidir. Buna rağmen disk I/O üreteceğinden, sonucu yoğun saatlerde onlarca tablo üzerinde paralel çalıştırmayın. Eklenti kurulumu çoğu kurulumda yüksek yetki gerektirir; uygulama rolüne CREATE EXTENSION ayrıcalığı vermek yerine DBA tarafından kontrollü kurulmalıdır.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_len / 1024 / 1024 AS table_mb,
    approx_tuple_count,
    round(dead_tuple_percent::numeric, 2) AS dead_tuple_pct,
    round(approx_free_percent::numeric, 2) AS free_space_pct
FROM pgstattuple_approx('public.orders');

Autovacuum'un temizleyemediği eski transaction'ları bulun

VACUUM, fiziksel olarak ölü görünen bir tuple'ı ancak hiçbir aktif snapshot onu göremiyorsa kaldırabilir. Uzun süren bir transaction'ın backend_xmin değeri eski kaldığında, PostgreSQL bu transaction'ın görünür sayabileceği tuple'ları silmez. Bu nedenle autovacuum logunda sık vacuum görmek, bloat'un gerçekten küçüldüğü anlamına gelmez. Aşağıdaki sorguda age(backend_xmin) değeri yüksek ve xact_age uzun oturumlar, investigation listesine alınmalıdır.

SELECT
    pid,
    usename,
    application_name,
    state,
    now() - xact_start AS xact_age,
    age(backend_xmin) AS xmin_age,
    left(query, 200) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC, xact_start ASC;

İkinci yaygın engel replication slot'tur. Tüketilmeyen logical replication slot'ları WAL birikimiyle bilinse de, slotun xmin veya catalog_xmin değeri eski kaldığında vacuum temizliğini de sınırlar. inactive olan slotu hemen silmek yerine önce abone tüketicisinin geri getirilebilirliğini doğrulayın; slot silinirse abonenin yeniden snapshot alması gerekebilir. Bu kontrol, bloat analizi sırasında sık atlanan production edge case'lerden biridir.

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    xmin,
    catalog_xmin,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 AS retained_wal_mb
FROM pg_replication_slots
ORDER BY retained_wal_mb DESC NULLS LAST;

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

Varsayılan vacuum tetikleme hesabı kabaca autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples şeklindedir. Örneğin 50 milyon satırlı, sürekli UPDATE alan bir orders tablosunda scale factor 0.2 ise vacuum yaklaşık 10 milyon ölü tuple oluşana kadar bekleyebilir. Bu gecikme heap sayfa sayısını, indeks pointer'larını ve sorguların buffer okumasını büyütür. Küçük ve sık değişen tablolar için cluster geneli ayarı değiştirmek yerine storage parameter'ları tablo seviyesinde ayarlayın.

ALTER TABLE public.orders SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_threshold = 2000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit = 2000
);

SELECT
    c.relname,
    c.reloptions
FROM pg_class c
WHERE c.oid = 'public.orders'::regclass;

Bu değişiklikte cost_limit'i yükseltmenin mekanizması önemlidir: vacuum worker daha fazla maliyet bütçesiyle daha az uyur ve temizliği daha erken bitirir; karşılığında I/O yarışması artabilir. Bu yüzden önce tek tabloda uygulayın, disk gecikmesini işletim sistemi metriği veya izleme sisteminizdeki disk queue depth ile izleyin. INSERT ağırlıklı append-only tabloda güncelleme kaynaklı dead tuple az olabilir; sunucunuz destekliyorsa autovacuum_vacuum_insert_threshold ve autovacuum_vacuum_insert_scale_factor ayarları visibility map güncelliği için ayrıca değerlendirilmelidir.

Bu ayarlar database indexleme kararlarından bağımsız değildir. Vacuum, indekslerden artık geçersiz olan TID referanslarını temizleyebilir fakat btree dosyasını her zaman küçültmez. UPDATE ile değiştirilen indeksli kolonlar hem yeni indeks kaydı üretir hem de eski kaydı vacuum'a bırakır; bu nedenle yalnızca okunma istatistiği zayıf olan bir indeksin yazma maliyetini pg_stat_user_indexes ve pg_stat_all_indexes ile ayrı ölçmek gerekir.

Database indexleme sonrası bloat ve sorgu maliyetini doğrulayın

Önce-sonra karşılaştırması için aynı sorgu parametreleri, aynı eşzamanlılık seviyesi ve yeterince uzun ölçüm penceresi kullanın. Tek bir EXPLAIN çıktısı cache sıcaklığına göre yanıltıcı olabilir. Değişiklik öncesi ve sonrası 5 dakikalık aynı pgbench senaryosunu çalıştırın; işlem/saniye, ortalama latency ve hata oranını kaydedin. Uygulama sorgusunu workload.sql dosyasına koyarak ölçümün sentetik ama tekrar edilebilir olmasını sağlayın.

# Önce ve sonra aynı komutu çalıştırın.
pgbench   -h db.internal   -U benchmark_user   -d appdb   -c 32 -j 8 -T 300 -P 30   -f workload.sql   -r

Sorgu seviyesinde heap ve indeks erişimini ayırmak için gerçekçi bir müşteri kimliğiyle EXPLAIN (ANALYZE, BUFFERS) alın. Shared Hit Blocks artışı tek başına kötü değildir; kritik sinyal, aynı satır sayısı için yükselen shared read, temp read/write ve actual time değeridir. Vacuum sonrasında plan değiştiyse, pg_stat_statements içinden calls, mean_exec_time ve total_exec_time değerlerini aynı zaman pencerelerinde kıyaslayın; sadece en hızlı tek çalıştırmayı raporlamayın.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, status, created_at
FROM public.orders
WHERE customer_id = 481516
ORDER BY created_at DESC
LIMIT 50;

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    shared_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE query LIKE '%FROM public.orders%'
ORDER BY total_exec_time DESC
LIMIT 10;

İndeks bloat'u şüpheli ise pgstatindex ile avg_leaf_density ve leaf_fragmentation değerlerini kaydedin. Düşük leaf density, özellikle range scan yapan büyük btree indekslerinde daha fazla sayfa ziyareti anlamına gelir. Gerçek bir yeniden inşa gerekiyorsa REINDEX INDEX CONCURRENTLY komutu yazma erişimini tamamen kesmeden çalışabilir, ancak transaction block içinde çalıştırılamaz ve geçici olarak ek disk alanı ister. Önce tablo bloat'unu ve uzun transaction engellerini çözmeden reindex yapmak, kısa sürede aynı şişkinliğin tekrar oluşmasına yol açabilir.

SELECT *
FROM pgstatindex('public.orders_customer_created_idx');

REINDEX INDEX CONCURRENTLY public.orders_customer_created_idx;

SQL Server eğitimi ve nosql eğitimi bağlamında aynı hatayı yapmayın

Bir sql server eğitimi sırasında öğrenilen DMV'leri PostgreSQL'e, PostgreSQL autovacuum ayarlarını da SQL Server'a taşımayın. SQL Server tarafında sürüm zinciri ve ghost kayıt davranışını incelemek için sys.dm_db_index_physical_stats ile avg_fragmentation_in_percent, page_count ve ghost cleanup etkisini ayrı ölçün. PostgreSQL'deki n_dead_tup ile SQL Server fragmentation yüzdesi aynı metriği ifade etmez; ilki MVCC tuple temizliği, ikincisi çoğunlukla indeks sayfa düzeniyle ilgilidir.

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.page_count,
    ips.avg_fragmentation_in_percent
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;

Bir nosql eğitimi bağlamında da MongoDB WiredTiger compaction veya LSM tabanlı motorlardaki compaction, PostgreSQL VACUUM ile eş tutulmamalıdır. Örneğin MongoDB'de db.collection.stats() ile storageSize ve totalIndexSize izlenebilir; compact operasyonu çalışma yükü ve replikasyon davranışı açısından ayrı planlanmalıdır. Veritabanı motorunun dosya alanını geri verme mekanizmasını doğrulamadan, yalnızca disk kullanım grafiğine bakarak bakım komutu çalıştırmak production riski yaratır.

Sık Sorulan Sorular

PostgreSQL'de VACUUM nedir?

VACUUM, güncelleme ve silme sonrası kalan ölü satırların alanını yeniden kullanılabilir hale getiren bakım komutudur. Ayrıca görünürlük haritasını günceller ve transaction kimliği taşmasını (wraparound) önlemeye yardım eder.

Autovacuum ne zaman çalışır?

Ölü satır sayısı autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × satır sayısı değerini aştığında çalışır; varsayılanlar 50 ve 0,2'dir. Büyük ve sık güncellenen tablolarda bu eşikler tablo bazında düşürülebilir.

VACUUM FULL ne zaman kullanılmalı?

Yalnızca tablo ciddi şekilde şişmişse ve alanın işletim sistemine geri verilmesi gerekiyorsa. VACUUM FULL tabloyu yeniden yazar ve süresince tabloya erişimi tamamen kilitler; bu nedenle bakım penceresinde planlanmalıdır.

PostgreSQL bloat oranı hangi sorguyla ölçülür?

İlk sıralama için pg_stat_user_tables içindeki n_dead_tup, n_live_tup ve last_autovacuum alanlarını kullanın. Karar vermeden önce şüpheli tablolarda pgstattuple eklentisinin pgstattuple_approx fonksiyonuyla ölü satır yüzdesini, indekslerde pgstatindex ile yaprak doluluk oranını ölçün.