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

• Введение в Postgres. Особенности СУБД Postgres по сравнению с Oracle, Microsoft SQL Server, MySQL

• Проектирование системы баз данных. Выбор типа базы данных: OLTP, хранилище данных (Data Warehouse, DW), шлюзовые базы данных (staging databases), OLAP. Особенности проектирования каждого типа

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

• Нормализация таблиц. Создание таблиц.

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

• Применение доменов (domains) и правил (rules) в Postgres.

• Пользовательские типы данных как дополнительная возможность обеспечения целостности данных.

• Работа с представлениями (views) в Postgres.

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

 

2.   Запросы

• Основы синтаксиса языка запросов 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() и другие.

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

 

3.   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. Виды триггеров, лучшие практики применения триггеров. Триггеры аудита изменений в таблицах.

• Создание функций Postgres на Python и Java. Особенности работы с функциями, созданными не на PL/pgSQL.

 

4.   Оптимизация

• Мониторинг производительности при помощи системных представлений сервера Postgres.

• Представления pg_stat и pg_stat_io.

• Добавление дополнительных представлений производительности. Включение pg_stat_statements.

• Анализ архитектуры приложения и системы баз данных Postgres. Разделение данных на текущие и архивные. Виды баз данных: OLTP, Data Warehouse, staging databases, OLAP. Модификация существующих баз данных для разделения данных на текущие и архивные.

• Настройка подсистемы подключений к Postgres. Выбор типа драйвера подключения.

• Применение пулов подключений (connection pool).

• Анализ настроек сервера Postgres с точки зрения влияния на производительность.

• Файл postgresql.conf.

• Параметры shared_buffers,  wal_buffers,  effective_cache_size, maintenance_work_mem, max_parallel_workers_per_gather и другие.

• Сравнение производительности при разных вариантах настроек и применение pgbench.

• Анализ системы индексов.

• Применение представления pg_stat_user_tables, отношение idx_scan к seq_scan.

• Типы индексов в Postgres.

• Настройка оптимальной системы индексов для баз данных Postgres.

• Секционирование (partitioning) и шардинг (sharding) в Postgres.

• Распределение нагрузки по дискам и по набору серверов.

• Распухание (bloating) и фрагментация таблиц баз данных Postgres как особенности версионной системы изменений. Мониторинг подсистемы VACUUM и ее настройка.

• Анализ системы автоматического пересчета статистики в базах данных Postgres.

• Проверка адекватности статистики и настройка ручного пересчета статистики.

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

• Лучшие практики при оптимизации запросов в Postgres.