Как работает СУБД и зачем SQL

Как работает СУБД и зачем SQL

Главная идея

Как это выглядит на практике

1. Представь: у тебя есть Django-приложение и таблица orders с 5 миллионами строк. Пользователь открывает свой профиль — приложение делает запрос: SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10. Как именно PostgreSQL его выполняет?
2. Шаг 1 — Подключение. Django открывает TCP-соединение к PostgreSQL (обычно localhost:5432). PostgreSQL fork'ает новый OS-процесс специально для этого соединения. Этот процесс будет обслуживать только тебя до закрытия соединения.
3. Шаг 2 — Парсинг. Parser читает SQL-строку как текст и проверяет синтаксис. Если написать SELEKT вместо SELECT — получишь ошибку именно здесь, до обращения к данным. Parser строит AST (Abstract Syntax Tree) — дерево разбора запроса.
4. Шаг 3 — Анализ. Analyzer проверяет, существует ли таблица orders, есть ли столбцы user_id и created_at, имеет ли текущий пользователь право читать эту таблицу. Если orders не существует — ошибка relation 'orders' does not exist.
5. Шаг 4 — Планирование. Planner — самый умный компонент. Он знает статистику: сколько строк в таблице (n_live_tup), как распределены значения user_id, есть ли индекс на user_id. Если индекс есть — план будет 'Index Scan on orders using idx_orders_user_id'. Если нет — 'Seq Scan on orders' — последовательный перебор всех 5 миллионов строк.
6. Шаг 5 — Выполнение без индекса. Без индекса Executor читает страницы данных последовательно: страница 1 (содержит строки 1-200), страница 2 (строки 201-400)... и так все 5 миллионов строк. Для каждой проверяет: user_id = 12345? Находит нужные строки, сортирует, берёт 10. Время: 3-5 секунд.
7. Шаг 6 — Выполнение с индексом. С B-tree индексом на user_id Executor делает Index Scan: по B-tree находит все указатели на строки с user_id = 12345 (допустим, 47 строк), читает только эти страницы. Время: 5-10 миллисекунд. Разница в 300-1000 раз!
8. Шаг 7 — Кеш. Если эти же страницы уже в shared buffers (кеш PostgreSQL в RAM) — диск не читается вообще. Первый запрос читает с диска, последующие — из RAM. Вот почему первый запрос после запуска сервера медленнее.
9. Шаг 8 — Результат. Executor формирует набор строк (result set) и отправляет их клиенту по TCP. Django получает Python-объекты, ORM превращает строки в модели. Пользователь видит свои заказы.
10. Теперь другой сценарий: вы решаете добавить возможность поиска заказов по тексту комментария. WHERE comment LIKE '%возврат%'. Даже с индексом на comment — запрос будет медленным: паттерн начинается с %, индекс B-tree не работает. Нужен GIN-индекс на tsvector или pg_trgm. Именно это понимание приходит из знания архитектуры.
11. Важная деталь: PostgreSQL хранит данные в файлах на диске как набор 8KB-страниц. Каждая страница содержит несколько строк (tuples). При UPDATE PostgreSQL НЕ перезаписывает строку на месте — он создаёт новую версию строки в другой странице и помечает старую как мёртвую (dead tuple). Это MVCC — механизм многоверсионного параллелизма.
12. Следствие MVCC: со временем в таблице накапливаются мёртвые строки (например, после миллиона UPDATE). Это называется table bloat — таблица физически занимает больше места, чем живых данных. Autovacuum периодически очищает мёртвые строки. Без autovacuum таблица будет только расти, а запросы замедлятся.

Примеры кода

Жизненный цикл запроса — видим план через EXPLAIN

-- Смотрим что PostgreSQL собирается делать (без выполнения)
EXPLAIN
SELECT id, total_amount, created_at
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

-- Типичный вывод БЕЗ индекса:
-- Limit  (cost=185432.00..185432.03 rows=10)
--   ->  Sort  (cost=185432.00..185443.50)
--         Sort Key: created_at DESC
--         ->  Seq Scan on orders  (cost=0.00..185182.00 rows=100000)
--               Filter: (user_id = 12345)

-- Типичный вывод С индексом:
-- Limit  (cost=0.56..12.34 rows=10)
--   ->  Index Scan using idx_orders_user_id on orders
--         Index Cond: (user_id = 12345)

EXPLAIN ANALYZE — реальное время выполнения

-- ANALYZE выполняет запрос и показывает реальное время
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total_amount, created_at
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

-- Вывод с индексом:
-- Limit  (actual time=0.123..0.145 rows=10 loops=1)
--   ->  Index Scan ... (actual time=0.120..0.140 rows=10 loops=1)
--         Buffers: shared hit=15 read=2
-- Planning Time: 0.234 ms
-- Execution Time: 0.178 ms

Смотрим активные запросы и находим медленные

-- Все запросы дольше 1 секунды
SELECT
  pid,
  now() - query_start AS duration,
  state,
  left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '1 second'
  AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Статистика по таблицам: живые и мёртвые строки
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Что происходит под капотом

• PostgreSQL хранит данные в файлах как набор страниц по 8KB (heap pages). Каждая страница имеет заголовок, массив указателей на строки (line pointer array) и сами строки (tuples). Строка не может занимать больше одной страницы — большие данные (TEXT > ~2KB) хранятся отдельно в системе TOAST (The Oversized Attribute Storage Technique) с автоматическим сжатием.
• Shared buffers — кеш в общей памяти, доступный всем backend-процессам одновременно. При обращении к странице PostgreSQL сначала ищет её в shared buffers; при промахе (cache miss) читает с диска и кладёт в кеш. Дефолтное значение 128MB — катастрофически мало для продакшена; рекомендуется 25% RAM. При размере БД, помещающейся в shared buffers полностью, повторные запросы не трогают диск вообще.
• Query planner собирает статистику о данных через ANALYZE: гистограммы распределения значений, количество уникальных значений (n_distinct), корреляцию физического порядка строк с логическим. На основе статистики он оценивает selectivity (какой % строк вернёт условие WHERE user_id = X) и выбирает план с наименьшей оценочной стоимостью. Устаревшая статистика (не было ANALYZE после массовых вставок) приводит к плохим планам.
• WAL (Write-Ahead Log) — журнал изменений. Правило WAL: любое изменение данных должно быть сначала записано в WAL и сброшено на диск, и только потом страница данных считается изменённой. При crash PostgreSQL смотрит последний checkpoint и воспроизводит WAL-записи после него — данные восстанавливаются гарантированно. WAL также используется для потоковой репликации: standby-сервер получает WAL-записи и применяет их.
• MVCC (Multi-Version Concurrency Control) позволяет транзакциям видеть консистентный снимок данных, не блокируя друг друга. При UPDATE PostgreSQL создаёт новую версию строки (с новым xmin — ID транзакции, создавшей её) и помечает старую версию xmax = ID текущей транзакции. Читающие транзакции видят версию, которая была актуальна на момент начала их снимка (snapshot). Это значит, что читатели не блокируют писателей и наоборот — ключевое преимущество перед lock-based СУБД.
• Autovacuum daemon запускается автоматически и периодически выполняет VACUUM для таблиц с накопившимися мёртвыми строками. VACUUM не освобождает место на диске (для этого VACUUM FULL), но помечает пространство мёртвых строк как доступное для новых данных. VACUUM ANALYZE обновляет статистику для планировщика. Для больших активных таблиц нужно снижать autovacuum_vacuum_scale_factor — иначе autovacuum срабатывает слишком редко.
• Планировщик выбирает алгоритм JOIN: Nested Loop (эффективен если внутренняя таблица маленькая или есть индекс), Hash Join (строит хеш-таблицу из меньшей стороны в work_mem и проходит по большей — хорош для больших таблиц без индексов), Merge Join (обе стороны уже отсортированы или можно отсортировать). Выбор алгоритма зависит от размеров таблиц, наличия индексов и параметра work_mem.
• Транзакционный ID (xid) — 32-битное число, увеличивающееся с каждой транзакцией. PostgreSQL имеет механизм защиты от wraparound (переполнения xid после 2 миллиардов транзакций): старые строки периодически 'замораживаются' (frozen) — их xmin заменяется специальным значением FrozenTransactionId. Без регулярного VACUUM база данных через ~2 млрд транзакций откажет с критической ошибкой wraparound.
• Индексы в PostgreSQL — отдельные структуры данных (B-tree, GIN, GiST, BRIN, Hash), физически отдельные от таблицы. При INSERT/UPDATE/DELETE PostgreSQL обновляет все индексы таблицы — это стоимость, которую платишь за быстрые SELECT. Поэтому 20 индексов на таблице с интенсивной записью могут замедлить вставки в 5-10 раз.
• PostgreSQL логирует медленные запросы при log_min_duration_statement = '1000' (мс). Файл postgresql.log хранит все запросы дольше порога. pgBadger — инструмент парсинга этого лога с красивыми отчётами. Для продакшена обязательно настрой логирование медленных запросов — иначе ты слепой в момент инцидента.

Термины урока

Мини-разбор реальной ситуации

Стартап строит SaaS-продукт. В разработке — SQLite, в продакшене — PostgreSQL. Всё работает на 100 пользователях. При масштабировании до 10 000 пользователей страница 'История заказов' начинает тормозить — 8 секунд. Команда в панике. Разработчик без знания SQL говорит: 'Нужно добавить кеш (Redis)'. Старший разработчик открывает EXPLAIN ANALYZE на запросе страницы и видит: Seq Scan on orders (cost=0.00..284000.00 rows=2400000). Весь Sequential Scan — 2.4 миллиона строк без индекса. Причина: в SQLite запрос работал быстро — таблица помещалась в памяти. В PostgreSQL с реальными данными — полный перебор. Решение заняло 30 секунд: CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id). Результат: 8000мс → 12мс. Урок: понимание EXPLAIN и архитектуры СУБД превращает многочасовое расследование в 30-секундное исправление.