Разбор базы веб-приложения
К концу серии каталог стал больше, чем набор SELECT. У него есть ограничения данных, правила публикации, защита редакторской формы, поисковое представление, управляемое изменение схемы и границы обслуживания. Итоговый урок связывает эти части в одну модель, чтобы следующая функция не разрушила договор предыдущей.
Используем PostgreSQL 17.11. В архиве продолжения подготовлен самостоятельный снимок catalog-db-advanced/lesson-32: четыре темы, шесть курсов и двенадцать учебных уроков. В нём уже заполнена difficulty, сохранены свойства JSONB, история миграций содержит версии 1–3 и создан поисковый документ. SQL и клиентское приложение не запускались; реальный API здесь не развёрнут.
Публичная карточка курса
Пользователь открывает каталог и должен увидеть опубликованные курсы с темой, сложностью и краткой информацией об уроках. Начнём с одного результата на курс. Количество фактических строк уроков считаем отдельно до соединения:
WITH lesson_totals AS (
SELECT course_id, count(*) AS recorded_lessons,
count(body) AS lessons_with_text,
sum(reading_minutes) AS minutes
FROM catalog.lessons
GROUP BY course_id
)
SELECT c.id, c.slug, c.title, t.title AS topic,
c.difficulty, c.version, c.planned_lessons,
coalesce(l.recorded_lessons, 0) AS recorded_lessons,
coalesce(l.lessons_with_text, 0) AS lessons_with_text,
coalesce(l.minutes, 0) AS minutes
FROM catalog.courses AS c
JOIN catalog.topics AS t ON t.id = c.topic_id
LEFT JOIN lesson_totals AS l ON l.course_id = c.id
WHERE c.status = 'published'
ORDER BY c.published_at DESC, c.id DESC;Ожидаются пять карточек в порядке id 6, 5, 3, 2, 1. У PostgreSQL и Markdown одинаковый момент публикации, поэтому второй ключ определяет их порядок явно. У каждого опубликованного курса в снимке записано два урока с текстом. Черновой курс производительности не попадает в публичный набор, хотя его строки по-прежнему существуют.
planned_lessons остаётся редакционным планом. Оно не переименовывается в готовое число глав: JavaScript имеет план 20, а recorded_lessons равняется 2. Такое различие должно быть понятно также в названии полей API и подписи интерфейса. Сумма минут относится к записанным учебным урокам, а не к ещё не написанным будущим главам.
Предварительная агрегация предотвращает умножение карточек при соединении. Если позднее появятся теги и несколько авторов, нельзя просто присоединить все коллекции к тому же SELECT и считать COUNT(*) готовой статистикой уроков. Каждая связь должна иметь осмысленную кратность и собственное агрегирование или отдельный результат.
Граница схемы и JSONB
difficulty теперь обычная обязательная колонка с тремя допустимыми значениями. JSONB хранит дополнительные свойства и учебную копию старого level. В этом снимке уровни согласованы, но такую согласованность нельзя считать вечной автоматически. Приложению нужно знать, какой источник является главным после завершения миграции.
Для публичной карточки используется именно difficulty. Если старый клиент ещё пишет properties.level, переход не завершён на уровне приложения, даже когда история SQL содержит третью запись. Развёртывание клиентского договора и изменение схемы оценивают вместе: правило базы не переписывает код уже работающих редакторов.
Поле version относится к предметным изменениям карточки. История schema_migrations относится к переходам структуры. Пользователь, отправивший version 1, не сообщает «схема первой версии». Такое разделение позволяет обнаружить устаревшую форму независимо от того, сколько миграций выполнил администратор.
Перед новой функцией полезно сформулировать допустимое состояние. Например, публикация требует published_at, план положителен, slug уникален, lesson принадлежит существующему курсу. Если функцию нельзя выразить через эти правила без исключений, надо понять новое правило, а не обходить старое случайным JSON-флагом.
Правка и конкурентный результат
Редактор получает текущую карточку и version. Когда он сохраняет название или цену, короткая транзакция проверяет ожидаемую версию и увеличивает её. Если UPDATE возвращает ноль строк, клиент не объявляет успех: нужно отличить недоступный объект от конфликта редакций согласно договору приложения.
Представим, что два редактора открыли JavaScript с version 1. Первая правка сохраняет новое значение и version 2. Второе сохранение со старой версией должно дать контролируемый конфликт, а не тихо затереть первую работу. Повторное чтение и осознанное объединение изменений принадлежат интерфейсу; автоматическое повторение устаревшего UPDATE не исправляет намерение пользователя.
Блокировка FOR UPDATE подходит другой ситуации: короткая серверная операция должна прочитать и изменить связанное состояние в одной транзакции. Она не нужна для удержания карточки на всё время работы человека. Если операция меняет несколько курсов, единый порядок получения блокировок уменьшает риск цикла, а ограниченное повторение всей транзакции относится только к подходящим ошибкам.
В app-contract.md эти случаи оформлены как договор будущего сервиса, без запуска HTTP-сервера. Клиент получает значения отдельно от SQL, возвращает успех после COMMIT и не отправляет внешнее уведомление внутри автоматически повторяемой попытки. Работа с транзакциями в Psycopg описана в официальном руководстве.
Поиск и права
Поиск слова «каталог» использует отдельное языковое представление title и description. Ожидаются курсы HTML, JavaScript и PostgreSQL. Фильтр published остаётся частью запроса рядом с полнотекстовым условием. Индекс помогает выбранным операторам, но не принимает решение о разрешении доступа.
Поисковая карточка возвращает тот же предметный идентификатор и стабильный slug, что обычный каталог. Если результат поиска ведёт на случайно иной адрес или считает каждый урок отдельным курсом, проблема находится в договоре результата, а не исправляется сменой веса rank. Совпадение документа, ранжирование и маршрут ссылки нужно рассматривать отдельно.
Публичное соединение и административное соединение имеют разные обязанности. В итоговом reset кластерные роли не создаются автоматически: пример прав предыдущей главы требует отдельного решения владельца лаборатории. Эта граница предотвращает превращение восстановительного снимка данных в неявную настройку всего экземпляра.
Наблюдение вместо догадки
Когда каталог кажется медленным, нужно отделить ожидание свободного соединения, выполнение запроса и ожидание блокировки. Увеличение max_size пула без понимания причины может только добавить конкурирующую работу. Напротив, один долгий BEGIN способен удерживать ресурсы при небольшом числе карточек.
В observability.sql подготовлено чтение состояния соединений:
SELECT application_name, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = current_database();Доступность подробностей зависит от роли наблюдателя; отсутствие видимого текста чужого запроса не доказывает отсутствие деятельности. Поля и разрешения статистических представлений изложены в руководстве PostgreSQL. Мы не публикуем выдуманные задержки и число активных подключений: это данные будущего настоящего наблюдения.
Прикладной журнал может связывать идентификатор операции, код результата и длительность этапов, не записывая пароль DSN или содержимое приватной формы. Пользовательскому сообщению достаточно понятного исхода; служебной диагностике нужна причина. Смешивание этих уровней обычно делает и интерфейс, и разбор ошибки хуже.
Следующее изменение каталога
Перед дальнейшим расширением сохраните исходный смысл ключей и отделите новую возможность от масштаба примера. Разработка для двадцати тысяч материалов требует реальных измерений на подходящем наборе, но шесть курсов уже позволяют разобрать публикацию, пустые поля и конфликты без большого количества случайных данных.
Подготовленный маршрут резервного копирования заканчивается отдельной целью восстановления; факт его успешного выполнения ещё предстоит подтвердить вручную. Команды обслуживания также не считаются выполненными тестами. В редакционной заметке остаётся чёткая граница между авторским чтением SQL и фактической работой базы.
Итоговая модель пригодна для продолжения: публичное чтение имеет определённый результат, правка контролирует версию, изменение схемы учитывает работающих клиентов, а наблюдение и восстановление имеют самостоятельный договор. Следующий урок или сервис можно строить вокруг конкретного нового действия, сохраняя эти правила и объясняя каждое намеренное изменение.