раздел 33

PostgreSQL с нуля

PostgreSQL для тех, кто ставит его впервые. Установка, psql и GUI-клиенты, типы данных и автонумерация, таблицы с ограничениями и внешними ключами, запросы от SELECT до оконных функций, изменение данных, транзакции и индексы. Дальше то, за что Postgres выбирают: JSONB, массивы, полнотекстовый поиск, PL/pgSQL. Финал про эксплуатацию: MVCC и VACUUM изнутри, партиционирование, репликация, пулы соединений и бэкапы.

40 уроков40 тем · 10 кластеров

Начать раздел

Хочу пройти

Без входа у каждого урока видно начало. Войди, чтобы читать целиком, отмечать пройденное и решать задачи. войти

«PostgreSQL с нуля» покупается один раз и остаётся навсегда: 500 ₽. Оплата пока не подключена, доступ открывается промокодом. ввести промокод

Уроки раздела

Начать раздел

Понятия курса

Понятия разобраны внутри уроков. Нажми на понятие, чтобы открыть урок, где оно объясняется.

Введение в PostgreSQL

Создание базы, таблицы и GUI-клиентыИерархия кластер, база, схема, таблица; CREATE DATABASE, схема public, первая таблица и GUI-клиенты DBeaver и pgAdmin.урок 4 · Создание базы, таблицы и GUI-клиентыопираются: урок 5
Установка и подключение к базеPostgres в Docker и из пакетов, кластер, порт 5432, роли, строка подключения и pg_hba.conf.урок 2 · Установка и подключение к базеопираются: урок 3
Что такое PostgreSQL и чем он выделяетсяКлиент-серверная СУБД с открытым кодом, история проекта, расширяемость, строгость к стандарту и место среди других СУБД.урок 1 · Что такое PostgreSQL и чем он выделяетсяопираются: урок 2
psql и мета-командыКонсольный клиент psql: мета-команды для баз, таблиц и режима вывода, файл .psqlrc и системный каталог pg_catalog под ними.урок 3 · psql и мета-командыопираются: урок 4

Типы данных PostgreSQL

Автонумерация: serial и identityПоследовательности, serial как сокращение, GENERATED ALWAYS AS IDENTITY, дыры в нумерации и возврат id через RETURNING.урок 6 · Автонумерация: serial и identity
Булев, спецтипы и приведение типовboolean и NULL, uuid, inet, перечисления ENUM, приведение типов через :: и CAST, неявные приведения и ошибки преобразования.урок 8 · Булев, спецтипы и приведение типовопираются: урок 9
Текст и даты: varchar, text, timestamp, intervaltext против varchar(n) и char(n), date, timestamp и timestamptz с часовыми поясами, interval и арифметика дат.урок 7 · Текст и даты: varchar, text, timestamp, intervalопираются: урок 8
Числовые типы: integer, bigint, numericЦелые smallint, integer и bigint, переполнение, точный numeric для денег и неточные real и double precision.урок 5 · Числовые типы: integer, bigint, numericопираются: урок 6, урок 7

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

Внешние ключи и связи таблицREFERENCES, связи «один ко многим» и «многие ко многим», ON DELETE CASCADE, SET NULL и RESTRICT.урок 11 · Внешние ключи и связи таблицопираются: урок 12
Ограничения целостностиPRIMARY KEY, UNIQUE, CHECK и NOT NULL, имена ограничений, составные ключи и текст ошибок нарушения.урок 10 · Ограничения целостностиопираются: урок 11
ALTER TABLE и нормализацияДобавление, переименование и удаление колонок, смена типа через USING, вынос повторов в отдельную таблицу.урок 12 · ALTER TABLE и нормализацияопираются: урок 13
CREATE TABLE подробноИмена и типы колонок, DEFAULT, NOT NULL, IF NOT EXISTS, временные таблицы, CREATE TABLE AS и DROP TABLE.урок 9 · CREATE TABLE подробноопираются: урок 10

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

Агрегаты, GROUP BY и HAVINGcount, sum, avg, min и max, группировка, фильтр групп через HAVING, FILTER и агрегаты с NULL.урок 15 · Агрегаты, GROUP BY и HAVINGопираются: урок 16
Подзапросы, CTE и оконные функцииПодзапросы в WHERE и FROM, EXISTS, WITH для пошаговых запросов, row_number, rank и накопительные суммы через OVER.урок 16 · Подзапросы, CTE и оконные функцииопираются: урок 17
Соединения таблиц: JOININNER, LEFT, RIGHT и FULL JOIN на базе магазина, псевдонимы таблиц, соединение трёх таблиц, поиск строк без пары.урок 14 · Соединения таблиц: JOINопираются: урок 15
SELECT, WHERE, ORDER BY, LIMITУчебная база магазина, выбор колонок и выражений, фильтры с AND, OR, IN, BETWEEN, LIKE и IS NULL, сортировка и постраничный вывод.урок 13 · SELECT, WHERE, ORDER BY, LIMITопираются: урок 14

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

Индексы, EXPLAIN и VACUUMЗачем индекс, CREATE INDEX, первый взгляд на EXPLAIN, Seq Scan против Index Scan, цена индекса на запись, мёртвые версии строк и VACUUM.урок 20 · Индексы, EXPLAIN и VACUUMопираются: урок 21
Транзакции и ACIDBEGIN, COMMIT и ROLLBACK в PostgreSQL, автофиксация, прерванная транзакция после ошибки, SAVEPOINT, транзакционный DDL и уровень изоляции по умолчанию.урок 19 · Транзакции и ACIDопираются: урок 20
INSERT, UPDATE, DELETE и RETURNINGВставка одной и многих строк, INSERT ... SELECT, UPDATE и DELETE с WHERE и без, UPDATE ... FROM, RETURNING вместо второго запроса, TRUNCATE.урок 17 · INSERT, UPDATE, DELETE и RETURNINGопираются: урок 18
UPSERT через ON CONFLICTON CONFLICT DO NOTHING и DO UPDATE, EXCLUDED, цель конфликта по ключу или ограничению, гонка «проверь, потом вставь» и MERGE.урок 18 · UPSERT через ON CONFLICTопираются: урок 19

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

Представления (VIEW)CREATE VIEW как сохранённый запрос, обновляемые представления, CREATE OR REPLACE, материализованные представления и REFRESH.урок 22 · Представления (VIEW)опираются: урок 23
Работа с JSONBjson и jsonb, операторы -> и ->>, вложенные пути, @> и ?, сборка JSON из строк, когда класть данные в JSONB, а когда в колонки.урок 21 · Работа с JSONBопираются: урок 22
Роли, права и резервные копииCREATE ROLE, LOGIN и группы, GRANT и REVOKE, владелец объектов, роль приложения с минимумом прав, pg_dump и pg_restore.урок 24 · Роли, права и резервные копииопираются: урок 25
Функции и PL/pgSQLФункции на SQL и PL/pgSQL, параметры и возвращаемые типы, переменные, IF и циклы, RAISE, триггер для updated_at, процедуры.урок 23 · Функции и PL/pgSQLопираются: урок 24

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

Почему индекс не используетсяНизкая селективность, функция над колонкой, несовпадение типов, LIKE с процентом в начале, OR, устаревшая статистика и ANALYZE, маленькая таблица.урок 28 · Почему индекс не используетсяопираются: урок 29
Стратегия индексирования: составные, частичные, по выражениюПорядок колонок в составном индексе, частичный индекс с WHERE, индекс по выражению, покрывающий индекс с INCLUDE и Index Only Scan, цена лишних индексов.урок 27 · Стратегия индексирования: составные, частичные, по выражениюопираются: урок 28
Типы индексов: B-tree, Hash, GIN, GiST, BRINКакой индекс для какого запроса: B-tree для сравнений и сортировки, Hash для равенства, GIN для массивов, JSONB и текста, GiST для диапазонов и геометрии, BRIN для огромных упорядоченных таблиц.урок 25 · Типы индексов: B-tree, Hash, GIN, GiST, BRINопираются: урок 26
EXPLAIN и EXPLAIN ANALYZE: читаем планДерево плана снизу вверх, стоимость и оценка строк, ANALYZE с реальным временем, BUFFERS, расхождение оценки и факта, способы соединения Nested Loop, Hash Join и Merge Join.урок 26 · EXPLAIN и EXPLAIN ANALYZE: читаем планопираются: урок 27

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

Запросы и индексы по JSONBОператоры @>, ?, ?| и ?&, jsonpath и jsonb_path_query, GIN с jsonb_ops и jsonb_path_ops, B-tree по выражению для одного ключа, что ускоряется, а что нет.урок 30 · Запросы и индексы по JSONBопираются: урок 31
Массивы в PostgreSQLТип массива, литералы и ARRAY[], индексы с единицы, ANY, @>, && и unnest, array_agg, GIN по массиву, когда массив заменяет связующую таблицу, а когда нет.урок 31 · Массивы в PostgreSQLопираются: урок 32
Полнотекстовый поиск: tsvector и tsqueryПочему LIKE не поиск, лексемы и словари, to_tsvector и to_tsquery на русском, оператор @@, ранжирование ts_rank, подсветка ts_headline, GIN-индекс и генерируемая колонка.урок 32 · Полнотекстовый поиск: tsvector и tsqueryопираются: урок 33
JSONB: хранение и доступДвоичный формат jsonb против текстового json, нормализация ключей, извлечение по путям #> и #>>, изменение через jsonb_set, || и минус, jsonb_each и jsonb_array_elements, когда JSONB, а когда колонки.урок 29 · JSONB: хранение и доступопираются: урок 30

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

Мёртвые кортежи и раздувание таблицОткуда берутся мёртвые версии, рост таблицы и индексов при UPDATE и DELETE, долгие транзакции держат горизонт, HOT-обновления и fillfactor, как заметить раздувание.урок 34 · Мёртвые кортежи и раздувание таблицопираются: урок 35
Уровни изоляции и блокировки в PostgreSQLRead committed, repeatable read и serializable в PostgreSQL, ошибка сериализации и повтор, блокировки строк SELECT FOR UPDATE, взаимоблокировки, pg_locks и lock_timeout.урок 36 · Уровни изоляции и блокировки в PostgreSQLопираются: урок 37
MVCC изнутри: версии строк, xmin и xmaxСтрока как цепочка версий, служебные колонки xmin, xmax и ctid, снимок транзакции и правило видимости, почему UPDATE это вставка новой версии.урок 33 · MVCC изнутри: версии строк, xmin и xmaxопираются: урок 34
VACUUM и autovacuumЧто делает VACUUM, чем отличается VACUUM FULL, карта видимости, autovacuum и его пороги, ANALYZE, заморозка и переполнение счётчика транзакций.урок 35 · VACUUM и autovacuumопираются: урок 36

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

Бэкапы, расширения и мониторингЛогический и физический бэкап, pg_basebackup и архив WAL, восстановление на момент времени, проверка восстановления, расширения и pg_stat_statements, что мониторить, обновление версии.урок 40 · Бэкапы, расширения и мониторинг
Партиционирование таблицДекларативное партиционирование по диапазону, списку и хешу, отсечение секций в плане, секция по умолчанию, отключение и удаление старых секций, ограничения ключей.урок 37 · Партиционирование таблицопираются: урок 38
Пулы соединений и масштабирование чтенияПроцесс на соединение и max_connections, пул в приложении и PgBouncer, режимы session и transaction, чтение с реплик и задержка, когда масштабировать вертикально.урок 39 · Пулы соединений и масштабирование чтенияопираются: урок 40
Репликация: потоковая и логическаяWAL как основа репликации, ведущий и реплики, синхронная и асинхронная, отставание реплики, логическая репликация через публикации и подписки, переключение при отказе.урок 38 · Репликация: потоковая и логическаяопираются: урок 39