PostgreSQL 15 изнутри

Компания Postgres Professional

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

Москва, 2023

УДК 004.65
ББК 32.972.134
Р59

Рогов Е. В.

Р59
PostgreSQL 15 изнутри. — М.: ДМК Пресс, 2023. — 662 с.

ISBN 978-5-93700-178-8

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

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

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

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

УДК 004.65
ББК 32.972.134

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

ISBN 978-5-6045970-2-6
© Текст, оформление, ООО «ППГ», 2023
ISBN 978-5-93700-178-8
© Издание, ДМК Пресс, 2023

Оглавление

О книге . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17
Глава 1. Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
23

Часть I. Изоляция и многоверсионность
45
Глава 2. Изоляция . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
47
Глава 3. Страницы и версии строк . . . . . . . . . . . . . . . . . . . . . .
75
Глава 4. Снимки данных . . . . . . . . . . . . . . . . . . . . . . . . . . . .
97
Глава 5. Внутристраничная очистка и hot-обновления . . . . . . . . . . 111
Глава 6. Очистка и автоочистка . . . . . . . . . . . . . . . . . . . . . . . . 124
Глава 7. Заморозка
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Глава 8. Перестроение таблиц и индексов . . . . . . . . . . . . . . . . . . 163

Часть II. Буферный кеш и журнал
175
Глава 9. Буферный кеш . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Глава 10. Журнал предзаписи . . . . . . . . . . . . . . . . . . . . . . . . . 198
Глава 11. Режимы журнала . . . . . . . . . . . . . . . . . . . . . . . . . . . 220

Часть III. Блокировки
239
Глава 12. Блокировки отношений
. . . . . . . . . . . . . . . . . . . . . . 241
Глава 13. Блокировки строк . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Глава 14. Блокировки разных объектов . . . . . . . . . . . . . . . . . . . 279
Глава 15. Блокировки в памяти . . . . . . . . . . . . . . . . . . . . . . . . 291

Часть IV. Выполнение запросов
301
Глава 16. Этапы выполнения запросов . . . . . . . . . . . . . . . . . . . . 303
Глава 17. Статистика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Глава 18. Табличные методы доступа
. . . . . . . . . . . . . . . . . . . . 352
Глава 19. Индексные методы доступа . . . . . . . . . . . . . . . . . . . . 375
Глава 20. Индексное сканирование . . . . . . . . . . . . . . . . . . . . . . 395
Глава 21. Вложенный цикл . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

3

Оглавление

Глава 22. Хеширование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Глава 23. Сортировка и слияние
. . . . . . . . . . . . . . . . . . . . . . . 466

Часть V. Типы индексов
493
Глава 24. Хеш-индекс . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
Глава 25. B-дерево
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Глава 26. Индекс GiST
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Глава 27. Индекс SP-GiST . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
Глава 28. Индекс GIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
Глава 29. Индекс BRIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622

Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
Предметный указатель . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651

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-цепочки
. . . . . . . . . . . . . . . . . . . . . . . . . 120
5.5. Внутристраничная очистка индексов
. . . . . . . . . . . . . . . 122

6

Содержание

Глава 6. Очистка и автоочистка
124
6.1. Очистка вручную . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
6.2. Еще раз о горизонте базы данных
. . . . . . . . . . . . . . . . . 127
6.3. Этапы выполнения очистки . . . . . . . . . . . . . . . . . . . . . 130
Сканирование таблицы
. . . . . . . . . . . . . . . . . . . . . . . 130
Очистка индексов . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Очистка таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Усечение таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
6.4. Анализ
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
6.5. Автоматическая очистка и анализ . . . . . . . . . . . . . . . . . 133
Устройство автоочистки . . . . . . . . . . . . . . . . . . . . . . . 134
Какие таблицы требуют очистки . . . . . . . . . . . . . . . . . . 135
Какие таблицы требуют анализа
. . . . . . . . . . . . . . . . . . 137
Автоочистка в действии . . . . . . . . . . . . . . . . . . . . . . . 138
6.6. Регулирование нагрузки . . . . . . . . . . . . . . . . . . . . . . . 142
Управление интенсивностью обычной очистки
. . . . . . . . . 143
Управление интенсивностью автоочистки
. . . . . . . . . . . . 143
6.7. Мониторинг очистки . . . . . . . . . . . . . . . . . . . . . . . . . 144
Отслеживание выполнения ручной очистки
. . . . . . . . . . . 145
Отслеживание выполнения автоочистки
. . . . . . . . . . . . . 147

Глава 7. Заморозка
149
7.1. Переполнение счетчика транзакций . . . . . . . . . . . . . . . . 149
7.2. Заморозка версий и правила видимости
. . . . . . . . . . . . . 150
7.3. Управление заморозкой . . . . . . . . . . . . . . . . . . . . . . . 153
Минимальный возраст для заморозки . . . . . . . . . . . . . . . 154
Возраст для агрессивной заморозки . . . . . . . . . . . . . . . . 156
Возраст для аварийного срабатывания автоочистки . . . . . . . 158
Возраст для приоритетного режима заморозки
. . . . . . . . . 160
7.4. Заморозка вручную . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Очистка с заморозкой
. . . . . . . . . . . . . . . . . . . . . . . . 161
Заморозка при загрузке . . . . . . . . . . . . . . . . . . . . . . . 161

Глава 8. Перестроение таблиц и индексов
163
8.1. Полная очистка . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Необходимость
. . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Оценка плотности информации
. . . . . . . . . . . . . . . . . . 164

7

Содержание

Заморозка
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
8.2. Другие способы перестроения
. . . . . . . . . . . . . . . . . . . 169
Аналоги полной очистки . . . . . . . . . . . . . . . . . . . . . . . 169
Перестроение без долгих блокировок . . . . . . . . . . . . . . . 170
8.3. Профилактика . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Читающие запросы . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Обновление данных
. . . . . . . . . . . . . . . . . . . . . . . . . 172

Часть II. Буферный кеш и журнал
175

Глава 9. Буферный кеш
177
9.1. Кеширование
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
9.2. Устройство буферного кеша . . . . . . . . . . . . . . . . . . . . . 178
9.3. Попадание в кеш
. . . . . . . . . . . . . . . . . . . . . . . . . . . 180
9.4. Промах кеша . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Поиск буфера и вытеснение . . . . . . . . . . . . . . . . . . . . . 186
9.5. Массовое вытеснение
. . . . . . . . . . . . . . . . . . . . . . . . 188
9.6. Настройка размера . . . . . . . . . . . . . . . . . . . . . . . . . . 191
9.7. Прогрев кеша
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
9.8. Локальный кеш . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

Глава 10. Журнал предзаписи
198
10.1. Журналирование
. . . . . . . . . . . . . . . . . . . . . . . . . . . 198
10.2. Устройство журнала
. . . . . . . . . . . . . . . . . . . . . . . . . 200
Логическая структура . . . . . . . . . . . . . . . . . . . . . . . . . 200
Физическая структура
. . . . . . . . . . . . . . . . . . . . . . . . 203
10.3. Контрольная точка . . . . . . . . . . . . . . . . . . . . . . . . . . 205
10.4. Восстановление . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
10.5. Фоновая запись . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
10.6. Настройка
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Настройка контрольной точки
. . . . . . . . . . . . . . . . . . . 214
Настройка фоновой записи . . . . . . . . . . . . . . . . . . . . . 217
Мониторинг . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217

Глава 11. Режимы журнала
220
11.1. Производительность . . . . . . . . . . . . . . . . . . . . . . . . . 220

8

Содержание

11.2. Надежность
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Кеширование
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Повреждение данных . . . . . . . . . . . . . . . . . . . . . . . . . 226
Неатомарность записи . . . . . . . . . . . . . . . . . . . . . . . . 228
11.3. Уровни журнала . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Minimal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Replica
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Logical . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

Часть III. Блокировки
239

Глава 12. Блокировки отношений
241
12.1. Общие сведения о блокировках . . . . . . . . . . . . . . . . . . . 241
12.2. Тяжелые блокировки . . . . . . . . . . . . . . . . . . . . . . . . . 244
12.3. Блокировки номеров транзакций
. . . . . . . . . . . . . . . . . 246
12.4. Блокировки отношений . . . . . . . . . . . . . . . . . . . . . . . 247
12.5. Очередь ожидания
. . . . . . . . . . . . . . . . . . . . . . . . . . 250

Глава 13. Блокировки строк
254
13.1. Устройство . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
13.2. Режимы блокировки строки . . . . . . . . . . . . . . . . . . . . . 255
Исключительные режимы . . . . . . . . . . . . . . . . . . . . . . 255
Разделяемые режимы
. . . . . . . . . . . . . . . . . . . . . . . . 257
13.3. Мультитранзакции . . . . . . . . . . . . . . . . . . . . . . . . . . 258
13.4. Очередь ожидания
. . . . . . . . . . . . . . . . . . . . . . . . . . 260
Исключительные режимы . . . . . . . . . . . . . . . . . . . . . . 260
Разделяемые режимы
. . . . . . . . . . . . . . . . . . . . . . . . 267
13.5. Блокировка без ожидания . . . . . . . . . . . . . . . . . . . . . . 270
13.6. Взаимоблокировки . . . . . . . . . . . . . . . . . . . . . . . . . . 272
Взаимоблокировка при обновлении строк
. . . . . . . . . . . . 274
Взаимоблокировка двух команд UPDATE
. . . . . . . . . . . . . 275

Глава 14. Блокировки разных объектов
279
14.1. Блокировки неотношений . . . . . . . . . . . . . . . . . . . . . . 279
14.2. Блокировки расширения отношения . . . . . . . . . . . . . . . . 281
14.3. Блокировки страниц . . . . . . . . . . . . . . . . . . . . . . . . . 282

9

Содержание

14.4. Рекомендательные блокировки . . . . . . . . . . . . . . . . . . . 282
14.5. Предикатные блокировки . . . . . . . . . . . . . . . . . . . . . . 284

Глава 15. Блокировки в памяти
291
15.1. Спин-блокировки . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
15.2. Легкие блокировки . . . . . . . . . . . . . . . . . . . . . . . . . . 292
15.3. Примеры . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Буферный кеш . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Буферы журнала предзаписи . . . . . . . . . . . . . . . . . . . . 294
15.4. Мониторинг ожиданий . . . . . . . . . . . . . . . . . . . . . . . . 295
15.5. Семплирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

Часть IV. Выполнение запросов
301

Глава 16. Этапы выполнения запросов
303
16.1. Демонстрационная база данных . . . . . . . . . . . . . . . . . . 303
16.2. Протокол простых запросов . . . . . . . . . . . . . . . . . . . . . 306
Разбор
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Трансформация . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Планирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Исполнение
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
16.3. Протокол расширенных запросов
. . . . . . . . . . . . . . . . . 321
Подготовка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Привязка параметров
. . . . . . . . . . . . . . . . . . . . . . . . 322
Планирование и исполнение . . . . . . . . . . . . . . . . . . . . 323
Получение результатов . . . . . . . . . . . . . . . . . . . . . . . . 326

Глава 17. Статистика
327
17.1. Базовая статистика . . . . . . . . . . . . . . . . . . . . . . . . . . 327
17.2. Неопределенные значения
. . . . . . . . . . . . . . . . . . . . . 331
17.3. Уникальные значения
. . . . . . . . . . . . . . . . . . . . . . . . 332
17.4. Наиболее частые значения
. . . . . . . . . . . . . . . . . . . . . 334
17.5. Гистограмма . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
17.6. Статистика для нескалярных типов данных
. . . . . . . . . . . 341
17.7. Средний размер поля . . . . . . . . . . . . . . . . . . . . . . . . . 342
17.8. Корреляция
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342

10