PostgreSQL: От основ к продвинутой разработке и администрированию

Архитектура PostgreSQL: процессы, управление памятью и физическое хранение данных

Добро пожаловать в курс «PostgreSQL: От основ к продвинутой разработке и администрированию». Мы начинаем наше погружение не с написания простых запросов, а с фундамента — архитектуры. Понимание того, как база данных устроена «под капотом», отличает профессионального инженера данных от новичка. Это знание поможет вам в будущем оптимизировать сложные запросы, настраивать сервер и решать проблемы производительности.

PostgreSQL — это мощная объектно-реляционная система управления базами данных (ORDBMS). Она основана на классической клиент-серверной модели, но имеет свои уникальные особенности реализации, о которых мы поговорим в этой статье.

Процессная модель PostgreSQL

Первое, что нужно усвоить: PostgreSQL — это система, основанная на процессах, а не на потоках (threads). Это важное отличие от некоторых других СУБД (например, MySQL или MS SQL Server).

Когда вы запускаете PostgreSQL, на сервере операционной системы стартует несколько взаимодействующих процессов. Давайте разберем их иерархию.

!Общая схема взаимодействия процессов, памяти и диска в PostgreSQL

1. Postmaster (Main Process)

Это главный процесс, который запускается первым. Его задачи: * Загрузка конфигурационных файлов (postgresql.conf, pg_hba.conf). * Выделение общей памяти (Shared Memory). * Прослушивание порта (по умолчанию 5432) в ожидании входящих подключений. * Запуск фоновых процессов.

Когда клиент (ваше приложение или psql) пытается подключиться к базе данных, он обращается именно к Postmaster.

2. Backend процессы (Server Processes)

Как только Postmaster одобряет подключение клиента, он «порождает» (используя системный вызов fork) новый отдельный процесс — Backend process (часто называемый просто postgres).

> Один клиент = Один Backend процесс.

Этот процесс обслуживает только одного клиента и живет до тех пор, пока клиент не отключится. Он принимает SQL-запросы, компилирует их, выполняет и возвращает результат. Изоляция процессов обеспечивает стабильность: если один Backend процесс упадет из-за ошибки, это не обрушит весь сервер.

3. Фоновые процессы (Background Workers)

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

* Background Writer: Периодически сбрасывает «грязные» (измененные) страницы данных из оперативной памяти на диск, чтобы освободить место для новых данных. * Checkpointer: Отвечает за создание контрольных точек (checkpoints). Это момент, когда гарантируется, что все данные из памяти физически записаны в файлы данных. * Autovacuum Launcher: Запускает процессы очистки (vacuum), которые удаляют старые версии строк и обновляют статистику для планировщика запросов. * WAL Writer: Записывает данные из буфера журнала предзаписи (Write-Ahead Log) на диск. Это критически важно для надежности данных.

Управление памятью

Память в PostgreSQL делится на две большие категории: общая память (Shared Memory) и локальная память (Local Memory).

Общая память (Shared Memory)

Эта область памяти выделяется при старте сервера и доступна всем процессам (Backend и фоновым). Она используется для кэширования данных и координации работы.

Shared Buffers (Общие буферы): Самая важная часть. Здесь хранятся страницы таблиц и индексов. Когда вы делаете SELECT, PostgreSQL сначала ищет данные здесь. Если их нет, он читает их с диска и помещает в Shared Buffers.

WAL Buffers: Буфер для журнала транзакций. Все изменения сначала записываются сюда, а затем сбрасываются на диск.

Локальная память (Local Memory)

Эта память выделяется индивидуально для каждого Backend процесса. Она используется для выполнения конкретных операций запроса.

* work_mem: Память для операций сортировки (ORDER BY), хеширования (HASH JOIN) и группировки. Если запросу нужно больше памяти, чем указано в work_mem, PostgreSQL начинает использовать временные файлы на диске, что резко снижает производительность. * maintenance_work_mem: Память для операций обслуживания, таких как создание индексов (CREATE INDEX) или VACUUM. * temp_buffers: Буфер для временных таблиц.

Для грубой оценки максимального потребления памяти можно использовать следующую формулу:

Где: * — максимальное потребление оперативной памяти. * — размер общих буферов (Shared Buffers). * — максимальное количество одновременных подключений (max_connections). * — размер памяти для рабочих операций (work_mem).

Важно понимать, что один сложный запрос может использовать work_mem несколько раз (для каждого узла сортировки или хеширования), поэтому реальное потребление может быть выше.

Физическое хранение данных

Теперь спустимся на уровень файловой системы. Все данные кластера PostgreSQL хранятся в директории, на которую указывает переменная окружения PGDATA (обычно /var/lib/postgresql/data).

Структура файлов

Внутри PGDATA вы найдете множество подпапок: * base/: Здесь лежат базы данных по умолчанию. Каждая база данных — это подпапка с числовым идентификатором (OID). * global/: Общие таблицы для всего кластера (например, список пользователей pg_authid). * pg_wal/: Журналы предзаписи (Write-Ahead Logs). Это «черный ящик» базы данных, куда записываются все транзакции перед тем, как попасть в основные файлы.

Страницы и Кортежи (Pages and Tuples)

PostgreSQL хранит данные внутри файлов блоками фиксированного размера, которые называются страницами (pages). По умолчанию размер страницы составляет 8 КБ.

!Анатомия страницы данных в PostgreSQL

Внутри страницы находятся:

Заголовок (Header): Метаданные страницы.

Указатели (Item Pointers): Ссылки на физическое расположение строк внутри страницы.

Кортежи (Tuples): Это и есть ваши строки данных. В PostgreSQL строки называются кортежами (или версиями строк).

Почему «версии строк»? PostgreSQL использует модель MVCC (Multi-Version Concurrency Control). Когда вы обновляете строку (UPDATE), старая версия не удаляется и не перезаписывается. Вместо этого создается новая версия строки в той же или другой странице, а старая помечается как «мертвая». Это позволяет читающим транзакциям видеть старые данные, пока пишущая транзакция еще не завершилась, обеспечивая высокую конкурентность без блокировок чтения.

TOAST

Если вы попытаетесь сохранить в поле очень большой текст или JSON, который не помещается в одну страницу (8 КБ), PostgreSQL использует механизм TOAST (The Oversized-Attribute Storage Technique). Большие данные нарезаются на кусочки, сжимаются и сохраняются в отдельной системной таблице, а в основной таблице остается только ссылка на них.

Write-Ahead Logging (WAL)

Это один из самых важных концептов для обеспечения надежности. Идея проста: любое изменение сначала записывается в журнал (WAL), и только потом применяется к файлам данных.

Если сервер внезапно обесточится, данные в оперативной памяти (Shared Buffers) пропадут. Но при следующем запуске PostgreSQL прочитает WAL-файлы и «проиграет» все транзакции, которые были подтверждены, но не успели записаться в файлы данных. Это гарантирует целостность базы данных.

Резюме

В этой статье мы разобрали три кита архитектуры PostgreSQL:

Процессы: Postmaster управляет всем, для каждого клиента создается свой процесс postgres, а фоновые процессы обслуживают систему.

Память: Shared Buffers ускоряют работу всех, а work_mem используется локально для сложных запросов.

Хранение: Данные лежат в страницах по 8 КБ, используется MVCC, а надежность гарантируется механизмом WAL.

В следующей части курса мы перейдем к установке PostgreSQL и настройке рабочего окружения, чтобы увидеть все эти компоненты в действии.

Продвинутый SQL: оконные функции, CTE и работа с типом данных JSONB

В предыдущей статье мы погрузились в недра PostgreSQL, изучив процессы, память и физическое хранение данных. Теперь, понимая, как база данных работает «под капотом», пришло время научиться извлекать из неё данные максимально эффективно и элегантно.

Многие разработчики останавливаются на базовом знании SQL: SELECT, JOIN, GROUP BY. Однако PostgreSQL предлагает мощный инструментарий, который позволяет перенести сложную логику обработки данных из кода приложения (Python, Java, Go) непосредственно в базу данных. Это не только сокращает объем передаваемых данных, но и зачастую работает значительно быстрее.

В этой статье мы разберем три столпа продвинутого SQL: CTE (Common Table Expressions), оконные функции и работу с полуструктурированными данными через JSONB.

Common Table Expressions (CTE)

Часто SQL-запросы превращаются в нечитаемую «лапшу» из вложенных подзапросов. Представьте, что вам нужно найти отделы, где средняя зарплата выше средней по компании, а затем выбрать из них топ-5 сотрудников.

Использование классических подзапросов делает код сложным для восприятия и отладки. Здесь на помощь приходят CTE (Обобщенные табличные выражения), которые вводятся ключевым словом WITH.

CTE — это, по сути, именованный временный результат, который существует только во время выполнения одного запроса. Вы можете думать о нем как о временной таблице или переменной, содержащей выборку данных.

Простой пример CTE

В этом примере мы сначала посчитали продажи по регионам (regional_sales), затем отфильтровали лучшие регионы (top_regions) и только потом использовали эти данные в основном запросе. Читаемость такого кода на порядок выше, чем у вложенных конструкций.

Рекурсивные CTE

Это одна из самых мощных возможностей PostgreSQL. Рекурсивные CTE позволяют работать с иерархическими данными (деревьями, графами), хранящимися в плоских таблицах. Классический пример: структура подчинения сотрудников или категории товаров.

!Визуализация древовидной структуры организации и её представление в таблице базы данных

Синтаксис рекурсивного запроса состоит из двух частей, объединенных UNION ALL:

Нерекурсивная часть (Anchor member): Стартовая точка (например, найти директора).

Рекурсивная часть (Recursive member): Запрос, который ссылается на само CTE.

PostgreSQL выполняет этот запрос итеративно, пока рекурсивная часть не перестанет возвращать новые строки.

Оконные функции (Window Functions)

Оконные функции — это «магия» аналитики в SQL. Они позволяют выполнять вычисления по набору строк, связанных с текущей строкой, не группируя их в одну строку вывода.

В отличие от GROUP BY, который схлопывает строки, оконные функции сохраняют исходное количество строк, но добавляют к каждой строке вычисленное значение (например, сумму, ранг или скользящее среднее).

!Сравнение работы агрегации через GROUP BY и оконные функции

Ключевое слово для оконных функций — OVER.

Синтаксис

* PARTITION BY: Разделяет данные на группы (окна). Вычисления происходят независимо внутри каждой группы. * ORDER BY: Определяет порядок строк внутри окна (важно для ранжирования и функций смещения).

Популярные функции

ROW_NUMBER(): Просто нумерует строки внутри окна.

RANK() и DENSE_RANK(): Присваивают ранг. Разница в том, как они обрабатывают одинаковые значения. Если два участника делят 1-е место, RANK даст следующему 3-е место, а DENSE_RANK — 2-е.

LAG() и LEAD(): Позволяют заглянуть в предыдущую или следующую строку. Незаменимы для поиска изменений (например, разница продаж с предыдущим днем).

Пример использования

Допустим, мы хотим узнать зарплату сотрудника, среднюю зарплату в его отделе и ранг сотрудника по зарплате внутри отдела.

В этом запросе PARTITION BY depname означает, что среднее и ранг считаются отдельно для каждого отдела. При этом мы видим каждого сотрудника отдельной строкой.

JSONB: PostgreSQL как NoSQL

Одной из причин огромной популярности PostgreSQL является его универсальность. Благодаря типу данных JSONB, Postgres может эффективно работать как документная база данных (подобно MongoDB), сохраняя при этом все преимущества реляционной СУБД (ACID-транзакции, JOIN-ы).

JSON vs JSONB

В PostgreSQL есть два типа данных для JSON:

json: Хранит данные как обычный текст. При каждом обращении база данных заново парсит строку. Сохраняет форматирование и дубликаты ключей.

jsonb (binary): Хранит данные в декомпозированном двоичном формате. Это немного замедляет вставку (из-за конвертации), но кардинально ускоряет чтение и обработку. Поддерживает индексацию.

> Практический совет: В 99% случаев используйте jsonb, если вам не нужно сохранять исходные пробелы и порядок ключей «как было прислано».

Основные операторы

Работа с JSONB требует знания специальных операторов:

* -> : Возвращает поле JSON как объект JSON (можно продолжать цепочку). * ->> : Возвращает поле JSON как текст. * @> : Проверяет, содержит ли левый JSON правый JSON (оператор вхождения).

Пример работы

Создадим таблицу с логами событий:

Теперь выберем всех пользователей, которые совершили клик:

Индексация JSONB

Главная мощь JSONB раскрывается с GIN-индексами (Generalized Inverted Index). Они позволяют искать по любым ключам внутри JSON-документа мгновенно.

После создания индекса запрос вида `WHERE data @> '{

Оптимизация производительности: типы индексов и анализ планов выполнения запросов (EXPLAIN)

В предыдущих статьях мы разобрали архитектуру PostgreSQL и научились писать сложные запросы с использованием CTE и JSONB. Однако, по мере роста объема данных, даже самый элегантный SQL-запрос может стать причиной «тормозов» всего приложения.

Написание запроса — это только половина дела. Вторая половина — заставить его работать быстро. В этой статье мы переходим к инженерной части работы с базами данных: пониманию того, как PostgreSQL планирует выполнение запросов, и как мы можем помочь ему, используя правильные индексы.

Планировщик запросов (Query Planner)

SQL — это декларативный язык. Вы говорите базе данных что вы хотите получить (SELECT ), но не говорите как* это сделать. Решение о том, как именно добыть данные, принимает компонент, называемый планировщиком (Query Planner или Optimizer).

Планировщик оценивает множество возможных путей выполнения запроса и выбирает тот, который, по его мнению, будет самым дешевым. Эта «стоимость» (cost) — безразмерная величина, которая отражает нагрузку на процессор и дисковую подсистему.

EXPLAIN: Рентген для запроса

Чтобы увидеть, что происходит «под капотом», используется команда EXPLAIN. Она показывает план выполнения, не запуская сам запрос.

Вывод может выглядеть так:

Разберем ключевые метрики:

cost=0.28..8.29: Это оценка стоимости.

* Первое число (0.28) — startup cost. Стоимость подготовки к началу выдачи первой строки (например, время на загрузку узла индекса). * Второе число (8.29) — total cost. Общая стоимость получения всех строк.

rows=1: Ожидаемое количество строк, которые вернет этот узел плана.

width=128: Ожидаемый средний размер одной строки в байтах.

Для более точного анализа используется EXPLAIN ANALYZE. Эта команда реально выполняет запрос и показывает не только ожидаемое, но и фактическое время выполнения.

> Важно: Никогда не запускайте EXPLAIN ANALYZE для запросов DELETE или UPDATE на продуктивной базе без транзакции, которая будет отменена (ROLLBACK), иначе вы реально измените данные.

Математика стоимости

Как планировщик считает эти цифры? Он использует статистику распределения данных и настроечные константы. Упрощенная формула стоимости последовательного сканирования выглядит так:

Где: * — общая стоимость операции. * — количество страниц данных на диске, которые нужно прочитать. * — стоимость последовательного чтения одной страницы (по умолчанию 1.0). * — количество обрабатываемых строк. * — стоимость обработки одной строки процессором (по умолчанию 0.01).

Понимая эту формулу, становится ясно: чтобы ускорить запрос, нужно либо читать меньше страниц (используя индексы), либо обрабатывать меньше строк.

Типы сканирования (Scan Types)

В планах EXPLAIN вы часто будете встречать следующие методы доступа к данным:

1. Sequential Scan (Seq Scan)

Это полное сканирование таблицы. PostgreSQL читает страницу за страницей от начала до конца.

* Когда это хорошо: Если таблица очень маленькая или если вам нужно прочитать большую часть таблицы (например, для отчета по всем пользователям). * Когда это плохо: Если вам нужна одна строка из миллиона. Это классический случай отсутствия индекса.

2. Index Scan

Поиск данных с использованием индекса. Сначала база данных ищет ключ в индексе, получает ссылку на физическое расположение строки в таблице (TID — Tuple ID), а затем идет в таблицу за данными.

!Визуализация процесса Index Scan: сначала поиск в индексе, затем переход к данным в таблице (Heap).

3. Index Only Scan

Самый быстрый вариант. Если все запрашиваемые колонки уже содержатся в самом индексе, PostgreSQL даже не обращается к основной таблице (Heap).

Пример: SELECT id FROM users WHERE id > 1000. Если есть индекс по id, данные будут взяты прямо из него.

4. Bitmap Heap Scan

Гибридный метод. Используется, когда индекс возвращает много строк, но не настолько много, чтобы делать Seq Scan.

Bitmap Index Scan: Сначала сканируется индекс и строится битовая карта (bitmap) страниц, где лежат нужные строки.

Bitmap Heap Scan: Затем страницы читаются с диска в оптимальном порядке, используя эту карту.

Типы индексов в PostgreSQL

PostgreSQL поддерживает множество типов индексов. Выбор правильного типа критически важен для производительности.

B-Tree (Сбалансированное дерево)

Это индекс «по умолчанию». Если вы пишете CREATE INDEX, создается именно B-Tree. Он идеально подходит для большинства задач.

Поддерживает операции: * Равенство (=). * Диапазоны (<, <=, >, >=). * Сортировка (ORDER BY). * Поиск по префиксу строки (LIKE 'foo%').

Структура B-Tree гарантирует, что путь от корня до любого листа имеет одинаковую длину, что обеспечивает предсказуемое время поиска.

Где: * — время поиска. * — степень ветвления дерева (сколько потомков у узла). * — количество записей в индексе.

Hash

Хеш-индекс хранит 32-битный хеш-код значения.

* Плюсы: Может быть быстрее B-Tree для операций равенства (=). Занимает меньше места. * Минусы: Не поддерживает диапазоны и сортировку. Нельзя использовать для UNIQUE ограничений (до версии 10). * Применение: Редко, только для очень специфичных случаев проверки на точное совпадение.

GIN (Generalized Inverted Index)

Этот тип индекса мы упоминали в прошлой статье про JSONB. GIN — это «обобщенный обратный индекс». Он создан для типов данных, которые содержат множество значений внутри одной ячейки (массивы, JSONB, текстовый поиск tsvector).

Вместо того чтобы индексировать строку целиком, GIN индексирует каждый отдельный элемент внутри неё.

Пример использования:

GiST (Generalized Search Tree)

Гибкая структура для сложных типов данных. Чаще всего используется в PostGIS (геоинформационные системы) для поиска точек на карте, пересечений полигонов и поиска ближайших соседей.

BRIN (Block Range INdex)

Представьте, что у вас есть таблица на 10 терабайт с логами, где записи идут строго по времени. B-Tree индекс для такой таблицы будет огромным и тяжелым.

BRIN — это очень компактный индекс. Он хранит не ссылки на строки, а минимальное и максимальное значение для группы страниц (блоков).

* Принцип: «В блоках с 1 по 100 даты лежат в диапазоне от 01.01.2023 до 02.01.2023». Если вы ищете дату за 2024 год, PostgreSQL сразу пропустит эти блоки. * Размер: В сотни раз меньше B-Tree.

Стратегия оптимизации

Когда вы сталкиваетесь с медленным запросом, следуйте алгоритму:

Запустите EXPLAIN ANALYZE. Посмотрите, где реальное время (actual time) сильно отличается от ожидаемого или просто велико.

Ищите Seq Scan на больших таблицах. Это первый кандидат на создание индекса.

Проверьте условия WHERE.

* Если вы используете функции на колонке (например, WHERE lower(email) = '...'), обычный индекс не сработает. Нужен функциональный индекс: CREATE INDEX ON users (lower(email)). * Если вы используете LIKE '%text%' (с процентом в начале), B-Tree индекс бесполезен. Рассмотрите триграммные индексы (расширение pg_trgm).

Проверьте кардинальность. Индекс по колонке gender (где всего 2 значения: 'M' и 'F') в таблице на миллион строк, скорее всего, не будет использоваться, так как проще прочитать всю таблицу, чем прыгать по индексу за 50% записей.

Резюме

Оптимизация в PostgreSQL — это баланс. Индексы ускоряют чтение (SELECT), но замедляют запись (INSERT, UPDATE, DELETE), так как при каждом изменении данных нужно обновлять и индексы.

* Используйте B-Tree для 90% задач (числа, даты, строки). * Используйте GIN для JSONB и массивов. * Используйте EXPLAIN для диагностики проблем. * Помните формулу стоимости: цель — сократить количество читаемых страниц ().

В следующей части курса мы рассмотрим транзакции, уровни изоляции и блокировки — механизмы, которые обеспечивают целостность данных при одновременной работе множества пользователей.

Управление конкурентным доступом: транзакции, уровни изоляции и механизм MVCC

В предыдущей статье мы научились ускорять запросы с помощью индексов и анализа EXPLAIN. Теперь представьте ситуацию: ваш идеально оптимизированный запрос работает быстро, но в это же время тысяча пользователей пытаются купить последний билет на концерт. Как база данных гарантирует, что билет достанется только одному, а не будет продан десять раз? Как сделать так, чтобы отчет бухгалтерии сходился, даже если во время его формирования идут активные продажи?

Сегодня мы поговорим о «сердце» надежности PostgreSQL — системе управления конкурентным доступом. Мы разберем концепцию ACID, магию MVCC и уровни изоляции транзакций.

Что такое транзакция и ACID?

Транзакция — это логическая единица работы с базой данных. Это может быть один SQL-запрос или последовательность из сотни команд, которые должны быть выполнены как единое целое.

Классический пример: банковский перевод. Чтобы перевести 100 рублей от Алисы к Бобу, нужно:

Вычесть 100 рублей со счета Алисы.

Добавить 100 рублей на счет Боба.

Если свет погаснет после первого шага, деньги исчезнут. Чтобы этого не случилось, транзакции в PostgreSQL подчиняются требованиям ACID:

* A — Atomicity (Атомарность): Всё или ничего. Либо выполняются все команды транзакции, либо ни одной. Промежуточные состояния не сохраняются. * C — Consistency (Согласованность): Транзакция переводит базу данных из одного корректного состояния в другое, соблюдая все ограничения (constraints, foreign keys). * I — Isolation (Изолированность): Параллельные транзакции не должны мешать друг другу (об этом мы поговорим подробно). * D — Durability (Долговечность): Если система сказала «ОК» (Commit), данные сохранены надежно, даже если через секунду сервер сгорит. За это отвечает журнал WAL, который мы обсуждали в первой статье.

В SQL управление транзакциями осуществляется командами: * BEGIN — начать транзакцию. * COMMIT — зафиксировать изменения. * ROLLBACK — отменить все изменения с момента BEGIN.

Проблемы параллельного доступа

Когда множество клиентов читают и пишут данные одновременно, возникают специфические эффекты (аномалии). Стандарт SQL описывает несколько основных проблем:

Грязное чтение (Dirty Read): Вы видите данные, добавленные другой транзакцией, которая еще не зафиксировалась (не сделала COMMIT). Если та транзакция сделает ROLLBACK, окажется, что вы прочитали данные, которых никогда не существовало.

Неповторяющееся чтение (Non-repeatable Read): Вы делаете SELECT и видите, что у Боба 100 рублей. В это время другая транзакция меняет баланс Боба и делает COMMIT. Вы снова делаете тот же SELECT внутри своей транзакции и видите уже 200 рублей. Данные изменились прямо у вас под ногами.

Фантомное чтение (Phantom Read): Вы выбираете всех пользователей из города «Москва» и получаете 5 строк. Другая транзакция добавляет нового москвича и делает COMMIT. Вы повторяете выборку и получаете 6 строк. Сами строки не менялись, но появилась новая — «фантом».

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

Чтобы бороться с этими аномалиями, SQL предлагает 4 уровня изоляции. Чем выше уровень, тем строже изоляция, но тем ниже производительность (обычно).

Посмотрим, как это реализовано в PostgreSQL:

| Уровень изоляции | Dirty Read | Non-repeatable Read | Phantom Read | | :--- | :---: | :---: | :---: | | Read Uncommitted | Невозможно в PG | Возможен | Возможен | | Read Committed (Default) | Невозможно | Возможен | Возможен | | Repeatable Read | Невозможно | Невозможно | Невозможно в PG | | Serializable | Невозможно | Невозможно | Невозможно |

Особенности PostgreSQL

Read Uncommitted: В стандарте SQL этот уровень допускает грязное чтение. Но в PostgreSQL архитектура устроена так, что грязное чтение реализовать сложнее, чем не реализовать. Поэтому, даже если вы запросите этот уровень, Postgres будет работать как Read Committed.

Read Committed: Это уровень по умолчанию. Каждый запрос внутри транзакции видит снимок данных, актуальный на момент начала этого запроса.

Repeatable Read: Вся транзакция видит снимок данных, актуальный на момент выполнения первого запроса в этой транзакции. В PostgreSQL этот уровень также защищает от фантомного чтения (в отличие от стандарта SQL).

Serializable: Самый строгий уровень. Эмулирует последовательное выполнение транзакций. Если система обнаруживает, что параллельное выполнение может привести к результату, невозможному при последовательном выполнении, одна из транзакций будет прервана с ошибкой serialization failure.

MVCC: Как это работает под капотом

Как PostgreSQL удается обеспечивать изоляцию, не блокируя всю таблицу при каждом чтении? Ответ кроется в аббревиатуре MVCC (Multi-Version Concurrency Control — Многоверсионное управление конкурентным доступом).

Главное правило MVCC: > Пишущие не блокируют читающих, а читающие не блокируют пишущих.

В первой статье мы говорили, что при UPDATE старая строка не удаляется, а создается новая версия. Теперь мы поймем зачем.

!Иллюстрация того, как разные транзакции видят разные версии одной и той же строки благодаря механизму MVCC.

Системные столбцы xmin и xmax

У каждой строки (кортежа) в PostgreSQL есть скрытые служебные поля: * xmin: ID транзакции, которая создала эту версию строки. * xmax: ID транзакции, которая удалила (или обновила) эту версию строки.

Когда вы выполняете SELECT, PostgreSQL проверяет видимость каждой строки для вашей текущей транзакции. Упрощенно, логику видимости можно описать следующей формулой:

Где: * — видимость строки (истина или ложь). * — идентификатор транзакции, создавшей строку. * — идентификатор вашей текущей транзакции (или снимка данных). * — идентификатор транзакции, удалившей строку.

Пояснение: Строка видна вам, если она была создана транзакцией, которая уже завершилась до начала вашей (), И при этом она либо не удалена вовсе (), либо удалена транзакцией, которую вы еще «не видите» ().

Именно благодаря этому механизму вы можете делать долгий отчет по таблице, пока другие пользователи активно меняют в ней данные. Вы будете работать со «снимком» (Snapshot) данных на момент начала вашего запроса.

Блокировки (Locks)

Хотя MVCC позволяет избегать блокировок при чтении, запись данных всегда требует эксклюзивного доступа к конкретной строке. Если две транзакции попытаются одновременно обновить одну и ту же строку:

Первая транзакция захватывает блокировку строки (Row-Level Lock).

Вторая транзакция пытается обновить строку, видит блокировку и «засыпает» (ждет).

Когда первая транзакция делает COMMIT или ROLLBACK, вторая просыпается и продолжает работу.

Явные блокировки

Иногда вам нужно заблокировать строки при чтении, чтобы никто не изменил их до того, как вы закончите обработку. Для этого используется конструкция SELECT ... FOR UPDATE.

Взаимоблокировки (Deadlocks)

С блокировками связана классическая проблема — Deadlock.

Представьте: * Транзакция А заблокировала строку 1 и хочет строку 2. * Транзакция Б заблокировала строку 2 и хочет строку 1.

Они будут ждать друг друга вечно. К счастью, в PostgreSQL есть механизм обнаружения взаимоблокировок. Спустя определенное время (по умолчанию 1 секунда, параметр deadlock_timeout), система поймет, что произошел клинч, и принудительно прервет одну из транзакций, чтобы дать другой завершиться.

Резюме

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

Транзакции обеспечивают целостность данных через принципы ACID.

Уровни изоляции позволяют балансировать между точностью данных и производительностью. По умолчанию PostgreSQL использует Read Committed.

MVCC — это киллер-фича PostgreSQL, позволяющая читателям не блокировать писателей. Это достигается за счет хранения нескольких версий строк и проверки полей xmin / xmax.

Блокировки необходимы для изменения данных, но могут приводить к ожиданиям и взаимоблокировкам.

В следующей части курса мы перейдем к теме, которая часто пугает новичков, но необходима для создания надежных систем — резервное копирование и репликация.

Основы администрирования: настройка конфигурации, резервное копирование и обслуживание (VACUUM)

Мы прошли долгий путь: изучили процессы и память, научились писать сложные запросы с CTE и JSONB, оптимизировали их с помощью индексов и разобрались, как транзакции изолируют пользователей друг от друга. Теперь настало время надеть шляпу администратора базы данных (DBA).

Даже самый совершенный двигатель требует замены масла и настройки. PostgreSQL — не исключение. В этой статье мы разберем три кита, на которых держится здоровье вашего кластера: конфигурация, обслуживание (очистка) и резервное копирование.

Настройка конфигурации (postgresql.conf)

«Из коробки» PostgreSQL настроен очень консервативно. Это сделано для того, чтобы сервер мог запуститься практически на любом, даже самом слабом оборудовании. Однако для продуктивной среды стандартные настройки, скорее всего, не подойдут.

Главный конфигурационный файл называется postgresql.conf. Он находится в директории данных (PGDATA), о которой мы говорили в первой статье.

Ключевые параметры памяти

Вспомним архитектуру памяти из первой лекции. У нас есть общая память и локальная память процессов. Вот как их настраивать:

shared_buffers

Это объем памяти, выделяемый серверу для кэширования данных. Если данных нет в этом буфере, Postgres лезет на диск. Рекомендация:* Обычно устанавливают в 25% от всей оперативной памяти (RAM) сервера. Ставить больше 40% часто не имеет смысла, так как PostgreSQL также полагается на кэш файловой системы ОС.

work_mem

Лимит памяти для одной операции сортировки или хеширования внутри одного запроса. Важно:* Это не общий лимит! Если у вас 100 подключений, и каждое выполняет запрос с 3 сортировками, то потребление будет: . Риск:* Слишком высокое значение может привести к ошибке «Out of Memory» и падению сервера.

maintenance_work_mem

Память для операций обслуживания (создание индексов, VACUUM, добавление внешних ключей). Рекомендация:* Можно ставить значительно больше, чем work_mem (например, 1 ГБ), так как эти операции выполняются редко и не параллельно в большом количестве.

effective_cache_size

Этот параметр не выделяет память. Это «подсказка» планировщику запросов о том, сколько памяти доступно в системе для кэширования (включая кэш ОС). Влияние:* Если значение высокое, планировщик будет охотнее использовать индексы, считая, что они, скорее всего, уже в памяти.

Применение настроек

Не все параметры применяются одинаково: * Reload: Большинство параметров можно применить без перезагрузки сервера (команда SELECT pg_reload_conf(); или pg_ctl reload). * Restart: Параметры, влияющие на выделение памяти или процессы (например, shared_buffers), требуют полной перезагрузки службы PostgreSQL.

Обслуживание: Проблема мертвых кортежей и VACUUM

В статье про транзакции мы обсуждали механизм MVCC. Когда вы делаете UPDATE или DELETE, старая версия строки не удаляется физически, а лишь помечается как «мертвая» (dead tuple).

Если эти мертвые строки не убирать, таблица раздувается (bloat). Файл на диске растет, индексы становятся огромными, а SELECT * начинает читать гигабайты мусора, чтобы найти пару живых строк.

!Визуализация процесса очистки страницы от мертвых кортежей и освобождения места для новых записей.

VACUUM (Обычная очистка)

Команда VACUUM проходит по таблицам и помечает место, занятое мертвыми кортежами, как свободное.

Что делает: Освобождает место внутри* файлов для повторного использования будущими вставками. * Чего НЕ делает: Обычно не возвращает место операционной системе (размер файла не уменьшается). * Блокировки: Работает параллельно с чтением и записью. Не блокирует таблицу.

VACUUM FULL

Это «ядерное оружие» администратора.

* Что делает: Полностью переписывает таблицу в новый файл, удаляя весь мусор и уплотняя данные. Размер файла на диске уменьшается до минимума. * Цена: Требует Exclusive Lock (полная блокировка). Никто не сможет даже читать таблицу во время работы.

> Практический совет: Избегайте VACUUM FULL на работающих продуктовых базах. Это операция для технологических окон.

Autovacuum Daemon

Вам не нужно запускать VACUUM вручную каждый день. В PostgreSQL есть фоновый процесс Autovacuum, который делает это автоматически.

Он просыпается, проверяет статистику и запускает очистку, если количество мертвых кортежей превысило порог. Формула срабатывания выглядит так:

Где: * — порог количества мертвых кортежей, при котором запустится автовакуум. * — базовый порог (параметр autovacuum_vacuum_threshold, обычно 50 строк). * — коэффициент масштабирования (параметр autovacuum_vacuum_scale_factor, обычно 0.2 или 20%). * — общее количество живых кортежей в таблице.

Это означает, что по умолчанию очистка начнется, когда изменится 20% таблицы. Для огромных таблиц (миллиарды строк) 20% — это слишком много мусора, поэтому параметр autovacuum_vacuum_scale_factor для них часто уменьшают.

Transaction ID Wraparound

У PostgreSQL есть архитектурная особенность: счетчик транзакций (XID) — это 32-битное число (около 4 миллиардов значений). Если счетчик переполнится и пойдет по кругу, старые транзакции могут внезапно показаться «будущими», и данные станут невидимыми. Это называется Wraparound.

Autovacuum автоматически предотвращает это, запуская специальный режим «заморозки» (freezing) старых транзакций. Никогда не отключайте Autovacuum полностью!

Резервное копирование (Backup)

Даже идеально настроенный сервер не застрахован от rm -rf / или сбоя диска. Резервные копии делятся на два основных типа: логические и физические.

!Сравнение логического (SQL-команды) и физического (копии файлов) резервного копирования.

1. Логическое резервное копирование (pg_dump)

Утилита pg_dump создает текстовый файл с SQL-командами, необходимыми для воссоздания базы данных.

* Плюсы: * Можно восстановить на другой операционной системе или даже другой архитектуре процессора. * Можно восстановить на более новой версии PostgreSQL (удобно для обновлений). * Можно бэкапить отдельные таблицы. * Минусы: * Медленное восстановление (базе нужно заново выполнить все INSERT и построить индексы).

Пример создания бэкапа:

Пример восстановления:

2. Физическое резервное копирование (pg_basebackup)

Это копирование самих файлов базы данных на бинарном уровне.

* Плюсы: * Очень быстрое восстановление (просто копируем файлы обратно). * Позволяет реализовать PITR (Point-in-Time Recovery). * Минусы: * Бэкап можно развернуть только на той же мажорной версии PostgreSQL и той же архитектуре ОС.

Point-in-Time Recovery (PITR)

Это «машина времени» для базы данных. Представьте, что в 14:00 стажер случайно удалил таблицу users. Если у вас есть ночной физический бэкап и сохранены все WAL-файлы (журналы предзаписи) с ночи до 14:00, вы можете:

Восстановить ночной бэкап.

«Проиграть» WAL-файлы до момента 13:59:59.

Получить базу в состоянии за секунду до аварии.

Для настройки PITR используются параметры archive_mode и archive_command, которые отправляют закрытые WAL-сегменты в надежное хранилище (например, на S3).

Резюме

Администрирование PostgreSQL — это баланс между производительностью и надежностью.

Конфигурация: Не оставляйте настройки по умолчанию. Настройте shared_buffers и work_mem под ваше железо.

VACUUM: Мертвые кортежи — неизбежное зло MVCC. Autovacuum — ваш лучший друг, следите, чтобы он работал и справлялся с нагрузкой.

Бэкапы: pg_dump хорош для миграций и маленьких баз. Для серьезных систем используйте физическую репликацию и PITR.

Теперь, когда мы умеем настраивать и обслуживать один сервер, в следующей (и заключительной) части курса мы поговорим о том, как масштабировать PostgreSQL и обеспечивать высокую доступность с помощью репликации.