Введение в PostgreSQL: Архитектура, ACID и Оптимизация

Оглавление

  1. Реляционные базы данных и ACID
  2. Уровни изоляции транзакций и аномалии
  3. Механизм MVCC (Multiversion Concurrency Control)
  4. Особенности PostgreSQL и архитектура соединений
  5. Нормализация и денормализация данных
  6. Индексы в PostgreSQL
  7. Репликация и высокая доступность (Patroni)

1. Реляционные базы данных и ACID

Основные определения

  • База данных (БД): Упорядоченный набор структурированной информации.
  • СУБД (Система управления базами данных): Программное обеспечение для управления структурой БД и доступом к данным.
  • Реляционная база данных: Модель, предложенная Эдгаром Коддом в 1970 году.
    • Данные хранятся в виде таблиц (отношений).
    • Таблицы состоят из строк (записей) и столбцов (полей).

Концепция ACID

Набор свойств, гарантирующих надежность транзакций (фундаментальная основа реляционных СУБД, таких как PostgreSQL).

  • A — Atomicity (Атомарность):
    • Транзакция — неделимая операция.
    • Выполняется полностью либо не выполняется совсем.
    • При сбое любой операции внутри транзакции происходит откат всех изменений.
  • C — Consistency (Согласованность):
    • Транзакция переводит БД из одного корректного состояния в другое.
    • Все правила и ограничения (constraints) должны быть соблюдены.
  • I — Isolation (Изолированность):
    • Параллельные транзакции не должны влиять друг на друга.
    • Результат одновременного выполнения эквивалентен их последовательному выполнению.
  • D — Durability (Надёжность):
    • После фиксации (коммита) изменения сохраняются даже при сбое системы (например, отключении питания).

2. Уровни изоляции транзакций и аномалии

Изоляция (часть концепции ACID) может быть настроена для баланса между скоростью и строгостью данных.

Виды аномалий параллельного доступа

  • Грязное чтение (Dirty Read): Чтение данных, измененных другой транзакцией, которые еще не были зафиксированы.
  • Неповторяющееся чтение (Non-repeatable Read): Повторное чтение одной строки в транзакции дает разные результаты из-за изменений в другой зафиксированной транзакции.
  • Фантомное чтение (Phantom Read): Повторное чтение диапазона строк возвращает новые строки-“фантомы”, добавленные другой зафиксированной транзакцией.

Стандартные уровни изоляции

  1. Read Uncommitted: Допускает все аномалии.
  2. Read Committed:
    • Защищает от грязного чтения.
    • Видит только зафиксированные данные.
    • Уровень по умолчанию в PostgreSQL.
  3. Repeatable Read:
    • Защищает от грязного и неповторяющегося чтения.
    • Работает со «снимком» данных на момент начала транзакции.
  4. Serializable:
    • Самый строгий уровень, защищает от всех аномалий (включая фантомные чтения).
    • Гарантирует полную последовательность выполнения.

3. Механизм MVCC (Multiversion Concurrency Control)

MVCC — это основной механизм PostgreSQL для обеспечения изоляции (Isolation) без использования блокировок на чтение.

Принципы работы MVCC

  • Версионность строк:
    • При UPDATE или DELETE старая версия строки физически остается в базе, но помечается как неактуальная.
    • Создается новая версия строки.
  • Системные поля строк:
    • xmin: ID транзакции, создавшей версию.
    • xmax: ID транзакции, удалившей или обновившей версию.
  • Снимок (Snapshot):
    • Транзакция видит версию строки, если xmin закоммичен, а xmax либо пуст, либо принадлежит незакоммиченной транзакции на момент создания снимка.

Очистка данных: VACUUM

  • Мертвые строки: Накопившиеся неактуальные версии строк, невидимые для активных транзакций.
  • Горизонт событий: ID самой старой активной транзакции (xmin). Все версии строк старше горизонта считаются устаревшими.
  • VACUUM: Процесс “сборки мусора”, который удаляет мертвые строки и освобождает место.

Типы нагрузки и MVCC

  • OLTP (Online Transaction Processing): Короткие, частые транзакции (банковские операции). Идеальны для MVCC.
  • OLAP (Online Analytical Processing): Сложные аналитические запросы.
    • Проблема: Длительные OLAP-запросы удерживают горизонт событий, мешая VACUUM очищать данные.

4. Особенности PostgreSQL и архитектура соединений

Ключевые характеристики

  • История: Проект с открытым кодом, развиваемый в Беркли.
  • Типы данных: UUID, JSON/JSONB, геопространственные типы, массивы.
  • Расширяемость: Модули (например, TimescaleDB для временных рядов).
  • Блокировки: Сложная иерархия на уровнях таблиц и строк.

Архитектура процессов и пулеры

  • Модель “Процесс на соединение”: PostgreSQL создает отдельный backend-процесс для каждого подключения. Это ресурсоемко.
  • Пулер соединений (Connection Pooler): Прокси-сервер для переиспользования постоянных соединений с БД.
  • Популярные решения:
    • PgBouncer: Легкий, однопоточный.
    • Odyssey: Многопоточный (от Яндекса).
    • PgCat: Open-source, поддержка балансировки и шардирования.
    • PgDoorman: Решение от Ozon Tech на базе PgCat.

5. Нормализация и денормализация данных

Методы организации данных для соблюдения Consistency (Согласованности) из ACID.

Нормальные формы (НФ)

  • 1НФ: Атрибуты атомарны, нет повторяющихся групп.
  • 2НФ: Соблюдена 1НФ, все неключевые атрибуты зависят от всего составного первичного ключа.
  • 3НФ: Соблюдена 2НФ, нет транзитивных зависимостей (неключевые атрибуты зависят только от ключа).

Сравнение подходов

Характеристика Нормализация Денормализация
Цель Устранение избыточности Ускорение чтения
Согласованность Повышает Понижает
Скорость записи Ускоряет Замедляет
Скорость чтения Замедляет (нужны JOIN) Ускоряет (избегает JOIN)

Примечание: Денормализацию следует использовать только при наличии явных проблем с производительностью и редком редактировании данных.

6. Индексы в PostgreSQL

Индексы — структуры для быстрого поиска строк (аналог оглавления книги).

Влияние на производительность

  • Плюс: Значительное ускорение SELECT с условием WHERE.
  • Минус: Замедление INSERT, UPDATE, DELETE (требуется обновление индекса).

Виды индексов

  • B-Tree: Стандартный индекс. Операции: <, >, =, BETWEEN.
  • Hash: Только для точного равенства (=).
  • GiST / SP-GiST: Геоданные (поиск ближайших точек), префиксный поиск.
  • GIN (Generalized Inverted Index): Массивы, JSONB, полнотекстовый поиск.
  • BRIN (Block Range Index): Для огромных таблиц, упорядоченных физически (например, по дате).

Специальные возможности

  • Индексы на выражения: По результату функции, например lower(email).
  • Фильтрованные (частичные) индексы: Только для строк, подходящих под условие WHERE status = 'active'.

7. Репликация и высокая доступность (Patroni)

Репликация обеспечивает Durability (Надежность) и масштабируемость системы.

Задачи репликации

  1. Надежность: Резервное копирование.
  2. High Availability (HA): Быстрое переключение при отказе мастера.
  3. Производительность: Распределение нагрузки на чтение.

Виды репликации в PostgreSQL

  • Синхронная (Sync): Мастер ждет подтверждения от реплики. Нет потери данных, но запись медленнее.
  • Асинхронная (Async): Мастер не ждет реплику. Запись быстрее, но возможна потеря данных при аварии.

Patroni

  • Инструмент (шаблон) для создания отказоустойчивых кластеров.
  • Использует внешние хранилища конфигураций: etcd, Consul.
  • Функции: Автоматическое управление ролями (master/replica) и выполнение failover (автопереключение при сбое).