Postgres для инженеров по тестированию производительности

Темы курса:

Выполнение простых и перекрестных запросов

Изменение данных в базе;

Понимание механизмов планирования и выполнения запросов;

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

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

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

Использование основных объектов БД: таблицы, индексы, представления;

Продолжительность курса: 24 часа

План курса:

1 . Использование основных объектов БД: таблицы, индексы, представления

Проектирование сущностей (таблиц) и атрибутов (столбцов). Выбор типов данных для столбцов таблиц. Особенности системы типов данных в Postgres. Нормализация таблиц. Создание таблиц.

Ограничения целостности данных в Postgres. Первичные и внешние ключи, ограничения уникальности, условия на значения (check constraints). Применение доменов (domains) и правил (rules) в Postgres. Пользовательские типы данных как дополнительная возможность обеспечения целостности данных.

Работа с представлениями (views) в Postgres. Для чего нужны назначения. Создание обычных, материализованных и рекурсивных представлений.

Система индексов. Применение представления pg_stat_user_tables, отношение idx_scan к seq_scan. Типы индексов в Postgres. Создание индексов. Настройка оптимальной системы индексов для баз данных Postgres.

  1. Выполнение простых и перекрестных запросов

Основы синтаксиса языка запросов Postgres SQL. Базовый синтаксис SELECT. Список столбцов и DISTINCT.

Сортировка в запросах Postgres SQL. Выражение ORDER BY.

Фильтрация в запросах Postgres SQL. Выражение WHERE. Логические операторы  AND, OR, NOT, IN, BETWEEN.  Поиск с использованием подстановочных символов (LIKE) и регулярных выражений (SIMILAR TO).

Работа с пустыми значениями. Оператор IS NULL и функция COALESCE.

Ограничение количества выводимых записей. LIMIT и FETCH.

Встроенные скалярные функции в Postgres SQL: строковые, математические, даты/времени.

Объединение нескольких таблиц в запросах. INNER JOIN, LEFT OUTER JOIN, RIGTH OUTER JOIN, FULL JOIN, SELF JOIN, CROSS JOIN. Особенности работы с объединениями в Postgres.

Группировка в запросах Postgres SQL. Выражения GROUP BY, HAVING, CUBE, ROLLUP, GROUPING SETS. Агрегатные функции: SUM(), MIN(), MAX(), AVG(), COUNT и другие.

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

Работа с наборами значений. UNION, EXCEPT, INTERSECT, SOME/ANY/ALL, EXISTS.

Временные таблицы и общие табличные выражения (CTE, Common Table Expressions). Декомпозиция запросов. Иерархические запросы в Postgres.

Функции ранжирования в Postgres: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() и другие.

  1. Изменение данных в базе

Команды, изменяющие данные: INSERT, UPDATE, DELETE, TRUNCATE в Postgres SQL. SELECT INTO и INSERT..SELECT. Работа с транзакциями в Postgres

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

Анализ настроек сервера Postgres с точки зрения влияния на производительность. Файл postgresql.conf. Параметры shared_buffers,  wal_buffers,  effective_cache_size, maintenance_work_mem, max_parallel_workers_per_gather и другие. Сравнение производительности при разных вариантах настроек и применение pgbench

  1. Поиск проблемных запросов и их оптимизация

Оптимизация запросов в Postgres. Выбор запросов для оптимизации. Команда EXPLAIN. Применение параметров конфигурации на уровне сеанса. Развертывание модуля pg_hint_plans и применение хинтов в запросах. Лучшие практики при оптимизации запросов в Postgres