Что такое snapshot в PostgreSQL?

Snapshot в PostgreSQL

Snapshot (снимок) в PostgreSQL — это снимок состояния базы данных на конкретный момент времени, который определяет, какие строки видны для конкретной транзакции. Snapshot обеспечивает изоляцию транзакций, позволяя разным транзакциям видеть разные версии одних и тех же данных в зависимости от уровня изоляции.

Проблема многовверсионности

Постгре использует MVCC (Multi-Version Concurrency Control) — каждая транзакция видит свою версию данных:

-- Сессия 1:
BEGIN;
UPDATE users SET balance = 100 WHERE id = 1;
-- Баланс изменяется, но COMMIT ещё не выполнен

-- Сессия 2 (одновременно):
BEGIN;
SELECT balance FROM users WHERE id = 1;  -- Видит старое значение!
-- Из-за SNAPSHOT: видит состояние БД на момент начала транзакции

Что такое Snapshot

Snapshot содержит информацию о:

1. xmin (transaction ID минимальный) — ID самой старой активной транзакции
2. xmax (transaction ID максимальный) — ID следующей назначаемой транзакции
3. xip (active transactions list) — список ID активных транзакций

-- Посмотреть информацию о транзакциях
SELECT txid_current_snapshot();
-- Пример вывода: 1000:1005:1001,1002,1003
-- 1000 = xmin (самая старая активная)
-- 1005 = xmax (следующая ID)
-- 1001,1002,1003 = активные транзакции

Типы Snapshots

1. Read Committed (по умолчанию)

Каждое сказание в транзакции видит свой новый snapshot:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
  SELECT COUNT(*) FROM orders;      -- Snapshot #1
  -- Здесь другая транзакция добавляет новый заказ и коммитит
  SELECT COUNT(*) FROM orders;      -- Snapshot #2 - видит новый заказ!
COMMIT;

-- Результат может быть разным между двумя SELECT'ами
-- Это "non-repeatable read" (грязное чтение обновлений)

2. Repeatable Read

Транзакция видит только данные, которые были закоммичены ДО её начала:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
  SELECT COUNT(*) FROM orders;      -- Snapshot #1 при начале транзакции
  -- Здесь другая транзакция добавляет новый заказ и коммитит
  SELECT COUNT(*) FROM orders;      -- Snapshot #1 - ТОТ ЖЕ! Не видит изменений
COMMIT;

-- Результат COUNT будет одинаковым
-- Snapshot фиксирован на момент BEGIN

3. Serializable

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
  SELECT * FROM accounts WHERE user_id = 1;     -- Snapshot #1
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
  -- Если другая транзакция пытается читать/писать те же данные,
  -- возникнет конфликт и текущая транзакция откатится
COMMIT;

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

Пример 1: Выявление проблемы Snapshot

# Python код демонстрирующий различие snapshots
import psycopg2
import threading
import time

def thread_1():
    """Читает данные дважды"""
    conn = psycopg2.connect("dbname=mydb user=postgres")
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
    cursor = conn.cursor()
    
    cursor.execute("BEGIN")
    cursor.execute("SELECT COUNT(*) FROM orders")
    count1 = cursor.fetchone()[0]
    print(f"Первое чтение: {count1} заказов")
    
    time.sleep(1)  # Даём времени thread_2 добавить заказ
    
    cursor.execute("SELECT COUNT(*) FROM orders")
    count2 = cursor.fetchone()[0]
    print(f"Второе чтение: {count2} заказов")
    
    # Результат: count1 может != count2
    cursor.execute("COMMIT")
    cursor.close()
    conn.close()

def thread_2():
    """Добавляет новый заказ"""
    time.sleep(0.5)  # Даём time thread_1 выполнить первый SELECT
    
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    cursor.execute("INSERT INTO orders (user_id, amount) VALUES (1, 100)")
    conn.commit()
    cursor.close()
    conn.close()

thread_1_obj = threading.Thread(target=thread_1)
thread_2_obj = threading.Thread(target=thread_2)

thread_1_obj.start()
thread_2_obj.start()

thread_1_obj.join()
thread_2_obj.join()

Пример 2: Phantom read (фантомное чтение)

-- Сессия 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
  SELECT COUNT(*) FROM users WHERE age < 25;  -- 5 пользователей
  -- Сессия 2 добавляет нового пользователя с age = 20 и коммитит
  SELECT COUNT(*) FROM users WHERE age < 25;  -- 6 пользователей!
COMMIT;
-- Появился "фантомный" пользователь

-- Сессия 2
INSERT INTO users (name, age) VALUES ('Bob', 20);
COMMIT;

Пример 3: Snapshot изоляция в Repeatable Read

-- Сессия 1: Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  -- Snapshot 1000:1005:1001 (фиксирован на этот момент)
  SELECT COUNT(*) FROM orders;  -- 100
  -- Сессия 2 добавляет заказ и коммитит
COMMIT;

-- Сессия 2:
BEGIN;
INSERT INTO orders VALUES (...);
COMMIT;  -- Заказ видим для новых транзакций

-- Сессия 1 (продолжение):
BEGIN;
  SELECT COUNT(*) FROM orders;  -- ВСЁ ЕЩЁ 100!
  -- Повторное чтение видит тот же snapshot
COMMIT;

Пример 4: Выявление старого Snapshot

-- Долгоживущая транзакция занимает старый snapshot
BEGIN;
SELECT COUNT(*) FROM huge_table;  -- 1000000 строк

-- Пока транзакция открыта, PostgreSQL не может удалить
-- старые версии строк (из-за MVCC)
-- Размер базы растёт из-за накопления версий

COMMIT;  -- Только теперь PostgreSQL может очищать старые версии

Управление Snapshots

Посмотреть активные транзакции

-- Информация о текущих транзакциях
SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
WHERE state != 'idle';

-- Информация о snapshots
SELECT * FROM pg_stat_database;

Отмена долгоживущей транзакции

-- Найти долгоживущую транзакцию
SELECT pid, state_change, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < NOW() - INTERVAL '10 minutes';

-- Отменить её
SELECT pg_terminate_backend(12345);  -- PID процесса

Проблемы со Snapshots

1. Bloat базы данных (из-за старых snapshots)

-- Проблема: долгоживущая транзакция
-- PostgreSQL не может удалить старые версии строк
-- рост размера базы

-- Решение: VACUUM
VACUUM ANALYZE;  -- Очистить мёртвые версии

-- Или для конкретной таблицы
VACUUM ANALYZE table_name;

2. Phantom reads в READ COMMITTED

-- Решение: использовать REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Или вручную управлять snapshot'ом
SET TRANSACTION SNAPSHOT 'sql-token';

Мониторинг Snapshots

# Мониторинг старых snapshots
import psycopg2

def find_old_transactions():
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    cursor.execute("""
        SELECT 
            pid,
            usename,
            EXTRACT(EPOCH FROM (NOW() - query_start)) as duration_seconds,
            query
        FROM pg_stat_activity
        WHERE state != 'idle'
        AND query_start < NOW() - INTERVAL '5 minutes'
        ORDER BY duration_seconds DESC;
    """)
    
    for row in cursor.fetchall():
        pid, user, duration, query = row
        if duration > 300:  # Более 5 минут
            print(f"Внимание: PID {pid} ({user}): {duration:.0f}s")
            # Рассмотрить завершение
    
    cursor.close()
    conn.close()

find_old_transactions()

Лучшие практики

1. Используй REPEATABLE READ для критичных операций
2. Не держи открытые транзакции долго — закрывай их быстро
3. Мониторь долгоживущие транзакции — они убивают производительность
4. Регулярно выполняй VACUUM — удаляет старые версии строк
5. Проверяй размер БД — раздутие говорит о проблемах со snapshots

Понимание Snapshots и MVCC критично для написания высокопроизводительных приложений на PostgreSQL. Правильный уровень изоляции и управление транзакциями помогают избежать race conditions и проблем с производительностью.