PGDATA/
├── postgresql.conf          # Главный конфиг (порт, shared_buffers, wal_level и т.д.)
├── pg_hba.conf             # Правила аутентификации (какие IP могут подключаться, как)
├── pg_ident.conf           # Маппирование ОС-пользователей на DB-пользователей
├── base/                   # Каталог с базами данных
│   └── 16384/              # Конкретная БД (16384 - это её OID)
│       ├── 16385           # Таблица (16385 - её OID)
│       ├── 16385_fsm       # Free Space Map для этой таблицы
│       ├── 16385_vm        # Visibility Map для этой таблицы
│       └── 16385.1, 16385.2... # Если таблица > 1GB, разбита на сегменты
├── pg_wal/                 # Write-Ahead Log файлы
├── pg_xact/                # Информация о статусе транзакций
├── global/                 # Глобальные объекты (pg_database, pg_tablespace)
├── pg_multixact/           # Для блокировок (обычно игнорируем)
└── postmaster.pid          # PID главного процесса

Страница (8192 bytes)
├── Page Header (24 bytes)
│   ├── pd_lsn (8 bytes) - Log Sequence Number (позиция в WAL)
│   ├── pd_checksum (2 bytes) - контрольная сумма
│   ├── pd_lower (2 bytes) - где заканчивается массив item pointers
│   ├── pd_upper (2 bytes) - где начинаются данные tuple'ов
│   └── ... ещё метаданные
├── Item Pointer Array
│   └── Указатели на каждый tuple в этой странице (4 bytes каждый)
├── Free Space (свободное место между item pointers и tuple данными)
└── Tuple Data (сами данные строк)

t_xmin       // XID (Transaction ID) транзакции, которая создала эту версию
t_xmax       // XID транзакции, которая удалила эту версию (0 если не удалена)
t_ctid       // "Current Tuple ID" - где находится последняя версия этой логической строки
t_infomask   // Флаги (HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED и т.д.)
t_hoff       // Смещение до начала данных (разные типы данных требуют разного выравнивания)

-- Получить OID таблицы
SELECT oid, relname FROM pg_class WHERE relname = 'users';
-- Результат: 16385, 'users'

-- Получить OID типа данных
SELECT oid, typname FROM pg_type WHERE typname = 'integer';
-- Результат: 23, 'integer'

SELECT pg_total_relation_size(16385);  -- по OID или по имени
-- или
SELECT pg_total_relation_size('users');

UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Текущий XID
SELECT txid_current();
-- Результат: 12345

Tuple с xmin=100, xmax=105

Транзакция 103 видит эту строку? ДА
  - xmin (100) committed и < 103
  - xmax (105) не в активных XID'ах или не committed

Транзакция 110 видит эту строку? НЕТ
  - xmax (105) committed и < 110
  - Значит, строка уже удалена

-- Информация о строке
SELECT xmin, xmax, ctid FROM users WHERE id = 1;
-- xmin=100, xmax=0, ctid=(0,1)
-- Строка была создана транзакцией 100, не удалена, находится в блоке 0 позиция 1

UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- Старая версия: xmin=100, xmax=102, ctid=(0,1)
-- Новая версия: xmin=102, xmax=0, ctid=(0,2)  <- указывает на себя

BEGIN;
SELECT COUNT(*) FROM users;  -- Snapshot A, видим 100 пользователей
-- Другая транзакция добавляет 10 пользователей и коммитит
SELECT COUNT(*) FROM users;  -- Snapshot B, видим 110 пользователей (!)

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM users;  -- Snapshot A, видим 100 пользователей
-- Другая транзакция добавляет 10 пользователей и коммитит
SELECT COUNT(*) FROM users;  -- Тот же Snapshot A, видим 100 пользователей (!)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Может выкинуть ошибку, если транзакция конфликтует с другой

000000010000000000000001  <- Timeline + номер
000000010000000000000002
000000010000000000000003
...

WAL Record структура:
├── xl_tot_len  (4 bytes)   // Полная длина записи
├── xl_xid      (4 bytes)   // XID транзакции
├── xl_prev     (8 bytes)   // Указатель на предыдущую запись (для проверки)
├── xl_rmid     (1 byte)    // Resource Manager ID (что менялось: heap, index и т.д.)
├── xl_info     (1 byte)    // Дополнительная информация
└── ... data ...             // Данные самого изменения

-- Настройки в postgresql.conf:
fsync = on                            -- Вообще писать на диск (ВАЖНО!)
synchronous_commit = on               -- Ждать записи на диск перед ответом
wal_level = replica                   -- Уровень логирования (minimal/replica/logical)

-- Ручной checkpoint
CHECKPOINT;

-- Мониторинг
SELECT * FROM pg_stat_bgwriter;
-- checkpoints_timed — плановые checkpoint'ы
-- checkpoints_req — внеплановые (когда WAL вырос слишком большой)

checkpoint_timeout = 5min              -- Максимум времени между checkpoint'ами
max_wal_size = 1GB                    -- Если WAL вырос больше - принудительный checkpoint
checkpoint_completion_target = 0.5    -- На сколько процентов во время checkpoint'а писать

        Корневая страница (средние значения)
        /                    \
    [Левое поддерево]    [Правое поддерево]
    /          \         /          \
Листья    Листья    Листья    Листья
(данные)  (данные)  (данные)  (данные)

-- Информация об индексе
SELECT * FROM pg_stat_user_indexes WHERE indexrelname = 'idx_users_email';
-- idx_scan — сколько раз использовался индекс
-- idx_tup_read — сколько кортежей прочитано через индекс

-- Размер индекса
SELECT pg_size_pretty(pg_relation_size('idx_users_email'));

-- Для массивов
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Поиск
SELECT * FROM posts WHERE tags @> ARRAY['database'];

-- Для геометрии (требует PostGIS расширение)
CREATE INDEX idx_locations_point ON locations USING GiST (point);
SELECT * FROM locations WHERE point <@ box '((0,0),(100,100))';

-- Для таблицы логов с временными метками
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ,
    message TEXT
);
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);

-- Поиск за последний день
SELECT * FROM logs WHERE timestamp > NOW() - INTERVAL '1 day';

CREATE INDEX idx_users_id ON users USING HASH (id);
-- Работает только для ==, не для <, >, диапазонов
SELECT * FROM users WHERE id = 123;

SQL запрос
    ↓
Parser (парсинг)
    ↓
Analyzer (семантический анализ, проверка таблиц/колонок)
    ↓
Rewriter (применение правил, VIEW'ов)
    ↓
Planner (оптимизация, выбор best plan)
    ↓
Executor (исполнение плана)
    ↓
Результат

-- Обновление статистики
ANALYZE users;

-- Просмотр статистики
SELECT * FROM pg_stats WHERE tablename = 'users';
-- n_distinct — сколько уникальных значений
-- correlation — корреляция между логическим порядком и физическим
-- most_common_vals — самые частые значения

-- Увеличить детальность статистики для важной колонки
ALTER TABLE users ALTER COLUMN email SET STATISTICS 1000;
ANALYZE users;

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 30;
-- Seq Scan on users  (cost=0.00..35.50 rows=500 width=32)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'john@example.com';
-- Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age BETWEEN 25 AND 35;
-- Bitmap Heap Scan on users  (cost=50.00..100.00 rows=1000)
--   Bitmap Index Scan using idx_users_age  (cost=0.00..50.00)

FOR EACH row in left_table:
    FOR EACH row in right_table:
        IF match:
            emit result

-- Для этого нужно достаточно work_mem
work_mem = 256MB

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE id = 1;

-- Query Plan:

-- Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
--   Filter: (id = 1)
--   Planning Time: 0.123 ms
--   Execution Time: 0.456 ms
--   Buffers: shared hit=5 read=2

shared_buffers = 2GB              -- Рекомендация: 25% RAM

-- Мониторинг
SELECT * FROM pg_buffercache_summary();
-- buffers_used — сколько буферов используется
-- buffers_unused — свободные

wal_buffers = 16MB                -- Обычно по умолчанию нормально

work_mem = 4MB                        -- На одну операцию!
-- Если в памяти не влезает, PostgreSQL сортирует "блоками" на диске

-- Увеличить для конкретной сессии
SET work_mem = '256MB';

-- Выполнить запрос
SELECT column1, COUNT(*) FROM huge_table GROUP BY column1;

-- Вернуть обратно
RESET work_mem;

maintenance_work_mem = 256MB          -- Обычно больше, чем work_mem

effective_cache_size = 6GB            -- Рекомендация: 75% RAM

-- Таблица до VACUUM
SELECT COUNT(*) FROM users;
-- 1000 живых строк, но физически в таблице 5000 версий!

VACUUM users;

-- Таблица после VACUUM
-- Те же 1000 живых строк, мёртвые версии удалены

VACUUM users;                  -- Обычный, может выполняться параллельно с запросами
VACUUM ANALYZE users;         -- VACUUM + обновление статистики
VACUUM (VERBOSE) users;       -- С подробным логированием

VACUUM FULL users;            -- ОПАСНО! Заблокирует таблицу на долгое время

-- Посмотреть свободное место в таблице
SELECT *, round(avail*100.0/8192, 2) as free_percent
FROM pg_freespace('users')
LIMIT 5;

-- blkno — номер блока (страницы)
-- avail — свободных байтов в блоке
-- free_percent — процент свободного места

SELECT blkno, all_visible, all_frozen
FROM pg_visibility_map('users')
LIMIT 5;

-- blkno — номер блока
-- all_visible — все версии на этом блоке видимы всем транзакциям?
-- all_frozen — все версии заморожены (очень старые)?

-- Включить/выключить
autovacuum = on                       -- По умолчанию включён

-- Сколько worker процессов
autovacuum_max_workers = 3

-- Как часто проверять
autovacuum_naptime = 1min

-- Пороги для запуска VACUUM
autovacuum_vacuum_threshold = 50             -- Абсолютно мёртвых версий
autovacuum_vacuum_scale_factor = 0.2         -- Или 20% от размера таблицы

-- Настроить для конкретной таблицы (например, часто обновляемой)
ALTER TABLE active_logs SET (
    autovacuum_vacuum_threshold = 1000,      -- Больше допуска
    autovacuum_vacuum_scale_factor = 0.1,    -- 10% вместо 20%
    autovacuum_vacuum_cost_delay = 1         -- Быстрее (мм по умолчанию 2)
);

-- Текущий процесс VACUUM
SELECT * FROM pg_stat_progress_vacuum;

-- История по таблицам
SELECT schemaname, tablename,
       last_vacuum, last_autovacuum,
       vacuum_count, autovacuum_count,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

-- Если n_dead_tup растёт, autovacuum не справляется
-- Нужно или увеличить aggressiveness или добавить workers

-- Что сейчас выполняется?
SELECT pid, usename, state, query_start,
       NOW() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Если duration часы, что-то не так. Может быть:

-- - Плохой query план
-- - Блокировка
-- - Просто долгая операция

-- Блокировки: что блокирует что?
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Размер всей БД
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Размер таблицы (с индексами)
SELECT pg_size_pretty(pg_total_relation_size('users'));

-- Только сама таблица (без индексов)
SELECT pg_size_pretty(pg_relation_size('users'));

-- Топ 10 самых больших таблиц
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Включить
CREATE EXTENSION pg_stat_statements;

-- Самые долгие запросы
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- Очистить статистику
SELECT pg_stat_statements_reset();

-- Сколько было deadlock'ов?
SELECT datname, deadlocks FROM pg_stat_database;

-- Если deadlocks > 0, нужно анализировать, почему они происходят
-- Обычно — плохой порядок блокировок в приложении

log_lock_waits = on
log_statement = 'all'  -- ИЛИ 'ddl' для только DDL

EXPLAIN SELECT * FROM users WHERE age > 30;

EXPLAIN (ANALYZE, BUFFERS, TIMING true, FORMAT JSON)
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.total > 1000;

Nested Loop (cost=0.29..856.44 rows=100 width=68) (actual time=0.123..5.234 rows=95 loops=1)
│ cost=0.29..856.44      -- Оценка: startup_cost..total_cost (в условных единицах)
│ rows=100               -- Ожидаемое количество строк
│ actual time=0.123..5.234 -- Реальное: первая строка..последняя строка (миллисекунды)
│ actual rows=95         -- Фактическое количество строк
│ loops=1                -- Количество итераций узла

Buffers: shared hit=234 read=45 dirtied=2 written=1
│ hit=234    -- Найдено в shared_buffers (горячий кеш, быстро)
│ read=45    -- Прочитано с диска (медленно)
│ dirtied=2  -- Модифицировано в памяти (не писалось на диск)
│ written=1  -- Написано на диск

CREATE EXTENSION pg_stat_statements;

-- Топ медленных запросов по абсолютному времени
SELECT 
    query,
    calls,
    total_exec_time::numeric(10,2) as total_ms,
    mean_exec_time::numeric(10,2) as avg_ms,
    max_exec_time::numeric(10,2) as max_ms,
    rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- Топ запросов по среднему времени (частые, но медленные)
SELECT 
    query,
    calls,
    mean_exec_time::numeric(10,2) as avg_ms,
    stddev_exec_time::numeric(10,2) as stddev_ms,
    (SELECT count(*) FROM pg_stat_statements) as total_queries
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Сброс статистики (начать отсчет заново)
SELECT pg_stat_statements_reset();

-- Активные долгие запросы
SELECT 
    pid,
    usename,
    application_name,
    state,
    query_start,
    now() - query_start as duration,
    query
FROM pg_stat_activity
WHERE state = 'active' 
  AND now() - query_start > interval '5 seconds'
ORDER BY query_start;

-- Заблокированные запросы и блокировщики
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_activity.query AS blocking_statement,
    blocked_activity.application_name
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Информация о текущих открытых транзакциях
SELECT 
    pid,
    usename,
    xact_start,
    now() - xact_start as transaction_duration,
    state_change,
    state,
    query_start,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- Одноколонный индекс
CREATE INDEX idx_users_email ON users (email);

-- Составной индекс для фильтрации и сортировки
CREATE INDEX idx_users_status_created ON users (status, created_at);

-- Включение дополнительных колонок (covering index, PostgreSQL 11+)
-- Используется Index Only Scan - не нужно обращаться к основной таблице
CREATE INDEX idx_users_email_covering ON users (email) 
INCLUDE (name, created_at, phone);

-- Часто фильтруем по статусу 'active', имеет смысл индексировать только активные записи
CREATE INDEX idx_users_active_email ON users (email) 
WHERE status = 'active';

-- Экономит место, ускоряет поиск на часто используемых условиях
-- Обычно меньше на 50-90% чем полный индекс

-- Поиск по email регистронезависимо
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- Теперь этот запрос использует индекс:
SELECT * FROM users WHERE lower(email) = 'john@example.com';

-- Без индекса пришлось бы сканировать всю таблицу

-- Полнотекстовый поиск
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Поиск в JSON данных
CREATE INDEX idx_users_tags ON users USING GIN (tags);

-- Поиск - будет быстрым
SELECT * FROM users WHERE tags @> ARRAY['admin', 'moderator'];

-- Поиск по координатам (геолокация)
CREATE INDEX idx_locations_point ON locations USING GiST (point);

-- Поиск в диапазоне дат (tsrange)
CREATE INDEX idx_events_when ON events USING GiST (when);

-- Для логов и временных рядов (данные отсортированы по timestamp)
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
CREATE INDEX idx_metrics_created ON metrics USING BRIN (created_at);

-- Очень компактный, но требует данных отсортированных по индексируемой колонке

-- Статистика по индексам
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
  AND schemaname != 'pg_catalog'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Дублирующиеся индексы
SELECT 
    pg_size_pretty(SUM(pg_relation_size(idx))) as total_size,
    (array_agg(idx))[1] as idx1,
    (array_agg(idx))[2] as idx2
FROM (
    SELECT 
        indexrelname::text as idx,
        (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| 
         indkey::text ||E'\n'|| COALESCE(indexprs::text,'')||E'\n' ||
         COALESCE(indpred::text,'')) AS key
    FROM pg_index
) sub
GROUP BY key HAVING COUNT(*) > 1;

-- Размер всех индексов по таблице
SELECT 
    tablename,
    SUM(pg_relation_size(indexrelid))::bigint as total_index_size,
    COUNT(*) as index_count
FROM pg_indexes
JOIN pg_class ON pg_class.relname = tablename
JOIN pg_index ON pg_index.indrelid = pg_class.oid
WHERE schemaname = 'public'
GROUP BY tablename
ORDER BY total_index_size DESC;

-- ❌ Не использует индекс (функция на колонке)
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';
SELECT * FROM users WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- ✓ Использует индекс (колонка без функции)
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ✓ Использует функциональный индекс (если создан)
CREATE INDEX idx_users_upper_email ON users (UPPER(email));
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

-- Высокая selectivity - индекс вернет мало строк (хорошо)
SELECT * FROM users WHERE id = 123;           -- selectivity ~0.001%
SELECT * FROM users WHERE email = '...';      -- selectivity ~0.01%

-- Низкая selectivity - индекс вернет много строк (плохо для индекса, но хорошо проверить)
SELECT * FROM users WHERE status = 'active';  -- selectivity ~80% - может быть полезен partial индекс
SELECT * FROM users WHERE is_deleted = false; -- selectivity ~95% - индекс почти бесполезен

-- Планировщик может выбрать Seq Scan вместо Index Scan, если selectivity плохая

-- LIKE: работает с индексом если нет % в начале
-- ✓ Использует индекс
SELECT * FROM users WHERE email LIKE 'john%';
SELECT * FROM users WHERE email LIKE 'john@example.%';

-- ❌ Не использует индекс (% в начале)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Решение: использовать специальные индексы или полнотекстовый поиск

-- IN лучше, чем множество OR
-- ✓ Хорошо оптимизируется
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

-- ❌ Медленнее
SELECT * FROM orders WHERE status = 'pending' 
  OR status = 'processing' 
  OR status = 'shipped';

-- UNION вместо OR для разных индексов
-- Если у нас есть индекс на status и на priority, лучше использовать UNION
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE priority = 'high' AND status != 'pending';

-- Nested Loop - для маленьких таблиц или когда есть хороший индекс
-- Для каждой строки внешней таблицы ищет строки во внутренней таблице
-- Хорошо когда внутренняя таблица имеет индекс на JOIN колонке

-- Hash Join - когда одна таблица помещается в памяти
-- Создается хеш-таблица из меньшей таблицы, затем ищутся совпадения
-- Быстро, но требует много памяти (параметр work_mem)

-- Merge Join - для больших отсортированных таблиц
-- Обе таблицы отсортированы, затем происходит слияние
-- Требует сортировки, но может быть эффективным

-- Принудительное отключение для тестирования
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

-- Хороший JOIN
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > now() - interval '90 days'
GROUP BY u.id, u.name;

-- Нужна оптимизация - слишком много строк до JOIN
-- Лучше сначала отфильтровать, потом JOINить
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM (
    SELECT id, name 
    FROM users 
    WHERE created_at > now() - interval '90 days'
) u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- ❌ Медленно: подзапрос для каждой строки (может быть материализован как IN)
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- ✓ Лучше: EXISTS (ранний выход, не загружает все результаты)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o 
              WHERE o.user_id = u.id AND o.total > 1000);

-- ✓ Часто лучше: JOIN (позволяет параллелизм и оптимизацию)
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

-- Выбор стратегии:

-- - EXISTS для больших таблиц с малым количеством совпадений
-- - JOIN для обычного случая
-- - IN когда подзапрос возвращает мало строк (до 100-1000)

-- ❌ Плохо: OFFSET требует пропустить много строк
-- Для страницы 1000 нужно пропустить 99,000 строк
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 99000;
-- Execution time: 850ms

-- ✓ Хорошо: cursor-based пагинация (keyset pagination)
-- Помнит последний ID/значение, находит следующие
SELECT * FROM orders 
WHERE created_at < '2024-11-10 15:30:00'  -- Фильтруем по известной позиции
ORDER BY created_at DESC 
LIMIT 21;  -- Берем на 1 больше для определения next_cursor
-- Execution time: 5ms

-- Или с ID для еще лучшей производительности
SELECT * FROM orders 
WHERE id < 15842  -- Последний ID с предыдущей страницы
ORDER BY id DESC 
LIMIT 21;
-- Execution time: 1ms

-- CTE может улучшить читаемость, но планировщик материализует результаты
-- Это может быть медленнее, чем один большой запрос

-- ❌ Может быть медленнее
WITH expensive_calculation AS (
    SELECT id, complex_calculation() as result
    FROM large_table
)
SELECT * FROM expensive_calculation
WHERE result > 100;

-- ✓ Может быть быстрее (использует фильтр до вычисления)
SELECT id, complex_calculation() as result
FROM large_table
WHERE complex_calculation() > 100;

-- Совет: используйте CTE для улучшения читаемости, 
-- но проверяйте план исполнения чтобы убедиться в эффективности

-- shared_buffers: кеш для блоков таблиц и индексов
-- Рекомендация: 25% от общей оперативной памяти, но не более 40GB
-- На 32GB RAM: 8GB
shared_buffers = 8GB

-- work_mem: память для операций сортировки и хеширования на сессию
-- Рекомендация: total_memory / (max_connections * 2)
-- На 32GB с 200 соединениями: 32GB / (200 * 2) = ~80MB, но минимум 4MB
work_mem = 80MB

-- maintenance_work_mem: память для VACUUM, CREATE INDEX, ALTER TABLE
-- Рекомендация: 10% от RAM или до 2GB
maintenance_work_mem = 2GB

-- effective_cache_size: подсказка планировщику о доступном кеше (OS cache + shared_buffers)
-- Рекомендация: 75% от RAM (общая подсказка, не выделяется реально)
-- На 32GB: 24GB
effective_cache_size = 24GB

-- wal_buffers: буфер для Write-Ahead Log перед записью на диск
-- Рекомендация: 16MB для большинства случаев
wal_buffers = 16MB

-- Установка на сессию (пример для особо требовательной операции)
SET work_mem = '1GB';
SET maintenance_work_mem = '4GB';
SELECT * FROM complex_query_requiring_lots_of_memory();

-- checkpoint_timeout: как часто делать checkpoint (сохранять состояние)
-- Слишком часто = много IO, слишком редко = долгое восстановление после краша
checkpoint_timeout = 15min

-- checkpoint_completion_target: желаемая часть интервала для завершения checkpoint
-- 0.9 = checkpoint должен закончиться за 90% времени интервала
checkpoint_completion_target = 0.9

-- max_wal_size: максимальный размер WAL перед принудительным checkpoint
# На 32GB: 4GB (даст время на восстановление, но не съест всю память)
max_wal_size = 4GB

-- min_wal_size: минимальный размер WAL перед его переработкой
min_wal_size = 1GB

-- synchronous_commit: когда считать транзакцию закоммиченной
-- on: медленнее, но гарантирует сохранение на диск
-- remote_apply: реплика получила данные
-- local: данные в wal_buffers (еще на диске сервера)
-- off: асинхронно (быстро, но может потеряться при краше)
synchronous_commit = on

-- Мониторинг checkpoint активности
SELECT * FROM pg_stat_bgwriter;
-- checkpoints_timed: количество плановых checkpoints
-- checkpoints_req: количество принудительных checkpoints (когда max_wal_size достигнут)
-- buffers_checkpoint: количество буферов записано при checkpoint

-- seq_page_cost: стоимость чтения одной страницы последовательно
# Для SSD может быть меньше (1.0 вместо 4.0)
seq_page_cost = 1.0

-- random_page_cost: стоимость чтения случайной страницы
-- Для SSD: 1.1 (почти как последовательное на SSD)
-- Для HDD: 4.0 (очень медленно)
random_page_cost = 1.1

-- cpu_tuple_cost: стоимость обработки одной строки
cpu_tuple_cost = 0.01

-- cpu_index_tuple_cost: стоимость обработки одной строки в индексе
cpu_index_tuple_cost = 0.005

-- cpu_operator_cost: стоимость операции (сравнение, математика)
cpu_operator_cost = 0.0025

-- default_statistics_target: сколько образцов брать для ANALYZE
-- Больше = точнее оценки, но ANALYZE медленнее
-- 100 по умолчанию, может быть до 10000 для важных таблиц
default_statistics_target = 100

-- Для отдельной таблицы
ALTER TABLE important_table SET (n_distinct_inherited = 10000);

-- max_parallel_workers: максимум рабочих процессов глобально
# На 8-ядерном сервере: 8
max_parallel_workers = 8

-- max_parallel_workers_per_gather: рабочих на одну операцию Gather
-- Рекомендация: половина от max_parallel_workers
max_parallel_workers_per_gather = 4

-- max_parallel_maintenance_workers: рабочих для VACUUM, CREATE INDEX
max_parallel_maintenance_workers = 4

-- min_parallel_table_scan_size: минимальный размер таблицы для параллелизма
-- Слишком маленькие таблицы не параллелятся (не стоит овердхед)
min_parallel_table_scan_size = 8MB

-- parallel_tuple_cost: стоимость обработки одной строки в параллельном режиме
parallel_tuple_cost = 0.1

-- parallel_setup_cost: овердхед запуска параллельных рабочих
parallel_setup_cost = 500.0

-- VACUUM (не блокирует чтение)
VACUUM users;

-- VACUUM ANALYZE (повторно собрать статистику)
VACUUM ANALYZE users;

-- VACUUM FULL (полная перестройка, блокирует все операции)
-- Используется редко, может быть медленным
VACUUM FULL users;

-- Асинхронный VACUUM (не блокирует, но медленнее)
VACUUM ANALYZE VERBOSE users;

-- Включить автоматический VACUUM (по умолчанию включен)
autovacuum = on

-- Максимум рабочих процессов autovacuum
autovacuum_max_workers = 6

-- Как часто проверять нужен ли vacuum (в секундах)
autovacuum_naptime = 15s

-- Запустить VACUUM если삭제/обновлено более 50 строк
autovacuum_vacuum_threshold = 50

-- ИЛИ если обновлено более 10% таблицы
autovacuum_vacuum_scale_factor = 0.1

-- Стоимость операции VACUUM (чтобы не убить производительность)
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 10ms

-- Аналогично для ANALYZE
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

-- Таблица часто изменяется (много UPDATE/DELETE), нужно чаще чистить
ALTER TABLE active_logs SET (
    autovacuum_vacuum_threshold = 10,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 5
);

-- Таблица только добавления (append-only), редко меняется
ALTER TABLE audit_logs SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.5,
    autovacuum_analyze_scale_factor = 0.1
);

-- Bloat в таблицах (мертвые строки)
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_percent,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000 OR (n_live_tup + n_dead_tup > 0 AND 
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 10)
ORDER BY n_dead_tup DESC;

-- Bloat в индексах
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_scan
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024
ORDER BY pg_relation_size(indexrelid) DESC;

-- Прогресс текущей операции VACUUM
SELECT 
    pid,
    datname,
    relname,
    phase,
    heap_blks_scanned,
    heap_blks_total,
    ROUND(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 1) as percent_complete
FROM pg_stat_progress_vacuum;

-- Range партиционирование (по диапазонам значений)
CREATE TABLE orders (
    id SERIAL,
    customer_id INTEGER,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

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

-- List партиционирование (по списку значений)
CREATE TABLE sales (
    id SERIAL,
    country VARCHAR,
    amount NUMERIC
) PARTITION BY LIST (country);

CREATE TABLE sales_eu PARTITION OF sales
    FOR VALUES IN ('DE', 'FR', 'IT', 'ES');
CREATE TABLE sales_us PARTITION OF sales
    FOR VALUES IN ('US', 'CA', 'MX');

-- Hash партиционирование (равномерное распределение)
CREATE TABLE metrics (
    id SERIAL,
    server_id INTEGER,
    value NUMERIC,
    ts TIMESTAMP
) PARTITION BY HASH (server_id);

CREATE TABLE metrics_0 PARTITION OF metrics FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE metrics_1 PARTITION OF metrics FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE metrics_2 PARTITION OF metrics FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE metrics_3 PARTITION OF metrics FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Partition Pruning: автоматическое исключение ненужных партиций из плана
-- Работает только когда условие в WHERE может быть вычислено на этапе планирования
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-05-15';
-- Покажет, что сканируется только orders_2024_q2

-- Для эффективного pruning используйте констант или параметры:

-- ✓ Хорошо
SELECT * FROM orders WHERE order_date = '2024-05-15';
SELECT * FROM orders WHERE order_date >= '2024-05-01' AND order_date < '2024-06-01';

-- ❌ Не работает pruning (функция)
SELECT * FROM orders WHERE order_date = CURRENT_DATE;

-- ✓ Работает если явно кастировать
SELECT * FROM orders WHERE order_date = CURRENT_DATE::date;

-- Параметры подключения
max_connections = 200              -- Максимум соединений в PostgreSQL
superuser_reserved_connections = 3 -- Зарезервировано для администратора

-- Таймауты
statement_timeout = 300s           -- Максимум на один запрос (5 минут)
idle_in_transaction_session_timeout = 60s  -- Максимум неиспользуемой транзакции

-- Для Connection Pooling используйте PgBouncer или pgpool-II
-- PgBouncer конфиг (/etc/pgbouncer/pgbouncer.ini):
# [databases]
# mydb = host=localhost port=5432 dbname=mydb
#
# [pgbouncer]
# pool_mode = transaction
# max_client_conn = 1000
# default_pool_size = 25
# min_pool_size = 10
# reserve_pool_size = 5
# reserve_pool_timeout = 3

-- Создание материализованного представления
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT 
    u.id,
    u.name,
    COUNT(o.id) as total_orders,
    SUM(o.amount) as total_spent,
    AVG(o.amount) as avg_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Индекс для быстрого поиска
CREATE INDEX idx_user_order_summary_id ON user_order_summary (id);

-- Использование
SELECT * FROM user_order_summary WHERE id = 123;

-- Обновление (блокирует читающие запросы)
REFRESH MATERIALIZED VIEW user_order_summary;

-- Обновление без блокировки (PostgreSQL 9.5+)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

-- Регулярное обновление через pg_cron расширение
SELECT cron.schedule('refresh_user_order_summary', '0 2 * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary');

-- Вместо JOIN каждый раз:
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- Хранить денормализованно в отдельной таблице:
CREATE TABLE user_summary (
    user_id INTEGER PRIMARY KEY,
    name VARCHAR,
    order_count INTEGER,
    total_spent NUMERIC,
    last_updated TIMESTAMP
);

-- Обновлять через триггер или batch job
UPDATE user_summary SET 
    order_count = (SELECT COUNT(*) FROM orders WHERE user_id = user_id),
    total_spent = (SELECT SUM(amount) FROM orders WHERE user_id = user_id),
    last_updated = now()
WHERE user_id = NEW.user_id;

-- Prepared statements предотвращают SQL injection и кешируют план
-- Приложение (например, Python):

import psycopg2

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

# Подготовить запрос
cur.execute("""
    PREPARE get_user (INTEGER) AS
    SELECT * FROM users WHERE id = $1;
""")

# Выполнить несколько раз (план переиспользуется)
cur.execute("EXECUTE get_user (%s)", (123,))
user1 = cur.fetchone()

cur.execute("EXECUTE get_user (%s)", (456,))
user2 = cur.fetchone()

# Мониторинг prepared statements
SELECT 
    name,
    statement,
    calls,
    total_time / calls as avg_time
FROM pg_prepared_statements
ORDER BY calls DESC;

-- READ COMMITTED (по умолчанию) - быстро, но может быть грязные чтения между операциями
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ - строже, гарантирует консистентность
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE - максимальная изоляция, самая медленная
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Для обычных OLTP операций используйте READ COMMITTED
-- Для аналитики/отчетов используйте REPEATABLE READ
-- SERIALIZABLE нужен редко и может привести к конфликтам

-- ❌ Медленно: каждый INSERT в отдельной транзакции (1000 операций в синхронизацией с диском)
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');
-- ~1000 миллисекунд для 1000 строк

-- ✓ Быстро: все в одной транзакции (синхронизация только в конце)
BEGIN;
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');
-- ... еще 998 INSERT
COMMIT;
-- ~10-50 миллисекунд

-- ✓ Еще быстрее: VALUES с множественными строками
INSERT INTO users (name, email) VALUES 
    ('John', 'john@example.com'),
    ('Jane', 'jane@example.com'),
    -- ... еще 998 строк
;

-- ✓ Максимально быстро: COPY (для очень больших объемов)
COPY users (name, email) FROM STDIN;
John    john@example.com
Jane    jane@example.com
\.

-- Пример Python с batch вставками:
def bulk_insert_users(users_list, batch_size=1000):
    for i in range(0, len(users_list), batch_size):
        batch = users_list[i:i + batch_size]
        placeholders = ','.join(['(%s, %s)'] * len(batch))
        query = f"INSERT INTO users (name, email) VALUES {placeholders}"
        values = []
        for user in batch:
            values.extend([user['name'], user['email']])
        cursor.execute(query, values)
    conn.commit()

-- Установить baseline для мониторинга
-- Эти метрики нужно отслеживать регулярно

-- 1. Размер базы данных
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
WHERE datname = 'mydb';

-- 2. Размер таблиц
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 3. Загруженность кеша (hit ratio должна быть > 99%)
SELECT 
    'index hit rate' as name,
    ROUND(100.0 * sum(CASE WHEN idx_blks_hit > 0 THEN idx_blks_hit ELSE 0 END) / 
        NULLIF(sum(idx_blks_hit + idx_blks_read), 0), 2) as ratio
FROM pg_statio_user_indexes
UNION
SELECT 
    'table hit rate' as name,
    ROUND(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2)
FROM pg_statio_user_tables;

-- 4. Активные соединения
SELECT 
    state,
    COUNT(*) as count
FROM pg_stat_activity
GROUP BY state;

-- 5. Блокировки и deadlocks
SELECT 
    deadlocks,
    conflicts
FROM pg_stat_database
WHERE datname = 'mydb';

SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column1
HAVING condition
ORDER BY column1 ASC|DESC
LIMIT count OFFSET start;

SELECT * FROM users;                          -- Все колонки
SELECT id, name, email FROM users;            -- Конкретные колонки
SELECT id AS user_id, name AS full_name FROM users;  -- Переименование

SELECT DISTINCT country FROM users;           -- Уникальные значения

SELECT * FROM users LIMIT 10 OFFSET 20;      -- Пагинация

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE country IN ('USA', 'Canada');
SELECT * FROM users WHERE email IS NOT NULL;

SELECT * FROM users WHERE name LIKE 'John%';          -- John*, регистрозависимо
SELECT * FROM users WHERE name ILIKE '%john%';        -- Любая часть, регистронезависимо
SELECT * FROM users WHERE email ~ '^[a-z]+@';         -- Регулярное выражение

SELECT * FROM users WHERE age > 18 AND country = 'USA';
SELECT * FROM users WHERE age < 18 OR age > 65;

SELECT COUNT(*) FROM users;                    -- Общее количество строк
SELECT COUNT(DISTINCT country) FROM users;     -- Уникальные значения

SELECT SUM(salary), AVG(age), MIN(created_at), MAX(created_at)
FROM employees;

SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;
SELECT ARRAY_AGG(name ORDER BY created_at) FROM users;

SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country;

SELECT country, AVG(salary) as avg_salary
FROM employees
GROUP BY country
HAVING AVG(salary) > 50000;

SELECT region, country, SUM(sales)
FROM orders
GROUP BY ROLLUP(region, country);

SELECT country, product, SUM(amount)
FROM sales
GROUP BY CUBE(country, product);

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

SELECT * FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

SELECT * FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

SELECT p.name, c.name
FROM products p
CROSS JOIN categories c;

SELECT e1.name, e2.name as manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

SELECT u.name, o.total, p.name as product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

SELECT * FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

SELECT avg_salary_by_dept.department, avg_salary_by_dept.avg_salary
FROM (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
) avg_salary_by_dept
WHERE avg_salary > 50000;

SELECT name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num
FROM employees;

SELECT name, score,
       RANK() OVER (ORDER BY score DESC) as rank,
       DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM students;

SELECT date, amount,
       LAG(amount, 1) OVER (ORDER BY date) as prev_amount,
       LEAD(amount, 1) OVER (ORDER BY date) as next_amount
FROM sales;

SELECT name, salary, department,
       AVG(salary) OVER (PARTITION BY department) as dept_avg,
       SUM(salary) OVER (ORDER BY hire_date) as running_total
FROM employees;

SELECT name, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary,
       LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
FROM employees;

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) as count
FROM high_earners
GROUP BY department;

WITH 
    dept_stats AS (
        SELECT department, AVG(salary) as avg_salary
        FROM employees GROUP BY department
    ),
    high_salary_depts AS (
        SELECT department FROM dept_stats WHERE avg_salary > 70000
    )
SELECT e.name, e.salary, e.department
FROM employees e
JOIN high_salary_depts hsd ON e.department = hsd.department;

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

SELECT product_id FROM order_items_2023
UNION ALL
SELECT product_id FROM order_items_2024;

SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

SELECT product_id FROM inventory
EXCEPT
SELECT product_id FROM sold_products;

SELECT name, age,
       CASE
           WHEN age < 18 THEN 'Несовершеннолетний'
           WHEN age BETWEEN 18 AND 65 THEN 'Взрослый'
           ELSE 'Пенсионер'
       END as age_group
FROM users;

SELECT name, COALESCE(phone, email, 'No contact') as contact
FROM users;

SELECT name, NULLIF(phone, '') as phone
FROM users;

SELECT GREATEST(10, 20, 30) as max_value;
SELECT LEAST(10, 20, 30) as min_value;

SELECT NOW(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

SELECT EXTRACT(YEAR FROM created_at) as year,
       EXTRACT(MONTH FROM created_at) as month,
       EXTRACT(DAY FROM created_at) as day
FROM orders;

SELECT DATE_TRUNC('month', created_at) as month,
       COUNT(*) as orders_count
FROM orders
GROUP BY 1;

SELECT created_at + INTERVAL '1 day' as tomorrow,
       created_at - INTERVAL '1 month' as month_ago
FROM orders;

SELECT name, AGE(NOW(), birth_date) as age
FROM users;

SELECT first_name || ' ' || last_name as full_name FROM users;
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

SELECT UPPER(name), LOWER(email), INITCAP(city) FROM users;

SELECT TRIM('  hello  ') as trimmed;  -- 'hello'

SELECT LENGTH(name), POSITION('@' IN email), SUBSTRING(name FROM 1 FOR 5)
FROM users;

SELECT REPLACE(phone, '-', '') as phone_clean,
       REGEXP_REPLACE(email, '@.*', '') as username
FROM users;

SELECT SPLIT_PART(email, '@', 1) as username,
       SPLIT_PART(email, '@', 2) as domain
FROM users;

SELECT metadata->>'name' as name,
       metadata->'age' as age,
       metadata->'address'->>'city' as city
FROM users;

SELECT * FROM products WHERE attributes @> '{\"color\": \"red\"}';
SELECT * FROM products WHERE attributes ? 'warranty';
SELECT * FROM products WHERE attributes ?| ARRAY['size', 'color'];

SELECT JSON_BUILD_OBJECT('name', name, 'email', email) as user_json
FROM users;

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) as users
FROM users;

SELECT ARRAY[1, 2, 3, 4, 5];
SELECT ARRAY_AGG(name) FROM users;

SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
SELECT * FROM articles WHERE ARRAY['sql'] <@ tags;
SELECT * FROM articles WHERE tags && ARRAY['database', 'sql'];

SELECT ARRAY_LENGTH(tags, 1) as tags_count FROM articles;
SELECT UNNEST(ARRAY[1, 2, 3]);

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

BEGIN;
    DELETE FROM orders WHERE id = 123;
    ROLLBACK;  -- Отменяет изменения

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    SAVEPOINT sp1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    ROLLBACK TO sp1;  -- Откатывает только второе обновление
    -- Первое обновление остаётся
COMMIT;

INSERT INTO users (id, email, name)
VALUES (1, 'john@example.com', 'John')
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email, name = EXCLUDED.name;

INSERT INTO users (email, name)
VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

INSERT INTO products (id, name, price)
VALUES (1, 'Product', 100)
ON CONFLICT (id) DO UPDATE
SET price = EXCLUDED.price
WHERE products.price < EXCLUDED.price;

SELECT u.name, recent_orders.order_date, recent_orders.total
FROM users u
CROSS JOIN LATERAL (
    SELECT order_date, total
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY order_date DESC
    LIMIT 5
) recent_orders;

INSERT INTO users (name, email)
VALUES ('John', 'john@example.com')
RETURNING id, name, created_at;

UPDATE users
SET last_login = NOW()
WHERE id = 1
RETURNING id, name, last_login;

DELETE FROM users
WHERE inactive = true
RETURNING id, name, email;

-- Транзакция 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Видит 1000
-- Пока идёт обработка...

-- Транзакция 2 (параллельно)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Видит те же 1000
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Вернёмся к Транзакции 1
UPDATE accounts SET balance = 1500 WHERE id = 1;  -- Потерянное обновление!
COMMIT;

-- Правильный вариант
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- Блокируем строку
-- Теперь никто другой не может изменять эту строку
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Другие транзакции могут читать, но не могут:

--   - UPDATE эту строку
--   - DELETE эту строку
--   - SELECT FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

CREATE OR REPLACE FUNCTION reserve_product(p_product_id INT, p_qty INT)
RETURNS BOOLEAN AS $$
DECLARE
    v_current_qty INT;
BEGIN
    -- Блокируем на чтение
    SELECT quantity INTO v_current_qty
    FROM products WHERE id = p_product_id FOR UPDATE;
    
    -- Проверяем наличие
    IF v_current_qty < p_qty THEN
        RETURN FALSE;  -- Недостаточно товара
    END IF;
    
    -- Резервируем
    UPDATE products SET quantity = quantity - p_qty WHERE id = p_product_id;
    INSERT INTO reservations (product_id, qty, reserved_at)
    VALUES (p_product_id, p_qty, NOW());
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT * FROM users WHERE id = 1 FOR NO KEY UPDATE;
-- Другие транзакции:

--   ✓ Могут читать
--   ✓ Могут INSERT с FK на эту строку
--   ✗ Не могут UPDATE/DELETE
--   ✗ Не могут FOR UPDATE / FOR NO KEY UPDATE на эту строку
UPDATE users SET name = 'New Name' WHERE id = 1;
COMMIT;

-- Сценарий 1: Обновляем пользователя
BEGIN;
SELECT * FROM users WHERE id = 1 FOR NO KEY UPDATE;
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Параллельно другой процесс МОЖЕТ выполнить:
INSERT INTO user_profiles (user_id, bio)
VALUES (1, 'Some bio');  -- Создаёт FK на users.id = 1
-- Это пройдёт, потому что мы использовали FOR NO KEY UPDATE

COMMIT;

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Другие транзакции:

--   ✓ Могут читать
--   ✓ Могут SELECT FOR SHARE на эту строку
--   ✗ Не могут UPDATE/DELETE
--   ✗ Не могут FOR UPDATE / FOR NO KEY UPDATE / FOR KEY SHARE
COMMIT;

-- Процесс 1 и Процесс 2 одновременно
BEGIN;
SELECT balance INTO v_balance FROM accounts WHERE id = 1 FOR SHARE;

-- Выполняют сложные расчёты
v_interest := v_balance * calculate_interest_rate();

-- Другой процесс может делать то же самое параллельно
-- Но никто не может UPDATE эту строку

INSERT INTO interest_log (account_id, amount, calc_date)
VALUES (1, v_interest, NOW());

COMMIT;

BEGIN;
SELECT * FROM users WHERE id = 1 FOR KEY SHARE;
-- Другие транзакции:

--   ✓ Могут читать
--   ✓ Могут SELECT FOR SHARE / FOR KEY SHARE
--   ✓ Могут UPDATE неключевые поля
--   ✗ Не могут DELETE
--   ✗ Не могут UPDATE первичный ключ
--   ✗ Не могут FOR UPDATE / FOR NO KEY UPDATE
COMMIT;

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- Если строка уже заблокирована другой транзакцией:

-- → ошибка "lock_not_available" — ОШИБКА: could not obtain lock on row
-- Не ждём бесконечно, не блокируем приложение
COMMIT;

CREATE OR REPLACE FUNCTION try_reserve_product(p_product_id INT, p_qty INT)
RETURNS TABLE(success BOOLEAN, message TEXT) AS $$
DECLARE
    v_current_qty INT;
BEGIN
    -- Пытаемся захватить блокировку без ожидания
    SELECT quantity INTO v_current_qty
    FROM products WHERE id = p_product_id FOR UPDATE NOWAIT;
    
    IF v_current_qty >= p_qty THEN
        UPDATE products SET quantity = quantity - p_qty WHERE id = p_product_id;
        RETURN QUERY SELECT TRUE, 'Reserved'::TEXT;
    ELSE
        RETURN QUERY SELECT FALSE, 'Insufficient quantity'::TEXT;
    END IF;
    
EXCEPTION WHEN lock_not_available THEN
    -- Товар уже зарезервирован кем-то, не ждём
    RETURN QUERY SELECT FALSE, 'Product is being processed by another request'::TEXT;
END;
$$ LANGUAGE plpgsql;

try {
    val success = db.queryOne("SELECT try_reserve_product(?, ?)", productId, qty)
    if (success) {
        // Резервирование успешно
    } else {
        // Попробуем другой товар или вернём ошибку клиенту
    }
} catch (e: LockNotAvailableException) {
    // Товар в данный момент заблокирован, предложим другой товар
    logger.info("Product locked, trying alternative")
}

-- Простой пример
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY priority DESC
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- Получим первую незаблокированную задачу

CREATE OR REPLACE FUNCTION get_next_task_for_worker()
RETURNS TABLE(task_id INT, task_data JSONB, priority INT) AS $$
BEGIN
    RETURN QUERY
    UPDATE tasks
    SET 
        status = 'processing',
        worker_pid = pg_backend_pid(),
        started_at = NOW()
    WHERE id = (
        SELECT id FROM tasks
        WHERE status = 'pending'
        ORDER BY priority DESC, created_at ASC
        FOR UPDATE SKIP LOCKED  -- Пропустим уже взятые задачи
        LIMIT 1
    )
    RETURNING id, data, priority;
END;
$$ LANGUAGE plpgsql;

-- Если 5 воркеров одновременно вызывают get_next_task_for_worker():

-- Поток 1: Видит tasks id=1,2,3,4,5 - берёт 1 (priority=100, чтение FOR UPDATE)
-- Поток 2: Видит 2,3,4,5 (1 пропущена SKIP LOCKED) - берёт 2
-- Поток 3: Видит 3,4,5 - берёт 3
-- и т.д.

-- Без SKIP LOCKED:

-- Поток 1: Берёт 1, получает блокировку
-- Поток 2: Пытается для 1, ждёт... ждёт... (блокирован!)
-- Это замораживает очередь

-- Все потоки пытаются взять одну задачу
SELECT id FROM tasks WHERE status = 'pending' ORDER BY priority DESC LIMIT 1
FOR UPDATE;  -- Все ждут получить одну строку, конфликт!

SELECT u.id, u.name, a.balance
FROM users u
JOIN accounts a ON u.id = a.user_id
WHERE u.id = 1
FOR UPDATE OF u;  -- Только users.id=1 заблокирована
-- accounts строка НЕ заблокирована!

-- Операция: переводим деньги между счётами
BEGIN;
SELECT a1.id, a1.balance, a2.id, a2.balance
FROM accounts a1
JOIN accounts a2 ON a1.user_id = a2.user_id
WHERE a1.id = 100 AND a2.id = 200
FOR UPDATE OF a1;  -- Блокируем только счёт отправителя

-- Вторая половина операции может идти параллельно для других счётов!
UPDATE accounts SET balance = balance - 100 WHERE id = 100;
COMMIT;

-- Параллельно:
BEGIN;
SELECT * FROM accounts WHERE id = 200 FOR UPDATE;  -- Это может выполниться!
COMMIT;

INSERT INTO table VALUES (...);            -- ROW EXCLUSIVE
UPDATE table SET ...;                      -- ROW EXCLUSIVE
DELETE FROM table WHERE ...;               -- ROW EXCLUSIVE
SELECT * FROM table;                       -- ACCESS SHARE
SELECT * FROM table FOR UPDATE;            -- ROW SHARE (не FOR UPDATE!)
DROP TABLE table;                          -- ACCESS EXCLUSIVE
ALTER TABLE table ADD COLUMN ...;          -- ACCESS EXCLUSIVE
TRUNCATE TABLE table;                      -- ACCESS EXCLUSIVE
CREATE INDEX ON table (...);               -- SHARE
VACUUM;                                    -- SHARE UPDATE EXCLUSIVE

BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- Никто не может ничего с этой таблицей делать
-- Даже SELECT!
COMMIT;

-- Сессия 1
BEGIN;
ALTER TABLE products ADD COLUMN new_col INT;  -- ACCESS EXCLUSIVE
-- Таблица полностью заморозена!

-- Сессия 2 (параллельно)
SELECT * FROM products;  -- ЖДЁТ! ACCESS SHARE конфликтует с ACCESS EXCLUSIVE
-- INSERT, UPDATE, DELETE - всё ждёт!

-- Пока Сессия 1 не COMMIT, вся таблица недоступна

-- Захватить блокировку
SELECT pg_advisory_lock(12345);

-- Попытаться захватить (неблокирующее)
SELECT pg_try_advisory_lock(12345);  -- true если получилась, false если заблокирована

-- Освободить
SELECT pg_advisory_unlock(12345);

-- Освободить все
SELECT pg_advisory_unlock_all();

-- Сессия 1
SELECT pg_advisory_lock(100);  -- Получаем блокировку
-- Ждём...

-- Сессия 2
SELECT pg_advisory_lock(100);  -- Ждёт пока Сессия 1 не освободит!
-- (Это блокирует сессию 2, как FOR UPDATE)

BEGIN;
SELECT pg_advisory_xact_lock(12345);  -- Автоматически освободится
-- Работаем...
COMMIT;  -- Блокировка автоматически освобождена

-- Неблокирующая версия
BEGIN;
SELECT pg_try_advisory_xact_lock(12345);
COMMIT;

CREATE OR REPLACE FUNCTION ensure_singleton_process(p_process_name TEXT)
RETURNS BOOLEAN AS $$
DECLARE
    v_lock_id BIGINT;
BEGIN
    v_lock_id := ('x' || md5(p_process_name))::bit(64)::bigint;
    
    -- Пытаемся получить блокировку без ожидания
    IF pg_try_advisory_lock(v_lock_id) THEN
        RAISE LOG 'Process % started', p_process_name;
        RETURN TRUE;
    ELSE
        RAISE LOG 'Process % already running', p_process_name;
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Использование в приложении (например, Kotlin coroutine)

fun processBackgroundJob(jobName: String) {
    val lockId = jobName.hashCode().toLong()
    
    try {
        db.queryOne("SELECT pg_try_advisory_lock(?)", lockId)?.let { (locked) ->
            if (locked as Boolean) {
                try {
                    // Выполняем долгую операцию
                    performHeavyWork()
                } finally {
                    // Если другой process уже стартовал за время нашей работы,
                    // он начнёт работать сам
                    db.query("SELECT pg_advisory_unlock(?)", lockId)
                }
            } else {
                logger.info("Process $jobName is already running")
            }
        }
    } catch (e: Exception) {
        logger.error("Background job failed", e)
    }
}

CREATE OR REPLACE FUNCTION distribute_paychecks()
RETURNS TABLE(success BOOLEAN, processed_count INT) AS $$
DECLARE
    v_lock_id BIGINT := 1001;  -- Фиксированный ID для этой операции
    v_count INT;
BEGIN
    -- Только один процесс в кластере должен распределять зарплату
    IF NOT pg_try_advisory_xact_lock(v_lock_id) THEN
        RETURN QUERY SELECT FALSE, 0;
        RETURN;
    END IF;
    
    -- Мы получили блокировку - выполняем
    INSERT INTO payroll_transactions (employee_id, amount, date)
    SELECT employee_id, salary, NOW()
    FROM employees
    WHERE status = 'active';
    
    GET DIAGNOSTICS v_count = ROW_COUNT;
    
    RETURN QUERY SELECT TRUE, v_count;
    -- Блокировка автоматически освобождается при COMMIT
END;
$$ LANGUAGE plpgsql;

SELECT
    l.locktype,                    -- Тип: relation (table), extend, etc
    l.relation::regclass AS table, -- Имя таблицы
    l.mode,                        -- Режим: AccessShare, ExclusiveLock, etc
    l.granted,                     -- true если получена, false если ждёт
    a.pid,                         -- ID процесса PostgreSQL
    a.usename,                     -- Username
    a.state,                       -- idle, active, fastpath function call
    a.query,                       -- Текущий SQL запрос
    a.xact_start,                  -- Когда началась транзакция
    NOW() - a.xact_start AS xact_duration
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
ORDER BY l.granted, l.pid;

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocked.query_start AS blocked_since,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query,
    NOW() - blocked.query_start AS wait_time
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON (
    blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.pid != blocking_locks.pid
)
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted
ORDER BY blocked.query_start;

-- Мягко: позволить завершить текущий запрос, потом отключить
SELECT pg_cancel_backend(12345);  -- Отменить текущий запрос

-- Жёстко: сразу отключить
SELECT pg_terminate_backend(12345);  -- Убить соединение

-- ❌ ПЛОХО: блокировка удерживается долго
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
PERFORM some_heavy_computation();  -- 30 секунд!
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- ✓ ХОРОШО: минимизируем время блокировки
v_data := (SELECT * FROM accounts WHERE id = 1);
PERFORM some_heavy_computation();  -- Без блокировки
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Сессия 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
PERFORM pg_sleep(1);
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

-- Сессия 2 (параллельно)
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
PERFORM pg_sleep(1);
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- Deadlock!

-- ✓ Решение: всегда блокируем в одном порядке
-- Обе сессии блокируют сначала id=1, потом id=2
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

-- ❌ Ненужное усложнение: блокируем, но не изменяем
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
PERFORM complex_calculation();
COMMIT;  -- Ничего не изменили!

-- ❌ Приложение упадёт с ошибкой
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- ✓ Правильно: обработайте ошибку
BEGIN;
    SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
EXCEPTION WHEN lock_not_available THEN
    -- Пробуйте позже или вернётё ошибку клиенту
    RAISE EXCEPTION 'Resource is busy, try again later';
END;

-- Пессимистичная: заблокировать немедленно
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Оптимистичная: прочитать, затем проверить при обновлении
SELECT id, balance, version FROM accounts WHERE id = 1;  -- version = 5
UPDATE accounts SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;  -- Проверяем, что версия не изменилась

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 1,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Проверка: сколько строк было обновлено?
GET DIAGNOSTICS affected_rows = ROW_COUNT;
IF affected_rows = 0 THEN
    RAISE EXCEPTION 'Optimistic lock failure: data was modified';
END IF;

CREATE OR REPLACE FUNCTION transfer_money_optimistic(
    from_account_id INT,
    to_account_id INT,
    amount DECIMAL(10,2),
    max_retries INT DEFAULT 3
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
    affected_rows INT;
BEGIN
    WHILE retry_count < max_retries LOOP
        BEGIN
            -- Пытаемся обновить оба счета атомарно
            -- Если версии не совпадают, ни одно обновление не произойдет
            UPDATE accounts 
            SET balance = balance - amount, version = version + 1
            WHERE id = from_account_id 
              AND balance >= amount;
            
            -- Проверяем, обновилась ли строка
            GET DIAGNOSTICS affected_rows = ROW_COUNT;
            IF affected_rows = 0 THEN
                -- Либо версия конфликт, либо недостаточно средств
                RAISE EXCEPTION 'Insufficient funds or lock conflict on source account';
            END IF;

            -- Теперь обновляем счет получателя
            UPDATE accounts 
            SET balance = balance + amount, version = version + 1
            WHERE id = to_account_id;
            
            GET DIAGNOSTICS affected_rows = ROW_COUNT;
            IF affected_rows = 0 THEN
                -- Конфликт версий на счете получателя
                RAISE EXCEPTION 'Lock conflict on target account';
            END IF;

            -- Если мы здесь, оба обновления успешны
            RETURN TRUE;

        EXCEPTION WHEN OTHERS THEN
            -- Конфликт или другая ошибка — повторяем
            retry_count := retry_count + 1;
            IF retry_count >= max_retries THEN
                -- Исчерпли количество попыток
                RAISE;
            END IF;
            -- Экспоненциальная задержка перед повтором: 10ms, 20ms, 30ms
            PERFORM pg_sleep(0.01 * retry_count);
        END;
    END LOOP;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    last_modified TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Приложение читает текущий timestamp
SELECT id, price, quantity, last_modified FROM products WHERE id = 1;
-- Результат: last_modified = '2024-01-15 10:30:45.123456'

-- Обновление с проверкой timestamp
UPDATE products
SET price = 99.99, quantity = quantity - 1, last_modified = NOW()
WHERE id = 1 AND last_modified = '2024-01-15 10:30:45.123456';

-- Если кто-то изменил этот товар, last_modified уже другой, UPDATE затронет 0 строк

-- Явно выбираем xmin
SELECT id, balance, xmin FROM accounts WHERE id = 1;
-- xmin = 12345 (например)

-- Обновляем с проверкой xmin
UPDATE accounts
SET balance = balance - 100.00
WHERE id = 1 AND xmin = 12345::xid;

public class Account {
    private Long id;
    private BigDecimal balance;
    private Integer version;

    public Account() {}
    
    public Account(Long id, BigDecimal balance, Integer version) {
        this.id = id;
        this.balance = balance;
        this.version = version;
    }

    // Геттеры, сеттеры опущены для краткости
    public Long getId() { return id; }
    public BigDecimal getBalance() { return balance; }
    public Integer getVersion() { return version; }
    public void setBalance(BigDecimal balance) { this.balance = balance; }
    public void setVersion(Integer version) { this.version = version; }
}

public class AccountDAO {
    private final DataSource dataSource;

    public AccountDAO(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Account findById(Long id) throws SQLException {
        String sql = "SELECT id, balance, version FROM accounts WHERE id = ?";
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new Account(
                        rs.getLong("id"),
                        rs.getBigDecimal("balance"),
                        rs.getInt("version")
                    );
                }
                return null;
            }
        }
    }

    // Критический метод: обновляем только если версия совпадает
    public boolean updateBalance(Long id, BigDecimal newBalance, Integer expectedVersion)
            throws SQLException {
        String sql = "UPDATE accounts SET balance = ?, version = version + 1 " +
                    "WHERE id = ? AND version = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setBigDecimal(1, newBalance);
            stmt.setLong(2, id);
            stmt.setInt(3, expectedVersion);

            // executeUpdate() возвращает количество затронутых строк
            int affected = stmt.executeUpdate();
            return affected > 0;  // true = успешно, false = конфликт версий
        }
    }
}

public class AccountService {
    private final AccountDAO accountDAO;
    private static final int MAX_RETRIES = 3;
    private static final int RETRY_DELAY_MS = 50;

    public AccountService(AccountDAO accountDAO) {
        this.accountDAO = accountDAO;
    }

    public void transferMoney(Long fromId, Long toId, BigDecimal amount) 
            throws SQLException, OptimisticLockException {
        
        for (int attempt = 1; attempt <= MAX_RETRIES; attempt++) {
            try {
                // Прочитаем оба счета с их текущими версиями
                Account fromAccount = accountDAO.findById(fromId);
                Account toAccount = accountDAO.findById(toId);

                if (fromAccount == null || toAccount == null) {
                    throw new IllegalArgumentException("Account not found");
                }

                // Проверка бизнес-логики
                if (fromAccount.getBalance().compareTo(amount) < 0) {
                    throw new IllegalArgumentException("Insufficient funds");
                }

                // Вычисляем новые балансы
                BigDecimal newFromBalance = fromAccount.getBalance().subtract(amount);
                BigDecimal newToBalance = toAccount.getBalance().add(amount);

                // Пытаемся обновить ОБОИХ счетов
                // Если хотя бы один конфликтует, оба должны откатиться
                boolean fromSuccess = accountDAO.updateBalance(fromId, newFromBalance, 
                                                              fromAccount.getVersion());
                boolean toSuccess = accountDAO.updateBalance(toId, newToBalance, 
                                                            toAccount.getVersion());

                if (!fromSuccess || !toSuccess) {
                    // Конфликт: данные изменились между SELECT и UPDATE
                    if (attempt < MAX_RETRIES) {
                        // Экспоненциальная задержка перед повтором
                        Thread.sleep(RETRY_DELAY_MS * attempt);
                        continue;  // Повторяем попытку
                    } else {
                        throw new OptimisticLockException(
                            "Transfer failed after " + MAX_RETRIES + " attempts");
                    }
                }

                // Успех!
                return;

            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
                throw new SQLException("Transfer interrupted", e);
            }
        }
    }
}

public class OptimisticLockException extends Exception {
    public OptimisticLockException(String message) {
        super(message);
    }
}

@Entity
@Table(name = "accounts")
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(precision = 10, scale = 2, nullable = false)
    private BigDecimal balance = BigDecimal.ZERO;

    @Version  // Hibernate автоматически управляет этим полем
    private Integer version;

    @UpdateTimestamp
    @Column(nullable = false)
    private LocalDateTime updatedAt;

    protected Account() {}
    
    public Account(BigDecimal balance) {
        this.balance = balance;
    }

    // Бизнес-методы изменяют состояние
    public void withdraw(BigDecimal amount) {
        if (balance.compareTo(amount) < 0) {
            throw new IllegalArgumentException("Insufficient funds");
        }
        balance = balance.subtract(amount);
        // Заметьте: version НЕ меняем явно, Hibernate сделает это
    }

    public void deposit(BigDecimal amount) {
        if (amount.signum() <= 0) {
            throw new IllegalArgumentException("Amount must be positive");
        }
        balance = balance.add(amount);
    }

    public Long getId() { return id; }
    public BigDecimal getBalance() { return balance; }
    public Integer getVersion() { return version; }
}

@Repository
public interface AccountRepository extends JpaRepository<Account, Long> {}

@Service
@Transactional
public class AccountService {
    private final AccountRepository accountRepository;

    public AccountService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    // @Retryable автоматически перепробует метод при OptimisticLockingFailureException
    @Retryable(
        value = {OptimisticLockingFailureException.class},
        maxAttempts = 3,
        backoff = @Backoff(delay = 50, multiplier = 2)  // 50ms, 100ms, 200ms
    )
    public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
        Account fromAccount = accountRepository.findById(fromId)
            .orElseThrow(() -> new EntityNotFoundException("Source account not found"));
        
        Account toAccount = accountRepository.findById(toId)
            .orElseThrow(() -> new EntityNotFoundException("Target account not found"));

        // Hibernate перехватит эти вызовы и создаст SQL UPDATE с проверкой версии
        fromAccount.withdraw(amount);
        toAccount.deposit(amount);

        // Сохраняем изменения. При flush транзакции Hibernate проверит версии
        accountRepository.save(fromAccount);
        accountRepository.save(toAccount);
        
        // Если версия не совпадает, выбросится OptimisticLockingFailureException
        // и @Retryable повторит весь метод заново
    }

    // Fallback метод, если retry исчерпаны
    @Recover
    public void recoverFromOptimisticLock(OptimisticLockingFailureException ex,
                                         Long fromId, Long toId, BigDecimal amount) {
        // Логирование ошибки, отправка алерта, или graceful degradation
        throw new BusinessException(
            "Transfer failed due to too many concurrent modifications. Try again.",
            ex
        );
    }
}

UPDATE products
SET quantity = quantity - 1, version = version + 1
WHERE id = 1
  AND version = 5                    -- Оптимистичная блокировка
  AND quantity > 0                   -- Товар в наличии
  AND status = 'available'           -- Доступен для продажи
  AND price < 1000;                  -- Цена в разумных пределах

public void updateMultipleAccounts(List<AccountUpdate> updates) throws SQLException {
    String sql = "UPDATE accounts SET balance = ?, version = version + 1 " +
                "WHERE id = ? AND version = ?";

    try (Connection conn = dataSource.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        
        conn.setAutoCommit(false);

        // Добавляем в batch
        for (AccountUpdate update : updates) {
            stmt.setBigDecimal(1, update.getNewBalance());
            stmt.setLong(2, update.getAccountId());
            stmt.setInt(3, update.getExpectedVersion());
            stmt.addBatch();
        }

        // Выполняем batch и получаем количество затронутых строк для каждого
        int[] results = stmt.executeBatch();

        // Проверяем: все ли обновления прошли?
        boolean allSuccess = true;
        for (int i = 0; i < results.length; i++) {
            if (results[i] == 0) {
                // Конфликт версии на i-й записи
                allSuccess = false;
                System.err.println("Conflict at index " + i);
            }
        }

        if (!allSuccess) {
            conn.rollback();
            throw new OptimisticLockException("Some records conflicted");
        }

        conn.commit();
    }
}

public boolean compareAndSwap(Long recordId, String oldValue, String newValue)
        throws SQLException {
    String sql = "UPDATE records SET value = ?, version = version + 1 " +
                "WHERE id = ? AND value = ?";

    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        stmt.setString(1, newValue);
        stmt.setLong(2, recordId);
        stmt.setString(3, oldValue);

        return stmt.executeUpdate() > 0;  // true если успешно, false если oldValue не совпадал
    }
}

// Использование
String current = readValue(recordId);
while (!compareAndSwap(recordId, current, newValue)) {
    // Конфликт: значение изменилось
    current = readValue(recordId);  // Перечитываем
    newValue = computeNewValue(current);  // Пересчитываем
    // Пытаемся снова
}

@Service
public class FailFastAccountService {
    public void transferMoney(Long fromId, Long toId, BigDecimal amount) 
            throws BusinessException {
        try {
            performTransfer(fromId, toId, amount);
        } catch (OptimisticLockingFailureException e) {
            // Сразу сообщаем клиенту: попробуй позже
            throw new BusinessException("Transaction failed due to concurrent access. " +
                                      "Please retry your request.", e);
        }
    }
}

@Service
public class RetryAccountService {
    private static final int MAX_RETRIES = 3;
    private static final long INITIAL_DELAY = 50;  // ms

    public void transferMoney(Long fromId, Long toId, BigDecimal amount)
            throws BusinessException {
        OptimisticLockingFailureException lastException = null;

        for (int attempt = 1; attempt <= MAX_RETRIES; attempt++) {
            try {
                performTransfer(fromId, toId, amount);
                return;  // Успех!
            } catch (OptimisticLockingFailureException e) {
                lastException = e;
                if (attempt < MAX_RETRIES) {
                    long delay = INITIAL_DELAY * (long) Math.pow(2, attempt - 1);
                    try {
                        Thread.sleep(delay);
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        throw new BusinessException("Transfer interrupted", ie);
                    }
                }
            }
        }

        throw new BusinessException(
            "Transfer failed after " + MAX_RETRIES + " attempts",
            lastException
        );
    }
}

@Service
public class MergeAccountService {
    public void updateAccountProfile(Long accountId, AccountUpdateRequest request) {
        int maxAttempts = 3;
        for (int attempt = 0; attempt < maxAttempts; attempt++) {
            try {
                Account current = accountRepository.findById(accountId)
                    .orElseThrow(() -> new EntityNotFoundException("Account not found"));

                // Кто-то мог изменить поля между нашим SELECT и обновлением
                // Мы пытаемся "мержить" изменения: применить запрос к текущему состоянию
                Account merged = new Account(current.getId());
                merged.setVersion(current.getVersion());
                merged.setEmail(request.getEmail() != null ? 
                               request.getEmail() : current.getEmail());
                merged.setPhone(request.getPhone() != null ? 
                               request.getPhone() : current.getPhone());
                merged.setName(request.getName() != null ? 
                              request.getName() : current.getName());

                accountRepository.save(merged);
                return;  // Успех

            } catch (OptimisticLockingFailureException e) {
                if (attempt >= maxAttempts - 1) {
                    throw new BusinessException("Failed to update account after " +
                                              maxAttempts + " attempts", e);
                }
                // Повторяем с новым текущим состоянием
                try {
                    Thread.sleep(50 * (attempt + 1));
                } catch (InterruptedException ie) {
                    Thread.currentThread().interrupt();
                    throw new BusinessException("Update interrupted", ie);
                }
            }
        }
    }
}

-- Транзакция 1                    -- Транзакция 2
BEGIN;
UPDATE accounts SET balance = 1000 WHERE id = 1;
                                   BEGIN;
                                   SELECT balance FROM accounts WHERE id = 1;
                                   -- Видит 1000 (uncommitted)
ROLLBACK;                          -- Но транзакция 1 откатывается!

-- Транзакция 1                    -- Транзакция 2
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Читает 500
                                   BEGIN;
                                   UPDATE accounts SET balance = 1000 WHERE id = 1;
                                   COMMIT;
SELECT balance FROM accounts WHERE id = 1;  -- Читает 1000!
COMMIT;

-- Транзакция 1                    -- Транзакция 2
BEGIN;
SELECT COUNT(*) FROM accounts WHERE balance > 500;  -- 5
                                   INSERT INTO accounts (balance) VALUES (1000);
                                   COMMIT;
SELECT COUNT(*) FROM accounts WHERE balance > 500;  -- 6!
COMMIT;

-- Начальное состояние: Alice = 500$, Bob = 500$
-- Правило: сумма всех счетов >= 0

-- Транзакция 1 (Alice снимает 600$)
BEGIN;
SELECT SUM(balance) FROM accounts;  -- 1000$ (всё ок для снятия 600$)
UPDATE accounts SET balance = balance - 600 WHERE owner = 'Alice';
-- Alice = -100$, Bob = 500$, сумма = 400$
COMMIT;

-- Транзакция 2 (Bob снимает 600$)
BEGIN;
SELECT SUM(balance) FROM accounts;  -- 1000$ (всё ок для снятия 600$)
UPDATE accounts SET balance = balance - 600 WHERE owner = 'Bob';
-- Alice = -100$, Bob = -100$, сумма = -200$ ← нарушено правило!
COMMIT;

-- Для текущей транзакции
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Для сессии
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Глобально в postgresql.conf
default_transaction_isolation = 'repeatable read'

-- Проверка текущего уровня
SHOW transaction_isolation;

-- Сессия 1 (READ COMMITTED)
BEGIN;
SELECT * FROM accounts WHERE id = 1;  -- alice | 1000.00

-- Сессия 2
UPDATE accounts SET balance = 1500.00 WHERE id = 1;
COMMIT;

-- Сессия 1 (новый snapshot для этого SELECT!)
SELECT * FROM accounts WHERE id = 1;  -- alice | 1500.00 (видит изменения!)
COMMIT;

-- Сессия 1
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Установлена row-level блокировка на строку id=1

-- Сессия 2 (READ COMMITTED)
BEGIN;
UPDATE accounts SET balance = balance + 200 WHERE id = 1;  
-- Ждет освобождения блокировки от сессии 1

-- Сессия 1
COMMIT;  -- balance теперь 1100, блокировка снята

-- Сессия 2 АВТОМАТИЧЕСКИ:

-- 1. Получает блокировку
-- 2. Перечитывает СВЕЖУЮ версию строки (1100)
-- 3. Применяет операцию: 1100 + 200 = 1300
COMMIT;

-- Сессия 1 (REPEATABLE READ)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;  -- alice | 1000.00
-- Snapshot зафиксирован

-- Сессия 2
UPDATE accounts SET balance = 1500.00 WHERE id = 1;
COMMIT;

-- Сессия 1 (использует ТОТ ЖЕ snapshot!)
SELECT * FROM accounts WHERE id = 1;  -- alice | 1000.00 (старые данные!)
COMMIT;

-- Сессия 1 (REPEATABLE READ)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM accounts WHERE balance > 600;  -- 2

-- Сессия 2
INSERT INTO accounts (balance) VALUES (800.00);
COMMIT;

-- Сессия 1 (тот же snapshot → новая строка не видна)
SELECT COUNT(*) FROM accounts WHERE balance > 600;  -- Все еще 2
COMMIT;

-- Сессия 1 (REPEATABLE READ)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- 1000.00

-- Сессия 2
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = 1200.00 WHERE id = 1;
COMMIT;  -- Успешно

-- Сессия 1
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;

CREATE OR REPLACE FUNCTION transfer_with_retry(
    from_id INT, to_id INT, amount DECIMAL, max_retries INT DEFAULT 5
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
BEGIN
    WHILE retry_count < max_retries LOOP
        BEGIN
            -- Начинаем новую транзакцию с REPEATABLE READ
            BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

            -- Проверка баланса
            IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
                ROLLBACK;
                RETURN FALSE;
            END IF;

            -- Выполняем перевод
            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;

            COMMIT;
            RETURN TRUE;

        EXCEPTION
            WHEN serialization_failure THEN
                ROLLBACK;
                retry_count := retry_count + 1;
                IF retry_count >= max_retries THEN
                    RAISE;  -- Пробрасываем ошибку выше
                END IF;
                -- Exponential backoff
                PERFORM pg_sleep(0.01 * retry_count);
        END;
    END LOOP;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Начальное состояние: checking = 1000, savings = 1000
-- Правило: SUM(balance) >= 2000

-- Сессия 1 (SERIALIZABLE)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM balances;  -- 2000.00
-- SIRead lock на все прочитанные строки
UPDATE balances SET balance = balance - 100 WHERE account_type = 'checking';
-- checking = 900

-- Сессия 2 (SERIALIZABLE)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM balances;  -- 2000.00
-- SIRead lock на все прочитанные строки
UPDATE balances SET balance = balance - 100 WHERE account_type = 'savings';
-- savings = 900

-- Сессия 1
COMMIT;  -- Успешно

-- Сессия 2
COMMIT;  -- ERROR: could not serialize access due to read/write dependencies

-- Read-only SERIALIZABLE никогда не получают serialization failure
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
SELECT * FROM accounts;
SELECT SUM(balance) FROM accounts;
COMMIT;  -- Всегда успешно

-- Может подождать безопасного snapshot
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT * FROM accounts;
COMMIT;

-- Сессия 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM devices WHERE customer_id = 1;  -- SeqScan!
-- SIRead lock на ВСЮ таблицу, а не только на строки customer_id=1

-- Сессия 2
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM devices WHERE customer_id = 2;  -- SeqScan!
-- SIRead lock на ВСЮ таблицу
INSERT INTO devices (customer_id) VALUES (2);

-- Конфликт, хотя строки разные!
COMMIT;  -- ERROR: could not serialize access

-- postgresql.conf
max_pred_locks_per_transaction = 64      -- Predicate locks на транзакцию
max_pred_locks_per_relation = -2         -- На таблицу (-2 = auto)
max_pred_locks_per_page = 2              -- На страницу

-- Мониторинг predicate locks
SELECT locktype, relation::regclass, mode, granted
FROM pg_locks 
WHERE mode = 'SIReadLock';

-- Начальное состояние: balance = 1000

-- Сессия 1
BEGIN;  -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;  -- 1000
-- ... бизнес-логика на клиенте: new_balance = 1000 + 100 = 1100

-- Сессия 2
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000
-- ... бизнес-логика: new_balance = 1000 + 200 = 1200
UPDATE accounts SET balance = 1200 WHERE id = 1;
COMMIT;

-- Сессия 1
UPDATE accounts SET balance = 1100 WHERE id = 1;  -- Перезаписало 1200!
COMMIT;
-- Потерян UPDATE сессии 2

CREATE OR REPLACE FUNCTION transfer_read_committed(
    from_id INT, to_id INT, amount DECIMAL
) RETURNS BOOLEAN AS $$
BEGIN
    -- Всё в одной транзакции, операции атомарны
    BEGIN;
    
    -- Проверка и списание в одном месте
    IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
        ROLLBACK;
        RETURN FALSE;
    END IF;

    -- UPDATE с relative операцией (не абсолютное значение!)
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    COMMIT;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

BEGIN;
-- Блокируем строку для чтения и изменения
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Другие транзакции будут ждать

-- Вычисляем на клиенте (или в приложении)
-- ... new_balance = balance + 100

UPDATE accounts SET balance = new_balance WHERE id = 1;
COMMIT;

CREATE OR REPLACE FUNCTION transfer_repeatable_read(
    from_id INT, to_id INT, amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
BEGIN
    WHILE retry_count < 5 LOOP
        BEGIN
            BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

            IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
                ROLLBACK;
                RETURN FALSE;
            END IF;

            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;

            COMMIT;
            RETURN TRUE;

        EXCEPTION
            WHEN serialization_failure THEN
                ROLLBACK;
                retry_count := retry_count + 1;
                PERFORM pg_sleep(0.01 * retry_count);
        END;
    END LOOP;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION transfer_serializable(
    from_id INT, to_id INT, amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
BEGIN
    WHILE retry_count < 10 LOOP  -- Больше попыток из-за SSI
        BEGIN
            BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            IF (SELECT balance FROM accounts WHERE id = from_id) < amount THEN
                ROLLBACK;
                RETURN FALSE;
            END IF;

            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;

            COMMIT;
            RETURN TRUE;

        EXCEPTION
            WHEN serialization_failure THEN
                ROLLBACK;
                retry_count := retry_count + 1;
                PERFORM pg_sleep(0.01 * power(2, retry_count));  -- Exponential backoff
        END;
    END LOOP;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Общая статистика по базе
SELECT 
    datname,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    ROUND(100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0), 2) AS rollback_ratio,
    conflicts,
    deadlocks
FROM pg_stat_database
WHERE datname = current_database();

-- Текущие активные транзакции и их длительность
SELECT 
    pid,
    usename,
    state,
    query_start,
    NOW() - query_start AS duration,
    wait_event_type,
    wait_event,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
  AND pid <> pg_backend_pid()
ORDER BY query_start;

-- Блокировки и ожидающие транзакции
SELECT 
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks AS blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
    AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
    AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
    AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
    AND blocked_locks.pid <> blocking_locks.pid
JOIN pg_stat_activity AS blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

-- Текущие predicate locks
SELECT 
    locktype,
    relation::regclass AS table_name,
    page,
    tuple,
    pid,
    mode
FROM pg_locks 
WHERE mode = 'SIReadLock'
ORDER BY relation, page, tuple;

-- Статистика по granularity predicate locks
SELECT 
    relation::regclass AS table_name,
    COUNT(*) FILTER (WHERE tuple IS NOT NULL) AS tuple_locks,
    COUNT(*) FILTER (WHERE page IS NOT NULL AND tuple IS NULL) AS page_locks,
    COUNT(*) FILTER (WHERE page IS NULL AND tuple IS NULL) AS relation_locks
FROM pg_locks 
WHERE mode = 'SIReadLock'
GROUP BY relation
ORDER BY relation_locks DESC, page_locks DESC;

-- postgresql.conf
log_lock_waits = on                    -- Логировать ожидание блокировок
deadlock_timeout = 1s                  -- Через сколько детектить deadlock
log_statement = 'all'                  -- Логировать все statement (осторожно!)
log_duration = on                      -- Логировать длительность запросов
log_min_duration_statement = 1000      -- Логировать только медленные (> 1s)

-- Для отладки serialization failures
log_error_verbosity = verbose          -- Подробные сообщения об ошибках

-- ПЛОХО: долгая транзакция с вычислениями
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts;
-- ... длительные вычисления на клиенте ...
-- ... HTTP запросы, бизнес-логика ...
UPDATE accounts SET balance = calculated_value WHERE id = 1;
COMMIT;

-- ХОРОШО: вычисления вне транзакции
-- Подготовка данных вне транзакции
calculated_value := complex_calculation();

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = calculated_value WHERE id = 1;
COMMIT;

CREATE OR REPLACE FUNCTION execute_with_retry(
    sql_command TEXT,
    isolation_level TEXT DEFAULT 'READ COMMITTED',
    max_retries INT DEFAULT 5
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
    backoff_ms INT;
BEGIN
    WHILE retry_count < max_retries LOOP
        BEGIN
            EXECUTE format('BEGIN TRANSACTION ISOLATION LEVEL %s', isolation_level);
            EXECUTE sql_command;
            COMMIT;
            RETURN TRUE;

        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                ROLLBACK;
                retry_count := retry_count + 1;
                
                IF retry_count >= max_retries THEN
                    RAISE NOTICE 'Max retries exceeded: %', SQLERRM;
                    RAISE;
                END IF;
                
                -- Exponential backoff с jitter
                backoff_ms := (10 * power(2, retry_count))::INT;
                backoff_ms := backoff_ms + (random() * backoff_ms * 0.1)::INT;
                
                RAISE NOTICE 'Retry % after % ms: %', retry_count, backoff_ms, SQLERRM;
                PERFORM pg_sleep(backoff_ms / 1000.0);
        END;
    END LOOP;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Для обычных read-only операций
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT * FROM large_report_view;
COMMIT;
-- Преимущества: не может модифицировать данные, немного быстрее

-- Для долгих отчетов без риска конфликтов
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT * FROM complex_analytical_view;
COMMIT;
-- Преимущества: подождет безопасного snapshot, никогда не получит ошибку

-- Для backup
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
-- pg_dump использует этот паттерн
COMMIT;

-- ПЛОХО: SeqScan → predicate lock на всю таблицу
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM devices WHERE customer_id = 1;  -- SeqScan если таблица мала
COMMIT;

-- ХОРОШО: создать индекс → Index Scan → row-level predicate locks
CREATE INDEX idx_devices_customer_id ON devices(customer_id);

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM devices WHERE customer_id = 1;  -- Index Scan → row locks
COMMIT;

# Python пример retry логики
def execute_with_retry(transaction_func, max_retries=5):
    for attempt in range(max_retries):
        try:
            return transaction_func()
        except psycopg2.extensions.TransactionRollbackError as e:
            if "could not serialize" in str(e) or "deadlock" in str(e):
                if attempt >= max_retries - 1:
                    raise
                backoff = (0.01 * (2 ** attempt)) + random.uniform(0, 0.01)
                time.sleep(backoff)
                continue
            raise

# Использование
def transfer_money():
    conn.execute("BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE")
    # ... бизнес-логика ...
    conn.execute("COMMIT")

execute_with_retry(transfer_money)

-- Создать таблицу для audit
CREATE TABLE balance_audit (
    id SERIAL PRIMARY KEY,
    check_time TIMESTAMP DEFAULT NOW(),
    total_balance DECIMAL,
    constraint_violated BOOLEAN
);

-- Триггер для проверки бизнес-правила
CREATE OR REPLACE FUNCTION check_balance_constraint()
RETURNS TRIGGER AS $$
DECLARE
    total DECIMAL;
BEGIN
    SELECT SUM(balance) INTO total FROM accounts;
    
    INSERT INTO balance_audit (total_balance, constraint_violated)
    VALUES (total, total < 0);
    
    IF total < 0 THEN
        RAISE NOTICE 'Business rule violated: total balance = %', total;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER balance_check_trigger
AFTER INSERT OR UPDATE ON accounts
FOR EACH STATEMENT
EXECUTE FUNCTION check_balance_constraint();

-- Найти строки, которые часто изменяются
SELECT 
    schemaname,
    relname,
    n_tup_upd + n_tup_del AS modifications,
    n_tup_upd,
    n_tup_del,
    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_tup_upd + n_tup_del > 1000
ORDER BY modifications DESC
LIMIT 20;

-- Если dead_ratio высок → много concurrent updates → вероятны конфликты

-- Проверить, использует ли запрос SeqScan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM devices WHERE customer_id = 1;

-- Если видим "Seq Scan" → predicate lock на всю таблицу
-- Решение: добавить индекс или использовать FORCE INDEX

-- Принудительно использовать Index Scan (для теста)
SET enable_seqscan = off;
EXPLAIN SELECT * FROM devices WHERE customer_id = 1;
SET enable_seqscan = on;

-- ПЛОХО
BEGIN;  -- READ COMMITTED
balance := (SELECT balance FROM accounts WHERE id = 1);
IF balance >= 100 THEN
    -- Между SELECT и UPDATE данные могли измениться!
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
END IF;
COMMIT;

-- ХОРОШО: атомарная операция
BEGIN;
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1 AND balance >= 100;
-- Проверить, была ли изменена строка
IF NOT FOUND THEN
    RAISE EXCEPTION 'Insufficient balance';
END IF;
COMMIT;

-- ПЛОХО: без обработки ошибок
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... операции ...
COMMIT;  -- Может упасть с serialization_failure

-- ХОРОШО: с retry
retry_count := 0;
LOOP
    BEGIN
        BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        -- ... операции ...
        COMMIT;
        EXIT;  -- Успешно
    EXCEPTION
        WHEN serialization_failure THEN
            IF retry_count >= 5 THEN RAISE; END IF;
            retry_count := retry_count + 1;
    END;
END LOOP;

-- ПЛОХО: долгая транзакция
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM orders;  -- Snapshot зафиксирован
-- ... 10 секунд обработки на клиенте ...
-- ... HTTP запросы, вычисления ...
UPDATE orders SET status = 'processed';
COMMIT;  -- Высокий шанс serialization_failure

-- ХОРОШО: минимальная транзакция
-- Обработка вне транзакции
processed_ids := process_orders_logic();

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE orders SET status = 'processed' WHERE id = ANY(processed_ids);
COMMIT;

-- ПЛОХО: без индекса → SeqScan → table-level predicate lock
CREATE TABLE events (id INT, user_id INT, event_type TEXT);

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM events WHERE user_id = 123;  -- SeqScan
-- ... другие операции ...
COMMIT;  -- Конфликтует с транзакциями других user_id!

-- ХОРОШО: с индексом → Index Scan → row-level predicate locks
CREATE INDEX idx_events_user_id ON events(user_id);

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM events WHERE user_id = 123;  -- Index Scan
COMMIT;  -- Конфликтует только с тем же user_id

-- Посмотреть размер всех индексов по таблице
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_indexes_size(schemaname||'.'||tablename) DESC;

                     [корень: 50]
                    /            \
           [20, 30, 40]        [60, 70, 80]
          /    |    |    \     /   |   |   \
    [5]  [25]  [35] [45]  [55] [65] [75] [85]

-- Простой индекс на одну колонку
CREATE INDEX idx_users_email ON users (email);

-- Составной индекс (несколько колонок)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- Индекс в обратном порядке (для ORDER BY DESC)
CREATE INDEX idx_products_price_desc ON products (price DESC);

-- Управление NULL значениями
CREATE INDEX idx_users_last_login ON users (last_login DESC NULLS LAST);
-- NULLS LAST = NULL значения в конце (по умолчанию для DESC)
-- NULLS FIRST = NULL значения в начале (по умолчанию для ASC)

-- Уникальный индекс (также выполняет роль constraint)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
-- Гарантирует, что в колонке email не будет дубликатов

-- Все эти запросы используют индекс на age:
SELECT * FROM users WHERE age = 25;                    -- Точный поиск
SELECT * FROM users WHERE age > 18;                   -- Диапазон
SELECT * FROM users WHERE age BETWEEN 18 AND 65;      -- Диапазон
SELECT * FROM users WHERE age IN (25, 30, 35);        -- Несколько значений
SELECT * FROM users WHERE age IS NULL;                -- NULL значения

-- ORDER BY также использует индекс
SELECT * FROM orders ORDER BY created_at DESC;        -- Быстро, если индекс есть
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10; -- Очень быстро!

-- LIKE с префиксом ИСПОЛЬЗУЕТ индекс
SELECT * FROM users WHERE email LIKE 'john%';
-- PostgreSQL понимает, что это поиск в диапазоне [john, john~)

-- LIKE с суффиксом НЕ использует индекс
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Нужно сканировать все значения, потому что паттерн может быть в любом месте

-- Решение: использовать pg_trgm для полнотекстовых поисков
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);
SELECT * FROM users WHERE email LIKE '%gmail%';  -- Теперь быстро!

-- Создание Hash индекса
CREATE INDEX idx_users_id_hash ON users USING HASH (id);

-- Эффективно - работает с Hash индексом
SELECT * FROM users WHERE id = 12345;

-- НЕ эффективно - Hash индекс не помогает
SELECT * FROM users WHERE id > 12345;        -- Нужен B-tree
SELECT * FROM users WHERE id BETWEEN 100 AND 200;  -- Нужен B-tree

-- Таблица со статьями с тегами
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    tags TEXT[]  -- Массив тегов
);

-- Индекс для поиска по тегам
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- Поиск работает быстро
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'database'];
-- Оператор @> означает "содержит элементы"

SELECT * FROM articles WHERE 'postgresql' = ANY(tags);
-- Тоже работает быстро с GIN индексом

-- Таблица с JSON данными
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    attributes JSONB  -- {"color": "red", "size": "L", "material": "cotton"}
);

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Поиск по JSONB быстро
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
-- Находит все продукты, где color = red, независимо от других полей

SELECT * FROM products WHERE attributes ? 'size';
-- Находит продукты, у которых есть ключ size

-- Полнотекстовый поиск
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

-- Создаем полнотекстовый индекс
CREATE INDEX idx_articles_content ON articles 
    USING GIN (to_tsvector('english', content));

-- Поиск очень быстро
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & performance');
-- Ищет статьи, содержащие ОБА слова: postgresql И performance

-- Таблица с локациями
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    point POINT  -- Координаты (x, y)
);

CREATE INDEX idx_locations_point ON locations USING GiST (point);

-- Поиск близких точек
SELECT * FROM locations 
WHERE point <-> POINT(0,0) < 1000;  -- В радиусе 1000 единиц от начала координат
-- <-> это оператор расстояния, работает очень быстро с GiST

-- Система бронирования комнат
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT,
    check_in DATE,
    check_out DATE
);

-- Создаем индекс на диапазоне
CREATE INDEX idx_bookings_period ON bookings 
    USING GiST (tsrange(check_in::timestamp, check_out::timestamp));

-- Поиск пересечений
SELECT * FROM bookings 
WHERE tsrange(check_in::timestamp, check_out::timestamp) @> '2024-01-15'::timestamp;
-- Находит все брони, которые включают эту дату

-- Гарантируем, что одна комната не может быть забронирована на пересекающиеся периоды
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING GiST (
    room_id WITH =,  -- Одна и та же комната
    tsrange(check_in::timestamp, check_out::timestamp) WITH &&  -- Пересекающиеся диапазоны
);

-- Теперь попытка создать пересекающуюся бронь вызовет ошибку
INSERT INTO bookings (room_id, check_in, check_out) 
VALUES (1, '2024-01-15', '2024-01-20');  -- OK

INSERT INTO bookings (room_id, check_in, check_out) 
VALUES (1, '2024-01-18', '2024-01-22');  -- ERROR - пересекается с предыдущей

-- Для точек на плоскости (quad-tree структура)
CREATE INDEX idx_locations_spgist ON locations USING SPGIST (point);

-- Для IP адресов (иерархическая структура)
CREATE INDEX idx_logs_ip ON access_logs USING SPGIST (client_ip inet_ops);

-- Для префиксных поисков по строкам
CREATE INDEX idx_domains_spgist ON domains USING SPGIST (domain_name text_ops);
-- Например, быстро может найти все домены, начинающиеся с "example"

-- Логи с временным рядом данных
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    level VARCHAR(20),
    message TEXT
);

-- Индекс на timestamp
CREATE INDEX idx_logs_timestamp_brin ON logs USING BRIN (timestamp);
-- Это займет примерно в 1000 раз меньше места, чем B-tree индекс!

-- Поиск по дате работает быстро
SELECT * FROM logs 
WHERE timestamp > '2024-01-15' AND timestamp < '2024-01-16';
-- PostgreSQL используя BRIN быстро находит какие блоки содержат эту дату

-- Настройка BRIN
CREATE INDEX idx_sales_date_brin ON sales USING BRIN (sale_date)
WITH (pages_per_range = 128);
-- pages_per_range - сколько страниц в одном диапазоне
-- Меньше = точнее, но больше индекс
-- Больше = менее точно, но меньше индекс

-- Пример: таблица заказов
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),  -- только 5-10 значений: 'pending', 'completed' и т.д.
    created_at TIMESTAMP
);

-- ПЛОХО: status первым (низкая селективность)
CREATE INDEX idx_orders_bad ON orders (status, user_id, created_at);
-- PostgreSQL найдет тысячи строк со status='pending', потом должен отфильтровать по user_id
-- Это неэффективно

-- ХОРОШО: user_id первым (высокая селективность)
CREATE INDEX idx_orders_good ON orders (user_id, created_at, status);
-- PostgreSQL сразу фильтрует до ~10 заказов конкретного пользователя
-- Потом быстро находит нужные даты и статусы

-- Посмотреть статистику колонок
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders' AND schemaname = 'public'
ORDER BY abs(n_distinct) DESC;

-- n_distinct - примерное число уникальных значений (-1 означает очень много)
-- correlation - насколько значения коррелируют с физическим порядком (от -1 к 1)

-- ЭТИ ЗАПРОСЫ используют индекс эффективно:

-- Поиск по первой колонке
SELECT * FROM orders WHERE user_id = 123;
-- Используется: (user_id) ← только первая часть индекса

-- Поиск по первым двум колонкам
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';
-- Используется: (user_id, created_at) ← первые две части

-- Поиск по всем трем
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01' AND status = 'pending';
-- Используется: (user_id, created_at, status) ← весь индекс

-- ЭТИ ЗАПРОСЫ ПЛОХО используют индекс:

-- Пропущена первая колонка
SELECT * FROM orders WHERE created_at > '2024-01-01';
-- НЕ использует индекс! PostgreSQL не может использовать индекс, если пропущена первая колонка

-- Пропущена вторая колонка
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Используется (user_id) часть, но status НЕ эффективен

-- Обычный индекс
CREATE INDEX idx_users_email ON users (email);

-- Если запрос выбирает другие колонки, PostgreSQL должен обращаться к таблице
SELECT name, phone, created_at FROM users WHERE email = 'john@example.com';
-- План: Index Scan → потом Heap Fetch

-- Покрывающий индекс
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name, phone, created_at);

-- Теперь запрос полностью отвечается индексом!
SELECT name, phone, created_at FROM users WHERE email = 'john@example.com';
-- План: Index Only Scan ← никакого обращения к таблице!

-- Обычный индекс (индексирует все 10 млн строк пользователей)
CREATE INDEX idx_users_email ON users (email);

-- Частичный индекс (индексирует только 1 млн активных пользователей)
CREATE INDEX idx_users_active_email ON users (email) WHERE status = 'active';

-- Если в запросе есть условие WHERE, PostgreSQL может использовать частичный индекс
SELECT * FROM users WHERE email = 'john@example.com' AND status = 'active';
-- Использует idx_users_active_email (меньше, быстрее)

-- Но если статус не указан, индекс не поможет
SELECT * FROM users WHERE email = 'john@example.com';
-- Не может использовать частичный индекс (нужен обычный)

-- Только недавние заказы (последний месяц)
CREATE INDEX idx_orders_recent ON orders (created_at, total)
WHERE created_at > NOW() - INTERVAL '30 days';

-- Исключая NULL значения
CREATE INDEX idx_users_phone ON users (phone) WHERE phone IS NOT NULL;
-- Сэкономим место, если у 50% пользователей нет phone

-- Сложные условия
CREATE INDEX idx_orders_important ON orders (priority, created_at)
WHERE status IN ('pending', 'processing') AND total > 1000;
-- Индексирует только важные, активные заказы с большой суммой

-- Без индекса - каждый раз медленно
SELECT * FROM users WHERE lower(email) = 'john@example.com';
-- PostgreSQL должен вычислить lower(email) для КАЖДОЙ строки

-- С функциональным индексом
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- Теперь поиск быстро (индекс предвычислил lower для всех строк)
SELECT * FROM users WHERE lower(email) = 'john@example.com';

-- ✓ IMMUTABLE функции - результат зависит только от аргументов
CREATE OR REPLACE FUNCTION normalize_phone(phone TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN regexp_replace(phone, '[^0-9]', '', 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE INDEX idx_users_phone_normalized ON users (normalize_phone(phone));

-- ✗ VOLATILE функции - результат может меняться
-- PostgreSQL НЕ позволит использовать такие функции в индексах
CREATE OR REPLACE FUNCTION get_discount(user_id INT)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (SELECT discount FROM user_discounts WHERE user_id = $1);
END;
$$ LANGUAGE plpgsql VOLATILE;

-- Попытка создать индекс вызовет ошибку
CREATE INDEX idx_orders_discount ON orders ((get_discount(user_id)));
-- ERROR: index expression cannot reference variables

-- Индекс по году и месяцу (для группировки)
CREATE INDEX idx_orders_year_month ON orders 
    (EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));

-- Индекс по длине строки
CREATE INDEX idx_long_descriptions ON products (length(description))
WHERE length(description) > 1000;

-- Индекс по вычисленному полю (profit margin)
CREATE INDEX idx_products_profit ON products ((price - cost) / price)
WHERE price > cost;

-- Гарантируем уникальность email только для активных пользователей
CREATE UNIQUE INDEX idx_users_email_active_unique ON users (email)
WHERE status = 'active';

-- Теперь:
INSERT INTO users (email, status) VALUES ('test@example.com', 'active');   -- OK
INSERT INTO users (email, status) VALUES ('test@example.com', 'inactive'); -- OK (другой статус)
INSERT INTO users (email, status) VALUES ('test@example.com', 'active');   -- ERROR (дубликат)

-- Это полезно когда:

-- - Удаленные пользователи могут иметь дублирующийся email
-- - Нужна уникальность только для активных записей
-- - Экономим место на индексе (индексируем только активных)

-- Синхронное создание (блокирует таблицу для записи)
CREATE INDEX idx_users_email ON users (email);
-- Все INSERT/UPDATE/DELETE ждут, пока индекс создастся
-- Плохо для production!

-- Конкурентное создание (не блокирует таблицу)
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- INSERT/UPDATE/DELETE работают нормально
-- Создание индекса медленнее, но таблица доступна
-- Используйте ЭТО для production

-- Проверка перед созданием
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);
-- Если индекс уже существует, ошибки не будет

-- Удаление
DROP INDEX CONCURRENTLY idx_users_email;
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;

-- Переиндексировать один индекс
REINDEX INDEX idx_users_email;
-- ← это блокирует таблицу во время работы

-- Конкурентная переиндексация (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Переиндексировать все индексы таблицы
REINDEX TABLE users;

-- Переиндексировать всю базу
REINDEX DATABASE mydb;

-- Посмотреть какие индексы как часто используются
SELECT
    schemaname, tablename, indexname,
    idx_scan,           -- Сколько раз индекс был использован в поиске
    idx_tup_read,       -- Сколько записей индекса было прочитано
    idx_tup_fetch       -- Сколько записей было получено из таблицы через индекс
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Интерпретация:

-- idx_scan = 0 → индекс не используется (можно удалить)
-- idx_tup_read >> idx_tup_fetch → индекс возвращает много "ложных срабатываний"

-- Найти индексы, которые не используются
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Это хорошие кандидаты на удаление (сначала проверьте!)

-- Проверить, насколько часто индекс попадает в кэш
SELECT schemaname, tablename, indexname,
       idx_blks_read,  -- Обращения к диску
       idx_blks_hit,   -- Попадания в кэш
       ROUND(100.0 * idx_blks_hit / NULLIF(idx_blks_hit + idx_blks_read, 0), 2) as cache_hit_ratio
FROM pg_statio_user_indexes
WHERE schemaname = 'public' AND (idx_blks_read + idx_blks_hit) > 0
ORDER BY cache_hit_ratio;

-- cache_hit_ratio близко к 100% → индекс хорошо используется
-- cache_hit_ratio < 90% → может быть недостаточно памяти или плохая локальность доступа

-- Найти индексы, которые дублируют друг друга
WITH index_columns AS (
    SELECT schemaname, tablename, indexname,
           string_agg(attname, ',' ORDER BY attnum) as columns
    FROM pg_stat_user_indexes si
    JOIN pg_index pi ON si.indexrelid = pi.indexrelid
    JOIN pg_attribute pa ON pa.attrelid = pi.indrelid AND pa.attnum = ANY(pi.indkey)
    WHERE schemaname = 'public'
    GROUP BY schemaname, tablename, indexname
)
SELECT i1.schemaname, i1.tablename,
       i1.indexname as index1, i2.indexname as index2,
       i1.columns
FROM index_columns i1
JOIN index_columns i2 ON i1.schemaname = i2.schemaname
    AND i1.tablename = i2.tablename
    AND i1.columns = i2.columns
    AND i1.indexname > i2.indexname;

-- Если два индекса на одни и те же колонки - это избыточно

-- Посмотреть как PostgreSQL выполняет запрос
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE email = 'john@example.com';

-- Типы сканирования:

-- Seq Scan - сканирует всю таблицу (плохо)
-- Index Scan - использует индекс + обращается к таблице (хорошо)
-- Bitmap Index Scan - сканирует индекс, потом комбинирует несколько индексов (хорошо для OR)
-- Index Only Scan - только индекс, БЕЗ обращения к таблице (отлично!)

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' ORDER BY created_at DESC LIMIT 10;

-- Output:

-- Limit  (cost=0.29..9.28 rows=10 width=32) (actual time=0.034..0.041 rows=10 loops=1)
--   -> Index Scan Backward using idx_orders_user_status_date on orders
--       (cost=0.29..9821.29 rows=1000 width=32) (actual time=0.033..0.039 rows=10 loops=1)
--       Index Cond: (user_id = 123) AND (status = 'completed')

-- cost=0.29..9.28 - плановые затраты (первая строка к последней строке)
-- actual time=0.034..0.041 - реальное время
-- rows=10 - сколько строк вернулось
-- index cond - условие использованное для индекса

-- Стандартные параметры (для HDD)
-- random_page_cost = 4.0 (стоимость случайного обращения к странице)
-- seq_page_cost = 1.0 (стоимость последовательного обращения к странице)

-- Для SSD диски должны быть меньше разницы
SET random_page_cost = 1.1;      -- SSD почти как последовательный доступ
SET seq_page_cost = 1.0;
SET cpu_index_tuple_cost = 0.005;

-- Проверить текущие значения
SHOW random_page_cost;

-- Для постоянного изменения добавьте в postgresql.conf
random_page_cost = 1.1

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Индексы для OLTP
CREATE INDEX idx_orders_user_id ON orders (user_id);           -- FK
CREATE INDEX idx_orders_status ON orders (status);             -- WHERE фильтр
CREATE INDEX idx_orders_created_at ON orders (created_at DESC); -- ORDER BY

CREATE TABLE sales (
    id BIGSERIAL PRIMARY KEY,
    region VARCHAR(50),
    product_category VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2)
);

-- Индексы для OLAP
CREATE INDEX idx_sales_analysis ON sales (region, product_category, sale_date);
CREATE INDEX idx_sales_date_brin ON sales USING BRIN (sale_date) 
    WITH (pages_per_range = 128);
-- BRIN очень хорош для временных рядов в аналитике

-- Очистить таблицу от мертвых версий
VACUUM users;

-- Очистить + обновить статистику
VACUUM ANALYZE users;

-- Полная очистка (пересоздает таблицу и индексы, освобождает место)
VACUUM FULL users;  -- ← блокирует таблицу!

-- Установка расширения
CREATE EXTENSION pgstattuple;

-- Проверить фрагментацию индекса
SELECT * FROM pgstatindex('idx_users_email');

-- Ключевые метрики:

-- avg_leaf_density > 90% - хорошо
-- leaf_fragmentation < 10% - хорошо
-- version = 4 - это B-tree индекс

-- Если фрагментация высокая, переиндексируйте
REINDEX INDEX CONCURRENTLY idx_users_email;

-- Настроить для конкретной таблицы
ALTER TABLE users SET (
    autovacuum_vacuum_scale_factor = 0.1,   -- 10% от строк
    autovacuum_analyze_scale_factor = 0.05, -- 5% от строк
    autovacuum_vacuum_cost_delay = 10       -- мс задержки (не перегружать диск)
);

-- Посмотреть когда последний раз была очистка
SELECT schemaname, tablename,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY last_autovacuum DESC NULLS LAST;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL REFERENCES categories(id),
    price DECIMAL(10,2) NOT NULL,
    brand VARCHAR(100),
    attributes JSONB,  -- {"size": "L", "color": "red", "material": "cotton"}
    created_at TIMESTAMP DEFAULT NOW()
);

-- Индексы оптимизированные для e-commerce
CREATE INDEX idx_products_category ON products (category_id);
-- FK индекс для JOIN'ов и фильтрации по категориям

CREATE INDEX idx_products_brand ON products (brand) 
WHERE brand IS NOT NULL;
-- Частичный индекс (экономим место, т.к. может быть много NULL)

CREATE INDEX idx_products_category_price ON products (category_id, price DESC);
-- Составной: сначала фильтруем по категории, потом сортируем по цене

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
-- GIN для поиска по JSON (быстро находит все товары определенного цвета/размера)

CREATE INDEX idx_products_search ON products 
    USING GIN (to_tsvector('russian', name));
-- Полнотекстовый поиск по названию товара

CREATE INDEX idx_products_catalog ON products (category_id, price DESC)
INCLUDE (name, brand) WHERE price > 0;
-- Покрывающий индекс для каталога: (катего+цена) + (название+бренд)
-- Может полностью ответить на запрос "Товары в категории, отсортированные по цене"

CREATE TABLE access_logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    ip_address INET NOT NULL,
    user_id INT,
    url TEXT NOT NULL,
    response_code INT NOT NULL,
    response_time_ms INT NOT NULL
);

-- Loggerные таблицы обычно очень большие, поэтому стратегия другая

CREATE INDEX idx_logs_timestamp_brin ON access_logs 
    USING BRIN (timestamp);
-- BRIN очень маленький для временных рядов (логи обычно append-only)

CREATE INDEX idx_logs_user_recent ON access_logs (user_id, timestamp DESC)
WHERE timestamp > NOW() - INTERVAL '30 days';
-- Частичный индекс только для недавних логов (обычно это интересует)

CREATE INDEX idx_logs_errors ON access_logs (timestamp DESC, response_code)
WHERE response_code >= 400;
-- Поиск ошибок

CREATE INDEX idx_logs_ip ON access_logs USING SPGIST (ip_address);
-- SP-GiST для IP адресов (поиск подсетей, диапазонов)

CREATE INDEX idx_logs_slow ON access_logs (timestamp, response_time_ms)
WHERE response_time_ms > 1000;
-- Поиск медленных запросов

□ Есть ли запросы, которые этот индекс ускорит?
□ Проверил ли я EXPLAIN план текущих запросов?
□ Нет ли уже существующего индекса, который можно использовать?
□ Оправдывает ли выигрыш в SELECT стоимость INSERT/UPDATE?
□ Есть ли WHERE условие, которое можно использовать для частичного индекса?
□ Правильный ли порядок колонок (наиболее селективные первыми)?
□ Смогу ли я использовать INCLUDE для меньшего размера?
□ Есть ли мониторинг, который подскажет, используется ли индекс?

Если ответ "да" на большинство - создавайте индекс.
Если ответ "не уверен" - используйте EXPLAIN ANALYZE и тестируйте.

-- 1. Анализируем текущий запрос
EXPLAIN ANALYZE
SELECT user_id, COUNT(*) as order_count, SUM(total) as total_sum
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 10;
-- → Seq Scan - плохо, нужен индекс

-- 2. Создаем индекс CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_orders_recent_user 
ON orders (user_id, created_at DESC)
WHERE created_at > '2024-01-01';

-- 3. Проверяем результат
EXPLAIN ANALYZE
SELECT user_id, COUNT(*) as order_count, SUM(total) as total_sum
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 10;
-- → Index Scan - хорошо!

-- 4. Мониторим использование
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE indexname = 'idx_orders_recent_user';

-- 5. Через неделю проверяем, что индекс работает
-- Если idx_scan = 0, удаляем лишний индекс
DROP INDEX CONCURRENTLY idx_orders_recent_user;

-- Оптимальная конфигурация для 32GB сервера с OLTP нагрузкой
shared_buffers = 8GB                  -- 25% от RAM: кэш PostgreSQL
effective_cache_size = 24GB           -- 75% от RAM: для планировщика запросов
work_mem = 256MB                      -- Per operation memory (sort, hash join)
maintenance_work_mem = 2GB            -- Для VACUUM, CREATE INDEX, ALTER TABLE
max_connections = 200
max_parallel_workers = 16
random_page_cost = 1.1                -- SSD: меньше чем HDD (4.0)

-- Стратегии распределения
-- Read Replicas: масштабирование чтения (80% операций)
-- Sharding: разделение данных по серверам (каждый сервер отвечает за подмножество)
-- Connection Pooling: переиспользование соединений

Master (5432)
  ↓ WAL stream
Standby (in recovery mode)
  ↓ WAL stream
Standby2 (cascading)

-- postgresql.conf
wal_level = replica                   -- Обязателен для репликации
max_wal_senders = 10                  -- Макс одновременных реплик
wal_keep_size = 1GB                   -- Сохранять WAL на 1GB больше
max_replication_slots = 10            -- Слоты предотвращают удаление нужного WAL
hot_standby = on                      -- Разрешить запросы на Standby
archive_mode = on                     -- Архивирование WAL для disaster recovery
archive_command = 'cp %p /archive/%f' -- Команда архивирования

# Создание базовой копии - снимок всех файлов БД с Master
# -R: автоматически создать recovery.conf (PostgreSQL 12+: standby.signal)
pg_basebackup -h master_host -D /var/lib/postgresql/data \
    -U replica -W -v -P -R

# WAL streaming начинается автоматически

# Разрешить репликацию от Standby
host    replication     replica     192.168.1.0/24     md5

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'secure_password';

-- Проверить статус реплик
SELECT client_addr, 
       state,                         -- 'streaming', 'catchup', 'backup'
       sent_lsn,                      -- До какого LSN отправлены WAL
       write_lsn,                     -- Принято, но не синхронизировано
       flush_lsn,                     -- Записано на диск
       replay_lsn,                    -- Применено (Standby прошёл)
       write_lag,                     -- Задержка write
       flush_lag,                     -- Задержка flush
       replay_lag                     -- Полная задержка репликации
FROM pg_stat_replication;

-- Проверить статус восстановления
SELECT pg_is_in_recovery();                    -- true = в режиме Standby
SELECT pg_last_wal_receive_lsn();              -- Последний полученный WAL
SELECT pg_last_wal_replay_lsn();               -- Последний применённый WAL
SELECT pg_last_xact_replay_timestamp();        -- Когда был выполнен последний XID

SELECT client_addr,
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
       pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
       pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- postgresql.conf на Master
synchronous_commit = on               -- COMMIT ждёт: sent+write+flush+replay на реплике

-- off: полностью асинхронно (по умолчанию)
synchronous_commit = off

-- local: данные записаны локально на Master в WAL
synchronous_commit = local

-- remote_write: Standby получил и записал в OS buffer (на диск ещё может не быть)
synchronous_commit = remote_write

-- on (он же remote_flush): Standby синхронизировал на диск
synchronous_commit = on

-- remote_apply: Standby полностью применил изменение (медленнее, самое безопасное)
synchronous_commit = remote_apply

-- Синхронизировать с первым ответившим (быстро)
synchronous_standby_names = 'FIRST 1 (standby1, standby2, standby3)'

-- Ждать подтверждения от любых 2 из 3 реплик (отказоустойчиво)
synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'

-- Все standby (консервативно, медленно)
synchronous_standby_names = 'standby1,standby2,standby3'

-- Publisher (отправляет изменения)
-- postgresql.conf
wal_level = logical                   -- Обязателен для logical replication
max_replication_slots = 10
max_wal_senders = 10

-- Создать publication (набор таблиц для репликации)
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Или только конкретные таблицы
CREATE PUBLICATION users_publication FOR TABLE users, orders;

-- С фильтром (только активные пользователи)
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

-- Subscriber (получает изменения и применяет)
-- Может быть на другой версии PostgreSQL!
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_host dbname=mydb user=replica password=password'
    PUBLICATION my_publication;

-- Опции: copy_data=true (копировать существующие данные)
-- synchronous_commit=off (асинхронно)

-- На Publisher
SELECT slot_name, slot_type, active, restart_lsn 
FROM pg_replication_slots;

-- На Subscriber
SELECT subname, received_lsn, latest_end_lsn, latest_end_time,
       pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) as lag
FROM pg_stat_subscription;

Master
  ↓ WAL
Standby1 (промежуточный, может быть Publisher)
  ↓ WAL
Standby2, Standby3 (питаются от Standby1)

-- postgresql.conf на Standby1
hot_standby = on                      -- Разрешить запросы
max_wal_senders = 5                   -- Может отправлять WAL дальше
wal_level = replica

-- Standby2 подключится к Standby1
primary_conninfo = 'host=standby1_host port=5432 user=replica password=password'

CREATE TABLE orders (
    id BIGSERIAL,
    user_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    status VARCHAR(20) NOT NULL
) PARTITION BY RANGE (created_at);

-- Каждый месяц - отдельная партиция
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE EXTENSION pg_partman;

-- Создать родительскую таблицу с автоматическим добавлением партиций
SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => 'monthly',              -- Новая партиция каждый месяц
    p_premake => 2                        -- Создать 2 партиции заранее
);

-- Запланировать обслуживание
SELECT cron.schedule('maintain_orders_partitions', 
    '0 1 * * *',                          -- Каждую ночь в 01:00
    'SELECT partman.maintain_partition_trigger_value(''public.orders'')');

CREATE TABLE users (
    id BIGSERIAL,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL
) PARTITION BY HASH (id);

-- 4 партиции (для 4 ядер или 4 серверов)
CREATE TABLE users_part_0 PARTITION OF users 
    FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE users_part_1 PARTITION OF users 
    FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE users_part_2 PARTITION OF users 
    FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE users_part_3 PARTITION OF users 
    FOR VALUES WITH (modulus 4, remainder 3);

CREATE TABLE sales (
    id BIGSERIAL,
    region VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    sale_date DATE NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE sales_us PARTITION OF sales 
    FOR VALUES IN ('US', 'USA', 'United States');
CREATE TABLE sales_eu PARTITION OF sales 
    FOR VALUES IN ('UK', 'DE', 'FR', 'IT', 'ES');
CREATE TABLE sales_asia PARTITION OF sales 
    FOR VALUES IN ('JP', 'CN', 'IN', 'KR');
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

CREATE EXTENSION citus;

-- Добавить worker nodes
SELECT citus_add_node('worker1_host', 5432);
SELECT citus_add_node('worker2_host', 5432);

-- Создать распределённую таблицу
CREATE TABLE events (
    id BIGSERIAL,
    user_id INT NOT NULL,
    event_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Сделать её распределённой (шардированной) по user_id
SELECT create_distributed_table('events', 'user_id');

-- Reference таблица - реплицируется на все worker nodes
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255), name VARCHAR(255));
SELECT create_reference_table('users');

Coordinator (принимает запросы)
  ↓
Worker1 (events shard: user_id % 3 = 0)
Worker2 (events shard: user_id % 3 = 1)
Worker3 (events shard: user_id % 3 = 2)

SELECT * FROM events WHERE user_id = 123;
-- Coordinator вычислит: 123 % 3 = 0 → routing на Worker1
-- Worker1 вернёт результат

CREATE EXTENSION postgres_fdw;

-- Создать connection к worker узлам
CREATE SERVER shard1_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'shard1.example.com', port '5432', dbname 'shard1_db');

CREATE USER MAPPING FOR postgres
    SERVER shard1_server
    OPTIONS (user 'postgres', password 'secret');

-- Создать foreign table, указывающую на удалённую таблицу
CREATE FOREIGN TABLE users_shard1 (
    id BIGINT,
    email VARCHAR(255),
    name VARCHAR(255)
) SERVER shard1_server 
  OPTIONS (schema_name 'public', table_name 'users');

-- Создать локальную распределённую таблицу
CREATE TABLE users (
    id BIGINT NOT NULL,
    email VARCHAR(255),
    name VARCHAR(255)
) PARTITION BY RANGE (id);

-- Подключить foreign tables как партиции
-- Shard 1: ids 1-1M
ALTER TABLE users ATTACH PARTITION users_shard1
    FOR VALUES FROM (1) TO (1000001);

-- Shard 2: ids 1M-2M
ALTER TABLE users_shard2 ATTACH PARTITION ...
    FOR VALUES FROM (1000001) TO (2000001);

-- Теперь SELECT * FROM users автоматически кверирует нужные шарды!

┌─ Клиент 1
├─ Клиент 2  ──→ PgBouncer (pool) ──→ 10 реальных соединений с PostgreSQL
├─ Клиент 3
└─ Клиент 10K

# /etc/pgbouncer/pgbouncer.ini

[databases]
mydb = host=localhost port=5432 dbname=mydb max_db_connections=50

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# КРИТИЧНЫЙ параметр: режим переиспользования соединений
pool_mode = transaction              # ← Обычно используется

# Пулинг параметры
max_client_conn = 1000               # Макс клиентов к PgBouncer
default_pool_size = 25               # Соединений от PgBouncer к PostgreSQL
reserve_pool_size = 5                # Резервные соединения при перегрузке
reserve_pool_timeout = 3             # Таймаут для резервных

# Очистка соединений
server_reset_query = DISCARD ALL     # Очистить сессию между использованиями
server_check_query = SELECT 1        # Проверка живого соединения
server_lifetime = 3600               # Переподключение каждый час
server_idle_timeout = 600            # Закрыть неиспользуемое соединение

1. session - соединение привязано к клиенту на всю сессию
   + Полная сессионная изоляция (SET переменные сохраняются)
   - Скейлится только до ~100 одновременных клиентов
   Когда: Приложения с долгоживущими соединениями

2. transaction - соединение возвращается в пул после COMMIT/ROLLBACK
   + Скейлится до 1000+ клиентов на одном PgBouncer
   + Эффективное использование ресурсов
   - Нельзя использовать мультистейтментные транзакции
   - SET session_var не сохраняется между операциями
   Когда: Микросервисы, API серверы

3. statement - соединение возвращается после каждого statement (агрессивно)
   + Максимальное переиспользование
   - Большинство приложений не совместимы
   Когда: Очень редко, специальные сценарии

# Подключиться к админ интерфейсу
psql -h localhost -p 6432 -U pgbouncer pgbouncer

# Команды
SHOW POOLS;      # Статус каждого пула
SHOW CLIENTS;    # Активные клиенты и их статусы
SHOW SERVERS;    # Серверные соединения
SHOW STATS;      # Статистика (запросы, bytesio)
SHOW CONFIG;     # Текущая конфигурация

database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
---------+------+-----------+------------+-----------+---------+---------+-----------+---------+-------
mydb     | app  | 5         | 0          | 10        | 15      | 0       | 0         | 0       | 0

# odyssey.conf
storage "postgres_server" {
    type "remote"
    host "localhost"
    port 5432
}

database "mydb" {
    user "postgres" {
        authentication "md5"
        password "password"

        storage "postgres_server"
        pool "transaction"
        pool_size 50
        pool_timeout 4000      # ms
        client_max 1000
    }
}

listen {
    host "*"
    port 6432
}

# /etc/haproxy/haproxy.cfg
global
    maxconn 4096
    tune.ssl.default-dh-param 2048

defaults
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms
    option tcplog

# Master для записи (все WRITE операции + transactions)
frontend postgres_write
    bind *:5000
    default_backend postgres_master

backend postgres_master
    balance roundrobin
    option tcp-check
    tcp-check connect port 5432
    server master1 master1.example.com:5432 check inter 5000 fall 3
    server master2 master2.example.com:5432 check inter 5000 fall 3 backup

# Replicas для чтения (READ-only запросы)
frontend postgres_read
    bind *:5001
    default_backend postgres_replicas

backend postgres_replicas
    balance roundrobin
    option tcp-check
    tcp-check connect port 5432
    server replica1 replica1.example.com:5432 check inter 5000 fall 3
    server replica2 replica2.example.com:5432 check inter 5000 fall 3
    server replica3 replica3.example.com:5432 check inter 5000 fall 3

#!/usr/bin/env python3
import psycopg2
from http.server import HTTPServer, BaseHTTPRequestHandler
import json

class HealthCheckHandler(BaseHTTPRequestHandler):
    def do_GET(self):
        response_code = 503  # Service Unavailable
        response_data = {"status": "unhealthy"}
        
        if self.path == '/master':
            is_healthy = self.check_master()
            response_code = 200 if is_healthy else 503
            response_data = {"status": "healthy" if is_healthy else "unhealthy"}
            
        elif self.path == '/replica':
            is_healthy, lag = self.check_replica()
            response_code = 200 if is_healthy else 503
            response_data = {
                "status": "healthy" if is_healthy else "unhealthy",
                "replication_lag_seconds": lag
            }
        
        self.send_response(response_code)
        self.send_header('Content-Type', 'application/json')
        self.end_headers()
        self.wfile.write(json.dumps(response_data).encode())

    def check_master(self):
        """Проверить, что узел - Master и пригоден для записи"""
        try:
            conn = psycopg2.connect(
                host='localhost', port=5432, 
                database='postgres', 
                user='healthcheck', password='password',
                connect_timeout=2
            )
            cur = conn.cursor()
            # NOT pg_is_in_recovery() = Master (не в режиме восстановления)
            cur.execute("SELECT NOT pg_is_in_recovery()")
            is_master = cur.fetchone()[0]
            conn.close()
            return is_master
        except:
            return False

    def check_replica(self):
        """Проверить Standby и его lag от Master"""
        try:
            conn = psycopg2.connect(
                host='localhost', port=5432,
                database='postgres',
                user='healthcheck', password='password',
                connect_timeout=2
            )
            cur = conn.cursor()
            
            # Проверить, в режиме восстановления ли
            cur.execute("SELECT pg_is_in_recovery()")
            is_standby = cur.fetchone()[0]
            
            # Посчитать lag в секундах
            cur.execute("""
                SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) 
                as replay_lag_sec
            """)
            lag = cur.fetchone()[0] or 0
            
            conn.close()
            
            # Здоров, если: Standby + lag < 30 сек
            is_healthy = is_standby and lag < 30
            return is_healthy, lag
        except Exception as e:
            return False, -1

if __name__ == '__main__':
    server = HTTPServer(('0.0.0.0', 8008), HealthCheckHandler)
    print("Health check server started on port 8008")
    server.serve_forever()

# Улучшенная конфигурация HAProxy с HTTP checks
backend postgres_replicas
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    server replica1 replica1.example.com:5432 check port 8008 inter 5000 fall 3
    server replica2 replica2.example.com:5432 check port 8008 inter 5000 fall 3

# /etc/patroni/patroni.yml
scope: postgres-cluster                 # Имя кластера в etcd
name: node1                             # Уникальное имя узла

restapi:
  listen: 0.0.0.0:8008
  connect_address: node1.example.com:8008

etcd:
  hosts: 

    - etcd1.example.com:2379
    - etcd2.example.com:2379
    - etcd3.example.com:2379

bootstrap:
  dcs:
    ttl: 30                             # TTL лидера (Master)
    loop_wait: 10                       # Проверка каждые 10 сек
    maximum_lag_on_failover: 1048576    # Max 1MB lag на реплике для failover
    
    postgresql:
      use_pg_rewind: true               # Быстрое переключение при failover
      use_slots: true                   # Использовать replication slots
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10

postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/postgresql/data
  
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password

# Проверить статус кластера
curl http://node1:8008/cluster
# Ответ:
# {
#   "members": [
#     {"name": "node1", "role": "leader", ...},
#     {"name": "node2", "role": "replica", "lag": 123456, ...},
#     {"name": "node3", "role": "replica", "lag": 654321, ...}
#   ]
# }

# На Standby: повысить до Master
pg_ctl promote -D /var/lib/postgresql/data

# Или создать trigger файл (если standby.signal используется)
touch /tmp/promote_standby

# После этого Standby перестанет быть в recovery mode

-- На новом Master
SELECT pg_is_in_recovery();           -- Должно быть FALSE
SELECT pg_last_wal_replay_lsn();      -- Последний LSN
SELECT * FROM pg_stat_replication;   -- Проверить новых реплик

# settings.py
DATABASES = {
    'master': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'pg-master.example.com',
        'NAME': 'production',
        'USER': 'app',
        'PASSWORD': 'password'
    },
    'replica': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'pg-replica.example.com',  # или через HAProxy port 5001
        'NAME': 'production',
        'USER': 'app',
        'PASSWORD': 'password'
    }
}

# router.py - автоматический выбор БД
class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        """Все чтение идёт на реплику"""
        return 'replica'

    def db_for_write(self, model, **hints):
        """Вся запись идёт на Master"""
        return 'master'

    def allow_relation(self, obj1, obj2, **hints):
        """Разрешить relations"""
        return True

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """Миграции только на Master"""
        return db == 'master'

# Использование
from django.apps import apps
User = apps.get_model('myapp', 'User')

# Это автоматически использует replica DB
users = User.objects.all()

# Это использует master DB
new_user = User.objects.create(name='John')

# Принудить Master (например, после write нужна актуальная информация)
user = User.objects.using('master').get(id=1)

# После create, прочитать из master
new_user = User.objects.create(name='John')
fresh_user = User.objects.using('master').get(id=new_user.id)

# Архитектура для глобального масштабирования
Регион US-East:

  - PostgreSQL Master (5 ядер, 32GB)
  - 2 Read Replicas (синхронные)
  - PgBouncer на каждой машине
  - HAProxy для routing read/write

Регион EU-West:

  - Read Replica от US-East Master (асинхронная, ~500ms lag)
  - PgBouncer
  - HAProxy для балансировки между US-East Master (write) и локальной Replica

Регион Asia-Pacific:

  - Read Replica от US-East Master (асинхронная, ~1s lag)
  - Локальный Redis cache
  - PgBouncer
  - HAProxy

Стратегия маршрутизации:

- Write (PUT/POST): всегда US-East Master (может быть медленно из других регионов)
- Read (GET): локальная Replica
- Для критичного чтения (нужны fresh данные): Master

# Выбор БД по типу операции и локации
def get_db_for_operation(operation_type, user_region):
    if operation_type == 'write':
        return 'master_us_east'  # Всегда в master регионе
    else:  # read
        region_to_db = {
            'us-east': 'replica_us_east',
            'eu-west': 'replica_eu_west',
            'asia': 'replica_asia'
        }
        return region_to_db.get(user_region, 'replica_us_east')

CREATE OR REPLACE FUNCTION calculate_discount(
    order_amount DECIMAL,
    customer_type VARCHAR(20)
) RETURNS DECIMAL AS $$
BEGIN
    RETURN CASE
        WHEN customer_type = 'VIP' AND order_amount > 1000 THEN order_amount * 0.15
        WHEN customer_type = 'VIP' THEN order_amount * 0.10
        WHEN order_amount > 500 THEN order_amount * 0.05
        ELSE 0
    END;
END;
$$ LANGUAGE plpgsql;

SELECT order_id, total_amount,
       calculate_discount(total_amount, customer_type) AS discount,
       total_amount - calculate_discount(total_amount, customer_type) AS final_price
FROM orders
WHERE calculate_discount(total_amount, customer_type) > 100;

CREATE OR REPLACE FUNCTION get_user_orders(user_id_param BIGINT)
RETURNS TABLE(
    order_id BIGINT,
    order_date TIMESTAMP,
    total_amount DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.created_at, o.total_amount
    FROM orders o
    WHERE o.user_id = user_id_param
    ORDER BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_user_orders(123);

CREATE OR REPLACE FUNCTION get_top_products(limit_count INT)
RETURNS SETOF products AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM products
    ORDER BY sales_count DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_top_products(10);

CREATE OR REPLACE FUNCTION calculate_order_totals(
    order_id_param BIGINT,
    OUT subtotal DECIMAL,
    OUT tax DECIMAL,
    OUT total DECIMAL
) AS $$
BEGIN
    SELECT SUM(price * quantity) INTO subtotal
    FROM order_items WHERE order_id = order_id_param;
    
    tax := subtotal * 0.1;
    total := subtotal + tax;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM calculate_order_totals(123);
SELECT subtotal, total FROM calculate_order_totals(123);

CREATE OR REPLACE PROCEDURE process_order(
    order_id_param BIGINT,
    OUT success BOOLEAN,
    OUT message TEXT
) AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM products p
        JOIN order_items oi ON p.id = oi.product_id
        WHERE oi.order_id = order_id_param
        AND p.stock >= oi.quantity
    ) THEN
        success := FALSE;
        message := 'Insufficient stock';
        RETURN;
    END IF;
    
    UPDATE orders SET status = 'processing' WHERE id = order_id_param;
    
    success := TRUE;
    message := 'Order processed successfully';
    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL process_order(123, NULL, NULL);

CREATE OR REPLACE PROCEDURE transfer_money(
    from_account_id BIGINT,
    to_account_id BIGINT,
    amount DECIMAL
) AS $$
DECLARE
    current_balance DECIMAL;
BEGIN
    SELECT balance INTO current_balance
    FROM accounts WHERE id = from_account_id FOR UPDATE;
    
    IF current_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    UPDATE accounts SET balance = balance - amount
    WHERE id = from_account_id;
    
    UPDATE accounts SET balance = balance + amount
    WHERE id = to_account_id;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION complex_calculation()
RETURNS INT AS $$
DECLARE
    counter INT := 0;
    max_value INT;
    result INT;
BEGIN
    SELECT MAX(value) INTO max_value FROM table_name;
    
    WHILE counter < 10 LOOP
        counter := counter + 1;
        result := counter * max_value;
    END LOOP;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_age_category(age INT)
RETURNS VARCHAR AS $$
BEGIN
    IF age < 18 THEN
        RETURN 'Minor';
    ELSIF age BETWEEN 18 AND 65 THEN
        RETURN 'Adult';
    ELSE
        RETURN 'Senior';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Числовой FOR цикл (от start до end включительно)
CREATE OR REPLACE FUNCTION sum_range(start_num INT, end_num INT)
RETURNS INT AS $$
DECLARE
    total INT := 0;
    i INT;
BEGIN
    FOR i IN start_num..end_num LOOP
        total := total + i;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- Цикл по результатам запроса
CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID AS $$
DECLARE
    order_record RECORD;
BEGIN
    FOR order_record IN SELECT * FROM orders WHERE status = 'pending' LOOP
        UPDATE orders SET status = 'processing' WHERE id = order_record.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- WHILE цикл
CREATE OR REPLACE FUNCTION calculate_fibonacci(n INT)
RETURNS INT AS $$
DECLARE
    a INT := 0;
    b INT := 1;
    temp INT;
    counter INT := 0;
BEGIN
    WHILE counter < n LOOP
        temp := a + b;
        a := b;
        b := temp;
        counter := counter + 1;
    END LOOP;
    RETURN a;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safe_division(a DECIMAL, b DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_with_retry(record_id INT, max_retries INT DEFAULT 3)
RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
BEGIN
    WHILE retry_count < max_retries LOOP
        BEGIN
            UPDATE my_table SET value = value + 1 WHERE id = record_id;
            RETURN TRUE;
        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                retry_count := retry_count + 1;
                IF retry_count >= max_retries THEN
                    RAISE;
                END IF;
                PERFORM pg_sleep(0.1 * retry_count);
        END;
    END LOOP;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_salary_before_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;
    
    IF NEW.salary > 1000000 THEN
        RAISE NOTICE 'High salary: %', NEW.salary;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_check_trigger
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION check_salary_before_insert();

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO users_audit (user_id, operation, changed_at)
        VALUES (OLD.id, TG_OP, NOW());
        RETURN OLD;
    ELSE
        INSERT INTO users_audit (user_id, operation, changed_at)
        VALUES (NEW.id, TG_OP, NOW());
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

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

CREATE OR REPLACE FUNCTION update_user_view()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users SET name = NEW.name WHERE id = NEW.id;
    UPDATE user_profiles SET bio = NEW.bio WHERE user_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_user_view_trigger
    INSTEAD OF UPDATE ON users_view
    FOR EACH ROW
    EXECUTE FUNCTION update_user_view();

CREATE OR REPLACE FUNCTION dynamic_query(
    table_name TEXT,
    column_name TEXT,
    filter_value TEXT
) RETURNS TABLE(result JSON) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT row_to_json(t) FROM %I t WHERE %I = $1',
        table_name, column_name
    ) USING filter_value;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safe_dynamic_query(
    table_name TEXT,
    id_value INT
) RETURNS SETOF RECORD AS $$
BEGIN
    IF table_name NOT IN ('users', 'orders', 'products') THEN
        RAISE EXCEPTION 'Invalid table name';
    END IF;
    
    RETURN QUERY EXECUTE
        format('SELECT * FROM %I WHERE id = $1', table_name)
    USING id_value;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION median_accumulate(state NUMERIC[], value NUMERIC)
RETURNS NUMERIC[] AS $$
BEGIN
    RETURN array_append(state, value);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION median_final(state NUMERIC[])
RETURNS NUMERIC AS $$
DECLARE
    sorted_state NUMERIC[];
    count INT;
BEGIN
    sorted_state := ARRAY(SELECT unnest(state) ORDER BY 1);
    count := array_length(sorted_state, 1);
    
    IF count % 2 = 0 THEN
        RETURN (sorted_state[count/2] + sorted_state[count/2 + 1]) / 2;
    ELSE
        RETURN sorted_state[(count + 1) / 2];
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE median(NUMERIC) (
    SFUNC = median_accumulate,
    STYPE = NUMERIC[],
    FINALFUNC = median_final,
    INITCOND = '{}'
);

SELECT department, median(salary) FROM employees GROUP BY department;

CREATE OR REPLACE FUNCTION get_sensitive_data()
RETURNS TABLE(id INT, data TEXT)
SECURITY DEFINER AS $$
BEGIN
    RETURN QUERY SELECT id, sensitive_column FROM secret_table;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION get_sensitive_data() TO app_user;
REVOKE EXECUTE ON FUNCTION get_sensitive_data() FROM PUBLIC;

CREATE OR REPLACE FUNCTION public_data()
RETURNS TABLE(id INT, name TEXT)
SECURITY INVOKER AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM public_table;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION calculate_tax(amount DECIMAL)
RETURNS DECIMAL
IMMUTABLE AS $$
BEGIN
    RETURN amount * 0.1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_current_exchange_rate()
RETURNS DECIMAL
STABLE AS $$
BEGIN
    RETURN (SELECT rate FROM exchange_rates WHERE currency = 'USD' AND date = CURRENT_DATE);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION log_access()
RETURNS VOID
VOLATILE AS $$
BEGIN
    INSERT INTO access_log (timestamp) VALUES (NOW());
END;
$$ LANGUAGE plpgsql;

-- Просмотр всех функций и процедур схемы
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

-- Просмотр кода функции (psql команда)
\sf calculate_discount

-- Просмотр параметров
SELECT parameter_name, parameter_mode, data_type
FROM information_schema.parameters
WHERE specific_name = 'calculate_discount'
ORDER BY ordinal_position;

-- Удаление (IF EXISTS предотвращает ошибку)
DROP FUNCTION IF EXISTS calculate_discount(DECIMAL, VARCHAR);
DROP PROCEDURE IF EXISTS process_order(BIGINT);

-- Если функция перегружена (несколько версий с разными параметрами)
DROP FUNCTION IF EXISTS my_func(INT, TEXT);

-- Переименование
ALTER FUNCTION calculate_discount RENAME TO calc_discount;

-- Изменение владельца
ALTER FUNCTION calculate_discount OWNER TO new_user;

-- Изменение схемы
ALTER FUNCTION calculate_discount SET SCHEMA new_schema;

CREATE OR REPLACE FUNCTION debug_function(value INT)
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Processing value: %', value;
    RAISE WARNING 'This is a warning';
    RAISE INFO 'Informational message';
    
    IF value < 0 THEN
        RAISE EXCEPTION 'Value must be positive';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Вывод можно видеть в логах PostgreSQL или в результатах CALL
CALL debug_function(-5);

RAISE NOTICE 'Current value: %, next value: %', var1, var2;

SET log_min_messages = DEBUG;
SET log_statement = 'all';
-- Перенаправляет RAISE в log файл PostgreSQL (/var/log/postgresql/)

SELECT * FROM calculate_order_totals(123) \g
-- \g выполняет последний запрос и показывает результат

-- ❌ Неправильно
BEGIN
    SELECT MAX(salary) FROM employees;  -- SELECT просто выполнится и забудется
END;

-- ✅ Правильно
BEGIN
    SELECT MAX(salary) INTO max_salary FROM employees;
END;

-- ❌ Неправильно
IF condition THEN
    SET some_var = value;
    -- нет RETURN, выполнение продолжится дальше
END IF;

-- ✅ Правильно
IF condition THEN
    RETURN value;
END IF;

-- ❌ Бесконечная рекурсия
CREATE FUNCTION bad_recursion(n INT) RETURNS INT AS $$
BEGIN
    RETURN bad_recursion(n + 1);
END;

-- ✅ Условие выхода
CREATE FUNCTION good_recursion(n INT) RETURNS INT AS $$
BEGIN
    IF n <= 1 THEN RETURN 1; END IF;
    RETURN n * good_recursion(n - 1);
END;

-- ❌ Race condition
BEGIN
    SELECT balance INTO bal FROM accounts WHERE id = 1;
    UPDATE accounts SET balance = bal - 100 WHERE id = 1;
END;

-- ✅ Блокировка
BEGIN
    SELECT balance INTO bal FROM accounts WHERE id = 1 FOR UPDATE;
    UPDATE accounts SET balance = bal - 100 WHERE id = 1;
END;

-- ❌ Неправильно, OUT параметр передан как обычное значение
CALL my_proc(123, 'result');

-- ✅ Правильно, передаём NULL для OUT параметра
CALL my_proc(123, NULL);

-- Денежный перевод между счетами
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Обе UPDATE выполнены или ни одна

-- Явный откат
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;  -- Ничего не произойдет, данные вернулись в исходное состояние

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
COMMIT;  -- Первый UPDATE будет применен, второй отменен

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob');  -- ERROR: duplicate key value violates unique constraint

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),  -- account может ссылаться только на существующего user
    balance DECIMAL
);

INSERT INTO accounts (user_id, balance) VALUES (999, 1000);  -- ERROR: insert or update on table "accounts" violates foreign key constraint

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL CHECK (balance >= 0)  -- баланс не может быть отрицательным
);

UPDATE accounts SET balance = -100 WHERE id = 1;  -- ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO users (email) VALUES ('test@example.com');
INSERT INTO users (email) VALUES ('test@example.com');  -- ERROR: duplicate key value violates unique constraint

-- Транзакция 1 (READ UNCOMMITTED)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Вернет 1000

-- Транзакция 2 (параллельная)
-- BEGIN;
-- UPDATE accounts SET balance = 500 WHERE id = 1;
-- -- Еще не COMMIT

-- Транзакция 1 снова читает
SELECT balance FROM accounts WHERE id = 1;  -- Может вернуть 500 (dirty read - видит незавершенные данные)
-- Но если Транзакция 2 откатится, то информация была неверной

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Вернет 1000

-- Другая транзакция обновит и закоммитит
-- UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;  -- Вернет 500 (non-repeatable read)
COMMIT;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Snapshot на момент BEGIN, вернет 1000

-- Другая транзакция обновит и закоммитит
-- UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;  -- ВСЕГДА вернет 1000 (консистентный snapshot)
COMMIT;

BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 5 заказов
-- Другая транзакция добавит новый ORDER
-- INSERT INTO orders (status) VALUES ('pending'); COMMIT;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Может вернуть 6 (phantom read!)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(balance) FROM accounts;  -- Snapshot

-- Другая транзакция добавит новый счет
-- INSERT INTO accounts (balance) VALUES (100); COMMIT;

SELECT SUM(balance) FROM accounts;  -- Новый счет НЕ появится в сумме (старый snapshot)
COMMIT;

-- Если конфликт обнаружен:

-- ERROR: could not serialize access due to concurrent update
-- Нужно повторить транзакцию

Приложение
    ↓
WRITE to WAL Log (на диск)  ← Безопасная точка
    ↓
APPLY to buffers (в памяти)  ← Быстро
    ↓
COMMIT успешен

-- postgresql.conf

-- fsync = on (по умолчанию) — гарантирует запись на диск
-- Каждый COMMIT записывается на диск (безопасно, медленнее)
fsync = on

-- synchronous_commit = on (по умолчанию)
-- Приложение ждет подтверждения записи WAL на диск перед возвратом COMMIT
synchronous_commit = on

-- Для высоконагруженных систем (есть риск потери последних коммитов):
synchronous_commit = local  -- Ждет записи на локальный диск, но не на реплику
synchronous_commit = off  -- Не ждет записи (быстро, но опасно!)

BEGIN;
INSERT INTO accounts VALUES (100, 'Test', 1000);
COMMIT;  -- После этого данные гарантированно на диске (если fsync=on)

-- Даже если сервер упадет прямо сейчас,
-- при рестарте эта транзакция восстановится из WAL

// Приложение
money = 100;
accountFrom.balance -= money;  // Обновлено в памяти (но еще не в БД!)
// CRASH! Сервер упал, соединение закрыто

accountTo.balance += money;    // Никогда не выполнится
// Деньги исчезли: снялись с одного счета, но не поступили на другой!

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Alice отдает
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Bob получает
COMMIT;  -- Обе операции атомарны

-- Сценарий 1: crash до COMMIT
-- → Оба UPDATE откатываются (ROLLBACK автоматически)
-- → Балансы Alice и Bob не изменились

-- Сценарий 2: crash после COMMIT
-- → WAL гарантирует, что обе операции восстановятся из логов
-- → Деньги благополучно перечислены

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Alice: OK
UPDATE accounts SET balance = balance + 100 WHERE id = 999;  -- Bob не существует: ERROR!
-- Автоматический ROLLBACK (весь блок отменяется)

-- В базе:

-- Alice: баланс не изменился
-- Ошибка была обработана атомарно

BEGIN;
-- Пакетная обработка 100 переводов
FOR i = 1 TO 100:
    SAVEPOINT sp_i;
    TRY:
        UPDATE accounts SET balance = balance - amount[i] WHERE id = from_id[i];
        UPDATE accounts SET balance = balance + amount[i] WHERE id = to_id[i];
    CATCH:
        ROLLBACK TO sp_i;  -- Откатываем только этот перевод
        LOG error

COMMIT;  -- Все успешные переводы сохранены, ошибочные откачены

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN;
UPDATE accounts SET balance = -50 WHERE id = 1;  -- Нарушает CHECK
COMMIT;  -- ERROR: violates check constraint "positive_balance"

-- Результат: UPDATE не применен, баланс остался прежним (Atomicity + Consistency)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE RESTRICT,  -- на удаление — запрет
    amount DECIMAL
);

BEGIN;
DELETE FROM users WHERE id = 1;  -- У этого user есть orders!
COMMIT;  -- ERROR: update or delete on table "users" violates foreign key constraint

-- Foreign key защитил consistency — не дал создать "зависающую" ссылку

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,  -- на удаление — удалить тоже
    amount DECIMAL
);

DELETE FROM users WHERE id = 1;  -- Все orders этого user тоже удалятся автоматически
-- Consistency сохранена: нет зависающих ссылок

-- Транзакция 1 (REPEATABLE READ)
BEGIN;
SELECT COUNT(*) FROM accounts WHERE balance > 1000;  -- 5 счетов

-- Транзакция 2: добавляет новый счет с balance > 1000 и коммитит

-- Транзакция 1
SELECT COUNT(*) FROM accounts WHERE balance > 1000;  -- Может вернуть 6!

-- Транзакция 1
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ждет Транзакцию 2

-- Транзакция 2
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- Deadlock!
-- ERROR: deadlock detected

Файловая система
    ↓
WAL логи (pg_wal/)  ← Точные записи всех операций
    ↓
Data files (base/)  ← Могут быть "грязные" (не все коммиты применены)

При рестарте:
1. PostgreSQL читает WAL логи
2. Применяет все коммиченные транзакции к data files
3. Откатывает все некоммиченные транзакции
4. База готова к работе (консистентна)

fsync = on              -- гарантирует записи на физический диск
synchronous_commit = on -- приложение ждет подтверждения записи

-- Если fsync = off:

-- PostgreSQL не гарантирует запись на диск → риск потери данных при crash'е OS
-- (но это очень редко, современные файловые системы буферизируют)

BEGIN;              -- Начало транзакции
SELECT ...;         -- Операция 1
UPDATE ...;         -- Операция 2
INSERT ...;         -- Операция 3
COMMIT;             -- Фиксация (успех) или ROLLBACK (откат)

INSERT INTO users VALUES (1, 'Alice');  -- Автоматически COMMIT после каждой команды
UPDATE users SET name = 'Bob' WHERE id = 1;  -- Еще один автоматический COMMIT

-- В psql:
\set AUTOCOMMIT off

-- Теперь каждая команда требует явного COMMIT или ROLLBACK
INSERT INTO users VALUES (2, 'Charlie');
COMMIT;

BEGIN;
INSERT INTO users VALUES (3, 'Diana');     -- OK, никаких проблем
INSERT INTO users VALUES (3, 'Edward');    -- ERROR: duplicate key
-- После ошибки транзакция переходит в состояние "failed" (отмены)
-- В этом состоянии все последующие команды игнорируются
-- Нужно явно ROLLBACK перед новыми командами
ROLLBACK;
-- После ROLLBACK все изменения в этой транзакции отменяются

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT transfer1;
-- На этой точке состояние сохранено. Баланс счета 1 уменьшился на 100.

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SAVEPOINT transfer2;
-- На этой точке оба счета обновлены.

UPDATE accounts SET balance = balance + 50 WHERE id = 3;  -- ERROR: id=3 не существует
-- Ошибка! Эта операция не выполнена.

ROLLBACK TO transfer2;  
-- Откатываемся к transfer2. Операция с id=3 отменяется.
-- Но операции до transfer2 (обновления счетов 1 и 2) остаются в транзакции.

COMMIT;  
-- Коммитятся обновления счетов 1 и 2 (первые два UPDATE).
-- UPDATE для счета 3 не был выполнен благодаря ROLLBACK TO.

-- Транзакция A
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT COUNT(*) FROM orders;  -- Результат: 5
-- (в этот момент Транзакция B вставляет новый order и коммитит)
SELECT COUNT(*) FROM orders;  -- Результат: 6 (phantom read!)
COMMIT;

-- Транзакция A
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders;  -- Результат: 5
-- (в этот момент Транзакция B вставляет новый order и коммитит)
SELECT COUNT(*) FROM orders;  -- Результат: 5 (тот же! защита от phantom read)
COMMIT;

Транзакция A                       Транзакция B
├─ Блокирует строку 1             ├─ Блокирует строку 2
├─ Ждет блокировку строки 2   ←→  ├─ Ждет блокировку строки 1
└─ Вечное ожидание                └─ Вечное ожидание

-- Транзакция A (в сессии 1)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Блокирует строку 1 (держит эксклюзивную блокировку на запись)

-- [ждем несколько секунд, не коммитим]

-- Транзакция B (в сессии 2) 
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- Блокирует строку 2 (держит эксклюзивную блокировку на запись)

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Блокирует строку 2 еще раз (уже держит блокировку, поэтому OK)

-- [В сессии 1 (Транзакция A) продолжаем]
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Ждет эксклюзивной блокировки на строку 2 (которую держит Транзакция B)
-- Транзакция A заморозилась здесь.

-- [В сессии 2 (Транзакция B) пытаемся]
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Ждет эксклюзивной блокировки на строку 1 (которую держит Транзакция A)
-- Транзакция B заморозилась здесь.

-- DEADLOCK! Обе транзакции зависли в цикле взаимного ожидания.
-- PostgreSQL обнаруживает циклическую зависимость через ~1 секунду
-- и убивает одну из них (обычно более молодую):

-- ERROR: deadlock detected

Транзакция A ждет блокировку X (держит Транзакция B)
    ↓
Транзакция B ждет блокировку Y (держит Транзакция C)
    ↓
Транзакция C ждет блокировку X (держит Транзакция A)
    ↓
Циклическая зависимость → DEADLOCK!

-- Процесс 1: обновляет в порядке id=1, затем id=2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Процесс 2: обновляет в порядке id=2, затем id=1 (ОБРАТНЫЙ порядок!)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  
-- Блокирует строку 2

UPDATE accounts SET balance = balance + 50 WHERE id = 1;  
-- Ждет блокировку строки 1 (которую держит Процесс 1)
-- Процесс 2 заморозилась.

-- Тем временем Процесс 1:

-- Блокирует строку 1, ждет строку 2 (которую держит Процесс 2)
-- Процесс 1 заморозилась.

-- DEADLOCK!
COMMIT;

-- Оба процесса обновляют в ОДИНАКОВОМ порядке: всегда меньший ID сначала, потом больший

-- Процесс 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Процесс 2
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;  
-- Пытается получить блокировку строки 1
-- Но Процесс 1 уже держит её → Процесс 2 ждет

UPDATE accounts SET balance = balance + 50 WHERE id = 2;
-- Эта строка не выполнится, пока Процесс 2 ждет строку 1

-- Процесс 1 успешно обновляет обе строки и коммитит → освобождает блокировки
-- Процесс 2 получает блокировку 1, затем обновляет 1 и 2 → успешно коммитит

-- Никакого deadlock'а благодаря одинаковому порядку
COMMIT;

CREATE OR REPLACE FUNCTION transfer_money(
    from_id INT, 
    to_id INT, 
    amount DECIMAL
) RETURNS VOID AS $$
BEGIN
    -- Всегда обновляем в порядке: меньший id → больший id
    IF from_id < to_id THEN
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    ELSE
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Используем эту функцию
SELECT transfer_money(1, 2, 100);  -- Безопасно от deadlock'ов
SELECT transfer_money(2, 1, 100);  -- Тоже безопасно (функция переставит в правильный порядок)

BEGIN;
SELECT * FROM orders WHERE id = 123;  
-- Получаем данные order'а из БД
-- Блокировка на чтение (если используется FOR UPDATE)

-- Теперь долгая бизнес-логика: 5 минут
-- — Валидация данных
-- — API вызовы
-- — Вычисления
-- — Логирование
-- ВСЕ это время транзакция активна и держит блокировку!

UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

-- Проблема: другие транзакции не могут работать с этим order'ом 5 минут!

-- ШАГ 1: Читаем данные БЕЗ транзакции (копируем в память)
SELECT * FROM orders WHERE id = 123;  
-- Результат: {id=123, total=500, ...}

-- ШАГ 2: Долгая бизнес-логика (вне транзакции!) — 5 минут
-- Никаких блокировок! Другие транзакции могут спокойно работать.
expensive_calculation_result = process_order({id=123, total=500, ...});

-- ШАГ 3: Коротенькая транзакция только для записи результата
BEGIN;
UPDATE orders SET status = 'processed', result = expensive_calculation_result WHERE id = 123;
-- Эта строка выполняется за миллисекунды
COMMIT;

-- Транзакция существовала минимально необходимое время

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  
-- Получаем строку И блокируем её для других транзакций
-- Другие транзакции не смогут даже прочитать эту строку

-- Долгая бизнес-логика, 5 минут
-- Строка заблокирована всё это время

COMMIT;

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

-- ШАГ 1: Читаем данные БЕЗ блокировки
SELECT id, balance, version FROM accounts WHERE id = 1;  
-- Результат: {id=1, balance=1000, version=3}

-- ШАГ 2: Бизнес-логика (вне транзакции)
new_balance = 1000 - 100;

-- ШАГ 3: Обновляем с проверкой версии
BEGIN;
UPDATE accounts 
SET balance = 900, version = version + 1 
WHERE id = 1 AND version = 3;
-- Если version все еще 3 → обновляем и инкрементируем версию
-- Если version != 3 (кто-то другой изменил строку) → UPDATE не произойдет (0 rows)

-- Проверяем, что UPDATE прошел успешно
IF ROW_COUNT = 0 THEN
    -- Версия не совпала → конфликт, нужен retry с новой версией
    ROLLBACK;
    -- Повторяем с новыми данными
ELSE
    -- Успех
    COMMIT;
END IF;

-- Преимущества:

-- 1. Обе транзакции могут читать одну и ту же строку одновременно
-- 2. Конфликт решается через retry, а не через взаимную блокировку
-- 3. Никакого deadlock'а

-- postgresql.conf или во время сессии
SET lock_timeout = '1s';  
-- Максимум 1 секунда ждать блокировку
-- Если блокировка держится дольше → ERROR: canceling statement due to lock timeout

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
-- Если эта строка не может получить блокировку 2 за 1 секунду → ERROR
COMMIT;

ОШИБКА: deadlock detected

int maxRetries = 3;
int retryCount = 0;

while (retryCount < maxRetries) {
    try {
        connection.setAutoCommit(false);
        
        // Операции, которые могут привести к deadlock'у
        statement.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        statement.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
        
        connection.commit();
        break;  // Успех, выходим из цикла
    } catch (SQLException e) {
        if (e.getMessage().contains("deadlock detected")) {
            connection.rollback();
            retryCount++;
            if (retryCount >= maxRetries) throw e;  // Максимум попыток исчерпан
            
            // Ждем немного перед повтором (чтобы не перегружать систему)
            Thread.sleep(100 * retryCount);
        } else {
            throw e;  // Не deadlock, а другая ошибка → пробросим
        }
    }
}

int maxRetries = 5;
long initialWait = 10;  // миллисекунды

for (int attempt = 0; attempt < maxRetries; attempt++) {
    try {
        // Бизнес-логика в транзакции
        executeTransfer();
        break;  // Успех
    } catch (DeadlockException e) {
        if (attempt == maxRetries - 1) throw e;  // Последняя попытка, сдаемся
        
        // Экспоненциальное ожидание: 10ms, 20ms, 40ms, 80ms, 160ms
        long waitTime = initialWait * (long) Math.pow(2, attempt);
        Thread.sleep(waitTime);
    }
}

@Transactional(noRollbackFor = [DeadlockException::class])
fun transferMoney(fromId: Long, toId: Long, amount: BigDecimal) {
    try {
        // код транзакции
    } catch (e: DeadlockException) {
        Thread.sleep(100)
        transferMoney(fromId, toId, amount)  // Retry
    }
}

-- postgresql.conf
log_lock_waits = on
deadlock_timeout = 1000  -- миллисекунды (по умолчанию 1000)

-- После изменения конфига перезагрузить PostgreSQL:

-- systemctl restart postgresql

-- Какие транзакции ждут блокировку
SELECT 
    pid, 
    usename, 
    pg_blocking_pids(pid) as blocked_by,
    query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid)::text != '{}';
-- Если результат не пустой → есть активное ожидание

-- Информация о блокировке
SELECT 
    l.locktype,
    l.relation::regclass,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.query_start
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;
-- Показывает, какие блокировки не предоставлены (held или waiting)

-- Сессия 1 (терминал 1)
BEGIN;
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Блокируем строку 1, но не коммитим

-- [ждем 10 секунд, не закрывая сеанс]

-- Сессия 2 (терминал 2)
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 2;
UPDATE accounts SET balance = 300 WHERE id = 1;  
-- Ждет блокировку 1 (которую держит Сессия 1)
-- Сессия 2 заморозилась

-- Вернемся в Сессию 1 (терминал 1)
UPDATE accounts SET balance = 150 WHERE id = 2;  
-- Ждет блокировку 2 (которую держит Сессия 2)

-- DEADLOCK обнаружен! Одна из сессий получит ERROR.

CREATE OR REPLACE FUNCTION transfer_money(
    from_id INT, 
    to_id INT, 
    amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    first_id INT;
    second_id INT;
BEGIN
    -- Упорядочиваем: меньший ID обновляем первым
    IF from_id < to_id THEN
        first_id := from_id;
        second_id := to_id;
    ELSE
        first_id := to_id;
        second_id := from_id;
    END IF;
    
    -- Обновляем в гарантированном порядке
    UPDATE accounts SET balance = balance - amount WHERE id = first_id AND id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = second_id AND id = to_id;
    
    -- Проверяем, что оба счета существуют и имели достаточный баланс
    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- Используем функцию
SELECT transfer_money(1, 2, 100);  -- Безопасно
SELECT transfer_money(2, 1, 100);  -- Тоже безопасно
SELECT transfer_money(1, 1, 100);  -- Ошибка (один счет)

-- Таблица с версией (для оптимистичной блокировки)
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL NOT NULL,
    version INT NOT NULL DEFAULT 1
);

-- Приложение читает данные
-- SELECT balance, version FROM accounts WHERE id = 1;  -> {balance=1000, version=3}

-- Бизнес-логика применяется (без БД)
-- new_balance = 1000 - 100 = 900;

-- Приложение обновляет, проверяя версию
BEGIN;
UPDATE accounts 
SET balance = 900, version = version + 1 
WHERE id = 1 AND version = 3;

-- Если версия совпадает → 1 row updated
-- Если версия не совпадает (другой процесс изменил запись) → 0 rows updated

-- Приложение проверяет количество обновленных строк
-- Если 0 → конфликт, retry с новыми данными
-- Если 1 → успех
COMMIT;

-- Проблема: два процесса читают одно значение, оба его изменяют, оба пишут
-- Результат: изменение одного процесса теряется (lost update)

-- Процесс 1
SELECT balance FROM accounts WHERE id = 1;  -- Читает: 1000
-- ... вычисляет ...
UPDATE accounts SET balance = 900 WHERE id = 1;  -- Пишет: 900 (успешно)

-- Процесс 2 (параллельно)
SELECT balance FROM accounts WHERE id = 1;  -- Читает: 1000 (не видит изменение Процесса 1!)
-- ... вычисляет ...
UPDATE accounts SET balance = 950 WHERE id = 1;  -- Пишет: 950
-- Результат: изменение Процесса 1 потеряно! Баланс 900, но должен быть 850 или 950.

-- Решение: оптимистичная блокировка с версией
SELECT balance, version FROM accounts WHERE id = 1;  -- Читает: {balance=1000, version=1}

-- [оба процесса выполняют свои вычисления]

-- Процесс 1
UPDATE accounts SET balance = 900, version = 2 WHERE id = 1 AND version = 1;
-- Успешно (version совпадает)

-- Процесс 2
UPDATE accounts SET balance = 950, version = 2 WHERE id = 1 AND version = 1;
-- FAIL! (version уже 2, не 1) → нужен retry
-- Retry: заново читает {balance=900, version=2}, пересчитывает, пишет с новой версией

-- Потери данных избежана!

SELECT u.name, COUNT(o.id) 
FROM users u 
JOIN orders o ON u.id = o.user_id 
GROUP BY u.id 
HAVING COUNT(o.id) > 5
    ↓
Проверяет синтаксис (все ли скобки, запятые, ключевые слова на месте)
    ↓
Создает parse tree (дерево с узлами для SELECT, FROM, JOIN, GROUP BY, HAVING)
    ↓
Если синтаксис неправильный → возвращает ошибку, процесс останавливается

Parse tree 
    ↓
Определяет все возможные пути выполнения запроса:

  - Какой порядок таблиц в JOIN?
  - Использовать индекс или полное сканирование?
  - Какая стратегия JOIN (Nested Loop, Hash, Merge)?
    ↓
Вычисляет cost (стоимость) для каждого варианта
    ↓
Выбирает вариант с минимальной стоимостью
    ↓
Создает execution plan (подробный план выполнения с операциями и порядком)

Execution plan
    ↓
Выполняет операции в порядке плана:

  - Sequential Scan: прочитать все строки таблицы
  - Index Scan: прочитать через индекс
  - Hash Join: соединить две таблицы с помощью хэш-таблицы
  - Sort: отсортировать результаты
  - GroupAggregate: сгруппировать и агрегировать
    ↓
Возвращает результаты пользователю

EXPLAIN SELECT * FROM users WHERE id = 1;

--     QUERY PLAN
-- Seq Scan on users  (cost=0.00..35.00 rows=1 width=100)
--   Filter: (id = 1)

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

--     QUERY PLAN
-- Seq Scan on users  (cost=0.00..35.00 rows=1 width=100) (actual time=0.023..0.025 rows=1 loops=1)
--   Filter: (id = 1)

EXPLAIN SELECT * FROM users WHERE id = 1;
-- Seq Scan on users (cost=0.00..35.00 rows=1)

CREATE INDEX idx_users_id ON users(id);

EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan using idx_users_id on users (cost=0.28..8.29 rows=1)

Нужна 1 строка с id=123

Sequential Scan:

  - Читает ВСЕ 1,000,000 строк
  - 1,000,000 × 100 байт = 100 МБ
  - Нужно обратиться к диску (slow disk IO)

Index Scan:

  - B-tree индекс находит нужное значение за 3-4 обращения к диску
  - Затем обращается к таблице за данными (ещё 1 обращение)
  - Всего ~4-5 обращений к диску

Разница в производительности: 100,000x раз быстрее!

EXPLAIN SELECT * FROM users;  -- Без WHERE, нужны ВСЕ строки
-- Seq Scan on users (cost=0.00..1000.00 rows=10000)

FOR EACH row IN outer_table:
    FOR EACH row IN inner_table:
        IF join_condition matches:
            output row

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.country = 'USA';

-- Nested Loop  (cost=0.29..1000.00)
--   -> Seq Scan on users u (cost=0.00..35.00)
--        Filter: (country = 'USA')
--   -> Index Scan on orders o (cost=0.29..20.00)
--        Index Cond: (user_id = u.id)

Фаза 1: Читает внутреннюю таблицу целиком и строит хэш-таблицу в памяти
Фаза 2: Для каждой строки внешней таблицы ищет совпадения в хэш-таблице

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;

-- Hash Join (cost=100.00..5000.00 rows=50000)
--   Hash Cond: (o.user_id = u.id)
--   -> Seq Scan on orders o (cost=0.00..3000.00 rows=100000)
--   -> Hash (cost=50.00..50.00 rows=10000)
--        -> Seq Scan on users u (cost=0.00..35.00 rows=10000)

Обе таблицы отсортированы по join key
    ↓
Проходит по обеим таблицам одновременно, как застежка-молния
    ↓
Когда находит совпадение, выдает результат

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;

-- Merge Join (cost=5000.00..10000.00 rows=50000)
--   Merge Cond: (u.id = o.user_id)
--   -> Index Scan using idx_users_id on users u (cost=0.29..50.00)
--   -> Index Scan using idx_orders_user_id on orders o (cost=0.29..3000.00)

cost = seq_page_cost × pages + random_page_cost × random_accesses + cpu_cost

Пример:
Таблица 1000 страниц, нужно сканировать 800 страниц
cost = 1.0 × 800 (sequential) + 0.01 × 100000 (обработка строк)
     = 800 + 1000
     = 1800

seq_page_cost = 1.0          # Стоимость чтения одной страницы последовательно
random_page_cost = 4.0       # Стоимость случайного обращения к странице (диск)
                              # На SSD: 1.1 (почти нет разницы, пока нет кэша)
cpu_tuple_cost = 0.01        # Стоимость обработки одной строки (фильтр, агрегация)
cpu_operator_cost = 0.0025   # Стоимость одной операции (+, >, LIKE и т.д.)

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Вариант 1 (Sequential Scan всех 10,000 строк):
  cost = 1.0 × 100 pages = 100

Вариант 2 (Index Scan):
  cost = random_page_cost × random_accesses + cpu_cost
       = 4.0 × 4 (4 обращения через индекс) + 0.01 × 1 (обработка 1 строки)
       = 16 + 0.01
       = 16.01

Вывод: 16.01 < 100 → выбирает Index Scan

INSERT INTO users SELECT ... FROM generate_series(1, 1000000);
-- Таблица теперь 1,000,000 строк, но статистика старая

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Seq Scan (cost=100)  ← неправильный выбор!
-- Плаер думает, что таблица маленькая (только 10,000 строк)

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

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Index Scan (cost=16.01)  ← правильный выбор!

-- Медленный запрос
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Seq Scan on users (actual time=0.023..50.5 rows=1)
-- Полное сканирование 10,000 строк, всего 50ms

-- Добавляем индекс
CREATE INDEX idx_users_email ON users(email);

-- Проверяем новый план
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Index Scan using idx_users_email (actual time=0.0023..0.005 rows=1)
-- Поиск через индекс, всего 0.005ms
-- Улучшение в 10,000 раз!

-- Медленный JOIN
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, amount DECIMAL);

EXPLAIN SELECT c.name, COUNT(o.id) 
FROM customers c 
JOIN orders o ON c.id = o.customer_id 
GROUP BY c.id;
-- Hash Join (cost=5000..10000)  ← дорого, нет индекса

-- Добавляем индекс на customer_id
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Теперь плаер может использовать Nested Loop
EXPLAIN SELECT ...;
-- Nested Loop (cost=100..500)  ← дешевле!

-- Медленно: индекс на name не используется
EXPLAIN SELECT * FROM users WHERE LOWER(name) = 'john';
-- Seq Scan (cost=0.00..1000.00)  ← не может использовать индекс
-- Плаер не знает, как использовать индекс для LOWER(name)

-- Решение 1: переписать без функции (если возможно)
EXPLAIN SELECT * FROM users WHERE name = 'John';
-- Index Scan on idx_users_name (cost=0.28..8.3)  ← использует индекс!

-- Решение 2: создать индекс на выражение
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
EXPLAIN SELECT * FROM users WHERE LOWER(name) = 'john';
-- Index Scan on idx_users_name_lower (cost=0.28..8.3)  ✓

-- Медленно: индекс может не использоваться
EXPLAIN SELECT * FROM users 
WHERE first_name = 'John' OR last_name = 'Smith';
-- Seq Scan (cost=0.00..1000.00)
-- PostgreSQL может не уметь комбинировать индексы

-- Решение: использовать UNION
EXPLAIN SELECT * FROM users WHERE first_name = 'John'
UNION
SELECT * FROM users WHERE last_name = 'Smith';
-- Index Scan (cost=0.28..8.3)  + Index Scan (cost=0.28..8.3)
-- Может быть быстрее, если есть индексы

-- Default work_mem = 4MB (очень мало)
-- Для больших JOIN, GROUP BY, ORDER BY может быть недостаточно

-- Плохо: Hash Join не влезает в memory
EXPLAIN ANALYZE SELECT ... FROM big_table1 
JOIN big_table2 ON ... 
GROUP BY ... ORDER BY ...;
-- Hash Join ... (actual time=5000ms due to disk spilling)

-- Улучшить:
SET work_mem = '256MB';

-- Теперь:
EXPLAIN ANALYZE SELECT ...;
-- Hash Join ... (actual time=50ms)  ← в 100 раз быстрее!
-- Данные теперь в памяти, нет обращений к диску

-- Массовая вставка
INSERT INTO users SELECT ... FROM generate_series(1, 1000000);

-- Статистика старая, плаер выбирает неправильный план
EXPLAIN SELECT ... WHERE ...;
-- Seq Scan (cost=100)  ← думает, что таблица маленькая

-- Обновляем статистику
ANALYZE users;  -- Для конкретной таблицы
-- Или
ANALYZE;  -- Для всех таблиц в БД

-- Теперь плаер видит правду
EXPLAIN SELECT ... WHERE ...;
-- Index Scan (cost=16)  ← правильный выбор

-- Плохо: много отдельных запросов
SELECT * FROM users WHERE id = 1;  -- 1 запрос
SELECT * FROM orders WHERE user_id = 1;  -- 2-й запрос
SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items);  -- 3-й запрос
-- Всего: 100+ запросов для 100 пользователей!

-- Хорошо: один JOIN
SELECT u.*, o.*, oi.*, p.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE u.id = 1;
-- Один запрос, один plan

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR);
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, amount DECIMAL);

-- Плохой индекс (на неправильное поле, не используется в JOIN)
CREATE INDEX idx_orders_id ON orders(id);

EXPLAIN SELECT c.name, SUM(o.amount) 
FROM customers c 
JOIN orders o ON c.id = o.customer_id 
GROUP BY c.id, c.name;

-- Hash Join (cost=1000..5000)  ← дорого!
-- Плаер не может использовать индекс, потому что индекс на id, а нужен на customer_id

-- Добавляем правильный индекс
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

EXPLAIN SELECT c.name, SUM(o.amount) 
FROM customers c 
JOIN orders o ON c.id = o.customer_id 
GROUP BY c.id, c.name;

-- Nested Loop (cost=100..500)  ← намного дешевле!
-- Теперь плаер может использовать индекс на customer_id

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXPLAIN показывает:

-- Subquery Scan on orders_subquery (cost=5000..10000)
-- Seq Scan on users (cost=0.00..1000.00)
--   Filter: (id IN (...))
-- Плаер вынужден выполнить субзапрос, потом для каждого результата искать в users

SELECT DISTINCT u.* 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 1000;

-- EXPLAIN показывает:

-- Hash Join (cost=100..500)
-- Seq Scan on orders (filter: amount > 1000)
-- Seq Scan on users
-- Плаер выполняет JOIN, результат получается быстрее

SELECT user_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY user_id;

EXPLAIN ANALYZE SELECT ...;
-- GroupAggregate (actual time=5000ms)
-- Sort (actual time=3000ms)  ← долго сортирует!
--   Seq Scan on orders (actual time=1000ms)  ← читает все данные
-- Данные не влезают в work_mem, идёт сортировка на диске

CREATE INDEX idx_orders_user_id ON orders(user_id);

EXPLAIN ANALYZE SELECT ...;
-- GroupAggregate (actual time=50ms)  ← в 100 раз быстрее!
--   Index Scan using idx_orders_user_id (actual time=30ms)
-- Данные уже отсортированы по user_id (потому что индекс B-tree), не нужна сортировка

SELECT DISTINCT email 
FROM users;

EXPLAIN ANALYZE SELECT ...;
-- HashAggregate (actual time=5000ms, memory=100mb)
-- Seq Scan on users (10,000,000 rows)
-- Плаер вынужден прочитать ВСЕ строки и хэшировать для DISTINCT

-- Если нужны просто уникальные emails
CREATE INDEX idx_users_email ON users(email);

SELECT DISTINCT email 
FROM users;

-- Не всегда помогает Index, но можно использовать
-- SELECT email FROM users GROUP BY email;
-- с индексом на email плаер может использовать Index Scan для групп

PostgreSQL/pg_wal/
├── 000000010000000000000001  (16 MB)
├── 000000010000000000000002  (16 MB)
├── 000000010000000000000003  (16 MB)
└── ...

-- postgresql.conf

-- fsync = on | off
-- Гарантирует, что данные из WAL буфера действительно записались на физический диск
-- (не останавливаются в буферах ОС)
-- ВАЖНО: fsync = off - ОПАСНО для production, может привести к потере данных при power failure!
fsync = on

-- synchronous_commit = on | local | off | remote_apply | remote_write
-- Контролирует ждет ли PostgreSQL физической записи WAL на диск перед COMMIT

-- synchronous_commit = on (default)
-- Ждет подтверждения записи WAL на диск локально перед ответом приложению
-- Самый безопасный вариант, но медленнее (накладные расходы на fsync)
synchronous_commit = on

-- synchronous_commit = local
-- Ждет записи на локальный диск но НЕ ждет репликации на standby
-- Быстрее чем on, но при crash'е мастера потеряются некоммитанные изменения на master
-- что уже покинули master и достигли приложения (RPO = 0, но потеря возможна)
synchronous_commit = local

-- synchronous_commit = off
-- Не ждет даже локальной записи перед COMMIT
-- Приложение получает подтверждение, но данные еще в буфере памяти
-- ОПАСНО! Используется только для non-critical данных или тестирования
-- При power failure потеряются последние изменения, но на очень короткий интервал
synchronous_commit = off

-- synchronous_commit = remote_write
-- Ждет, пока standby запишет WAL в свой буфер (но не применит к своим данным)
-- Быстрее чем remote_apply, но standby может потерять данные при его crash'е
synchronous_commit = remote_write

-- synchronous_commit = remote_apply
-- Ждет пока standby применит WAL к своим данным
-- Максимальная безопасность, но максимальная задержка
synchronous_commit = remote_apply

-- wal_level = minimal | replica | logical
-- Определяет сколько информации писать в WAL
-- minimal - минимум, нужен только crash recovery (нельзя делать архивирование и репликацию!)
-- replica - для репликации и PITR (обычно используется)
-- logical - для logical replication и logical decoding
wal_level = replica

Приложение: INSERT INTO table VALUES (...)
         ↓
PostgreSQL copilot: Скопировать в WAL buffer (в памяти ОС)
         ↓
synchronous_commit = on?
  - Да: Вызвать fsync() - блокирующий вызов ОС, ждет пока диск физически запишет
  - Нет: Продолжить сразу
         ↓
Приложение получит OK

Автоматические checkpoints:

1. По времени
   checkpoint_timeout = 5min  (default)
   Каждые 5 минут PostgreSQL проверяет: пора ли сделать checkpoint
   
2. По размеру WAL
   max_wal_size = 100MB  (default при установке)
   Если WAL логи выросли больше 100 MB - принудительный checkpoint
   Это гарантирует что логи не займут весь диск
   
3. Явный CHECKPOINT
   Приложение может вызвать: CHECKPOINT;
   Полезно перед обслуживанием

-- postgresql.conf

-- Как часто делать checkpoints
-- Слишком частые checkpoints = много дисковых операций = медленно
-- Слишком редкие = большой WAL, долгое восстановление при crash'е
checkpoint_timeout = 5min      # По умолчанию

-- Максимальный размер WAL логов
-- При превышении - принудительный checkpoint
-- Слишком маленький = частые checkpoints
-- Слишком большой = требуется больше места на диске для pg_wal/
max_wal_size = 100MB           # Зависит от нагрузки и места на диске

-- Как долго растягивать checkpoint процесс
-- checkpoint_completion_target = 0.9
-- Значит: checkpoint'ы должны завершиться за 90% времени между checkpoints
-- Позволяет распределить нагрузку писания на диск равномерно
-- Без этого параметра checkpoint может создать пик нагрузки на диск
checkpoint_completion_target = 0.9  # 50% - 99%

-- Минимальное расстояние в WAL между checkpoints
-- После checkpoint'а PostgreSQL будет ждать этот интервал перед следующим
min_wal_size = 80MB             # Не делать checkpoints слишком часто

Начало checkpoint
    ↓
PostgreSQL создает CHECKPOINT RECORD в WAL
    ↓
Собирает все грязные буферы (shared_buffers с флагом dirty=true)
    ↓
Распределяет писание на диск в течение checkpoint_completion_target времени
    ↓
Синхронизирует основные данные (fsync для всех изменённых блоков)
    ↓
Обновляет pg_control файл (контрольная точка восстановления)
    ↓
Удаляет или архивирует WAL файлы перед этой checkpoint
    ↓
Checkpoint завершен

-- Информация о контрольных точках
SELECT * FROM pg_control_checkpoint();
-- checkpoints: количество сделанных checkpoints
-- time: время последнего checkpoint'а

-- Если checkpoints слишком частые (каждые 30 сек):

-- 1. Увеличить max_wal_size (много нагрузки на запись)
-- 2. Увеличить shared_buffers (вмещать больше грязных страниц)
-- 3. Увеличить checkpoint_timeout

-- Если checkpoint'ы долгие (дольше 1 минуты):

-- 1. Снизить checkpoint_completion_target (распределять писание дольше)
-- 2. Увеличить shared_buffers (меньше грязных страниц на checkpoint)
-- 3. Проверить performance диска (может быть медленный SSD/HDD)

11:00 - Администратор запускает вредоносный запрос: DELETE FROM users;
        Все пользователи удалены, никто не заметил
11:05 - Обнаружена проблема, БД содержит 0 пользователей
11:10 - Нужна база на состояние 11:03 (за 2 минуты до ошибки)

-- postgresql.conf

-- Уровень логирования (должен быть replica или logical для архивирования)
wal_level = replica

-- Включить архивирование
archive_mode = on

-- Команда для архивирования каждого заполненного WAL файла
-- %p = путь к WAL файлу в pg_wal/
-- %f = только имя файла
-- Команда выполняется как shell command
archive_command = 'test ! -f /mnt/backup/wal_archive/%f && cp %p /mnt/backup/wal_archive/%f'
# Это означает: если файла еще нет в архиве, скопировать его туда
# test ! -f = проверка что файла нет (защита от двойного копирования)

-- Опционально: где хранить WAL если репликация не работает
-- При потере стендбая WAL нужно где-то держать
wal_keep_size = 1GB  # Хранить 1 GB WAL локально (для повторного подключения репликанта)

# 1. Посмотреть архив на файловой системе
ls -lh /mnt/backup/wal_archive/ | head -20
# Должны быть файлы вроде: 000000010000000000000001, 000000010000000000000002

# 2. В PostgreSQL проверить статистику архивирования
SELECT * FROM pg_stat_archiver;

-- Если архивирование отстает (old_count > 100):

-- 1. Проверить доступность `/mnt/backup/wal_archive/` 
-- 2. Проверить место на диске
-- 3. Проверить права доступа на архивную директорию
-- Пока архивирование отстает, БД не может удалить старые WAL файлы
-- и pg_wal/ будет расти (может заполнить диск!)

# Способ 1: pg_basebackup (встроенная утилита)
# -h host, -U user, -D destination, -Ft (tar format), -z (gzip compress), -P (progress)
pg_basebackup -h localhost -U backup_user \
  -D /backup/base_backup_2025_11_13 \
  -Ft -z -P

# Результат: /backup/base_backup_2025_11_13/base.tar.gz (~размер БД)
# Запомнить: backup начинается с какой LSN? 
# Это нужно при восстановлении

# Способ 2: pg_basebackup с заметкой
pg_basebackup ... -l "backup_2025_11_13_critical"

# Способ 3: На более новых версиях можно использовать SQL:
SELECT * FROM pg_backup_start('backup_2025_11_13');
-- Сделать что-то в это время (скопировать pg_data вручную)
SELECT * FROM pg_backup_stop();

# 1. Подготовить чистую директорию для pg_data
rm -rf /var/lib/postgresql/new_data
mkdir -p /var/lib/postgresql/new_data

# 2. Распаковать base backup
cd /var/lib/postgresql/new_data
tar -xzf /backup/base_backup_2025_11_13/base.tar.gz

# 3. Создать recovery.signal (PostgreSQL 12+)
# Это файл сигнализирует серверу "ты в режиме восстановления"
touch /var/lib/postgresql/new_data/recovery.signal

# 4. Создать postgresql.auto.conf с параметрами восстановления
cat > /var/lib/postgresql/new_data/postgresql.auto.conf << EOF
# Восстанавливаться до этого момента времени
recovery_target_timeline = 'latest'
recovery_target_time = '2025-11-13 11:03:00'
recovery_target_inclusive = false  # До этого времени, не включая

# Где искать архивированные WAL файлы
restore_command = 'cp /mnt/backup/wal_archive/%f "%p"'
# %f = имя файла, %p = путь куда копировать
EOF

# 5. Запустить PostgreSQL
# Он прочитает recovery.signal и запустится в режиме восстановления
systemctl start postgresql
# или
pg_ctl -D /var/lib/postgresql/new_data start

# 6. PostgreSQL будет:
#    а) Применять base_backup
#    б) Вызывать restore_command для каждого WAL файла
#    в) Воспроизводить операции из WAL до recovery_target_time
#    г) Остановиться при достижении целевого времени
#    д) Удалить recovery.signal
#    е) БД готова!

-- Восстановиться к конкретному времени
recovery_target_time = '2025-11-13 11:03:00'

-- Восстановиться к конкретной LSN (Log Sequence Number)
recovery_target_lsn = '0/3000000'

-- Восстановиться к конкретной транзакции
recovery_target_xid = 12345

-- Восстановиться на самый последний момент (default)
recovery_target = 'immediate'

-- Включить ли целевое время/LSN в восстановление
recovery_target_inclusive = true   # До и включая
recovery_target_inclusive = false  # До, но не включая

Сценарий: сервер упал (power failure, kill -9, crash БД)
    ↓
Администратор запускает сервер заново
    ↓
PostgreSQL видит "unclean shutdown" в pg_control файле
    ↓
Запускается CRASH RECOVERY
    ↓
PostgreSQL читает последний checkpoint из pg_control
    ↓
PostgreSQL загружает WAL файлы с момента checkpoint'а
    ↓
Воспроизводит все операции из WAL (redo phase)
    ↓
Откатывает все незаконченные транзакции (undo phase)
    ↓
Если были открытые транзакции - они ОТКАТЫВАЮТСЯ
    ↓
БД переводится в режим готовности
    ↓
Новые клиенты могут подключаться

-- Во время работы БД
BEGIN;
UPDATE users SET active = true WHERE id = 1;
-- Сервер упал ЗДЕСЬ (не было COMMIT)

-- При восстановлении:

-- PostgreSQL видит что транзакция 12345 не имеет COMMIT записи в WAL
-- Откатывает все её операции
-- UPDATE отменяется
-- users.active остается как было до UPDATE

Сценарий: диск с pg_data полностью повреждён (отказал, удалена, скомпрометирована)
    ↓
1. Удаляем поврежденный pg_data (или переименовываем на .old)
2. Распаковываем base_backup в новый pg_data
   └─> pg_data теперь содержит состояние БД от момента backup'а (например 23:00)
3. Копируем все WAL архивные файлы в pg_wal/
   └─> pg_wal теперь содержит лог всех операций с 23:00 до "сейчас"
4. Создаем recovery.signal (сигнал режиму восстановления)
5. Создаем postgresql.auto.conf с:
   └─> recovery_target_time (если хотим на конкретный момент)
   └─> restore_command (где искать архивные WAL файлы)
6. Запускаем PostgreSQL
    ↓
PostgreSQL видит recovery.signal:
  а) Включает режим архивного восстановления (archival recovery mode)
  б) Вызывает restore_command для каждого WAL файла по порядку
  в) Воспроизводит операции из WAL
  г) Если recovery_target_time указано - останавливается при его достижении
  д) Удаляет recovery.signal
  е) Становится новым primary (или standby, если настроено)

000000010000000000000001  ← содержит операции 0-16MB
000000010000000000000002  ← содержит операции 16-32MB
000000010000000000000003  ← содержит операции 32-48MB

-- postgresql.conf для critical production БД

-- === DURABILITY ===
fsync = on                          # ОБЯЗАТЕЛЬНО! Без этого потеря данных при power failure
synchronous_commit = on             # Ждать физической записи перед COMMIT

-- === WAL ARCHIVING ===
wal_level = replica                 # Нужно для archiving и replication
archive_mode = on
archive_command = 'test ! -f /backup/wal_archive/%f && cp %p /backup/wal_archive/%f'
archive_timeout = 300               # Архивировать每300 sec даже если WAL не заполнен

-- === CHECKPOINTS ===
checkpoint_timeout = 10min          # Не слишком часто, не слишком редко
max_wal_size = 1GB                  # На основе available disk space
min_wal_size = 500MB                # Не делать checkpoints слишком часто
checkpoint_completion_target = 0.9  # Распределить нагрузку

-- === REPLICATION ===
max_wal_senders = 3                 # Максимум 3 стендбая
wal_keep_size = 1GB                 # Хранить WAL локально для репликантов

# 1. Ежедневный base backup (например в 2:00 ночи)
crontab: 0 2 * * * pg_basebackup -D /backup/daily_backup_$(date +%Y%m%d) -Ft -z -P

# 2. WAL архивирование (автоматическое через archive_command)
# Проверять что работает:
* * * * * psql -c "SELECT archived_count FROM pg_stat_archiver" 
# Если archived_count не растет - проблема!

# 3. Хранение:
# - Base backups: 7-30 дней на NAS/S3 (зависит от RTO/RPO)
# - WAL archives: 7-14 дней на диске, потом на долгосрочное хранилище

# 4. Тестирование восстановления (ежемесячно!)
# На тестовой машине восстановить на конкретный момент
# Проверить что данные корректны

-- Возможная причина: архивирование не работает
SELECT * FROM pg_stat_archiver;
-- Если failed_count > 0 или archived_count не растет

-- Проверить что archive_command работает
-- Вручную выполнить команду:
bash> test ! -f /backup/wal_archive/000000010000000000000001 && \
      cp /var/lib/postgresql/pg_wal/000000010000000000000001 \
      /backup/wal_archive/000000010000000000000001

-- Если ошибка - проблема с доступом к архивной директории
-- Проверить: существует ли директория, права доступа, место на диске

-- Возможные причины:

-- 1. max_wal_size слишком большой (много WAL логов воспроизводить)
-- Решение: уменьшить max_wal_size (но будет больше checkpoints)

-- 2. checkpoint_timeout слишком большой (долго между checkpoints)
-- Решение: уменьшить checkpoint_timeout

-- 3. shared_buffers слишком большой (много грязных страниц на checkpoint)
-- Решение: может быть оптимизировать нагрузку

-- Проверить текущий размер pg_wal/
ls -lh /var/lib/postgresql/pg_wal/ | tail -20
# Сколько WAL файлов? Если 100+ файлов - это может быть проблемой

# Проверить что файлы на месте:
ls /backup/wal_archive/ | grep "^000000010000000000000001$"

# Проверить что restore_command работает:
bash> cp /backup/wal_archive/000000010000000000000001 /tmp/test_wal
# Если ошибка - проверить права доступа

# Проверить postgresql.auto.conf параметры:
grep "restore_command\|recovery_target" /var/lib/postgresql/pg_data/postgresql.auto.conf

#!/bin/bash
# Тестовый скрипт восстановления на момент времени

# 1. Создать тестовую БД
createdb test_pitr

# 2. Создать таблицу и добавить данные
psql test_pitr << SQL
CREATE TABLE products (id INT PRIMARY KEY, name TEXT, price DECIMAL);
INSERT INTO products VALUES (1, 'Apple', 0.5), (2, 'Orange', 0.3);
SQL

# 3. Запомнить текущее время (нужно для восстановления)
TARGET_TIME=$(date '+%Y-%m-%d %H:%M:%S')
echo "Target recovery time: $TARGET_TIME"
sleep 5

# 4. Добавить еще данных
psql test_pitr << SQL
INSERT INTO products VALUES (3, 'Banana', 0.2);
INSERT INTO products VALUES (4, 'Grape', 1.0);
SQL

# 5. Создать base backup
pg_basebackup -D /tmp/test_backup -Ft -z

# 6. Восстановить на старую дату
rm -rf /tmp/test_restored
mkdir -p /tmp/test_restored
cd /tmp/test_restored
tar -xzf /tmp/test_backup/base.tar.gz

# 7. Создать recovery.signal
touch /tmp/test_restored/recovery.signal

# 8. Создать postgresql.auto.conf с параметрами восстановления
cat > /tmp/test_restored/postgresql.auto.conf << EOF
recovery_target_time = '$TARGET_TIME'
recovery_target_inclusive = false
restore_command = 'cp /var/lib/postgresql/pg_wal/%f "%p"'
EOF

# 9. Запустить сервер на другом порту
pg_ctl -D /tmp/test_restored -o "-p 5433" start

# 10. Проверить что восстановилось на нужный момент
sleep 5
psql -p 5433 -d test_pitr -c "SELECT * FROM products ORDER BY id;"
# Должно быть 2 записи (до вставки Apple и Orange)

# 11. Очистить
pg_ctl -D /tmp/test_restored stop

-- Для надежности (production):
fsync = on
synchronous_commit = on
wal_level = replica
archive_mode = on

-- Для производительности восстановления:
checkpoint_timeout = 10min
max_wal_size = 1GB
checkpoint_completion_target = 0.9

-- Для PITR:
archive_command = 'test ! -f /backup/wal_archive/%f && cp %p /backup/wal_archive/%f'
restore_command = 'cp /backup/wal_archive/%f "%p"'

Транзакция 1 (xid=100): UPDATE users SET name = 'Bob' WHERE id = 1;
    ↓
Строка с (id=1, name='Alice') xmin=95 xmax=100  → помечена как "удалена для 100+"
Строка с (id=1, name='Bob')   xmin=100 xmax=∞   → новая версия
    ↓
Обе версии занимают место на диске одновременно!

Позже, когда все транзакции завершены и максимальный xmin превышает 100:
Строка (id=1, name='Alice') → МЕРТВАЯ (ни одна активная транзакция не нуждается в ней)
    ↓
VACUUM должен удалить мертвые версии и освободить место

UPDATE users SET status = 'active' 100 раз;

Если в таблице 1 млн строк:

- После 1-го UPDATE: 2 млн версий (1 млн живых + 1 млн мертвых)
- После 100-го UPDATE: 101 млн версий (1 млн живых + 100 млн мертвых)

Размер диска: 1 GB → 101 GB (рост в 100 раз!)

VACUUM users;

1. Проходит по записанным TID'ам
2. Удаляет строку с диска
3. Отмечает это место как свободное в FSM (Free Space Map)

VACUUM FULL users;

1. Переписывает всю таблицу в новый файл
   - Копирует ТОЛЬКО живые строки в правильном порядке
   - Убирает все дырки (фрагментацию)
   
2. Отдает старый файл ОС (место действительно освобождается на диске)

3. Перестраивает индексы
   - Старые индексы указывали на старые TID'ы
   - Новые версии имеют новые TID'ы → нужна переиндексация

4. Полностью блокирует таблицу на весь процесс

-- Базовый VACUUM (рекомендуемый)
VACUUM users;
-- Выполняется БЕЗ эксклюзивной блокировки
-- Может работать параллельно с SELECT, INSERT, UPDATE, DELETE

-- VACUUM + обновление статистики
VACUUM ANALYZE users;
-- После очистки обновляет pg_statistic для плана запросов

-- VACUUM FULL (ОПАСНО!)
VACUUM FULL users;
-- Полная блокировка таблицы, очень медленно для больших таблиц

-- VACUUM конкретных колонок (редко используется)
VACUUM (ANALYZE, VERBOSE) users (id, name);
-- ANALYZE соберет статистику только по id и name

-- postgresql.conf

# Включить autovacuum (по умолчанию ON)
autovacuum = on

# Количество фоновых процессов autovacuum одновременно
autovacuum_max_workers = 3  # default

# Как часто проверять таблицы (в секундах)
autovacuum_naptime = 1min  # default

# Главный параметр: запустить VACUUM когда мертвые версии составляют % от таблицы
autovacuum_vacuum_scale_factor = 0.1  # 10% мертвых версий (default)

# Минимальное абсолютное количество мертвых версий для VACUUM
autovacuum_vacuum_threshold = 50  # default

# Аналогично для ANALYZE (обновления статистики)
autovacuum_analyze_scale_factor = 0.05  # 5% (default)
autovacuum_analyze_threshold = 50  # default

Таблица: 10,000 строк
autovacuum_vacuum_scale_factor = 0.1 (10%)
autovacuum_vacuum_threshold = 50

VACUUM запустится когда:
dead_tuples >= max(10,000 * 0.1, 50) = max(1,000, 50) = 1,000

-- Высоконагруженная таблица (много UPDATE'ов)
ALTER TABLE accounts SET (
    autovacuum_vacuum_scale_factor = 0.05,  # 5% (вместо 10%, чище чаще)
    autovacuum_vacuum_threshold = 100,
    autovacuum_analyze_scale_factor = 0.02, # 2% (чаще обновляем статистику)
    autovacuum_analyze_threshold = 50
);

-- Таблица, которую редко обновляют (mostly read-only)
ALTER TABLE metadata SET (
    autovacuum_vacuum_scale_factor = 0.2,   # 20% (чистим реже)
    autovacuum_vacuum_threshold = 200,
    autovacuum_enabled = off  # Можем вообще отключить autovacuum
);

Симптомы:

- Высокая нагрузка на CPU (особенно для больших таблиц)
- Другие операции замедляются (I/O конкуренция)
- vacuum_cost_limit ограничивает скорость VACUUM

Решение:

- Увеличить autovacuum_vacuum_scale_factor (0.1 → 0.2)
- Увеличить autovacuum_naptime (проверять реже)
- Увеличить vacuum_cost_limit

Симптомы:

- Таблица растет на диске (мертвые версии скапливаются)
- Index bloat (индексы тоже раздуваются)
- Замедление запросов со временем
- Риск hit transaction ID (xid) wraparound (редко, но критично)

Решение:

- Уменьшить autovacuum_vacuum_scale_factor (0.1 → 0.05)
- Уменьшить autovacuum_naptime (проверять чаще)
- Увеличить autovacuum_max_workers (больше параллельных процессов)

-- Для большинства приложений (balanced)
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 50

-- Для high-churn таблиц (много INSERT/UPDATE/DELETE)
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 100
autovacuum_analyze_scale_factor = 0.02
autovacuum_analyze_threshold = 50

-- Для read-mostly таблиц (стабильные данные)
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 100
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 100

Вопросы, на которые отвечает статистика:
1. Сколько всего строк в таблице?
2. Распределение значений в колонке (min, max, avg)?
3. Сколько NULL значений?
4. Есть ли корреляция между колонками?
5. Какой процент строк содержит значение = 'active'?

SELECT * FROM users WHERE status = 'active';

Planner гадает:

- Может вернуть 1% строк (нужен index)
- Может вернуть 99% строк (нужен seq scan)

Неправильное предположение → неправильный план → медленный запрос

Planner знает точно:

- 'active' составляет 80% строк
- Index не поможет (слишком много строк)
- Выбирает sequential scan

Правильное решение → быстрый запрос

ANALYZE users;

-- Обновить статистику всей таблицы
ANALYZE users;

-- Обновить статистику конкретных колонок (редко нужно)
ANALYZE users (id, email);

-- Детальная статистика (VERBOSE выводит информацию)
ANALYZE (VERBOSE) users;
-- Вывод: количество живых/мертвых строк, время выполнения

-- ANALYZE без VACUUM (отдельно)
ANALYZE users;

-- Чаще используется вместе с VACUUM
VACUUM ANALYZE users;

1. После массовой вставки
   INSERT INTO users SELECT * FROM import_table;
   ANALYZE users;  -- Planner теперь знает размер таблицы

2. После массового удаления
   DELETE FROM users WHERE archived = true;
   ANALYZE users;  -- Плана запросов могут измениться

3. После восстановления из backup'а
   -- Статистика не восстанавливается, нужна переиндексация
   REINDEX TABLE users;
   ANALYZE users;

4. После изменения distribution параметров
   ALTER TABLE users ALTER COLUMN status SET STATISTICS 100;
   ANALYZE users;

-- Таблицы с большим процентом мертвых версий
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    round(100.0 * (total_relation_size - pg_relation_size) / total_relation_size) as wasted_percent
FROM (
    SELECT 
        schemaname,
        tablename,
        pg_total_relation_size(schemaname||'.'||tablename) as total_relation_size,
        pg_relation_size(schemaname||'.'||tablename) as pg_relation_size
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
) t
WHERE total_relation_size > 1000000  -- Только таблицы > 1MB
ORDER BY wasted_percent DESC
LIMIT 20;

-- Интерпретация:

-- wasted_percent > 30% → нужна чистка
-- wasted_percent > 50% → срочная чистка
-- wasted_percent > 70% → может быть VACUUM FULL (если таблица стабилизируется)

-- Когда был последний VACUUM/AUTOVACUUM для каждой таблицы
SELECT 
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup)) as dead_percent
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

-- Что важно смотреть:

-- 1. last_autovacuum = NULL → AUTOVACUUM никогда не запускался (проблема!)
-- 2. dead_percent > 20% → много мертвых версий, нужна чистка
-- 3. vacuum_count = 0 → таблица никогда не вакуумилась (проблема!)

-- postgresql.conf
log_autovacuum_min_duration = 0
# log_autovacuum_min_duration = 1000  # Только операции > 1 секунды

-- Потом в логах можно видеть:

-- LOG: autovacuum: VACUUM users (pages: 0 removed, 1520 remain)
-- LOG: analyze: ANALYZE users (total time: 125.524 ms)

-- Таблица accounts постоянно обновляется (баланс, статус платежа и т.д.)

ALTER TABLE accounts SET (
    autovacuum_vacuum_scale_factor = 0.05,     -- Чистим при 5% мертвых (вместо 10%)
    autovacuum_vacuum_threshold = 100,         -- Минимум 100 мертвых версий
    autovacuum_analyze_scale_factor = 0.02,    -- Обновляем статистику при 2%
    autovacuum_analyze_threshold = 50,
    autovacuum_vacuum_cost_delay = 1,          -- 1ms задержка (более агрессивно)
    autovacuum_vacuum_cost_limit = 500         -- Больше операций за раз
);

-- Результат: очистка происходит чаще, но не перегружает систему

-- Вставили миллион новых пользователей из external источника
INSERT INTO users (name, email, created_at) 
SELECT name, email, NOW() FROM external_import;

-- На этот момент:

-- 1. Таблица выросла на 1 млн новых строк
-- 2. Статистика старая (Query Planner не знает о новых строках)
-- 3. ANALYZE не запустился (может быть задержка)

-- Принудительно обновляем статистику
ANALYZE users;

-- Теперь:

-- EXPLAIN SELECT * FROM users WHERE ...
-- покажет правильное количество строк
-- и правильный план выполнения

-- Старый паттерн: UPDATE всей таблицы в цикле
BEGIN;
FOR i IN 1..100 LOOP
  UPDATE products SET price = price * 1.1 WHERE category_id = 1;
  COMMIT;
  BEGIN;
END LOOP;

-- Результат: 100% мертвых версий в таблице
-- Таблица выросла в 100+ раз!

-- Лучший подход:
DELETE FROM products WHERE category_id = 1;
INSERT INTO products SELECT * FROM archive.products WHERE category_id = 1;
-- Потом VACUUM и ANALYZE

-- Или даже лучше: CREATE TABLE AS SELECT и переименовать
CREATE TABLE products_new AS SELECT * FROM products WHERE category_id != 1;
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_new RENAME TO products;
DROP TABLE products_old;
-- Это дает полностью чистую таблицу

-- postgresql.conf для системы с высокой нагрузкой

# AUTOVACUUM основные параметры
autovacuum = on
autovacuum_max_workers = 4              # 4 процесса параллельно
autovacuum_naptime = 10s                # Проверять каждые 10 сек (вместо 1 мин)

# Параметры VACUUM
autovacuum_vacuum_scale_factor = 0.05   # 5% (агрессивнее чистим)
autovacuum_vacuum_threshold = 50        # Минимум 50 мертвых
autovacuum_analyze_scale_factor = 0.02  # 2% для ANALYZE
autovacuum_analyze_threshold = 30

# Стоимость VACUUM (не перегружать систему)
vacuum_cost_limit = 200                 # Максимум 200 ms на операцию
vacuum_cost_delay = 2                   # Ждать 2ms между операциями
# Формула: один UPDATE = 20 cost units
# 200 ms на 10 cost units = 200 / 10 = 20 UPDATES в секунду (контролируемо)

# Логирование
log_autovacuum_min_duration = 1000      # Логировать VACUUM > 1 сек
log_min_duration_statement = 5000       # Логировать запросы > 5 сек

# Результат:
# - AUTOVACUUM проверяет таблицы чаще (каждые 10 сек)
# - Очищает агрессивнее (5% vs 10%)
# - Не перегружает систему (cost_limit контролирует нагрузку)
# - Дает достаточно времени на очистку между пиками трафика

-- Если VACUUM работает медленно, смотрим:

1. Размер таблицы и количество мертвых версий:
SELECT 
    pg_size_pretty(pg_total_relation_size('public.large_table')),
    (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'large_table');

-- Если > 1M мертвых версий и таблица > 10GB → очень долгий VACUUM

2. Параметры VACUUM:
SHOW vacuum_cost_limit;      -- Если < 200, VACUUM может быть очень медленным
SHOW vacuum_cost_delay;
-- Если cost_delay > 10ms, это еще больше замедляет

3. Попытка ускорить (осторожно!):
SET vacuum_cost_limit = 1000;       -- Уменьшить ограничение на стоимость
SET vacuum_cost_delay = 0;          -- Убрать задержку между операциями
VACUUM users;                        -- Теперь быстрее, но нагружает систему

4. Если очень срочно и таблица малоиспользуемая:

-- Можно вообще отключить cost limit
SET vacuum_cost_limit = 0;
VACUUM ANALYZE users;
-- Но делать в off-peak время!