PostgreSQL 18 изнутри

Егор Рогов
Русское издание · PostgreSQL 18 · Москва, 2026

Начать чтение · Печатать всю книгу · Исходный PDF

Содержание

Иллюстрации

1. Рисунок publication.1 — Логотип издательства «ДМК Пресс»
2. Рисунок 1.1 — Базы данных
3. Рисунок 1.2 — Табличные пространства
4. Рисунок 1.3 — Слои и файлы
5. Рисунок 1.4 — Процессы и память
6. Рисунок 3.1 — Заголовок страницы
7. Рисунок 4.1 — Что такое снимок данных
8. Рисунок 4.2 — Видимость версий строк в снимке
9. Рисунок 4.3 — Из чего состоит снимок
10. Рисунок 4.4 — Из чего состоит снимок
11. Рисунок 4.5 — Горизонт транзакции
12. Рисунок 6.1 — Переполнение счетчика транзакций
13. Рисунок 6.2 — Заморозка версий и правила видимости
14. Рисунок 7.1 — Возраст для аварийного срабатывания автоочистки
15. Рисунок 9.1 — Устройство буферного кеша
16. Рисунок 9.2 — Попадание в кеш
17. Рисунок 9.3 — Поиск буфера и вытеснение
18. Рисунок 10.1 — Контрольная точка
19. Рисунок 10.2 — Контрольная точка
20. Рисунок 10.3 — Настройка контрольной точки
21. Рисунок 12.1 — Очередь ожидания
22. Рисунок 13.1 — Исключительные режимы
23. Рисунок 13.2 — Исключительные режимы
24. Рисунок 13.3 — Исключительные режимы
25. Рисунок 13.4 — Разделяемые режимы
26. Рисунок 13.5 — Разделяемые режимы
27. Рисунок 13.6 — Взаимоблокировки
28. Рисунок 15.1 — Буферный кеш
29. Рисунок 15.2 — Буферы журнала предзаписи
30. Рисунок 15.3 — Кеш SLRU
31. Рисунок 16.1 — Демонстрационная база данных
32. Рисунок 16.2 — Разбор
33. Рисунок 16.3 — Трансформация
34. Рисунок 16.4 — Планирование
35. Рисунок 16.5 — Планирование
36. Рисунок 16.6 — Планирование
37. Рисунок 16.7 — Планирование
38. Рисунок 16.8 — Планирование
39. Рисунок 16.9 — Планирование
40. Рисунок 16.10 — Планирование
41. Рисунок 16.11 — Исполнение
42. Рисунок 17.1 — Уникальные значения
43. Рисунок 17.2 — Наиболее частые значения
44. Рисунок 17.3 — Гистограмма
45. Рисунок 17.4 — Гистограмма
46. Рисунок 18.1 — Последовательное сканирование
47. Рисунок 18.2 — Параллельные планы выполнения
48. Рисунок 19.1 — Индексы и расширяемость
49. Рисунок 20.1 — Хороший случай: высокая корреляция
50. Рисунок 20.2 — Плохой случай: низкая корреляция
51. Рисунок 20.3 — Плохой случай: низкая корреляция
52. Рисунок 20.4 — Оценка стоимости
53. Рисунок 20.5 — Оценка стоимости
54. Рисунок 20.6 — Сравнение методов доступа
55. Рисунок 22.1 — Однопроходное соединение хешированием
56. Рисунок 22.2 — Однопроходное соединение хешированием
57. Рисунок 22.3 — Двухпроходное соединение хешированием
58. Рисунок 22.4 — Двухпроходное соединение хешированием
59. Рисунок 22.5 — Двухпроходное соединение хешированием
60. Рисунок 22.6 — Параллельное однопроходное хеш-соединение
61. Рисунок 22.7 — Параллельное однопроходное хеш-соединение
62. Рисунок 22.8 — Параллельное двухпроходное хеш-соединение
63. Рисунок 22.9 — Параллельное двухпроходное хеш-соединение
64. Рисунок 22.10 — Параллельное двухпроходное хеш-соединение
65. Рисунок 22.11 — Параллельное двухпроходное хеш-соединение
66. Рисунок 23.1 — Внешняя сортировка
67. Рисунок 23.2 — Внешняя сортировка
68. Рисунок 23.3 — Внешняя сортировка
69. Рисунок 23.4 — Внешняя сортировка
70. Рисунок 23.5 — Внешняя сортировка
71. Рисунок 23.6 — Сравнение способов соединения
72. Рисунок 24.1 — Страничная организация
73. Рисунок 24.2 — Страничная организация
74. Рисунок 24.3 — Страничная организация
75. Рисунок 24.4 — Страничная организация
76. Рисунок 25.1 — Общий принцип
77. Рисунок 25.2 — Поиск по равенству
78. Рисунок 25.3 — Поиск по неравенству
79. Рисунок 25.4 — Поиск по диапазону
80. Рисунок 25.5 — Вставка
81. Рисунок 25.6 — Страничная организация
82. Рисунок 25.7 — Оптимизация пропуска
83. Рисунок 25.8 — Оптимизация пропуска
84. Рисунок 25.9 — Оптимизация пропуска
85. Рисунок 25.10 — Оптимизация пропуска
86. Рисунок 25.11 — Оптимизация пропуска
87. Рисунок 25.12 — Оптимизация пропуска
88. Рисунок 26.1 — R-дерево для точек
89. Рисунок 26.2 — R-дерево для точек
90. Рисунок 26.3 — R-дерево для точек
91. Рисунок 26.4 — Поиск вхождения в область
92. Рисунок 26.5 — Поиск вхождения в область
93. Рисунок 26.6 — Поиск вхождения в область
94. Рисунок 26.7 — Поиск ближайших соседей
95. Рисунок 26.8 — Поиск ближайших соседей
96. Рисунок 26.9 — Поиск ближайших соседей
97. Рисунок 26.10 — Поиск ближайших соседей
98. Рисунок 26.11 — Вставка
99. Рисунок 26.12 — Вставка
100. Рисунок 26.13 — Индексация tsvector
101. Рисунок 26.14 — Индексация tsvector
102. Рисунок 26.15 — Индексация tsvector
103. Рисунок 26.16 — Индексация tsvector
104. Рисунок 27.1 — Дерево квадрантов для точек
105. Рисунок 27.2 — Дерево квадрантов для точек
106. Рисунок 27.3 — Дерево квадрантов для точек
107. Рисунок 27.4 — Класс операторов
108. Рисунок 27.5 — Класс операторов
109. Рисунок 27.6 — Страничная организация
110. Рисунок 27.7 — Поиск
111. Рисунок 27.8 — Поиск
112. Рисунок 27.9 — Вставка
113. Рисунок 27.10 — Вставка
114. Рисунок 27.11 — Вставка
115. Рисунок 27.12 — Вставка
116. Рисунок 27.13 — K-мерные деревья для точек
117. Рисунок 27.14 — K-мерные деревья для точек
118. Рисунок 27.15 — K-мерные деревья для точек
119. Рисунок 27.16 — Префиксное дерево для строк
120. Рисунок 27.17 — Поиск
121. Рисунок 27.18 — Вставка
122. Рисунок 27.19 — Вставка
123. Рисунок 28.1 — Индекс для полнотекстового поиска
124. Рисунок 28.2 — Поиск
125. Рисунок 28.3 — Частые и редкие лексемы
126. Рисунок 28.4 — Вставка
127. Рисунок 28.5 — Класс операторов jsonb_ops
128. Рисунок 28.6 — Класс операторов jsonb_path_ops
129. Рисунок 29.1 — Страничная организация
130. Рисунок 29.2 — Вставка значений
131. Рисунок 29.3 — Размер зоны и эффективность поиска
132. Рисунок 29.4 — Мультидиапазоны значений (minmax-multi)
133. Рисунок 29.5 — Мультидиапазоны значений (minmax-multi)
134. Рисунок 29.6 — Фильтры Блума (bloom)

Таблицы

1. Таблица 2.1 — Уровни изоляции и аномалии в стандарте SQL
2. Таблица 2.2 — Уровни изоляции и аномалии в PostgreSQL
3. Таблица 12.1 — Матрица конфликтов: Shared и Exclusive
4. Таблица 12.2 — Матрица конфликтов блокировок отношений
5. Таблица 13.1 — Матрица конфликтов блокировок строк
6. Таблица 18.1 — Размер таблицы и количество процессов
7. Таблица 26.1 — Сигнатуры лексем
8. Таблица 26.2 — Сигнатуры документов
9. Таблица 28.1 — Функция согласованности: «стоя» или «кудряв»
10. Таблица 28.2 — Функция согласованности: обязательная и дополнительная лексемы

Об издании

Русский оригинал книги Егора Рогова «PostgreSQL 18 изнутри», ДМК Пресс, 2026. Сохранены текст, примеры, иллюстрации, примечания и порядок глав исходного издания. Номера страниц отсылают к печатной книге; номера иллюстраций и таблиц добавлены для навигации в веб-издании.

© Текст, оформление, ООО «ППГ», 2026; © Издание, ДМК Пресс, 2026.

Сайт книги · Выходные сведения · Издательские сведения

English edition: PostgreSQL 14 Internals — отдельное издание, описывающее PostgreSQL 14.

Титульный лист и выходные сведения

Компания Postgres Professional

Егор Рогов PostgreSQL 18 изнутри

Москва, 2026

УДК 004.65 ББК 32.972.134 Р59

Рогов Е. В.

Р59 PostgreSQL 18 изнутри. — М.: ДМК Пресс, 2026. — 682 с.

ISBN 978-5-93700-499-4

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

В настоящем издании учтены замечания читателей и исправлены опечатки, а также отражены изменения, произошедшие в версии PostgreSQL 18.

Сайт книги: https://postgrespro.ru/education/books/internals.

Для администраторов и программистов.

УДК 004.65 ББК 32.972.134

На обложке использован фрагмент гравюры из книги The History of Four-footed Beasts and Serpents Эдварда Топсела, изданной в 1658 году в Лондоне.

ISBN 978-5-93700-499-4 © Текст, оформление, ООО «ППГ», 2026 © Издание, ДМК Пресс, 2026

О книге

— До чего же это все просто! — воскликнул Шпунтик. — А я где-то читал, что писателю нужен какой-то вымысел, замысел…

— Э, замысел! — нетерпеливо перебил его Смекайло. — Это только в книгах так пишется, что нужен замысел, а попробуй задумай что-нибудь, когда все уже и без тебя задумано! Что ни возьми — все уже было. Николай Носов, Приключения Незнайки и его друзей

Для кого эта книга

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

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

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

Чего нет в книге

Эта книга — не сборник рецептов. На все случаи жизни готовых решений не напасешься, а понимание внутренней механики сложной системы дает возможность критически переосмысливать чужой опыт и делать свои собственные выводы. Поэтому я и объясняю такие подробности устройства, знание которых на первый взгляд не имеет практического смысла.

Но эта книга и не учебник. Она углубляется в одни области (более интересные мне самому) и обходит стороной другие. Если вы изучаете SQL, обратите внимание на учебник Евгения Моргунова PostgreSQL. Основы языка SQL¹, а необходимый теоретический фундамент даст книга Бориса Новикова Основы технологий баз данных ².

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

Еще эта книга не учит разрабатывать ядро PostgreSQL. Я не предполагаю у читателя знания языка C и ориентируюсь на администраторов и прикладных разработчиков. Хотя и ссылаюсь постоянно на исходный код, из которого можно узнать столько подробностей, сколько душе угодно, и даже больше.

Что в книге есть

Во вводной главе без особых деталей я даю основные понятия, на которые опирается все дальнейшее повествование. Я предполагаю, что вы не почерпнете из этой главы практически ничего нового, но все-таки включаю ее для полноты картины. К тому же она может пригодиться тем, кто переходит с других СУБД.

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

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

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

Четвертая часть объясняет, как сервер планирует и выполняет SQL-запросы. Я рассказываю, какие есть способы доступа к данным, какие применяются методы соединения и как используется статистическая информация.

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

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

Обозначения

Я пытался писать книгу так, чтобы ее можно было читать последовательно, страница за страницей. Но всю правду не получается раскрыть сразу, и к одной и той же теме приходится возвращаться несколько раз. Если бы я каждый раз писал «это будет рассмотрено позже», книга сильно увеличилась бы в размере, поэтому в таких случаях я ставлю на полях номер страницы, на которой тема развивается дальше. Такой же номер, ведущий назад, отсылает к месту в книге, где уже что-то говорилось о предмете обсуждения. (с. 19)

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

Также на полях указываются значения по умолчанию для обсуждаемых параметров. Сами параметры (как обычные, так и параметры хранения) выделены курсивом: work_mem. (по умолчанию: 4MB)

В сносках я постоянно ссылаюсь на первоисточники. Их несколько, и на первом месте стоит кладезь полезной информации — документация³. Являясь органичной частью проекта, она всегда поддерживается в актуальном состоянии самими разработчиками. Но главный первоисточник — безусловно, исходный код⁴. Удивительно, на какое количество вопросов можно найти ответы просто в комментариях и файлах README, даже не владея языком C. Реже я ссылаюсь на записи коммитфеста ⁵: в переписках pgsql-hackers всегда можно проследить историю изменений и понять логику принятых разработчиками решений, но ценой чтения огромного массива обсуждений.

Конечно, в книге много фрагментов кода, в основном на языке SQL. Код показан с приглашением =>; если необходимо, то следом за ним приведен и ответ сервера:

=> SELECT clock_timestamp();
        clock_timestamp
-------------------------------
 2026-04-05 20:51:34.767686+03
(1 row)

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

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

=> SHOW server_version;
 server_version
----------------
 18.3
(1 row)

Чтобы повторить такие команды (а это полезно для самообразования, как и любые эксперименты), удобно открыть два терминала с psql.

Отдельные команды и названия различных объектов базы данных (таких как таблицы и столбцы, функции, расширения) выделены в тексте моноширинным шрифтом: UPDATE, pg_class.

Вызовы утилит из операционной системы показаны с приглашением, оканчивающимся на $:

postgres$ whoami
postgres

Я использую Linux, но без какой-либо специфики; достаточно будет самого базового понимания.

Благодарности

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

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

Я признателен компании Postgres Professional за возможность работать над этой книгой не только в свободное время. Но компания — это люди, и я хочу сказать отдельное спасибо Олегу Бартунову за неиссякаемую энергию и идеи и Ивану Панченко за всестороннюю поддержку и L^ATEX.

Спасибо моим товарищам по образовательному отделу за творческую атмосферу и дискуссии, в ходе которых формировался материал учебных курсов и способ его подачи, что нашло свое отражение и в книге. Спасибо Павлу Толмачеву за внимательную вычитку черновиков.

Многие главы книги впервые были опубликованы в виде статей на Хабре⁶, и я благодарен читателям за замечания и отклики. Они показали нужность этой работы, позволили разглядеть белые пятна в моих знаниях и сделать текст лучше.

Спасибо Людмиле Мантровой, проделавшей огромную работу над языком книги. Если вы не спотыкаетесь на каждом втором предложении, это ее заслуга.

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

1 Введение

1.1. Организация данных

Базы данных

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

Данные, которыми управляет PostgreSQL, хранятся в базах данных ¹. Один экземпляр PostgreSQL одновременно работает с несколькими базами, которые вместе называются кластером баз данных.

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

При инициализации в PGDATA создаются три одинаковые базы данных:

template0 используется, например, для восстановления из логической резервной копии или для создания базы в другой кодировке и никогда не должна меняться; (с. 631)

template1 служит шаблоном для всех остальных баз данных, которые может создать пользователь в этом кластере;

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

Системный каталог

Метаинформация обо всех объектах кластера (таких как таблицы, индексы, типы данных или функции) хранится в таблицах, относящихся к системному каталогу ³. В каждой базе данных имеется собственный набор таблиц (и представлений), описывающих объекты этой конкретной базы. Существует также несколько таблиц системного каталога, общих для всего кластера, которые не принадлежат какой-либо определенной базе данных (формально используется фиктивная база с нулевым идентификатором), но доступны в любой из них.

К системному каталогу можно обращаться с помощью обычных запросов SQL, а изменения в нем происходят при выполнении команд DDL. Клиент psql располагает целым рядом специальных команд для просмотра системного каталога.

Все имена таблиц системного каталога имеют префикс pg_, например pg_database. Имена столбцов начинаются с трехбуквенного кода, который обычно соответствует имени таблицы, например datname.

Во всех таблицах системного каталога столбец с первичным ключом называется oid и имеет одноименный тип oid (object identifier) — целое 32-битное число.

Схемы

Схемы ⁵ представляют собой пространства имен для всех объектов, хранящихся в базе данных. Кроме пользовательских схем, имеется несколько специальных служебных:

public используется по умолчанию для пользовательских объектов, если не выполнены иные настройки;

pg_catalog используется для таблиц системного каталога;

information_schema дает альтернативное представление системного каталога, регламентируемое стандартом SQL;

pg_toast используется для объектов, относящихся к TOAST; (с. 33)

pg_temp объединяет временные таблицы (хотя временные таблицы разных пользователей создаются в разных схемах pg_temp_N, каждый обращается к своим объектам, используя имя pg_temp).

Схемы существуют внутри базы данных, и все объекты базы принадлежат каким-либо схемам.

Если при обращении к объекту схема не указана явно, выбирается первая подходящая схема из перечисленных в пути поиска. Путь формируется на основе значения параметра search_path, к которому, в частности, неявно добавляются схемы pg_catalog и (при необходимости) pg_temp. Это позволяет иметь в разных схемах объекты с одинаковыми именами.

Табличные пространства

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

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

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

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

pg_default располагается в каталоге PGDATA/base и используется как табличное пространство по умолчанию, если явно не выбрать для этой цели другое пространство;

pg_global располагается в каталоге PGDATA/global и хранит общие для всего кластера объекты системного каталога.

При создании пользовательского табличного пространства указывается произвольный каталог; PostgreSQL создает на него символьную ссылку в каталоге PGDATA/pg_tblspc. Вообще, все пути PostgreSQL относительные и отсчитываются от каталога данных PGDATA. Благодаря этому можно переносить PGDATA на другое место (конечно, предварительно остановив сервер).

Рисунок сводит воедино базы данных, схемы и табличные пространства. Здесь база данных postgres использует табличное пространство по умолчанию xyzzy, а база template1 — pg_default. На пересечении табличных пространств и схем находятся различные объекты базы данных:

Отношения

Самые важные объекты базы данных — таблицы и индексы — при всех своих различиях схожи в том, что состоят из строк. Для таблиц это очевидно, но и в B-деревьях узлы состоят из строк, содержащих индексированные значения и ссылки на другие узлы или на табличные строки. (с. 523)

Есть еще некоторое количество объектов, устроенных похожим образом: последовательности (по сути однострочные таблицы), материализованные представления (по сути таблицы, которые помнят запрос). А еще есть обычные представления, которые сами по себе не хранят данные, но во всех остальных смыслах похожи на таблицы.

Все эти объекты в PostgreSQL называются общим словом отношение (relation).

Слои и файлы

Информация, связанная с отношением, организована в несколько слоев ⁶ (forks) разных типов, каждый из них содержит определенный вид данных.

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

Файл постепенно растет, и когда его размер достигает 1 Гбайта, создается следующий файл этого же слоя (такие файлы иногда называют сегментами). Порядковый номер сегмента добавляется в конец имени файла.

Ограничение размера файла в 1 Гбайт возникло исторически для поддержки различных файловых систем, некоторые из которых не умеют работать с файлами большого размера. Ограничение можно изменить при сборке PostgreSQL (./configure --with-segsize).

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

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

Имеется несколько стандартных типов слоев.

Основной слой (main fork) — это собственно данные: те самые табличные или индексные строки. Основной слой существует для любых отношений (кроме представлений, которые не содержат данных).

Имена файлов основного слоя состоят только из числового идентификатора (равного значению столбца relfilenode таблицы pg_class).

Посмотрим для примера путь к файлу таблицы, созданной в табличном пространстве pg_default:

=> CREATE UNLOGGED TABLE t(
  a integer,
  b numeric,
  c text,
  d json
);
=> INSERT INTO t VALUES (1, 2.0, 'foo', '{}');
=> SELECT pg_relation_filepath('t');
 pg_relation_filepath
----------------------
 base/16384/16385
(1 row)

Каталог base соответствует табличному пространству pg_default, следующий подкаталог — базе данных, и уже в нем находится интересующий нас файл:

=> SELECT oid FROM pg_database WHERE datname = 'internals';
  oid
-------
 16384
(1 row)
=> SELECT relfilenode FROM pg_class WHERE relname = 't';
 relfilenode
-------------
       16385
(1 row)

Вот соответствующий файл в файловой системе:

=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385');
 size
------
 8192
(1 row)

Слой инициализации (init fork) ⁷ существует только для нежурналируемых таблиц (созданных с указанием UNLOGGED) и их индексов. Такие объекты ничем не отличаются от обычных, кроме того, что действия с ними не записываются в журнал предзаписи. За счет этого работа с ними происходит быстрее, но в случае сбоя невозможно восстановить данные в согласованном состоянии. Поэтому при восстановлении PostgreSQL просто удаляет все слои таких объектов и записывает слой инициализации на место основного слоя. В результате получается «пустышка». (с. 211)

Таблица t создана нежурналируемой, поэтому у нее есть слой инициализации. Он имеет такое же имя, как и основной слой, но с суффиксом _init:

=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385_init');
 size
------
    0
(1 row)

Карта свободного пространства (free space map) ⁸ — слой, в котором отслеживается примерный объем свободного места внутри страниц. Этот объем постоянно меняется: при добавлении новых версий строк уменьшается, при очистке — увеличивается. Карта свободного пространства используется при вставке новых версий строк, чтобы быстро найти подходящую страницу, на которую поместятся добавляемые данные.

Файлы, относящиеся к карте свободного пространства, имеют суффикс _fsm. Но появляются они не сразу, а только при необходимости. Самый простой способ добиться этого — выполнить очистку таблицы: (с. 133)

=> VACUUM t;
=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385_fsm');
 size
-------
 24576
(1 row)

Для ускорения поиска карта свободного пространства организована как дерево и занимает минимум три страницы (отсюда и размер файла слоя для почти пустой таблицы).

Карта свободного пространства существует не только для таблиц, но и для индексов. Но поскольку индексную строку нельзя добавить на произвольную страницу (например, для B-дерева место вставки определяется порядком сортировки), отслеживаются только те страницы, которые были полностью очищены и могут быть снова задействованы в индексной структуре.

Карта видимости (visibility map) ⁹ — слой, который позволяет быстро определить, требует ли страница очистки или заморозки. Для этого на каждую табличную страницу в этом слое отведено два бита.

Одним битом отмечены страницы, которые содержат только актуальные версии строк. Процесс очистки пропускает такие страницы, поскольку в них нечего очищать. Кроме того, когда транзакция пытается прочитать строку из такой страницы, можно не проверять ее видимость, а это позволяет использовать сканирование только индекса. (с. 131) (с. 422)

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

Файлы карты видимости имеют суффикс _vm. Обычно они самые небольшие по размеру:

=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385_vm');
 size
------
 8192
(1 row)

Карта видимости существует для таблиц, но не для индексов. (с. 89)

Страницы

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

Обычно страница имеет размер 8 Кбайт. Его можно поменять в некоторых пределах (до 32 Кбайт), но только при сборке (./configure --with-blocksize), и, как правило, никто этим не занимается. Собранный и запущенный экземпляр может работать со страницами только одного размера — создать табличные пространства с разноразмерными страницами нельзя.

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

TOAST

Каждая строка должна помещаться целиком на одну страницу: нет способа «продолжить» строку на следующей странице. Для длинных строк используется технология, названная TOAST ¹⁰ (The Oversized Attributes Storage Technique).

TOAST подразумевает несколько стратегий. Длинные значения атрибутов можно отправить в отдельную служебную таблицу, предварительно нарезав на небольшие фрагменты-«тосты». Другой вариант — сжать длинное значение так, чтобы строка все-таки поместилась на одну страницу. А можно и то и другое: сначала сжать, а уже потом нарезать и отправить.

Если основная таблица содержит потенциально длинные атрибуты, для нее сразу же создается отдельная toast-таблица (одна для всех атрибутов). Например, если в таблице есть столбец типа numeric или text, toast-таблица будет создана, даже если в таком столбце никогда не будут храниться длинные значения.

Для индексов технология TOAST предлагает только сжатие; вынесение атрибутов в отдельную таблицу не поддерживается. Это накладывает ограничение на размер индексируемых ключей (как это ограничение реализуется, зависит от класса операторов). (с. 397)

Обычно стратегии определяются типами столбцов. Посмотреть стратегии проще всего командой \d+ в psql, но для сокращения вывода я использую запрос к системному каталогу: (psql: \d+)

=> SELECT attname, atttypid::regtype,
  CASE attstorage
    WHEN 'p' THEN 'plain'
    WHEN 'e' THEN 'external'
    WHEN 'm' THEN 'main'
    WHEN 'x' THEN 'extended'
  END AS storage
FROM pg_attribute
WHERE attrelid = 't'::regclass AND attnum > 0;
 attname | atttypid | storage
---------+----------+----------
 a       | integer  | plain
 b       | numeric  | main
 c       | text     | extended
 d       | json     | extended
(4 rows)

Стратегии состоят в следующем:

plain — TOAST не используется (стратегия применяется для заведомо «коротких» типов данных, как integer);

extended — допускается как сжатие, так и хранение в отдельной toast-таблице;

external — длинные значения хранятся в toast-таблице несжатыми;

main — длинные значения в первую очередь сжимаются, а в toast-таблицу попадают, только если сжатие не помогло.

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

1. Сначала перебираем атрибуты со стратегиями external и extended, двигаясь от самых длинных к более коротким. Extended-атрибуты сжимаются, и если после этого значение (само по себе, без учета других атрибутов) превосходит четверть страницы, оно сразу же отправляется в toast-таблицу. External-атрибуты обрабатываются так же, но не сжимаются.

2. Если после первого прохода строка все еще не помещается, по одному отправляем в toast-таблицу оставшиеся атрибуты со стратегиями external и extended.

3. Если и это не помогло, пытаемся сжать атрибуты со стратегией main, оставляя их при этом в табличной странице.

4. Если строка все равно недостаточно коротка, отправляем в toast-таблицу main-атрибуты.

Пороговое значение составляет все те же 2000 байт, но может переопределяться параметром хранения toast_tuple_target на уровне таблицы.

Иногда может оказаться полезным изменить стратегию для некоторых столбцов. Если заранее известно, что данные в столбце не сжимаются (например, в столбце хранятся JPEG-изображения), можно установить для него стратегию external — это позволит сэкономить на бесполезных попытках сжатия. Изменить стратегию можно как при создании таблицы (предложение STORAGE команды CREATE TABLE), так и впоследствии: (v. 16)

=> ALTER TABLE t ALTER COLUMN d SET STORAGE external;

Повторив запрос, получим:

 attname | atttypid | storage
---------+----------+----------
 a       | integer  | plain
 b       | numeric  | main
 c       | text     | extended
 d       | json     | external
(4 rows)

Для сжатия доступны два алгоритма: традиционный PGLZ и более современный LZ4 (оба являются разновидностями алгоритма словарного сжатия Лемпеля—Зива). По умолчанию сжатие выполняется алгоритмом, указанным в параметре default_toast_compression; значение можно установить для отдельных столбцов в предложении COMPRESSION. Эталонные тесты показывают, что при сходном с PGLZ уровне сжатия LZ4 тратит меньше ресурсов процессора. (v. 15) (по умолчанию: pglz)

Toast-таблицы располагаются в отдельной схеме pg_toast, не входящей в путь поиска, и поэтому обычно не видны. Для временных таблиц используется схема pg_toast_temp_N аналогично обычной pg_temp_N.

Конечно, при желании всегда можно подглядеть за внутренней механикой процесса. Скажем, в таблице t есть три потенциально длинных атрибута, поэтому toast-таблица обязана быть. Вот она:

=> SELECT relnamespace::regnamespace, relname
FROM pg_class WHERE oid = (
  SELECT reltoastrelid FROM pg_class WHERE relname = 't'
);
 relnamespace |    relname
--------------+----------------
 pg_toast     | pg_toast_16385
(1 row)

=> \d+ pg_toast.pg_toast_16385
TOAST table "pg_toast.pg_toast_16385"
   Column   |  Type   | Storage
------------+---------+---------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain
Owning table: "public.t"
Indexes:
    "pg_toast_16385_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Access method: heap

Логично, что для «тостов», на которые нарезается строка, применяется стратегия plain: TOAST второго уровня не существует.

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

=> SELECT indexrelid::regclass FROM pg_index
WHERE indrelid = (
  SELECT oid FROM pg_class WHERE relname = 'pg_toast_16385'
);
          indexrelid
-------------------------------
 pg_toast.pg_toast_16385_index
(1 row)

=> \d pg_toast.pg_toast_16385_index
Unlogged index "pg_toast.pg_toast_16385_index"
  Column   |  Type   | Key? | Definition
-----------+---------+------+------------
 chunk_id  | oid     | yes  | chunk_id
 chunk_seq | integer | yes  | chunk_seq
primary key, btree, for table "pg_toast.pg_toast_16385"

Таким образом, toast-таблица увеличивает минимальное число файлов, «обслуживающих» таблицу, до восьми: три слоя основной таблицы, три слоя toast-таблицы и два слоя toast-индекса.

Столбец c использует стратегию extended, значения в нем будут сжиматься:

=> UPDATE t SET c = repeat('A',5000);
=> SELECT * FROM pg_toast.pg_toast_16385;
 chunk_id | chunk_seq | chunk_data
----------+-----------+------------
(0 rows)

В toast-таблице ничего нет: повторяющиеся символы сжались алгоритмом PGLZ, и после этого значение поместилось в обычной табличной странице.

А теперь составим значение из случайных символов:

=> UPDATE t SET c = (
  SELECT string_agg( chr(trunc(65+random()*26)::integer), '')
  FROM generate_series(1,5000)
)
RETURNING left(c,10) || '...' || right(c,10);
        ?column?
-------------------------
 TGSXWZTOBN...SJWTGKUXDT
(1 row)
UPDATE 1

Такую последовательность сжать не получается. В табличную строку записывается идентификатор chunk_id:

=> SELECT pg_column_toast_chunk_id(c) -- начиная с v.17
FROM t;
 pg_column_toast_chunk_id
--------------------------
                    16390
(1 row)

А само значение попадает в toast-таблицу:

=> SELECT chunk_id,
  chunk_seq,
  length(chunk_data),
  left(encode(chunk_data,'escape')::text, 10) || '...' ||
  right(encode(chunk_data,'escape')::text, 10)
FROM pg_toast.pg_toast_16385;
 chunk_id | chunk_seq | length |        ?column?
----------+-----------+--------+-------------------------
    16390 |         0 |   1996 | TGSXWZTOBN...LGRUNTKNNC
    16390 |         1 |   1996 | AFBHZKOFQG...KMKVNXBKWL
    16390 |         2 |   1008 | VEVQVJGFYA...SJWTGKUXDT
(3 rows)

Видно, что данные нарезаны на фрагменты. Размер фрагментов выбирается так, чтобы на странице toast-таблицы помещалось четыре строки. От версии к версии это значение может немного меняться в зависимости от размера заголовка страницы.

При обращении к «длинному» атрибуту PostgreSQL автоматически, прозрачно для приложения, восстанавливает исходное значение и возвращает его клиенту. Если же такие атрибуты не участвуют в запросе, то toast-таблица не читается. Это одна из причин не использовать «звездочку» в производственном коде.

Если клиент запрашивает начальную часть длинного значения, то будут прочитаны только необходимые фрагменты, в том числе и в случае, когда значение хранится в сжатом виде. (v. 13)

Тем не менее и на сжатие с нарезкой, и на последующее восстановление тратится довольно много ресурсов. Поэтому хранить объемные данные в PostgreSQL — не лучшая идея, особенно если они активно используются и при этом для них не требуется транзакционная логика (как пример: отсканированные оригиналы бухгалтерских документов). Потенциально более выгодная альтернатива — хранить такие данные в файловой системе, а в базе данных держать только имена соответствующих файлов. Правда, тогда СУБД не сможет обеспечивать согласованность данных. (v. 17)

EXPLAIN с параметрами analyze и serialize.

1.2. Процессы и память

Экземпляр сервера PostgreSQL состоит из нескольких взаимодействующих процессов.

В первую очередь при старте сервера запускается процесс postgres, традиционно называемый postmaster. Postmaster запускает все остальные процессы (в Unix-подобных системах для этого используется системный вызов fork) и «присматривает» за ними — если какой-нибудь процесс завершится аварийно, postmaster перезапустит его (или весь сервер, если сочтет, что процесс мог повредить общие данные).

Работу сервера обеспечивает ряд служебных процессов. Основные из них:

startup восстанавливает систему после сбоев; (с. 223)

autovacuum очищает таблицы и индексы от неактуальных данных; (с. 138)

wal writer записывает на диск журнальные записи; (с. 236)

checkpointer выполняет контрольную точку; (с. 219)

writer записывает грязные страницы на диск; (с. 226)

wal sender передает журнальные записи на реплику;

wal receiver принимает журнальные записи на реплике.

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

Чтобы процессы могли обмениваться информацией, postmaster выделяет общую память, которая доступна всем процессам.

Из-за того, что диски (особенно HDD, но и SSD тоже) работают значительно медленнее, чем оперативная память, применяется кеширование: в общей области оперативной памяти отводится место под недавно прочитанные страницы в надежде, что они еще не раз понадобятся и можно будет сэкономить на повторном обращении к диску. Измененные данные также записываются на диск не сразу, а через некоторое время. (с. 183)

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

Свой кеш имеется и у операционной системы. PostgreSQL не использует (почти) прямой ввод-вывод в обход механизмов операционной системы, поэтому кеширование получается двойным.

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

в разные моменты времени. Чтобы иметь возможность восстановить согласованность данных, в процессе работы PostgreSQL ведет журнал предзаписи (WAL), позволяющий при необходимости выполнить потерянные операции повторно. (с. 211)

1.3. Клиенты и клиент-серверный протокол

Еще одна задача процесса postmaster — слушать входящие соединения. При появлении нового клиента postmaster порождает для него обслуживающий процесс ¹² (backend). Клиент устанавливает соединение и начинает сеанс общения со своим серверным процессом. Сеанс продолжается до отключения клиента или разрыва связи.

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

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

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

• Чем больше запущено процессов, тем больше времени требуется на просмотр их списка, а эта операция выполняется очень часто. В результате с увеличением числа клиентов производительность может падать. (с. 99)

В таких случаях используют пул соединений, чтобы ограничить число обслуживающих процессов. PostgreSQL не имеет встроенного пула соединений, поэтому приходится применять сторонние решения: менеджеры пулов, встроенные в сервер приложений, или внешние программы (такие как PgBouncer ¹³ или Odyssey ¹⁴). При этом, как правило, один процесс на сервере поочередно выполняет транзакции разных клиентов. Это накладывает определенные ограничения на разработку приложений, позволяя использовать средства, которые локализованы в пределах транзакции, но не сеанса.

Чтобы клиент и сервер понимали друг друга, они должны использовать один и тот же протокол взаимодействия ¹⁵. Обычно для реализации протокола используют штатную библиотеку libpq, хотя встречаются и независимые реализации.

Говоря в самых общих чертах, протокол позволяет клиенту подключиться к серверу и выполнять SQL-запросы.

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

SQL-запросы передаются обслуживающему процессу в текстовом виде. Процесс разбирает текст, оптимизирует запрос, выполняет его и возвращает результат клиенту.

Часть I. Изоляция и многоверсионность

2 Изоляция

2.1. Согласованность

Важная особенность реляционных СУБД — обеспечение согласованности (consistency), то есть корректности данных.

Известно, что на уровне базы данных можно создавать ограничения целостности (integrity constraints), такие как NOT NULL или UNIQUE. СУБД следит за тем, чтобы данные никогда не нарушали эти ограничения, то есть оставались целостными.

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

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

Но если приложение выполняет только корректные последовательности операторов, в чем тогда роль СУБД?

Во-первых, корректная последовательность операторов может временно нарушать согласованность данных, и это, как ни странно, нормально.

Заезженный, но понятный пример состоит в переводе средств с одного счета на другой. Правило согласованности может звучать так: перевод никогда не меняет общей суммы денег на счетах. Такое правило довольно трудно (хотя и возможно) записать на SQL в виде ограничения целостности, так что пусть оно существует на уровне приложения и остается невидимым для СУБД. Перевод состоит из двух операций: первая уменьшает средства на одном счете, вторая — увеличивает на другом. Первая операция нарушает согласованность данных, вторая — восстанавливает.

Если первая операция выполнится, а вторая — по причине какого-то сбоя — нет, то согласованность нарушится. А это недопустимо. Ценой неимоверных усилий такие ситуации можно обрабатывать на уровне приложения, но, к счастью, это не требуется. Задачу полностью решает СУБД, если знает, что две операции составляют неделимое целое, то есть транзакцию.

Но есть и второй, более тонкий момент. Транзакции, абсолютно правильные сами по себе, при одновременном выполнении могут начать работать некорректно. Это происходит из-за того, что перемешивается порядок выполнения операций разных транзакций. Если бы СУБД сначала выполняла все операции одной транзакции, а только потом — все операции другой, такой проблемы не возникало бы, но без распараллеливания работы производительность была бы невообразимо низкой.

полнение.

Ситуации, когда корректные транзакции некорректно работают вместе, называются аномалиями одновременного выполнения.

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

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

Таким образом, транзакцией называется множество операций, которые переводят базу данных из одного корректного состояния в другое корректное состояние (согласованность) при условии, что транзакция выполнена полностью (атомарность) и без помех со стороны других транзакций (изоляция). Это определение объединяет требования, стоящие за первыми тремя буквами акронима ACID: Atomicity, Consistency, Isolation. Они настолько тесно связаны друг с другом, что рассматривать их по отдельности просто нет смысла. На самом деле сложно отделить и требование долговечности (Durability), ведь при крахе системы в ней остаются изменения незафиксированных транзакций, а с ними приходится что-то делать, чтобы восстановить согласованность данных. (с. 211)

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

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

2.2. Уровни изоляции и аномалии в стандарте SQL

Стандарт SQL описывает четыре уровня изоляции¹. Эти уровни определяются перечислением аномалий, которые допускаются или не допускаются при одновременном выполнении транзакций. Поэтому разговор об уровнях придется начать с аномалий.

Стоит иметь в виду, что стандарт — некое теоретическое построение, которое влияет на практику, но с которым практика в то же время сильно расходится. Поэтому все примеры здесь умозрительные. Они будут использовать операции над счетами клиентов: это довольно наглядно, хотя, надо признать, не имеет ни малейшего отношения к тому, как банковские операции устроены в действительности.

Интересно, что со стандартом SQL расходится и настоящая теория баз данных², развившаяся уже после того, как стандарт был принят, а практика успела уйти вперед.

Потерянное обновление

Аномалия потерянного обновления (lost update) возникает, когда две транзакции читают одну и ту же строку таблицы, затем одна из них обновляет эту строку, после чего вторая обновляет эту же строку, не учитывая изменений, сделанных первой транзакцией.

Например, две транзакции собираются увеличить сумму на одном и том же счете на 100 ₽. Первая транзакция читает текущее значение (1000 ₽), затем вторая транзакция читает то же самое значение. Первая увеличивает сумму (получается 1100 ₽) и записывает в базу это новое значение. Вторая поступает так же: получает те же 1100 ₽ и записывает их. В результате клиент потерял 100 ₽.

Потерянное обновление не допускается стандартом ни на одном уровне изоляции.

Грязное чтение и Read Uncommitted

Аномалия грязного чтения (dirty read) возникает, когда транзакция читает еще не зафиксированные изменения, сделанные другой транзакцией.

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

Грязное чтение допускается стандартом на уровне Read Uncommitted.

Неповторяющееся чтение и Read Committed

Аномалия неповторяющегося чтения (non-repeatable read) возникает, когда транзакция читает одну и ту же строку два раза, а в промежутке между чтениями вторая транзакция изменяет (или удаляет) эту строку и фиксирует изменения. Тогда первая транзакция получит разные результаты.

Например, пусть правило согласованности запрещает отрицательные суммы на счетах клиентов. Первая транзакция собирается уменьшить сумму на счете на 100 ₽. Она проверяет текущее значение, получает 1000 ₽ и решает, что уменьшение возможно. В это время вторая транзакция уменьшает сумму на счете до нуля и фиксирует изменения. Если бы теперь первая транзакция повторно проверила сумму, она получила бы 0 ₽ (но она уже приняла решение уменьшить значение, и счет «уходит в минус»).

Неповторяющееся чтение допускается стандартом на уровнях Read Uncommitted и Read Committed.

Фантомное чтение и Repeatable Read

Аномалия фантомного чтения (phantom read) возникает, когда одна транзакция два раза читает набор строк по одинаковому условию, а в промежутке между чтениями другая транзакция добавляет строки, удовлетворяющие этому условию, и фиксирует изменения. Тогда первая транзакция получит разные наборы строк.

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

Фантомное чтение допускается стандартом на уровнях Read Uncommitted, Read Committed и Repeatable Read.

Отсутствие аномалий и Serializable

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

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

В качестве иллюстрации приведу известную всем таблицу из стандарта, к которой для ясности добавлен последний столбец:

Почему именно эти аномалии?

Почему из множества возможных аномалий в стандарте перечислены только несколько, и почему именно такие?

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

Кроме того, предполагалось, что изоляция должна быть построена на блокировках. Идея широко применявшегося протокола двухфазного блокирования (2PL) состоит в том, что в процессе выполнения транзакция блокирует затронутые строки, а при завершении — освобождает блокировки. Сильно упрощая: чем больше блокировок захватывает транзакция, тем лучше она изолирована от других транзакций. Но и тем сильнее страдает производительность системы, поскольку вместо совместной работы транзакции начинают выстраиваться в очередь за одними и теми же строками.

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

Если транзакция блокирует изменяемые строки для изменений, но не для чтения, получаем уровень Read Uncommitted с возможностью прочитать незафиксированные данные.

Если изменяемые строки блокируются и для чтения, и для изменений, получаем уровень Read Committed: незафиксированные данные прочитать нельзя, но при повторном обращении к строке можно получить другое значение (неповторяющееся чтение).

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

Но с Serializable проблема: невозможно заблокировать строку, которой еще нет. Из-за этого остается возможность фантомного чтения: другая транзакция может добавить строку, попадающую под условия выполненного ранее запроса, и эта строка окажется в повторной выборке.

Поэтому для полной изоляции обычных блокировок не хватает — нужно блокировать не строки, а условия (предикаты). Такие предикатные блокировки были предложены еще в 1976 году при работе над System R, но их практическая применимость ограничена достаточно простыми условиями, для которых понятно, конфликтуют ли два разных предиката. До реализации предикатных блокировок в задуманном виде дело, насколько мне известно, не дошло ни в одной системе. (с. 298)

2.3. Уровни изоляции в PostgreSQL

Со временем на смену блокировочным протоколам управления транзакциями пришел протокол изоляции на основе снимков (Snapshot Isolation, SI). Его идея состоит в том, что каждая транзакция работает с согласованным снимком данных на определенный момент времени. В снимок попадают все актуальные изменения, зафиксированные до момента его создания.

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

В PostgreSQL реализован многоверсионный вариант протокола SI. Многоверсионность подразумевает, что в СУБД в один момент времени могут сосуществовать несколько версий одной и той же строки. Это позволяет включать в снимок подходящую версию, а не обрывать транзакции, пытающиеся прочитать устаревшие данные.

За счет использования снимков данных изоляция в PostgreSQL отличается от той, что требует стандарт, и в целом она строже. Грязное чтение не допускается по определению. Формально в PostgreSQL можно указать уровень Read Uncommitted, но работать он будет точно так же, как Read Committed, поэтому дальше я вообще не буду говорить про этот уровень. Уровень Repeatable Read не допускает не только неповторяющегося, но и фантомного чтения (хотя и не обеспечивает полную изоляцию). Правда, на уровне Read Committed можно в ряде случаев потерять изменения. (с. 161)

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

Для этого создадим таблицу счетов. У Алисы и Боба по 1000 ₽, но у Боба открыто два счета:

=> CREATE TABLE accounts(
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  client text,
  amount numeric
);
=> INSERT INTO accounts VALUES
  (1, 'alice', 1000.00),
  (2, 'bob', 100.00),
  (3, 'bob', 900.00);

Read Committed

Отсутствие грязного чтения. Легко убедиться в том, что грязные данные прочитать невозможно. Начнем транзакцию. По умолчанию она использует уровень изоляции Read Committed ³:

=> BEGIN;
=> SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)

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

=> SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 row)

Итак, в открытой транзакции снимаем средства со счета, но не фиксируем изменения. Свои собственные изменения транзакция всегда видит:

=> UPDATE accounts SET amount = amount - 200 WHERE id = 1;
=> SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+--------
  1 | alice  | 800.00
(1 row)

Во втором сеансе начинаем еще одну транзакцию с тем же уровнем Read Committed:

=> BEGIN;
=> SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+---------
  1 | alice  | 1000.00
(1 row)

Как и ожидалось, другая транзакция не видит незафиксированные изменения — грязное чтение не допускается.

Неповторяющееся чтение. Пусть теперь первая транзакция зафиксирует изменения, а вторая повторно выполнит тот же самый запрос:

=> COMMIT;

=> SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+--------
  1 | alice  | 800.00
(1 row)
=> COMMIT;

Запрос получает уже новые данные — это и есть аномалия неповторяющегося чтения, которая допускается на уровне Read Committed.

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

IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
  UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
END IF;

За время, которое проходит между проверкой и обновлением, другие транзакции могут как угодно изменить состояние счета, так что такая «проверка» ни от чего не спасает. Удобно представлять себе, что между операторами одной транзакции могут «вклиниться» произвольные операторы других транзакций, например вот так:

IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN

UPDATE accounts SET amount = amount - 200 WHERE id = 1;
COMMIT;

  UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
END IF;

Если, переставив операторы, можно все испортить, значит, код написан некорректно. Не стоит обманывать себя, что с таким стечением обстоятельств мы не столкнемся: если неприятность может случиться, она произойдет обязательно. А вот воспроизводить и, следовательно, исправлять такие ошибки очень сложно.

Как написать код корректно? Есть несколько возможностей:

• Заменить процедурный код декларативным.

Например, в данном случае проверка легко превращается в ограничение целостности:

ALTER TABLE accounts ADD CHECK amount >= 0;

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

• Использовать один оператор SQL.

Проблемы с согласованностью возникают из-за того, что в промежутке между операторами может завершиться другая транзакция, и видимые данные изменятся. Если оператор один, то и промежутков никаких нет.

В PostgreSQL достаточно средств, чтобы одним SQL-оператором решать сложные задачи. Отмечу общие табличные выражения (CTE), в которых в том числе можно использовать операторы INSERT, UPDATE, DELETE, MERGE, а также оператор INSERT ON CONFLICT, который атомарно реализует логику «вставить, а если строка уже есть, то обновить». (v. 15)

• Задействовать пользовательские блокировки.

Последнее средство — вручную установить исключительную блокировку на все нужные строки (SELECT FOR UPDATE) или вообще на всю таблицу (LOCK TABLE). Это всегда работает, но сводит на нет преимущества многоверсионности: вместо одновременного выполнения часть операций будет выполняться последовательно. (с. 268) (с. 261)

Несогласованное чтение. Однако не все так просто. Реализация PostgreSQL такова, что допускает другие, менее известные аномалии, которые не регламентируются стандартом.

Допустим, первая транзакция начала перевод средств с одного счета Боба на другой:

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 2;

В это время другая транзакция подсчитывает баланс Боба, причем подсчет выполняется в цикле по всем счетам Боба. Фактически транзакция начинает с первого счета (и, разумеется, видит прежнее состояние):

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 2;
 amount
--------
 100.00
(1 row)

В этот момент первая транзакция успешно завершается:

=> UPDATE accounts SET amount = amount + 100 WHERE id = 3;
=> COMMIT;

А другая читает состояние второго счета (и видит уже новое значение):

=> SELECT amount FROM accounts WHERE id = 3;
 amount
---------
 1000.00
(1 row)
=> COMMIT;

В итоге вторая транзакция получила в сумме 1100 ₽, то есть прочитала некорректные данные. Такая аномалия называется несогласованным чтением (read skew).

Как избежать этой аномалии, оставаясь на уровне Read Committed? Конечно, использовать один оператор. Например, так:

SELECT sum(amount) FROM accounts WHERE client = 'bob';

До сих пор я утверждал, что видимость данных может поменяться только между операторами, но так ли это очевидно? А если запрос выполняется долго, может ли он увидеть часть данных в одном состоянии, а часть — уже в другом?

Проверим. Удобный способ для этого — вставить в оператор искусственную задержку, вызвав функцию pg_sleep. Первая строка будет прочитана сразу, а вторая — через две секунды:

=> SELECT amount, pg_sleep(2) -- 2 секунды
FROM accounts WHERE client = 'bob';

Пока эта конструкция выполняется, в другой транзакции переводим средства обратно:

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100 WHERE id = 2;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 3;
=> COMMIT;

Результат показывает, что оператор видит данные в таком состоянии, в каком они находились на момент начала его выполнения, что, безусловно, правильно:

 amount  | pg_sleep
---------+----------
    0.00 |
 1000.00 |
(2 rows)

Но и тут не все так просто. Если в запросе вызывается изменчивая функция (с категорией изменчивости VOLATILE) и в этой функции выполняется другой запрос, то этот вложенный запрос будет видеть данные, не согласованные с данными основного запроса.

Проверим состояние счетов Боба, используя функцию:

=> CREATE FUNCTION get_amount(id integer) RETURNS numeric
AS $$
  SELECT amount FROM accounts a WHERE a.id = get_amount.id;
$$ VOLATILE LANGUAGE sql;
=> SELECT get_amount(id), pg_sleep(2)
FROM accounts WHERE client = 'bob';

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

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100 WHERE id = 2;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 3;
=> COMMIT;

В этом случае получим несогласованные данные — 100 ₽ пропали:

 get_amount | pg_sleep
------------+----------
     100.00 |
     800.00 |
(2 rows)

Подчеркну, что такой эффект возможен только на уровне изоляции Read Committed и только с категорией изменчивости VOLATILE. Беда в том, что по умолчанию используется именно этот уровень изоляции и именно эта категория изменчивости, так что остается признать — грабли лежат очень удачно.

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

Посмотрим, что происходит при попытке изменения одной и той же строки двумя транзакциями. Сейчас у Боба 1000 ₽ на двух счетах:

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | bob    | 800.00
(2 rows)

Начинаем транзакцию, которая уменьшает баланс Боба:

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 3;

В это же время другая транзакция начисляет проценты на все счета с общим балансом, равным или превышающим 1000 ₽:

=> UPDATE accounts SET amount = amount * 1.01
WHERE client IN (
  SELECT client
  FROM accounts
  GROUP BY client
  HAVING sum(amount) >= 1000
);

Выполнение оператора UPDATE состоит из двух частей. Сначала фактически выполняется оператор SELECT, который отбирает для обновления строки, соответствующие условию. Поскольку изменение первой транзакции не зафиксировано, вторая транзакция не может его видеть, и оно никак не влияет на выбор строк для начисления процентов. Таким образом, счета Боба попадают под условие, и после выполнения обновления его баланс должен увеличиться на 10 ₽.

На втором этапе выполнения выбранные строки обновляются одна за другой. Вторая транзакция вынуждена подождать, поскольку строка id = 3 в настоящий момент изменяется первой транзакцией и поэтому заблокирована.

Между тем первая транзакция фиксирует изменения:

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client |  amount
----+--------+----------
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(2 rows)

Да, с одной стороны, команда UPDATE не должна видеть изменений второй транзакции. Но с другой — она не должна потерять изменения, зафиксированные во второй транзакции.

После снятия блокировки оператор UPDATE перечитывает строку, которую пытается обновить (но только ее одну!). В результате получается, что Бобу начислено 9 ₽, исходя из суммы 900 ₽. Но если бы у Боба было 900 ₽, его счета вообще не должны были попасть в выборку. (с. 279)

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

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

Вот пример, который уже был. Приложение читает и запоминает (вне базы данных) текущий баланс счета Алисы: (с. 50)

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 800.00
(1 row)

В это время другая транзакция действует так же:

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 800.00
(1 row)

Первая транзакция увеличивает запомненное ранее значение на 100 ₽ и записывает в базу:

=> UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;
 amount
--------
 900.00
(1 row)
UPDATE 1
=> COMMIT;

И вторая транзакция тоже:

=> UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;
 amount
--------
 900.00
(1 row)
UPDATE 1
=> COMMIT;

К сожалению, Алиса недосчиталась 100 ₽. СУБД ничего не знает о том, что запомненное значение 800 ₽ как-то связано с accounts.amount, и допускает аномалию потерянного изменения. На уровне изоляции Read Committed такой код некорректен.

Repeatable Read

Отсутствие неповторяющегося и фантомного чтения. Само название уровня изоляции Repeatable Read ⁴ говорит о повторяемости чтения. Проверим это, а заодно убедимся и в отсутствии фантомов. Для этого в первой транзакции вернем счета Боба в прежнее состояние и создадим новый счет для Чарли:

=> BEGIN;
=> UPDATE accounts SET amount = 200.00 WHERE id = 2;
=> UPDATE accounts SET amount = 800.00 WHERE id = 3;
=> INSERT INTO accounts VALUES
  (4, 'charlie', 100.00);
=> SELECT * FROM accounts ORDER BY id;
 id | client  | amount
----+---------+--------
  1 | alice   | 900.00
  2 | bob     | 200.00
  3 | bob     | 800.00
  4 | charlie | 100.00
(4 rows)

Во втором сеансе начнем транзакцию с уровнем Repeatable Read, указав его в команде BEGIN (уровень первой транзакции не важен):

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT * FROM accounts ORDER BY id;
 id | client |  amount
----+--------+----------
  1 | alice  |   900.00
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(3 rows)

Теперь первая транзакция фиксирует изменения, а вторая повторно выполняет тот же самый запрос:

=> COMMIT;

=> SELECT * FROM accounts ORDER BY id;
 id | client |  amount
----+--------+----------
  1 | alice  |   900.00
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(3 rows)
=> COMMIT;

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

Ошибка сериализации вместо потерянных изменений. Как мы уже видели, на уровне изоляции Read Committed при обновлении одной и той же строки двумя транзакциями может возникнуть аномалия несогласованного чтения. Это происходит из-за того, что ожидающая транзакция перечитывает заблокированную строку и видит ее на один момент времени, а остальные строки выборки — на другой. (с. 61)

На уровне Repeatable Read такая аномалия не допускается, но, если она все-таки возникает, сделать уже ничего нельзя — поэтому транзакция обрывается с ошибкой сериализации. Проверим, повторив тот же сценарий с процентами:

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | bob    | 800.00
(2 rows)

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> UPDATE accounts SET amount = amount * 1.01
WHERE client IN (
  SELECT client
  FROM accounts
  GROUP BY client
  HAVING sum(amount) >= 1000
);

=> COMMIT;
    ERROR:  could not serialize access due to concurrent update
    => ROLLBACK;

Данные остались согласованными:

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | bob    | 700.00
(2 rows)

Такая же ошибка будет и в случае любого другого конкурентного изменения строки, даже если оно не затрагивает интересующие нас столбцы.

Можно проверить, что и сценарий с приложениями, обновляющими баланс на основе запомненного значения, приводит к той же ошибке:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 900.00
(1 row)

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 900.00
(1 row)

=> UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1
RETURNING amount;
 amount
---------
 1000.00
(1 row)
UPDATE 1
=> COMMIT;
    => UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1
    RETURNING amount;
    ERROR:  could not serialize access due to concurrent update
    => ROLLBACK;

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

Несогласованная запись. Итак, в PostgreSQL на уровне изоляции Repeatable Read предотвращаются все аномалии, описанные в стандарте. Но не все вообще — науке до сих пор неизвестно, сколько их существует. Однако доказан важный факт: изоляция на основе снимков оставляет возможными ровно две аномалии, сколько бы их ни было всего.

Первая из этих аномалий — несогласованная запись (write skew).

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

Первая транзакция получает сумму на счетах Боба:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
  sum
--------
 900.00
(1 row)

Вторая транзакция получает ту же сумму:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
  sum
--------
 900.00
(1 row)

Первая транзакция справедливо полагает, что сумму одного из счетов можно уменьшить на 600 ₽:

=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;

И вторая транзакция приходит к такому же выводу. Но уменьшает другой счет:

=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
=> COMMIT;

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+---------
  2 | bob    | -400.00
  3 | bob    |  100.00
(2 rows)

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

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

Но сначала восстановим состояние счетов Боба:

=> UPDATE accounts SET amount = 900.00 WHERE id = 2;
=> SELECT * FROM accounts WHERE client = 'bob';

 id | client | amount
----+--------+--------
  3 | bob    | 100.00
  2 | bob    | 900.00
(2 rows)

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

=> BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1
=> UPDATE accounts SET amount = amount + (
  SELECT sum(amount) FROM accounts WHERE client = 'bob'
) * 0.01
WHERE id = 2;

Затем другая транзакция снимает деньги с другого счета Боба и фиксирует свои изменения:

=> BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
=> COMMIT;

Если в этот момент первая транзакция будет зафиксирована, никакой аномалии не возникнет: мы могли бы считать, что сначала выполнена первая транзакция, а затем вторая (но не наоборот, потому что первая транзакция увидела состояние счета id = 3 до того, как этот счет был изменен второй транзакцией).

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

=> SELECT * FROM accounts WHERE client = 'bob'; -- 3
 id | client | amount
----+--------+--------
  2 | bob    | 900.00
  3 | bob    |   0.00
(2 rows)

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

=> COMMIT;

У Боба на счетах всего 900 ₽ (что показала только читающая транзакция), а проценты оказались начисленными на все 1000 ₽:

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client |  amount
----+--------+----------
  2 | bob    | 910.0000
  3 | bob    |     0.00
(2 rows)

Serializable

На уровне Serializable ⁵ предотвращаются все возможные аномалии. Фактически Serializable реализован как надстройка над изоляцией на основе снимков данных. Те аномалии, которые не возникают при Repeatable Read (такие как грязное, неповторяемое, фантомное чтение), не возникают и на уровне Serializable. А те две аномалии, которые возникают (несогласованная запись и аномалия только читающей транзакции), специальным образом обнаруживаются, и в этом случае транзакция обрывается: возникает уже знакомая нам ошибка сериализации.

Отсутствие аномалий. Можно убедиться, что сценарий с аномалией несогласованной записи приведет к ошибке сериализации: (с. 67)

=> BEGIN ISOLATION LEVEL SERIALIZABLE;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
   sum
----------
 910.0000
(1 row)

=> BEGIN ISOLATION LEVEL SERIALIZABLE;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
   sum
----------
 910.0000
(1 row)

=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;
    => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
    => COMMIT;
    COMMIT

=> COMMIT;
ERROR:  could not serialize access due to read/write dependencies among
transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT:  The transaction might succeed if retried.

К такой же ошибке приведет и сценарий аномалии только читающей транзакции.

Откладывание читающей транзакции. Чтобы только читающая транзакция не приводила к аномалии и не могла пострадать от нее, PostgreSQL предлагает интересный механизм: такая транзакция может быть отложена до тех пор, пока ее выполнение не станет безопасным. Это единственный случай, когда оператор SELECT может быть заблокирован обновлениями строк.

Посмотрим на примере сценария аномалии только читающей транзакции:

=> UPDATE accounts SET amount = 900.00 WHERE id = 2;
=> UPDATE accounts SET amount = 100.00 WHERE id = 3;
=> SELECT * FROM accounts WHERE client = 'bob' ORDER BY id;
 id | client | amount
----+--------+--------
  2 | bob    | 900.00
  3 | bob    | 100.00
(2 rows)

=> BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1
=> UPDATE accounts SET amount = amount + (
  SELECT sum(amount) FROM accounts WHERE client = 'bob'
) * 0.01
WHERE id = 2;
    => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2
    => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
    => COMMIT;

Третью транзакцию явно объявляем только читающий (READ ONLY) и откладываемой (DEFERRABLE):

=> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3
=> SELECT * FROM accounts WHERE client = 'alice';

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

И только после того, как первая транзакция будет зафиксирована, третья продолжит выполнение:

=> COMMIT;

 id | client | amount
----+--------+---------
  1 | alice  | 1000.00
(1 row)
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client |  amount
----+--------+----------
  2 | bob    | 910.0000
  3 | bob    |     0.00
(2 rows)
=> COMMIT;

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

Уровень Serializable дает простоту программирования, но цена за нее — накладные расходы на обнаружение возможных аномалий и обрыв некоторой доли транзакций. Снизить накладные расходы можно, явно обозначая только читающие транзакции как READ ONLY. Но главный вопрос, конечно, в том, насколько велика доля оборванных транзакций, ведь их придется выполнять повторно. Если бы обрывались только те транзакции, которые действительно несовместимо пересекаются по данным, все было бы неплохо. Но такая реализация неизбежно оказалась бы слишком ресурсоемкой, поскольку пришлось бы отслеживать операции с каждой строкой.

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

Если используется уровень изоляции Serializable, он должен применяться для всех транзакций приложения. При смешении транзакций разного уровня изоляции уровень Serializable будет (без всяких предупреждений) вести себя как Repeatable Read. Поэтому при использовании Serializable имеет смысл изменить значение параметра default_transaction_isolation, хотя, конечно, это не помешает указать неправильный уровень явно. (по умолчанию: read committed)

Есть и другие ограничения реализации, например запросы на уровне Serializable не будут работать на репликах. И хотя работа над улучшением функциональности не прекращается, имеющиеся ограничения и накладные расходы снижают привлекательность такого уровня изоляции. (v. 12)

2.4. Какой уровень изоляции использовать?

Уровень изоляции Read Committed используется в PostgreSQL по умолчанию, и, по всей видимости, именно этот уровень применяется в абсолютном большинстве приложений. Он удобен тем, что на нем обрыв транзакции возможен только в случае сбоя, но для предотвращения несогласованности обрыв не применяется. Иными словами, ошибка сериализации возникнуть не может, и о повторении транзакций заботиться не надо.

Обратная сторона медали — большое число возможных аномалий, подробно рассмотренных выше. Разработчик вынужден постоянно иметь их в виду и писать код так, чтобы не допускать их появления. Если не получается сформулировать нужные действия в одном SQL-операторе, приходится прибегать к явной установке блокировок. Самое неприятное то, что код сложно тестировать на наличие ошибок, связанных с получением несогласованных данных, а сами ошибки могут возникать непредсказуемым и невоспроизводимым образом и поэтому сложны в исправлении.

Уровень изоляции Repeatable Read снимает часть проблем несогласованности, но, увы, не все. Поэтому приходится не только помнить об оставшихся аномалиях, но и изменять приложение так, чтобы оно корректно обрабатывало ошибки сериализации. Это, конечно, неудобно. Но для только читающих транзакций этот уровень прекрасно дополняет Read Committed и полезен, например, для построения отчетов, использующих несколько SQL-запросов.

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

3 Страницы и версии строк

3.1. Структура страниц

Каждая страница имеет внутреннюю разметку и, как правило, содержит следующие разделы ¹:

• заголовок;

• массив указателей на версии строк;

• свободное пространство;

• версии строк;

• специальную область.

Заголовок страницы

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

Размеры легко получить с помощью расширения pageinspect². Заглянем в самую первую страницу таблицы (нумерация начинается с нуля):

=> CREATE EXTENSION pageinspect;
=> SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('accounts',0));
 lower | upper | special | pagesize
-------+-------+---------+----------
   152 |  6904 |    8192 |     8192
(1 row)

Специальная область

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

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

Версии строк

Перед специальной областью располагаются строки (rows) — те самые данные, которые хранятся в базе, с добавлением некоторой служебной информации.

В случае таблиц мы говорим не просто о строках, а о версиях строк (row versions, tuples), поскольку многоверсионность предполагает существование нескольких версий одной и той же строки. На индексы многоверсионность не распространяется; вместо этого индексы ссылаются на все возможные табличные версии строк, среди которых по правилам видимости выбираются подходящие.

Указатели на версии строк

Массив указателей на версии строк служит оглавлением страницы. Он располагается сразу за заголовком.

Индексные строки должны как-то ссылаться на версии строк в таблице. Для этого используются шестибайтные идентификаторы версий строк (tuple id, tid). Идентификатор состоит из номера страницы в файле основного слоя и должен еще содержать какое-то указание на версию строки в этой странице. (с. 29)

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

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

Каждый указатель занимает ровно четыре байта и содержит:

• смещение версии строки относительно начала страницы;

• длину версии строки;

• несколько битов, определяющих статус версии строки.

Свободное место

Между указателями и версиями строк может оставаться свободное место (которое и отмечено в карте свободного пространства). Никакой фрагментации внутри страницы не бывает, все свободное место всегда представлено одним фрагментом ³. (с. 31)

3.2. Структура версий строк

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

xmin, xmax — номера транзакций, которые отличают данную версию от других версий той же строки;

infomask — ряд информационных битов, определяющих свойства версии;

ctid — ссылка на следующую, более новую версию той же строки;

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

В результате заголовок получается довольно большой — минимум 23 байта на каждую версию строки, а обычно больше из-за карты неопределенных значений и из-за обязательного выравнивания начала данных. Для «узкой» таблицы объем служебных данных вполне может превышать объем полезной информации.

Формат данных на диске полностью совпадает с представлением данных в оперативной памяти. Страница вместе с версиями строк читается в буферный кеш «как есть», без каких бы то ни было преобразований. Поэтому файлы данных с одной платформы оказываются несовместимыми с другими платформами⁴.

Одна из причин несовместимости — порядок следования байтов. Например, в архитектуре x86 принят порядок от младших разрядов к старшим (little-endian), z/Architecture использует обратный порядок (big-endian), а в ARM порядок переключаемый.

Несовместимость вызывается также выравниванием данных по границам машинных слов, которое требуется многим архитектурам. Например, в 32-битной системе архитектуры x86 целые числа (тип integer, занимает четыре байта) будут выровнены по границе четырехбайтных слов, как и числа с плавающей точкой двойной точности (тип double precision, восемь байт). А в 64-битной системе значения double будут выровнены по границе восьмибайтных слов.

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

=> CREATE TABLE padding(
  b1 boolean,
  f1 float,
  b2 boolean,
  f2 float
);
=> INSERT INTO padding VALUES (true,1.0,false,2.0);
=> SELECT lp_len FROM heap_page_items(get_raw_page('padding', 0));
 lp_len
--------
     56
(1 row)

Я использовал функцию heap_page_items расширения pageinspect, которая позволяет получить информацию об указателях и версиях строк.

Строка занимает 56 байт. Из них 24 байта уходит на заголовок, столбцы типа float занимают по 8 байт, boolean — по 1 байту. В сумме имеем 42, а 14 байт пропадают из-за выравнивания float по границе восьмибайтных слов. Перестроив таблицу, можно использовать место более эффективно:

=> DROP TABLE padding;
=> CREATE TABLE padding(
  f1 float,
  f2 float,
  b1 boolean,
  b2 boolean
);

Надо, однако, учитывать, что выравниваются и версии строк внутри страницы. Как и предполагалось, строки стали занимать 42 байта, но 6 байт все-таки пропадают между строками:

=> INSERT INTO padding VALUES (1.0,2.0,true,false);
=> SELECT lp_len, 8192 - lp_off AS allocated, lp_off
FROM heap_page_items(get_raw_page('padding', 0));
 lp_len | allocated | lp_off
--------+-----------+--------
     42 |        48 |   8144
(1 row)

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

3.3. Выполнение операций над версиями строк

Чтобы разные версии одной и той же строки можно было различить, каждая из версий имеет две отметки, определяющие ее «время действия», — xmin и xmax. Но используется не время как таковое, а постоянно увеличивающийся счетчик номеров транзакций. (с. 134)

Когда строка создается, значение xmin устанавливается равным номеру транзакции, выполнившей команду INSERT.

Когда строка удаляется, значению xmax текущей версии присваивается номер транзакции, выполнившей команду DELETE.

Команду UPDATE можно в некотором приближении рассматривать как две операции: DELETE и INSERT. В текущей версии строки устанавливается значение xmax, равное номеру транзакции, выполнившей UPDATE. Затем создается новая версия той же строки; значение xmin у нее совпадает со значением xmax предыдущей версии. Точно так же и команда MERGE «распадается» на элементарные вставки и удаления. (v. 15)

Дальше я подробно покажу, как выполняются разные операции с версиями строк на низком уровне⁶.

Для экспериментов понадобится таблица с двумя столбцами и индексом по одному из них:

=> CREATE TABLE t(
  id integer GENERATED ALWAYS AS IDENTITY,
  s text
);
=> CREATE INDEX ON t(s);

Вставка

Вставляем одну строку, предварительно начав транзакцию:

=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');

Вот номер нашей текущей транзакции:

=> --  txid_current() до v.13
SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                795
(1 row)

Заглянем в содержимое страницы. Функция heap_page_items дает нам всю необходимую информацию, но показывает данные «как есть», в формате, сложном для восприятия:

=> SELECT *
FROM heap_page_items(get_raw_page('t',0))
\gx
-[ RECORD 1 ]-------------------
lp          | 1
lp_off      | 8160
lp_flags    | 1
lp_len      | 32
t_xmin      | 795
t_xmax      | 0
t_field3    | 0
t_ctid      | (0,1)
t_infomask2 | 2
t_infomask  | 2050
t_hoff      | 24
t_bits      |
t_oid       |
t_data      | \x0100000009464f4f

Чтобы разобраться, оставим только часть информации и расшифруем некоторые столбцы:

=> SELECT '(0,'||lp||')' AS ctid,
     CASE lp_flags
       WHEN 0 THEN 'unused'
       WHEN 1 THEN 'normal'
       WHEN 2 THEN 'redirect to '||lp_off
       WHEN 3 THEN 'dead'
     END AS state,
     t_xmin as xmin,
     t_xmax as xmax,
     (t_infomask & 256) > 0  AS xmin_committed,
     (t_infomask & 512) > 0  AS xmin_aborted,
     (t_infomask & 1024) > 0 AS xmax_committed,
     (t_infomask & 2048) > 0 AS xmax_aborted
FROM heap_page_items(get_raw_page('t',0))
\gx
-[ RECORD 1 ]--+-------
ctid           | (0,1)
state          | normal
xmin           | 795
xmax           | 0
xmin_committed | f
xmin_aborted   | f
xmax_committed | f
xmax_aborted   | t

Вот что здесь сделано:

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

• Расшифровано состояние указателя lp_flags. Здесь он имеет значение normal — это значит, что указатель действительно ссылается на версию строки.

• Из всех информационных битов пока выделены только две пары. Биты xmin_committed и xmin_aborted показывают, зафиксирована ли и отменена ли транзакция с номером xmin. Аналогичную информацию о транзакции xmax дают биты xmax_committed и xmax_aborted.

В расширении pageinspect есть функция heap_tuple_infomask_flags, расшиф- (v. 13)

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

Поле xmin в версии строки заполнено номером текущей транзакции. Транзакция еще активна, поэтому биты xmin_committed и xmin_aborted не установлены.

Поле xmax заполнено фиктивным номером 0, поскольку данная версия строки не удалена и является актуальной. Транзакции не будут обращать внимания на этот номер, поскольку установлен бит xmax_aborted.

Такой запрос нам еще не раз понадобится, поэтому я оберну его в функцию. А заодно для компактности вывода уберу столбцы информационных битов, приписав статус транзакции к ее номеру:

=> CREATE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
     CASE lp_flags
       WHEN 0 THEN 'unused'
       WHEN 1 THEN 'normal'
       WHEN 2 THEN 'redirect to '||lp_off
       WHEN 3 THEN 'dead'
     END AS state,
     t_xmin || CASE
       WHEN (t_infomask & 256) > 0 THEN ' c'
       WHEN (t_infomask & 512) > 0 THEN ' a'
       ELSE ''
     END AS xmin,
     t_xmax || CASE
       WHEN (t_infomask & 1024) > 0 THEN ' c'
       WHEN (t_infomask & 2048) > 0 THEN ' a'
       ELSE ''
     END AS xmax
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE sql;

В таком виде уже значительно понятнее, что творится в заголовке версии:

=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin | xmax
-------+--------+------+------
 (0,1) | normal | 795  | 0 a
(1 row)

Похожую, но существенно менее детальную информацию можно получить и из самой таблицы, используя псевдостолбцы xmin и xmax:

=> SELECT xmin, xmax, * FROM t;
 xmin | xmax | id |  s
------+------+----+-----
  795 |    0 |  1 | FOO
(1 row)

Фиксация

При успешном завершении транзакции нужно запомнить ее статус — отметить, что она зафиксирована. Для этого используется структура, называемая clog ⁷ (commit log). Это не таблица системного каталога, а специальные файлы в каталоге PGDATA/pg_xact.

На несколько файлов clog разбит исключительно для удобства. Работа с этими файлами ведется постранично, через буферы в общей памяти сервера. Размер кеша clog настраивается параметром transaction_buffers, а по умолчанию составляет $\frac{1}{512}$ размера буферного кеша. (с. 309) (v. 17)

В clog, как и в заголовке версии строки, для каждой транзакции отведено два бита: committed и aborted.

Итак, при фиксации транзакции в clog выставляется бит committed для данной транзакции. Когда какая-либо другая транзакция обратится к нашей табличной странице, ей придется ответить на вопрос: завершилась ли транзакция с номером xmin?

• Если нет, то созданная версия строки не должна быть видна.

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

• Если завершилась, то фиксацией или отменой? При отмене версия строки тоже не должна быть видна.

Для этой проверки как раз и нужна структура clog. Но хотя последние страницы clog сохраняются в буферах в оперативной памяти, все равно такую проверку накладно выполнять каждый раз. Поэтому выясненный однажды статус транзакции записывается в заголовок версии строки в информационные биты xmin_committed и xmin_aborted; их еще называют битами-подсказками (hint bits). Если один из этих битов установлен, то состояние транзакции xmin считается известным, и следующей транзакции уже не придется обращаться ни к clog, ни к ProcArray.

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

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

Зафиксируем наконец вставку строки, с которой мы начали транзакцию:

=> COMMIT;

В странице ничего не изменилось (но мы знаем, что статус транзакции уже записан в clog):

=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin | xmax
-------+--------+------+------
 (0,1) | normal | 795  | 0 a
(1 row)

Теперь транзакция, первой обратившаяся к странице («нормальным» образом, не с помощью pageinspect), должна будет определить статус транзакции xmin и записать его в информационные биты:

=> SELECT * FROM t;
 id |  s
----+-----
  1 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | normal | 795 c | 0 a
(1 row)

Удаление

При удалении строки в поле xmax актуальной версии записывается номер удаляющей транзакции, а бит xmax_aborted сбрасывается. (с. 268)

Удалим строку:

=> BEGIN;
=> DELETE FROM t;
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                796
(1 row)

Номер транзакции записался в поле xmax, но информационные биты еще не установлены:

=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | normal | 795 c | 796
(1 row)

Отмена

Отмена изменений работает аналогично фиксации и выполняется так же быстро, только в clog вместо бита committed выставляется бит aborted. Хоть команда и называется ROLLBACK, отката изменений не происходит: все, что транзакция успела изменить в страницах данных, остается на месте.

=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | normal | 795 c | 796
(1 row)

При обращении к странице проверяется статус, и в версии строки устанавливается бит-подсказка xmax_aborted. Сам номер xmax при этом остается в странице, но смотреть на него уже никто не будет:

=> SELECT * FROM t;
 id |  s
----+-----
  1 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+-------
 (0,1) | normal | 795 c | 796 a
(1 row)

Обновление

Обновление работает так, как будто сначала выполнилось удаление текущей версии строки, а затем — вставка новой:

=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                797
(1 row)

Запрос выдает одну строку (новую версию):

=> SELECT * FROM t;
 id |  s
----+-----
  1 | BAR
(1 row)

Но в странице хранятся обе версии:

=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | normal | 795 c | 797
 (0,2) | normal | 797   | 0 a
(2 rows)

Удаленная ранее версия помечена номером текущей транзакции в поле xmax. Это значение записано поверх старого, поскольку предыдущая транзакция была отменена. А бит xmax_aborted сброшен, так как статус текущей транзакции еще неизвестен.

Ну и завершим транзакцию.

=> COMMIT;

3.4. Индексы

В индексах любого типа никогда не бывает версий строк, каждая строка представлена ровно одним экземпляром. Иными словами, в заголовке индексной строки не бывает полей xmin и xmax. Ссылки из индекса ведут на все табличные версии строк. Транзакции требуется заглянуть в таблицу, чтобы разобраться, какая из версий ей видна (если только страница не отмечена в карте видимости). (с. 115)

Для удобства создадим простую функцию, показывающую с помощью расширения pageinspect все индексные записи на странице (в пределах страницы B-дерева записи располагаются в виде плоского списка): (с. 523)

=> CREATE FUNCTION index_page(relname text, pageno integer)
RETURNS TABLE(itemoffset smallint, htid tid)
AS $$
SELECT itemoffset,
       htid -- ctid до v.13
FROM bt_page_items(relname,pageno);
$$ LANGUAGE sql;

Обнаруживаем на странице указатели на обе версии табличной строки, как на актуальную, так и на старую:

=> SELECT * FROM index_page('t_s_idx',1);
 itemoffset | htid
------------+-------
          1 | (0,2)
          2 | (0,1)
(2 rows)

Поскольку BAR < FOO, указатель на вторую версию строки стоит в индексе на первом месте.

3.5. TOAST

Toast-таблица является, по сути, обычной таблицей, и для нее поддерживается собственная версионность: версии «тостов» не связаны с версиями строк основной таблицы. Но внутренняя работа с toast-таблицей построена так, что строки никогда не обновляются, а только добавляются и удаляются, так что версионность в данном случае несколько вырожденная. (с. 33)

Когда данные меняются, в основной таблице всегда создается новая версия строки. Но если обновление не затрагивает «длинное» значение, хранящееся в TOAST, то новая версия строки будет ссылаться на прежнее значение в toast-таблице. И только когда обновление поменяет «длинное» значение, будут созданы и новая версия строки в основной таблице, и новые «тосты».

3.6. Виртуальные транзакции

PostgreSQL использует оптимизацию, позволяющую «экономить» номера транзакций.

Если транзакция только читает данные, то она никак не влияет на видимость версий строк. Поэтому вначале обслуживающий процесс выдает транзакции виртуальный номер ⁸ (virtual xid). Номер состоит из идентификатора обслуживающего процесса и последовательного числа. Выдача этого номера не требует синхронизации между всеми процессами и поэтому выполняется очень быстро. Настоящего номера у транзакции еще нет: (с. 259)

=> BEGIN;
=> --  txid_current_if_assigned() до v.13
SELECT pg_current_xact_id_if_assigned();
 pg_current_xact_id_if_assigned
--------------------------------

(1 row)

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

Если же транзакция начинает менять данные, ей выдается настоящий, уникальный номер транзакции:

=> UPDATE accounts
SET amount = amount - 1.00;
=> SELECT pg_current_xact_id_if_assigned();
 pg_current_xact_id_if_assigned
--------------------------------
                            799
(1 row)
=> COMMIT;

3.7. Вложенные транзакции

Точки сохранения

В SQL определены точки сохранения (savepoint), которые позволяют отменить часть операций транзакции, не прерывая ее полностью. Но это не укладывается в приведенную выше схему, поскольку статус транзакции один на все изменения, а физически никакие данные не откатываются.

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

Вложенные транзакции имеют свой собственный номер (больший, чем номер основной транзакции). Статус вложенных транзакций записывается в clog обычным образом, но зафиксированные вложенные транзакции одновременно отмечаются двумя битами, committed и aborted. Финальный статус зависит от статуса основной транзакции: если она отменена, то и все вложенные транзакции считаются отмененными.

Информация о вложенности транзакций хранится в файлах внутри каталога PGDATA/pg_subtrans, а обращение к страницам этих файлов происходит через SLRU-кеш. Его размер настраивается параметром subtransaction_buffers, а по умолчанию составляет $\frac{1}{512}$ размера буферного кеша. (с. 309)

Опустошим таблицу, начнем транзакцию и вставим строку:

=> TRUNCATE TABLE t;
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                801
(1 row)

Теперь поставим точку сохранения и вставим еще одну строку:

=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                801
(1 row)

Заметьте, что функция pg_current_xact_id выдает номер основной, а не вложенной транзакции.

=> SELECT *
FROM heap_page('t',0) p
  LEFT JOIN t ON p.ctid = t.ctid;
 ctid  | state  | xmin | xmax | id |  s
-------+--------+------+------+----+-----
 (0,1) | normal | 801  | 0 a  |  2 | FOO
 (0,2) | normal | 802  | 0 a  |  3 | XYZ
(2 rows)

Откатимся к точке сохранения и вставим третью строку:

=> ROLLBACK TO sp;
=> INSERT INTO t(s) VALUES ('BAR');
=> SELECT *
FROM heap_page('t',0) p
  LEFT JOIN t ON p.ctid = t.ctid;
 ctid  | state  | xmin | xmax | id |  s
-------+--------+------+------+----+-----
 (0,1) | normal | 801  | 0 a  |  2 | FOO
 (0,2) | normal | 802  | 0 a  |    |
 (0,3) | normal | 803  | 0 a  |  4 | BAR
(3 rows)

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

Фиксируем изменения:

=> COMMIT;
=> SELECT * FROM t;
 id |  s
----+-----
  2 | FOO
  4 | BAR
(2 rows)
=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | normal | 801 c | 0 a
 (0,2) | normal | 802 a | 0 a
 (0,3) | normal | 803 c | 0 a
(3 rows)

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

Вложенные транзакции нельзя использовать в SQL напрямую, то есть нельзя начать новую транзакцию, не завершив текущую:

=> BEGIN;
BEGIN
=> BEGIN;
WARNING:  there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING:  there is no transaction in progress
COMMIT

Этот механизм задействуется неявно: при использовании точек сохранения, при обработке исключений PL/pgSQL и в некоторых других, более экзотических случаях.

Ошибки и атомарность операций

Что случится, если в процессе выполнения операции произойдет ошибка?

Например:

=> BEGIN;
=> SELECT * FROM t;
 id |  s
----+-----
  2 | FOO
  4 | BAR
(2 rows)
=> UPDATE t SET s = repeat('X', 1/(id-4));
ERROR:  division by zero

После сбоя транзакция считается прерванной, и больше ни одна операция в ней не допускается:

=> SELECT * FROM t;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block

И даже если попытаться зафиксировать изменения, PostgreSQL сообщит об отмене:

=> COMMIT;
ROLLBACK

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

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

=> SELECT * FROM heap_page('t',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | normal | 801 c | 804
 (0,2) | normal | 802 a | 0 a
 (0,3) | normal | 803 c | 0 a
 (0,4) | normal | 804   | 0 a
(4 rows)

К слову, в psql имеется режим, который все-таки позволяет продолжать работу транзакции после сбоя так, как будто действия ошибочного оператора откатываются:

=> \set ON_ERROR_ROLLBACK on
=> BEGIN;
=> UPDATE t SET s = repeat('X', 1/(id-4));
ERROR:  division by zero
=> SELECT * FROM t;
 id |  s
----+-----
  2 | FOO
  4 | BAR
(2 rows)
=> COMMIT;
COMMIT

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

4 Снимки данных

4.1. Что такое снимок данных

Физически в страницах данных могут находиться несколько версий одной и той же строки, хотя каждая транзакция должна видеть максимум одну из них. Все вместе версии разных строк, наблюдаемые транзакцией, образуют снимок данных (snapshot). Снимок обеспечивает согласованную в ACID-смысле картину данных на определенный момент времени и содержит только самые актуальные данные, зафиксированные к моменту его создания. (с. 54)

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

На уровне изоляции Read Committed снимок создается в начале каждого оператора транзакции и остается активным все время работы этого оператора.

На уровнях Repeatable Read и Serializable снимок создается один раз в начале первого оператора транзакции и остается активным до самого конца транзакции.

4.2. Видимость версий строк в снимке

Конечно, снимок данных не является физической копией всех необходимых версий строк. Фактически снимок задается несколькими числами, а видимость версий строк в снимке определяется правилами.

Будет данная версия строки видна в снимке или нет — зависит от полей xmin и xmax ее заголовка (то есть от номеров создавшей и удалившей транзакций) и от соответствующих этим полям информационных битов. Интервалы xmin–xmax не пересекаются, поэтому каждая строка представлена в любом снимке максимум одной своей версией.

Точные правила видимости ¹ довольно сложны и учитывают множество различных ситуаций и крайних случаев. Упрощая, можно сказать, что версия строки видна, когда в снимке видны изменения, сделанные транзакцией xmin, и не видны изменения, сделанные транзакцией xmax (иными словами, если версия строки уже появилась, но еще не удалена).

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

Вот простой пример. Транзакции изображены в виде отрезков (от момента начала до момента фиксации):

Здесь:

• изменения транзакции 2 будут видны, потому что она завершилась до создания снимка;

• изменения транзакции 1 не будут видны, потому что она была активна на момент создания снимка;

• изменения транзакции 3 не будут видны, потому что она началась позже создания снимка (не важно, закончилась она или нет).

4.3. Из чего состоит снимок

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

Узнать можно лишь текущий статус транзакций. Эта информация есть в общей памяти сервера в структуре ProcArray, которая содержит список всех активных сеансов и их транзакций. Постфактум же невозможно выяснить, была ли какая-то транзакция активна в момент создания снимка. (с. 211)

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

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

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

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

Итак, снимок данных состоит из нескольких значений, которые запоминаются в момент его создания ⁵:

Нижняя граница снимка xmin, в качестве которой выступает номер самой старой активной транзакции.

Все транзакции с меньшими номерами либо зафиксированы, и тогда их изменения видны в снимке, либо отменены, и тогда изменения игнорируются. (с. 134)

Верхняя граница снимка xmax, в качестве которой берется значение, на единицу большее номера последней зафиксированной транзакции. Верхняя граница определяет момент времени, в который был сделан снимок.

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

Список активных транзакций xip_list (xid-in-progress list), в который попадают номера всех активных транзакций (меньшие xmax), за исключением виртуальных, которые никак не влияют на видимость. (с. 91)

Также в снимке сохраняются еще несколько параметров, но они пока не так важны.

Графически можно представить снимок как прямоугольник, охватывающий транзакции от xmin до xmax:

Чтобы посмотреть, как видимость определяется снимком, воспроизведем ситуацию по показанному выше сценарию на таблице accounts.

=> TRUNCATE TABLE accounts;

Первая транзакция вставляет в таблицу первую строку и остается активной:

=> BEGIN;
=> INSERT INTO accounts VALUES (1, 'alice', 1000.00);
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                809
(1 row)

Вторая транзакция вставляет вторую строку и сразу завершается:

=> BEGIN;
=> INSERT INTO accounts VALUES (2, 'bob', 100.00);
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                810
(1 row)
=> COMMIT;

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

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> --  txid_current_snapshot() до v.13
SELECT pg_current_snapshot();
 pg_current_snapshot
---------------------
 809:811:809
(1 row)

Функция выводит через двоеточие три составляющих снимка: поля xmin, xmax и список xip_list (состоящий в данном случае из одного номера).

После того как снимок создан, завершаем первую транзакцию:

=> COMMIT;

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

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100 WHERE id = 2;
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                811
(1 row)
=> COMMIT;

В созданном снимке видна только одна версия:

=> SELECT ctid, * FROM accounts;
 ctid  | id | client | amount
-------+----+--------+--------
 (0,2) |  2 | bob    | 100.00
(1 row)

Хотя в таблице их три:

=> SELECT * FROM heap_page('accounts',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+-------
 (0,1) | normal | 809 c | 0 a
 (0,2) | normal | 810 c | 811 c
 (0,3) | normal | 811 c | 0 a
(3 rows)

Как PostgreSQL понимает, какие версии показывать? По сформулированным выше правилам в снимке видны изменения только следующих зафиксированных транзакций:

• с номерами xid < xmin — безусловно (например, транзакция, создавшая таблицу accounts);

• с номерами xmin ⩽ xid < xmax — за исключением попавших в список xip_list.

Первая строка (0,1) не видна, так как номер создавшей ее транзакции входит в список активных транзакций (хотя и попадает при этом в диапазон снимка).

Последняя версия второй строки (0,3) не видна, поскольку создана транзакцией с номером, выходящим за верхнюю границу снимка.

Зато видна первая версия второй строки (0,2): номер создавшей ее транзакции попадает в диапазон снимка, но не входит в список активных транзакций (вставка видна), и при этом номер удалившей транзакции выходит за верхнюю границу снимка (удаление не видно).

=> COMMIT;

4.4. Видимость собственных изменений

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

Для этого в заголовке версии строки есть специальное поле (которое отображается в псевдостолбцах cmin и cmax), показывающее порядковый номер операции внутри транзакции. Столбец cmin представляет номер операции вставки, а cmax — операции удаления, но для экономии места в заголовке строки это на самом деле одно поле, а не два разных. Считается, что вставка и удаление той же строки в одной транзакции выполняются редко. (Если это все-таки происходит, в то же самое поле записывается специальный «комбо-номер», для которого обслуживающий процесс запоминает реальные cmin и cmax⁶.)

В качестве примера начнем транзакцию и вставим в таблицу строку:

=> BEGIN;
=> INSERT INTO accounts VALUES (3, 'charlie', 100.00);
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                812
(1 row)

Теперь откроем курсор для запроса, возвращающего число строк в таблице:

=> DECLARE c CURSOR FOR SELECT count(*) FROM accounts;

И после этого вставим еще одну строку:

=> INSERT INTO accounts VALUES (4, 'charlie', 200.00);

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

=> SELECT xmin, CASE WHEN xmin = 812 THEN cmin END cmin, *
FROM accounts;
 xmin | cmin | id | client  | amount
------+------+----+---------+---------
  809 |      |  1 | alice   | 1000.00
  811 |      |  2 | bob     |  200.00
  812 |    0 |  3 | charlie |  100.00
  812 |    1 |  4 | charlie |  200.00
(4 rows)

Запрос курсора обнаружит три строки, а не четыре — строка, добавленная после открытия курсора, не попадет в снимок данных, поскольку в нем учитываются только версии строк с cmin < 1:

=> FETCH c;
 count
-------
     3
(1 row)

Разумеется, этот номер cmin также запоминается в снимке, но вывести его средствами SQL не получится.

4.5. Горизонт транзакции

Нижняя граница снимка (номер xmin самой ранней транзакции, активной на момент его создания) имеет важный смысл — она определяет горизонт транзакции, работающей с этим снимком.

Горизонт транзакции без активного снимка (например, транзакции с уровнем изоляции Read Committed между выполнениями операторов) определяется ее собственным номером, если он ей присвоен.

Все транзакции, находящиеся за горизонтом (то есть транзакции с номерами xid < xmin), уже гарантированно зафиксированы. А это значит, что за своим горизонтом транзакция всегда видит только актуальные версии строк.

PostgreSQL знает текущий горизонт транзакций всех процессов; собственный горизонт транзакция может увидеть в таблице pg_stat_activity:

=> BEGIN;
=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          812
(1 row)

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

Похожим образом можно определить и горизонт базы данных. Для этого надо взять горизонты всех транзакций, работающих с этой базой, и среди них найти наиболее «дальний», имеющий самый старый xmin⁷. Это и будет тот горизонт, за которым неактуальные версии строк в этой базе данных уже никогда не будут видны ни одной транзакции. Такие версии строк могут быть безопасно удалены очисткой — именно поэтому понятие горизонта так важно с практической точки зрения.

Сделаем выводы из сказанного:

• если транзакция с уровнем изоляции Repeatable Read или Serializable (не важно, настоящая или виртуальная) выполняется долго, она тем самым удерживает горизонт базы данных и препятствует очистке;

• настоящая транзакция с уровнем изоляции Read Committed точно так же удерживает горизонт базы данных, выполняет ли она оператор или просто бездействует (находится в состоянии idle in transaction);

• виртуальная транзакция с уровнем изоляции Read Committed удерживает горизонт только в процессе выполнения операторов.

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

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

=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                813
(1 row)

=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          812
(1 row)

И только после завершения транзакции горизонт продвигается вперед, позволяя очистке удалить неактуальные версии строк:

=> COMMIT;
=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          814
(1 row)

В идеале не следует совмещать активные обновления (порождающие версии строк) с долгими транзакциями, поскольку это будет приводить к раздуванию таблиц и индексов. (с. 178)

4.6. Снимок данных для системного каталога

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

Вот простой пример:

=> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
=> SELECT 1; -- создан снимок для транзакции

=> ALTER TABLE accounts ALTER amount SET NOT NULL;

=> INSERT INTO accounts(client, amount) VALUES ('alice', NULL);
ERROR:  null value in column "amount" of relation "accounts" violates
not-null constraint
DETAIL:  Failing row contains (1, alice, null).
=> ROLLBACK;

Команда INSERT «увидела» ограничение целостности, которое появилось уже после того, как был создан снимок данных транзакции. Может показаться, что такое поведение нарушает изоляцию, но если бы транзакция успела обратиться к таблице accounts, то команда ALTER TABLE была бы заблокирована до окончания этой транзакции. (с. 261)

В целом система ведет себя так, как будто для каждого обращения к системному каталогу создается новый снимок. Но реализация⁸, конечно, более сложна, поскольку постоянное создание новых снимков негативно сказалось бы на производительности; кроме того, многие объекты системного каталога кешируются, и это тоже надо учитывать.

4.7. Экспорт снимка данных

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

Разумеется, нельзя полагаться на то, что картины данных совпадут просто потому, что транзакции запущены «одновременно». Такие гарантии дает механизм экспорта и импорта снимка.

Функция pg_export_snapshot возвращает идентификатор снимка, который может быть передан в другую транзакцию (внешними по отношению к СУБД средствами):

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT count(*) FROM accounts;
 count
-------
     4
(1 row)
=> SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000011-00000008-1
(1 row)

Перед тем как выполнить первый оператор, другая транзакция может импортировать снимок с помощью команды SET TRANSACTION SNAPSHOT. Предварительно надо установить уровень изоляции Repeatable Read или Serializable, потому что на уровне Read Committed операторы будут использовать собственные снимки:

=> DELETE FROM accounts;
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SET TRANSACTION SNAPSHOT '00000011-00000008-1';

Теперь вторая транзакция будет работать со снимком первой и, соответственно, видеть четыре строки (а не ноль):

=> SELECT count(*) FROM accounts;
 count
-------
     4
(1 row)

Разумеется, изменения, сделанные первой транзакцией после экспорта снимка, не будут видны второй транзакции (и наоборот): действуют обычные правила видимости.

Время жизни экспортированного снимка совпадает со временем жизни экспортирующей транзакции.

=> COMMIT;

=> COMMIT;

5 Самоочистка и hot-обновления

5.1. Самоочистка

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

• Ранее выполненное обновление (UPDATE) не обнаружило достаточно места, чтобы разместить новую версию строки на этой же странице. Такая ситуация запоминается в заголовке страницы.

• Табличная страница заполнена больше, чем на 90 %.

• Табличная страница заполнена больше, чем на значение параметра хранения fillfactor.

PostgreSQL вставляет (INSERT) новую строку на страницу, только если эта страница заполнена менее, чем на fillfactor процентов. Остальное место приберегается для новых версий строк, которые получаются в результате обновлений (UPDATE). Со значением по умолчанию место в таблицах не резервируется. (по умолчанию: 100)

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

Тот факт, что страница может очищаться при чтении, означает, что оператор SELECT может вызвать изменение страниц. Это еще один такой случай в дополнение к отложенному изменению битов-подсказок. (с. 86)

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

=> CREATE TABLE hot(id integer, s char(2000)) WITH (fillfactor = 75);
=> CREATE INDEX hot_id ON hot(id);
=> CREATE INDEX hot_s ON hot(s);

Если в столбце s хранить только латинские буквы, каждая версия строки будет иметь фиксированный размер 2004 байта, не считая 24 байт заголовка. Параметр хранения fillfactor установлен в 75 %. Значит, места на странице будет хватать на четыре версии строки, но вставить получится только три.

Теперь вставим одну строку и несколько раз изменим ее:

=> INSERT INTO hot VALUES (1, 'A');
=> UPDATE hot SET s = 'B';
=> UPDATE hot SET s = 'C';
=> UPDATE hot SET s = 'D';

В странице сейчас четыре версии строки:

=> SELECT * FROM heap_page('hot',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+-------
 (0,1) | normal | 820 c | 821 c
 (0,2) | normal | 821 c | 822 c
 (0,3) | normal | 822 c | 823
 (0,4) | normal | 823   | 0 a
(4 rows)

Как и ожидалось, мы только что вышли за fillfactor. На это указывает разница между значениями pagesize и upper — она превышает порог в 75 % от размера страницы, составляющий 6144 байта: (с. 75)

=> SELECT upper, pagesize FROM page_header(get_raw_page('hot',0));
 upper | pagesize
-------+----------
    64 |     8192
(1 row)

При следующем обращении к странице срабатывает самоочистка, которая вычищает все неактуальные версии. Теперь освободившегося места хватает на новую версию (0,5):

=> UPDATE hot SET s = 'E';
=> SELECT * FROM heap_page('hot',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+------
 (0,1) | dead   |       |
 (0,2) | dead   |       |
 (0,3) | dead   |       |
 (0,4) | normal | 823 c | 824
 (0,5) | normal | 824   | 0 a
(5 rows)

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

Указатели на удаленные версии строк пока нельзя освободить, поскольку на них ведут ссылки c индексной страницы; статус таких указателей меняется с normal на dead («мертвая» версия). Заглянем в первую страницу индекса hot_s (нулевая занята метаинформацией): (с. 529)

=> SELECT * FROM index_page('hot_s',1);
 itemoffset | htid
------------+-------
          1 | (0,1)
          2 | (0,2)
          3 | (0,3)
          4 | (0,4)
          5 | (0,5)
(5 rows)

Ту же картину мы увидим и в другом индексе:

=> SELECT * FROM index_page('hot_id',1);
 itemoffset | htid
------------+-------
          1 | (0,1)
          2 | (0,2)
          3 | (0,3)
          4 | (0,4)
          5 | (0,5)
(5 rows)

При индексном доступе сервер может получить (0,1), (0,2) или (0,3) в качестве идентификатора версии строки. Тогда он попробует прочитать соответствующую версию строки из табличной страницы, но обнаружит, что указатель имеет статус dead, то есть версия уже не существует и должна игнорироваться. А заодно изменит статус указателя и в индексной странице, чтобы повторно не обращаться к таблице².

Расширим функцию для просмотра индексной страницы, добавив признак «мертвого» указателя: (v. 13)

=> DROP FUNCTION index_page(text, integer);
=> CREATE FUNCTION index_page(relname text, pageno integer)
RETURNS TABLE(itemoffset smallint, htid tid, dead boolean)
AS $$
SELECT itemoffset,
       htid,
       dead -- начиная с v.13
FROM bt_page_items(relname,pageno);
$$ LANGUAGE sql;
=> SELECT * FROM index_page('hot_id',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,1) | f
          2 | (0,2) | f
          3 | (0,3) | f
          4 | (0,4) | f
          5 | (0,5) | f
(5 rows)

Пока все указатели на индексной странице активны. Но при первом же обращении к таблице по индексу статус указателей меняется:

=> EXPLAIN (analyze, costs off, timing off, summary off, buffers off)
SELECT * FROM hot WHERE id = 1;
                        QUERY PLAN
-----------------------------------------------------------
 Index Scan using hot_id on hot (actual rows=1.00 loops=1)
   Index Cond: (id = 1)
   Index Searches: 1
(3 rows)

=> SELECT * FROM index_page('hot_id',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,1) | t
          2 | (0,2) | t
          3 | (0,3) | t
          4 | (0,4) | t
          5 | (0,5) | f
(5 rows)

Хотя табличная строка, на которую ссылается четвертый указатель, еще не вычищена и имеет статус normal, она уже ушла за горизонт базы данных. Поэтому и четвертый указатель в индексе помечен как мертвый.

5.2. Hot-обновления

Держать в индексе ссылки на все версии каждой строки неэффективно.

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

Во-вторых, в индексах накапливаются ссылки на исторические версии строк, которые потом приходится вычищать вместе с самими версиями. (с. 125)

Естественно, чем больше индексов создано на таблице, тем с большими сложностями приходится сталкиваться.

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

С появлением лишних индексных записей борется оптимизация, называемая hot-обновлением ³ (Heap-Only Tuple update). Такое обновление возможно в трех случаях:

• на таблице не создан ни один индекс (в этом случае любое обновление будет hot-обновлением);

• на таблице есть индексы, но обновляемые столбцы не используются ни в одном из них;

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

Считается, что столбец используется в индексе, если он хоть как-то упоминается в команде CREATE INDEX: либо индекс построен по самому столбцу или по выражению, содержащему этот столбец, либо столбец входит в include-индекс как неключевой. Исключение составляют лишь индексы BRIN: в них нет ссылок на табличные строки, и поэтому они никак не мешают hot-обновлениям. (v. 16) (с. 642)

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

Версии, на которые нет ссылок из индекса, маркируются битом Heap-Only Tuple («только табличная версия строки»). Если же версия входит в цепочку, она помечается битом Heap Hot Updated.

Если при сканировании индекса сервер попадает в табличную страницу и обнаруживает версию с флагом Heap Hot Updated, он понимает, что не надо останавливаться, и проходит дальше по всей цепочке обновлений. Разумеется, для всех прочитанных таким образом версий строк проверяется видимость, прежде чем клиент получит результат.

Чтобы посмотреть на работу hot-обновления, удалим один индекс и опустошим таблицу.

=> DROP INDEX hot_s;
=> TRUNCATE TABLE hot;

Для удобства пересоздадим функцию heap_page, дополнив вывод тремя полями — ctid и двумя битами, относящимися к hot-обновлению:

=> DROP FUNCTION heap_page(text,integer);
=> CREATE FUNCTION heap_page(
  relname text,
  pageno integer
)
RETURNS TABLE(
  ctid tid,
  state text,
  xmin text,
  xmax text,
  hhu text,
  hot text,
  t_ctid tid
)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         -- f пригодится позже
         WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
         WHEN (t_infomask & 256) > 0 THEN ' c'
         WHEN (t_infomask & 512) > 0 THEN ' a'
         ELSE ''
       END AS xmin,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' c'
         WHEN (t_infomask & 2048) > 0 THEN ' a'
         ELSE ''
       END AS xmax,
       CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu,
       CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot,
       t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE sql;

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

=> INSERT INTO hot VALUES (1, 'A');
=> UPDATE hot SET s = 'B';

В странице появилась hot-цепочка изменений:

• флаг Heap Hot Updated (hhu) говорит о том, что надо идти дальше по цепочке ctid;

• флаг Heap Only Tuple (hot) показывает, что на данную версию строки нет ссылок из индексов.

=> SELECT * FROM heap_page('hot',0);
 ctid  | state  | xmin  | xmax | hhu | hot | t_ctid
-------+--------+-------+------+-----+-----+--------
 (0,1) | normal | 831 c | 832  | t   |     | (0,2)
 (0,2) | normal | 832   | 0 a  |     | t   | (0,2)
(2 rows)

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

=> UPDATE hot SET s = 'C';
=> UPDATE hot SET s = 'D';
=> SELECT * FROM heap_page('hot',0);
 ctid  | state  | xmin  | xmax  | hhu | hot | t_ctid
-------+--------+-------+-------+-----+-----+--------
 (0,1) | normal | 831 c | 832 c | t   |     | (0,2)
 (0,2) | normal | 832 c | 833 c | t   | t   | (0,3)
 (0,3) | normal | 833 c | 834   | t   | t   | (0,4)
 (0,4) | normal | 834   | 0 a   |     | t   | (0,4)
(4 rows)

При этом в индексе обнаруживаем одну-единственную ссылку на «голову» цепочки:

=> SELECT * FROM index_page('hot_id',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,1) | f
(1 row)

Цепочка hot-обновлений не выходит за рамки страницы, поэтому обход всей цепочки никогда не требует обращения к другим страницам и не ухудшает производительность.

Тип обновлений отслеживается как часть статистики использования таблицы. В данном случае из семи операций UPDATE три были hot-обновлениями:

=> SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_all_tables
WHERE relid = 'hot'::regclass;
 n_tup_upd | n_tup_hot_upd
-----------+---------------
         7 |             3
(1 row)

5.3. Самоочистка при hot-обновлениях

Частный, но важный случай представляет собой самоочистка при hot-обновлениях.

В начатом примере уже превышен порог fillfactor, так что следующее обновление должно привести к самоочистке. Но на этот раз в странице находится цепочка обновлений. «Голова» этой hot-цепочки всегда должна оставаться на своем месте, поскольку на нее ссылается индекс, а остальные указатели могут быть освобождены, ведь на них точно нет ссылок извне.

Чтобы не трогать «голову», применяется двойная адресация: указатель, на который ссылается индекс, — в данном случае (0,1) — получает статус redirect и ведет на версию строки, с которой в данный момент начинается цепочка:

=> UPDATE hot SET s = 'E';
=> SELECT * FROM heap_page('hot',0);
 ctid  |     state     | xmin  | xmax | hhu | hot | t_ctid
-------+---------------+-------+------+-----+-----+--------
 (0,1) | redirect to 4 |       |      |     |     |
 (0,2) | normal        | 835   | 0 a  |     | t   | (0,2)
 (0,3) | unused        |       |      |     |     |
 (0,4) | normal        | 834 c | 835  | t   | t   | (0,2)
(4 rows)

Здесь версии (0,1), (0,2) и (0,3) были вычищены, «головной» указатель 1 остался перенаправлять доступ, а указатели 2 и 3 были освобождены (получили статус unused), поскольку на эти версии гарантированно не было ссылок из индексов. Новая версия строки была записана на освободившееся место под номером (0,2).

Выполним обновление еще несколько раз:

=> UPDATE hot SET s = 'F';

=> UPDATE hot SET s = 'G';

Hot-цепочка выросла:

=> SELECT * FROM heap_page('hot',0);
 ctid  |     state     | xmin  | xmax  | hhu | hot | t_ctid
-------+---------------+-------+-------+-----+-----+--------
 (0,1) | redirect to 4 |       |       |     |     |
 (0,2) | normal        | 835 c | 836 c | t   | t   | (0,3)
 (0,3) | normal        | 836 c | 837   | t   | t   | (0,5)
 (0,4) | normal        | 834 c | 835 c | t   | t   | (0,2)
 (0,5) | normal        | 837   | 0 a   |     | t   | (0,5)
(5 rows)

Следующее обновление снова вызывает самоочистку:

=> UPDATE hot SET s = 'H';
=> SELECT * FROM heap_page('hot',0);
 ctid  |     state     | xmin  | xmax | hhu | hot | t_ctid
-------+---------------+-------+------+-----+-----+--------
 (0,1) | redirect to 5 |       |      |     |     |
 (0,2) | normal        | 838   | 0 a  |     | t   | (0,2)
 (0,3) | unused        |       |      |     |     |
 (0,4) | unused        |       |      |     |     |
 (0,5) | normal        | 837 c | 838  | t   | t   | (0,2)
(5 rows)

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

5.4. Разрыв hot-цепочки

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

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

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT 1;

Теперь выполним обновления в первом сеансе:

=> UPDATE hot SET s = 'I';

=> UPDATE hot SET s = 'J';

=> UPDATE hot SET s = 'K';

Пока все версии помещаются на одной странице:

=> SELECT * FROM heap_page('hot',0);
 ctid  |     state     | xmin  | xmax  | hhu | hot | t_ctid
-------+---------------+-------+-------+-----+-----+--------
 (0,1) | redirect to 2 |       |       |     |     |
 (0,2) | normal        | 838 c | 839 c | t   | t   | (0,3)
 (0,3) | normal        | 839 c | 840 c | t   | t   | (0,4)
 (0,4) | normal        | 840 c | 841   | t   | t   | (0,5)
 (0,5) | normal        | 841   | 0 a   |     | t   | (0,5)
(5 rows)

При следующем обновлении места на странице уже не хватит, а самоочистка не сможет ничего освободить:

=> UPDATE hot SET s = 'L';

    => COMMIT; -- снимок больше не нужен
=> SELECT * FROM heap_page('hot',0);
 ctid  |     state     | xmin  | xmax  | hhu | hot | t_ctid
-------+---------------+-------+-------+-----+-----+--------
 (0,1) | redirect to 2 |       |       |     |     |
 (0,2) | normal        | 838 c | 839 c | t   | t   | (0,3)
 (0,3) | normal        | 839 c | 840 c | t   | t   | (0,4)
 (0,4) | normal        | 840 c | 841 c | t   | t   | (0,5)
 (0,5) | normal        | 841 c | 842   |     | t   | (1,1)
(5 rows)

В версии (0,5) видим ссылку (1,1), ведущую на страницу 1:

=> SELECT * FROM heap_page('hot',1);
 ctid  | state  | xmin | xmax | hhu | hot | t_ctid
-------+--------+------+------+-----+-----+--------
 (1,1) | normal | 842  | 0 a  |     |     | (1,1)
(1 row)

Впрочем, эта ссылка не используется, поскольку в версии (0,5) не установлен бит Heap Hot Updated. А на версию (1,1) можно попасть из индекса, в котором теперь две ссылки. Каждая из них ведет на начало своей hot-цепочки:

=> SELECT * FROM index_page('hot_id',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,1) | f
          2 | (1,1) | f
(2 rows)

Разрывы hot-цепочек отслеживаются в статистике отдельно: (v. 16)

=> SELECT n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relid = 'hot'::regclass;
 n_tup_upd | n_tup_hot_upd | n_tup_newpage_upd
-----------+---------------+-------------------
        15 |            10 |                 1
(1 row)

Здесь из пятнадцати операций UPDATE одиннадцать были hot-обновлениями: десяти хватило места на странице, а одной нет, что привело к разрыву цепочки.

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

5.5. Самоочистка индексов

Я говорил о том, что самоочистка работает с одной табличной страницей и не трогает индексы. Но и для индексов есть собственная самоочистка⁴, которая тоже работает с одной (но уже индексной) страницей.

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

Есть два вида записей, которые самоочистка может удалить из индекса.

В первую очередь удаляются записи, помеченные ранее как мертвые⁵. Как я уже говорил, такая пометка ставится при индексном доступе, если оказывается, что индексная запись ссылается на версию строки, которая не видна ни в одном снимке или уже не существует.

Если заведомо мертвых записей не нашлось, проверяются строки, ссылающиеся на разные версии одной и той же табличной строки ⁶. Из-за многоверсионности при обновлениях может возникать большое количество неактуальных версий строк, которые, скорее всего, достаточно быстро уйдут за горизонт базы данных. Hot-обновления смягчают этот эффект, но работают не всегда: если обновляемый столбец входит в какой-нибудь индекс, ссылки на все версии строк появляются и во всех остальных индексах. Перед расщеплением страницы имеет смысл поискать в ней записи, еще не помеченные как мертвые, но уже допускающие очистку. Для этого надо проверить видимость соответствующих версий строк, что требует обращения к табличной странице. Поэтому рассматриваются не все, а только «перспективные» индексные записи, появившиеся как копия существующих для нужд многоверсионности. Заплатить цену проверки видимости таких записей оказывается выгоднее, чем допустить лишнее расщепление индексной страницы. (v. 14)

6 Очистка и заморозка

6.1. Очистка

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

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

Очистка работает параллельно с другой активностью в системе. Таблица и индексы при этом могут использоваться обычным образом и для чтения, и для изменения (однако одновременное выполнение таких команд, как CREATE INDEX, ALTER TABLE, и некоторых других будет невозможно). (с. 261)

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

Очистка обновляет и карту свободного пространства, отражая появившееся в страницах свободное место.

Создадим таблицу и индекс:

=> CREATE TABLE vac(
  id integer,
  s char(100)
) WITH (autovacuum_enabled = off);
=> CREATE INDEX vac_s ON vac(s);

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

Добавляем строку и выполняем пару обновлений:

=> INSERT INTO vac(id,s) VALUES (1,'A');
=> UPDATE vac SET s = 'B';
=> UPDATE vac SET s = 'C';

Сейчас в таблице три версии строки:

=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax  | hhu | hot | t_ctid
-------+--------+-------+-------+-----+-----+--------
 (0,1) | normal | 845 c | 846 c |     |     | (0,2)
 (0,2) | normal | 846 c | 847   |     |     | (0,3)
 (0,3) | normal | 847   | 0 a   |     |     | (0,3)
(3 rows)

На каждую из них ведет ссылка из индекса:

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,1) | f
          2 | (0,2) | f
          3 | (0,3) | f
(3 rows)

После очистки мертвые версии строк пропадают, и остается только одна, актуальная:

=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax | hhu | hot | t_ctid
-------+--------+-------+------+-----+-----+--------
 (0,1) | unused |       |      |     |     |
 (0,2) | unused |       |      |     |     |
 (0,3) | normal | 847 c | 0 a  |     |     | (0,3)
(3 rows)

При этом два первых указателя, ссылавшиеся на вычищенные версии, получили статус unused, а не dead, как было бы при самоочистке, поскольку на них больше нет ссылок из индекса:

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,3) | f
(1 row)

Указатели в статусе unused считаются свободными и могут быть задействованы для новых версий строк.

Страница теперь отмечена в карте видимости, куда мы можем заглянуть с помощью расширения pg_visibility:

=> CREATE EXTENSION pg_visibility;
=> SELECT all_visible
FROM pg_visibility_map('vac',0);
 all_visible
-------------
 t
(1 row)

Признак того, что все версии строк на странице видны во всех снимках данных, проставляется и в заголовке табличной страницы:

=> SELECT flags & 4 > 0 AS all_visible
FROM page_header(get_raw_page('vac',0));
 all_visible
-------------
 t
(1 row)

6.2. Еще раз о горизонте базы данных

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

Повторим предыдущий опыт с начала:

=> TRUNCATE vac;
=> INSERT INTO vac(id,s) VALUES (1,'A');
=> UPDATE vac SET s = 'B';

Но перед тем как снова обновить строку, начнем вторую транзакцию, удерживающую горизонт базы данных (подойдет практически любая транзакция, кроме виртуальной с уровнем изоляции Read Committed). Например, пусть транзакция поменяет что-нибудь в другой таблице. (с. 106)

=> BEGIN;
=> INSERT INTO accounts(id, client, amount)
  VALUES (1, 'alice', 1000.00);

=> UPDATE vac SET s = 'C';

Сейчас в таблице три версии строки, а в индексе — три ссылки. Что изменится после очистки?

=> VACUUM vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax  | hhu | hot | t_ctid
-------+--------+-------+-------+-----+-----+--------
 (0,1) | unused |       |       |     |     |
 (0,2) | normal | 851 c | 853 c |     |     | (0,3)
 (0,3) | normal | 853 c | 0 a   |     |     | (0,3)
(3 rows)
=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,2) | f
          2 | (0,3) | f
(2 rows)

Если в прошлый раз в таблице осталась только одна версия строки, то теперь их две: очистка решила, что версия (0,2) еще не может быть удалена. Причина, конечно, в горизонте базы данных, который в нашем примере определяется незавершенной транзакцией:

=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          852
(1 row)

Можно попросить очистку рассказать о том, что происходит, указав предложение VERBOSE:

=> VACUUM VERBOSE vac;
INFO:  vacuuming "internals.public.vac"
INFO:  finished vacuuming "internals.public.vac": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly
scanned
tuples: 0 removed, 2 remain, 1 are dead but not yet removable
removable cutoff: 852, which was 2 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 14 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Здесь видно, что:

• в таблице не обнаружено версий строк, которые можно было бы удалить (tuples: 0 removed);

• найдено две версии, которые удалять нельзя (2 remain);

• одна из неудаляемых строк мертвая (1 are dead but not yet removable), другая — актуальная;

• текущий горизонт очистки (removable cutoff) совпадает с горизонтом открытой транзакции.

Завершение открытой транзакции приводит к тому, что горизонт базы данных продвигается и позволяет продолжить очистку:

=> COMMIT;

=> VACUUM VERBOSE vac;
INFO:  vacuuming "internals.public.vac"
INFO:  finished vacuuming "internals.public.vac": index scans: 1
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly
scanned
tuples: 1 removed, 1 remain, 0 are dead but not yet removable
removable cutoff: 854, which was 0 XIDs old when operation ended
new relfrozenxid: 853, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 0 pages set all-frozen (0 were
all-visible)
index scan needed: 1 pages from table (100.00% of total) had 1 dead
item identifiers removed
index "vac_s": pages: 2 in total, 0 newly deleted, 0 currently deleted,
0 reusable
avg read rate: 0.000 MB/s, avg write rate: 95.274 MB/s
buffer usage: 27 hits, 0 reads, 1 dirtied
WAL usage: 6 records, 1 full page images, 8981 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Очистка обнаружила ушедшую за изменившийся горизонт мертвую версию и удалила ее; в странице осталась только последняя, актуальная версия:

=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax | hhu | hot | t_ctid
-------+--------+-------+------+-----+-----+--------
 (0,1) | unused |       |      |     |     |
 (0,2) | unused |       |      |     |     |
 (0,3) | normal | 853 c | 0 a  |     |     | (0,3)
(3 rows)

В индексе также только одна запись:

=> SELECT * FROM index_page('vac_s',1);
 itemoffset | htid  | dead
------------+-------+------
          1 | (0,3) | f
(1 row)

6.3. Этапы выполнения очистки

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

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

Дальше мы поговорим об этих этапах подробнее.

Сканирование таблицы

Все начинается со сканирования таблицы ⁴ с учетом карты видимости: отмеченные в карте страницы пропускаются, поскольку гарантированно содержат только актуальные версии строк. Идентификаторы (tid) тех версий строк из прочитанных страниц, что выходят за горизонт и больше не нужны, записываются в специальное хранилище в памяти. Сразу удалять эти версии строк нельзя, поскольку на них могут ссылаться индексы. (v. 17)

Хранилище представляет собой префиксное дерево⁵. Ключом в нем служат номера страниц, а в узлах хранятся битовые карты версий строк. Структура ограничена в размерах значением параметра maintenance_work_mem; память под нее выделяется по мере необходимости. (с. 605) (с. 427) (по умолчанию: 64MB)

Очистка индексов

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

Для каждого индекса, размер которого превышает значение параметра min_parallel_index_scan_size, может запускаться отдельный рабочий процесс; в этом случае очистка нескольких индексов будет происходить в параллельном режиме. Желаемое количество процессов очистки можно указать в команде VACUUM (parallel N). В реальности их будет запущено не больше, чем имеется свободных фоновых процессов ⁸ и подходящих индексов (поскольку один индекс не может обрабатываться несколькими процессами одновременно). (v. 13) (по умолчанию: 512kB)

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

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

Очистка таблицы

Затем начинается этап очистки таблицы¹⁰. Таблица снова сканируется: это необходимо, чтобы вычистить из страниц запомненные ранее версии строк и освободить указатели. Теперь, когда ссылок из индексов уже нет, это можно сделать безопасно.

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

Если на этапе сканирования таблица не была прочитана полностью, то хранилище tid очищается, и все повторяется с того момента, на котором сканирование таблицы остановилось в прошлый раз.

Усечение таблицы

После очистки в табличных страницах появляется свободное место; иногда может посчастливиться освободить страницу целиком. Если в конце файла образовалось некоторое количество пустых страниц, очистка может «откусить» хвостовую часть файла и вернуть место операционной системе. Это происходит на заключительном этапе усечения таблицы¹¹.

Усечение требует кратковременной исключительной блокировки таблицы. Попытки получить блокировку продолжаются не дольше пяти секунд, чтобы не останавливать надолго работу других процессов. (с. 261)

Из-за необходимости блокировать работу с таблицей усечение выполняется, только если пустых страниц достаточно много: как минимум $\frac{1}{16}$ часть файла или, для больших таблиц, 1000 страниц (эти пороговые значения не настраиваются).

Есть несколько способов полностью отключить усечение, которые могут пригодиться, если блокировка все равно вызывает проблемы:

• вызвать команду VACUUM c указанием truncate off; (v. 12)

• сбросить параметры хранения vacuum_truncate и toast.vacuum_truncate; (v. 12)

• сбросить конфигурационный параметр vacuum_truncate. (v. 18)

6.4. Заморозка

Переполнение счетчика транзакций

Под номер транзакции в PostgreSQL выделено 32 бита. Четыре миллиарда выглядит довольно большим числом, но при активной работе оно может быть израсходовано достаточно быстро. Например, при нагрузке 1000 транзакций в секунду (не считая виртуальных) это произойдет всего через полтора месяца непрерывной работы.

После исчерпания разрядности счетчик должен будет обнулиться и пойти по следующему кругу (по-английски это называется словом wraparound). Но ведь считать, что транзакция с меньшим номером началась раньше транзакции с бóльшим номером, можно только в случае постоянно возрастающих номеров. Поэтому понятно, что просто так обнулить счетчик и начать нумерацию заново нельзя.

Почему под номер транзакции не выделено 64 бита — ведь это полностью исключило бы проблему? Дело в том, что в заголовке каждой версии строки хранятся два номера транзакций — xmin и xmax. Заголовок и так достаточно большой (минимум 24 байта с учетом выравнивания), а увеличение разрядности добавило бы еще 8 байт. (с. 75)

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

В коде это естественным образом реализуется 32-битной арифметикой: надо найти разницу 32-битных номеров транзакций и сравнить полученный результат с нулем¹³.

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

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

Заморозка версий и правила видимости

Чтобы не допустить таких «путешествий во времени», процесс очистки (помимо освобождения места в страницах) выполняет еще одну задачу¹⁴. Он ищет версии строк, находящиеся за горизонтом базы данных (видимые во всех снимках), и специальным образом их помечает — замораживает. (с. 274)

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

Можно считать, что в замороженных версиях строк номер xmin заменяется на условную «минус бесконечность» (показанную на рисунке в виде снежинки) — признак того, что создавшая версию транзакция завершилась так давно, что конкретный номер уже не важен. Хотя на самом деле xmin не меняется, а в качестве признака заморозки выставляются одновременно два бита-подсказки — бит фиксации и бит отмены.

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

Вручную заморозка вызывается командой VACUUM FREEZE. В нашем примере она замораживает единственную версию строки:

=> VACUUM FREEZE vac;
=> SELECT * FROM heap_page('vac',0);
 ctid  | state  | xmin  | xmax | hhu | hot | t_ctid
-------+--------+-------+------+-----+-----+--------
 (0,1) | unused |       |      |     |     |
 (0,2) | unused |       |      |     |     |
 (0,3) | normal | 853 f | 0 a  |     |     | (0,3)
(3 rows)

Страница теперь отмечена не только в карте видимости, но и в карте заморозки:

=> SELECT all_visible, all_frozen
FROM pg_visibility_map('vac',0);
 all_visible | all_frozen
-------------+------------
 t           | t
(1 row)

6.5. Анализ

В дополнение к очистке есть еще одна задача, никак с ней формально не связанная: анализ ¹⁵, или, иными словами, сбор статистической информации. Бо́льшая часть статистики предназначается для планировщика запросов: количество строк (pg_class.reltuples) и страниц (pg_class.relpages) в отношениях, распределение данных по столбцам таблиц и другие сведения. Такие значения, как количество страниц, отмеченных в карте видимости (pg_class.relallvisible) и в карте заморозки (pg_class.relallfrozen), не используются планировщиком, но нужны для управления самим анализом и очисткой. (с. 346)

Вручную анализ можно выполнять сам по себе командой ANALYZE¹⁶, но можно и совмещать с очисткой: VACUUM ANALYZE. Правда, при этом очистка и анализ выполняются последовательно — никакой экономии не происходит.

7 Автоочистка

7.1. Автоматическая очистка и анализ

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

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

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

К тому же нагрузка может изменяться со временем, так что запуск очистки по какому-либо фиксированному расписанию в любом случае не годится: чем чаще обновляется таблица, тем чаще ее надо и очищать.

Задача решается автоматической очисткой ¹ — механизмом, который подстраивает частоту срабатывания очистки и анализа под скорость изменения таблиц.

Устройство автоочистки

При включенной автоматической очистке (конфигурационный параметр autovacuum) в системе всегда присутствует процесс autovacuum launcher, ответственный за расписание. Он поддерживает список баз данных, в которых есть какая-либо активность, опираясь на статистику их использования. Такая статистика собирается при установленном параметре track_counts. Не следует выключать эти параметры, иначе автоочистка не будет работать. (по умолчанию: on) (по умолчанию: on)

Раз в autovacuum_naptime единиц времени процесс autovacuum launcher запускает (как обычно, с помощью postmaster) рабочий процесс autovacuum worker ² для каждой «активной» базы данных из списка. Соответственно, если в кластере используется несколько баз данных (N штук), то за интервал autovacuum_naptime будет запущено N рабочих процессов. Но при этом общее количество одновременно выполняющихся рабочих процессов ограничено значением параметра autovacuum_max_workers. (по умолчанию: 1min) (по умолчанию: 3)

Ограничение количества рабочих процессов можно менять на лету, но только в пределах autovacuum_worker_slots. А изменение количества слотов уже требует перезапуска сервера, поскольку резервирует место в его разделяемой памяти. (v. 18) (по умолчанию: 16) (с. 379)

из max_worker_processes.

Запустившись, рабочий процесс подключается к указанной ему базе данных и начинает с того, что строит два списка:

• список всех таблиц, материализованных представлений и toast-таблиц, требующих очистки;

• список всех таблиц и материализованных представлений, требующих анализа (toast-таблицы не анализируются, потому что обращение к ним всегда происходит по индексу).

Затем рабочий процесс по очереди очищает или анализирует отобранные объекты (или выполняет и то, и другое), после чего завершается.

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

• Очистка, запускаемая вручную, использует для накопления идентификаторов версий фрагмент памяти размером maintenance_work_mem.

При автоматической очистке одновременно выполняются несколько рабочих процессов, и оперативная память нужна каждому. Поэтому для автоочистки предусмотрен отдельный параметр autovacuum_work_mem. Значение по умолчанию, при котором используется обычное ограничение maintenance_work_mem, скорее всего, следует скорректировать, если конфигурация предполагает большое количество рабочих процессов (autovacuum_max_workers). (по умолчанию: -1)

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

Если процесс не успеет выполнить всю намеченную работу за интервал времени autovacuum_naptime, процесс autovacuum launcher пошлет в ту же базу данных еще один рабочий процесс, и они будут работать вместе. Второй процесс построит свои собственные списки объектов для очистки и анализа и пойдет по ним. Параллельно будут обрабатываться разные таблицы; на уровне одной таблицы параллелизма нет.

Какие таблицы требуют очистки

Автоочистку можно отключить на уровне отдельных таблиц, хотя сложно придумать причину, по которой это было бы необходимо. За отключение отвечают два параметра хранения: один для простых таблиц, другой — для toast-таблиц:

• autovacuum_enabled;

• toast.autovacuum_enabled.

Обычно же автоочистка срабатывает при накоплении либо неактуальных версий, либо новых строк. (с. 153)

Неактуальные версии строк. Примерное число неактуальных версий постоянно отслеживается и доступно в таблице pg_stat_all_tables.

Считается, что очистка необходима, если количество неактуальных, мертвых версий строк превышает пороговое значение, определяемое тремя параметрами:

• autovacuum_vacuum_threshold — абсолютное значение (в штуках); (по умолчанию: 50)

• autovacuum_vacuum_scale_factor — доля строк в таблице; (по умолчанию: 0.2)

• autovacuum_vacuum_max_threshold — дополнительный порог для очень больших таблиц. (по умолчанию: 100 млн) (v. 18)

Формула такова: очистка мертвых версий требуется, если

или (v. 18)

В первой части формулы для больших таблиц (а именно с ними и связаны возможные проблемы) важен параметр autovacuum_vacuum_scale_factor. Значение по умолчанию 20 % представляется сильно завышенным; скорее всего, его потребуется существенно уменьшить. Если и после этого пороговое значение слишком велико для очень больших таблиц, его можно ограничить сверху параметром из второй части формулы.

Если не удается подобрать универсальные значения параметров, их можно переопределить для отдельных таблиц с помощью одноименных параметров хранения (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, autovacuum_vacuum_max_threshold). Аналогичные параметры хранения с префиксом toast определяют настройки для toast-таблиц.

Вставленные строки. Для хранилищ данных типична ситуация, когда строки добавляются, но никогда не удаляются и не изменяются. В этом случае в таблице не будет мертвых версий и предыдущее условие срабатывания не будет выполняться. Однако и такие таблицы необходимо очищать, чтобы заблаговременно замораживать версии строк и обновлять карту видимости (и тем самым разрешать сканирование только индекса). Поэтому добавляется еще одно условие. (v. 13) (с. 422)

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

• autovacuum_vacuum_insert_threshold; (по умолчанию: 1000)

• autovacuum_vacuum_insert_scale_factor. (по умолчанию: 0.2)

Как и число неактуальных версий, количество вставленных строк отслеживается как часть статистики использования таблицы.

Формула такова:

Если с первым слагаемым все понятно — это просто абсолютное значение, — то ко второму необходим комментарий. В нем вычисляется доля не от всех, а только от незамороженных строк; соответствующая часть таблицы оценивается отношением числа полностью замороженных страниц (relallfrozen) к их общему количеству (relpages). Дело в том, что с ростом таблицы строк в ней становится все больше и больше, и без коэффициента очистка выполнялась бы все реже и реже. А вот количество незамороженных строк периодически уменьшается, поэтому частота срабатываний не увеличивается — что хорошо. (v. 18)

Как и в предыдущем случае, мы можем переопределить значения параметров для отдельных таблиц с помощью одноименных параметров хранения (autovacuum_vacuum_insert_threshold и autovacuum_vacuum_insert_scale_factor) и их toast-версий.

Какие таблицы требуют анализа

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

Считается, что анализа требуют те таблицы, в которых число измененных строк (с момента прошлого анализа) превышает пороговое значение, заданное парой параметров:

• autovacuum_analyze_threshold; (по умолчанию: 50)

• autovacuum_analyze_scale_factor. (по умолчанию: 0.1)

Формула срабатывания автоанализа:

Настройки автоанализа также можно переопределить на уровне таблиц с помощью одноименных параметров хранения (autovacuum_analyze_threshold и autovacuum_analyze_scale_factor). Поскольку toast-таблицы не анализируются, для них соответствующих параметров нет.

Автоочистка в действии

Начнем с написания функции, возвращающей текущее значение параметра:

=> CREATE FUNCTION p(param text, c pg_class) RETURNS float
AS $$
  SELECT coalesce(
    -- если параметр хранения задан, то берем его
    (SELECT option_value
     FROM   pg_options_to_table(c.reloptions)
     WHERE  option_name = CASE -- для toast-таблиц имя отличается
              WHEN c.relkind = 't' THEN 'toast.' ELSE ''
            END || param
    ),
    -- иначе берем значение конфигурационного параметра
    current_setting(param)
  )::float;
$$ LANGUAGE sql;

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

Так может выглядеть представление для очистки:

=> CREATE VIEW need_vacuum AS
WITH c AS (
  SELECT c.oid,
    greatest(c.reltuples, 0) AS reltuples,
    CASE
      WHEN c.relpages > 0 THEN
        1 - c.relallfrozen::float / c.relpages
      ELSE 1
    END AS pct_unfrozen,
    p('autovacuum_vacuum_threshold', c) AS threshold,
    p('autovacuum_vacuum_scale_factor', c) AS scale_factor,
    p('autovacuum_vacuum_max_threshold', c) AS max_threshold,
    p('autovacuum_vacuum_insert_threshold', c) AS ins_threshold,
    p('autovacuum_vacuum_insert_scale_factor', c) AS ins_scale_factor
  FROM pg_class c
  WHERE c.relkind IN ('r','m','t')
),
r AS (
  SELECT st.schemaname || '.' || st.relname AS tablename,
    st.n_dead_tup AS dead_tup,
    least(
      c.threshold + c.scale_factor * c.reltuples,
      c.max_threshold
    ) AS max_dead_tup,
    st.n_ins_since_vacuum AS ins_tup,
    c.ins_threshold + c.ins_scale_factor * c.reltuples * c.pct_unfrozen
      AS max_ins_tup,
    st.last_autovacuum
  FROM pg_stat_all_tables st
    JOIN c ON c.oid = st.relid
)
SELECT *,
  CASE
    WHEN dead_tup > max_dead_tup OR ins_tup > max_ins_tup
      THEN 'Пора!'
  END AS is_it_time
FROM r;

Столбец max_dead_tup показывает пороговое значение срабатывания автоочистки из-за мертвых версий строк, а столбец max_ins_tup — из-за вставки новых строк.

А вот аналогичное представление для анализа:

=> CREATE VIEW need_analyze AS
WITH c AS (
  SELECT c.oid,
    greatest(c.reltuples, 0) AS reltuples,
    p('autovacuum_analyze_threshold', c) AS threshold,
    p('autovacuum_analyze_scale_factor', c) AS scale_factor
  FROM pg_class c
  WHERE c.relkind IN ('r','m')
),
r AS (
  SELECT st.schemaname || '.' || st.relname AS tablename,
    st.last_autoanalyze,
    st.n_mod_since_analyze AS mod_tup,
    c.threshold + c.scale_factor * c.reltuples AS max_mod_tup
  FROM pg_stat_all_tables st
    JOIN c ON c.oid = st.relid
)
SELECT *,
  CASE
    WHEN mod_tup > max_mod_tup THEN 'Пора!'
  END AS is_it_time
FROM r;

Столбец max_mod_tup показывает пороговое значение срабатывания автоанализа.

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

=> ALTER SYSTEM SET autovacuum_naptime = '1s';
=> SELECT pg_reload_conf();

Опустошим таблицу vac и вставим в нее тысячу строк. Напомню, что автоочистка отключена на уровне таблицы.

=> TRUNCATE TABLE vac;
=> INSERT INTO vac(id,s)
  SELECT id, 'A' FROM generate_series(1,1000) id;

Вот что покажет наше представление для очистки:

=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+-----------
tablename       | public.vac
dead_tup        | 0
max_dead_tup    | 50
ins_tup         | 1000
max_ins_tup     | 1000
last_autovacuum |
is_it_time      |

Пороговое значение max_dead_tup = 50, хотя по приведенной выше формуле должно быть 50 + 0,2 × 1000 = 250. Дело в том, что еще нет статистики по таблице, поскольку команда INSERT сама по себе ее не обновляет:

=> SELECT reltuples FROM pg_class WHERE relname = 'vac';
 reltuples
-----------
        -1
(1 row)

Специальная константа −1 для pg_class.reltuples позволяет отличать таблицу без статистики от пустой, но проанализированной таблицы, для которой этот столбец будет равен нулю. Для расчетов отрицательное значение приводится к нулю, что и дает 50 + 0,2 × 0 = 50. (v. 14)

Значение max_ins_tup = 1000 отличается от расчетного числа 1200 по той же причине.

Заглянем в представление для анализа:

=> SELECT * FROM need_analyze WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]----+-----------
tablename        | public.vac
last_autoanalyze |
mod_tup          | 1006
max_mod_tup      | 50
is_it_time       | Пора!

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

Включим его:

=> ALTER TABLE vac SET (autovacuum_enabled = on);

После небольшой паузы таблица оказывается проанализированной, пороговое значение исправляется на корректные 150 строк:

=> SELECT reltuples FROM pg_class WHERE relname = 'vac';
 reltuples
-----------
      1000
(1 row)
=> SELECT * FROM need_analyze WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]----+------------------------------
tablename        | public.vac
last_autoanalyze | 2026-04-05 20:52:06.591521+03
mod_tup          | 0
max_mod_tup      | 150
is_it_time       |

Вернемся к автоочистке:

=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+-----------
tablename       | public.vac
dead_tup        | 0
max_dead_tup    | 250
ins_tup         | 1000
max_ins_tup     | 1200
last_autovacuum |
is_it_time      |

Значения max_dead_tup и max_ins_tup тоже исправились с учетом выясненного в процессе анализа размера таблицы.

На данный момент очистка может состояться в одном из двух случаев:

• при появлении более 250 мертвых версий строк;

• при вставке в таблицу более 200 строк. (v. 13)

Снова отключим автоочистку и обновим 251 строку — на одну больше, чем пороговое значение:

=> ALTER TABLE vac SET (autovacuum_enabled = off);
=> UPDATE vac SET s = 'B' WHERE id % 4 = 0 OR id = 1;
UPDATE 251
=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+-----------
tablename       | public.vac
dead_tup        | 251
max_dead_tup    | 250
ins_tup         | 1000
max_ins_tup     | 1200
last_autovacuum |
is_it_time      | Пора!

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

=> ALTER TABLE vac SET (autovacuum_enabled = on);
=> SELECT * FROM need_vacuum WHERE tablename = 'public.vac' \gx
-[ RECORD 1 ]---+------------------------------
tablename       | public.vac
dead_tup        | 0
max_dead_tup    | 250
ins_tup         | 0
max_ins_tup     | 1200
last_autovacuum | 2026-04-05 20:52:11.763126+03
is_it_time      |

7.2. Заморозка

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

=> CREATE TABLE tfreeze(
  id integer,
  s char(300)
) WITH (fillfactor = 10, autovacuum_enabled = off);

Очередной вариант функции для просмотра страниц с помощью расширения pageinspect будет отображать диапазон страниц, расшифровывать признак заморозки (f) и показывать возраст транзакции xmin (для этого используется системная функция age; сам возраст, конечно, не хранится в страницах данных):

=> CREATE FUNCTION heap_page(
  relname text, pageno_from integer, pageno_to integer
)
RETURNS TABLE(
  ctid tid, state text,
  xmin text, xmin_age integer, xmax text
) AS $$
SELECT (pageno,lp)::text::tid AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'unused'
         WHEN 1 THEN 'normal'
         WHEN 2 THEN 'redirect to '||lp_off
         WHEN 3 THEN 'dead'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
         WHEN (t_infomask & 256) > 0 THEN ' c'
         WHEN (t_infomask & 512) > 0 THEN ' a'
         ELSE ''
       END AS xmin,
       age(t_xmin) AS xmin_age,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' c'
         WHEN (t_infomask & 2048) > 0 THEN ' a'
         ELSE ''
       END AS xmax
FROM generate_series(pageno_from, pageno_to) p(pageno),
     heap_page_items(get_raw_page(relname, pageno))
ORDER BY pageno, lp;
$$ LANGUAGE sql;

Вставляем в таблицу некоторое количество строк и сразу выполняем очистку, чтобы создалась карта видимости:

=> CREATE EXTENSION IF NOT EXISTS pg_visibility;
=> INSERT INTO tfreeze(id, s)
  SELECT id, 'FOO'||id FROM generate_series(1,100) id;
INSERT 0 100
=> VACUUM tfreeze;

Будем наблюдать за первыми двумя страницами с помощью расширения pg_visibility. После очистки обе страницы оказываются отмеченными в карте видимости (all_visible), но не в карте заморозки (all_frozen) — они еще содержат незамороженные версии строк:

=> SELECT *
FROM generate_series(0,1) g(blkno),
     pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | f
     1 | t           | f
(2 rows)

У транзакции, создавшей строки, возраст xmin_age равен 1, поскольку это последняя транзакция, которая выполнялась в системе:

=> SELECT * FROM heap_page('tfreeze',0,1);
 ctid  | state  | xmin  | xmin_age | xmax
-------+--------+-------+----------+------
 (0,1) | normal | 877 c |        1 | 0 a
 (0,2) | normal | 877 c |        1 | 0 a
 (1,1) | normal | 877 c |        1 | 0 a
 (1,2) | normal | 877 c |        1 | 0 a
(4 rows)

7.3. Управление заморозкой

Заморозкой управляют четыре основных конфигурационных параметра. Значения всех четырех представляют возраст транзакций и определяют моменты, начиная с которых:

• версии начинают замораживаться — vacuum_freeze_min_age;

• выполняется агрессивная заморозка — vacuum_freeze_table_age;

• заморозка срабатывает аварийно — autovacuum_freeze_max_age;

• заморозка работает в приоритетном режиме — vacuum_failsafe_age. (v. 14)

Минимальный возраст для заморозки

Минимальный возраст транзакции xmin, при котором версию строки можно замораживать, определяется параметром vacuum_freeze_min_age. Чем меньше это значение, тем сильнее могут вырасти накладные расходы: если строка окажется «горячей» и будет активно изменяться, то заморозка все новых и новых версий будет пропадать без пользы. Установка относительно большого значения параметра позволяет выждать. (по умолчанию: 50 млн)

Чтобы посмотреть, как происходит заморозка, уменьшим значение до единицы: (с. 136)

=> ALTER SYSTEM SET vacuum_freeze_min_age = 1;
=> SELECT pg_reload_conf();

Обновим теперь одну строку на нулевой странице. Новая версия попадет на ту же страницу благодаря небольшому значению fillfactor:

=> UPDATE tfreeze SET s = 'BAR' WHERE id = 1;

Возраст транзакций увеличился на единицу. Табличные страницы выглядят сейчас следующим образом:

=> SELECT * FROM heap_page('tfreeze',0,1);
 ctid  | state  | xmin  | xmin_age | xmax
-------+--------+-------+----------+------
 (0,1) | normal | 877 c |        2 | 878
 (0,2) | normal | 877 c |        2 | 0 a
 (0,3) | normal | 878   |        1 | 0 a
 (1,1) | normal | 877 c |        2 | 0 a
 (1,2) | normal | 877 c |        2 | 0 a
(5 rows)

Теперь строки старше vacuum_freeze_min_age = 1 подлежат заморозке. Но очистка рассматривает только те страницы, которые не отмечены в карте видимости: (с. 131)

=> SELECT *
FROM generate_series(0,1) g(blkno),
     pg_visibility_map('tfreeze',g.blkno)
ORDER BY g.blkno;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | t           | f
(2 rows)