PostgreSQL Autovacuum SLO Tuning: Ako nastaviť vacuum pre 200M riadkov a 5k UPSERT/s

Autovacuum je tichy upratovac, kym nepride do roboty. “Prečo je databáza pomalá? Včera fungovala.” Pozriem na pg_stat_user_tables a vidím 50 miliónov dead tuples. Autovacuum beží, ale nestíha. Klasika.

Problém: Autovacuum má defaultné nastavenia pre OLTP databázu s pár tisíc riadkami. Pri 200M riadkoch a 5000 UPSERT/s tieto defaults zlyhávajú.

Testované na: PostgreSQL 16.1, Ubuntu 22.04, 32GB RAM, NVMe SSD, pgbench + custom workload

Prečo Default Autovacuum Nestíha

Matematika Dead Tuples

Pri UPDATE v PostgreSQL:

1. Starý riadok sa označí ako “dead” (nie je fyzicky zmazaný)
2. Nový riadok sa zapíše
3. Dead tuples zaberajú miesto a spomaľujú index scany

Dead tuples za hodinu = UPDATE rate × 3600
5000 UPSERT/s × 3600 = 18 miliónov dead tuples/hodinu

Default Trigger Thresholds

-- Default nastavenia
autovacuum_vacuum_threshold = 50        -- min dead tuples
autovacuum_vacuum_scale_factor = 0.2    -- 20% tabuľky

-- Pre 200M riadkovú tabuľku:
-- Trigger = 50 + (200M × 0.2) = 40,000,050 dead tuples
-- Pri 5k UPSERT/s = vacuum každých 2.2 hodiny

Problém: Kým sa vacuum spustí, máte 40M dead tuples = bloat, pomalé queries, zbytočné I/O.

SLO-Driven Vacuum Nastavenie

Krok 1: Definuj SLO

## Vacuum SLO
- Max dead tuples: 5% veľkosti tabuľky (10M pre 200M tabuľku)
- Max bloat ratio: 20%
- Vacuum frequency: každých 30 minút pri vysokej záťaži

Krok 2: Vypočítaj Vacuum Rate

# vacuum_rate_calculator.py
from dataclasses import dataclass

@dataclass
class VacuumSLO:
    table_rows: int
    update_rate_per_sec: int
    max_dead_tuple_ratio: float  # napr. 0.05 = 5%
    target_vacuum_interval_min: int

def calculate_vacuum_settings(slo: VacuumSLO) -> dict:
    # Max povolené dead tuples
    max_dead = int(slo.table_rows * slo.max_dead_tuple_ratio)

    # Dead tuples za target interval
    dead_per_interval = slo.update_rate_per_sec * slo.target_vacuum_interval_min * 60

    # Scale factor aby sa vacuum spustil včas
    # trigger = threshold + (scale_factor × table_rows)
    # Chceme: trigger = dead_per_interval
    # scale_factor = (trigger - threshold) / table_rows

    threshold = 1000  # nízky fixed threshold
    scale_factor = (dead_per_interval - threshold) / slo.table_rows

    return {
        "autovacuum_vacuum_threshold": threshold,
        "autovacuum_vacuum_scale_factor": round(max(0.001, scale_factor), 4),
        "expected_trigger_dead_tuples": dead_per_interval,
        "max_allowed_dead_tuples": max_dead
    }

# Príklad pre našu tabuľku
slo = VacuumSLO(
    table_rows=200_000_000,
    update_rate_per_sec=5000,
    max_dead_tuple_ratio=0.05,
    target_vacuum_interval_min=30
)

settings = calculate_vacuum_settings(slo)
print(settings)
# {'autovacuum_vacuum_threshold': 1000,
#  'autovacuum_vacuum_scale_factor': 0.045,
#  'expected_trigger_dead_tuples': 9000000,
#  'max_allowed_dead_tuples': 10000000}

Krok 3: Aplikuj Per-Table Settings

-- Nastavenie pre konkrétnu tabuľku
ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.045,
    autovacuum_vacuum_cost_limit = 2000,  -- agresívnejšie
    autovacuum_vacuum_cost_delay = 2      -- menej pauzy
);

-- Verifikácia
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'high_churn_table';

Monitoring: pg_stat Metriky

Query pre Vacuum Health Dashboard

-- vacuum_health.sql
WITH table_stats AS (
    SELECT
        schemaname,
        relname,
        n_live_tup,
        n_dead_tup,
        CASE WHEN n_live_tup > 0
            THEN round(100.0 * n_dead_tup / n_live_tup, 2)
            ELSE 0
        END as dead_tuple_ratio,
        last_vacuum,
        last_autovacuum,
        vacuum_count,
        autovacuum_count,
        EXTRACT(EPOCH FROM (now() - last_autovacuum))/60 as minutes_since_vacuum
    FROM pg_stat_user_tables
    WHERE n_live_tup > 100000  -- len veľké tabuľky
)
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    dead_tuple_ratio || '%' as dead_ratio,
    round(minutes_since_vacuum) || ' min' as since_vacuum,
    CASE
        WHEN dead_tuple_ratio > 20 THEN 'CRITICAL'
        WHEN dead_tuple_ratio > 10 THEN 'WARNING'
        ELSE 'OK'
    END as status
FROM table_stats
ORDER BY dead_tuple_ratio DESC;

Bloat Estimation

-- Reálny bloat (vyžaduje pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_name,
    pg_size_pretty(table_len) as table_size,
    round(100 - (tuple_len::float / table_len * 100), 2) as bloat_pct,
    pg_size_pretty(table_len - tuple_len) as wasted_space
FROM (
    SELECT
        'high_churn_table'::text as table_name,
        (pgstattuple('high_churn_table')).*
) t;

Reprodukovateľný Benchmark

Test Setup

#!/bin/bash
# vacuum_benchmark.sh

# Vytvor test tabuľku
psql -c "
CREATE TABLE vacuum_test (
    id bigserial PRIMARY KEY,
    data jsonb,
    updated_at timestamp DEFAULT now()
);

-- Naplň 10M riadkov (škálované pre test)
INSERT INTO vacuum_test (data)
SELECT jsonb_build_object('value', generate_series(1, 10000000));

-- Vytvor index
CREATE INDEX idx_vacuum_test_updated ON vacuum_test(updated_at);
"

# Simuluj UPDATE workload
pgbench -c 10 -j 4 -T 3600 -f - <<EOF
\set id random(1, 10000000)
UPDATE vacuum_test SET data = jsonb_build_object('value', :id), updated_at = now() WHERE id = :id;
EOF

Výsledky: Default vs Tuned

Gotchas

1. Vacuum vs Analyze

-- Vacuum a Analyze majú ODDELENÉ thresholds!
ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.045,
    -- Analyze môže byť menej častý
    autovacuum_analyze_threshold = 10000,
    autovacuum_analyze_scale_factor = 0.1
);

2. Cost Limit Koordinácia

-- Globálny limit sa ZDIEĽA medzi všetkými vacuum procesmi
-- Ak máš 3 autovacuum workers a limit 200:
-- Každý worker má efektívne ~67

-- Pre agresívny vacuum na jednej tabuľke:
ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_cost_limit = 1000,  -- dedicated limit
    autovacuum_vacuum_cost_delay = 2
);

3. Long Running Transactions Blocker

-- Vacuum NEMÔŽE vyčistiť tuples viditeľné pre aktívne transakcie
SELECT
    pid,
    now() - xact_start as duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '5 minutes';

Prometheus Metriky

# prometheus_vacuum_rules.yml
groups:
  - name: postgresql_vacuum
    rules:
      - alert: HighDeadTupleRatio
        expr: |
          pg_stat_user_tables_n_dead_tup /
          (pg_stat_user_tables_n_live_tup + 1) > 0.1
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "High dead tuple ratio on {{ $labels.relname }}"

      - alert: VacuumNotRunning
        expr: |
          time() - pg_stat_user_tables_last_autovacuum > 7200
        for: 5m
        labels:
          severity: critical

Checklist

## Autovacuum SLO Checklist

### Analýza
- [ ] Identifikuj high-churn tabuľky (UPDATE/DELETE rate)
- [ ] Zmeraj aktuálny dead tuple ratio
- [ ] Vypočítaj požadovanú vacuum frequency

### Konfigurácia
- [ ] Nastav per-table autovacuum thresholds
- [ ] Zvýš cost_limit pre kritické tabuľky
- [ ] Zníž cost_delay pre rýchlejší vacuum

### Monitoring
- [ ] Dashboard pre dead tuple ratio
- [ ] Alert na bloat > 20%
- [ ] Sleduj vacuum duration a frequency

### Prevencia
- [ ] Kill idle transactions > 5 min
- [ ] Monitoruj replication slots
- [ ] Regular VACUUM ANALYZE pre statistics

Záver

Autovacuum nie je “set and forget”. Pre high-churn tabuľky:

1. Vypočítaj potrebnú vacuum frequency z UPDATE rate
2. Nastav per-table thresholds podľa SLO
3. Monitoruj dead tuple ratio a bloat
4. Alertuj pred tým, než problém eskaluje

Default nastavenia sú pre malé databázy. Pri 200M+ riadkoch potrebuješ custom tuning.

Súvisiace články

• Zero-downtime PostgreSQL migrácie - Bezpečné schema zmeny
• K8s PostgreSQL Connection Storm - Connection management