PostgreSQL 16 изнутри

Компания Postgres Professional
Егор Рогов
PostgreSQL 16
изнутри
Москва, 2024

УДК 004.65
ББК 32.972.134
Р59
Рогов Е. В.
Р59
PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024. — 664 с.
ISBN 978-5-93700-305-8
В книге рассматривается внутреннее устройство СУБД PostgreSQL: детали реализации многоверсионности и изоляции на
основе снимков данных, включая процедуру очистки неактуальных версий строк; буферный кеш и журнал предзаписи; использование блокировок различных уровней; планирование и выполнение SQL-запросов; принципы расширяемости и особенности
имеющихся индексных методов доступа. Большое внимание уделяется возможностям, предоставляемым для самостоятельного
изучения механизмов функционирования PostgreSQL.
В настоящем издании учтены замечания читателей и исправлены
опечатки, а также отражены изменения, произошедшие в версии
PostgreSQL 16.
Сайт книги: https://postgrespro.ru/education/books/internals.
Для администраторов и программистов.
УДК 004.65
ББК 32.972.134
На обложке использован фрагмент гравюры из книги The History of Fourfooted Beasts and Serpents Эдварда Топсела, изданной в 1658 году в Лондоне.
ISBN 978-5-93700-305-8
© Текст, оформление, ООО «ППГ», 2024
© Издание, ДМК Пресс, 2024

Оглавление
О книге . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17
Глава 1. Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
23
Часть I. Изоляция и многоверсионность
45
Глава 2. Изоляция . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
47
Глава 3. Страницы и версии строк . . . . . . . . . . . . . . . . . . . . . .
75
Глава 4. Снимки данных . . . . . . . . . . . . . . . . . . . . . . . . . . . .
97
Глава 5. Внутристраничная очистка и hot-обновления . . . . . . . . . . 111
Глава 6. Очистка и автоочистка . . . . . . . . . . . . . . . . . . . . . . . . 125
Глава 7. Заморозка
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Глава 8. Перестроение таблиц и индексов . . . . . . . . . . . . . . . . . . 165
Часть II. Буферный кеш и журнал
177
Глава 9. Буферный кеш . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Глава 10. Журнал предзаписи . . . . . . . . . . . . . . . . . . . . . . . . . 201
Глава 11. Режимы журнала . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Часть III. Блокировки
243
Глава 12. Блокировки отношений
. . . . . . . . . . . . . . . . . . . . . . 245
Глава 13. Блокировки строк . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Глава 14. Блокировки разных объектов . . . . . . . . . . . . . . . . . . . 283
Глава 15. Блокировки в памяти . . . . . . . . . . . . . . . . . . . . . . . . 295
Часть IV. Выполнение запросов
305
Глава 16. Этапы выполнения запросов . . . . . . . . . . . . . . . . . . . . 307
Глава 17. Статистика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Глава 18. Табличные методы доступа
. . . . . . . . . . . . . . . . . . . . 356
Глава 19. Индексные методы доступа . . . . . . . . . . . . . . . . . . . . 379
Глава 20. Индексное сканирование . . . . . . . . . . . . . . . . . . . . . . 399
Глава 21. Вложенный цикл . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
3

Оглавление
Глава 22. Хеширование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Глава 23. Сортировка и слияние
. . . . . . . . . . . . . . . . . . . . . . . 469
Часть V. Типы индексов
495
Глава 24. Хеш-индекс . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Глава 25. B-дерево
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Глава 26. Индекс GiST
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
Глава 27. Индекс SP-GiST . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570
Глава 28. Индекс GIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
Глава 29. Индекс BRIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624
Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Предметный указатель . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
4

Содержание
О книге
17
Глава 1. Введение
23
1.1. Организация данных . . . . . . . . . . . . . . . . . . . . . . . . .
23
Базы данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
23
Системный каталог . . . . . . . . . . . . . . . . . . . . . . . . . .
24
Схемы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
25
Табличные пространства . . . . . . . . . . . . . . . . . . . . . . .
26
Отношения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
27
Слои и файлы . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
28
Страницы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
33
TOAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
33
1.2. Процессы и память . . . . . . . . . . . . . . . . . . . . . . . . . .
39
1.3. Клиенты и клиент-серверный протокол . . . . . . . . . . . . . .
41
Часть I. Изоляция и многоверсионность
45
Глава 2. Изоляция
47
2.1. Согласованность . . . . . . . . . . . . . . . . . . . . . . . . . . . .
47
2.2. Уровни изоляции и аномалии в стандарте SQL . . . . . . . . . .
49
Потерянное обновление . . . . . . . . . . . . . . . . . . . . . . .
50
Грязное чтение и Read Uncommitted . . . . . . . . . . . . . . . .
50
Неповторяющееся чтение и Read Committed . . . . . . . . . . .
51
Фантомное чтение и Repeatable Read
. . . . . . . . . . . . . . .
51
Отсутствие аномалий и Serializable
. . . . . . . . . . . . . . . .
52
Почему именно эти аномалии? . . . . . . . . . . . . . . . . . . .
52
2.3. Уровни изоляции в PostgreSQL . . . . . . . . . . . . . . . . . . .
54
Read Committed . . . . . . . . . . . . . . . . . . . . . . . . . . . .
55
Repeatable Read . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
Serializable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
70
2.4. Какой уровень изоляции использовать? . . . . . . . . . . . . . .
73
5

Содержание
Глава 3. Страницы и версии строк
75
3.1. Структура страниц
. . . . . . . . . . . . . . . . . . . . . . . . . .
75
Заголовок страницы
. . . . . . . . . . . . . . . . . . . . . . . . .
75
Специальная область . . . . . . . . . . . . . . . . . . . . . . . . .
76
Версии строк . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
76
Указатели на версии строк . . . . . . . . . . . . . . . . . . . . . .
77
Свободное место
. . . . . . . . . . . . . . . . . . . . . . . . . . .
78
3.2. Структура версий строк
. . . . . . . . . . . . . . . . . . . . . . .
78
3.3. Выполнение операций над версиями строк . . . . . . . . . . . .
80
Вставка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
81
Фиксация . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
85
Удаление . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
87
Отмена . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
88
Обновление
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
88
3.4. Индексы
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
89
3.5. TOAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
90
3.6. Виртуальные транзакции
. . . . . . . . . . . . . . . . . . . . . .
91
3.7. Вложенные транзакции
. . . . . . . . . . . . . . . . . . . . . . .
92
Точки сохранения . . . . . . . . . . . . . . . . . . . . . . . . . . .
92
Ошибки и атомарность операций
. . . . . . . . . . . . . . . . .
94
Глава 4. Снимки данных
97
4.1. Что такое снимок данных . . . . . . . . . . . . . . . . . . . . . .
97
4.2. Видимость версий строк в снимке . . . . . . . . . . . . . . . . .
98
4.3. Из чего состоит снимок
. . . . . . . . . . . . . . . . . . . . . . .
99
4.4. Видимость собственных изменений . . . . . . . . . . . . . . . . 104
4.5. Горизонт транзакции . . . . . . . . . . . . . . . . . . . . . . . . . 105
4.6. Снимок данных для системного каталога . . . . . . . . . . . . . 108
4.7. Экспорт снимка данных . . . . . . . . . . . . . . . . . . . . . . . 109
Глава 5. Внутристраничная очистка и hot-обновления
111
5.1. Внутристраничная очистка . . . . . . . . . . . . . . . . . . . . . 111
5.2. Hot-обновления . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
5.3. Внутристраничная очистка при hot-обновлениях . . . . . . . . 119
5.4. Разрыв hot-цепочки
. . . . . . . . . . . . . . . . . . . . . . . . . 121
5.5. Внутристраничная очистка индексов
. . . . . . . . . . . . . . . 123
6

Содержание
Глава 6. Очистка и автоочистка
125
6.1. Очистка вручную . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
6.2. Еще раз о горизонте базы данных
. . . . . . . . . . . . . . . . . 128
6.3. Этапы выполнения очистки . . . . . . . . . . . . . . . . . . . . . 131
Сканирование таблицы
. . . . . . . . . . . . . . . . . . . . . . . 131
Очистка индексов . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Очистка таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Усечение таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
6.4. Анализ
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
6.5. Автоматическая очистка и анализ . . . . . . . . . . . . . . . . . 134
Устройство автоочистки . . . . . . . . . . . . . . . . . . . . . . . 135
Какие таблицы требуют очистки . . . . . . . . . . . . . . . . . . 136
Какие таблицы требуют анализа
. . . . . . . . . . . . . . . . . . 138
Автоочистка в действии . . . . . . . . . . . . . . . . . . . . . . . 139
6.6. Регулирование нагрузки . . . . . . . . . . . . . . . . . . . . . . . 143
Управление интенсивностью обычной очистки
. . . . . . . . . 144
Управление интенсивностью автоочистки
. . . . . . . . . . . . 144
6.7. Мониторинг очистки . . . . . . . . . . . . . . . . . . . . . . . . . 145
Отслеживание выполнения ручной очистки
. . . . . . . . . . . 146
Отслеживание выполнения автоочистки
. . . . . . . . . . . . . 148
Глава 7. Заморозка
150
7.1. Переполнение счетчика транзакций . . . . . . . . . . . . . . . . 150
7.2. Заморозка версий и правила видимости
. . . . . . . . . . . . . 151
7.3. Управление заморозкой . . . . . . . . . . . . . . . . . . . . . . . 154
Минимальный возраст для заморозки . . . . . . . . . . . . . . . 155
Возраст для агрессивной заморозки . . . . . . . . . . . . . . . . 157
Возраст для аварийного срабатывания автоочистки . . . . . . . 159
Возраст для приоритетного режима заморозки
. . . . . . . . . 161
7.4. Заморозка вручную . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Очистка с заморозкой
. . . . . . . . . . . . . . . . . . . . . . . . 162
Заморозка при загрузке . . . . . . . . . . . . . . . . . . . . . . . 162
Глава 8. Перестроение таблиц и индексов
165
8.1. Полная очистка . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Необходимость
. . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Оценка плотности информации
. . . . . . . . . . . . . . . . . . 166
7

Содержание
Заморозка
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
8.2. Другие способы перестроения
. . . . . . . . . . . . . . . . . . . 171
Аналоги полной очистки . . . . . . . . . . . . . . . . . . . . . . . 171
Перестроение без долгих блокировок . . . . . . . . . . . . . . . 172
8.3. Профилактика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Читающие запросы . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Обновление данных
. . . . . . . . . . . . . . . . . . . . . . . . . 173
Часть II. Буферный кеш и журнал
177
Глава 9. Буферный кеш
179
9.1. Кеширование
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
9.2. Устройство буферного кеша . . . . . . . . . . . . . . . . . . . . . 180
9.3. Попадание в кеш
. . . . . . . . . . . . . . . . . . . . . . . . . . . 182
9.4. Промах кеша . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Поиск буфера и вытеснение . . . . . . . . . . . . . . . . . . . . . 188
9.5. Массовое вытеснение
. . . . . . . . . . . . . . . . . . . . . . . . 190
9.6. Настройка размера . . . . . . . . . . . . . . . . . . . . . . . . . . 194
9.7. Прогрев кеша
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
9.8. Локальный кеш . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Глава 10. Журнал предзаписи
201
10.1. Журналирование
. . . . . . . . . . . . . . . . . . . . . . . . . . . 201
10.2. Устройство журнала
. . . . . . . . . . . . . . . . . . . . . . . . . 203
Логическая структура . . . . . . . . . . . . . . . . . . . . . . . . . 203
Физическая структура
. . . . . . . . . . . . . . . . . . . . . . . . 206
10.3. Контрольная точка . . . . . . . . . . . . . . . . . . . . . . . . . . 208
10.4. Восстановление . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
10.5. Фоновая запись . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
10.6. Настройка
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Настройка контрольной точки
. . . . . . . . . . . . . . . . . . . 217
Настройка фоновой записи . . . . . . . . . . . . . . . . . . . . . 220
Мониторинг . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Глава 11. Режимы журнала
224
11.1. Производительность . . . . . . . . . . . . . . . . . . . . . . . . . 224
8

Содержание
11.2. Надежность
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Кеширование
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Повреждение данных . . . . . . . . . . . . . . . . . . . . . . . . . 230
Неатомарность записи . . . . . . . . . . . . . . . . . . . . . . . . 232
11.3. Уровни журнала . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Minimal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Replica
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Logical . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Часть III. Блокировки
243
Глава 12. Блокировки отношений
245
12.1. Общие сведения о блокировках . . . . . . . . . . . . . . . . . . . 245
12.2. Тяжелые блокировки . . . . . . . . . . . . . . . . . . . . . . . . . 248
12.3. Блокировки номеров транзакций
. . . . . . . . . . . . . . . . . 250
12.4. Блокировки отношений . . . . . . . . . . . . . . . . . . . . . . . 251
12.5. Очередь ожидания
. . . . . . . . . . . . . . . . . . . . . . . . . . 254
Глава 13. Блокировки строк
258
13.1. Устройство . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
13.2. Режимы блокировки строки . . . . . . . . . . . . . . . . . . . . . 259
Исключительные режимы . . . . . . . . . . . . . . . . . . . . . . 259
Разделяемые режимы
. . . . . . . . . . . . . . . . . . . . . . . . 261
13.3. Мультитранзакции . . . . . . . . . . . . . . . . . . . . . . . . . . 262
13.4. Очередь ожидания
. . . . . . . . . . . . . . . . . . . . . . . . . . 264
Исключительные режимы . . . . . . . . . . . . . . . . . . . . . . 264
Разделяемые режимы
. . . . . . . . . . . . . . . . . . . . . . . . 271
13.5. Блокировка без ожидания . . . . . . . . . . . . . . . . . . . . . . 274
13.6. Взаимоблокировки . . . . . . . . . . . . . . . . . . . . . . . . . . 276
Взаимоблокировка при обновлении строк
. . . . . . . . . . . . 278
Взаимоблокировка двух команд UPDATE
. . . . . . . . . . . . . 279
Глава 14. Блокировки разных объектов
283
14.1. Блокировки неотношений . . . . . . . . . . . . . . . . . . . . . . 283
14.2. Блокировки расширения отношения . . . . . . . . . . . . . . . . 285
14.3. Блокировки страниц . . . . . . . . . . . . . . . . . . . . . . . . . 286
9

Содержание
14.4. Рекомендательные блокировки . . . . . . . . . . . . . . . . . . . 286
14.5. Предикатные блокировки . . . . . . . . . . . . . . . . . . . . . . 288
Глава 15. Блокировки в памяти
295
15.1. Спин-блокировки . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
15.2. Легкие блокировки . . . . . . . . . . . . . . . . . . . . . . . . . . 296
15.3. Примеры . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Буферный кеш . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Буферы журнала предзаписи . . . . . . . . . . . . . . . . . . . . 298
15.4. Мониторинг ожиданий . . . . . . . . . . . . . . . . . . . . . . . . 299
15.5. Семплирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Часть IV. Выполнение запросов
305
Глава 16. Этапы выполнения запросов
307
16.1. Демонстрационная база данных . . . . . . . . . . . . . . . . . . 307
16.2. Простой протокол запросов . . . . . . . . . . . . . . . . . . . . . 310
Разбор
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Трансформация . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Планирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Исполнение
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
16.3. Расширенный протокол запросов
. . . . . . . . . . . . . . . . . 325
Подготовка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Привязка параметров
. . . . . . . . . . . . . . . . . . . . . . . . 326
Планирование и исполнение . . . . . . . . . . . . . . . . . . . . 327
Получение результатов . . . . . . . . . . . . . . . . . . . . . . . . 330
Глава 17. Статистика
331
17.1. Базовая статистика . . . . . . . . . . . . . . . . . . . . . . . . . . 331
17.2. Неопределенные значения
. . . . . . . . . . . . . . . . . . . . . 335
17.3. Уникальные значения
. . . . . . . . . . . . . . . . . . . . . . . . 336
17.4. Наиболее частые значения
. . . . . . . . . . . . . . . . . . . . . 338
17.5. Гистограмма . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
17.6. Статистика для нескалярных типов данных
. . . . . . . . . . . 345
17.7. Средний размер поля . . . . . . . . . . . . . . . . . . . . . . . . . 346
17.8. Корреляция
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
10