В чём разница между VACUUM и VACUUM FULL?

Различия между VACUUM и VACUUM FULL в PostgreSQL

VACUUM — это критическая операция в PostgreSQL для очистки мусора и оптимизации производительности. Существуют две основные версии: обычный VACUUM и VACUUM FULL, которые значительно отличаются.

1. Что такое мусор (dead tuples) в PostgreSQL?

PostgreSQL использует MVCC (Multi-Version Concurrency Control), поэтому при UPDATE или DELETE строки не удаляются сразу.

-- Создаём таблицу
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');

-- UPDATE создаёт новую версию строки
UPDATE users SET name = 'Alicia' WHERE id = 1;

-- Старая версия остаётся в таблице как "мусор" (dead tuple)
-- DELETE работает также
DELETE FROM users WHERE id = 1;

Почему так? Потому что другие транзакции могут ещё нужна старая версия данных. MVCC позволяет разным транзакциям видеть разные версии данных.

2. VACUUM (обычный)

Определение: Обычный VACUUM удаляет мёртвые версии строк, но не переорганизует таблицу.

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Выполнить VACUUM
cur.execute("VACUUM users;")
conn.commit()

# Или VACUUM с анализом
cur.execute("VACUUM ANALYZE users;")
conn.commit()

-- В SQL
VACUUM users;
VACUUM ANALYZE users;  -- Обновляет статистику

-- Для всех таблиц
VACUUM;

Характеристики:

• Удаляет мёртвые версии строк
• Помечает пространство как свободное
• Не сжимает таблицу
• Не блокирует таблицу (может работать параллельно с SELECT, UPDATE, DELETE)
• Быстрая операция
• Пространство остаётся в таблице для будущих INSERT

Как это работает:

До VACUUM:
[живая строка] [мёртвая строка] [живая строка] [пусто] [мёртвая]

После VACUUM:
[живая строка] [пусто] [живая строка] [пусто] [пусто]
^
Строки остаются на месте, но мёртвые помечаются как пусто

3. VACUUM FULL

Определение: VACUUM FULL удаляет мёртвые строки И переорганизует таблицу, перемещая живые строки.

-- VACUUM FULL
VACUUM FULL users;

-- Можно с анализом
VACUUM FULL ANALYZE users;

Характеристики:

• Удаляет мёртвые версии
• Компактно переорганизует таблицу
• Пересортирует строки, убирая пробелы
• Блокирует таблицу (эксклюзивная блокировка)
• Медленнее обычного VACUUM
• Освобождает место на диске
• Может быть недоступна таблица длительное время

Как это работает:

До VACUUM FULL:
[живая строка] [мёртвая] [живая строка] [пусто] [мёртвая]

После VACUUM FULL:
[живая строка] [живая строка] [пусто] [пусто] [пусто]
^
Все живые строки перемещены вплотную друг к другу

4. Сравнение

5. Практические примеры

Пример 1: Мониторинг мусора

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Посмотреть информацию о мусоре в таблице
cur.execute("""
    SELECT 
        schemaname,
        tablename,
        n_dead_tup,
        n_live_tup,
        last_vacuum,
        last_autovacuum
    FROM pg_stat_user_tables
    WHERE tablename = 'users';
""")

for row in cur.fetchall():
    print(f"Таблица: {row[1]}")
    print(f"Живых строк: {row[3]}")
    print(f"Мёртвых строк: {row[2]}")
    print(f"Процент мусора: {row[2]/(row[2]+row[3])*100:.1f}%")

Пример 2: Автоматический VACUUM

-- PostgreSQL автоматически запускает VACUUM
-- Настройка автоматического VACUUM в postgresql.conf:

autovacuum = on  -- Включить
autovacuum_naptime = 1min  -- Проверка каждую минуту
autovacuum_vacuum_threshold = 50  -- Минимум 50 мёртвых строк
autovacuum_analyze_threshold = 50  -- Минимум 50 строк для анализа

Пример 3: Ручной VACUUM в приложении

from sqlalchemy import create_engine, text

engine = create_engine('postgresql://user:password@localhost/mydb')

# После большой очистки данных
with engine.connect() as conn:
    # Обычный VACUUM
    conn.execute(text("VACUUM users;"))
    conn.commit()
    
    # Или VACUUM FULL если нужно освободить место
    conn.execute(text("VACUUM FULL users;"))
    conn.commit()

6. Когда использовать каждый

VACUUM используй:

• Регулярно (автоматически через autovacuum)
• Когда есть много UPDATE/DELETE операций
• В рабочее время (параллельно работают другие запросы)
• После больших пакетных обновлений
• Для ежедневного обслуживания

-- Типичный cron job
0 2 * * * psql -d mydb -c "VACUUM ANALYZE;"

VACUUM FULL используй:

• Когда таблица раздулась слишком много
• Когда нужно освободить место на диске
• Во время окна обслуживания (когда нет активных пользователей)
• Редко (раз в месяц/квартал)
• Когда нет других вариантов

-- В окно обслуживания (ночью, выходные)
VACUUM FULL ANALYZE large_table;

7. Пример из практики: раздувшаяся таблица

import psycopg2
from datetime import datetime

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

def check_table_bloat(table_name):
    """Проверить раздутие таблицы"""
    cur.execute(f"""
        SELECT 
            pg_size_pretty(pg_total_relation_size('{table_name}'))::text as total_size,
            pg_size_pretty(pg_relation_size('{table_name}'))::text as table_size,
            n_dead_tup as dead_rows,
            n_live_tup as live_rows
        FROM pg_stat_user_tables
        WHERE tablename = '{table_name}';
    """)
    return cur.fetchone()

# Проверить размер
info = check_table_bloat('orders')
print(f"Размер таблицы: {info[1]}")
print(f"Мёртвых строк: {info[2]}")
print(f"Живых строк: {info[3]}")

if info[2] > 10000:  # Более 10000 мёртвых строк
    print(f"Требуется VACUUM для таблицы orders")
    cur.execute("VACUUM ANALYZE orders;")
    conn.commit()
    print("VACUUM выполнена")

if info[2] > 100000:  # Более 100000 мёртвых строк
    print(f"Рекомендуется VACUUM FULL для таблицы orders")
    # Выполнить во время обслуживания

8. ANALYZE

АНАЛИЗ обновляет статистику таблицы для оптимизатора:

-- VACUUM + ANALYZE
VACUUM ANALYZE users;

-- Статистика нужна для:
-- - Выбора правильного индекса
-- - Оценки стоимости запроса
-- - Улучшения производительности

9. Проблемы и решения

Проблема: VACUUM FULL блокирует таблицу слишком долго

-- Решение: использовать pg_repack расширение
CREATE EXTENSION pg_repack;
SELECT pg_repack.repack_table('orders');
-- Это делает то же, что VACUUM FULL, но без блокировки

10. Вывод

• VACUUM — быстрая, регулярная очистка, выполняется параллельно с работой таблицы
• VACUUM FULL — полная реорганизация, сжатие, но блокирует таблицу
• Используй обычный VACUUM каждый день через autovacuum
• Используй VACUUM FULL редко, только когда критически нужно освободить место
• Всегда комбинируй с ANALYZE для обновления статистики