Базы данных

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

40 уроков · 10 разделов · ~5 ч · бесплатно, без регистрации

Курс PostgreSQL для тех, кто уже владеет базовым SQL и хочет работать именно с PostgreSQL — одной из самых мощных СУБД с открытым кодом. Разберём её типы данных и автонумерацию, ограничения целостности и связи таблиц, выборку данных с JOIN и оконными функциями, изменение данных с RETURNING и UPSERT, транзакции и ACID, индексы и анализ запросов, а также продвинутые темы: JSONB, представления, функции на PL/pgSQL, роли и резервные копии. Переносимые примеры можно сразу запускать в живой SQL-песочнице, а PostgreSQL-специфику мы разбираем отдельно и помечаем.

Курс «PostgreSQL» состоит из 10 разделов и 40 уроков: Введение в PostgreSQL, Типы данных PostgreSQL, Таблицы, ограничения и схема, Запросы и выборка данных, Изменение данных и производительность, Продвинутое и эксплуатация, Индексы и оптимизация запросов, JSONB, массивы и полнотекстовый поиск, MVCC, VACUUM и обслуживание и Масштабирование, репликация и эксплуатация. Уроки идут по порядку — от основ к более сложным темам, в каждом есть объяснение с примерами, а в конце — вопросы для самопроверки. К урокам привязаны задачи с автоматической проверкой: прочитали тему — сразу закрепили её кодом.

Программа учебника

10 разделов · 40 уроков
  1. 1Введение в PostgreSQL4 урока

    1. 1Что такое PostgreSQL и чем он выделяетсяPostgreSQL — мощная объектно-реляционная СУБД с открытым кодом. Разбираем её сильные стороны: надёжность, расширяемость, стандарты SQL и работу с JSON.
    2. 2Установка и подключение к базеКак установить PostgreSQL на разных системах, что такое сервер, кластер и роль, и как подключиться к базе через psql и строку подключения.
    3. 3psql и мета-командыУчимся работать в psql: мета-команды с обратным слешем (\l, \dt, \d, \q), просмотр таблиц и структуры, выполнение SQL-файлов.
    4. 4Создание базы, таблицы и GUI-клиентыСоздаём первую базу данных и таблицу, наполняем её данными и делаем выборку. Обзор графических клиентов pgAdmin и DBeaver.
  2. 2Типы данных PostgreSQL4 урока

    1. 5Числовые типы: integer, bigint, numericЦелые и дробные числа в PostgreSQL: smallint, integer, bigint, numeric и real. Когда нужна точность numeric, а когда хватит integer.
    2. 6Автонумерация: serial и identityКак PostgreSQL автоматически нумерует строки: типы serial и bigserial, современный GENERATED AS IDENTITY и последовательности (sequence).
    3. 7Текст и даты: varchar, text, timestamp, intervalСтроковые типы varchar и text, типы даты и времени date, timestamp, timestamptz и interval. Как PostgreSQL хранит и сравнивает время.
    4. 8Булев, спецтипы и приведение типовЛогический тип boolean, специальные типы uuid, json/jsonb и массивы, а также приведение типов через CAST и оператор :: в PostgreSQL.
  3. 3Таблицы, ограничения и схема4 урока

    1. 9CREATE TABLE подробноРазбираем команду CREATE TABLE в PostgreSQL: объявление столбцов, значения по умолчанию, временные таблицы и удаление таблиц через DROP.
    2. 10Ограничения целостностиОграничения PostgreSQL: PRIMARY KEY, UNIQUE, NOT NULL, CHECK и DEFAULT. Как база сама защищает данные от некорректных значений.
    3. 11Внешние ключи и связи таблицСвязываем таблицы через FOREIGN KEY. Действия ON DELETE и ON UPDATE: CASCADE, RESTRICT, SET NULL. Как поддерживать ссылочную целостность.
    4. 12ALTER TABLE и нормализацияИзменяем существующие таблицы командой ALTER TABLE: добавление и удаление столбцов, ограничений. Кратко о нормализации и разбиении данных.
  4. 4Запросы и выборка данных4 урока

    1. 13SELECT, WHERE, ORDER BY, LIMITОсновы выборки данных: SELECT с фильтрацией WHERE, сортировка ORDER BY, ограничение LIMIT и OFFSET. Практика в живой SQL-песочнице.
    2. 14Соединения таблиц: JOINВсе виды JOIN в SQL: INNER, LEFT, RIGHT и FULL. Как соединять таблицы по ключу и что происходит с несовпавшими строками. Практика в песочнице.
    3. 15Агрегаты, GROUP BY и HAVINGАгрегатные функции COUNT, SUM, AVG, MIN, MAX. Группировка строк через GROUP BY и фильтрация групп через HAVING. Практика в песочнице.
    4. 16Подзапросы, CTE и оконные функцииВложенные подзапросы, общие табличные выражения WITH (CTE) для читаемости и оконные функции PostgreSQL: ROW_NUMBER, RANK, SUM OVER.
  5. 5Изменение данных и производительность4 урока

    1. 17INSERT, UPDATE, DELETE и RETURNINGИзменение данных в PostgreSQL: вставка INSERT, обновление UPDATE, удаление DELETE и фирменное предложение RETURNING для получения изменённых строк.
    2. 18UPSERT через ON CONFLICTUPSERT в PostgreSQL: команда INSERT ... ON CONFLICT DO UPDATE/DO NOTHING. Как вставить строку или обновить существующую одним запросом.
    3. 19Транзакции и ACIDТранзакции PostgreSQL: BEGIN, COMMIT, ROLLBACK и принципы ACID. Как несколько операций выполняются как единое целое и откатываются при сбое.
    4. 20Индексы, EXPLAIN и VACUUMЗачем нужны индексы в PostgreSQL: B-tree, частичные индексы, ускорение поиска. Анализ запросов через EXPLAIN ANALYZE и кратко о VACUUM.
  6. 6Продвинутое и эксплуатация4 урока

    1. 21Работа с JSONBJSONB в PostgreSQL: хранение документов, операторы -> и ->>, запросы по содержимому, оператор @> и индексы GIN для быстрого поиска.
    2. 22Представления (VIEW)Представления в PostgreSQL: обычные VIEW как сохранённые запросы и материализованные представления MATERIALIZED VIEW для кеширования тяжёлых вычислений.
    3. 23Функции и PL/pgSQLПользовательские функции в PostgreSQL: SQL-функции и процедурный язык PL/pgSQL с переменными, условиями и циклами. Обзор триггеров.
    4. 24Роли, права и резервные копииУправление доступом в PostgreSQL: роли и команда GRANT. Резервное копирование через pg_dump и восстановление pg_restore. Что изучать дальше.
  7. 7Индексы и оптимизация запросов4 урока

    1. 25Типы индексов: B-tree, Hash, GiN, GiST, BRINКогда какой индекс в PostgreSQL: B-tree по умолчанию для сравнений и сортировки, GIN для массивов, JSONB и полнотекста, GiST для геометрии и диапазонов, BRIN для огромных отсортированных таблиц, Hash для равенства.
    2. 26EXPLAIN и EXPLAIN ANALYZE: читаем планКак читать план запроса в PostgreSQL: узлы Seq Scan, Index Scan, Bitmap Scan, поля cost, rows и width, разница EXPLAIN и EXPLAIN ANALYZE, actual time, BUFFERS и что значит «дорого».
    3. 27Стратегия индексирования: составные, частичные, по выражениюПроектирование индексов в PostgreSQL: порядок столбцов в составном индексе, частичный индекс с WHERE, индекс по выражению, покрывающий индекс с INCLUDE и Index Only Scan для covered-запросов.
    4. 28Почему индекс не используетсяЧастые причины, по которым PostgreSQL не использует индекс: функция на столбце, неявные касты типов, низкая селективность, устаревшая статистика без ANALYZE, LIKE с ведущим процентом и условия с OR.
  8. 8JSONB, массивы и полнотекстовый поиск4 урока

    1. 29JSONB: хранение и доступТип jsonb в PostgreSQL: чем json отличается от jsonb, операторы -> ->> #>, извлечение и вложенность полей, и когда JSONB лучше обычных колонок.
    2. 30Запросы и индексы по JSONBПоиск по JSONB в PostgreSQL: операторы @> ? ?|, GIN-индекс по jsonb, jsonb_path_query и JSONPath, обновление через jsonb_set и частые ошибки.
    3. 31Массивы в PostgreSQLМассивы в PostgreSQL: тип array, операторы ANY и ALL, разворачивание через unnest, оператор содержания и GIN-индекс, и когда массив, а когда отдельная таблица.
    4. 32Полнотекстовый поиск: tsvector и tsqueryПолнотекстовый поиск в PostgreSQL: to_tsvector и to_tsquery, ранжирование ts_rank, GIN-индекс, словари и стемминг, и сравнение с LIKE и расширением pg_trgm.
  9. 9MVCC, VACUUM и обслуживание4 урока

    1. 33MVCC изнутри: версии строк, xmin/xmaxКак PostgreSQL хранит несколько версий одной строки, что такое скрытые столбцы xmin и xmax, как снимок транзакции решает видимость версий и почему читатели не блокируют писателей.
    2. 34Мёртвые кортежи и раздувание таблицОткуда в PostgreSQL берётся bloat: мёртвые версии строк после UPDATE и DELETE, как заметить раздувание таблиц и индексов через pg_stat_user_tables и расширения, чем оно вредит производительности.
    3. 35VACUUM и autovacuumЧто делает VACUUM в PostgreSQL, чем обычный VACUUM отличается от VACUUM FULL, как устроен и настраивается autovacuum, зачем нужен ANALYZE для статистики и когда вмешиваться вручную.
    4. 36Уровни изоляции и блокировки в PostgreSQLИзоляция в PostgreSQL: Read Committed по умолчанию, Repeatable Read через снимок, Serializable и SSI, явные блокировки строк и таблиц, advisory locks и обнаружение deadlock.
  10. 10Масштабирование, репликация и эксплуатация4 урока

    1. 37Партиционирование таблицДекларативное партиционирование в PostgreSQL по диапазону, списку и хешу: зачем дробить большие таблицы, как работает partition pruning и частые грабли.
    2. 38Репликация: потоковая и логическаяРепликация PostgreSQL: primary и standby, потоковая репликация через WAL, hot standby для чтения, логическая репликация, синхронный и асинхронный режимы.
    3. 39Пулы соединений и масштабирование чтенияПочему соединения в PostgreSQL дорогие, как PgBouncer экономит ресурсы, режимы пулинга session/transaction/statement и масштабирование чтения репликами.
    4. 40Бэкапы, расширения и мониторингБэкапы pg_dump/pg_restore, физический бэкап и восстановление на точку во времени (PITR) через WAL, расширения CREATE EXTENSION и мониторинг через pg_stat_*.