Функция перевода денег БЕЗ транзакции:

14:32:15.123 - Запрос: перевести 10,000₽ от user_id=1 к user_id=2
14:32:15.124 - UPDATE accounts SET balance = balance - 10000 WHERE user_id = 1;
                ✅ Выполнено! Баланс user 1: 50000₽ → 40000₽

14:32:15.125 - 💥 СБОЙ СЕРВЕРА (out of memory)

14:32:45.000 - Сервер перезапущен

Результат:
• user_id=1: баланс 40000₽ (было 50000₽) ← деньги списаны ✅
• user_id=2: баланс 20000₽ (было 20000₽) ← деньги НЕ зачислены ❌
• 10,000₽ исчезли из системы! 💸

15:00 - Звонок клиента: "Куда делись мои 10,000₽?!"
15:15 - Техподдержка: "В логах нет операции зачисления..."
15:30 - Бухгалтерия: "Общий баланс не сходится на -10,000₽!"

Итог: Потеря денег, репутационный ущерб, ручное восстановление данных

BEGIN;  -- ← начало транзакции (все изменения пока "черновик")
    UPDATE accounts SET balance = balance - 10000 WHERE user_id = 1;
    -- Если здесь произойдёт сбой → АВТОМАТИЧЕСКИЙ ROLLBACK
    UPDATE accounts SET balance = balance + 10000 WHERE user_id = 2;
COMMIT;  -- ← фиксация (все изменения применяются атомарно)

-- Если сбой случится до COMMIT → всё откатится автоматически!
-- Либо обе операции выполнятся, либо ни одна.

┌─────────────────────────────────────────────────────────────┐
│ 1. BEGIN (начало транзакции)                                │
│    Получает Transaction ID (xid)                            │
│    Изменения видны только внутри транзакции                 │
└──────────────────┬──────────────────────────────────────────┘
                   ↓
┌─────────────────────────────────────────────────────────────┐
│ 2. Выполнение SQL команд                                    │
│    UPDATE, INSERT, DELETE...                                │
│    Изменения в памяти (shared buffers) + WAL                │
│    Другие транзакции НЕ видят изменения                     │
└──────────────────┬──────────────────────────────────────────┘
                   ↓
         ┌─────────┴─────────┐
         │                   │
         ↓                   ↓
┌────────────────┐  ┌────────────────┐
│ 3a. COMMIT     │  │ 3b. ROLLBACK   │
│ Применить      │  │ Отменить       │
│ изменения      │  │ изменения      │
└────────┬───────┘  └────────┬───────┘
         │                   │
         ↓                   ↓
┌────────────────┐  ┌────────────────┐
│ Изменения      │  │ Изменения      │
│ видны ВСЕМ     │  │ отброшены      │
└────────────────┘  └────────────────┘

@Service
public class TransferService {
    @Autowired
    private DataSource dataSource;

    // ❌ БЕЗ транзакции (ОПАСНО!):
    public void transferMoneyWrong(Long fromUserId, Long toUserId, BigDecimal amount) {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {

            // Операция 1: снять деньги
            stmt.executeUpdate(String.format(
                "UPDATE accounts SET balance = balance - %s WHERE user_id = %d",
                amount, fromUserId
            ));

            // 💥 Если здесь произойдёт ошибка/сбой → деньги исчезнут!
            // (первая операция уже выполнена, вторая — нет)

            // Операция 2: зачислить деньги
            stmt.executeUpdate(String.format(
                "UPDATE accounts SET balance = balance + %s WHERE user_id = %d",
                amount, toUserId
            ));

        } catch (SQLException e) {
            // Слишком поздно! Первая операция уже применена.
            throw new RuntimeException("Transfer failed", e);
        }
    }

    // ✅ С транзакцией (ПРАВИЛЬНО):
    public void transferMoneyCorrect(Long fromUserId, Long toUserId, BigDecimal amount) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);  // Отключить autocommit!

            try (PreparedStatement stmt1 = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE user_id = ?");
                 PreparedStatement stmt2 = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE user_id = ?")) {

                // Операция 1: снять деньги
                stmt1.setBigDecimal(1, amount);
                stmt1.setLong(2, fromUserId);
                int rows1 = stmt1.executeUpdate();

                if (rows1 == 0) {
                    throw new BusinessException("Счёт отправителя не найден");
                }

                // Проверка баланса:
                BigDecimal balance = getBalance(conn, fromUserId);
                if (balance.compareTo(BigDecimal.ZERO) < 0) {
                    throw new BusinessException("Недостаточно средств");
                }

                // Операция 2: зачислить деньги
                stmt2.setBigDecimal(1, amount);
                stmt2.setLong(2, toUserId);
                int rows2 = stmt2.executeUpdate();

                if (rows2 == 0) {
                    throw new BusinessException("Счёт получателя не найден");
                }

                // Если всё ОК → фиксируем:
                conn.commit();  // ✅ Обе операции применяются атомарно!

            } catch (Exception e) {
                // При любой ошибке → откатываем ВСЁ:
                conn.rollback();  // ❌ Обе операции отменяются!
                throw new RuntimeException("Transfer failed: " + e.getMessage(), e);
            }

        } catch (SQLException e) {
            throw new RuntimeException("Database error", e);
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);  // Вернуть autocommit
                    conn.close();
                } catch (SQLException ignored) {}
            }
        }
    }

    // Вспомогательный метод:
    private BigDecimal getBalance(Connection conn, Long userId) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
                "SELECT balance FROM accounts WHERE user_id = ?")) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
        }
    }
}

@Service
public class TransferService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional  // ← Spring автоматически управляет транзакцией!
    public void transferMoney(Long fromUserId, Long toUserId, BigDecimal amount) {
        // Операция 1: снять деньги
        int rows1 = jdbcTemplate.update(
            "UPDATE accounts SET balance = balance - ? WHERE user_id = ?",
            amount, fromUserId
        );

        if (rows1 == 0) {
            throw new BusinessException("Счёт отправителя не найден");
        }

        // Проверка баланса:
        BigDecimal balance = jdbcTemplate.queryForObject(
            "SELECT balance FROM accounts WHERE user_id = ?",
            BigDecimal.class, fromUserId
        );

        if (balance.compareTo(BigDecimal.ZERO) < 0) {
            throw new BusinessException("Недостаточно средств");
            // При исключении Spring автоматически сделает ROLLBACK!
        }

        // Операция 2: зачислить деньги
        int rows2 = jdbcTemplate.update(
            "UPDATE accounts SET balance = balance + ? WHERE user_id = ?",
            amount, toUserId
        );

        if (rows2 == 0) {
            throw new BusinessException("Счёт получателя не найден");
        }

        // Если метод завершился без исключений → Spring сделает COMMIT
        // Если произошло исключение → автоматический ROLLBACK
    }
}

-- По умолчанию в PostgreSQL autocommit = ON:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ✅ Автоматически закоммичено сразу после выполнения

-- С явной транзакцией:
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Изменения пока не видны другим транзакциям
COMMIT;  -- Теперь видны всем

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;

    SAVEPOINT sp1;  -- Точка сохранения
        UPDATE accounts SET balance = balance + 100 WHERE id = 2;
        -- Ошибка!
    ROLLBACK TO sp1;  -- Откат до точки сохранения (первый UPDATE остаётся)

    UPDATE accounts SET balance = balance + 100 WHERE id = 3;  -- Попробовать другой счёт
COMMIT;

@Transactional
public void complexOperation() {
    // Операция 1 (важная):
    updateCriticalData();

    // Попытка необязательной операции:
    TransactionStatus savepoint = transactionManager.getTransaction(
        new DefaultTransactionDefinition()
    );

    try {
        updateOptionalData();  // Может упасть
    } catch (Exception e) {
        transactionManager.rollback(savepoint);  // Откатить только это
        log.warn("Optional operation failed, continuing", e);
    }

    // Операция 3 (важная):
    updateMoreCriticalData();
}

-- READ COMMITTED (по умолчанию):
BEGIN;  -- каждый SELECT видит последние закоммиченные данные

-- REPEATABLE READ:
BEGIN ISOLATION LEVEL REPEATABLE READ;  -- "замораживает" данные на момент старта транзакции

-- SERIALIZABLE (самый строгий):
BEGIN ISOLATION LEVEL SERIALIZABLE;  -- полная изоляция, как если бы транзакции выполнялись последовательно

@Transactional(isolation = Isolation.REPEATABLE_READ)
public void generateReport() {
    // Все SELECT видят данные на момент начала транзакции
    List<Order> orders = getOrders();
    BigDecimal total = calculateTotal();
    // Даже если другая транзакция добавит заказы → мы их не увидим
}

-- ❌ ОШИБКА:
BEGIN;
    UPDATE accounts SET balance = 1000 WHERE id = 1;
-- Забыли COMMIT → при закрытии соединения → ROLLBACK!

-- ✅ ПРАВИЛЬНО:
BEGIN;
    UPDATE accounts SET balance = 1000 WHERE id = 1;
COMMIT;

// ❌ ПЛОХО: длинная транзакция блокирует строки
@Transactional
public void processOrder(Long orderId) {
    Order order = getOrder(orderId);  // Блокировка строки
    sendEmail(order);  // 5 секунд! Строка заблокирована!
    updateStatus(order);
}
// Другие транзакции ждут 5+ секунд...

// ✅ ХОРОШО: короткие транзакции
public void processOrder(Long orderId) {
    Order order;

    // Транзакция 1: быстрое чтение
    order = transactionTemplate.execute(status -> getOrder(orderId));

    // Длинная операция БЕЗ транзакции:
    sendEmail(order);  // 5 секунд, но БД не заблокирована

    // Транзакция 2: быстрое обновление
    transactionTemplate.execute(status -> {
        updateStatus(order);
        return null;
    });
}

@Service
public class OrderService {
    // ❌ НЕ РАБОТАЕТ: @Transactional на private
    @Transactional
    private void processOrder() {
        // Транзакция НЕ создаётся! Spring не может проксировать private методы
    }

    // ✅ РАБОТАЕТ: @Transactional на public
    @Transactional
    public void processOrder() {
        // Транзакция создаётся через Spring AOP proxy
    }
}

-- Установить уровень:
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- или
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX1: UPDATE accounts SET balance = 0 WHERE id = 1;
TX2: SELECT balance FROM accounts WHERE id = 1;  -- видит 0
TX1: ROLLBACK;  -- откат!
TX2: -- использовал "грязные" данные, которых никогда не существовало

TX1: SELECT balance FROM accounts WHERE id = 1;  -- 1000
TX2: UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;
TX1: SELECT balance FROM accounts WHERE id = 1;  -- 500 (изменилось!)

TX1: SELECT * FROM orders WHERE status = 'pending';  -- 10 строк
TX2: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
TX1: SELECT * FROM orders WHERE status = 'pending';  -- 11 строк!

SHOW default_transaction_isolation;  -- read committed

-- Изменить глобально:
ALTER SYSTEM SET default_transaction_isolation = 'repeatable read';

-- Правило: минимум 1 дежурный
-- TX1: видит 2 дежурных (Анна и Борис), Анна уходит
-- TX2: видит 2 дежурных (Анна и Борис), Борис уходит
-- Обе транзакции COMMIT
-- Результат: 0 дежурных! Инвариант нарушен.

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- критическая бизнес-логика
COMMIT;

-- При конфликте получите ошибку:
-- ERROR: could not serialize access due to read/write dependencies

BEGIN;
INSERT INTO orders VALUES (1, 'pending');
SAVEPOINT sp1;
INSERT INTO orders VALUES (2, 'pending');  -- ошибка!
ROLLBACK TO sp1;  -- откат только до savepoint
INSERT INTO orders VALUES (3, 'pending');
COMMIT;  -- сохранятся id=1 и id=3

┌──────────────────────────────────────────────────────────────────────────┐
│  MVCC — Git для базы данных:                                             │
│  • Каждая строка хранит несколько версий (xmin, xmax)                    │
│  • Каждая транзакция видит СВОЙ snapshot данных                          │
│  • Читатели НЕ блокируют писателей                                       │
│  • Писатели НЕ блокируют читателей                                       │
└──────────────────────────────────────────────────────────────────────────┘

-- Просмотр системных колонок:
SELECT xmin, xmax, ctid, * FROM users WHERE id = 1;

-- Проверить VM через pageinspect:
CREATE EXTENSION pageinspect;
SELECT * FROM pg_visibility('users');

-- Проверить HOT статистику:
SELECT relname, n_tup_upd, n_tup_hot_upd,
      ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_ratio
FROM pg_stat_user_tables WHERE n_tup_upd > 0;

-- hot_ratio > 90% — отлично
-- hot_ratio < 50% — проблема

-- Оптимизация для HOT:
ALTER TABLE users SET (fillfactor = 70);  -- 30% страницы для обновлений

┌─────────────────────────────────────────────────────────────────┐
│  Snapshot = {                                                   │
│    xmin: 100,        // Все TX < 100 завершены (видимы)         │
│    xmax: 105,        // Все TX >= 105 ещё не начались           │
│    xip: [101, 103],  // Активные TX в момент snapshot           │
│  }                                                              │
└─────────────────────────────────────────────────────────────────┘

-- Посмотреть текущий snapshot:
SELECT pg_current_snapshot();
-- Результат: 100:105:101,103
-- Формат: xmin:xmax:xip_list

-- Исходное состояние:
-- users: id=1, name='Alice', xmin=100, xmax=0

BEGIN;  -- TX 200
UPDATE users SET name = 'Alicia' WHERE id = 1;
COMMIT;

┌─────────────────────────────────────────────────────────────────┐
│  ШАГ 1: До UPDATE                                               │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │ ctid=(0,1) │ xmin=100 │ xmax=0 │ id=1 │ name='Alice'     │   │
│  └──────────────────────────────────────────────────────────┘   │
│                                                                 │
│  ШАГ 2: UPDATE выполняется (TX 200)                             │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │ ctid=(0,1) │ xmin=100 │ xmax=200 │ id=1 │ name='Alice'   │ ← помечена как "мёртвая"
│  └──────────────────────────────────────────────────────────┘   │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │ ctid=(0,2) │ xmin=200 │ xmax=0 │ id=1 │ name='Alicia'    │ ← новая версия
│  └──────────────────────────────────────────────────────────┘   │
│                                                                 │
│  ШАГ 3: После COMMIT                                            │
│  - Старая версия (0,1) "мертва" для всех новых транзакций       │
│  - Новая версия (0,2) видима для всех                           │
│  - VACUUM потом удалит (0,1)                                    │
└─────────────────────────────────────────────────────────────────┘

Время   │ TX 100 (READ COMMITTED)    │ TX 101 (REPEATABLE READ)
────────┼────────────────────────────┼───────────────────────────
 T1    │ BEGIN                      │
 T2    │                            │ BEGIN
 T3    │                            │ SELECT balance → 1000 ✓
 T4    │ UPDATE balance = 500       │
 T5    │ COMMIT                     │
 T6    │                            │ SELECT balance → ???
────────┴────────────────────────────┴───────────────────────────

Строка balance после T5:
┌─────────────────────────────────────────────────────────────┐
│ Версия 1: xmin=50, xmax=100, balance=1000  ← "мертва"       │
│ Версия 2: xmin=100, xmax=0, balance=500   ← "живая"         │
└─────────────────────────────────────────────────────────────┘

TX 101 (snapshot создан в T2, до TX 100):
- Видит версию 1 (xmin=50 < snapshot, xmax=100 ещё активна в момент snapshot)
- НЕ видит версию 2 (xmin=100, но 100 была активна в момент snapshot)

┌─────────────────────────────────────────────────────────────────┐
│  Блокирующий подход:                                            │
│  TX1: UPDATE users SET name='A' WHERE id=1;  -- блокирует       │
│  TX2: SELECT * FROM users WHERE id=1;        -- ЖДЁТ! ⏳        │
│                                                                 │
│  MVCC:                                                          │
│  TX1: UPDATE users SET name='A' WHERE id=1;  -- создаёт версию  │
│  TX2: SELECT * FROM users WHERE id=1;        -- читает СТАРУЮ!  │
│                                               -- НЕ ждёт! ⚡     │
└─────────────────────────────────────────────────────────────────┘

-- Много UPDATE/DELETE = много мёртвых версий
-- Таблица 1GB данных может занимать 10GB на диске!

-- Диагностика:
SELECT
   relname,
   n_live_tup,
   n_dead_tup,
   ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Индексы тоже раздуваются! Каждая версия строки = запись в индексе
-- (кроме HOT updates)

-- Оценка bloat через pgstattuple:
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('users_pkey');

-- Даже ОДНА долгая транзакция не даёт очистить старые версии!
-- Все версии после её snapshot нужны для её видимости

-- Найти старые транзакции:
SELECT pid, age(backend_xid) AS xid_age,
      NOW() - xact_start AS duration,
      query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL
ORDER BY xid_age DESC;

-- 1. Проверить мёртвые строки (нужен VACUUM?)
SELECT relname, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 2. Проверить bloat таблиц
SELECT
   schemaname || '.' || relname AS table,
   pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
   n_live_tup,
   n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- 3. Найти долгие транзакции (блокируют VACUUM)
SELECT pid,
      NOW() - xact_start AS duration,
      state,
      LEFT(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start < NOW() - INTERVAL '5 minutes'
 AND state != 'idle';

-- 4. Проверить XID age (wraparound risk)
SELECT datname,
      age(datfrozenxid) AS xid_age,
      ROUND(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- 5. Проверить HOT ratio
SELECT relname,
      n_tup_upd,
      n_tup_hot_upd,
      CASE WHEN n_tup_upd > 0
           THEN ROUND(100.0 * n_tup_hot_upd / n_tup_upd, 2)
           ELSE 0 END AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 100
ORDER BY hot_pct;

-- Обычный VACUUM — освобождает место для переиспользования
VACUUM users;

-- VACUUM FULL — сжимает таблицу, НО блокирует!
VACUUM FULL users;  -- ⚠️ ACCESS EXCLUSIVE LOCK

-- Устанавливаем расширение
CREATE EXTENSION pg_repack;

-- Перепаковываем таблицу онлайн
-- (создаёт копию, переключает атомарно)
pg_repack -t users mydb

-- Перестраивает таблицу по индексу
CLUSTER users USING users_pkey;  -- ⚠️ Блокирует

-- 1. Настроить autovacuum агрессивнее для активных таблиц
ALTER TABLE hot_table SET (
   autovacuum_vacuum_scale_factor = 0.05,  -- 5% вместо 20%
   autovacuum_analyze_scale_factor = 0.02
);

-- 2. Увеличить fillfactor для таблиц с частыми UPDATE
ALTER TABLE users SET (fillfactor = 70);

-- 3. Мониторить и убивать долгие транзакции
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE xact_start < NOW() - INTERVAL '1 hour'
 AND state = 'idle in transaction';

-- Row-level:
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Table-level:
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

-- Advisory:
SELECT pg_advisory_lock(12345);  -- ждёт
SELECT pg_try_advisory_lock(12345);  -- не ждёт
SELECT pg_advisory_unlock(12345);

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Строка заблокирована

-- Варианты:
FOR UPDATE NOWAIT       -- ошибка если заблокировано
FOR UPDATE SKIP LOCKED  -- пропустить заблокированные
FOR SHARE               -- разрешить другие SELECT FOR SHARE

TX1: SELECT * FROM A FOR UPDATE;  -- блокирует A
TX2: SELECT * FROM B FOR UPDATE;  -- блокирует B
TX1: SELECT * FROM B FOR UPDATE;  -- ждёт TX2... 💤
TX2: SELECT * FROM A FOR UPDATE;  -- ждёт TX1... 💀 DEADLOCK!

LOCK TABLE t IN ACCESS SHARE MODE;      -- SELECT
LOCK TABLE t IN ROW SHARE MODE;         -- SELECT FOR UPDATE/SHARE
LOCK TABLE t IN ROW EXCLUSIVE MODE;     -- UPDATE, DELETE, INSERT
LOCK TABLE t IN SHARE MODE;             -- CREATE INDEX CONCURRENTLY
LOCK TABLE t IN EXCLUSIVE MODE;         -- Блокирует большинство операций
LOCK TABLE t IN ACCESS EXCLUSIVE MODE;  -- Полная блокировка (DDL)

Кластер ($PGDATA)
└── База данных (database)
    └── Схема (schema / namespace)
        └── Таблица (relation)
            └── Файлы на диске:
                ├── 16384          ← heap (основной файл с данными)
                ├── 16384_fsm      ← Free Space Map (карта свободного места)
                └── 16384_vm       ← Visibility Map (карта видимости строк)

┌────────────────────────────────────────────────┐  ← 8192 байта
│ PageHeaderData (24 байта)                      │
│   lsn      — позиция в WAL (crash recovery)    │
│   pd_lower — граница item pointers             │
│   pd_upper — начало данных строк               │
├────────────────────────────────────────────────┤
│ ItemId[1] ItemId[2] ItemId[3] ...  ← указатели │
│                                                │
│           (свободное место)                    │
│                                                │
│         ... Tuple 3 │ Tuple 2 │ Tuple 1        │  ← строки растут снизу
└────────────────────────────────────────────────┘

-- Посмотреть системные поля напрямую:
SELECT ctid, xmin, xmax, id, name FROM users LIMIT 5;
-- ctid = (0,1) → страница 0, слот 1

-- Узнать OID → имя файла на диске:
SELECT relname, relfilenode
FROM pg_class
WHERE relname = 'users';
-- Файл: $PGDATA/base/<db_oid>/<relfilenode>

-- Размеры: heap / индексы / TOAST / итого
SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid))       AS heap,
    pg_size_pretty(pg_indexes_size(oid))        AS indexes,
    pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_class
WHERE relname = 'orders';

-- Текущая позиция WAL:
SELECT pg_current_wal_lsn();

-- Ручной checkpoint:
CHECKPOINT;

-- Настройки:
SHOW checkpoint_timeout;  -- default: 5min (как часто)
SHOW max_wal_size;        -- default: 1GB (сколько WAL накопить)

-- Статистика autovacuum:
SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

XID: 1 → 2 → 3 → ... → 2 147 483 647 → 2 147 483 648 → ... → 4 294 967 295 → 1 → 2 ...
                                         ↑
                         Здесь "прошлое" и "будущее" меняются местами!

Строка, созданная в xmin=1 000, после wraparound видит XID 1 000 как "будущее"
→ строка становится НЕВИДИМОЙ для всех запросов!

ДО FREEZE:                        ПОСЛЕ FREEZE:
┌──────────────────────┐          ┌──────────────────────┐
│ xmin = 500 000       │  →→→→→   │ xmin = FrozenXID (2) │
│ xmax = 0             │          │ xmax = 0             │
│ data = "Иван"        │          │ data = "Иван"        │
└──────────────────────┘          └──────────────────────┘
  Возраст строки: 1.6 млрд XID      Возраст: 0 (навсегда молода!)

-- Мониторинг возраста транзакций:
SELECT
    datname,
    age(datfrozenxid)                            AS db_age,
    2147483647 - age(datfrozenxid)               AS remaining_xids,
    round((age(datfrozenxid)::numeric / 2147483647) * 100, 1) AS pct_used
FROM pg_database
ORDER BY db_age DESC;

-- Возраст отдельных таблиц:
SELECT relname, age(relfrozenxid) AS table_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY table_age DESC
LIMIT 10;

-- Принудительный freeze конкретной таблицы (без блокировки на чтение):
VACUUM FREEZE large_table;

-- VERBOSE — видно, сколько страниц заморожено:
VACUUM FREEZE VERBOSE large_table;

-- VIEW:
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- Materialized View:
CREATE MATERIALIZED VIEW sales_summary AS
SELECT date, SUM(amount) FROM orders GROUP BY date;

REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- без блокировки

CREATE MATERIALIZED VIEW daily_stats AS
SELECT DATE_TRUNC('day', created_at) AS day,
      COUNT(*), SUM(amount)
FROM orders GROUP BY 1;

CREATE UNIQUE INDEX ON daily_stats(day);

-- Обновление по расписанию:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_update_timestamp
   BEFORE UPDATE ON users
   FOR EACH ROW EXECUTE FUNCTION update_timestamp();

-- BEFORE ROW — можно изменить NEW
-- AFTER ROW — данные уже записаны
-- INSTEAD OF — заменяет операцию (для VIEW)

CREATE TABLE audit_log (
   id BIGSERIAL PRIMARY KEY,
   table_name TEXT,
   record_id BIGINT,
   action TEXT,
   old_data JSONB,
   new_data JSONB,
   changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
   INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
   VALUES (
       TG_TABLE_NAME,
       COALESCE(NEW.id, OLD.id),
       TG_OP,
       CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
       CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
   );
   RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_audit_users
   AFTER INSERT OR UPDATE OR DELETE ON users
   FOR EACH ROW EXECUTE FUNCTION audit_trigger();

-- RANGE:
CREATE TABLE orders (id SERIAL, order_date DATE, amount NUMERIC)
PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024_q1 PARTITION OF orders
   FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- LIST:
CREATE TABLE orders PARTITION BY LIST (region);
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU');

-- HASH:
CREATE TABLE orders PARTITION BY HASH (customer_id);
CREATE TABLE orders_p0 PARTITION OF orders
   FOR VALUES WITH (MODULUS 4, REMAINDER 0);

SELECT * FROM orders WHERE order_date = '2024-02-15';
-- Читает только orders_2024_q1, игнорирует остальные

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- Покажет сканирование только релевантных партиций

Партиционирование:
PostgreSQL (один сервер)
  └─ orders
      ├─ orders_2024_q1 (партиция)
      ├─ orders_2024_q2 (партиция)
      └─ orders_2024_q3 (партиция)

Шардирование:
PostgreSQL Server 1 (orders для клиентов 1-1000)
PostgreSQL Server 2 (orders для клиентов 1001-2000)
PostgreSQL Server 3 (orders для клиентов 2001-3000)

# Range-based
def get_shard(user_id):
    if user_id <= 1000:
        return "shard1"
    elif user_id <= 2000:
        return "shard2"
    else:
        return "shard3"

# Hash-based
def get_shard(user_id):
    shard_num = hash(user_id) % 3
    return f"shard{shard_num + 1}"

# Geography-based
def get_shard(country):
    mapping = {"US": "shard_us", "EU": "shard_eu", "ASIA": "shard_asia"}
    return mapping.get(country, "shard_default")

# Directory-based
def get_shard(user_id):
    return db.query("SELECT shard_name FROM shard_directory WHERE user_id = ?", user_id)

-- ❌ Не работает, если users и orders на разных шардах:
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- ✅ Решение: денормализация или application-level JOIN
-- Храним user_name прямо в orders:
SELECT user_name, COUNT(id) FROM orders GROUP BY user_name;

-- ❌ Нельзя в одной транзакции:
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- shard1
  UPDATE accounts SET balance = balance + 100 WHERE id = 2000; -- shard2
COMMIT;

-- ✅ Решение: Saga pattern или 2PC (Two-Phase Commit)
-- Или храни связанные данные на одном шарде (colocation)

-- ❌ AUTO_INCREMENT на каждом шарде даст дубликаты:
shard1: INSERT INTO users ... -- id=1
shard2: INSERT INTO users ... -- id=1 (конфликт!)

-- ✅ Решение: UUID, Snowflake ID, или диапазоны:
CREATE TABLE users (
    id BIGINT DEFAULT generate_snowflake_id(),  -- уникальный глобально
    ...
);

Добавили shard4 → нужно переместить 25% данных с shard1,2,3
На TB данных это может занять дни!

✅ Решение:
- Consistent Hashing (минимизирует перемещение)
- Virtual shards (логические шарды >> физических серверов)
- Заранее создавай больше шардов, чем серверов (1 сервер = 4 шарда)

# ❌ Теперь N серверов вместо одного:
pg_dump db1 > backup1.sql
pg_dump db2 > backup2.sql
pg_dump db3 > backup3.sql

# ✅ Решение: централизованный мониторинг (Prometheus + Grafana)
# и автоматизация бэкапов (pg_basebackup + wal-g)

-- Logical replication:
CREATE PUBLICATION all_tables FOR ALL TABLES;

CREATE SUBSCRIPTION my_sub
   CONNECTION 'host=primary dbname=mydb'
   PUBLICATION all_tables;

-- Создать слот:
SELECT pg_create_physical_replication_slot('replica1');

-- Просмотр слотов:
SELECT * FROM pg_replication_slots;

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Сбросить статистику:
SELECT pg_stat_statements_reset();

SELECT blocked.pid AS blocked_pid,
      blocking.pid AS blocking_pid,
      blocked.query AS blocked_query,
      blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
   ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

SELECT pid, usename, state, query,
      NOW() - query_start AS duration
FROM pg_stat_activity
WHERE datname = current_database();

-- Состояния:
-- active — выполняет запрос
-- idle — ожидает новый запрос
-- idle in transaction — В ТРАНЗАКЦИИ, но не выполняет (ОПАСНО!)

-- Убить проблемный процесс:
SELECT pg_terminate_backend(12345);  -- грубое завершение
SELECT pg_cancel_backend(12345);     -- мягкая отмена

CREATE ROLE analyst LOGIN PASSWORD 'secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
GRANT USAGE ON SCHEMA reporting TO analyst;

-- Отозвать права:
REVOKE INSERT ON users FROM analyst;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
   FOR ALL
   USING (tenant_id = current_setting('app.tenant_id')::INT);

-- Приложение устанавливает tenant_id:
SET app.tenant_id = 123;
SELECT * FROM orders;  -- автоматически только tenant_id = 123

# pg_hba.conf
hostssl all all 0.0.0.0/0 scram-sha-256

-- Первая страница:
SELECT id, name, created_at FROM products
ORDER BY created_at DESC, id DESC LIMIT 20;
-- Последняя строка: created_at='2024-01-15', id=12345

-- Следующая страница (передаём "курсор"):
SELECT id, name, created_at FROM products
WHERE (created_at, id) < ('2024-01-15', 12345)
ORDER BY created_at DESC, id DESC LIMIT 20;

-- Индекс для поддержки:
CREATE INDEX ON products(created_at DESC, id DESC);

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- "Удаление":
UPDATE users SET deleted_at = NOW() WHERE id = 123;

-- Выборка активных:
SELECT * FROM users WHERE deleted_at IS NULL;

-- Partial index для оптимизации:
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

-- Или RLS:
CREATE POLICY active_only ON users
   FOR SELECT USING (deleted_at IS NULL);

CREATE TABLE job_queue (
   id BIGSERIAL PRIMARY KEY,
   payload JSONB NOT NULL,
   status TEXT DEFAULT 'pending',
   scheduled_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ON job_queue(scheduled_at) WHERE status = 'pending';

-- Взять задачу (атомарно):
WITH next_job AS (
   SELECT id FROM job_queue
   WHERE status = 'pending' AND scheduled_at <= NOW()
   ORDER BY scheduled_at LIMIT 1
   FOR UPDATE SKIP LOCKED  -- ключевой момент!
)
UPDATE job_queue
SET status = 'processing'
WHERE id = (SELECT id FROM next_job)
RETURNING *;

-- Найти дубликаты по email:
SELECT email, COUNT(*), array_agg(id) AS ids
FROM users GROUP BY email HAVING COUNT(*) > 1;

-- Удалить дубликаты, оставив с минимальным id:
WITH duplicates AS (
   SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
   FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

-- Предотвратить дубликаты:
CREATE UNIQUE INDEX ON users(email);

Обычная таблица:          Temporal таблица:
┌────┬───────┬───────┐    ┌────┬───────┬───────────┬──────────────────────┬──────────────────────┐
│ id │ name  │ price │    │ id │ name  │ price     │ valid_from           │ valid_to             │
├────┼───────┼───────┤    ├────┼───────┼───────────┼──────────────────────┼──────────────────────┤
│  1 │ Apple │ 100   │    │  1 │ Apple │ 80        │ 2024-01-01 00:00:00  │ 2024-06-01 12:00:00  │
└────┴───────┴───────┘    │  1 │ Apple │ 100       │ 2024-06-01 12:00:00  │ infinity             │
 Цена обновлена — старая  └────┴───────┴───────────┴──────────────────────┴──────────────────────┘
 цена 80 потеряна!         Обе цены сохранены, можно запросить любой момент

-- Структура таблицы:
CREATE TABLE products (
    id         INT          NOT NULL,
    name       TEXT         NOT NULL,
    price      NUMERIC      NOT NULL,
    valid_from TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    valid_to   TIMESTAMPTZ  NOT NULL DEFAULT 'infinity',
    PRIMARY KEY (id, valid_from)  -- уникальность: одна запись на момент времени
);

-- Индекс для быстрых запросов "на момент времени":
CREATE INDEX ON products (id, valid_from, valid_to);

-- INSERT — как обычно:
INSERT INTO products (id, name, price) VALUES (1, 'Apple', 80);

-- UPDATE — закрыть старую запись + вставить новую:
WITH closed AS (
    UPDATE products
    SET valid_to = NOW()
    WHERE id = 1 AND valid_to = 'infinity'
    RETURNING id, name
)
INSERT INTO products (id, name, price, valid_from)
VALUES (1, 'Apple', 100, NOW());

-- DELETE — только закрыть запись (данные не удаляются!):
UPDATE products SET valid_to = NOW()
WHERE id = 1 AND valid_to = 'infinity';

-- Запрос текущих данных:
SELECT * FROM products WHERE valid_to = 'infinity';

-- Запрос на конкретный момент времени ("машина времени"):
SELECT * FROM products
WHERE id = 1
  AND valid_from <= '2024-03-15 10:00:00'
  AND valid_to   >  '2024-03-15 10:00:00';

-- Полная история конкретной записи:
SELECT * FROM products WHERE id = 1 ORDER BY valid_from;

-- Актуальное состояние через VIEW:
CREATE VIEW products_current AS
SELECT id, name, price FROM products WHERE valid_to = 'infinity';

-- Проблема: UPDATE миллионов строк блокирует таблицу

-- Решение: Batch update с LIMIT
DO $$
DECLARE
   batch_size INT := 10000;
   rows_affected INT;
BEGIN
   LOOP
       UPDATE orders SET status = 'archived'
       WHERE id IN (
           SELECT id FROM orders
           WHERE created_at < '2023-01-01' AND status != 'archived'
           LIMIT batch_size
           FOR UPDATE SKIP LOCKED
       );
       GET DIAGNOSTICS rows_affected = ROW_COUNT;
       EXIT WHEN rows_affected = 0;
       COMMIT;
       PERFORM pg_sleep(0.1);  -- пауза между batch
   END LOOP;
END $$;

-- Операторы:
-> : получить элемент как JSON
->> : получить элемент как текст
@> : содержит
? : есть ключ

-- Индекс:
CREATE INDEX ON docs USING gin(data);

-- Запросы:
SELECT * FROM docs WHERE data @> '{"status": "active"}';
SELECT * FROM docs WHERE data->>'name' = 'test';

-- Подписчик:
LISTEN new_orders;

-- Издатель:
NOTIFY new_orders, '{"order_id": 123}';

-- Через триггер:
CREATE FUNCTION notify_order() RETURNS TRIGGER AS $$
BEGIN
   PERFORM pg_notify('new_orders', row_to_json(NEW)::text);
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Блокировка по ключу:
SELECT pg_advisory_lock(12345);      -- ждёт
SELECT pg_try_advisory_lock(12345);  -- не ждёт, возвращает true/false
SELECT pg_advisory_unlock(12345);

-- Для job queue:
SELECT * FROM jobs WHERE pg_try_advisory_lock(id) LIMIT 1;

CREATE EXTENSION postgres_fdw;

CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'db2', dbname 'analytics');

CREATE FOREIGN TABLE remote_orders (...) SERVER remote;

SELECT * FROM remote_orders WHERE ...;  -- прозрачный доступ

CREATE EXTENSION pg_cron;

-- Очистка старых логов каждый день в 3:00:
SELECT cron.schedule('cleanup-logs', '0 3 * * *',
   $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'$$);

-- Обновление materialized view каждый час:
SELECT cron.schedule('refresh-mv', '0 * * * *',
   'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

-- Управление:
SELECT * FROM cron.job;
SELECT cron.unschedule('cleanup-logs');

-- Плохо: транзакция открыта часами
BEGIN;
SELECT * FROM orders;
-- ... приложение думает 2 часа ...
COMMIT;

-- Проблемы:
-- - Блокирует VACUUM
-- - Удерживает transaction ID
-- - Держит блокировки

-- Решение:
SET idle_in_transaction_session_timeout = '5min';

-- Плохо: блокирует таблицу на всё время
CREATE INDEX idx_big ON huge_table(column);

-- Хорошо: не блокирует чтение и запись
CREATE INDEX CONCURRENTLY idx_big ON huge_table(column);

-- Плохо: ACCESS EXCLUSIVE lock
ALTER TABLE users ADD COLUMN age INT NOT NULL DEFAULT 0;

-- Хорошо: поэтапно
ALTER TABLE users ADD COLUMN age INT;
UPDATE users SET age = 0 WHERE age IS NULL;  -- batch
ALTER TABLE users ALTER COLUMN age SET NOT NULL;

-- Плохо: блокирует, создаёт много dead tuples
DELETE FROM logs WHERE created_at < '2023-01-01';

-- Хорошо: batch delete
DO $$
BEGIN
   LOOP
       DELETE FROM logs WHERE id IN (
           SELECT id FROM logs WHERE created_at < '2023-01-01' LIMIT 10000
       );
       EXIT WHEN NOT FOUND;
       COMMIT;
       PERFORM pg_sleep(0.1);
   END LOOP;
END $$;

-- Ещё лучше: партиционирование + DROP PARTITION
DROP TABLE logs_2022_01;  -- мгновенно!

-- Транзакции
BEGIN; ... COMMIT;
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT ... FOR UPDATE SKIP LOCKED;

-- Мониторинг
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_locks;

-- Блокировки
SELECT pg_blocking_pids(pid);
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);

-- Статистика запросов
CREATE EXTENSION pg_stat_statements;
SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC;

-- VACUUM и статистика
VACUUM ANALYZE table_name;
SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables;