PostgreSQL.
Разобраться
и объяснить.

Этот конспект связывает пять лекций с тем, что ты уже сделал в лабораторной № 8. Читай по порядку ниже, проговаривай ответы вслух и проверяй себя без подсказки. Цель подготовки: объяснить почему получился такой результат, а затем назвать механизм PostgreSQL.

Как пользоваться

Слева находятся главы и поиск. Кнопка «Изучено» сохраняет отметку на этом устройстве; это отметка чтения, а не оценка знаний. В разделе самопроверки сначала сформулируй ответ сам и только потом раскрывай объяснение. Если сегодня есть ещё 20–30 минут, подробно прочитай «Инструменты» и «Путь запроса».

Приоритеты: обязательно — все вопросы из задания; углубление — статусы транзакций и аномалии; прошлые темы — архитектура ИС. Дополнительные знания включены для подготовки, но выполнять «Практику+» не требуется. Гарантировать список устных вопросов нельзя: здесь покрыты предоставленные материалы и задание.

Клиент, сервер, кластер и база — разные вещи

PostgreSQL — серверная СУБД. psql — отдельная клиентская программа: отправляет SQL серверу и показывает ответ. Закрыть psql — закончить клиентский сеанс, а не остановить сервер. SSH даёт доступ к командной строке удалённой машины; уже внутри неё ты запускаешь psql.

Кластер PostgreSQL — набор баз, обслуживаемых одним экземпляром сервера и объединённых общим каталогом данных. Здесь слово «кластер» не означает группу компьютеров. База содержит схемы, а схемы — таблицы и другие объекты. Одно обычное подключение работает с одной базой. Роли действуют на уровне кластера.

Установка и расширения

В лекции три варианта: готовые пакеты ОС, сборка из исходных кодов, облачный сервис. Пакеты обычно проще: интегрируют запуск с ОС и определяют расположение файлов. В учебной Ubuntu сервер уже установлен из пакетов. Расширение добавляет функциональность: типы, функции, операторы и другие возможности. Наличие файлов расширения в системе и его подключение командой CREATE EXTENSION в базе — разные шаги.

Где что лежит в нашей машине

Не путай пользователя ОС postgres, которому принадлежат файлы кластера, с ролью БД. В лабораторной ты входил в ОС и в psql как student. Права роли БД определяют, какие SQL-команды доступны; sudo относится к ОС.

pg_lsclusters
sudo pg_ctlcluster 16 main status
sudo pg_ctlcluster 16 main reload

reload перечитывает конфигурацию без остановки. restart останавливает и запускает сервер. Часть параметров можно менять на лету, часть требует перезапуска; проверяй context и pending_restart в pg_settings.

Как подключаться и читать приглашение

psql -d tools_overview -U student
# Для TCP-подключения явно задают также -h и -p:
psql -h localhost -p 5432 -U student -d tools_overview

Без параметров имя роли обычно берётся из имени пользователя ОС, а имя базы — из имени роли; локальное соединение обычно использует Unix-сокет. В виртуальной машине значения настроены так, что достаточно psql.

Шпаргалка по psql

\set topic 'MVCC'
\echo :topic
\unset topic

Переменные psql обрабатывает клиент. :name — подстановка, :'name' — SQL-литерал, :"name" — SQL-идентификатор. Команды запуска можно хранить в ~/.psqlrc. SQL завершают ;, команды с обратной косой чертой завершаются концом строки.

psql -d lab8_report -f /home/student/lab8/ddl.sql
psql -d lab8_report -c "SELECT * FROM books;"

work_mem: что именно ты менял

work_mem — базовый лимит памяти для отдельных операций сортировки и хеширования до использования временных файлов. Это не размер общего буферного кеша и не общий лимит на весь сервер. В запросе могут быть несколько таких операций, параллельные процессы и множество одновременно работающих сеансов; для хеширования учитывается также hash_mem_multiplier. Поэтому «чем больше, тем лучше» — плохое универсальное правило. Уточнение: документация PostgreSQL 16.

SHOW work_mem;
SELECT name, setting, unit, context, source, pending_restart
FROM pg_settings WHERE name = 'work_mem';

Четыре этапа

На разборе сервер понимает структуру SQL и обращается к системному каталогу: существует ли таблица, какие у столбцов типы. Переписывание преобразует дерево запроса по правилам, например раскрывает представления. Планировщик выбирает план по оценкам стоимости и статистике: читать всю таблицу или индекс, в каком порядке соединять данные. Исполнитель выполняет выбранный план. Каталог — метаданные, обычные таблицы — пользовательские данные; оба вида данных могут кешироваться.

Подготовленный оператор — не подготовленная транзакция

PREPARE get_book(integer) AS
    SELECT * FROM books WHERE id = $1;
EXECUTE get_book(2);
SELECT name, parameter_types, generic_plans, custom_plans
FROM pg_prepared_statements;
DEALLOCATE get_book;

PREPARE сохраняет подготовленный запрос для повторных вызовов в текущем сеансе. Значения параметров привязываются при EXECUTE. План может быть специализированным под значения либо общим; нельзя обещать, что любой запрос всегда планируется ровно один раз. Подготовка особенно полезна при повторении одной формы запроса. PREPARE TRANSACTION относится к двухфазной фиксации и решает другую задачу. Справочник PREPARE; PREPARE TRANSACTION.

Зачем нужен курсор

Курсор хранит состояние выполнения и позволяет получать результат порциями. Представь миллион строк: приложению может быть удобнее обрабатывать по тысяче, чем принимать весь результат разом. Но FETCH по одной строке через сеть добавляет много обменов. Размер порции — компромисс памяти и числа обращений.

BEGIN;
DECLARE book_cursor CURSOR FOR
    SELECT * FROM books ORDER BY id;
FETCH 2 FROM book_cursor;
FETCH 2 FROM book_cursor;
CLOSE book_cursor;
COMMIT;

В нашей таблице первая порция содержит две строки, вторая — одну, следующая была бы пустой. Обычный курсор закрывается при завершении транзакции; вариант WITH HOLD может пережить COMMIT. Курсор не является новой базой или новой транзакцией.

Процессы, память и пул соединений

Главный процесс postmaster принимает подключения и управляет серверными процессами. В обычной модели каждое клиентское соединение обслуживает отдельный backend. У backend есть локальная память: разобранные запросы, кеш метаданных, состояние курсоров и рабочая память операций. Общая память доступна нескольким процессам: буферный кеш, WAL-буферы, структуры блокировок. Фоновые процессы выполняют обслуживание независимо от отдельных запросов.

Много соединений — много процессов и затрат памяти. Пул соединений переиспользует серверные подключения для множества клиентских запросов. При пуле в режиме транзакций следующая транзакция клиента может попасть в другой backend: сеансовые настройки и сеансовые advisory locks требуют особого внимания. Пул не отменяет транзакции и не делает запросы автоматически корректными.

Страницы, кеш ОС и расширяемость

Данные таблиц и индексов хранятся в файлах страницами, обычно по 8 Кбайт. Запросу нужна строка, но в память читается целая страница. Кеш PostgreSQL работает поверх файловой системы: у ОС тоже есть кеш. Поэтому отсутствие страницы в shared_buffers ещё не доказывает физическое чтение с накопителя.

PostgreSQL расширяется типами данных, функциями, операторами, триггерами, методами индексов, процедурными языками и фоновыми worker-процессами. FDW (Foreign Data Wrapper) позволяет обращаться к внешним источникам через внешние таблицы. Не всё расширение — это отдельный сервер; многое встраивается в механизм запросов.

Одна осмысленная операция, несколько SQL-команд

Транзакция — логическая единица работы с БД: последовательность операций, которую фиксируют целиком или отменяют. Классическое определение лекции: переводит БД из одного согласованного состояния в другое. Согласованность означает соблюдение ограничений и бизнес-правил, а не просто отсутствие синтаксических ошибок.

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

BEGIN;
UPDATE accounts SET balance = balance - 1000
WHERE owner = 'Alice';
UPDATE accounts SET balance = balance + 1000
WHERE owner = 'Bob';
COMMIT;

BEGIN, COMMIT, ROLLBACK и autocommit

BEGIN начинает явную транзакцию. COMMIT фиксирует изменения. ROLLBACK отменяет незавершённую работу. В psql по умолчанию включён autocommit: отдельная успешно выполненная SQL-команда вне явного блока фиксируется автоматически. Это не значит, что она выполняется «без транзакции».

\echo :AUTOCOMMIT
BEGIN;
-- Здесь несколько команд в одной транзакции.
ROLLBACK;

В открытой явной транзакции ошибка обычно переводит её в состояние failed: последующие команды не выполняются до ROLLBACK либо ROLLBACK TO подходящей точки сохранения. В случае обрыва соединения незавершённая обычная транзакция отменяется. Если соединение оборвалось во время COMMIT и ответ не получен, клиент не всегда знает, успела ли фиксация произойти: результат нужно проверять, а не слепо повторять списание.

SAVEPOINT: отменить часть работы

BEGIN;
INSERT INTO books VALUES (10, 'Оставить');
SAVEPOINT sp;
INSERT INTO books VALUES (11, 'Отменить');
ROLLBACK TO sp;
RELEASE SAVEPOINT sp;
COMMIT;

Будет зафиксирована только строка 10. ROLLBACK TO не завершает внешнюю транзакцию и сохраняет саму указанную точку; RELEASE убирает точку сохранения, но не фиксирует транзакцию. Блокировки, полученные после точки сохранения, при откате к ней освобождаются. Savepoint не делает промежуточные данные видимыми другим сеансам.

DDL, DML и исключения

INSERT, UPDATE, DELETE изменяют данные; CREATE, ALTER, DROP меняют структуру объектов. SELECT читает данные (в разных учебных классификациях его относят к DML или DQL). В PostgreSQL большинство DDL также транзакционны: CREATE TABLE можно отменить. CREATE DATABASE и VACUUM — примеры команд, запрещённых в обычном явном блоке транзакции. Значения sequence, уже выданные nextval, при откате не возвращаются: пропуски в идентификаторах нормальны.

BEGIN;
CREATE TABLE rollback_example (id integer);
ROLLBACK;
-- Таблица rollback_example не останется.

Модели транзакций и двухфазная фиксация

В лекции модели различаются структурой, параллельностью и длительностью. Плоская транзакция имеет одну внешнюю границу. Точки сохранения дают частичный откат внутри неё. Цепочечная работа связывает несколько последовательных транзакций; распределённая работа охватывает несколько участников. Эти признаки не следует считать синонимами: цепочка не обязательно распределённая, а savepoint — не независимый COMMIT вложенной транзакции.

2PC (two-phase commit) нужен координатору для общего решения нескольких участников. Сначала каждый участник подготавливает свою транзакцию и гарантирует возможность последующего решения. Затем координатор сообщает: всем фиксировать или отменять. Подготовленная транзакция сохраняет ресурсы и блокировки, даже когда исходный сеанс уже закончился.

-- Только схема 2PC, не требуется выполнять для ЛР № 8.
BEGIN;
-- Изменения участника.
PREPARE TRANSACTION 'transfer_42';
-- Позднее, вне блока BEGIN, по решению координатора:
COMMIT PREPARED 'transfer_42';
-- Альтернатива: ROLLBACK PREPARED 'transfer_42';

Изоляция — набор гарантий, а не отключение параллельности

Несколько транзакций работают одновременно. Уровень изоляции определяет, какие последствия чужих изменений им разрешено наблюдать. Чем строже требования, тем больше может быть затрат и повторных выполнений. Сериализуемость означает, что результат успешно завершённых транзакций эквивалентен некоторому последовательному порядку; сервер при этом вполне может выполнять их параллельно.

Четыре явления на конкретных примерах

Матрица: стандарт SQL и PostgreSQL 16

Стандарт задаёт минимальные гарантии; реализация может быть строже. Команда с READ UNCOMMITTED в PostgreSQL допустима, но фактически даёт Read Committed. Поэтому фраза «этого уровня вообще нельзя указать» неточна. Repeatable Read в PostgreSQL предотвращает и фантомы. Сверка таблицы: документация PostgreSQL 16.

Когда возникает снимок

Read Committed используется по умолчанию: обычный SELECT видит снимок на начало своего оператора. Другая транзакция может зафиксироваться между двумя SELECT, и второй получит другой результат. COMMIT, произошедший посреди одного SELECT, не меняет его снимок в середине выполнения.

Repeatable Read: снимок создаётся при первом операторе, которому нужен снимок, после BEGIN, и используется до завершения транзакции. Важно уточнение: не обязательно в момент самой команды BEGIN. Транзакция видит собственные уже выполненные изменения; неизменный снимок не запрещает ей менять данные.

Почему Repeatable Read ещё не Serializable

Дежурят два врача: Анна и Борис. Правило: хотя бы один обязан остаться. Две RR-транзакции одновременно видят обоих дежурными. Первая снимает Анну, вторая снимает Бориса. Они меняют разные строки и могут обе зафиксироваться: дежурных не останется. Каждый снимок был согласован, а общий результат нарушил правило. Это write skew, пример аномалии сериализации.

При последовательной работе второй врач увидел бы, что остался один, и не ушёл бы. PostgreSQL Serializable добавляет обнаружение опасных зависимостей; одну из конфликтующих транзакций может завершить ошибкой сериализации. Приложение должно повторить всю транзакцию с новым чтением, а не только последнюю команду. RR тоже может выдать ошибку сериализации при попытке изменить строку, изменённую после его снимка.

Как можно читать строку, которую уже удалили

MVCC — Multiversion Concurrency Control, многоверсионное управление конкурентным доступом. Вместо требования всем читать единственную текущую копию сервер хранит несколько версий строки. Снимок выбирает подходящую версию. Поэтому обычный читатель и писатель обычно не ждут друг друга из-за блокировки строки.

INSERT создаёт версию. UPDATE в обычной модели создаёт новую версию и отмечает завершение жизни прежней; это не простая перезапись значения на месте. DELETE отмечает удаление версии, но не обязан немедленно убрать её байты. После COMMIT старая версия может ещё требоваться транзакции со старым снимком. Здесь и находится ответ на опыт с Repeatable Read.

xmin, xmax, xid и ctid

SELECT id, value, xmin, xmax, ctid FROM test_rows;

Системные столбцы не входят в SELECT *, но доступны явно. Они полезны для учебного разбора и диагностики, а не для замены первичного ключа или собственной бизнес-логики. Видимость определяют не только сравнения номеров, но и состояние транзакций, активность в момент снимка, собственные изменения и служебные признаки.

Снимок — не фотография таблицы

Снимок — компактная служебная информация: границы XID и список транзакций, ещё активных в момент создания снимка, плюс данные для правил видимости. Он не копирует все строки. Транзакция, начавшаяся раньше снимка, могла ещё не зафиксироваться; поэтому одного порогового номера недостаточно, нужен список активных. Если такая транзакция зафиксируется позднее, это не сделает её изменения видимыми старому RR-снимку.

Не путай xmin/xmax снимка с одноимёнными полями версии строки. Снимок задаёт границы и набор активных транзакций, а поля версии рассказывают её историю. Два связанных, но разных набора метаданных.

VACUUM и горизонт очистки

VACUUM освобождает для повторного использования место от версий, которые уже никому не нужны. Autovacuum запускает обслуживание автоматически. Старые версии нельзя убирать только на основании того, что DELETE сделал COMMIT: нужно учитывать самые старые ещё нужные снимки и другие удерживающие горизонт факторы. Долгая транзакция может мешать очистке и приводить к разрастанию таблиц.

Обычный VACUUM обычно не уменьшает файл таблицы до минимального размера: освобождённое пространство остаётся для повторной записи; свободный хвост файла иногда можно усечь. VACUUM FULL переписывает таблицу и требует сильной блокировки. ANALYZE собирает статистику для планировщика; это другая цель, хотя команды часто объединяют.

Из-за цикличности 32-битных XID очень старые версии должны быть безопасно «заморожены». Freeze позволяет не интерпретировать древний xid как будущий после оборота счётчика. Поэтому autovacuum нужен не только ради свободного места, но и для предотвращения проблем wraparound.

clog / pg_xact: два бита, большой смысл

Commit log, исторически clog, хранит статусы транзакций. В PostgreSQL 16 каталог называется PGDATA/pg_xact. На транзакцию отводится два бита статуса: in progress, committed, aborted, subcommitted. Страницы статусов хранятся в служебных файлах и буферизуются в общей памяти. Текущую активность сервер также отслеживает в оперативных структурах; флаги-подсказки в версиях помогают не перечитывать статус каждый раз.

Чтобы решить, видна ли версия с xmin = 120, недостаточно номера: нужно понять, зафиксировалась ли 120 и была ли она видима снимку. После ROLLBACK её версии становятся невидимыми остальным. Не требуется для каждой изменённой строки немедленно восстанавливать прежнее содержимое; физическая очистка происходит позднее. Это не обещание, что любой откат имеет нулевую стоимость: освобождение ресурсов тоже требует работы.

Зачем нужны блокировки при наличии MVCC

MVCC отвечает за видимость версий, блокировки — за допустимость одновременного доступа к ресурсу. Версии позволяют обычному SELECT читать старые данные параллельно UPDATE. Но два писателя не должны бесконтрольно менять одну строку, а DROP TABLE не должен уничтожить таблицу посреди чужого запроса.

Блокировка связана с ресурсом, владельцем и режимом доступа. Совместимые режимы можно держать одновременно; конфликтующие заставляют ждать или завершают попытку ошибкой при соответствующей настройке. На низком уровне используются общая память, примитивы синхронизации ОС и атомарные инструкции CPU. Не стоит смешивать короткие защёлки памяти с длительными SQL-блокировками объектов.

Табличные режимы: «row» в названии не значит блокировку строки

Примеры команд и матрица конфликтов уточнены по официальному справочнику. Для защиты прежде всего запомни: SELECT берёт ACCESS SHARE; изменение данных — ROW EXCLUSIVE; DROP требует ACCESS EXCLUSIVE. Два ROW EXCLUSIVE совместимы: само наличие табличной блокировки не запрещает менять разные строки.

Строковые блокировки

UPDATE и DELETE блокируют изменяемые строки от конфликтующих изменений. Обычный SELECT при этом читает подходящую версию и не ждёт завершения писателя из-за этой строковой блокировки. Но SELECT ... FOR UPDATE специально захватывает блокировку: он способен ждать и сам задерживать других писателей. Не говори «любой читатель никогда никого не блокирует»: SELECT также держит табличный ACCESS SHARE и мешает DROP.

BEGIN;
SELECT * FROM books WHERE id = 1 FOR UPDATE;
-- До COMMIT/ROLLBACK конкурентное изменение этой строки ждёт.
COMMIT;

Матрица конфликтов строковых режимов

«Конфликт» означает: разные транзакции не могут одновременно удерживать эти режимы на одной строке. Это отдельная таблица от режимов уровня таблицы.

DELETE берёт сильный режим FOR UPDATE. UPDATE обычно использует FOR NO KEY UPDATE, а при изменении некоторых ключевых столбцов — FOR UPDATE. Не всякий столбец с любым UNIQUE-индексом автоматически относится к защищаемому ключу: важна возможность использования соответствующего уникального индекса для внешнего ключа.

Как ограничить ожидание

BEGIN;
SET LOCAL lock_timeout = '500ms';
LOCK TABLE books IN ACCESS EXCLUSIVE MODE;
-- При конфликте ждём не более заданного времени.
ROLLBACK;

Когда блокировки освобождаются

Обычные транзакционные блокировки сохраняются до COMMIT или ROLLBACK, а не только до конца одного SQL-оператора. В autocommit эти моменты обычно совпадают. При ROLLBACK TO освобождаются блокировки, полученные после точки сохранения. При обрыве соединения обычная незавершённая транзакция отменяется и её блокировки снимаются. Для подготовленных 2PC-транзакций и сеансовых advisory locks правила иные.

Страницы и рекомендательные блокировки

Внутренние блокировки страниц и буферов защищают физические структуры при коротких операциях чтения, изменения, записи или перестроения. Они обычно снимаются сразу после операции, а не удерживаются до COMMIT. Приложение не управляет ими командой LOCK TABLE.

Advisory lock блокирует согласованный приложением числовой ключ: один bigint или пару integer. Это может обозначать обработку заказа, даже если строки заказа пока нет. Другие программы должны соблюдать тот же протокол; обычный UPDATE не начнёт сам уважать придуманную приложением блокировку.

Повторные захваты сеансового ключа одним сеансом учитываются: требуется соответствующее число unlock. Не вызывай блокирующую функцию в выражении с LIMIT, рассчитывая, что она выполнится только для итоговых строк: порядок вычисления может удивить. Сначала выдели набор ключей. Сеансовые advisory locks особенно опасны при пуле соединений в transaction mode: владение связано с реальным backend, а не с логическим клиентом.

Deadlock: цикл ожиданий

Обычное ожидание может закончиться чужим COMMIT. При взаимоблокировке участники ждут друг друга по циклу. PostgreSQL обнаруживает циклы собственных блокировок и прерывает одну из транзакций; приложение должно обработать ошибку и повторить операцию. Профилактика: одинаковый порядок захвата объектов, короткие транзакции, отсутствие ожидания действий пользователя внутри них. Зависимость, уходящая в приложение или внешнее соединение, не всегда полностью видна серверу.

Как посмотреть ожидание

SELECT pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

SELECT pid, locktype, mode, relation::regclass, granted
FROM pg_locks WHERE NOT granted;

granted = false означает ожидаемую, а не полученную блокировку. Не любое wait_event — ожидание SQL-блокировки: бывает ожидание клиента, диска и внутренних структур. pg_cancel_backend прерывает текущий запрос, pg_terminate_backend завершает сеанс; это разные действия, доступные при соответствующих правах.

Почему данные сначала оказываются в памяти

Читать и менять данные постранично в оперативной памяти быстрее, чем каждый раз обращаться к накопителю. Буферный кеш PostgreSQL — общий массив буферов, содержащих страницы таблиц и индексов и служебные данные. Размер задаёт shared_buffers. Изменённая страница в кеше называется грязной: её содержимое расходится с файлом данных на диске.

Грязный буфер не равен грязному чтению. Первое — состояние страницы в памяти; второе — аномалия видимости чужой незавершённой транзакции. Грязная страница может содержать и зафиксированные, и ещё незавершённые изменения разных строк.

Чтение, вытеснение и EXPLAIN

Если нужная страница уже в shared_buffers, получаем попадание — hit. Если нет, её читают в буфер — read. При нехватке места выбирается буфер для повторного использования. Если он грязный, его предварительно безопасно записывают. В лекции это объясняется как вытеснение редко используемых страниц; для PostgreSQL точнее говорить о clock-sweep с учётом использования, а не о буквальном LRU-списке. Реализация clock-sweep в PostgreSQL 16.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM books;

EXPLAIN показывает план, а ANALYZE в его параметрах реально выполняет запрос. Buffers: shared hit — обращения к уже кешированным страницам; shared read — страницы, загруженные в shared_buffers, возможно из кеша ОС. Повторный запуск часто быстрее, но маленькая таблица books не обязана показать заметную разницу. Числа из лекции относятся к её тестовой таблице, не к любому запросу.

Что такое WAL и где он хранится

WAL — Write-Ahead Log, журнал предзаписи. Это логический поток записей об изменениях, достаточный для восстановления. Физически он хранится сегментами в PGDATA/pg_wal; обычный размер сегмента — 16 Мбайт, другой задают при инициализации. WAL не является просто списком исходных SQL-запросов и не просматривается обычным cat как текстовый журнал сообщений.

Каждая позиция в потоке имеет LSN (Log Sequence Number), 64-битное смещение, обычно записанное двумя шестнадцатеричными частями через «/». Разница LSN показывает объём потока в байтах, не количество транзакций. Уровни wal_level: minimal — достаточно для локального восстановления; replica — также для физической репликации и соответствующих резервных копий; logical — дополнительные данные для логического декодирования. Значение по умолчанию — replica.

SELECT pg_current_wal_lsn();
SELECT name, size FROM pg_ls_waldir() ORDER BY name LIMIT 5;

Два правила, которые нужно произнести точно

• Перед записью изменённой страницы данных соответствующие ей записи WAL должны быть надёжно сохранены. Иначе на диске окажется изменение, для которого нет данных восстановления.
• При обычной синхронной фиксации сервер подтверждает COMMIT после надёжного сохранения WAL до записи фиксации. Все изменённые страницы таблиц к этому моменту записывать не обязательно.

Это нормальный ответ на вопрос из задания: транзакция уже зафиксирована, а часть страниц остаётся в памяти; потерянное из памяти будет восстановлено по WAL. Страницы могут попадать на диск и до COMMIT: сама запись страницы не означает фиксацию всех транзакций на ней. Статусы и MVCC не дают читать отменённые изменения как зафиксированные.

Порядок предзаписи и восстановление REDO сверены по документации PostgreSQL 16. Схема показывает один возможный порядок; записывать страницу строго только после COMMIT сервер не обязан.

Кто записывает данные и журнал

Контрольная точка и объём WAL

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

Старые сегменты WAL можно удалить или переиспользовать, когда они больше не нужны. Но необходимость архивирования, репликации или replication slot может удерживать их. Нельзя считать max_wal_size жёстким пределом размера pg_wal или вручную удалять нужные сегменты. В лекции ограничение объёма checkpoint объяснено как общая идея, а не безусловная гарантия фиксированного размера.

Почему WAL выгоден и где предел гарантии

Последовательная запись WAL обычно дешевле немедленной синхронизации множества разрозненных страниц. Одна синхронизация может обслужить несколько фиксаций — group commit. WAL используется также для физической репликации, резервного копирования и восстановления на момент времени: для PITR нужны подходящая базовая копия и непрерывная цепочка WAL, а не только каталог журнала от текущего момента.

Обычная гарантия ответа COMMIT предполагает надёжное хранилище и настройки вроде fsync = on и synchronous_commit = on. При synchronous_commit = off успех может вернуться до надёжного сохранения соответствующего WAL: недавние транзакции могут потеряться при сбое. Изменения временных и UNLOGGED-таблиц не защищаются обычным WAL так же, как данные обычных таблиц; UNLOGGED после аварии очищаются. Из этого не следует, что внутренние метаданные вообще никогда не журналируются. Настройки надёжности WAL.

fast, immediate и smart

Сообщение invalid record length ... expected at least 24, got 0 в твоём журнале сопровождалось успешным завершением REDO и запуском: в данном контексте это достижение конца доступных записей WAL, а не доказательство повреждения. Оценивать похожую строку нужно вместе с последующими сообщениями.

Сначала раздели две разные «трёхуровневости»

ANSI-SPARC — уровни описания данных: внешний, концептуальный, внутренний. Трёхзвенная ИС — распределение функций между клиентом, сервером приложений и сервером БД. Первое отвечает «как описаны данные», второе — «где выполняются части приложения». Их нельзя подменять друг другом.

ANSI-SPARC и независимость данных

Физическая независимость: меняем хранение или добавляем индекс, сохраняя логическую схему и интерфейс приложения. Логическая независимость: меняем концептуальную схему, стараясь сохранить внешние представления и работу приложений, например через совместимое VIEW. Это цель разделения уровней, а не обещание, что любое изменение бесплатно и никогда не потребует правок.

Полнота, непротиворечивость и целостность

Полнота — данные достаточно описывают предметную область: например, для учёта задолженностей есть студент, дисциплина и результат. Минимальная избыточность уменьшает дублирование и расхождения. Непротиворечивость — сведения не конфликтуют друг с другом. Целостность обеспечивается ключами, ограничениями допустимых значений, ссылками, транзакциями и правильной конкурентной работой.

Уточнение лекции: не любое повторение значения запрещено — двух студентов могут звать одинаково. Дублирование запрещается там, где этого требует ключ или бизнес-правило. Ссылочную целостность обеспечивает внешний ключ; CASCADE — лишь один вариант реакции на изменение или удаление, а не обязательный способ. RESTRICT / NO ACTION тоже могут сохранять целостность.

Что такое информационная система

ИС объединяет хранение информации, прикладную обработку и представление результата пользователю. СУБД — важная часть ИС, но не всё приложение. Клиент и сервер — роли программ, а не обязательно разные физические компьютеры. Сервер приложений одновременно может быть клиентом сервера БД.

Распределение может быть фрагментацией: на узлах разные части данных; или репликацией: несколько согласуемых копий. Несколько клиентов одной централизованной БД ещё не делают БД распределённой. Данные на нескольких узлах сами по себе не гарантируют доступность при любом сбое.

Другие признаки классификации ИС

На схеме лекции 7 (стр. 13) перечислены и другие независимые основания классификации. Одна система может одновременно относиться к нескольким категориям. Примеры ниже добавлены для понимания: на самом слайде перечислены признаки, а не развёрнутые классы.

Файл-сервер и клиент-сервер

Два звена: где разместить прикладную логику

Выделим три функции: представление, прикладная обработка, управление данными. В двухзвенной модели их распределяют между клиентом и сервером. Терминология RDA/DBS ниже соответствует именно предоставленной лекции.

На практике функции RDA и DBS часто смешиваются: часть проверок в приложении, часть правил и процедур на сервере. «Толстый клиент» обычно содержит больше прикладной логики, «тонкий» — меньше; это характеристики распределения функций, а не размера окна или мощности видеокарты.

Три звена: AS-модель

Application Server: клиент занимается интерфейсом, сервер приложений — бизнес-логикой, сервер БД — хранением и запросами. Пример: браузер запрашивает ведомость у веб-приложения, оно проверяет права и отправляет SQL в PostgreSQL. Преимущество — независимое развитие и масштабирование слоёв; цена — дополнительный обмен, инфраструктура и сложность. Три звена можно разместить на одном компьютере: звено — логическая ответственность, узел — физическое размещение.

ODBC: совместимость через драйвер

ODBC (Open Database Connectivity) — стандартный интерфейс доступа к различным СУБД. Он помогает приложению использовать общий набор вызовов, а специфическое взаимодействие выполняет драйвер. Для PostgreSQL существует psqlODBC. ODBC не является сервером БД и не гарантирует одинаковые SQL-диалекты или поведение всех СУБД.

Это разбор уже выполненной работы, а не скрипт для безусловного повторного запуска. Базы и таблицы у тебя существуют: повторный CREATE без подготовки выдаст ошибку, повторный INSERT с тем же первичным ключом тоже. В учебных SQL ниже показан первоначальный порядок. На защите важнее объяснить результат, чем снова создать объекты.

Часть 1 · установка, управление и psql

SHOW work_mem;
ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();
\c tools_overview
SHOW work_mem;
ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();
\c tools_overview
SHOW work_mem;

На скриншоте: значение стало 8MB, после сброса вернулось 4MB. Сервер не перезапускался: для этого параметра было достаточно перечитать конфигурацию. Отдельное переподключение — способ проверить новое значение без прежних настроек сеанса.

CREATE TABLE books (
    id integer PRIMARY KEY,
    title text NOT NULL
);

INSERT INTO books (id, title) VALUES
    (1, 'Основы PostgreSQL'),
    (2, 'Транзакции'),
    (3, 'Журнал WAL');

\c lab8_report
\! cat /home/student/lab8/ddl.sql
\! cat /home/student/lab8/populate.sql
\i /home/student/lab8/ddl.sql
\i /home/student/lab8/populate.sql
\d books
SELECT * FROM books ORDER BY id;

Файлы находятся на виртуальной машине, где запущен psql. В lab8_report вывод был CREATE TABLE, INSERT 0 3 и три строки. «0» в INSERT 0 3 — историческое поле OID, а «3» — число добавленных строк. Внутри .sql нет служебных строк SQL от shell here-document: там только SQL-команды.

pg_lsclusters
sudo grep "^$(date +%F)" /var/log/postgresql/postgresql-16-main.log

pg_lsclusters показал 16 main, порт 5432, статус online, каталог данных и путь к журналу. grep выбрал строки за текущую дату виртуальной машины. В журнале были запуск, готовность к подключениям и плановая контрольная точка. Это текстовые сообщения, не содержимое pg_wal.

Часть 2 · два настоящих сеанса

Начальное состояние: в isolation_lab есть таблица test_rows(id integer PRIMARY KEY, value text) с одной зафиксированной строкой id = 1. Два отдельных psql — два подключения. Открыть две вкладки, но выполнять всё по очереди в одном подключении — не тот опыт.

Часть 3 · процессы и остановка

ps -ww -eo pid,user,args | grep -E '[p]ostgres.*(checkpointer|background writer|walwriter)'

Ты нашёл checkpointer, background writer и walwriter. PID — текущий номер процесса ОС: после перезапуска он меняется. Это не xid и не постоянный идентификатор роли процесса.

sudo pg_ctlcluster 16 main stop -m fast
sudo pg_ctlcluster 16 main start
pg_lsclusters
sudo tail -n 40 /var/log/postgresql/postgresql-16-main.log

received fast shutdown request
aborting any active transactions
checkpoint starting: shutdown immediate
checkpoint complete
database system is shut down
...
database system is ready to accept connections

sudo pg_ctlcluster 16 main stop -m immediate --skip-systemctl-redirect
sudo pg_ctlcluster 16 main start
pg_lsclusters
sudo tail -n 50 /var/log/postgresql/postgresql-16-main.log

received immediate shutdown request
...
database system was interrupted
automatic recovery in progress
redo starts at ...
redo done at ...
checkpoint starting: end-of-recovery immediate wait
checkpoint complete
database system is ready to accept connections

В immediate нет завершающей контрольной точки перед остановкой; checkpoint после восстановления этому не противоречит. Отсутствие большого числа изменённых страниц не отменяет механизм REDO: небольшой объём работы в журнале — нормальный результат.

Что именно показали твои скриншоты

Отвечай вслух до раскрытия. Хороший ответ: определение → механизм → пример из лабы → важное ограничение. Кнопки самооценки сохраняются локально и помогают вернуться к трудным вопросам; автоматической проверки правильности здесь нет.

Нет вопросов с отметкой «Повторить». Сними фильтр, чтобы пройти самопроверку.

Транзакции

Изоляция и MVCC

Блокировки

Кеш и WAL

Инструменты и запросы

Архитектура ИС

Пять фраз, на которых легко потерять точность

Номера ниже — страницы PDF, начиная с первой; они могут не совпадать с номерами, напечатанными на слайдах. Повторяющиеся слайды объединены. Устные объяснения и примеры в конспекте — учебный пересказ, а не дословные цитаты. Две лекции DEV прочитаны через распознавание изображений и визуальную проверку ключевых схем.

Где уточнены формулировки лекций

• Read Uncommitted — допустимое имя с поведением RC; RR в PostgreSQL предотвращает фантомы; снимок RR возникает при первом требующем его операторе.
• COMMIT не требует записи всех страниц таблиц. WAL не подменяет все механизмы ACID.
• SELECT защищает таблицу от несовместимого DDL; FOR UPDATE — особое чтение с блокировкой.
• Advisory locks бывают сеансовыми и транзакционными. Указанной на одном слайде функции pg_try_advisory_unlock не существует.
• NOWAIT не ждёт lock_timeout. deadlock_timeout — не предел ожидания.
• Ненулевой xmax не всегда означает удаление; статус и служебные флаги важны.
• Вытеснение в PostgreSQL 16 — clock-sweep, а не буквальный LRU.
• Звенья приложения не обязаны соответствовать разным компьютерам; каскад не является единственным способом сохранить ссылочную целостность.

Официальные источники для точных деталей

• PostgreSQL 16: уровни изоляции — различия стандарта и реализации.
• PostgreSQL 16: блокировки — режимы, конфликты и advisory locks.
• PostgreSQL 16: правило WAL — предзапись и REDO.
• PostgreSQL 16: параметры WAL — wal_level и синхронность фиксации.
• PostgreSQL 16: память — work_mem и shared_buffers.
• PostgreSQL 16: файлы кластера — pg_xact и pg_wal.
• PREPARE, PREPARE TRANSACTION, DECLARE — запросы, 2PC, курсоры.
• Исходный код PostgreSQL 16: freelist.c — clock-sweep при выборе буфера.

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