devops-курс Все курсы

✻ Урок 4.4 · Тема 4: Docker и Compose

SQL и PostgreSQL: запросы, индексы, EXPLAIN

⏱ 4 ч

Как работать с кодом и когда идти дальше

Команды не нужно писать по памяти. Пользуйся примерами из урока и справкой. Ориентируйся на цель задания:

  • Запусти готовое. Код приложения и вспомогательных скриптов можно скопировать. Пойми, зачем они нужны, что запускают или меняют и как проверить результат. Разбирать каждую функцию внутри готового инструмента не требуется.
  • Измени по образцу. Разбери новые конструкции, объяснённые в уроке. Измени указанный параметр или часть кода, предскажи результат и проверь запуском.
  • Напиши сам. Собери свой вариант под условия задания. Образцы и справка разрешены: важно объяснить своё решение и проверить его работу.

Можно идти дальше, если ты выполнил практику, получил ожидаемый результат и понимаешь, что проверил. Если задание просит изменить код, проверь и своё изменение.

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

Зачем это нужно

Файл с заметками хорош, пока заметок сто и писатель один. Когда сервис живёт в нескольких копиях, десятки людей пишут одновременно, а данные нужно искать по условиям, файла не хватает. Для этого придумали базу данных (database): специальную программу, которая хранит данные, ищет в них, не даёт двум писателям испортить друг другу записи и не теряет сохранённое при сбое питания.

На работе ты будешь постоянно слышать «база тормозит». Половина таких случаев лечится одним индексом. Индекс (index) - это «оглавление» колонки: отдельная заготовка, по которой база находит нужные строки сразу, а не листает всю таблицу подряд, как ты ищешь слово в книге по алфавитному указателю, а не читая все страницы. Без индекса на миллионе строк поиск медленный, с индексом мгновенный. Увидеть, используется ли он, помогает план запроса (query plan): база по просьбе показывает, как именно собирается искать (команда EXPLAIN, разберём ниже).

Ещё три беды из жизни базы: забытое условие WHERE в команде удаления (удалится всё), потерянный пароль и приложение, которое стартовало раньше базы. Что такое WHERE, объясню в теории. Все эти истории ты пройдёшь руками на настоящем PostgreSQL: это популярная бесплатная база данных с открытым кодом.

Самую частую из них, «база тормозит», можно увидеть на простой картинке. В таблице миллион заметок, нужна одна:

Сверху база читает строки подряд, снизу идёт по индексу, то есть по «оглавлению», за несколько ступенек. Время на картинке условное, настоящие замеры с EXPLAIN ты сделаешь ниже в уроке. Потяни ползунок «Сколько строк подходит» вправо и посмотри, когда индекс перестаёт выигрывать.

В этом уроке ты поднимаешь PostgreSQL 18 в контейнере, учишься читать и писать SQL (Structured Query Language, язык запросов: на нём ты «разговариваешь» с базой короткими фразами вроде «покажи все заметки автора Аня»), ломаешь скорость запроса и лечишь её индексом, а потом переводишь «Заметки» на хранение в базе.

Шаг проекта: «Заметки» переезжают с файла на PostgreSQL (STORE=postgres), /readyz проверяет базу, появляется демонстрационный /slowsql. Код приложения становится версией v4.

Что нужно знать

  • Урок 4.2: Dockerfile: образ «Заметок» собирается из Dockerfile, файл requirements.txt (список чужих библиотек, которые нужны коду) в нём копируется первым слоем. Сегодня в него добавится первая настоящая зависимость (чужая библиотека, драйвер для общения с базой), и образ получит версию notes:0.4.0.
  • Урок 4.3: тома и сети Docker: том (volume) это папка Docker, которая переживает удаление контейнера. Сеть notes-net даёт контейнерам DNS-имена друг друга (имя превращается в адрес автоматически, урок 2.3). База будет жить в томе и в этой сети.
  • Урок 1.4: процессы и сигналы: docker stop просит процесс закончить работу аккуратно (образ postgres для этого использует сигнал SIGINT, это «быстрое выключение»: база дописывает журнал и закрывается). Если процесс не успел за 10 секунд (срок задаёт stop_grace_period, в docker run это --stop-timeout), Docker шлёт SIGKILL («убить немедленно»); его же шлёт docker rm -f. Для базы это большая разница, ты её увидишь.
  • Урок 2.2: порты и TCP: порт это номер «двери» на машине, а connection refused значит, что за дверью никто не слушает.
  • Урок 2.4: HTTP: коды ответа. В этом уроке встретятся 200 (успех), 500 (ошибка на стороне сервера) и 503 (сервис временно не может отвечать).

Картина целиком

Представь библиотеку. Читатель (клиент) не ходит по стеллажам сам. Он подходит к библиотекарю (сервер) и говорит: «Выдайте все книги Чехова, вышедшие после 1890 года». Библиотекарь знает, где что лежит, находит нужное по карточному каталогу (индекс), записывает выдачу в журнал (в базе он зовётся WAL, журнал записи, подробно разберём ниже, в разделе про транзакции) и следит, чтобы двоим не выдали одну и ту же последнюю книгу. Читателю неважно, как устроены стеллажи: он формулирует, что хочет, а не как искать.

PostgreSQL это такой библиотекарь. Клиентов у него два: psql (консольная программа, с которой ты вводишь запросы руками) и приложение «Заметки».

flowchart TB
    subgraph HOST["Хост (твоя машина или ВМ)"]
        CURL["curl :8080"]
        subgraph NET["сеть Docker notes-net"]
            APP["контейнер notes<br>app.py v4 (psycopg)"]
            DB["контейнер db, alias db<br>PostgreSQL, порт 5432"]
        end
        VOL[("том notes-pgdata<br>файлы базы")]
    end
    CURL -->|"порт 8080 опубликован наружу"| APP
    APP -->|"SQL по TCP, db:5432"| DB
    DB --- VOL

Порт 8080 опубликован наружу, порт 5432 виден только внутри сети.

Дальше в теории каждая часть этой схемы разбирается по отдельности: что такое таблица (данные в строках и колонках, как лист в Excel) и SQL, как база защищает данные (ограничения: правила вроде «это поле не может быть пустым»; транзакции: группа действий, которая выполняется целиком или не выполняется вообще, как перевод денег: списали и зачислили либо ни то ни другое), почему запросы бывают быстрыми и медленными (индексы и EXPLAIN), как запускается PostgreSQL в контейнере и как приложение к нему подключается.

Теория

Зачем нужна база, если есть файл

Сейчас «Заметки» пишут по строке JSON (текстовый формат записи данных вида {"id": 1, "text": "купить молоко"}) в файл. Это работает, пока всё просто. Посмотри, где начинаются проблемы.

  • Два писателя одновременно. Два запроса пришли в одну миллисекунду, оба открыли файл и дописали строку. В худшем случае строки перемешаются. В app.py для этого стоит замок LOCK, но он работает только внутри одного процесса. Запусти две копии приложения, и замок бесполезен.
  • Поиск. Чтобы найти заметку по тексту, нужно прочитать весь файл. Заметок миллион: читаем миллион строк.
  • Сбой посреди записи. Питание пропало, когда программа записала половину строки. В файле мусор, и никто не знает, какая запись последняя целая.
  • Связи. Автор, его посты, комментарии к постам: в файле придётся самому следить, чтобы комментарий не ссылался на удалённый пост.

База данных берёт всё это на себя. Аналогия: файл это школьная тетрадь, в которую пишут все подряд. База это картотека с дежурным библиотекарем, который решает, кому и когда можно открыть ящик. Аналогия перестаёт работать в одном: библиотекарь это программа, поэтому ему нужны память, процессор и место на диске, и у него бывают свои поломки.

Прикинь сам: Заметок миллион, и нужно найти одну по тексту. Сколько строк прочитает приложение, которое хранит заметки в файле?

В худшем случае все миллион: файл читают подряд с первой строки. База с индексом найдёт нужную строку, прочитав одну.

Осторожно, частое заблуждение: «База данных» и «сервер базы данных» это разные вещи. Сервер (в нашем случае PostgreSQL) это работающая программа, а база это набор данных внутри неё. На одном сервере может жить много баз. В этом уроке их будет две: notes для приложения и lab для тренировки.

Главное: файл подходит, пока писатель один, а данных мало; база берёт на себя параллельную запись, поиск, сбои и связи.

Чтобы понять, как база всё это делает, посмотрим, как в ней лежат данные.

Проверь понимание: почему замок LOCK внутри app.py не спасает, если запустить две копии приложения, пишущие в один файл?

Ответ

Замок живёт в памяти одного процесса, вторая копия о нём ничего не знает. Каждая копия думает, что пишет в файл одна. База решает это на своей стороне: все копии приложения обращаются к одному серверу, и он сам расставляет записи по очереди.

Реляционная база: таблицы, строки, столбцы

Реляционная база (relational database) хранит данные в таблицах (tables). Ты видел таблицы в электронных таблицах: строки и столбцы. Здесь то же самое, но строже.

  • Столбец (column) это одно поле у всех записей и у него есть тип: число, текст, время. В столбец с типом «число» нельзя положить слово.
  • Строка (row) это одна запись: одна заметка, один автор.
  • Таблица это набор строк с одинаковым набором столбцов.

Так выглядит таблица notes с тремя заметками (настоящий вывод psql, ты получишь такой же в задании 2 и в теории ниже):

 id |       text        |          created_at           
----+-------------------+-------------------------------
  1 | купить молоко     | 2026-09-30 12:13:37.493464+00
  2 | позвонить маме    | 2026-09-30 12:13:37.493813+00
  3 | оплатить интернет | 2026-09-30 12:13:37.493813+00
(3 rows)

Столбцов три: id (номер), text (текст), created_at (когда создана). Строк три. Слово «реляционная» происходит от relation (отношение): так математики называют таблицу. Между таблицами тоже бывают отношения, например «у автора много постов», их мы разберём в разделе про JOIN.

Как приложение «разговаривает» с базой? PostgreSQL это сервер: программа, которая работает постоянно, слушает порт 5432 и ждёт клиентов. Клиент подключается по сети (TCP, как в уроке 2.2), называет пользователя и пароль, а потом отправляет тексты запросов на языке SQL. Сервер выполняет их и возвращает таблицу-ответ.

SQL (Structured Query Language, «язык структурированных запросов») отличается от привычных языков программирования: ты описываешь, что хочешь получить, а не как это искать. Фраза «дай заметки с номером больше 10» не говорит, читать ли всю таблицу или заглянуть в индекс: это база решает сама.

Прикинь сам: В таблице notes три строки и три столбца. Сколько значений она хранит всего?

Девять: 3 × 3. Каждая из трёх строк это одна заметка с тремя полями.

Осторожно: SQL это язык, а не программа. PostgreSQL, MySQL, SQLite это разные программы, которые понимают почти один и тот же SQL. Ещё путают «таблицу в базе» и «файл»: файлы у базы свои, ты их не открываешь, а работаешь только через SQL.

Главное: реляционная база хранит данные в таблицах: у столбца свой тип, строка это одна запись, а сервер PostgreSQL это работающая программа, а не файл.

Данными управляют командами SQL. Их всего четыре.

Проверь понимание: чем строка отличается от столбца и сколько тех и других в таблице выше?

Ответ

Строка это одна запись целиком (заметка), столбец это одно поле у всех записей (например, text). В таблице три строки и три столбца.

SQL: четыре действия с данными

Почти всё, что делает приложение с данными, сводится к четырём действиям, их называют CRUD (Create, Read, Update, Delete): создать, прочитать, изменить, удалить. В SQL им соответствуют четыре команды.

Действие Команда Что делает
создать INSERT добавляет строку
прочитать SELECT выбирает строки и столбцы
изменить UPDATE меняет значения в существующих строках
удалить DELETE удаляет строки

Каждая команда заканчивается точкой с запятой: по ней клиент понимает, что запрос закончен. Ключевые слова (SELECT, WHERE) принято писать заглавными, но регистр не важен. Текстовые значения берут в одинарные кавычки: 'купить молоко'. Двойные кавычки означают имя таблицы или столбца, это другое.

Разберём на настоящем примере. Таблица notes создана командой из следующего раздела, сейчас нам важны только команды:

INSERT INTO notes (text) VALUES ('купить молоко');

Читается так: «вставь в таблицу notes в столбец text значение 'купить молоко'». Остальные столбцы (id, created_at) не названы: база заполнит их сама, как объясняется в следующем разделе. Ответ сервера INSERT 0 1 значит «вставлена 1 строка» (нуль это устаревший служебный номер, его можно игнорировать).

SELECT id, text FROM notes WHERE id > 1 ORDER BY id LIMIT 5;

По частям: SELECT id, text (какие столбцы показать), FROM notes (из какой таблицы), WHERE id > 1 (условие: только строки, где номер больше 1), ORDER BY id (сортировка по номеру), LIMIT 5 (не больше пяти строк). Звёздочка SELECT * означает «все столбцы». Настоящий ответ:

 id |       text        
----+-------------------
  2 | позвонить маме
  3 | оплатить интернет
(2 rows)
UPDATE notes SET text = 'купить кефир' WHERE id = 1;
DELETE FROM notes WHERE id = 3;

Сервер ответил UPDATE 1 и DELETE 1: изменена одна строка, удалена одна строка. Это число нужно смотреть всегда. Если ты хотел изменить одну строку, а в ответе UPDATE 4000, что-то пошло не так.

Главная ловушка: забытый WHERE. Без условия UPDATE и DELETE относятся ко всем строкам таблицы. Вот что случилось бы с моей таблицей из четырёх заметок (запущено внутри транзакции, чтобы можно было откатить, о транзакциях чуть ниже):

BEGIN
UPDATE 4
 id | text 
----+------
  1 | oops
  2 | oops
  5 | oops
  6 | oops
(4 rows)

ROLLBACK

UPDATE 4 в ответе это сигнал тревоги. Защита: перед опасной командой открой транзакцию (BEGIN;), проверь число строк, а потом либо COMMIT;, либо ROLLBACK;. Ещё один приём: сначала напиши SELECT с тем же WHERE и посмотри, какие строки попадут под изменение.

NULL. В базе есть особое значение NULL: «нет данных». Это не ноль и не пустая строка, а отсутствие значения. Сравнение с ним ведёт себя необычно: x = NULL не бывает истинным, для проверки пишут x IS NULL. Именно поэтому count(столбец) не считает NULL (пример в разделе про JOIN).

RETURNING. У INSERT в PostgreSQL есть полезное дополнение: INSERT INTO notes (text) VALUES ('ещё одна') RETURNING id; сразу возвращает id созданной строки. Так приложение узнаёт номер новой заметки за один запрос. Мы увидим это в коде pg_add.

Прикинь сам: Ты выполнил UPDATE notes SET text = 'x'; на таблице из 4000 строк. Сколько строк изменится и что ответит сервер?

Все 4000: условия WHERE нет. Сервер ответит UPDATE 4000, и это число надо смотреть всегда.

Осторожно: команду DELETE FROM notes; считают «удалением таблицы»: на самом деле она удаляет все строки, а сама таблица остаётся. Удаляет таблицу команда DROP TABLE, и она куда опаснее.

Между ними есть третья, TRUNCATE: она опустошает таблицу целиком, одной командой и без условия WHERE. Таблица остаётся, строк в ней нет. На больших таблицах TRUNCATE намного быстрее DELETE FROM, потому что не просматривает строки по одной, но и числа удалённых строк не сообщает: ответ просто TRUNCATE TABLE. Сравни три команды (на таблице posts из учебной базы lab, которую ты создашь в задании 2):

Команда Что остаётся Откат внутри BEGIN
DELETE FROM posts; пустая таблица, число строк в ответе да
TRUNCATE posts; пустая таблица, без числа строк да (в PostgreSQL)
DROP TABLE posts; ничего: нет ни строк, ни самой таблицы да (в PostgreSQL)

В PostgreSQL откатываются все три (во многих других базах TRUNCATE и DROP откатить нельзя, поэтому не привыкай к этому). Но без BEGIN каждая команда сама фиксируется сразу, и вернуть данные сможет только бэкап (backup, резервная копия: сохранённый снимок данных, из которого базу можно поднять заново; подробно в разделе «Резервная копия: том это не бэкап» ниже). Проверка на TRUNCATE:

BEGIN
TRUNCATE TABLE
 count 
-------
     0
(1 row)

ROLLBACK
 count 
-------
     3
(1 row)

Главное: четыре команды (INSERT, SELECT, UPDATE, DELETE) покрывают CRUD, а число строк в ответе проверяют, ведь без WHERE меняется вся таблица.

Данные должны быть правильными. Это обеспечивает схема.

Схема: типы и ограничения

Таблицу нужно создать до первой записи. Описание таблиц называют схемой (schema). Для «Заметок» схема зафиксирована в контракте курса:

CREATE TABLE IF NOT EXISTS notes (
    id         serial PRIMARY KEY,
    text       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Разбор построчно:

  • CREATE TABLE IF NOT EXISTS notes: создай таблицу notes, если её ещё нет. Слова IF NOT EXISTS делают команду безопасной для повторного запуска, приложение так делает при каждом старте.
  • id serial PRIMARY KEY: столбец id. Тип serial это целое число, которое база выдаёт сама: 1, 2, 3 и так далее (автоинкремент). Это старый способ: в новых схемах пишут id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, он делает то же самое, и «дыры» в номерах там такие же. Мы оставляем serial, потому что он зафиксирован в контракте курса. PRIMARY KEY (первичный ключ) значит «уникально и не пусто»: по нему строку можно назвать однозначно, и под него база автоматически строит индекс.
  • text text NOT NULL: столбец text типа text (произвольный текст). NOT NULL запрещает пустое значение.
  • created_at timestamptz NOT NULL DEFAULT now(): момент создания. timestamptz (timestamp with time zone) хранит момент времени в UTC, а при выводе переводит его в пояс текущей сессии; сам пояс он не запоминает. Для серверных данных используй его, а не timestamp без пояса: иначе через год никто не вспомнит, в каком поясе записаны времена. DEFAULT now() подставляет текущее время, если при вставке значение не указано.

Слова NOT NULL, PRIMARY KEY, DEFAULT, UNIQUE, REFERENCES называют ограничениями (constraints): правилами, которые база проверяет при каждой записи. Вот они за работой (настоящие ответы сервера):

ERROR:  null value in column "text" of relation "notes" violates not-null constraint
DETAIL:  Failing row contains (4, null, 2026-09-30 12:13:37.495103+00).

ERROR:  duplicate key value violates unique constraint "notes_pkey"
DETAIL:  Key (id)=(1) already exists.

Первая ошибка: я вставил NULL в text. Вторая: попытался вставить второй id = 1. В обоих случаях строка не записана, а база объяснила почему. Заметь на первой ошибке: Failing row contains (4, null, ...). Число 4 это номер, который serial уже выдал, а вставка не удалась. Он пропал: следующая вставка получила 5. Такие «дыры» в номерах нормальны, serial гарантирует только уникальность, а не непрерывность. Ниже (в разделе про контейнеры) увидишь и более заметный скачок.

Особое ограничение это REFERENCES (внешний ключ, foreign key): значение столбца обязано существовать в другой таблице. Пост не может ссылаться на автора, которого нет. В задании 2 ты увидишь это в деле.

Зачем всё это, если приложение и так не отправляет пустой текст? Ограничения защищают данные лучше кода приложения: приложений может быть несколько, скрипты и ручные правки тоже ходят в базу, а сама база одна. Ограничение в ней работает для всех клиентов сразу.

Прикинь сам: В таблице notes уже есть строка с id = 1. Что произойдёт, если вставить ещё одну строку с id = 1, и сколько строк станет в таблице?

База откажет с ошибкой duplicate key value violates unique constraint. Строк останется столько же, ведь запись не выполнена.

Осторожно: PRIMARY KEY считают «просто номером строки». На самом деле это правило (уникально и не пусто) плюс автоматический индекс. serial не «заполняет пробелы» после удалённых строк: удалил строку 3, следующая будет 4 или больше, а не 3.

Главное: ограничения (NOT NULL, PRIMARY KEY, REFERENCES) база проверяет при каждой записи от любого клиента, а не только от твоего приложения.

Теперь связи между таблицами: как склеить данные из нескольких.

Проверь понимание: зачем NOT NULL в базе, если приложение и так проверяет, что текст не пустой?

Ответ

В базу пишет не только твоё приложение: миграции (скрипты изменения схемы), другие сервисы, ручные правки через psql. Ограничение в БД работает для всех клиентов и не зависит от багов конкретного кода.

JOIN и агрегаты: несколько таблиц вместе

Данные обычно лежат в нескольких связанных таблицах. Пример для тренировки: таблица авторов и таблица постов. В posts есть столбец author_id, который ссылается на id из authors. Хранить имя автора в каждом посте не нужно: достаточно номера, а имя подтянется при запросе.

 authors               posts
 id | name             id | author_id | title
----+-------           ----+-----------+------------
  1 | anna              1 |     1     | про nginx
  2 | boris             2 |     1     | про TLS
  3 | vera              3 |     2     | про Docker

JOIN («соединение») склеивает строки двух таблиц по условию. Как это происходит по шагам: для каждой строки левой таблицы база ищет в правой строки, у которых выполнено условие ON, и выдаёт склеенные пары. Есть два основных вида:

  • INNER JOIN (или просто JOIN) оставляет только пары, где совпадение нашлось. Автор без постов исчезнет.
  • LEFT JOIN оставляет все строки левой таблицы. Если справа совпадения нет, недостающие столбцы заполняются NULL.

Настоящий результат LEFT JOIN из моего прогона:

 name  |   title    
-------+------------
 anna  | про nginx
 anna  | про TLS
 boris | про Docker
 vera  | 
(4 rows)

Vera осталась в результате, а справа у неё пусто (NULL). Теперь агрегаты. Агрегатные функции (count, sum, max) сворачивают много строк в одно значение. GROUP BY делит строки на группы, и функция считается для каждой группы отдельно:

SELECT a.name, count(p.id) AS posts
FROM authors a
LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.name
ORDER BY posts DESC, a.name;

Здесь a и p это короткие псевдонимы таблиц (authors a), AS posts даёт столбцу-результату имя. Вычисление руками: группа anna состоит из двух склеенных строк, count(p.id) даёт 2; boris из одной, 1; у vera одна склеенная строка, но p.id в ней NULL, а count(столбец) NULL не считает, поэтому 0. Теперь замени count(p.id) на count(*), который считает строки, а не значения. Два отдельных настоящих результата, один под другим. Первый, count(p.id):

 name  | posts
-------+-------
 anna  |     2
 boris |     1
 vera  |     0     <- верно: у vera нет постов

Второй, count(*):

 name  | posts
-------+-------
 anna  |     2
 boris |     1
 vera  |     1     <- неверно: посчитана строка-пустышка

Строка vera отличается: count(*) посчитал единственную строку-пустышку, которую породил LEFT JOIN, и соврал про vera. А INNER JOIN вообще выбросил бы vera:

 name  | posts 
-------+-------
 anna  |     2
 boris |     1
(2 rows)

Прикинь сам: В authors три автора (anna, boris, vera), в posts три поста (2 у anna, 1 у boris). Сколько строк вернёт INNER JOIN, а сколько LEFT JOIN?

INNER JOIN вернёт 3 строки: vera без постов исчезнет. LEFT JOIN вернёт 4: vera останется с пустым title (NULL).

Теперь условие на группы. WHERE отбирает строки до группировки, а HAVING отбирает уже готовые группы после GROUP BY (по значению агрегата). Например, авторы, у которых больше одного поста:

SELECT a.name, count(*) AS posts
FROM authors a
JOIN posts p ON p.author_id = a.id
GROUP BY a.name
HAVING count(*) > 1;
 name | posts
------+-------
 anna |     2
(1 row)

Писать WHERE count(*) > 1 нельзя: на этапе WHERE группы ещё не посчитаны, и база ответит ошибкой aggregate functions are not allowed in WHERE. Запомни порядок: WHERE режет строки, GROUP BY собирает группы, HAVING режет группы.

Осторожно: забытое ON. Без него каждая строка левой таблицы склеивается с каждой строкой правой, и на больших таблицах запрос не заканчивается. Ещё путают count(*) и count(столбец): первый считает строки, второй только непустые значения.

Главное: JOIN склеивает таблицы по условию: INNER оставляет только совпадения, LEFT сохраняет все строки левой таблицы.

Когда команд несколько и они связаны, нужны транзакции.

Проверь понимание: ты хочешь список авторов, у которых нет ни одного поста. Какой JOIN нужен и что отфильтруешь?

Ответ

LEFT JOIN, потому что INNER JOIN потерял бы именно этих авторов. После соединения оставляешь строки, где столбец из правой таблицы IS NULL: WHERE p.id IS NULL. Останется vera.

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

Перевод денег между счетами это два действия: списать у одного, зачислить другому. Если сервер упадёт между ними, деньги исчезнут. Нужен способ сказать базе: «эти два действия либо оба, либо ни одного». Это транзакция (transaction): группа команд «всё или ничего».

  • BEGIN; открывает транзакцию.
  • Дальше идут команды, и видишь их результат только ты.
  • COMMIT; фиксирует всё сразу, теперь это видят остальные.
  • ROLLBACK; отменяет всё, будто ничего не было.

Аналогия: ты собираешь заказ в корзине интернет-магазина. Пока не нажал «Оплатить», склад ничего не списал и другие покупатели твоей корзины не видят. Нажал «Оплатить» (COMMIT), и заказ стал настоящим. Закрыл вкладку (ROLLBACK или обрыв связи), и корзина исчезла. Аналогия перестаёт работать в том, что корзина не блокирует товар для других, а транзакция с изменением строки блокирует её (об этом ниже).

Настоящий эксперимент с двумя клиентами. Клиент A открыл транзакцию и изменил заметку 1, но не сделал COMMIT. Пока идёт транзакция, клиент B читает ту же заметку:

-- клиент B, пока транзакция A открыта:
 id |     text     
----+--------------
  1 | купить кефир
(1 row)

B видит старый текст: чужие несохранённые изменения невидимы (это и есть изоляция). Если B попытается изменить ту же строку, он будет ждать, пока A закончит. Я поставил B лимит ожидания в 1 секунду:

ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,4) in relation "notes"

Дальше то же самое по шагам в двух терминалах. Буква в начале строки говорит, чья это команда (A или B), порядок сверху вниз:

A: BEGIN;
A: UPDATE notes SET text = 'из транзакции' WHERE id = 1;
B: SELECT id, text FROM notes WHERE id = 1;     -- до COMMIT
    id |     text
   ----+--------------
     1 | купить кефир
A: COMMIT;
B: SELECT id, text FROM notes WHERE id = 1;     -- после COMMIT
    id |     text
   ----+---------------
     1 | из транзакции

Пока A не сказал COMMIT, B видит старый текст купить кефир. После COMMIT тот же запрос B возвращает новый текст из транзакции. Ещё один эксперимент: клиент открыл BEGIN, выполнил UPDATE notes SET text = 'не дойдёт до COMMIT'; (ответ UPDATE 4) и отключился, не дойдя до COMMIT. Следующий запрос показал таблицу без этих изменений: сервер сам откатил оборванную транзакцию.

ACID это четыре свойства, которые делают транзакции надёжными:

  • Atomicity (атомарность): всё или ничего, как в примере с оборванным клиентом.
  • Consistency (согласованность): после транзакции все ограничения (NOT NULL, REFERENCES) выполнены, иначе транзакция отклонена.
  • Isolation (изоляция): параллельные транзакции не видят чужих несохранённых изменений.
  • Durability (долговечность): после COMMIT данные не пропадут, даже если в ту же секунду отключат питание.

Как база обеспечивает долговечность? Перед изменением файлов данных она записывает описание изменения в отдельный журнал WAL (write-ahead log, «журнал упреждающей записи»). После сбоя сервер при старте перечитывает журнал и доделывает или отменяет то, что не закончилось. Журнал пишется последовательно и быстро, а таблицы обновляются позже.

Каждая одиночная команда без BEGIN это своя маленькая транзакция с автоматическим COMMIT. Поэтому забытый WHERE без BEGIN необратим (см. выше). А забытая открытая транзакция опасна по другой причине: она держит изменённые строки заблокированными, и все, кто хочет их менять, ждут.

Как база показывает B старую версию строки, пока A не закончил? Она не затирает данные на месте, а хранит рядом и старую, и новую версию. Это называется MVCC (multiversion concurrency control, «многоверсионность»): читатель смотрит на ту версию, которая ему положена, поэтому не ждёт писателя.

Для любопытных: уборка версий, уровни изоляции, забытая транзакция

Старые версии со временем никому не нужны, и их убирает автовакуум (autovacuum): фоновая уборка. Но пока открыта старая транзакция, версии, которые она могла бы прочитать, выбросить нельзя, и таблица пухнет.

Какие версии видит транзакция, зависит от уровня изоляции (isolation level). По умолчанию в PostgreSQL это READ COMMITTED: каждая команда видит только то, что другие уже зафиксировали к её началу. Поэтому B выше увидел новый текст сразу после COMMIT. Более строгие уровни (REPEATABLE READ, SERIALIZABLE) нужны редко, мы их не трогаем.

Найти забытую транзакцию можно в системном представлении pg_stat_activity (кто подключён и что делает), а снять её командой pg_terminate_backend (завершить соединение, откатив его транзакцию):

SELECT pid, state, now() - xact_start AS age
FROM pg_stat_activity
WHERE state = 'idle in transaction';

SELECT pg_terminate_backend(12345);   -- вместо 12345 подставь pid из первого запроса

Состояние idle in transaction значит «транзакция открыта, а клиент ничего не делает». Чем больше age, тем подозрительнее.

stateDiagram-v2
    [*] --> Открыта: BEGIN
    Открыта --> Открыта: UPDATE, INSERT (видишь только ты)
    Открыта --> Зафиксирована: COMMIT (теперь видят все)
    Открыта --> Отменена: ROLLBACK или обрыв соединения
    Зафиксирована --> [*]
    Отменена --> [*]

Прикинь сам: Клиент выполнил BEGIN и три UPDATE, после чего упал до COMMIT. Сколько из трёх изменений сохранится?

Ни одного: сервер откатит транзакцию при разрыве соединения. Это атомарность: всё или ничего.

Осторожно: думают, что ROLLBACK можно сделать после COMMIT. Нельзя: COMMIT это точка невозврата. Ещё думают, что ошибка внутри BEGIN сама закроет транзакцию: она остаётся открытой в состоянии «прервана», и нужно выполнить ROLLBACK.

Главное: транзакция выполняется целиком или не выполняется совсем (ACID), а COMMIT это точка невозврата.

База работает быстро, пока данных немного. На большом объёме помогают индексы.

Проверь понимание: клиент A выполнил BEGIN и UPDATE строки, а потом ROLLBACK. Что после этого увидит клиент B и чем ROLLBACK отличается от COMMIT?

Ответ

B увидит старый текст строки, будто UPDATE не было: ROLLBACK отменяет всё, что сделано с BEGIN. COMMIT наоборот делает изменения настоящими для всех. Обрыв связи до COMMIT для базы то же, что ROLLBACK.

Индексы и EXPLAIN

Запрос «найди заметку с текстом заметка 777777» среди миллиона строк. Без подсказки базе остаётся читать таблицу подряд от первой строки до последней и проверять условие в каждой. Это последовательное сканирование (Seq Scan). Оно честное, но время растёт вместе с таблицей.

Индекс (index) это отдельная структура, которая помогает искать быстрее. Аналогия: предметный указатель в конце учебника. Чтобы найти слово «транзакция», ты не листаешь все страницы, а смотришь в алфавитный указатель, находишь номер страницы и открываешь её. Аналогия перестаёт работать в том, что указатель в книге не меняется, а индекс нужно обновлять при каждой записи.

Как устроен индекс по умолчанию, B-tree (сбалансированное дерево): значения хранятся отсортированными, а поверх них лежат уровни «указателей», как оглавление оглавления. Чтобы найти значение, база спускается с верхнего уровня вниз, на каждом шаге отбрасывая большую часть данных. Для миллиона строк хватает трёх-четырёх шагов. Время растёт не пропорционально размеру таблицы, а очень медленно (логарифмически): в тысячу раз больше данных, всего на пару шагов больше.

У индекса есть цена, и её видно в настоящих числах. Размеры таблицы и индексов после моего эксперимента (миллион строк):

 Name                | Type  | Size  
 big_notes           | table | 65 MB    <- сами данные
 big_notes_pkey      | index | 21 MB    <- индекс под PRIMARY KEY (по id)
 big_notes_text_idx  | index | 39 MB    <- индекс, который создали мы (по text)

Индекс по text весит больше половины таблицы. Есть и вторая цена: каждую вставку и обновление база делает дважды, в таблицу и во все индексы. Я вставил по 500 000 строк в две одинаковые таблицы, одна с дополнительным индексом по тексту, другая без:

без индекса по text:  Time: 605.021 ms
с индексом по text:   Time: 2308.908 ms

Запись с индексом оказалась почти в четыре раза медленнее. Поэтому индексы создают под реальные запросы, а не «на всякий случай»: каждый лишний это место на диске и тормоза при записи.

EXPLAIN показывает план запроса (query plan): как база собирается его выполнять, не выполняя. EXPLAIN ANALYZE запрос ещё и выполняет и показывает реальное время. Разбор плана для запроса SELECT * FROM big_notes WHERE text = 'заметка 777777' без индекса. Сначала упрощённый план, только три строки, которые важны (настоящий вывод PostgreSQL 18 без лишнего):

 Parallel Seq Scan on big_notes
   Rows Removed by Filter: 333333
 Execution Time: 15.148 ms

Главное число здесь 333333: база прочитала и выбросила 333 333 строки ради одной нужной. Большое число в Rows Removed by Filter это главный признак «нужен индекс». Parallel Seq Scan значит последовательное сканирование таблицы, причём сразу несколькими процессами. Execution Time показывает, сколько запрос выполнялся на самом деле. Читать план надо снизу вверх и изнутри наружу: самая глубокая строка выполняется первой.

Для любопытных: полный вывод плана
 Gather  (cost=1000.00..14532.43 rows=1 width=33) (actual time=12.857..15.123 rows=1.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on big_notes  (cost=0.00..13532.33 rows=1 width=33) (actual time=10.845..11.753 rows=0.33 loops=3)
         Filter: (text = 'заметка 777777'::text)
         Rows Removed by Filter: 333333
 Planning Time: 0.133 ms
 Execution Time: 15.148 ms
  • Workers Launched: 2 значит два помощника плюс основной процесс: три исполнителя (loops=3), поэтому 333333 это число на каждого. Gather собирает результаты.
  • cost=1000.00..14532.43: оценка стоимости в условных единицах (сколько работы, по мнению планировщика: это часть PostgreSQL, которая перебирает способы выполнить запрос и выбирает самый дешёвый). Первое число это стоимость до первой строки, второе до последней. Единицы не секунды, ими сравнивают варианты плана между собой. width=33 средний размер строки в байтах.
  • rows=1: сколько строк планировщик ожидает получить. actual time=... и rows=1.00 это уже факт. Большая разница между ожиданием и фактом значит, что статистика устарела, и помогает команда ANALYZE таблица (она пересчитывает статистику, которой пользуется планировщик).

После создания индекса тот же запрос:

 Index Scan using big_notes_text_idx on big_notes  (cost=0.42..8.44 rows=1 width=33) (actual time=0.022..0.023 rows=1.00 loops=1)
   Index Cond: (text = 'заметка 777777'::text)
   Index Searches: 1
 Execution Time: 0.040 ms

Вместо чтения всей таблицы Index Scan: нашёл значение в индексе и прочитал единственную нужную строку. Выигрыш: 15.148 ms против 0.040 ms, почти в 380 раз. На моей машине таблица целиком помещается в память, поэтому seq scan непривычно быстрый: у тебя цифры будут другими, а на слабом сервере с диском разница ещё больше.

Осторожно с боевой таблицей: обычный CREATE INDEX на время построения не пускает в таблицу записи. На живой таблице пишут CREATE INDEX CONCURRENTLY: он строится дольше, зато записи не блокирует. У него два условия: его нельзя запускать внутри транзакции, а если построение оборвётся, останется нерабочий индекс с пометкой INVALID, его надо удалить и создать заново. В нашей лаборатории хватает обычного.

Когда индекс не помогает. Проверено на той же таблице:

  • WHERE text LIKE '%777' (поиск по концу строки) остаётся Parallel Seq Scan, Rows Removed by Filter: 333000. B-tree отсортирован по началу строки, а искать «всё, что заканчивается на 777», по нему нельзя.
  • Если условие отбирает половину таблицы (created_at > now() - interval '500000 seconds' вернул 499 984 строки из миллиона), планировщик сам выбирает Seq Scan. Идти по индексу и прыгать по таблице за каждой строкой дороже, чем прочитать всё подряд.
  • Даже LIKE 'заметка 7777%' (поиск по началу строки) при обычной языковой сортировке (в этом образе en_US.utf8) индекс не использовал. Причина: обычный B-tree упорядочен по правилам языка, а не по буквам подряд, и для поиска по началу строки не годится; нужен особый вид индекса (text_pattern_ops), его мы не разбираем. Вывод для практики: не гадай, а смотри EXPLAIN.

Ловушка EXPLAIN ANALYZE. Он выполняет запрос. Для SELECT это безопасно, а для UPDATE и DELETE он реально изменит данные. Оборачивай в BEGIN … ROLLBACK:

BEGIN
 (план опущен: EXPLAIN ANALYZE DELETE ... реально удалил 10 строк)
 count  
--------
 499990         <- внутри транзакции 10 строк уже удалены

ROLLBACK
 count  
--------
 500000         <- после отката все на месте

Прикинь сам: Запрос по тексту занимал 15 мс без индекса и 0,04 мс с индексом. Во сколько раз быстрее, и во сколько раз стала медленнее вставка (605 мс против 2309 мс)?

Поиск быстрее в 15 / 0,04 = 375 раз. Вставка медленнее примерно в 3,8 раза: каждую запись база делает и в таблицу, и во все индексы.

Осторожно, частое заблуждение: «Индекс ускоряет всё». Нет: он ускоряет поиск по своему столбцу для избирательных условий (отбирающих малую долю строк) и замедляет запись. Слово «избирательность» (selectivity) как раз про это: чем меньше доля отбираемых строк, тем полезнее индекс.

Главное: индекс ускоряет поиск избирательных условий, но замедляет запись и занимает место, а проверяют это через EXPLAIN ANALYZE.

Теперь выясним, как с базой разговаривают приложение и человек.

Проверь понимание: запрос WHERE created_at > ... тормозит на таблице в миллион строк. Что проверишь первым?

Ответ

EXPLAIN ANALYZE этого запроса. Если там Seq Scan, большой Rows Removed by Filter и большой Execution Time, а условие отбирает малую долю строк, нужен индекс по created_at. Если условие отбирает половину таблицы, индекс не поможет, планировщик сам выберет Seq Scan.

Клиент и сервер: как вообще разговаривают с базой

База это отдельная работающая программа, а не файл, который ты открываешь. Приложению, psql и коллеге нужен общий способ к ней обратиться. Без него каждая программа лезла бы в файлы базы сама, и мы вернулись бы к проблемам «двух писателей» из начала урока.

Банк и клиенты. Внутрь хранилища никто не заходит: все подходят к окошку, называют себя, просят операцию и получают ответ. Где аналогия перестаёт работать: к «окошку» базы могут подойти тысячи клиентов одновременно, и сервер ведёт их всех параллельно.

PostgreSQL слушает сетевой порт (по умолчанию 5432, как веб-сервер слушает 80 или 8080, урок 2.2). Клиент открывает соединение (connection) по TCP, представляется (имя пользователя, пароль, имя базы, к которой хочет попасть) и отправляет SQL-команды. Сервер выполняет каждую и возвращает результат: строки или слова вроде INSERT 0 1 («вставлена одна строка»). Когда клиент закончил, соединение закрывается. Открывать соединение не бесплатно (проверка пароля, выделение памяти на сервере), поэтому приложения держат несколько открытых заранее и переиспользуют их; это называют пулом соединений, ниже в разделе про приложение.

flowchart LR
    P["psql (ты руками)"] -->|"TCP :5432"| S["сервер PostgreSQL<br>кто ты, какая база?<br>пароль проверен, SQL, ответ"]
    A["«Заметки» (psycopg)"] -->|"TCP :5432"| S
    S --> F[("файлы базы на диске<br>в томе notes-pgdata")]

Посмотрим на примере. Когда ты выполнишь docker exec -it db psql -U notes -d notes, происходит вот что. docker exec запускает команду внутри работающего контейнера db; с -it и psql ты получаешь интерактивный сеанс. psql стартует и подключается к серверу на том же контейнере через локальный сокет (socket: вход в программу изнутри той же машины, не по сети; адрес по умолчанию), -U notes это «представиться пользователем notes», -d notes «хочу работать с базой notes». В ответ ты видишь приглашение notes=#: буква перед # это имя базы, а # значит, что у пользователя права суперпользователя (у обычного стоял бы >). Так ты сразу видишь, с кем ты разговариваешь.

Заметь, что пароль тебя не спросил. Внутри контейнера вход по локальному сокету идёт без пароля: так настроен образ postgres. Через сеть (например, из приложения на db:5432) пароль обязателен. Это касается и адреса 127.0.0.1 («эта же машина» по TCP): в pg_hba.conf образа (файл, в котором сервер записывает, кого и как пускать) для петли внутри контейнера тоже стоит trust, то есть «верю без пароля». Пароль спрашивают только у подключений по сетевому адресу контейнера: из соседнего контейнера по имени db или с хоста через опубликованный порт. А вот имя db внутри контейнера db превращается в адрес 172.x, а не в петлю, поэтому здесь пароль спросят (scram-sha-256).

Приложение делает то же самое, только не руками: берёт адрес из переменной DATABASE_URL вида postgresql://notes:пароль@db:5432/notes. Читается слева направо: протокол, пользователь, пароль, адрес сервера (db это имя контейнера в сети, урок 4.3), порт, имя базы.

Прикинь сам: В DATABASE_URL=postgresql://notes:secret@db:5432/notes сколько раз встречается слово notes и что оно означает в каждом месте?

Два раза: первое имя пользователя, второе имя базы. Хост здесь db, порт 5432, а пароль secret.

Осторожно: путают пользователя базы и пользователя Linux. Пользователь notes в PostgreSQL существует только внутри базы, к учётным записям операционной системы он не относится. Второе: «порт 5432 надо открывать наружу». Для работы приложения не надо: оно ходит к базе по внутренней сети Docker. Открытый наружу порт базы это лишняя дверь для чужих.

Главное: PostgreSQL это сервер на порту 5432: клиент подключается по сети, называет пользователя и базу, получает ответы на SQL.

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

Пользователь и пароль в PostgreSQL: кто что может

Если любой, кто достучался до порта, может читать и удалять данные, база не защищена. Поэтому сервер требует представиться и проверяет, что этому пользователю разрешено.

Пропуск в офис: у каждого сотрудника свой, и на нём записано, в какие помещения ему можно. Где аналогия не работает: в PostgreSQL права выдают не только на «помещения» (базы), но и на отдельные «шкафы» (таблицы) и даже на отдельные действия (читать, вставлять, удалять).

Пользователь в PostgreSQL (он же роль, role) создаётся командой SQL или при первом запуске контейнера. Переменная POSTGRES_USER в образе postgres:18 создаёт пользователя с правами суперпользователя (может всё на этом сервере), POSTGRES_PASSWORD задаёт ему пароль, POSTGRES_DB создаёт базу. Они читаются только при первой инициализации пустого каталога данных. Запущенный позже контейнер на уже созданных данных эти переменные игнорирует: поменять POSTGRES_PASSWORD в команде запуска недостаточно, пароль остался внутри тома. Эту ловушку разберём подробно в следующем разделе.

Вот как это выглядит на деле. Для нашего учебного стенда достаточно одного пользователя notes с полными правами. В боевой системе делают иначе: отдельный пользователь для приложения с правом читать и писать только в нужные таблицы, и отдельный для бэкапов с правом только читать. Тогда кража пароля приложения не даёт удалить базу целиком. Этот принцип, «ровно столько прав, сколько нужно» (принцип наименьших привилегий), ты уже видел для пользователя в контейнере (урок 4.2). Как это выглядит в SQL (на таблице notes из нашей схемы; выполняется под notes, как суперпользователь):

CREATE ROLE app LOGIN;                                  -- роль, которая может входить
GRANT SELECT, INSERT ON notes TO app;                   -- читать и добавлять, удалять нельзя
GRANT USAGE ON SEQUENCE notes_id_seq TO app;            -- право брать следующий номер для serial

Пароль роли app задают отдельно, интерактивной командой \password app в psql. Без права USAGE на последовательность (notes_id_seq, счётчик, который выдаёт serial) вставка упадёт с ошибкой permission denied for sequence. Схему при этом меняет другая роль, «для миграций» (миграция: скрипт, который меняет схему, например добавляет столбец): приложению права на CREATE и ALTER не нужны. У нас приложение создаёт таблицу само под notes, поэтому это учебное упрощение, и в проде его разделяют.

Прикинь сам: У приложения утёк пароль. У пользователя права только SELECT и INSERT в одну таблицу. Сколько таблиц злоумышленник сможет удалить?

Ни одной: DROP TABLE ему недоступен. С полными правами notes он удалил бы все.

Осторожно, частое заблуждение: что пароль можно хранить прямо в compose.yml или в Dockerfile. Нельзя: он попадёт в git или в образ и увидят все. Пароль передают отдельно (переменная окружения из файла, которого нет в git, урок 4.5).

Главное: у приложения должен быть отдельный пользователь с минимумом прав, а пароль не хранят в git и в образе.

Теперь запустим PostgreSQL в контейнере.

Проверь понимание: зачем приложению отдельный пользователь с ограниченными правами, если в учебном стенде хватает одного notes с полными?

Ответ

Если пароль приложения утечёт (например, через ошибку в коде), с правами «только читать и писать в свои таблицы» злоумышленник не сможет удалить таблицы и базу целиком. С правами суперпользователя сможет всё.

PostgreSQL в контейнере

Образ (image) postgres:18 это готовый PostgreSQL, упакованный в контейнер (см. урок 4.2). При запуске контейнер с пустым каталогом данных выполняет инициализацию: создаёт кластер (так PostgreSQL называет каталог с данными одного сервера), пользователя и базу. Как узнать, какие имя и пароль задать? Через переменные окружения. Переменная окружения (environment variable) это именованная настройка, которую процессу передают при запуске; в Docker её задаёт флаг -e ИМЯ=значение. Образ читает три:

  • POSTGRES_USER: имя создаваемого пользователя (роль с правами администратора). У нас notes.
  • POSTGRES_PASSWORD: его пароль.
  • POSTGRES_DB: имя создаваемой базы. У нас тоже notes.

Вот настоящие строки лога первого запуска (docker logs), по ним видно, что происходит:

fixing permissions on existing directory /var/lib/postgresql/18/docker ... ok
creating subdirectories ... ok
...
Success. You can now start the database server using:
...
waiting for server to start....LOG:  starting PostgreSQL 18.6 ...
LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE
...
waiting for server to shut down....LOG:  received fast shutdown request

Контейнер сначала запускает временный сервер, создаёт базу, останавливает его и только потом стартует настоящий. Это важно для готовности: в первые секунды сервер то отвечает, то нет, то снова нет.

Где лежат данные. Файлы базы должны жить в томе, иначе они исчезнут вместе с контейнером (урок 4.3). Начиная с PostgreSQL 18 образ хранит данные внутри версионного подкаталога: /var/lib/postgresql/18/docker (проверено: ls /var/lib/postgresql показал 18). Поэтому том монтируют на /var/lib/postgresql, а не на /var/lib/postgresql/data, как было в старых версиях. Если смонтировать том по старому пути, где уже лежит база от прежней версии, образ откажется стартовать с ошибкой и посоветует смонтировать том в /var/lib/postgresql (так описано в документации образа, мы это не прогоняли).

Подводный камень: переменные читаются только при инициализации. Если том уже содержит данные, образ пропускает инициализацию, и смена POSTGRES_PASSWORD ничего не меняет: пароль записан внутри данных кластера, а не берётся из переменной при каждом старте. Об этом сценарий в «Сломай и почини».

Порт 5432 наружу не публикуем. Флаг -p открывает порт контейнера на хосте и делает базу доступной всем, кто достучится до хоста. База нужна только приложению, а оно живёт в той же сети notes-net. Внутри сети порт доступен и без -p, а имя контейнера превращается в адрес встроенным DNS Docker (урок 4.3). Чтобы имя было понятным, мы задаём сетевой псевдоним --network-alias db: приложение обращается к db, как бы контейнер ни назывался.

Готовность. Сервер стартует несколько секунд. Программа pg_isready проверяет, принимает ли он подключения, и печатает один из ответов, которые я видел при запуске:

127.0.0.1:5432 - no response
127.0.0.1:5432 - rejecting connections
127.0.0.1:5432 - accepting connections

no response: сервера пока нет вообще, rejecting connections: он запущен, но ещё не готов, accepting connections: можно работать. Скрипты ждут именно последнюю строку циклом until ... do sleep 1; done. Ждём по TCP (-h 127.0.0.1): на первом запуске временный сервер слушает только unix-сокет, и по сокету accepting connections может прийти от него, а не от настоящего сервера.

Остановка и SIGKILL. docker stop шлёт образу postgres сигнал SIGINT (так задано в образе): PostgreSQL быстро завершает работу, сбрасывает данные и выходит. Если за 10 секунд (--stop-timeout, в Compose stop_grace_period) он не вышел, Docker шлёт SIGKILL. docker rm -f шлёт SIGKILL сразу, сервер не успевает ничего: при следующем старте он восстанавливается из журнала WAL. Данные целы, но есть побочный эффект, который ты увидишь в задании 4: serial перескакивает вперёд (PostgreSQL заранее резервирует в журнале по 32 номера последовательности, и после аварийной остановки эти номера считаются использованными). В моём прогоне после docker rm -f следующая заметка получила id = 34 вместо 2.

Пароль в -e виден. Всё, что передано через -e, показывает docker inspect db любому, кто может его запустить. Для учебного стенда это терпимо. В проде пароль кладут в файл или секрет и передают образу через POSTGRES_PASSWORD_FILE (путь к файлу с паролем вместо самого пароля).

Прикинь сам: Ты запустил контейнер с POSTGRES_PASSWORD=one, потом пересоздал его с тем же томом и POSTGRES_PASSWORD=two. Какой пароль действует?

one: переменные читаются только при первой инициализации пустого тома. Сменить пароль можно командой ALTER USER внутри базы. Если не помнишь пароль (терминал закрыт, переменная PGPASS пропала), он остался только в docker inspect db.

Осторожно, частое заблуждение: «Задал пароль в docker run, значит он всегда действует». Пароль действует, пока том не создан заново. Ещё путают «контейнер запущен» и «база готова»: между ними несколько секунд.

Главное: контейнер читает POSTGRES_USER, POSTGRES_PASSWORD и POSTGRES_DB только при первом запуске с пустым томом, а готовность проверяют pg_isready.

База готова. Подключим к ней приложение.

Приложение и база: подключение, параметры, проверка готовности

Как приложение находит базу и входит в неё? Ему передают строку подключения (connection string) в формате URL. У нас она лежит в переменной DATABASE_URL:

flowchart LR
    U["postgresql://notes:ПАРОЛЬ@db:5432/notes"]
    U --- A["postgresql: протокол"]
    U --- B["notes (первое): пользователь"]
    U --- C["ПАРОЛЬ: пароль"]
    U --- D["db: имя хоста, сетевой псевдоним контейнера базы"]
    U --- E["5432: порт"]
    U --- F["notes (второе): имя базы"]

Приложение использует драйвер (библиотеку для общения с базой) psycopg версии 3. Он ставится через pip из requirements.txt, как вся зависимость Python-проекта.

Соединение на каждый запрос. Функция pg_connect() открывает новое соединение при каждом обращении. Это просто, но каждое соединение стоит времени (подключение по TCP, проверка пароля) и памяти: PostgreSQL запускает на каждое соединение отдельный процесс. Число соединений ограничено параметром max_connections (в этом образе 100, я проверил командой SHOW max_connections;). Под нагрузкой лимит кончится, и появится ошибка too many connections. Лекарство называется пул соединений (connection pool): приложение держит несколько открытых соединений и одалживает их запросам по очереди. В нашем учебном коде пула нет, и это осознанный долг.

Параметры запроса и SQL-инъекции. Вот как приложение добавляет заметку:

conn.execute("INSERT INTO notes (text) VALUES (%s) RETURNING id", (text,))

Текст заметки передаётся отдельно от SQL-команды, вторым аргументом (text,). Драйвер отправляет их серверу порознь, и данные никогда не становятся частью команды. Если бы код склеивал строку сам, пользователь мог бы прислать заметку x'); DROP TABLE notes; --, и получилась бы команда, которая удаляет таблицу. Это SQL-инъекция (SQL injection): данные, которые обманом превратились в код. Я отправил в приложение ровно такую заметку (флаг -w печатает после ответа код HTTP):

curl -s -w ' %{http_code}\n' -X POST -d '{"text":"x'"'"'); DROP TABLE notes; --"}' http://127.0.0.1:8080/notes
{"id": 34} 201

201 значит «создано», а id равен 34 из-за того, что перед этим я останавливал базу через docker rm -f: serial перескочил вперёд (почему, объяснено выше). Проверяем, что таблица цела:

docker exec db psql -U notes -d notes -c "SELECT count(*) FROM notes"

Запрос вернул число строк, а не ошибку relation "notes" does not exist. Заметка записалась как обычный текст x'); DROP TABLE notes; --, а таблица notes осталась на месте. Правило: значения от пользователя только параметрами (%s и кортеж), никогда не склейкой строк и не f"...".

Проверка готовности. Эндпоинт /healthz отвечает «процесс жив» и базу не трогает. Эндпоинт /readyz отвечает «могу обслуживать запросы» и проверяет базу запросом SELECT 1. Различие важно. Оркестратор (система, которая запускает контейнеры и следит за ними, в теме 5 это Kubernetes) использует две проверки:

  • liveness («жив ли процесс»): если провалена, процесс перезапускают;
  • readiness («готов ли принимать трафик»): если провалена, процесс не трогают, а просто не направляют к нему запросы.

Если бы /healthz проверял базу, то при недоступной базе оркестратор перезапускал бы все копии приложения, хотя перезапуск базу не вылечит: получился бы каскад перезапусков. Проверка базы место в readiness: «пока базы нет, трафик мне не шлите». Наше приложение при недоступной базе остаётся живым: /healthz даёт 200, /readyz 503, а /notes 500 {"error": "storage"}. Схему создаёт функция ensure_pg_schema() при первом успешном обращении к базе, поэтому приложение, которое стартовало раньше базы, само восстанавливается, как только база появилась.

Прикинь сам: Пользователь прислал заметку x'); DROP TABLE notes; --. Сколько таблиц удалится, если приложение передаёт текст параметром (text,)?

Ни одной: заметка запишется как обычный текст, а таблица останется. Если подставить текст прямо в SQL, команда DROP TABLE выполнилась бы.

Осторожно: думают, что /readyz должен проверять всё подряд, включая внешние сервисы, от которых зависит сервис. Так делать опасно: сбой чужого сервиса снимет с трафика все ваши копии сразу. Проверяют только то, без чего запрос точно не выполнится.

Главное: значения от пользователя передают только параметрами, /healthz проверяет процесс, а /readyz базу.

Остался вопрос о сохранности данных, и том тут не спасёт.

Проверь понимание: почему параметры передают как (text,), а не подставляют текст прямо в SQL?

Ответ

Потому что тогда данные не становятся частью команды, и SQL-инъекция невозможна: что бы пользователь ни прислал, драйвер воспримет это как значение, а не как SQL. Заодно он сам разбирается с кавычками и спецсимволами.

Резервная копия: том это не бэкап

Том защищает данные от удаления контейнера, но не от ошибки человека: если кто-то выполнил DROP TABLE или DELETE без условия, том послушно сохранит уже пустую таблицу. Диск тоже может умереть вместе со всем томом. Копия, лежащая отдельно от базы, единственное, что спасает в таких случаях.

Том это записная книжка, которая лежит на вашем столе: она переживёт то, что вы уйдёте домой, но не переживёт пролитый на неё чай. Бэкап это ксерокопия книжки в другом здании. Оговорка: ксерокопия устаревает, её актуальность равна времени последнего копирования.

Для PostgreSQL есть утилита pg_dump: она подключается к базе как обычный клиент, читает данные и выводит их в виде текста из SQL-команд (CREATE TABLE ..., COPY ...). Чтобы восстановиться, этот текст скармливают psql в пустую базу. Читает pg_dump в одной транзакции со снимком данных, поэтому копия согласована, даже если во время копирования приложение пишет в базу.

Порядок такой:

  1. pg_dump делает текстовый файл (дамп).
  2. Файл уносят с сервера (на другой диск, в объектное хранилище).
  3. Для восстановления создают пустую базу и подают дамп на вход psql.
  4. Проверяют, что данные на месте: копия, которую ни разу не восстанавливали, это гипотеза, а не бэкап.

Команды для нашего стенда. Выполни все шаги по порядку, они безопасны для рабочей базы:

docker exec db pg_dump -U notes -d notes > notes-dump.sql
docker exec db dropdb -U notes --if-exists notes_restore
docker exec db createdb -U notes notes_restore
docker exec -i db psql -U notes -d notes_restore < notes-dump.sql
docker exec db psql -U notes -d notes -c "SELECT count(*) FROM notes"
docker exec db psql -U notes -d notes_restore -c "SELECT count(*) FROM notes"

Разбор. Первая строка запускает pg_dump внутри контейнера, а > (перенаправление, урок 1.2) записывает вывод в файл notes-dump.sql на хосте. Вторая удаляет старую notes_restore, если она осталась от прошлого запуска (--if-exists: без ошибки, когда её нет), а третья создаёт её пустой. Четвёртая подаёт файл на вход psql (-i держит вход открытым, < подставляет файл вместо клавиатуры). Две последние считают строки в обеих базах. Размер файла зависит от данных: для нашей учебной базы это несколько килобайт.

Как читать вывод: каждый из двух запросов печатает одно число в колонке count. Если числа равны, копия восстановилась полностью. Если во второй базе ошибка relation "notes" does not exist, дамп не дошёл: смотри сообщения четвёртой команды.

Лаборатория и прод. У нас одна база в одном контейнере, пароль в переменной окружения, ни реплики, ни восстановления на момент времени. В проде базу обычно берут управляемую у облачного провайдера или ведут оператором с Patroni (программа, которая следит за кластером и переключает главный сервер). Там делают физические копии файлов базы плюс архив журнала WAL, а логические копии снимают pg_dump -Fc и восстанавливают pg_restore.

Прикинь сам: pg_dump запускается каждую ночь в 03:00, а все строки удалили в 10:00. Сколько часов данных потеряется?

7 часов: восстановишь состояние на 03:00, а записи с 03:00 до 10:00 пропадут. Том сохранил бы уже пустую таблицу.

Осторожно, частое заблуждение: «У меня есть том, значит есть бэкап». Нет: том лежит на том же диске, под тем же управлением и с теми же ошибками, что и база. Ещё путают копию и репликацию. Реплика (replica) - это второй сервер базы, который в реальном времени повторяет все изменения первого, как второй экземпляр документа, в который автоматически попадает каждая твоя правка. Поэтому реплика мгновенно повторяет и DROP TABLE (команда удаления таблицы), а копия хранит состояние на момент снимка.

Главное: том защищает от удаления контейнера, но не от ошибки человека; бэкап это копия в другом месте, которую хоть раз проверили восстановлением.

Проверь понимание: чем резервная копия отличается от реплики и почему реплика не спасёт от случайного DROP TABLE?

Ответ

Реплика повторяет все изменения основного сервера в реальном времени, поэтому удаление таблицы тут же случится и на ней. Копия хранит состояние на момент снимка: из неё можно вернуть данные, какими они были до ошибки.

Практика

Все команды выполняй там же, где ты работал в уроках 4.1-4.3 (ВМ с Docker). Сеть notes-net из урока 4.3 должна существовать: docker network inspect notes-net >/dev/null 2>&1 || docker network create notes-net. Если контейнер notes с прошлых уроков ещё запущен, останови его: docker rm -f notes 2>/dev/null. Выводы ниже настоящие, получены на postgres:18 (версия сервера 18.6). Значения, которые меняются от запуска к запуску (время, адреса, номера процессов), у тебя будут другими.

Задание 1. Поднимаем PostgreSQL 18 и заходим в psql

Цель: запустить базу в сети notes-net с томом и выполнить первый запрос.

Предскажи: после docker run с -d сервер стартует несколько секунд. Что увидит psql, если подключиться мгновенно? А что покажет \dt в свежей базе notes?

Ответ

Сразу после старта подключение не удастся: psql: error: connection to server on socket ... failed: No such file or directory, потому что сервер ещё инициализируется (временный сервер на init-фазе). \dt покажет Did not find any tables.: таблиц ещё нет.

Шаги. Сначала разберём команды.

Пароль. Команда openssl rand -base64 24 печатает 24 случайных байта в виде текста. Дальше | (пайп, урок 1.2) передаёт этот текст команде tr -d '/+=', которая удаляет символы /, + и =: / ломает разбор адреса, а + и = убраны про запас (в пароле внутри URL безопаснее обходиться буквами и цифрами). Конструкция $(...) подставляет результат команды на своё место, а export делает переменную PGPASS видимой для программ, которые ты запускаешь из этой оболочки.

  1. Сгенерируй пароль и сохрани в переменную оболочки (ниже он подставляется как $PGPASS, в git его не кладём):
export PGPASS="$(openssl rand -base64 24 | tr -d '/+=')"

Команда молчит, это нормально. Переменная живёт только в этом окне терминала: если откроешь новое окно, задай PGPASS заново (но тогда это будет другой пароль, не тот, с которым создан том, см. «Сломай и почини»).

  1. Запусти контейнер. Разбор флагов: -d работать в фоне; --name db имя контейнера; --network notes-net подключить к сети; --network-alias db дополнительное DNS-имя в сети; -e ... три переменные окружения для инициализации; -v notes-pgdata:/var/lib/postgresql том notes-pgdata (Docker создаст его сам) на каталог данных; postgres:18 образ. Порт наружу не публикуем: приложение обратится по имени db внутри сети.
docker run -d --name db --network notes-net --network-alias db \
  -e POSTGRES_USER=notes -e POSTGRES_PASSWORD="$PGPASS" -e POSTGRES_DB=notes \
  -v notes-pgdata:/var/lib/postgresql \
  postgres:18

При первом запуске Docker скачает образ, это занимает время. В конце команда печатает длинный идентификатор контейнера.

  1. Дождись готовности и зайди в psql внутри контейнера. Цикл until ...; do sleep 1; done повторяет pg_isready (проверка «принимает ли сервер подключения»), пока та не вернёт успех. docker exec -it db psql -U notes -d notes запускает внутри контейнера db клиент psql от имени пользователя notes (-U) и подключает его к базе notes (-d); -it нужен, чтобы можно было печатать команды руками.
until docker exec db pg_isready -h 127.0.0.1 -U notes -d notes; do sleep 1; done
docker exec -it db psql -U notes -d notes

-h 127.0.0.1 проверяет через TCP. Сокет это вход в программу изнутри той же машины, не по сети, а приложению нужна именно сеть, поэтому и проверка идёт по сети. Заодно временный сервер первого запуска слушает только сокет, и без -h цикл мог бы остановиться раньше времени.

  1. Внутри psql введи по строке (\q выходит; команды с обратной косой чертой, как \dt, это команды самого psql, а не SQL):
SELECT version();
\dt
\q

Что должно получиться: цикл ожидания печатает по строке на каждую попытку, последняя accepting connections. У меня первая попытка попала в момент, когда сервер ещё не отвечал:

127.0.0.1:5432 - no response
127.0.0.1:5432 - accepting connections

Строк до успешной может быть больше (например, rejecting connections, если сервер уже запущен, но ещё не готов) или совсем не быть. Ответы psql (в интерактивном сеансе перед каждой командой стоит приглашение notes=#, ниже показан только вывод):

                                                         version                                                          
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

Did not find any tables.

Как читать вывод: в version() важны две вещи: 18.6 (мажорная версия 18, минорная у тебя может быть новее) и архитектура процессора (aarch64 у меня, у тебя может быть x86_64: это не ошибка). Did not find any tables. значит «таблиц нет»: база создана, но пуста.

Объясни себе:

  • Почему том смонтирован на /var/lib/postgresql, а не на /var/lib/postgresql/data?
  • Почему мы не публикуем порт 5432 на хост и как приложение найдёт базу?

Типичные ошибки:

  • docker: Error response from daemon: Conflict. The container name "/db" is already in use by container ...: старый контейнер с таким именем, убери docker rm -f db.
  • psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory (следом Is the server running locally and accepting connections on that socket?): сервер ещё не поднялся или контейнер упал, смотри docker logs db.
  • Bind for 0.0.0.0:5432 failed: port is already allocated (у Docker в тексте failed to set up container networking: driver failed programming external connectivity ...): появляется, если ты добавил -p 5432:5432, а порт занят хостовым PostgreSQL или другим контейнером. Нам порт наружу не нужен.

Задание 2. SELECT, INSERT, JOIN и транзакция

Цель: набить руку на SQL в отдельной учебной базе lab, не трогая рабочую notes.

Предскажи: у автора без постов LEFT JOIN вернёт count(p.id) равный чему? А count(*)?

Ответ

count(p.id) даст 0 (NULL не считаются), count(*) даст 1 (строка-результат существует, хоть справа пусто).

Шаги. Команды ниже используют два приёма. docker exec db createdb -U notes lab запускает в контейнере утилиту createdb, которая создаёт пустую базу lab. Конструкция docker exec -i db psql ... <<'SQL' ... SQL (heredoc, урок 1.6) отправляет весь текст между <<'SQL' и строкой SQL на вход psql: так удобно выполнять много команд сразу. Флаг -i держит вход открытым; кавычки вокруг SQL запрещают оболочке трогать $ и другие символы внутри текста.

  1. Создай базу и таблицы:
docker exec db createdb -U notes lab
docker exec -i db psql -U notes -d lab <<'SQL'
CREATE TABLE authors (id serial PRIMARY KEY, name text NOT NULL UNIQUE);
CREATE TABLE posts (
  id serial PRIMARY KEY,
  author_id int NOT NULL REFERENCES authors(id),
  title text NOT NULL
);
INSERT INTO authors (name) VALUES ('anna'), ('boris'), ('vera');
INSERT INTO posts (author_id, title) VALUES (1, 'про nginx'), (1, 'про TLS'), (2, 'про Docker');
SQL

Ожидаемый ответ: CREATE TABLE, CREATE TABLE, INSERT 0 3, INSERT 0 3 (по строке на команду; createdb ничего не печатает).

  1. Запрос с LEFT JOIN (разобран в теории):
docker exec -i db psql -U notes -d lab <<'SQL'
SELECT a.name, count(p.id) AS posts
FROM authors a LEFT JOIN posts p ON p.author_id = a.id
GROUP BY a.name ORDER BY posts DESC, a.name;
SQL
  1. Транзакция с откатом и нарушением ограничения:
docker exec -i db psql -U notes -d lab <<'SQL'
BEGIN;
DELETE FROM posts;
SELECT count(*) AS after_delete FROM posts;
ROLLBACK;
SELECT count(*) AS after_rollback FROM posts;
INSERT INTO posts (author_id, title) VALUES (99, 'нет такого автора');
SQL

Что должно получиться (первый запрос):

 name  | posts 
-------+-------
 anna  |     2
 boris |     1
 vera  |     0
(3 rows)

и транзакции:

BEGIN
DELETE 3
 after_delete 
--------------
            0
(1 row)

ROLLBACK
 after_rollback 
----------------
              3
(1 row)

ERROR:  insert or update on table "posts" violates foreign key constraint "posts_author_id_fkey"
DETAIL:  Key (author_id)=(99) is not present in table "authors".

Как читать вывод: таблица-ответ состоит из строки заголовков, линии и строк данных, внизу (N rows). Ответы вида BEGIN, DELETE 3, ROLLBACK это «квитанции» команд: DELETE 3 значит, что внутри транзакции удалено три строки. after_delete равен 0, потому что внутри своей транзакции ты видишь свои изменения, а after_rollback вернулся к 3: ROLLBACK отменил удаление. Последний запрос упал с ERROR: внешний ключ не позволил сослаться на автора 99. Обрати внимание на DETAIL: база объясняет причину. Название ограничения posts_author_id_fkey она придумала сама.

Если сомневаешься, что делает запрос из этого задания, вставь его нейросети и попроси объяснить по частям (SELECT, FROM, WHERE). Проверь ответ в psql: нейросеть любит выдумывать столбцы, которых в твоей таблице нет.

Объясни себе:

  • Почему vera осталась в результате, и что изменится при INNER JOIN?
  • Что доказал after_rollback?
  • Какую защиту дал REFERENCES и зачем она, если приложение «и так» передаёт верный author_id?

Типичные ошибки:

  • ERROR: relation "authors" does not exist: ты подключился не к той базе (нет -d lab) или таблицы не создались.
  • ERROR: column "a.name" must appear in the GROUP BY clause or be used in an aggregate function: в SELECT есть столбец, которого нет в GROUP BY (у меня так получилось, когда я убрал GROUP BY): добавь его или оберни в агрегат.
  • ERROR: duplicate key value violates unique constraint "authors_name_key" и DETAIL: Key (name)=(anna) already exists.: повторно выполнил вставку авторов, UNIQUE сработал: это норма.

Задание 3. Медленный запрос, EXPLAIN и индекс

Цель: воспроизвести «база тормозит», увидеть Seq Scan в плане и вылечить его индексом.

Предскажи: в таблице 1 000 000 строк, и мы ищем одну по точному значению text. Какой узел плана будет до индекса и какой после? Во сколько раз ускорится: в 2, в 100 или в 10000?

Ответ

До индекса Seq Scan (читает всю таблицу), после Index Scan (или Bitmap Index Scan). Ускорение порядка сотен раз, точные цифры зависят от машины: у меня 15 мс и 0,04 мс, то есть почти в 380 раз.

Шаги. Команда generate_series(1, 1000000) выдаёт числа от 1 до 1 000 000, а INSERT ... SELECT вставляет в таблицу по строке на каждое число. 'заметка ' || g склеивает слово и число (|| это склейка строк). now() - (g || ' seconds')::interval вычитает из текущего времени g секунд: ::interval превращает текст в промежуток времени. ANALYZE big_notes пересчитывает статистику для планировщика.

  1. Наполни таблицу миллионом строк в учебной базе lab (данные псевдослучайные, но воспроизводимые). На моей машине заполнение заняло пару секунд:
docker exec -i db psql -U notes -d lab <<'SQL'
CREATE TABLE big_notes (
  id serial PRIMARY KEY,
  text text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO big_notes (text, created_at)
SELECT 'заметка ' || g, now() - (g || ' seconds')::interval
FROM generate_series(1, 1000000) AS g;
ANALYZE big_notes;
SQL

Ожидаемый ответ: CREATE TABLE, INSERT 0 1000000, ANALYZE.

  1. Запрос без индекса. Флаг -c выполняет одну команду и выходит:
docker exec db psql -U notes -d lab -c \
  "EXPLAIN ANALYZE SELECT * FROM big_notes WHERE text = 'заметка 777777';"
  1. Создай индекс и повтори запрос:
docker exec db psql -U notes -d lab -c \
  "CREATE INDEX big_notes_text_idx ON big_notes (text);"
docker exec db psql -U notes -d lab -c \
  "EXPLAIN ANALYZE SELECT * FROM big_notes WHERE text = 'заметка 777777';"

Что должно получиться (цифры будут другими, важны узлы плана). Без индекса (PostgreSQL 18 сам добавил строки Buffers, я их здесь опустил для краткости):

 Gather  (cost=1000.00..14532.43 rows=1 width=33) (actual time=12.857..15.123 rows=1.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on big_notes  (cost=0.00..13532.33 rows=1 width=33) (actual time=10.845..11.753 rows=0.33 loops=3)
         Filter: (text = 'заметка 777777'::text)
         Rows Removed by Filter: 333333
 Planning Time: 0.133 ms
 Execution Time: 15.148 ms

CREATE INDEX отвечает CREATE INDEX (у меня 1,5 секунды), а после индекса:

 Index Scan using big_notes_text_idx on big_notes  (cost=0.42..8.44 rows=1 width=33) (actual time=0.022..0.023 rows=1.00 loops=1)
   Index Cond: (text = 'заметка 777777'::text)
   Index Searches: 1
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=50 read=1
 Planning Time: 0.258 ms
 Execution Time: 0.040 ms

Как читать вывод: сначала найди тип узла: Parallel Seq Scan до индекса и Index Scan using big_notes_text_idx после. Затем Rows Removed by Filter: 333333 (сколько строк прочитано зря) и Execution Time. Подробный разбор каждого поля плана есть в теории, раздел «Индексы и EXPLAIN». Первый запрос после создания индекса чуть медленнее второго (read=3 значит, что часть страниц пришлось прочитать не из кэша): поэтому при измерениях запрос повторяют.

Вставь нейросети вывод EXPLAIN ANALYZE и спроси, где запрос тратит время. Сверь ответ с типом узла (Seq Scan или Index Scan) и числом Rows Removed by Filter: нейросеть иногда советует индекс там, где условие отбирает половину таблицы.

  1. Посмотри, сколько места занял индекс, и проверь, что LIKE '%777' его не использует:
docker exec db psql -U notes -d lab -c '\di+ big_notes*'
docker exec db psql -U notes -d lab -c \
  "EXPLAIN ANALYZE SELECT * FROM big_notes WHERE text LIKE '%777';"

Первая команда покажет размеры индексов (у меня big_notes_pkey 21 MB и big_notes_text_idx 39 MB), вторая опять Parallel Seq Scan и Rows Removed by Filter: 333000.

Объясни себе:

  • Что значит Rows Removed by Filter и почему это признак проблемы?
  • Индекс занял место на диске. Что ещё стало дороже, и когда индекс не стоит создавать?
  • Почему запрос WHERE text LIKE '%777' индекс не использует?

Типичные ошибки:

  • ERROR: relation "big_notes" does not exist: работаешь в базе notes, а не lab.
  • Планировщик выбрал Seq Scan даже после индекса: не выполнен ANALYZE (устаревшая статистика) либо запрос отбирает большую долю таблицы.
  • Вставка миллиона строк идёт заметно дольше обычных секунд или падает с No space left on device: на очень слабой ВМ или при нехватке места уменьши число строк до 300000 в generate_series (числа в выводе EXPLAIN тогда будут другими).

Задание 4. Шаг проекта: «Заметки» переезжают на PostgreSQL

Изменения Python-приложения возьми из образца. Найди настройки подключения и SQL-запросы к заметкам, свяжи доступность БД с ответом /readyz; устройство драйвера и весь код HTTP-сервера можно пока не разбирать. SQL из заданий 2 и 3, напротив, разбери и выполняй осознанно: запросы, транзакция и индекс здесь относятся к твоему новому навыку.

Цель: приложение v4 хранит заметки в базе, /readyz проверяет БД, /slowsql показывает влияние медленного запроса.

Предскажи: запустишь новое приложение с STORE=postgres, но раньше базы. Что покажет docker logs, и что вернёт /readyz? А если приложение стартует раньше готовности БД, оно должно упасть или ждать?

Ответ

В логе будет предупреждение база пока недоступна: failed to resolve host 'db' (имя db ещё ни на что не указывает) или connection refused, а /readyz вернёт 503: процесс жив (/healthz даёт 200), но обслуживать запросы не может. Это ровно разница liveness и readiness. Приложение не падает: соединение открывается на каждый запрос, а схему оно создаёт при первом успешном обращении, поэтому как только база поднимется, /readyz станет 200.

Шаги.

  1. Перейди в репозиторий и создай db/schema.sql (схема из теории; приложение создаёт таблицу само, файл нужен, чтобы схема была в git и её можно было применить вручную). Команда mkdir -p db создаёт каталог (-p: без ошибки, если он уже есть), а cat > файл <<'SQL' ... SQL записывает текст в файл:
cd ~/notes
mkdir -p db
cat > db/schema.sql <<'SQL'
-- схема хранилища "Заметок"; повторный запуск безопасен
CREATE TABLE IF NOT EXISTS notes (
    id         serial PRIMARY KEY,
    text       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
SQL
  1. Добавь зависимость. psycopg[binary] это драйвер PostgreSQL для Python; слово в квадратных скобках просит установить вместе с ним готовую собранную часть, чтобы не нужен был компилятор. Запись >=3.2,<4 значит «версия 3.2 или новее, но не 4». Одинарные кавычки нужны, чтобы оболочка не приняла < и > за перенаправление ввода-вывода:
echo 'psycopg[binary]>=3.2,<4' > requirements.txt

Сборка образа (в следующем шаге) поставит psycopg 3.3.6 (актуально на 2026-09-30, у тебя может быть новее из ветки 3).

  1. Замени app.py эталоном версии v4. Этот файл заменяет твою версию из урока 2.4 целиком и сохраняет все прежние эндпоинты (/healthz, /slow, /error, /leak, /burn и остальные). Ниже разобрано, что в нём нового.
curl -fsSL -o app.py https://raw.githubusercontent.com/distinguished-sre/learning/main/devops/project/notes/versions/v4.py
grep -c psycopg app.py

Разбор curl: -f (вернуть ошибку при 404, а не сохранить страницу ошибки), -sS (тихо, но ошибки показать), -L (идти по перенаправлениям), -o app.py (сохранить в файл). grep -c считает строки с указанным словом: ненулевое число значит, что файл получен.

Что нового в v4. Выбор хранилища и подключение драйвера. Драйвер импортируется только при STORE=postgres, поэтому в файловом режиме он не нужен:

STORE = os.environ.get("STORE", "file")
DATABASE_URL = os.environ.get("DATABASE_URL", "")

# В файловом режиме сторонних зависимостей нет.
if STORE == "postgres":
    try:
        import psycopg
    except ImportError:
        print("STORE=postgres требует psycopg[binary]>=3.2,<4", file=sys.stderr)
        sys.exit(2)
elif STORE != "file":
    print("STORE должен быть file или postgres", file=sys.stderr)
    sys.exit(2)

Схема и функции работы с базой (по одной на действие):

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS notes (
    id         serial PRIMARY KEY,
    text       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
)
"""


def pg_connect():
    # Соединение на каждый запрос: просто, но без пула и с лишней задержкой.
    # Компромисс осознанный, пул появится, когда нагрузка это оправдает.
    return psycopg.connect(DATABASE_URL, connect_timeout=3)


def pg_init():
    with pg_connect() as conn:
        conn.execute(SCHEMA_SQL)


def pg_list():
    with pg_connect() as conn:
        rows = conn.execute(
            "SELECT id, text, created_at FROM notes ORDER BY id"
        ).fetchall()
    return [
        {"id": r[0], "text": r[1], "created_at": r[2].isoformat()} for r in rows
    ]


def pg_add(text):
    with pg_connect() as conn:
        # параметры передаются отдельно от SQL: защита от SQL-инъекций
        return conn.execute(
            "INSERT INTO notes (text) VALUES (%s) RETURNING id", (text,)
        ).fetchone()[0]


def pg_ready():
    try:
        with pg_connect() as conn:
            conn.execute("SELECT 1")
        return True
    except psycopg.Error:
        return False


def pg_sleep(sec):
    with pg_connect() as conn:
        conn.execute("SELECT pg_sleep(%s)", (sec,))

Как читать этот код. with pg_connect() as conn: открывает соединение и закрывает его в конце блока, а при успехе делает COMMIT (при исключении откат). connect_timeout=3 не даёт приложению ждать недоступную базу дольше трёх секунд. pg_list превращает строки из базы в словари для JSON, а время в строку (isoformat()). pg_add использует RETURNING id, о котором шла речь в теории, и параметр %s. pg_ready возвращает True, если SELECT 1 прошёл, и False при любой ошибке драйвера. pg_sleep вызывает функцию PostgreSQL, которая просто ждёт sec секунд: для демонстрации.

Дальше в файле функции ensure_pg_schema() (повторяет pg_init(), пока не получится), list_notes(), save_note(), storage_ready(), initialize_storage(): они выбирают между файлом и базой по STORE. Обработчик делает так:

  • GET /readyz при STORE=postgres отвечает 200 ready, если база отвечает, иначе 503 not ready;
  • GET /notes и POST /notes работают через pg_list() и pg_add(); при недоступной базе отвечают 500 {"error": "storage"} и пишут причину в лог;
  • GET /slowsql?sec=N (демонстрационный, в реальном сервисе его бы не было): целое N от 0 до 30 (иначе 400 sec must be 0..30), вызывает pg_sleep(N) и отвечает 200 slept N; при STORE=file отвечает 501 {"error": "postgres only"};
  • в main при STORE=postgres вызывается initialize_storage(): она пишет предупреждение в лог, если база недоступна, но не падает.

Полный файл версии v4 лежит в репозитории курса: project/notes/versions/v4.py.

  1. Обновлять Dockerfile не нужно: он из урока 4.2 уже копирует requirements.txt первым слоем и ставит зависимости. Пересобери образ (-t даёт ему имя и версию, точка означает «контекст сборки: текущий каталог»):
docker build -t notes:0.4.0 .

В выводе (у тебя он будет в формате твоего docker build, я привожу существенные строки) слой с зависимостями теперь не из кэша, потому что requirements.txt изменился:

 => [5/6] RUN pip install --no-cache-dir -r requirements.txt
 ...
 Successfully installed psycopg-3.3.6 psycopg-binary-3.3.6
 => [6/6] COPY app.py .
 => naming to docker.io/library/notes:0.4.0

Предупреждение WARNING: Running pip as the 'root' user ... безвредно: оно про запуск pip от root внутри сборки. Размер образа вырос до 250 MB (у меня) из-за драйвера.

  1. Запусти приложение в notes-net (пароль из переменной PGPASS, порт публикуем только на localhost). -p 127.0.0.1:8080:8080 открывает порт 8080 контейнера на порту 8080 только для самой машины. -e STORE=postgres включает режим базы, -e DATABASE_URL=... собирает строку подключения из разобранных в теории частей, ${PGPASS} подставляет пароль. docker rm -f notes 2>/dev/null в начале убирает старый контейнер с этим именем, если он есть, а сообщение об ошибке отправляет в никуда:
docker rm -f notes 2>/dev/null
docker run -d --name notes --network notes-net -p 127.0.0.1:8080:8080 \
  -e STORE=postgres -e APP_VERSION=0.4.0 \
  -e DATABASE_URL="postgresql://notes:${PGPASS}@db:5432/notes" \
  notes:0.4.0
  1. Проверь всю цепочку, включая переживание пересоздания базы. Команда curl -s -o /dev/null -w '%{http_code}\n' не печатает тело ответа (-o /dev/null), а выводит только код (-w). -X POST -d '...' отправляет POST с телом. docker rm -f db убивает контейнер базы (SIGKILL), а следующий docker run создаёт новый контейнер на том же томе:
curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/readyz
curl -s -X POST -d '{"text":"первая заметка в PostgreSQL"}' http://127.0.0.1:8080/notes
curl -s http://127.0.0.1:8080/notes
docker rm -f db
docker run -d --name db --network notes-net --network-alias db \
  -e POSTGRES_USER=notes -e POSTGRES_PASSWORD="$PGPASS" -e POSTGRES_DB=notes \
  -v notes-pgdata:/var/lib/postgresql postgres:18
until docker exec db pg_isready -h 127.0.0.1 -U notes -d notes; do sleep 1; done
curl -s http://127.0.0.1:8080/notes
curl -s -w ' %{http_code}\n' 'http://127.0.0.1:8080/slowsql?sec=2'

Что должно получиться:

200
{"id": 1}
[{"id": 1, "text": "первая заметка в PostgreSQL", "created_at": "2026-09-30T12:14:57.854796+00:00"}]
db
d0c1...длинный идентификатор нового контейнера...
127.0.0.1:5432 - rejecting connections
127.0.0.1:5432 - accepting connections
[{"id": 1, "text": "первая заметка в PostgreSQL", "created_at": "2026-09-30T12:14:57.854796+00:00"}]
slept 2 200

Время в created_at у тебя будет своё, идентификатор контейнера тоже. Заметка пережила удаление контейнера db, потому что данные лежат в томе notes-pgdata.

Как читать вывод: 200 это /readyz: приложение видит базу. {"id": 1} это ответ на POST: база выдала заметке номер 1 через RETURNING. Строка db печатается командой docker rm -f db. Между двумя accepting connections цикл ждал, пока новый контейнер поднимется: rejecting connections значит «запущен, но не готов». Последняя строка: slept 2 это тело ответа /slowsql, 200 код (curl печатает его из-за -w), и запрос действительно занял две секунды: приложение отправило базе SELECT pg_sleep(2).

  1. Посмотри, что видит база, пока идёт медленный запрос. Первая команда запускает /slowsql на 20 секунд в фоне (& в конце возвращает управление терминалу), вторая через секунду спрашивает у базы, чем она занята. pg_stat_activity это системная таблица со всеми текущими соединениями:
curl -s 'http://127.0.0.1:8080/slowsql?sec=20' >/dev/null &
sleep 1
docker exec db psql -U notes -d notes -c \
  "SELECT pid, usename, state, wait_event, now()-query_start AS runs, left(query,40) AS query FROM pg_stat_activity WHERE datname='notes' AND pid <> pg_backend_pid();"
 pid | usename | state  | wait_event |      runs       |        query        
-----+---------+--------+------------+-----------------+---------------------
  43 | notes   | active | PgSleep    | 00:00:01.057309 | SELECT pg_sleep($1)
(1 row)

Это тот же инструмент, которым на работе находят «зависшие» запросы. state = active значит «выполняется прямо сейчас», runs сколько уже идёт, pid номер процесса, которого при необходимости можно остановить командой SELECT pg_cancel_backend(pid). Условие pid <> pg_backend_pid() убирает из списка само соединение psql.

  1. Убедись, что docker rm -f (SIGKILL) оставил след в номерах:
curl -s -X POST -d '{"text":"вторая заметка"}' http://127.0.0.1:8080/notes

У меня ответ был {"id": 34}, а не {"id": 2}: после аварийной остановки PostgreSQL пропустил зарезервированные номера (см. теорию про SIGKILL). У тебя число может быть другим, и это нормально.

  1. Эксперимент «база пропала»: остановим её штатно и посмотрим на приложение.
docker stop db
curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/healthz
curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/readyz
curl -s -w ' %{http_code}\n' http://127.0.0.1:8080/notes
docker logs --tail 2 notes
docker start db
until docker exec db pg_isready -h 127.0.0.1 -U notes -d notes; do sleep 1; done
curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/readyz

Ожидаемый вывод: db, затем 200 (healthz жив), 503 (readyz не готов), {"error": "storage"} 500, две строки лога (первая ERROR ошибка хранилища: failed to resolve host 'db': [Errno -2] Name or service not known, вторая запись access-лога с 500), db, ожидание готовности и в конце 200. Обрати внимание на текст: контейнер остановлен, и Docker убрал его имя из DNS, поэтому приложение видит не Connection refused, а «имя не найдено». Приложение при этом не перезапускалось и само восстановилось.

Состояние проекта после урока: app.py версии v4, requirements.txt, db/schema.sql, образ notes:0.4.0, PostgreSQL 18 в сети notes-net (5432 внутри сети), долг: пароль базы передаётся переменной окружения (закроется в теме 9).

Зафиксируй в git:

git add app.py requirements.txt db/schema.sql
git commit -m "Хранилище PostgreSQL: STORE=postgres, readyz по БД, slowsql"

Объясни себе:

  • Почему /readyz теперь проверяет базу, а /healthz нет? Что произойдёт с трафиком, если БД недоступна, а мы проверяли бы её в liveness?
  • Зачем параметры передаются как (text,), а не подстановкой строки в SQL?

Типичные ошибки:

  • FATAL: password authentication failed for user "notes" (в логе приложения целиком: connection failed: connection to server at "172.18.0.3", port 5432 failed: FATAL: password authentication failed for user "notes"): пароль в DATABASE_URL не совпадает с тем, что записан в томе при инициализации. Проверь PGPASS: в новом окне терминала переменная пропала, её надо задать заново (и пароль должен совпадать с тем, с которым создан том). Или смени пароль через ALTER USER.
  • failed to resolve host 'db': [Errno -2] Name or service not known: контейнеры не в одной пользовательской сети или база не запущена, проверь docker network inspect notes-net.
  • STORE=postgres требует psycopg[binary]>=3.2,<4 и контейнер сразу завершился (код 2): образ собран до правки requirements.txt (или в нём нет драйвера), пересобери docker build.
  • Таблицы нет (relation "notes" does not exist) при ручной работе через psql: если приложение ни разу не подключалось к базе, схемы ещё нет, примени её вручную: docker exec -i db psql -U notes -d notes < db/schema.sql.

Сломай и почини

Четыре поломки на стенде из задания 4 (контейнеры db и notes работают, приложение пишет в базу). Скрипт ломает стенд, а ты диагностируешь и чинишь. Правило: сначала симптом, потом гипотезы, потом проверка, и только затем действие. Скрипт не требует sudo.

Скачай скрипт и запусти нужный сценарий. curl -fsSL -o сохраняет файл (разбор флагов был в задании 4), bash файл 1 запускает скрипт с аргументом «сценарий 1»:

curl -fsSL -o /tmp/break-4.4.sh https://raw.githubusercontent.com/distinguished-sre/learning/main/devops/project/notes/break/4.4/break.sh
bash /tmp/break-4.4.sh 1

Скрипт печатает Сценарий 1 готов. и подсказку, с чего начать. Аргумент fix возвращает всё как было (запускай его между сценариями). Скрипт короткий: перед запуском пролистай его (less /tmp/break-4.4.sh), чужой код вслепую не запускают. Ссылка ведёт на ветку main, поэтому она может измениться; если нужна строгая воспроизводимость, подставь в адрес хеш коммита вместо main. Если что-то пошло не так в твоём репозитории, откатывай конкретные файлы: git restore app.py, а не всё подряд.

Сценарий 1. Приложение живо, но /readyz отвечает 503

Симптом. bash /tmp/break-4.4.sh 1, затем:

curl -si http://127.0.0.1:8080/readyz | head -1
curl -s http://127.0.0.1:8080/notes
docker ps --format '{{.Names}}: {{.Status}}'

Ты увидишь HTTP/1.1 503 Service Unavailable, ответ {"error": "storage"} на /notes, а оба контейнера в списке Up.

Гипотезы: база упала (нет: db в списке); приложение потеряло сеть (проверь по имени); база принимает подключения, но приложение не пускают.

Проверка. Посмотри лог приложения и проверь вход с паролем из DATABASE_URL. pg_isready отвечает только на вопрос «сервер принимает соединения», пароль он не проверяет. Пароль проверяет настоящий вход по сетевому адресу, как у приложения. Адрес 127.0.0.1 для этого не годится (внутри контейнера он идёт без пароля, см. теорию), поэтому берём имя db:

docker logs --tail 3 notes
docker exec db pg_isready -h 127.0.0.1 -U notes -d notes
docker exec db psql "postgresql://notes:${PGPASS}@db:5432/notes" -c 'SELECT 1'

В логе будет причина FATAL: password authentication failed for user "notes", pg_isready ответит accepting connections (сервер жив), а psql по адресу db выдаст ту же ошибку про пароль: проблема именно в нём. Разница в том, что процесс базы работает, а роль notes теперь имеет другой пароль.

Разбор. Пароль роли хранится в самой базе (в томе), а приложение берёт свой из переменной DATABASE_URL. Если значения разошлись (кто-то выполнил ALTER USER, или контейнер пересоздали с другим POSTGRES_PASSWORD на старом томе: переменная действует только при первой инициализации тома), база честно отвечает отказом. Заметь, что /healthz при этом отвечает 200: процесс приложения здоров, а /readyz 503: работать он не может. Так и должно быть.

Починка. Задай ролям тот пароль, что в приложении. Быстрый способ: bash /tmp/break-4.4.sh fix. Ручной: docker exec db psql -U notes -d notes -c "ALTER USER notes PASSWORD '...'" со своим PGPASS. Затем проверь curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1:8080/readyz (ожидаем 200).

Сценарий 2. Контейнеры живы, но приложение не находит базу

Симптом. bash /tmp/break-4.4.sh fix && bash /tmp/break-4.4.sh 2, затем curl -si http://127.0.0.1:8080/notes | head -1 даёт HTTP/1.1 500 Internal Server Error.

Гипотезы: сломан пароль (проверь лог); базы нет (docker ps покажет); контейнеры в разных сетях.

Проверка:

docker logs --tail 2 notes
docker network inspect notes-net --format '{{range .Containers}}{{.Name}} {{end}}'

В логе failed to resolve host 'db': [Errno -2] Name or service not known (это не «отказ», а «имя не найдено»: разница между DNS-ошибкой и Connection refused была в уроке 2.3). Во втором выводе останется одно имя notes, контейнера db в сети нет.

Разбор. Docker DNS даёт имя db только контейнерам, подключённым к одной пользовательской сети. Отключённый от сети контейнер работает, но для приложения его нет.

Починка: docker network connect --alias db notes-net db (или bash /tmp/break-4.4.sh fix). Проверка: /readyz возвращает 200.

Сценарий 3. Отчёт аналитика «вешает» базу

Симптом. bash /tmp/break-4.4.sh fix && bash /tmp/break-4.4.sh 3 создаст в базе lab таблицу events на 3 миллиона строк без индекса (кроме первичного ключа). В жалобе: «отчёт по одному пользователю идёт долго, а пока он считается, сервис тормозит». Приложение живо, /readyz отвечает 200. Отчёт это вот такой запрос: сколько событий каждого вида у пользователя 12345.

Гипотезы: мало памяти у контейнера; сеть; плохой запрос без индекса.

Проверка. Скрипт подготовил только данные. Воспроизведи жалобу и сразу смотри, как база выполняет запрос:

docker exec db psql -U notes -d lab -c \
  "EXPLAIN ANALYZE SELECT kind, count(*) FROM events WHERE user_id = 12345 GROUP BY kind;"

Из всего вывода нужны три строки, как в задании 3 (цифры у тебя будут другими, важны узлы плана):

 Parallel Seq Scan on events
   Filter: (user_id = 12345)
   Rows Removed by Filter: ...
 Execution Time: ... ms

Parallel Seq Scan и огромное число в Rows Removed by Filter значат: база прочитала почти все 3 миллиона строк ради нескольких десятков нужных. Один такой запрос сам по себе может быть недолгим, но если отчёт запускают десятки раз в минуту или таблица растёт, соединения и процессор заняты постоянно. Чтобы увидеть такой запрос «вживую», запусти отчёт в фоне и спроси у базы, чем она занята (pg_stat_activity был в задании 4):

docker exec -d db psql -U notes -d lab -c "SELECT pg_sleep(20), count(*) FROM events WHERE user_id = 12345"
docker exec db psql -U notes -d lab -c \
  "SELECT pid, state, now()-query_start AS runs, left(query,50) AS query FROM pg_stat_activity WHERE datname='lab' AND pid <> pg_backend_pid();"

Первая команда запускает запрос в фоне (-d у docker exec значит «не ждать»), pg_sleep(20) нарочно растягивает его на 20 секунд: это имитация долгого отчёта, чтобы он успел попасть в список. Во втором выводе будет строка вида (pid и время у тебя другие):

  pid  | state  |      runs       |                      query
-------+--------+-----------------+--------------------------------------------------
   142 | active | 00:00:07.412903 | SELECT pg_sleep(20), count(*) FROM events WHERE u

Разбор. Точно то же, что в задании 3, но масштаб больше, и поэтому пользователи чувствуют. Пока долгий запрос читает всю таблицу, он занимает соединение и процессор, а остальным запросам достаётся меньше ресурсов, и они замедляются. Паузу pg_sleep мы добавили только для демонстрации в pg_stat_activity: чинит именно индекс по user_id, и мерить надо тот же самый запрос без pg_sleep.

Починка. Индекс по условию из запроса: CREATE INDEX events_user_id_idx ON events (user_id);, затем повтори тот же EXPLAIN ANALYZE: узел станет Index Scan или Bitmap Heap Scan, а Execution Time заметно упадёт. Долгий запрос при необходимости останавливают SELECT pg_cancel_backend(<pid>);. В лаборатории хватает обычного CREATE INDEX. На боевой таблице пишут CREATE INDEX CONCURRENTLY: он строится дольше, его нельзя запускать внутри транзакции, а при сбое остаётся нерабочий индекс INVALID, который надо удалить. Уборка стенда: bash /tmp/break-4.4.sh fix (удалит events).

Сценарий 4. «Открой базу наружу», а порт занят

Симптом. bash /tmp/break-4.4.sh fix && bash /tmp/break-4.4.sh 4, затем попробуй команду, которую скрипт напечатал:

docker run -d --name db-ext -p 127.0.0.1:5432:5432 -e POSTGRES_PASSWORD=x postgres:18

Docker ответит ошибкой вида Bind for 127.0.0.1:5432 failed: port is already allocated. Порт 5432 на хосте занял контейнер break-4-4-port, который запустил break.sh (он просто спит и держит порт). Увидеть его можно так: docker ps --format '{{.Names}}: {{.Ports}}' покажет строку break-4-4-port: 127.0.0.1:5432->5432/tcp.

Гипотезы: порт занят другим контейнером; порт занят процессом на хосте; ошибка в самой команде.

Проверка:

docker ps --format '{{.Names}}: {{.Ports}}'
docker rm -fv db-ext 2>/dev/null

Вторая команда убирает созданный, но не запущенный контейнер db-ext (Docker создаёт его перед попыткой занять порт) вместе с его анонимным томом (-v; образ postgres объявляет VOLUME, и том создаётся вместе с контейнером). В списке видно, какой контейнер уже держит 127.0.0.1:5432. Если контейнеров с портом нет, ищи процесс на хосте (ss -ltn | grep 5432, урок 2.2; с sudo и ключом -p добавится имя процесса).

Разбор. Порт на одном адресе может слушать один процесс. Запомни и обратный вывод: базу наружу публиковать обычно не нужно, приложению хватает сети Docker. Если нужна разовая отладка, публикуют на 127.0.0.1, а не на все адреса.

Починка: остановить того, кто держит порт, или выбрать другой порт хоста (-p 127.0.0.1:5433:5432). Уборка: bash /tmp/break-4.4.sh fix.

Итог разделов. В сценариях 1 и 2 приложение живое, но не готово, и различить причины помогает лог и прямая проверка базы; порядок такой: /readyz и логи приложения, затем pg_isready, затем сеть, затем содержимое базы. В сценарии 3 приложение готово, но один запрос перегружает базу (смотри pg_stat_activity и EXPLAIN). В сценарии 4 приложение ни при чём: ошибка при публикации порта нового контейнера.

ИИ в помощь

Нейросеть хорошо объясняет SQL и читает планы запросов, но схему твоей базы она не видит и охотно выдумывает столбцы. Общие правила: ИИ-помощник.

Задача: написать и проверить запрос.

У меня PostgreSQL 18, таблица notes(id serial primary key, text text not null, created_at timestamptz not null default now()).
Напиши запрос, который вернёт 5 последних заметок, созданных за сутки.
Объясни каждую часть запроса. Не меняй таблицу и не используй других столбцов.

Проверь ответ: выполни запрос в psql внутри BEGIN, а затем ROLLBACK. Типичная ошибка нейросети: добавить столбец, которого нет в таблице, или забыть ORDER BY created_at DESC.

Задача: разобрать медленный запрос.

Вот запрос и вывод EXPLAIN ANALYZE: <вставь запрос и план>.
Таблица на миллион строк. Какой тип узла выполняется, сколько строк отброшено фильтром и почему запрос медленный?
Предложи индекс и объясни, при каких условиях он не поможет.

Проверь ответ: создай индекс и повтори EXPLAIN ANALYZE: должен появиться Index Scan. Типичная ошибка нейросети: советовать индекс для условия LIKE '%текст', которое B-tree не ускоряет.

Задача: проверить, нет ли в приложении SQL-инъекции.

Вот функция приложения, которая пишет заметку в PostgreSQL: <вставь код>.
Есть ли здесь SQL-инъекция? Покажи, как значения передаются в запрос, и как исправить.

Проверь ответ: убедись, что значения идут вторым аргументом execute, а не через f-строку или %. Попробуй отправить заметку x'); DROP TABLE notes; -- и проверь, что таблица цела.

Словарик урока

Термин Простыми словами
СУБД (DBMS) программа, которая хранит данные и отвечает на запросы (PostgreSQL)
Реляционная база данные в таблицах, связанных между собой по ключам
Таблица, строка, столбец таблица это набор однотипных записей: строка это одна запись, столбец это одно свойство
SQL язык запросов к реляционной базе
CRUD четыре действия: создать, прочитать, изменить, удалить (INSERT, SELECT, UPDATE, DELETE)
Первичный ключ (PRIMARY KEY) столбец, значение которого однозначно определяет строку
Внешний ключ (FOREIGN KEY) столбец, ссылающийся на строку другой таблицы: база не даёт вписать несуществующую ссылку
Ограничение (constraint) правило, которое база проверяет сама: NOT NULL, UNIQUE, REFERENCES
NULL «значения нет»: не ноль и не пустая строка
JOIN объединение строк двух таблиц по условию
Агрегат функция над группой строк (count, sum)
Транзакция группа команд, которая выполняется целиком или не выполняется вообще
ACID четыре свойства транзакции: атомарность, согласованность, изоляция, долговечность
WAL журнал, в который база записывает изменения до записи в файлы: помогает восстановиться после сбоя
Индекс отдельная структура для быстрого поиска, ускоряет чтение и замедляет запись
EXPLAIN команда, показывающая, как база собирается выполнить запрос (ANALYZE выполняет его и измеряет)
Seq Scan чтение всей таблицы подряд
Строка подключения (DATABASE_URL) адрес базы одной строкой: postgresql://пользователь:пароль@хост:порт/база
Параметризованный запрос запрос, в котором значения передаются отдельно от текста SQL: защита от SQL-инъекций
SQL-инъекция атака, при которой ввод пользователя становится частью SQL-команды
Liveness / readiness «процесс жив» и «сервис готов работать»: у нас /healthz и /readyz
Пул соединений набор заранее открытых соединений, которые переиспользуют
MVCC многоверсионность: база хранит рядом старую и новую версию строки, чтобы читатели не мешали писателям
Autovacuum фоновая уборка старых версий строк, которые уже никому не нужны
READ COMMITTED уровень изоляции по умолчанию: каждая команда видит то, что другие зафиксировали к её началу
Кластер (cluster) каталог с данными одного сервера PostgreSQL, внутри него может быть много баз
HAVING условие на группы после GROUP BY; WHERE отбирает строки до группировки
TRUNCATE опустошает таблицу целиком одной командой; в PostgreSQL откатывается внутри транзакции
Реплика (replica) второй сервер базы, который в реальном времени повторяет изменения первого
План запроса (query plan) описание того, как база будет искать данные; его показывает EXPLAIN
Планировщик (planner) часть PostgreSQL, которая выбирает самый дешёвый способ выполнить запрос
Сокет (socket) вход в программу изнутри той же машины, не по сети: psql внутри контейнера подключается к серверу так
B-tree вид индекса по умолчанию: отсортированные значения и уровни указателей, как оглавление
Index Scan узел плана: база нашла строку по индексу и не читала всю таблицу
Драйвер библиотека, через которую программа на Python общается с базой (у нас psycopg)
Соединение (connection) открытый канал между клиентом и сервером базы, по нему идут SQL-команды
Роль / пользователь учётная запись внутри PostgreSQL с набором прав (не то же, что пользователь Linux)
pg_isready утилита, проверяющая, принимает ли сервер подключения

Вопросы с собеседований

Раздел для повторения: ответь вслух, потом открой ответ. Вопросы с пометкой «часто» задают почти на каждом собеседовании по теме урока: начни с них. Короткие вопросы с пометкой «на скорость» тренируй на время: ответ за 30 секунд.

1. [middle] [часто] Что такое индекс в базе данных, как он ускоряет запросы и чем за это платишь?

Ответ

Индекс - отдельная структура (в PostgreSQL по умолчанию B-tree), по ней база находит строки без полного просмотра таблицы. Создаю его командой CREATE INDEX ON notes (user_id); на столбцы из WHERE, JOIN и ORDER BY. Платой становятся место на диске и замедление INSERT, UPDATE и DELETE, потому что индекс тоже обновляется. Эффект проверяю через EXPLAIN ANALYZE: ищу Index Scan вместо Seq Scan. На маленькой таблице планировщик может выбрать Seq Scan, и это нормально.

Что хотят услышать: структура для поиска без полного скана, B-tree, цена на запись и место, EXPLAIN ANALYZE, индексы не на всё подряд.

Красный флаг: «Индекс на каждый столбец, чтобы всё было быстро».

2. [junior] [часто] Что делает LEFT JOIN и чем он отличается от INNER JOIN?

Ответ

INNER JOIN оставляет только строки, у которых есть пара в обеих таблицах. LEFT JOIN сохраняет все строки левой таблицы, а для отсутствующих пар подставляет NULL (автор без постов остаётся в результате).

Что хотят услышать: пример с NULL и разница count(*) и count(колонка).

Красный флаг: «это одно и то же».

3. [middle] [часто] Расшифруй ACID.

Ответ

Atomicity (атомарность: всё или ничего), Consistency (согласованность: ограничения не нарушены до и после), Isolation (изоляция: параллельные транзакции не видят чужие незавершённые изменения; в PostgreSQL это даёт MVCC, многоверсионность, подробнее в вопросе о MVCC), Durability (долговечность: после COMMIT данные переживут сбой, за это отвечает журнал WAL).

Что хотят услышать: по одному примеру на букву, для изоляции MVCC и снимок данных.

Красный флаг: называют только слова без смысла.

4. [junior] [на скорость] Чем реляционная база отличается от хранения в файле?

Ответ

База даёт параллельный доступ, транзакции, ограничения целостности, индексы для быстрого поиска и язык запросов. Файл же нужно блокировать и читать целиком, а согласованность обеспечивает только код приложения.

Что хотят услышать: конкурентная запись, целостность, поиск без чтения всего.

Красный флаг: «база нужна, потому что так принято».

5. [junior] Что такое первичный и внешний ключ?

Ответ

Первичный ключ однозначно определяет строку таблицы. Внешний ключ это столбец, ссылающийся на первичный ключ другой таблицы, и база не позволит сослаться на несуществующую строку (в задании 2 это была ошибка violates foreign key constraint).

Что хотят услышать: уникальность и ссылочная целостность.

Красный флаг: путают ключ с индексом или паролем.

6. [junior] [на скорость] Что такое транзакция?

Ответ

Группа команд, выполняемая как одно целое: либо все применяются (COMMIT), либо ни одна (ROLLBACK). Классический пример: перевод денег, где списание и зачисление не должны разделиться.

Что хотят услышать: атомарность и пример.

Красный флаг: «просто несколько запросов подряд».

7. [middle] Запрос по столбцу стал медленным. Что делаешь?

Ответ

Смотрю EXPLAIN (ANALYZE): если Seq Scan и много Rows Removed by Filter, добавляю индекс по столбцу из WHERE и повторяю EXPLAIN. Учитываю цену индекса: место и замедление записи.

Что хотят услышать: измерение до и после, цена индекса, ANALYZE.

Красный флаг: «добавить индекс на все столбцы».

8. [middle] Почему индекс не помогает для LIKE '%777'?

Ответ

B-tree упорядочен по началу значения, поэтому по окончанию или подстроке (LIKE '%777') приходится проверять все значения. Даже поиск по началу (LIKE 'abc%') обычный B-tree ускоряет не всегда: при языковой сортировке базы (например, en_US.utf8) индекс для него не используется, нужен особый класс оператора text_pattern_ops. Для поиска по подстроке нужны другие виды индексов (в PostgreSQL они есть, отдельная тема) или другая схема данных.

Что хотят услышать: упорядоченность индекса.

Красный флаг: «индексы просто иногда не работают».

9. [middle] Что такое SQL-инъекция и как от неё защититься?

Ответ

Ввод пользователя, склеенный с текстом SQL, начинает исполняться как команда. Защита: параметризованные запросы (%s и значения отдельным аргументом), права минимально нужные, проверка входа.

Что хотят услышать: параметры отдельно от текста запроса.

Красный флаг: «экранирую кавычки руками».

10. [middle] Приложение не подключается к базе. Порядок диагностики?

Ответ

Лог приложения (точный текст ошибки), pg_isready на самой базе, сеть и DNS-имя (одна ли сеть, резолвится ли имя), пароль и DATABASE_URL, затем pg_stat_activity и max_connections. Тексты различают причины: failed to resolve host, Connection refused, password authentication failed.

Что хотят услышать: от симптома к слою, а не «перезапущу всё».

Красный флаг: сразу пересоздать том.

11. [junior] Чем WHERE отличается от HAVING?

Ответ

WHERE отбирает строки до группировки, HAVING отбирает уже сформированные группы, обычно по агрегатам. Например, авторов, у которых больше пяти заметок: SELECT author, count(*) FROM notes GROUP BY author HAVING count(*) > 5;. Условие по обычному столбцу лучше ставить в WHERE, так база обработает меньше строк. Агрегатные функции в WHERE писать нельзя.

Что хотят услышать: до и после группировки, агрегаты только в HAVING, фильтр в WHERE дешевле.

Красный флаг: Использовать HAVING вместо WHERE для любого условия.

12. [middle] Чем DELETE, TRUNCATE и DROP отличаются, и как не потерять данные случайно?

Ответ

DELETE удаляет строки по условию и оставляет таблицу. TRUNCATE быстро очищает таблицу целиком, структура остаётся. DROP TABLE убирает таблицу вместе со структурой. В PostgreSQL все три можно откатить внутри транзакции, пока она не закоммичена. Перед опасной командой открываю BEGIN, смотрю SELECT count(*) с тем же условием, после удаления сверяю число и только тогда COMMIT. DELETE без WHERE удаляет всё, поэтому условие проверяю дважды, а на проде сначала делаю бэкап.

Что хотят услышать: три команды и их масштаб, транзакция как страховка, DELETE без WHERE, бэкап.

Красный флаг: Выполнять DELETE на проде без WHERE и без транзакции.

Проверено на версиях

Настоящие выводы получены 2026-09-30 на PostgreSQL 18.6 (образ postgres:18, Debian 13, aarch64), psycopg 3.3.6, Python 3.13.15, Docker Engine 29.6.2 (в составе Docker Desktop). Скрипт break.sh проверен на этом же стенде, все четыре сценария и fix.

Позже дописаны и запуском не проверялись (нет Docker под рукой): пример HAVING и TRUNCATE в BEGIN/ROLLBACK, роль app с GRANT, повторный запуск dropdb --if-exists, поведение при монтировании тома в старый путь .../data, отчёт SELECT kind, count(*) ... WHERE user_id = 12345 в сценарии 3 и вход по имени db в сценарии 1. Они выводятся из устройства образа (в pg_hba.conf петля 127.0.0.1 идёт по trust, сетевые адреса по scram-sha-256) и документации PostgreSQL.

Не проверено: архитектура x86_64 (числа времени и размеров будут другими, планы те же), команда ss на ВМ Multipass, интерактивный psql (баннер и приглашение), скачивание файлов с raw.githubusercontent.com, Ubuntu 26.04.

Итог урока: ты умеешь

  • объяснить, чем база отличается от файла, и что такое таблица, строка, ключ
  • писать SELECT, INSERT, UPDATE, DELETE и JOIN с агрегатами
  • объяснить транзакцию, ROLLBACK и ACID
  • читать EXPLAIN ANALYZE, отличать Seq Scan от Index Scan и создавать индекс
  • запустить PostgreSQL 18 в контейнере с томом и сетью и проверить pg_isready
  • подключить приложение через DATABASE_URL, знать зачем параметры запроса
  • отличить liveness от readiness и диагностировать четыре типовые поломки

Дальше: Docker Compose и PostgreSQL.

Проверь себя

Короткий тест по уроку: 5 вопросов из банка в 30. Засчитывается только полностью правильный ответ, порог 60%. Каждая новая попытка даёт другие вопросы, пока банк не закончится. Ответы видны после проверки.

Тест работает с включённым JavaScript.

тема 4 урок 4.4 4 ч курс 0/0 ← → уроки

Помоги улучшить курс

Что нужно изменить?