SQL, PostgreSQL и транзакции

› Базовые определения и архитектура

• База данных (БД): Совокупность данных, хранимых в соответствии со схемой, манипулирование которыми выполняется по строгим правилам.
• СУБД (Система управления базами данных): Программное обеспечение для создания, управления и взаимодействия с базами данных (PostgreSQL, Oracle, MySQL).
• Реляционная БД (RDBMS): Набор данных с предопределенными связями, организованный в виде таблиц (строки и столбцы).
  • Когда использовать: Нужна строгая консистентность (ACID), сложные транзакции, четкая структура данных (финансы, ERP, CRM).
• Нереляционная БД (NoSQL): БД без жесткой табличной схемы (Документоориентированные, Key-Value, Графовые).
  • Когда использовать: Гибкая схема данных, необходимость горизонтального масштабирования, огромный поток на запись/чтение простых данных (логи, кэш, сессии). Примеры: MongoDB, Redis, Cassandra.

Основные сущности взаимодействия

• Connection (Соединение) — физический сетевой канал связи (обычно TCP/IP сокет) между клиентским приложением и сервером базы данных, предназначенный для передачи сырых байтов данных.
• Session (Сессия) — логический контекст состояния на стороне СУБД, инициализируемый после успешной аутентификации клиента через открытое Connection. Хранит параметры окружения (таймзоны, переменные), права доступа, временные таблицы и статус текущих операций.
• Connection Pool (Пул соединений) — программный пул заранее созданных и постоянно поддерживаемых в открытом состоянии физических соединений к БД, переиспользуемых различными потоками приложения для исключения накладных расходов на постоянное установление TCP-соединения и аутентификацию.
• Transaction (Транзакция) — неделимая (атомарная) последовательность одной или нескольких SQL-операций, гарантирующая перевод базы данных из одного целостного состояния в другое в соответствии с принципами ACID.

› Подмножества языка SQL

SQL (Structured Query Language) делится на логические блоки:

1. DDL (Data Definition Language): Управление структурой (CREATE, ALTER, DROP).
2. DML (Data Manipulation Language): Управление данными (SELECT, INSERT, UPDATE, DELETE).
3. DCL (Data Control Language): Управление правами доступа (GRANT, DENY, REVOKE).
4. DTL (Data Transaction Language): Управление транзакциями (COMMIT, ROLLBACK).

› Проектирование и Нормализация

Нормализация — метод проектирования БД для устранения аномалий обновления и избыточности данных (дублирования).

• 1NF (Первая нормальная форма): Нет дублирующихся строк, в каждой ячейке атомарное значение (нет массивов/списков), в столбце данные одного типа.
• 2NF: Находится в 1NF + каждый неключевой столбец зависит от всего первичного ключа.
• 3NF: Находится во 2NF + нет транзитивных зависимостей (неключевые столбцы не зависят от других неключевых столбцов).

› Индексы

Индекс — это специальный объект БД, создаваемый для повышения производительности поиска. Требует накладных расходов памяти и замедляет операции записи (при INSERT/UPDATE индекс нужно перестраивать).

B-Tree

По умолчанию в PostgreSQL для индексов используется B-Tree, потому что:

1. Оно поддерживает не только точный поиск (=), но и поиск по диапазонам (>, <, BETWEEN), а также сортировку (ORDER BY), в отличие от Hash-индексов.
2. Оно оптимизировано для работы с жесткими дисками. Благодаря высокой “ветвистости” (сотни потомков у узла), дерево получается очень плоским (глубина 3-4 уровня даже для миллиардов строк), что минимизирует количество дорогих операций чтения с диска.
3. Листовые узлы связаны в список (B+ Tree), что делает сканирование диапазонов максимально быстрым.

Как рабоатет поиск

1. Парсинг и Планирование: СУБД парсит запрос. Планировщик (Planner) проверяет наличие индексов.
2. Оценка стоимости: Планировщик решает, что выгоднее.
   • Index Scan + Table Access: Поиск указателя в дереве индекса, затем физическое чтение строки из таблицы по этому указателю.
   • Full/Sequential Scan (FS): Полный перебор всех строк таблицы.
3. Золотое правило эффективности: Использование индекса оправдано, только если в выборку попадает менее 10-15% строк таблицы (высокая кардинальность). Если запрос вернет 50% таблицы, планировщик проигнорирует индекс и сделает Full Scan, так как прыгать по указателям для половины таблицы дороже, чем прочитать её целиком. Планировщик оценивает количество возвращаемых строк (Cardinality Estimation) на основе системной статистики (гистограммы, списки самых частых значений, количество уникальных значений). Эту статистику собирает фоновый процесс ANALYZE. Если статистика актуальна, планировщик с высокой точностью знает, превысит ли выборка порог в 10-15%, и на основе этого решает, использовать индекс или делать Full Scan.

Составные индексы (Composite Indexes)

Индекс по нескольким колонкам, например CREATE INDEX idx_a_b ON table(a, b).

• Порядок имеет критическое значение! Индекс (a, b) будет работать для запросов WHERE a = 1 и WHERE a = 1 AND b = 2.
• Он НЕ будет работать для запроса WHERE b = 2 (СУБД придется делать Full Scan). (правило левого префикса)

› Миграции и DDL операции под капотом

Изменение структуры БД (DDL) на “живой” базе (production) — опасный процесс.

• DDL Блокировки: Большинство операций CREATE и ALTER требуют эксклюзивной блокировки таблицы (AccessExclusiveLock).
• Создание индекса: Обычный CREATE INDEX блокирует таблицу для всех DML операций (запись останавливается).
  • Решение: В PostgreSQL используется CREATE INDEX CONCURRENTLY. Он строит индекс в фоновом режиме, не блокируя INSERT/UPDATE/DELETE. Это работает дольше, но система не деградирует.
• Удаление колонок: В PostgreSQL ALTER TABLE DROP COLUMN работает без блокировки данных (просто обновляет метаданные), а в Oracle может вызвать полную деградацию системы.

› Транзакции, Блокировки

Транзакция — логическая единица работы, состоящая из одной или нескольких SQL-команд. Завершается либо фиксацией (COMMIT), либо откатом (ROLLBACK).

Виды блокировок при конкурентном доступе

1. Pessimistic Lock (Пессимистичная блокировка):
   • Блокировка на уровне БД. Запрос SELECT ... FOR UPDATE блокирует выбранные строки. Другие транзакции, пытающиеся прочитать или изменить эти строки, зависают в ожидании.
   • SKIP LOCKED: Позволяет пропустить заблокированные строки и взять следующие свободные (идеально для реализации очередей задач в БД).
2. Optimistic Lock (Оптимистичная блокировка):
   • Блокировка на уровне приложения (без лока в БД). В таблицу добавляется колонка version.
   • При чтении запоминается версия. При UPDATE проверяется: WHERE id = ? AND version = ?. Если обновлено 0 строк (кто-то другой уже изменил версию), выбрасывается исключение (например, OptimisticLockException в Hibernate).
3. Deadlock (Взаимная блокировка):
   • Транзакция А заблокировала Строку 1 и ждет Строку 2. Транзакция Б заблокировала Строку 2 и ждет Строку 1. СУБД обнаруживает это и принудительно убивает одну из транзакций.

Автоматические блокировки (DML Locks)

• Массовые модификации (UPDATE table SET field = value без лимитов) заблокируют все строки таблицы до конца транзакции.
• Правильный подход: Выполнять массовые обновления/удаления в цикле маленькими пачками (батчами), чтобы не парализовать работу БД.

› Уровни изоляции транзаций и аномалии связанные с ними

АНОМАЛИИ (Проблемы параллельного доступа):

1. Dirty Read (Грязное чтение): Чтение данных, которые еще не были зафиксированы другой транзакцией (могут быть откачены).
2. Non-Repeatable Read (Неповторяющееся чтение): В одной транзакции читаем одну и ту же строку дважды и получаем РАЗНЫЕ значения (кто-то успел сделать Update).
3. Phantom Read (Фантомное чтение): В одной транзакции делаем запрос диапазона (напр. поиск по ID > 10) дважды и получаем РАЗНОЕ количество строк (кто-то успел сделать Insert/Delete).
4. Lost Update (Потерянное обновление): Две транзакции одновременно меняют одну строку — изменения первой затираются изменениями второй.

УРОВНИ ИЗОЛЯЦИИ:

1. READ UNCOMMITTED

   • Видит чужие незафиксированные изменения.
   • Допускает все аномалии (кроме потери обновления в некоторых БД).
   • Самый быстрый, но небезопасный.

2. READ COMMITTED (Default в PostgreSQL, Oracle)

   • Видит только зафиксированные изменения.
   • Защищает от: Dirty Read.
   • Проблемы: Non-Repeatable Read, Phantom Read.

3. REPEATABLE READ (Default в MySQL InnoDB)

   • Гарантирует, что повторное чтение одной строки даст тот же результат.
   • Защищает от: Dirty Read, Non-Repeatable Read.
   • Проблемы: Phantom Read (в Postgres защищает и от фантомов на этом уровне).

4. SERIALIZABLE

   • Полная изоляция, транзакции выполняются как будто строго по очереди.
   • Защищает от: Всех аномалий (включая Phantom Read).
   • Самый медленный из-за блокировок или проверок конфликтов.