Postgres для инженеров-разработчиков

Темы курса:

Понимание общего устройства, умение работать с буферным кэшем и журналом статистики. Умение обработки deadlock. Настройка мониторинга. Разграничение доступа. Умение организовывать структуру БД, написание функций, процедур, работа с курсором. Настройка триггеров. Обработка возникающих ошибок. Оптимизация запросов различными способами.

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

План курса:

  1. Общее устройство Postgres

Особенности PostgreSQL, архитектура PostgreSQL, отличия от других СУБД, новые возможности последних версий.  Буферный кэш, статистика, MVCC, система блокировок в Postgres, работа со взаимоблокировками (deadlocks)

  1. Разграничение доступа в Postgres

Настройка аутентификации через файлы конфигурации, роли PostgreSQL, система разрешений PostgreSQL, наилучшие практики при работе с разрешениями

  1. Умение организовывать структуру БД

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

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

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

  1. Язык PL/pgSQL: функции, процедуры, триггеры

Введение в язык PL/pgSQL. Основы синтаксиса, структура блоков BEGIN..END, символы строки в виде долларов $$. Работа с анонимными блоками PL/pgSQL.

Переменные и константы в PL/pgSQL. Объявление переменных, присвоение значений, SELECT INTO для присвоения значений переменных из запросов. Переменные типа RECORD. Объявления %ROWTYPE.

Синтаксические конструкции PL/pgSQL. Операторы условного перехода: IF и CASE. Циклы WHILE и FOR.

Обработка исключений в PL/pgSQL. Блоки EXCEPTION, команды RAISE и ASSERT.

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

Создание пользовательских функций PL/pgSQL. Работа с параметрами IN и OUT, возврат значений из функции. Типы функций IMMUTABLE, STABLE и VOLATILE. Перегрузка функций и особенности функций, возвращающих таблицы.

Создание пользовательских хранимых процедур в Postgres. Отличия хранимых процедур от функций.

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

  1. Оптимизация запросов

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