Учебник PostgreSQL для начинающих

Программа курса

1. 1 Введение в PostgreSQL

   1. Что такое PostgreSQL и чем он выделяется

      PostgreSQL — мощная объектно-реляционная СУБД с открытым кодом. Разбираем её сильные стороны: надёжность, расширяемость, стандарты SQL и работу с JSON.

   2. Установка и подключение к базе

      Как установить PostgreSQL на разных системах, что такое сервер, кластер и роль, и как подключиться к базе через psql и строку подключения.

   3. psql и мета-команды

      Учимся работать в psql: мета-команды с обратным слешем (\l, \dt, \d, \q), просмотр таблиц и структуры, выполнение SQL-файлов.

   4. Создание базы, таблицы и GUI-клиенты

      Создаём первую базу данных и таблицу, наполняем её данными и делаем выборку. Обзор графических клиентов pgAdmin и DBeaver.

2. 2 Типы данных PostgreSQL

   1. Числовые типы: integer, bigint, numeric

      Целые и дробные числа в PostgreSQL: smallint, integer, bigint, numeric и real. Когда нужна точность numeric, а когда хватит integer.

   2. Автонумерация: serial и identity

      Как PostgreSQL автоматически нумерует строки: типы serial и bigserial, современный GENERATED AS IDENTITY и последовательности (sequence).

   3. Текст и даты: varchar, text, timestamp, interval

      Строковые типы varchar и text, типы даты и времени date, timestamp, timestamptz и interval. Как PostgreSQL хранит и сравнивает время.

   4. Булев, спецтипы и приведение типов

      Логический тип boolean, специальные типы uuid, json/jsonb и массивы, а также приведение типов через CAST и оператор :: в PostgreSQL.

3. 3 Таблицы, ограничения и схема

   1. CREATE TABLE подробно

      Разбираем команду CREATE TABLE в PostgreSQL: объявление столбцов, значения по умолчанию, временные таблицы и удаление таблиц через DROP.

   2. Ограничения целостности

      Ограничения PostgreSQL: PRIMARY KEY, UNIQUE, NOT NULL, CHECK и DEFAULT. Как база сама защищает данные от некорректных значений.

   3. Внешние ключи и связи таблиц

      Связываем таблицы через FOREIGN KEY. Действия ON DELETE и ON UPDATE: CASCADE, RESTRICT, SET NULL. Как поддерживать ссылочную целостность.

   4. ALTER TABLE и нормализация

      Изменяем существующие таблицы командой ALTER TABLE: добавление и удаление столбцов, ограничений. Кратко о нормализации и разбиении данных.

4. 4 Запросы и выборка данных

   1. SELECT, WHERE, ORDER BY, LIMIT

      Основы выборки данных: SELECT с фильтрацией WHERE, сортировка ORDER BY, ограничение LIMIT и OFFSET. Практика в живой SQL-песочнице.

   2. Соединения таблиц: JOIN

      Все виды JOIN в SQL: INNER, LEFT, RIGHT и FULL. Как соединять таблицы по ключу и что происходит с несовпавшими строками. Практика в песочнице.

   3. Агрегаты, GROUP BY и HAVING

      Агрегатные функции COUNT, SUM, AVG, MIN, MAX. Группировка строк через GROUP BY и фильтрация групп через HAVING. Практика в песочнице.

   4. Подзапросы, CTE и оконные функции

      Вложенные подзапросы, общие табличные выражения WITH (CTE) для читаемости и оконные функции PostgreSQL: ROW_NUMBER, RANK, SUM OVER.

5. 5 Изменение данных и производительность

   1. INSERT, UPDATE, DELETE и RETURNING

      Изменение данных в PostgreSQL: вставка INSERT, обновление UPDATE, удаление DELETE и фирменное предложение RETURNING для получения изменённых строк.

   2. UPSERT через ON CONFLICT

      UPSERT в PostgreSQL: команда INSERT ... ON CONFLICT DO UPDATE/DO NOTHING. Как вставить строку или обновить существующую одним запросом.

   3. Транзакции и ACID

      Транзакции PostgreSQL: BEGIN, COMMIT, ROLLBACK и принципы ACID. Как несколько операций выполняются как единое целое и откатываются при сбое.

   4. Индексы, EXPLAIN и VACUUM

      Зачем нужны индексы в PostgreSQL: B-tree, частичные индексы, ускорение поиска. Анализ запросов через EXPLAIN ANALYZE и кратко о VACUUM.

6. 6 Продвинутое и эксплуатация

   1. Работа с JSONB

      JSONB в PostgreSQL: хранение документов, операторы -> и ->>, запросы по содержимому, оператор @> и индексы GIN для быстрого поиска.

   2. Представления (VIEW)

      Представления в PostgreSQL: обычные VIEW как сохранённые запросы и материализованные представления MATERIALIZED VIEW для кеширования тяжёлых вычислений.

   3. Функции и PL/pgSQL

      Пользовательские функции в PostgreSQL: SQL-функции и процедурный язык PL/pgSQL с переменными, условиями и циклами. Обзор триггеров.

   4. Роли, права и резервные копии

      Управление доступом в PostgreSQL: роли и команда GRANT. Резервное копирование через pg_dump и восстановление pg_restore. Что изучать дальше.

7. 7 Индексы и оптимизация запросов

   1. Типы индексов: B-tree, Hash, GiN, GiST, BRIN

      Когда какой индекс в PostgreSQL: B-tree по умолчанию для сравнений и сортировки, GIN для массивов, JSONB и полнотекста, GiST для геометрии и диапазонов, BRIN для огромных отсортированных таблиц, Hash для равенства.

   2. EXPLAIN и EXPLAIN ANALYZE: читаем план

      Как читать план запроса в PostgreSQL: узлы Seq Scan, Index Scan, Bitmap Scan, поля cost, rows и width, разница EXPLAIN и EXPLAIN ANALYZE, actual time, BUFFERS и что значит «дорого».

   3. Стратегия индексирования: составные, частичные, по выражению

      Проектирование индексов в PostgreSQL: порядок столбцов в составном индексе, частичный индекс с WHERE, индекс по выражению, покрывающий индекс с INCLUDE и Index Only Scan для covered-запросов.

   4. Почему индекс не используется

      Частые причины, по которым PostgreSQL не использует индекс: функция на столбце, неявные касты типов, низкая селективность, устаревшая статистика без ANALYZE, LIKE с ведущим процентом и условия с OR.

8. 8 JSONB, массивы и полнотекстовый поиск

   1. JSONB: хранение и доступ

      Тип jsonb в PostgreSQL: чем json отличается от jsonb, операторы -> ->> #>, извлечение и вложенность полей, и когда JSONB лучше обычных колонок.

   2. Запросы и индексы по JSONB

      Поиск по JSONB в PostgreSQL: операторы @> ? ?|, GIN-индекс по jsonb, jsonb_path_query и JSONPath, обновление через jsonb_set и частые ошибки.

   3. Массивы в PostgreSQL

      Массивы в PostgreSQL: тип array, операторы ANY и ALL, разворачивание через unnest, оператор содержания и GIN-индекс, и когда массив, а когда отдельная таблица.

   4. Полнотекстовый поиск: tsvector и tsquery

      Полнотекстовый поиск в PostgreSQL: to_tsvector и to_tsquery, ранжирование ts_rank, GIN-индекс, словари и стемминг, и сравнение с LIKE и расширением pg_trgm.

9. 9 MVCC, VACUUM и обслуживание

   1. MVCC изнутри: версии строк, xmin/xmax

      Как PostgreSQL хранит несколько версий одной строки, что такое скрытые столбцы xmin и xmax, как снимок транзакции решает видимость версий и почему читатели не блокируют писателей.

   2. Мёртвые кортежи и раздувание таблиц

      Откуда в PostgreSQL берётся bloat: мёртвые версии строк после UPDATE и DELETE, как заметить раздувание таблиц и индексов через pg_stat_user_tables и расширения, чем оно вредит производительности.

   3. VACUUM и autovacuum

      Что делает VACUUM в PostgreSQL, чем обычный VACUUM отличается от VACUUM FULL, как устроен и настраивается autovacuum, зачем нужен ANALYZE для статистики и когда вмешиваться вручную.

   4. Уровни изоляции и блокировки в PostgreSQL

      Изоляция в PostgreSQL: Read Committed по умолчанию, Repeatable Read через снимок, Serializable и SSI, явные блокировки строк и таблиц, advisory locks и обнаружение deadlock.

10. 10 Масштабирование, репликация и эксплуатация

    1. Партиционирование таблиц

       Декларативное партиционирование в PostgreSQL по диапазону, списку и хешу: зачем дробить большие таблицы, как работает partition pruning и частые грабли.

    2. Репликация: потоковая и логическая

       Репликация PostgreSQL: primary и standby, потоковая репликация через WAL, hot standby для чтения, логическая репликация, синхронный и асинхронный режимы.

    3. Пулы соединений и масштабирование чтения

       Почему соединения в PostgreSQL дорогие, как PgBouncer экономит ресурсы, режимы пулинга session/transaction/statement и масштабирование чтения репликами.

    4. Бэкапы, расширения и мониторинг

       Бэкапы pg_dump/pg_restore, физический бэкап и восстановление на точку во времени (PITR) через WAL, расширения CREATE EXTENSION и мониторинг через pg_stat_*.