Schönig Hans-Jürgen / Шениг Ганс-Юрген - Mastering PostgreSQL 11 / PostgreSQL 11. Мастерство разработки [2019, PDF, RUS]

Оглавление

Об авторе 11
О рецензенте 12
Предисловие 13
Глава 1. Обзор PostgreSQL 16
Что нового в PostgreSQL 11.0? 16
Новые средства администрирования базы данных 17
Усовершенствования в индексировании и оптимизации 18
Улучшенное управление кешем 20
Улучшенные оконные функции 20
Добавление JIT-компиляции 21
Улучшенное секционирование 21
Поддержка хранимых процедур 22
Улучшение команды ALTER TABLE 23
Резюме 24
Вопросы 24
Глава 2. Транзакции и блокировка 25
Работа с транзакциями в PostgreSQL 25
Обработка ошибок внутри транзакции 27
Использование команды SAVEPOINT 28
Транзакционные DDL-команды 29
Основы блокировки 30
Предотвращение типичных ошибок и явная блокировка 31
Использование фраз FOR SHARE и FOR UPDATE 33
Уровни изоляции транзакций 36
SSI-транзакции 38
Взаимоблокировки и смежные вопросы 38
Рекомендательные блокировки 40
Оптимизация хранилища и управление очисткой 41
Настройка VACUUM и autovacuum 41
Наблюдение за работой VACUUM 43
Ограничение длительности транзакций с помощью времени жизни снимка 46
Резюме 47
Вопросы 47
Глава 3. Использование индексов 49
Простые запросы и стоимостная модель 49
Использование команды EXPLAIN 50
Стоимостная модель PostgreSQL 52
Развертывание простых индексов 54
Сортировка результатов 55
Эффективное использование просмотра по битовой карте 57
Разумное использование индексов 57
Повышение быстродействия с помощью кластеризованных таблиц 59
Кластеризация таблиц 62
Просмотр только индекса 62
Дополнительные свойства B-деревьев 63
Комбинированные индексы 63
Добавление функциональных индексов 64
Уменьшение занятого места на диске 65
Добавление данных во время индексирования 66
Введение в классы операторов 67
Создание класса операторов для B-дерева 68
Типы индексов в PostgreSQL 73
Хеш-индексы 73
GiST-индексы 73
GIN-индексы 76
SP-GiST-индексы 77
BRIN-индексы 77
Добавление новых типов индексов 79
Получение более точных ответов с помощью нечеткого поиска 80
Расширение pg_trgm и его достоинства 80
Ускорение запросов с предикатом LIKE 82
Регулярные выражения 83
Полнотекстовый поиск 83
Сравнение строк 84
Определение GIN-индексов 85
Отладка поиска 86
Сбор статистики по словам 87
О пользе операторов исключения 87
Резюме 88
Вопросы 88
Глава 4. Передовые средства SQL 90
Введение в наборы группирования 90
Загрузка тестовых данных 90
Применение наборов группирования 91
Сочетание наборов группирования с фразой FILTER 94
Использование упорядоченных наборов 95
Гипотетические агрегаты 97
Оконные функции и аналитические средства 97
Разбиение данных 98
Упорядочение данных внутри окна 99
Скользящие окна 100
Абстрагирование окон 102
Использование встроенных оконных функций 103
Создание собственных агрегатов 109
Создание простых агрегатов 109
Добавление поддержки параллельных запросов 112
Повышение эффективности 113
Написание гипотетических агрегатов 114
Резюме 116
Глава 5. Журналы и статистика системы 117
Сбор статистических данных о работе системы 117
Системные представления в PostgreSQL 117
Создание файлов журналов 134
Конфигурационный файл postgresql.conf 134
Резюме 139
Вопросы 139
Глава 6. Оптимизация запросов для достижения максимальной производительности 141
Что делает оптимизатор 141
Оптимизация на примере 142
Разбираемся в планах выполнения 151
Систематический подход к планам выполнения 151
Выявление проблем 153
Соединения: осмысление и исправление 157
Как соединять правильно 157
Обработка внешних соединений 158
Параметр join_collapse_limit 159
Включение и выключение режимов оптимизатора 160
Генетическая оптимизация запросов 163
Секционирование данных 164
Создание секций 164
Применение табличных ограничений 166
Модификация наследуемой структуры 167
Перемещение таблицы в наследуемую структуру и из нее 168
Очистка данных 168
Секционирование в PostgreSQL 11.0 169
Настройка параметров для повышения производительности запросов 171
Ускорение сортировки 173
Ускорение административных задач 175
Распараллеливание запросов 176
Что PostgreSQL умеет делать параллельно? 179
Распараллеливание на практике 179
Введение в JIT-компиляцию 180
Настройка JIT 181
Выполнение запросов 181
Резюме 183
Глава 7. Написание хранимых процедур 184
Языки хранимых процедур 184
Фундаментальные основы – хранимые процедуры и функции 185
Анатомия функции 186
Языки хранимых процедур 189
Введение в PL/pgSQL 190
Создание хранимых процедур на PL/pgSQL 204
Введение в PL/Perl 205
Введение в PL/Python 211
Улучшение функций 214
Уменьшение числа вызовов функций 214
Резюме 218
Вопросы 218
Глава 8. Безопасность в PostgreSQL 220
Управление сетевой безопасностью 220
Подключения и адреса привязки 221
Файл pg_hba.conf 224
Безопасность на уровне экземпляра 228
Задание безопасности на уровне базы данных 232
Задание прав на уровне схемы 233
Работа с таблицами 235
Задание прав на уровне столбцов 236
Задание привилегий по умолчанию 237
Безопасность на уровне строк 238
Просмотр прав 242
Передача объектов и удаление пользователей 243
Резюме 245
Вопросы 245
Глава 9. Резервное копирование и восстановление 246
Простая выгрузка 246
Запуск pg_dump 247
Задание пароля и информации о подключении 248
Извлечение подмножества данных 250
Форматы резервной копии 250
Восстановление из резервной копии 252
Сохранение глобальных данных 253
Резюме 253
Вопросы 254
Глава 10. Резервное копирование и репликация 255
Что такое журнал транзакций 255
Знакомство с журналом транзакций 256
Контрольные точки 257
Оптимизация журнала транзакций 257
Архивация и восстановление журнала транзакций 259
Настройка архивации 259
Конфигурирование файла pg_hba.conf 260
Создание базовой резервной копии 261
Воспроизведение журнала транзакций 263
Очистка архива журналов транзакций 267
Настройка асинхронной репликации 268
Базовая настройка 268
Остановка и возобновление репликации 270
Проверка состояния репликации для обеспечения доступности 271
Отработка отказов и линии времени 274
Управление конфликтами 275
Повышение надежности репликации 276
Переход на синхронную репликацию 277
Настройка долговечности 279
Слоты репликации 280
Работа с физическими слотами репликации 281
Работа с логическими слотами репликации 283
Использование команд CREATE PUBLICATION и CREATE SUBSCRIPTION 285
Резюме 287
Вопросы 287
Глава 11. Полезные расширения 289
Как работают расширения 289
Проверка доступных расширений 290
Использование модулей из подборки contrib 293
Модуль adminpack 293
Применение фильтра Блума 294
Установка btree_gist и btree_gin 296
Dblink – пора расстаться 297
Доступ к файлам с помощью file_fdw 298
Анализ хранилища с помощью pageinspect 299
Анализ кеша с помощью pg_buffercache 301
Шифрование данных с помощью pgcrypto 302
Прогрев кеша с помощью pg_prewarm 302
Анализ производительности с помощью pg_stat_statements 304
Анализ хранилища с помощью pgstattuple 304
Нечеткий поиск с помощью pg_trgm 305
Подключение к удаленному серверу с помощью postgres_fdw 305
Другие полезные расширения 309
Резюме 310
Глава 12. Поиск и устранение неполадок 311
Первоначальное изучение незнакомой базы данных 311
Анализ результатов pg_stat_activity 311
Опрос pg_stat_activity 312
Выявление медленных запросов 314
Анализ отдельных запросов 315
Углубленный анализ с помощью perf 316
Анализ журнала 317
Анализ наличия индексов 318
Анализ памяти и ввода-вывода 318
О конкретных ошибочных ситуациях 320
Повреждение clog 320
Что означают сообщения о контрольной точке 321
Что делать с поврежденными страницами данных 322
Беззаботное управление подключениями 323
Борьба с разбуханием таблиц 323
Резюме 324
Вопросы 324
Глава 13. Переход на PostgreSQL 325
Перенос команд SQL в PostgreSQL 325
Латеральные соединения 325
Наборы группирования 326
Фраза WITH – общие табличные выражения 327
Фраза WITH RECURSIVE 328
Фраза FILTER 328
Оконные функции 329
Упорядоченные наборы – фраза WITHIN GROUP 329
Фраза TABLESAMPLE 330
Ограничение выборки и смещение 331
Фраза OFFSET 331
Темпоральные таблицы 332
Сопоставление с образцом во временных рядах 332
Переход с Oracle на PostgreSQL 332
Использование расширения oracle_fdw для переноса данных 333
Использование ora2pg для перехода с Oracle 334
Распространенные подводные камни 336
ora_migrator – быстрая миграция Oracle в PostgreSQL 337
Переход из MySQL или MariaDB на PostgreSQL 338
Обработка данных в MySQL и MariaDB 339
Перенос данных и схемы 343
Резюме 345
Предметный указатель 346