Как устроен PostgreSQL

1. КЛАСТЕР БАЗ ДАННЫХ, БАЗЫ ДАННЫХ И ТАБЛИЦЫ

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

1.1. Логическая структура кластера баз данных

1.2. Физическая структура кластера баз данных

2. АРХИТЕКТУРА ПРОЦЕССОВ И ПАМЯТИ

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

2.1. Архитектура процессов

2.2. Архитектура памяти

3. ОБРАБОТКА ЗАПРОСОВ

Согласно официальному документу, PostgreSQL поддерживает почти все основные возможности стандарта SQL:2011. Обработка запросов - самая сложная подсистема в PostgreSQL. Данный раздел полностью посвящен вопросу обработки запросов, в частности, оптимизации запросов.

Раздел разделен на три основные части:

Часть 1: Подраздел 3.1. - обзор обработки запросов в PostgreSQL;
Часть 2: Подразделы 3.2. — 3.4 описывают шаги, необходимые для получения оптимального плана выполнения запроса с одной таблицей. Главы 3.2 и 3.3 объясняют процессы оценки стоимости и создания дерева планов. В главе 3.4 кратко описана работа исполнителя;
Часть 3: Подразделы 3.5. — 3.6. описывают процесс создания оптимального плана запроса с несколькими таблицами. В главе 3.5 описаны три метода соединения: вложенный цикл (nested loop), слияние (merge join) и хэш-соединение (hash join). В разделе 3.6 описывается процесс создания дерева плана многотабличного запроса.

PostgreSQL поддерживает три технически интересные возможности: Foreign Data Wrappers (FDW), Параллельные запросы и JIT-компиляцию, доступную в версии 11 и новее. Первая из них подробно описана в разделе 4. Параллельный запрос и JIT-компиляция выходят за рамки данного документа.

3.1. Обзор

3.2. Оценка стоимости при работе с однотабличным запросом

3.3. Построение дерева плана однотабличного запроса

3.4. Как работает исполнитель

3.5. Способы соединения

3.6. Создание дерева плана для многотабличного процесса

4. FOREIGN DATA WRAPPERS (FDW)

Начиная с 2003 года, Postgres частично реализует спецификацию SQL Management of External Data (SQL/MED), позволяющую обращаться к данным, находящимся снаружи, используя обычные SQL-запросы.

Foreign-Data Wrappers (FDW) в PostgreSQL - это инструмент, который позволяет работать с данными из разных источников, будто они находятся прямо в PostgreSQL. Это часть стандарта SQL/MED для работы с внешними данными.

Основные особенности:

Интеграция с Внешними Источниками Данных: FDW позволяют PostgreSQL выполнять запросы к различным внешним источникам данных, включая другие SQL и NoSQL базы данных, CSV-файлы, веб-сервисы и даже программные приложения.
Прозрачный Доступ: Пользователи могут выполнять стандартные SQL-запросы к внешним данным, как будто они находятся непосредственно в PostgreSQL, обеспечивая удобный и единообразный доступ.
Разнообразие Драйверов: Существуют готовые FDW для многих популярных источников данных, таких как MySQL, MongoDB, Oracle и другие, облегчая интеграцию с различными системами. Если нужный FDW еще не написан, вы можете реализовать его самостоятельно.
Транзакции и Безопасность: Многие FDW поддерживают транзакции и меры безопасности, что важно для обеспечения целостности и конфиденциальности данных.
Упрощение Миграции и Интеграции: FDW могут использоваться для упрощения процессов миграции данных и для интеграции разрозненных данных в единую систему.

В целом, Foreign-Data Wrappers значительно расширяют функциональные возможности PostgreSQL, предоставляя гибкий и мощный инструмент для работы с внешними данными.

Установив необходимое расширение и выполнив соответствующие настройки, Вы сможете получить доступ к сторонним таблицам. Например, предположим, что есть два удаленных сервера, а именно PostgreSQL и MySQL, хранящие таблицы foreign_pg_tbl и foreign_my_tbl соответственно. В примере, представленном ниже, Вы получите доступ к сторонним таблицам, выполнив следующие запросы SELECT:

localdb=# -- foreign_pg_tbl is on the remote postgresql server. 
localdb-# SELECT count(*) FROM foreign_pg_tbl;

count
-------

20000
 
localdb=# -- foreign_my_tbl is on the remote mysql server. 
localdb-# SELECT count(*) FROM foreign_my_tbl;

count
-------

Вы также можете выполнить операции JOIN со стронними таблицами, хранящимися на разных серверах.

localdb=# SELECT count(*) FROM foreign_pg_tbl AS p, foreign_my_tbl AS m WHERE p.id = m.id;

count
-------

Большинство разработанных расширений FDW занесены в Postgres wiki. Однако почти все они не поддерживаются должным образом, за исключением обертки postgres_fdw, позволяющей импортировать определения сторонних таблиц с применением команды IMPORT FOREIGN SCHEMA. Эта команда создаёт на локальном сервере определения сторонних таблиц, соответствующие таблицам или представлениям, существующим на удалённом сервере.

4.1. Обзор

4.2. Как работает расширение POSTGRES_FDW

5. УПРАВЛЕНИЕ КОНКУРЕНТНЫМ ДОСТУПОМ

Управление конкурентным доступом является очень важной концепцией в СУБД. Оно гарантирует, что одновременное выполнение запросов несколькими процессами или пользователями оставит данные в согласованном состоянии.

Различают три типа управления конкурентным доступом: Многоверсионное управление конкурентным доступом(MVCC), Строгая двухфазная блокировка (S2PL) и Оптимистический конкурентный контроль (OCC). У каждой техники есть множество вариаций.

Основная идея MVCC заключается в том, что в базе данных допускается существование нескольких «версий» одного и того же элемента данных. Это позволяет улучшить ряд характеристик СУБД, наиболее важных для приложений. При использовании схемы MVCC каждая операция записи кортежа создает в базе данных его новую версию. Каждая версия помечается временной меткой транзакции, которая эту версию создала. В СУБД поддерживается внутренний список всех версий каждого элемента. При выполнении операции чтения СУБД определяет, к какой версии кортежа из этого списка будет обращаться соответствующая транзакция. Это обеспечивает сериализуемый порядок выполнения всех операций. Одним из преимуществ MVCC является то, что СУБД не отвергает опоздавшие операции. Другими словами, СУБД не отвергает операцию чтения из-за того, что затрагиваемый ей элемент уже перезаписан другой транзакцией. Одним из наиболее известных вариантов MVCC протокола является Snapshot Isolation (SI).

Для реализации SI некоторые СУБД, например Oracle, используют сегменты отката. Любая транзакция может охраняться одним и только одним сегментом отката – невозможно распределять данные отката, сгенерированные одной транзакцией, между несколькими сегментами отката. Это не проблема, так как сегменты отката не имеют фиксированного размера. То есть если транзакции необходимо больше места, Oracle автоматически добавит экстент для сегмента и транзакция продолжится. Несколько транзакций могут использовать один сегмент отката, но в обычной ситуации такого не должно происходить. PostgreSQL использует метод попроще. Новый элемент данных вставляется непосредственно в соответствующую страницу таблицы. При чтении элементов PostgreSQL выбирает соответствующую версию элемента в ответ на индивидуальную транзакцию, применяя правила проверки видимости.

SI не допускает три аномалии, определенные в стандарте ANSI SQL-92: Грязные чтения, Неповторяемые чтения и Фантомные чтения. Однако SI не может достичь истинной сериализуемости, поскольку допускает аномалии сериализации, такие как Write Skew и Read-only Transaction Skew. Обратите внимание, что стандарт ANSI SQL-92, основанный на классическом определении сериализуемости, не эквивалентен современному определению.

Для решения этой проблемы в версии 9.1 была добавлена функция Serializable Snapshot Isolation (SSI). SSI может обнаруживать аномалии сериализации и разрешать конфликты, вызванные этими аномалиями. Таким образом, PostgreSQL версий 9.1 и выше обеспечивает истинный уровень изоляции SERIALIZABLE. (Кроме того, SQL Server также использует SSI; Oracle по-прежнему использует только SI).

Данный раздел состоит из следующих 4 подразделов:

Часть 1: Подразделы 5.1. — 5.3.

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

Часть 2: Подразделы 5.4. — 5.6.

Эта часть иллюстрирует ключевые особенности, необходимые для реализации механизма управления параллелизмом.
В подразделах 5.4, 5.5 и 5.6 подробно описывается структура, хранящая состояние транзакций (clog).

Часть 3: Подразделы 5.7. — 5.9..

В этой части на конкретных примерах управление конкурентным доступом в PostgreSQL.
В разделе 5.7 описывается проверка видимости. В этом разделе также показано, как предотвращаются три аномалии, определенные в стандарте ANSI SQL. В разделе 5.8 описывается предотвращение потери обновлений, а в разделе 5.9 дано краткое описание SSI.

Часть 4: Подраздел 5.10.

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

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

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

Уровень изоляции	Грязное чтение	Неповторяемое чтение	Фантомное чтение	Аномалия сериализации
READ COMMITTED	Невозможно	Возможно	Возможно	Возможно
REPEATABLE READ^*1	Невозможно	Невозможно	Невозможно в PG (Возможно ANSI SQL)	Возможно
SERIALIZABLE	Невозможно	Невозможно	Невозможно	Невозможно

Примечание: для DML (языка манипулирования данными, например, SELECT, UPDATE, INSERT, DELETE) PostgreSQL использует SSI, а для DDL (языка определения данных, например, CREATE TABLE и т. д.) - 2PL.

5.1 Идентификатор транзакции

5.2 Структура кортежа

5.3. Вставка, удаление и обновление кортежей

5.4. COMMIT LOG (CLOG)

5.5. Транзакция со снимком данных (transaction snapshot)

5.6. Правила проверки видимости

5.7. Проверка видимости

5.8. Потерянное обновление (LOST Update)

5.9. Serializable snapshot isolation

5.10. Необходимые поддерживающие процессы

6. VACUUM

VACUUM высвобождает пространство, занимаемое «мёртвыми» кортежами. При обычных операциях PostgreSQL кортежи, удалённые или устаревшие в результате обновления, физически не удаляются из таблицы; они сохраняются в ней, пока не будет выполнена команда VACUUM. Таким образом, периодически необходимо выполнять VACUUM, особенно для часто изменяемых таблиц.

Существует два режима команды VACUUM: Concurrent VACUUM и VACUUM Full.

CONCURRENT VACUUM или просто VACUUM может работать параллельно с обычными операциями чтения и записи таблицы, так она не требует исключительной блокировки. Однако освобождённое место не возвращается операционной системе (в большинстве случаев); оно просто остаётся доступным для размещения данных этой же таблицы.

VACUUM FULL переписывает всё содержимое таблицы в новый файл на диске, не содержащий ничего лишнего, что позволяет возвратить неиспользованное пространство операционной системе. Эта форма работает намного медленнее и запрашивает исключительную блокировку для каждой обрабатываемой таблицы.

Также стоит упомянуть о дополнительной функции, представленный в PostgreSQL 8.1, а именно о демоне AUTOVACUUM, которая автоматически пылесосит базу данных, поэтому Вам не нужно вручную запускать оператор VACUUM. Демон AUTOVACUUM включен в конфигурации по умолчанию.

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

В виду того, что команда VACUUM предполагает сканирование целых таблиц, это достаточно дорогостоящий процесс. В версии 8.4 (2009) для повышения эффективности удаления “мертвых” кортежей была введена так называемая карта видимости (VM). Кроме того, в версии 9.6 (2016) за счет усовершенствования VM был значительно улучшен процесс FREEZE.

6.1. Concurrent VACUUM

6.2. Карта видимости

6.3. Заморозка

6.4. Удаление ненужных файлов CLOG

6.5. Демон AUTOVACUUM

6.6. FULL VACUUM

7. HOT и INDEX-ONLY SCAN

В данном разделе мы поговорим о двух способах индексного сканирования: heap only tuple (HOT) и Index-Only scan.

7.1. Heap Only Tuples (HOT)

7.2. INDEX-ONLY-SCAN

8. МЕНЕДЖЕР БУФЕРОВ

Менеджер буферов управляет передачей данных между общей памятью и постоянным хранилищем и может оказывать значительное влияние на производительность СУБД, поскольку работает очень эффективно.

Этот раздел посвящен менеджеру буферов. Первый подраздел – это общий обзор данной темы, последующие подразделы описывают следующее:

Структура менеджера буферов;
Блокировки менеджера буферов;
Принцип рабты менеджера буферов;
Кольцевой буфер;
Сбрасывание грязных страниц

8.1. Обзор

8.2. Структура менеджера буферов

8.3. Блокировки менеджера буферов

8.4. Принцип работы менеджера буфера

8.5. Кольцевой буфер

8.6. Сбрасывание грязных страниц

9. ЖУРНАЛ ПРЕДЗАПИСИ (WAL)

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

Журнал предзаписи (WAL) — это стандартный метод обеспечения целостности данных в рамках PostgerSQL. Основная идея WAL состоит в том, что изменения в файлах с данными (где находятся таблицы и индексы) должны записываться только после того, как эти изменения были занесены в журнал, т. е. после того как записи журнала, описывающие данные изменения, будут сохранены на постоянное устройство хранения. Если следовать этой процедуре, то записывать страницы данных на диск после подтверждения каждой транзакции нет необходимости, потому что мы знаем, что если случится сбой, то у нас будет возможность восстановить базу данных с помощью журнала.

Механизм WAL впервые был реализован в версии 7.1. Благодаря ему появился возможность восстановления на момент времени или Point-in-time-recovery (PITR), обеспечивающее непрерывное резервное копирование данных таблицы PostgreSQL. Механизм WAL также дал зеленый свет потоковой репликации или Streaming Replication (SR). PITR и SR подробно описаны раздел 10 и 11 соответственно.

Достаточно сложный механизм WAL невозможно объяснить в двух словах. Поэтому подробное описание WAL в PostgreSQL будет включать в себя следующие пункты:

Логическая и физическая структуры WAL (журнала транзакций);
Внутренне устройство WAL;
Запись данных WAL;
Процесс записи WAL;
Обработка контрольной точки;
Восстановление БД;
Управление файлами сегментов WAL;
Непрерывное архивирование.

9.1. Обзор

9.2. Журнал транзакций и файлы сегмента WAL

9.3. Внутреннее устройство сегмента WAL

9.4. Внутреннее устройство записи XLOG

9.5. Создание записей XLOG

9.6. Процесс записи WAL

9.7. Выполнение контрольной точки в PostgreSQL

9.8. Восстановление базы данных в PostgreSQL

9.9. Управление файлами сегментов WAL

9.10. Непрерывное архивирование

10. СОЗДАНИЕ БАЗОВОЙ РЕЗЕРВНОЙ КОПИИ И POINT-IN-TIME RECOVERY (PITR)

Резервное копирование баз данных можно условно разделить на две категории: логическое и физическое резервное копирование. Оба варианта имеют свои преимущества и недостатки. Одним из главных недостатков логического резервного копирования является то, что оно может занимать слишком много времени. В частности, создание резервной копии большой базы данных может занять много времени, а восстановление базы данных из резервной копии - еще больше. С другой стороны, физические резервные копии могут быть сделаны и восстановлены гораздо быстрее, что делает их очень важной и полезной функцией.

В PostgreSQL физическое полное резервное копирование доступно с версии 8.0. Физическое резервное копирование носит иначе называется базовым резервным копированием.

Point-in-time-recovery (PITR) обеспечивает непрерывное резервное копирование данных таблицы PostgreSQL. Вы можете восстановить таблицу на определенный момент времени в процессе создания виртуальной машины или через интерфейс восстановления из резервной копии. В процессе восстановления на определенный момент времени сохраненная таблица восстанавливается в новую виртуальную машину.

Функция PITR доступна только для баз данных под управлением PostgreSQL.

PITR помогает защитить таблицы PostgreSQL от случайных операций записи или удаления. Благодаря восстановлению на определенный момент времени вам не нужно беспокоиться о создании, обслуживании или планировании резервного копирования по запросу. Например, тестовый сценарий случайно записывает данные в рабочую таблицу PostgreSQL. С помощью PITR вы можете восстановить эту таблицу на любой момент времени.

В этом разделе мы поговорим о следующем:

Что такое резервное копирование?
Как работает PITR?
Что такое timelineId?
Другая полезная информация

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

10.1. Создание базовой резервной копии

10.2. Как работает POINT-IN-TIME RECOVERY

10.3. timelineid и файл timeline history

10.4. PITR c применением файла timeline history

11. ПОТОКОВАЯ РЕПЛИКАЦИЯ

Потоковая репликация (Streaming Replication) — это репликация, при которой от основного сервера PostgreSQL на реплики передается WAL (Write Ahead Log). И каждая реплика затем по этому журналу изменяет свои данные. Для настройки такой репликации все серверы должны быть одной версии, работать на одной ОС и архитектуре.

Этот раздел посвящен описанию принципа работы потоковой репликации и охватывает следующие темы:

Запуск потокой репликации;
Передача данных между основным и резервными серверами;
Управление несколькими резервными серверами основным сервером;
Обнаруживает сбоев в работе резервных серверов.

Примечание: Синхронная и асинхронная репликации

До версии 9.1 PostgreSQL поддерживал только асинхронную репликацию. Однако внедрение потоковой репликации обеспечило более надежное решение для синхронной репликации, гарантирующее, что изменения данных реплицируются на резервные серверы в режиме реального времени. Асинхронная репликация по-прежнему доступна, но была заменена более новой реализацией синхронной репликации.

11.1. Запуск потоковой репликации

11.2. Как осуществить потоковую репликацию

11.3. Управление несколькими резервными серверами

11.4. Обнаружение сбоев резервного сервера

← Предыдущая статья

Как получить информацию о структуре БД для документации

Следующая статья →

PostgreSQL 16. Организация данных. Часть 1