Раздел 11 — Индексы, транзакции, представления, оптимизация

Индекс — вспомогательная структура, позволяющая находить строки, не перебирая таблицу. Основной тип — B-дерево: поиск за O(log N) вместо O(N). На миллионе строк это разница между двадцатью обращениями и миллионом.

-- Обычный индекс
CREATE INDEX idx_posts_community_id ON posts (community_id);

-- Составной: порядок столбцов имеет значение
CREATE INDEX idx_likes_user_date ON likes (user_id, liked_at);

-- Уникальный
CREATE UNIQUE INDEX uq_posts_community_title ON posts (community_id, title);

-- Частичный: индексируется только часть строк
CREATE INDEX idx_posts_active ON posts (created_at)
    WHERE deleted_at IS NULL;

-- Функциональный: индекс по выражению
CREATE INDEX idx_users_name_upper ON users (upper(name));

-- Построение без блокировки записи — на боевой базе только так
CREATE INDEX CONCURRENTLY idx_views_user_id ON views (user_id);

Почему индекс не используется

-- Только план, запрос не выполняется
EXPLAIN SELECT * FROM posts WHERE community_id = 1;

-- Выполнить и показать реальные числа — основной рабочий вариант
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts WHERE community_id = 1;

-- Проверить, помог бы индекс: временно запретить полное сканирование
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM posts WHERE community_id = 1;
SET enable_seqscan = on;

Представление (VIEW) — сохранённый запрос, к которому обращаются как к таблице. Данных не хранит: при каждом обращении выполняется исходный запрос.

CREATE VIEW v_user_activity AS
SELECT u.id, u.name, u.city,
       (SELECT count(*) FROM likes l WHERE l.user_id = u.id) AS likes_count,
       (SELECT count(*) FROM views v WHERE v.user_id = u.id) AS views_count
FROM   users u;

SELECT * FROM v_user_activity WHERE likes_count > 2;

Материализованное представление хранит результат физически. Обращение мгновенно, но данные устаревают до следующего обновления.

CREATE MATERIALIZED VIEW mv_community_stats AS
SELECT c.id, c.name,
       count(p.id) AS posts_count,
       round(avg(p.score)) AS avg_score
FROM   communities c
LEFT JOIN posts p ON p.community_id = c.id
GROUP BY c.id, c.name;

-- Обновление: блокирует чтение на время пересчёта
REFRESH MATERIALIZED VIEW mv_community_stats;

-- Обновление без блокировки чтения — требует уникального индекса
CREATE UNIQUE INDEX uq_mv_community_stats ON mv_community_stats (id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_community_stats;

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

-- Точки сохранения: откат части транзакции
BEGIN;
    INSERT INTO posts (community_id, title, score) VALUES (1, 'первый', 100);
    SAVEPOINT sp1;
    INSERT INTO posts (community_id, title, score) VALUES (99, 'ошибка', 100);
    -- нарушение внешнего ключа
    ROLLBACK TO sp1;      -- откатили только вторую вставку
    INSERT INTO posts (community_id, title, score) VALUES (2, 'второй', 200);
COMMIT;                  -- сохранились первая и третья

Когда транзакции работают параллельно, возможны четыре типа проблем.

-- Задать уровень для транзакции
BEGIN ISOLATION LEVEL REPEATABLE READ;
    SELECT sum(balance) FROM accounts;
    -- любой повтор в этой транзакции даст то же число
COMMIT;

Потерянное обновление — самая коварная аномалия: данные молча теряются, ошибки нет. Возникает при схеме «прочитал, посчитал в приложении, записал».

-- Транзакция A            Транзакция B
-- SELECT balance → 1000
--                         SELECT balance → 1000
-- UPDATE SET balance=900
--                         UPDATE SET balance=1200
-- Итог 1200: списание A потеряно

Три способа не потерять обновление:

-- 1. Считать в самой базе — атомарно, чтения в приложении нет вообще
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 2. Пессимистичная блокировка: строка занята до конца транзакции
BEGIN;
    SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
    -- расчёт в приложении
    UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- 3. Оптимистичная блокировка: версия строки
UPDATE accounts SET balance = 900, version = version + 1
WHERE  id = 1 AND version = 5;
-- 0 изменённых строк = кто-то опередил, читаем заново и повторяем

Взаимная блокировка (deadlock) — две транзакции ждут ресурсы друг друга. PostgreSQL обнаруживает это автоматически и прерывает одну из них.

-- Транзакция A            Транзакция B
-- UPDATE строку 1
--                         UPDATE строку 2
-- UPDATE строку 2  ← ждёт B
--                         UPDATE строку 1  ← ждёт A
-- ERROR: deadlock detected