Postgres для системных аналитиков

Темы курса:

Архитектура баз данных, структура баз, PL/pgSQL, оптимизация

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

План курса:

Архитектура и структура баз данных Postgres

  1. Введение в Postgres. Особенности СУБД Postgres по сравнению с Oracle, Microsoft SQL Server, MySQL
  2. Таблицы и столбцы. Выбор типов данных для столбцов таблиц. Особенности системы типов данных в Postgres. Нормализация таблиц. Создание таблиц.
  3. Ограничения целостности данных в Postgres. Первичные и внешние ключи, ограничения уникальности, условия на значения (check constraints). Применение доменов (domains) и правил (rules) в Postgres. Пользовательские типы данных как дополнительная возможность обеспечения целостности данных.
  4. Работа с представлениями (views) в Postgres. Для чего нужны назначения. Создание обычных, материализованных и рекурсивных представлений.

PL/pgSQL

  1. Введение в язык PL/pgSQL. Основы синтаксиса, структура блоков BEGIN..END, символы строки в виде долларов $$. Работа с анонимными блоками PL/pgSQL.
  2. Переменные и константы в PL/pgSQL. Объявление переменных, присвоение значений, SELECT INTO для присвоения значений переменных из запросов. Переменные типа RECORD. Объявления %ROWTYPE.
  3. Синтаксические конструкции PL/pgSQL. Операторы условного перехода: IF и CASE. Циклы WHILE и FOR.
  4. Обработка исключений в PL/pgSQL. Блоки EXCEPTION, команды RAISE и ASSERT.
  5. Работа с курсорами в Postgres. Применение курсоров для прохода по набору записей.
  6. Создание пользовательских функций PL/pgSQL. Работа с параметрами IN и OUT, возврат значений из функции. Типы функций IMMUTABLE, STABLE и VOLATILE. Перегрузка функций и особенности функций, возвращающих таблицы.
  7. Создание пользовательских хранимых процедур в Postgres. Отличия хранимых процедур от функций.
  8. Триггеры и триггерные функции в Postgres. Виды триггеров, лучшие практики применения триггеров. Триггеры аудита изменений в таблицах.
  9. Создание функций Postgres на Python и Java. Особенности работы с функциями, созданными не на PL/pgSQL.

Оптимизация

  1. Мониторинг производительности при помощи системных представлений сервера Postgres. Представления pg_stat и pg_stat_io. Добавление дополнительных представлений производительности. Включение pg_stat_statements.
  2. Анализ архитектуры приложения и системы баз данных Postgres. Разделение данных на текущие и архивные. Виды баз данных: OLTP, Data Warehouse, staging databases, OLAP. Модификация существующих баз данных для разделения данных на текущие и архивные.
  3. Настройка подсистемы подключений к Postgres. Выбор типа драйвера подключения. Применение пулов подключений (connection pool).
  4. Анализ системы индексов. Применение представления pg_stat_user_tables, отношение idx_scan к seq_scan. Типы индексов в Postgres. Настройка оптимальной системы индексов для баз данных Postgres.
  5. Секционирование (partitioning) и шардинг (sharding) в Postgres. Распределение нагрузки по дискам и по набору серверов.
  6. Распухание (bloating) и фрагментация таблиц баз данных Postgres как особенности версионной системы изменений. Мониторинг подсистемы VACUUM и ее настройка.
  7. Анализ системы автоматического пересчета статистики в базах данных Postgres. Проверка адекватности статистики и настройка ручного пересчета статистики.
  8. Оптимизация запросов в Postgres. Выбор запросов для оптимизации. Команда EXPLAIN. Применение параметров конфигурации на уровне сеанса. Развертывание модуля pg_hint_plans и применение хинтов в запросах. Лучшие практики при оптимизации запросов в Postgres.