Лекция 13. Базы данных

Базы данныхОт папок с CSV к общей базе группы: SQL на SQLite, индексы и планы, транзакции, Python и PostgreSQL, эксплуатация
Вячеслав Федоров
Лекция 13
Разработка и применение ПО
в физических исследованиях
SQL
Изложение. Тринадцатая лекция посвящена базам данных. Данные эксперимента — журнал запусков установки, измерения датчиков, архив параметров пучка — рано или поздно перерастают папки с файлами CSV: с ними одновременно работают несколько человек и программ, а вопросы к данным становятся сложнее перебора файлов. В рамках лекции рассматривается, зачем нужна система управления базами данных и чем различаются такие системы, реляционная модель и язык SQL на примере SQLite, индексы и планы запросов, транзакции и одновременный доступ, работа с базами из Python и, в конце, эксплуатация: резервные копии, репликация, мониторинг и выбор СУБД. Надпись на титуле — SQL, язык запросов, общий для большинства систем лекции.
Демонстрации. Слайды с розовым выделением слова «Демонстрация» отмечают места, где изложение прерывается для работы в терминале виртуальной машины lab с Python 3.12 и встроенным модулем sqlite3, pandas, SQLAlchemy и psycopg, далее называемой стендом: на слайде проигрывается запись тех же команд. PostgreSQL 17 запускается на стенде в контейнере Docker и принимает соединения только на петлевом интерфейсе 127.0.0.1. Базы SQLite создаются заново в каждой демонстрации скриптами с фиксированным начальным значением генератора случайных чисел, что обеспечивает воспроизводимость их содержимого на другой машине; различаются времена выполнения, номера транзакций PostgreSQL и размер сжатой копии.

СодержаниеОт папок с файлами к общей базе группы
01
Зачем СУБД
02
Модель и SQL
03
Индексы и планы
04
Транзакции
05
Python и базы
06
Эксплуатация и выбор
Изложение. Лекция состоит из шести частей. Первая объясняет, зачем нужна СУБД, какие бывают виды нагрузки и по каким признакам различаются системы. Вторая посвящена реляционной модели и языку SQL на примере SQLite. Третья — индексам, планировщику запросов и статистике. Четвёртая — транзакциям и одновременному доступу: уровням изоляции, блокировкам, журналу WAL и многоверсионности. Пятая — работе с базами из Python: модулю sqlite3 и драйверу psycopg, параметрам запросов, ORM SQLAlchemy и pandas. Шестая — эксплуатации и выбору: резервным копиям, восстановлению на момент времени, репликации, масштабированию, мониторингу и нереляционным хранилищам.

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

Сквозной пример:
журнал измерений
experiments
кампания: id, name,
setup, started_at
runs
заход: id, experiment_id,
run_number, energy_mev,
operator, started_at
measurements
точка: id, run_id,
channel, t, value
1
N
1
N
2 кампании, 41 заход (40 с измерениями), 5 каналов по 5000 точек, 100 Гц: 1 000 000 измерений
Для физика
В синтетическом журнале сигнал BPM задан пропорциональным энергии пучка: 0,02 на МэВ с шумом.
Пояснение
Тот же журнал проходит через SQLite, PostgreSQL, SQLAlchemy и pandas.
Изложение. Все примеры лекции работают с одним журналом измерений, схема которого взята из главы книги «Базы данных». Кампания измерений, например калибровка датчиков положения пучка, состоит из заходов, различающихся энергией пучка и оператором, а в каждом заходе с нескольких каналов снимаются точки — время и значение. Таким образом, журнал состоит из трёх таблиц, связанных отношением «один ко многим»: experiments, runs и measurements. Скрипт make_lab.py заполняет журнал синтетическими данными: две кампании, 41 заход, из которых последний оставлен без измерений, и пять каналов по 5000 точек с частотой 100 Гц в каждом из остальных сорока заходов, всего миллион строк. Сигнал датчиков положения BPM в модели задан пропорциональным энергии пучка с коэффициентом 0,02 на мегаэлектронвольт, к нему добавлены медленное колебание с амплитудой 0,01 и небольшой шум; у настоящего BPM сигнал отражает положение пучка, а от энергии зависит через дисперсию. Температура магнита медленно растёт, ток пучка экспоненциально спадает. Миллион строк для СУБД немного, но этого уже достаточно, чтобы увидеть разницу между полным просмотром таблицы и поиском по индексу.

Зачем СУБДПределы файлов, OLTP, OLAP и временные ряды, различия систем, встраиваемая и серверная архитектура
01
Зачем СУБД
Модель и SQL
Индексы и планы
Транзакции
Python и базы
Эксплуатация и выбор
Изложение. Первая часть посвящена тому, какие задачи берёт на себя система управления базами данных, какие бывают виды нагрузки, по каким признакам различаются системы и как они устроены: встраиваемая библиотека или отдельный сервер.

Файлы
и СУБД
01
Одновременный доступ
скрипт дописывает файл, анализ читает недописанную строку
02
Целостность
в столбце температуры «error», ссылка на удалённый заход
03
Поиск
ради одного канала одного захода читаются все файлы
04
Восстановление
сбой посреди записи оставляет файл испорченным

ЗадачаСредство СУБД
одновременный доступтранзакции, блокировки, версии строк
целостностьтипы, ключи, NOT NULL, UNIQUE
поискязык SQL и индексы
восстановлениежурнал WAL и резервные копии
Для физика
Папка 2026-08-20/ с двадцатью файлами и result_final_v2.csv — знакомая история данных установки.
Изложение. Хранение данных в лаборатории обычно начинается с файла result.csv; затем появляются result_final.csv, result_final_v2.csv и папки по датам с десятками файлов, названных по энергии пучка и фамилии оператора. Через год вопрос «все измерения канала BPM01 при энергии выше 10 МэВ» превращается в многочасовую работу со скриптом, обходящим папки. Файлы не справляются с четырьмя задачами. Одновременный доступ: скрипт сбора дописывает файл, а программа анализа в тот же момент читает недописанную строку, два писателя портят файл. Целостность: CSV не проверяет ни типов, ни ссылок, и в столбец температуры попадает строка «error». Поиск: чтобы найти точки одного канала одного захода, приходится читать все файлы целиком. Восстановление: сбой посреди записи оставляет файл в промежуточном состоянии. Система управления базами данных, СУБД, представляет собой программное обеспечение, которое берёт эти задачи на себя: транзакции, блокировки и версии строк разграничивают доступ, типы, ключи и ограничения проверяют данные при записи, язык SQL и индексы находят нужное, журнал упреждающей записи WAL и резервные копии восстанавливают базу. Кроме того, СУБД отделяет хранение от логики программ, что позволяет нескольким программам работать с одной базой. Запрос при этом описывает, что требуется найти, а не как перебирать файлы.

Задача	Средство СУБД
одновременный доступ	транзакции, блокировки, версии строк
целостность	типы, ключи, NOT NULL, UNIQUE
поиск	язык SQL и индексы
восстановление	журнал WAL и резервные копии

OLTP, OLAP
и временные ряды

НагрузкаЗапросыПримерТипичные СУБД
OLTPмного коротких транзакций, по нескольку строкзапись захода, оплата в магазинеPostgreSQL, MySQL, SQLite
OLAPредкие тяжёлые запросы по миллиардам строкстатистика архива пучка за сезонClickHouse, DuckDB
временные рядыпоток точек «время — значение», агрегаты по окнамвакуум, температуры, токи магнитовInfluxDB, TimescaleDB
Пояснение
Нагрузка задаёт устройство системы: строки или столбцы на диске, транзакции, способ вставки.
Для физика
Журнал запусков — OLTP, анализ архива за сезон — OLAP, медленный контроль ускорителя — временные ряды.
Изложение. Системы различаются прежде всего нагрузкой, под которую они построены. OLTP, обработка транзакций в реальном времени, представляет собой множество коротких операций, каждая из которых читает или меняет несколько строк: запись нового захода, оплата в интернет-магазине. Ответ нужен за миллисекунды, а данные должны оставаться согласованными при тысячах одновременных клиентов. OLAP, аналитическая обработка, — редкие тяжёлые запросы, которые просматривают миллионы и миллиарды строк ради нескольких чисел, например статистика параметров пучка за сезон; ответ допустимо ждать секунды. Временные ряды сочетают обе черты: непрерывный поток мелких вставок «метка времени — значение» и аналитические запросы по окнам времени, как в системе медленного контроля ускорителя, где сотни датчиков вакуума, температуры и токов опрашиваются каждую секунду годами. От профиля нагрузки зависит устройство системы: OLTP-базы хранят таблицу по строкам и поддерживают транзакции, аналитические хранят данные по столбцам, что ускоряет агрегацию по нескольким столбцам миллиардов строк. Использовать систему не по назначению можно, но дорого: аналитика на PostgreSQL ограничена скоростью полного чтения больших таблиц, а частые мелкие вставки в ClickHouse перегружают его фоновые слияния.

Нагрузка	Запросы	Пример	Типичные СУБД
OLTP	много коротких транзакций, по нескольку строк	запись захода, оплата в магазине	PostgreSQL, MySQL, SQLite
OLAP	редкие тяжёлые запросы по миллиардам строк	статистика архива пучка за сезон	ClickHouse, DuckDB
временные ряды	поток точек «время — значение», агрегаты по окнам	вакуум, температуры, токи магнитов	InfluxDB, TimescaleDB

Различия
СУБД

SQLitePostgreSQLClickHouseValkey, Redis
Объёмфайл до сотен ГБТБ на серверПБ на кластереобъём памяти
Нагрузкавстроенная OLTPOLTPOLAPключ — значение
ЯзыкSQLSQLдиалект SQLкоманды GET, SET, SCAN
ТранзакцииACID, сериализуемыеACID, уровни изоляцииатомарная вставка блокаMULTI/EXEC без отката
Репликациянет встроеннойфизическая и логическаятаблицы Replicated, Keeperасинхронная на реплики
Пояснение
SQLite не заменит кластер, а кластер ради сотни байт не разворачивают: систему выбирают по нагрузке и объёму.
Изложение. Вместо перечня сотен систем рассмотрим несколько признаков, по которым они различаются; таблица сравнивает четыре системы, которые часто встречаются вместе. Объём: SQLite хранит базу в одном файле и уместна до сотен гигабайт, PostgreSQL обслуживает терабайты на одном сервере, ClickHouse — петабайты на кластере, а Valkey и Redis ограничены оперативной памятью. Нагрузка: SQLite — встраиваемая OLTP-база, PostgreSQL — классическая OLTP, ClickHouse — классическая OLAP, Valkey — хранилище «ключ — значение» в памяти. Язык запросов: PostgreSQL почти полностью следует стандарту SQL, у ClickHouse свой диалект SQL, а у Valkey вместо языка — набор команд, и фильтрацию по значениям выполняет приложение. Транзакции: SQLite и PostgreSQL обеспечивают полный набор гарантий ACID; в ClickHouse привычных транзакций нет, но вставка блока строк в одну партицию таблицы семейства MergeTree атомарна; Valkey выполняет каждую команду атомарно, а группу команд MULTI/EXEC — подряд, без вклинивания чужих запросов, но без отката. Репликация: у SQLite встроенной нет — в поставке есть утилита sqlite3_rsync для разовой синхронизации копии, а непрерывную репликацию дают сторонние средства; PostgreSQL поддерживает физическую и логическую, ClickHouse реплицирует таблицы семейства Replicated с координацией через ClickHouse Keeper, Valkey передаёт изменения репликам асинхронно.

	SQLite	PostgreSQL	ClickHouse	Valkey, Redis
Объём	файл до сотен ГБ	ТБ на сервер	ПБ на кластере	объём памяти
Нагрузка	встроенная OLTP	OLTP	OLAP	ключ — значение
Язык	SQL	SQL	диалект SQL	команды GET, SET, SCAN
Транзакции	ACID, сериализуемые	ACID, уровни изоляции	атомарная вставка блока	MULTI/EXEC без отката
Репликация	нет встроенной	физическая и логическая	таблицы Replicated, Keeper	асинхронная на реплики

Встраиваемая
и клиент-серверная
SQLite — библиотека
процесс python3 daq.py
код программы
и модуль sqlite3
lab.db, lab.db-wal
файлы на локальном диске
PostgreSQL — сервер, процесс на соединение
psql
сервис FastAPI
скрипт анализа
процесс 1
процесс 2
процесс 3
общая память
буферы
страниц,
блокировки,
снимки
файлы таблиц и журнал WAL
Пояснение
Новое соединение с PostgreSQL — новый процесс; пул соединений, например PgBouncer, переиспользует их.
Для физика
SQLite — база одного исследователя и его скриптов; PostgreSQL — общая база группы с правами доступа.
Изложение. По архитектуре СУБД делятся на встраиваемые и клиент-серверные. SQLite является библиотекой, которая работает внутри процесса программы: обращение к базе — вызов функции, без сети и отдельного сервиса, а вся база — файл на локальном диске, рядом с которым в режиме WAL находится файл журнала. Поэтому SQLite не требует установки и настройки, но права доступа к ней — права на файл, а писать в базу одновременно могут только процессы одной машины. PostgreSQL представляет собой сервер: главный процесс принимает соединения на порту 5432 и для каждого клиента порождает отдельный процесс, а процессы координируются через общую память, где хранятся буферы страниц, блокировки и сведения о транзакциях. Модель «процесс на соединение» надёжна и прозрачна: утечка памяти в одном сеансе исчезает вместе с процессом, а зависший сеанс виден в списке процессов вместе с типом выполняемой команды; текст запроса показывает представление pg_stat_activity. Цена — дорогое соединение: открыть его заметно дороже, чем выполнить простой запрос, а один сервер выдерживает порядка тысяч одновременных соединений. Программы, которые открывают соединение на каждый запрос, ставят перед сервером пул соединений, например PgBouncer: в транзакционном режиме он выдаёт серверное соединение клиенту на время одной транзакции. ClickHouse обслуживает клиентов потоками из пула, а Valkey выполняет команды в одном главном потоке.

Модель и SQLТаблицы и ключи, нормализация, CREATE TABLE, выборка, соединение и агрегация на SQLite
02
Зачем СУБД
Модель и SQL
Индексы и планы
Транзакции
Python и базы
Эксплуатация и выбор
Изложение. Вторая часть посвящена реляционной модели и основам языка SQL на примере SQLite: таблицам и ключам, нормализации, созданию таблиц с ограничениями, выборке, соединению и агрегации.

Реляционная
модель
Таблица runs: строка — заход, столбец — атрибут

idexperiment_idrun_numberenergy_mev
1115,0
2125,5
3123120,0
4124125,0
Пояснение
Столбец id — первичный ключ, его выдаёт база; experiment_id — внешний ключ на experiments.id.
01
Таблица
отношение: строки — объекты, столбцы — атрибуты с типом
02
Первичный ключ
однозначно задаёт строку; обычно суррогатный id
03
Внешний ключ
ссылка на ключ другой таблицы, которую проверяет СУБД
04
Многие ко многим
промежуточная таблица с двумя внешними ключами
Изложение. Реляционная модель, предложенная Эдгаром Коддом в 1970 году, остаётся основной: данные хранятся в таблицах, которые в теории называются отношениями. Строка описывает один объект, например заход, а столбец — атрибут с фиксированным типом: номер захода, энергию пучка. Порядок строк в таблице не определён, и упорядоченными они приходят только при явной сортировке в запросе. Первичный ключ является столбцом или набором столбцов, однозначно задающим строку; обычно это суррогатный целочисленный id, который выдаёт сама база. Внешний ключ представляет собой ссылку на первичный ключ другой таблицы: столбец runs.experiment_id указывает на кампанию, и СУБД не позволяет записать заход несуществующей кампании или удалить кампанию, на которую ссылаются заходы. Связь «многие ко многим», например публикаций и авторов, выражается промежуточной таблицей с двумя внешними ключами.

id	experiment_id	run_number	energy_mev
1	1	1	5,0
2	1	2	5,5
31	2	31	20,0
41	2	41	25,0

Нормализация
Одна широкая таблица: кампания, энергия и оператор в каждой точке

runкампанияEоператорtvalue
31Ресурсные испытания20,0Петрова0,000,406
31Ресурсные испытания20,0Петрова0,010,403
31Ресурсные испытания20,0Петрова0,020,402
Важно
Опечатка в фамилии оператора — правка миллиона строк, а сбой на середине оставляет противоречие.
01
1НФ
атомарные значения, без повторяющихся групп
02
2НФ
неключевые столбцы зависят от всего ключа
03
3НФ
и только от ключа: энергия — свойство захода, а не точки
Пояснение
Денормализация — сознательное дублирование ради быстрой аналитики; начинают с нормализованной схемы.
Изложение. Проще всего хранить всё в одной широкой таблице, повторяя в каждой точке измерения название кампании, энергию пучка и фамилию оператора. Такая схема работает до первой правки: если в фамилии оператора опечатка, исправлять приходится миллион строк, а сбой на середине оставляет данные противоречивыми. Это называется аномалиями обновления. Нормализация устраняет дублирование: каждый факт хранится один раз, а остальные таблицы на него ссылаются. Формально её описывают нормальные формы. Для первой необходимо, чтобы значения были атомарными, без повторяющихся групп: не список каналов в одной ячейке, а отдельная строка на канал. Вторая и третья сводятся к правилу «каждый неключевой столбец зависит от ключа, от всего ключа и ни от чего, кроме ключа»: энергия — свойство захода и хранится в runs, название установки — свойство кампании и хранится в experiments. Обратный процесс, денормализация, тоже бывает оправдан: для тяжёлой аналитики собирают широкую таблицу, чтобы не соединять таблицы в каждом запросе, — в таком виде данные часто переносят в аналитическую СУБД. Но проектирование начинают с нормализованной схемы.

run	кампания	E	оператор	t	value
31	Ресурсные испытания	20,0	Петрова	0,00	0,406
31	Ресурсные испытания	20,0	Петрова	0,01	0,403
31	Ресурсные испытания	20,0	Петрова	0,02	0,402

CREATE TABLE
и ограничения
CREATE TABLE runs (
    id            INTEGER PRIMARY KEY,
    experiment_id INTEGER NOT NULL
                  REFERENCES experiments(id),
    run_number    INTEGER NOT NULL,
    energy_mev    REAL NOT NULL,
    operator      TEXT,
    started_at    TEXT NOT NULL,
    UNIQUE (experiment_id, run_number)
);
 
PRAGMA foreign_keys = ON;  -- в каждом соединении
01
PRIMARY KEY
уникален; INTEGER PRIMARY KEY — номер строки, ключ другого типа в SQLite допускает NULL
02
NOT NULL, UNIQUE
пропуск или повтор — ошибка записи
03
REFERENCES
внешний ключ; SQLite проверяет его после PRAGMA
04
STRICT
строгая проверка типов в SQLite с версии 3.37
Важно
Без PRAGMA foreign_keys = ON ссылка на несуществующую кампанию проходит без ошибки.
Изложение. Схема таблицы является одновременно документацией и проверкой, которую база выполняет при каждой записи независимо от кода программы. Первичный ключ уникален и по стандарту не может быть пустым; в SQLite столбец INTEGER PRIMARY KEY совпадает с внутренним номером строки rowid и заполняется автоматически, а первичный ключ другого типа из-за давней ошибки допускает NULL, если столбец не объявлен NOT NULL, а таблица — не STRICT и не WITHOUT ROWID. NOT NULL запрещает пропуск значения, UNIQUE — повтор; в примере номер захода уникален внутри кампании. REFERENCES объявляет внешний ключ. Особенность SQLite: внешние ключи проверяются, только если в соединении выполнена команда PRAGMA foreign_keys = ON, и включать её необходимо в каждом новом соединении; без неё ссылка на несуществующую кампанию принимается без ошибки. PostgreSQL и MySQL с движком InnoDB проверяют внешние ключи по умолчанию. Ещё одна особенность SQLite — динамическая типизация: в столбец REAL можно записать строку «error», и база сохранит её как текст; таблицы с ключевым словом STRICT, появившиеся в версии 3.37, обеспечивают строгую проверку типов. Нарушение ограничения завершается ошибкой записи, а не порчей данных, которую обнаруживают через полгода. Время в журнале установки хранят в UTC или, в PostgreSQL, типом timestamptz: строка местного времени без смещения при переводе часов и при обмене данными между площадками даёт повторы и пропуски меток.

SELECT
и JOIN
SELECT run_number, energy_mev
FROM runs
WHERE energy_mev > 10
ORDER BY started_at DESC
LIMIT 10;
 
SELECT e.name, r.run_number, m.t, m.value
FROM measurements AS m
JOIN runs AS r ON r.id = m.run_id
JOIN experiments AS e
    ON e.id = r.experiment_id
WHERE m.channel = 'BPM01';
Логический порядок выполнения:
01
FROM, JOIN
источники строк и условие соединения
02
WHERE
отбор строк
03
GROUP BY, HAVING
группы и отбор групп
04
SELECT
столбцы результата
05
ORDER BY, LIMIT
сортировка и ограничение
Пояснение
LEFT JOIN сохраняет строки левой таблицы без пары: так находят заходы без измерений.
Изложение. SQL является декларативным языком: запрос описывает, какой результат нужен, а как его получить, решает СУБД. Рассмотрим запросы на слайде. Первый выбирает десять последних заходов с энергией выше 10 МэВ: WHERE отбирает строки, ORDER BY с DESC сортирует по убыванию, LIMIT ограничивает результат. Второй запрос собирает разложенные по таблицам данные обратно: JOIN сопоставляет строки по условию, обычно «внешний ключ равен первичному», а псевдонимы AS m, AS r и AS e сокращают имена. Части запроса записываются в одном порядке, а логически выполняются в другом: сначала FROM и JOIN формируют строки, затем WHERE отбирает их, GROUP BY и HAVING группируют, SELECT вычисляет столбцы результата, ORDER BY и LIMIT сортируют и обрезают. Поэтому по стандарту псевдоним столбца из SELECT нельзя использовать в WHERE, и PostgreSQL на такой запрос ответит ошибкой. Кроме обычного, внутреннего JOIN, есть LEFT JOIN: он сохраняет строки левой таблицы и при отсутствии пары справа заполняет её столбцы значением NULL. В скриптах столбцы перечисляют явно, а не через звёздочку: это позволяет сохранить прежний состав результата при добавлении столбца в таблицу.

GROUP BY
и HAVING
SELECT r.run_number, r.energy_mev,
       COUNT(*)     AS n,
       AVG(m.value) AS mean
FROM measurements AS m
JOIN runs AS r ON r.id = m.run_id
WHERE m.channel = 'BPM01'
GROUP BY r.id
HAVING COUNT(*) >= 100
ORDER BY r.energy_mev;
01
Агрегаты
COUNT, SUM, AVG, MIN, MAX сворачивают группу в одно значение
02
GROUP BY
признак группы: заход, канал, час
03
WHERE и HAVING
до группировки — по строкам, после — по агрегатам
Для физика
Сводка по заходам считается в базе: в программу приходят 40 строк, а не миллион.
Изложение. Агрегатные функции COUNT, SUM, AVG, MIN и MAX сворачивают группу строк в одно значение, а GROUP BY задаёт признак группы. Запрос на слайде строит сводку по заходам: для каждого захода число точек канала BPM01 и средний сигнал. WHERE отбирает строки до группировки, а HAVING — группы после неё, по вычисленным агрегатам: условие COUNT(*) >= 100 отбрасывает слишком короткие заходы. По стандарту и в PostgreSQL в списке SELECT при группировке допустимы столбцы группировки, агрегаты и столбцы, однозначно определяемые первичным ключом группы; здесь r.run_number и r.energy_mev определяются ключом r.id, поэтому запрос корректен. Главное практическое следствие: агрегация выполняется там, где хранятся данные, и в программу на Python приходит готовая сводка из нескольких десятков строк вместо миллиона точек.

Демонстрация:
журнал измерений
01
make_lab.py: схема и миллион строк одной транзакцией
02
JOIN трёх таблиц: заход 31 с названием кампании
03
GROUP BY: средний сигнал BPM01 по заходам
04
LEFT JOIN: заход без измерений
Что наблюдается
Миллион строк — за 0,9 с, файл 33,4 МиБ; средний сигнал растёт с энергией: 0,47, 0,48, 0,49 при 23,5–24,5 МэВ; без измерений — заход 41.
root@lab — запись со стенда
Демонстрация. Скрипт make_lab.py создаёт файл lab.db по схеме главы «Базы данных» и заполняет его одной транзакцией: миллион измерений записывается за 0,9 с, файл занимает 33,4 МиБ. Скрипт sql.py выполняет запрос из файла и печатает строку с именами столбцов, взятыми из cursor.description, и строки результата кортежами, как их возвращает модуль sqlite3. Запрос join.sql соединяет три таблицы и показывает две точки канала BPM01 захода 31 вместе с названием кампании «Ресурсные испытания» и энергией 20,0 МэВ; в запросе нет ORDER BY, поэтому это первые строки в порядке хранения, t = 0 и 0,01 с, и такой порядок не гарантирован. Запрос group.sql строит сводку по заходам: в трёх заходах с наибольшей энергией, 24,5, 24,0 и 23,5 МэВ, по 5000 точек, а средний сигнал равен 0,49, 0,48 и 0,47 — он растёт на 0,01 на каждые 0,5 МэВ, как заложено в модели. Запрос orphan.sql с LEFT JOIN находит заход без измерений: у него нет пары в measurements, столбцы справа заполнены NULL, и условие m.id IS NULL оставляет только заход 41.

Индексы и планыB-дерево, план и время запроса, цена индекса, планировщик и статистика, EXPLAIN ANALYZE
03
Зачем СУБД
Модель и SQL
Индексы и планы
Транзакции
Python и базы
Эксплуатация и выбор
Изложение. Третья часть посвящена тому, как СУБД находит нужные строки: индексам на основе B-дерева, их цене, планировщику запросов и статистике, на которую он опирается, и чтению планов в SQLite и PostgreSQL.

Индекс:
B-дерево
корень: 14 · 28
5 · 9
18 · 23
32 · 36
1–4
5–8
9–13
…
…
лист 5–8: (7, 'BPM01') → rowid 150001 … 155000; ключи в листьях упорядочены
Индекс (run_id, channel): ключи упорядочены по run_id, затем по channel
Пояснение
Поиск — спуск от корня к листу: три-четыре страницы вместо просмотра миллиона строк.
Для физика
Индекс (run_id, channel) ускоряет поиск по run_id и по паре, по channel — лишь перебором run_id.
Изложение. Без индекса запрос с условием WHERE просматривает всю таблицу строку за строкой: это полный просмотр, SCAN в плане SQLite и Seq Scan в PostgreSQL. Индекс представляет собой отдельную структуру, обычно B-дерево, в которой значения индексируемых столбцов хранятся упорядоченными вместе со ссылками на строки таблицы: в SQLite — номером строки rowid, в PostgreSQL — адресом версии строки. Каждый узел дерева — страница размером в несколько килобайт с сотнями ключей, поэтому даже для миллиона строк дерево имеет три-четыре уровня, и поиск сводится к спуску от корня к листу. На схеме упрощённо показаны значения run_id: чтобы найти заход 7, достаточно прочитать корень, один внутренний узел и лист, где находятся номера строк 150001–155000 — точки канала BPM01 этого захода. Упорядоченные ключи в листьях позволяют читать диапазон значений подряд, от листа к соседнему, и выдавать данные уже отсортированными; в PostgreSQL соседние страницы каждого уровня к тому же связаны ссылками, а SQLite переходит к следующему листу через родительский узел. Индекс по нескольким столбцам упорядочен сначала по первому столбцу, затем по второму, как телефонный справочник по фамилии и имени: индекс (run_id, channel) ускоряет поиск по run_id и по паре run_id и channel, а по одному channel — только перебором значений run_id, пропуском (skip scan): SQLite выбирает его после ANALYZE, если значений первого столбца немного, PostgreSQL — с версии 18. Число чтений, таким образом, растёт как логарифм размера таблицы и потому почти не зависит от него.

Демонстрация:
индекс и план
01
Запрос: средний сигнал BPM01 захода 7
02
План и время без индекса
03
Индекс (run_id, channel) и размер файла
04
План и время с индексом; сортировка по t
Что наблюдается
Без индекса — SCAN за 19,5 мс, с индексом — SEARCH за 0,39 мс, в 50 раз быстрее; файл вырос с 34 до 50 МиБ; ORDER BY t требует временного B-дерева.
root@lab — запись со стенда
Демонстрация. Файл lab.db заново создан скриптом make_lab.py. Запрос q1.sql считает средний сигнал канала BPM01 захода 7. Скрипт plan.py печатает план запроса командой EXPLAIN QUERY PLAN и медиану времени пяти выполнений. Без индекса план — SCAN measurements, полный просмотр миллиона строк, и запрос выполняется за 19,5 мс. Оценим цену индекса: команда du показывает размер файла 34 МиБ до создания индекса idx_m_run_channel по столбцам run_id и channel и 50 МиБ после, то есть индекс занял около 16 МиБ. С индексом план меняется на SEARCH measurements USING INDEX, и тот же запрос выполняется за 0,39 мс, в 50 раз быстрее; результат, средний сигнал 0,16, не изменился. Запрос q2.sql выбирает 5000 точек того же канала, упорядоченных по времени: строки находятся по индексу, но план содержит строку USE TEMP B-TREE FOR ORDER BY — индекс не содержит столбца t, и для сортировки строится временное B-дерево; запрос занимает 3,3 мс.

Цена и выбор
индекса
01
Место
индекс (run_id, channel) — около 16 МиБ к 34 МиБ таблицы
02
Запись
каждая вставка обновляет все индексы таблицы
03
Селективность
условие на большую долю строк быстрее выполнить полным просмотром
04
Порядок столбцов
(run_id, channel, t) убирает и сортировку по t
-- точки канала сразу по порядку времени
CREATE INDEX idx_m_run_channel_t
ON measurements (run_id, channel, t);
 
-- он же покрывает внешний ключ run_id;
-- runs (experiment_id) покрыт индексом UNIQUE
Важно
Индекс на каждый столбец замедляет запись и раздувает базу: индексы строят под частые запросы.
Изложение. Индекс не бесплатен. Он занимает место: как мы видели в демонстрации, индекс по двум столбцам увеличил файл на 16 МиБ, почти наполовину. Каждая вставка, изменение и удаление обновляет все индексы таблицы, поэтому таблица с десятком индексов пишется заметно медленнее. Индекс полезен, когда условие отбирает малую долю строк; если же под условие попадает большая часть таблицы, чтение строк вразброс по индексу обходится дороже последовательного чтения всей таблицы, и планировщик справедливо выбирает полный просмотр. Порядок столбцов определяет, какие запросы ускоряются: индекс (run_id, channel, t) позволяет найти точки канала и сразу получить их упорядоченными по времени, без временного B-дерева. Индекс, который содержит все столбцы, нужные запросу, называется покрывающим: таблицу при таком запросе не читают вовсе. Первичный ключ и ограничение UNIQUE индексируются автоматически, а столбцы внешних ключей в SQLite и PostgreSQL индексируют вручную, если их не покрывает индекс, начинающийся с того же столбца: без индекса на measurements.run_id удаление захода при включённой проверке внешних ключей просматривает все измерения. В журнале этот ключ покрывает индекс (run_id, channel, t), а runs.experiment_id — индекс ограничения UNIQUE (experiment_id, run_number); MySQL создаёт индекс по внешнему ключу сам. Таким образом, индексы строят под реальные частые запросы, а их использование проверяют планом.

Планировщик
и статистика
01
Варианты
полный просмотр или индекс, способ и порядок соединений
02
Стоимость
страница подряд — 1, вразброс — 4
03
Селективность
доля строк под условием — по статистике таблицы
04
ANALYZE
собирает статистику; в PostgreSQL его запускает автоочистка
-- SQLite: строк в таблице и на значение
SELECT idx, stat FROM sqlite_stat1;
-- idx_city | 400001 200
 
-- PostgreSQL: частые значения столбца
SELECT most_common_vals, n_distinct
FROM pg_stats
WHERE tablename = 'users'
  AND attname = 'city';
Важно
После массовой загрузки статистика устаревает: ANALYZE выполняют сразу после неё.
Изложение. Один и тот же запрос можно выполнить многими способами: прочитать таблицу целиком или спуститься по индексу, соединить таблицы вложенным циклом, хешированием или слиянием, в том или ином порядке. Планировщик перебирает варианты и выбирает самый дешёвый по оценке стоимости в условных единицах: в PostgreSQL чтение страницы подряд стоит 1, чтение страницы вразброс — 4, обработка строки — 0,01; эти веса задаются параметрами seq_page_cost, random_page_cost и cpu_tuple_cost. Оценка зависит от селективности условия, то есть доли строк, которые оно отберёт, а её планировщик берёт из статистики: числа строк, числа различных значений, самых частых значений и гистограммы распределения. Статистику собирает команда ANALYZE. В PostgreSQL её запускает фоновая автоочистка, когда изменилась заметная доля таблицы; SQLite собирает статистику только по команде ANALYZE или PRAGMA optimize и хранит её в таблице sqlite_stat1, где для индекса записаны число строк и среднее число строк на одно значение. Когда данные меняются быстрее, чем обновляется статистика, например после массовой загрузки, план опирается на устаревшие сведения, и запрос, выполнявшийся за миллисекунды, начинает занимать секунды. По этой причине ANALYZE необходимо выполнять сразу после массовой загрузки. Подсказок планировщику в ядре PostgreSQL нет, их дают расширения, и относятся к ним настороженно: закреплённый план опирается на прошлые предположения о данных.

Демонстрация:
статистика ANALYZE
01
200 000 пользователей из Москвы, индекс по городу
02
Запрос без статистики и после ANALYZE
03
Ещё 200 000 жителей 2000 городов без ANALYZE
04
ANALYZE в том же и в новом соединении
Что наблюдается
Без статистики индекс медленнее просмотра: 11,7 мс против 8,1 мс; устаревшая статистика ведёт к SCAN ради одной строки — 13,7 мс; новое соединение — SEARCH за 0,01 мс.
root@lab — запись со стенда
Демонстрация. Скрипт stats.py воспроизводит в уменьшенном масштабе случай, когда план запроса ухудшился из-за устаревшей статистики. В таблице users 200 000 пользователей, и все они живут в Москве; по городу построен индекс idx_city, а запрос считает москвичей старше 30 лет. Без статистики SQLite считает индекс полезным и выбирает SEARCH по нему: 158 623 строки за 11,7 мс. ANALYZE записывает в sqlite_stat1 строку «200000 200000» — на одно значение города приходятся все строки, — и планировщик переходит к полному просмотру SCAN, который оказывается быстрее, 8,1 мс. Затем в таблицу добавляются 200 000 жителей двух тысяч других городов и один житель Новосибирска, а статистика не обновляется. Планировщик по-прежнему считает город неселективным и ради одной строки просматривает всю таблицу за 13,7 мс. Повторный ANALYZE записывает новую статистику «400001 200», то есть 200 строк на город, и соединение сразу перечитывает её, однако план в нём остался прежним — 14,2 мс. Причиной является особенность SQLite версий 3.45–3.49: первый ANALYZE, обнаружив в индексе единственное значение, пометил idx_city как непригодный для поиска по равенству, повторный ANALYZE эту отметку не снимает, и она сохраняется в соединении до нового чтения схемы; в SQLite 3.50 и новее повторный ANALYZE меняет план и в том же соединении. Новое соединение читает схему заново и выбирает индекс: та же строка находится за 0,01 мс вместо 13,7 мс.

EXPLAIN ANALYZE
в PostgreSQL
01
Дерево узлов
Seq Scan, Index Scan, Bitmap Heap Scan, Sort, Hash Join
02
Оценка и факт
rows против actual rows: многократное расхождение — устаревшая статистика
03
Buffers
страницы по 8 КиБ из кеша (hit) и с диска (read)
04
Execution Time
время на сервере без передачи результата
EXPLAIN (ANALYZE, BUFFERS)
SELECT avg(value) FROM measurements
WHERE run_id = 7 AND channel = 'BPM01';
Важно
EXPLAIN ANALYZE выполняет запрос: UPDATE и DELETE проверяют внутри BEGIN … ROLLBACK.
Пояснение
Sort Method: external merge Disk — сортировке не хватило work_mem (4 МБ на узел).
Изложение. В PostgreSQL план показывает команда EXPLAIN, а с ключом ANALYZE запрос ещё и выполняется, и к каждому узлу плана добавляются фактические числа. План представляет собой дерево узлов: нижние узлы читают таблицы полным просмотром Seq Scan, по индексу Index Scan или через битовую карту страниц Bitmap Heap Scan, узлы выше соединяют, сортируют и агрегируют; читают план от вложенных узлов к внешним. Главное в выводе — сравнение оценки планировщика rows с фактическим числом строк actual rows: многократное расхождение указывает на устаревшую статистику или на условие, которое планировщик оценить не может. Ключ BUFFERS показывает, сколько страниц по 8 КиБ прочитано из общего кеша (hit) и с диска (read); в PostgreSQL 18 он включён по умолчанию. Execution Time — время выполнения на сервере без передачи результата клиенту. Если в узле сортировки появляется строка Sort Method: external merge с объёмом на диске, сортировке не хватило памяти work_mem, по умолчанию 4 МБ на каждый узел сортировки, а хеш-таблице — вдвое больше (hash_mem_multiplier = 2), и запрос пишет временные файлы. EXPLAIN ANALYZE действительно выполняет запрос, поэтому изменяющие команды необходимо проверять внутри транзакции, которую затем откатывают.

Демонстрация:
EXPLAIN ANALYZE
01
Таблица на миллион строк в PostgreSQL
02
План без индекса: параллельный просмотр
03
CREATE INDEX по (run_id, channel)
04
План с индексом: Index Scan
Что наблюдается
Без индекса — Parallel Seq Scan по 7353 страницам за 20,2 мс; с индексом — Index Scan по 45 страницам за 0,52 мс, почти в 40 раз быстрее.
root@lab — запись со стенда
Демонстрация. PostgreSQL 17 работает на стенде в контейнере pg, а алиас psql запускает клиент внутри контейнера командой docker exec. Скрипт fill.sql создаёт таблицу measurements с первичным ключом и заполняет её миллионом строк, как и журнал SQLite, заход за заходом и канал за каналом, только каналы здесь идут по алфавиту; вставка занимает 2,5 с, затем ANALYZE собирает статистику. Запрос explain.sql выполняется с ключами ANALYZE и BUFFERS, а оценки стоимости и время каждого узла отключены ради краткости вывода. Без индекса план — параллельный полный просмотр: два дополнительных процесса, Workers Launched: 2, и основной разбирают страницы таблицы диапазонами по мере готовности, каждый отбрасывает в среднем 331 667 строк, всего из кеша прочитано 7353 страницы, и запрос выполняется за 20,2 мс. После создания индекса m_run_ch тот же запрос выполняется по плану Index Scan: прочитано 45 страниц, 38 из кеша и 7 с диска, а время выполнения — 0,52 мс, почти в 40 раз меньше. Отметим, что страниц прочитано в 160 раз меньше: точки одного канала одного захода расположены в таблице рядом, и индекс ведёт прямо к ним.

ТранзакцииACID и цена фиксации, уровни изоляции, блокировки и WAL в SQLite, MVCC в PostgreSQL
04
Зачем СУБД
Модель и SQL
Индексы и планы
Транзакции
Python и базы
Эксплуатация и выбор
Изложение. Четвёртая часть посвящена транзакциям и одновременному доступу: гарантиям ACID и цене фиксации, уровням изоляции, блокировкам и журналу WAL в SQLite и многоверсионности в PostgreSQL.

Транзакция
и ACID
def save_run(con, run_row, points):
    cur = con.cursor()
    try:
        cur.execute('INSERT INTO runs … '
                    'VALUES (?, ?, ?, ?)', run_row)
        run_id = cur.lastrowid
        cur.executemany('INSERT INTO measurements … '
                        'VALUES (?, ?, ?, ?)',
                        [(run_id, *p) for p in points])
        con.commit()     # всё
    except sqlite3.Error:
        con.rollback()   # или ничего
        raise
01
Atomicity
всё или ничего: сбой откатывает сделанное
02
Consistency
ограничения соблюдены до и после
03
Isolation
чужие промежуточные состояния не видны
04
Durability
после COMMIT данные переживут отключение питания
Пояснение
Конструкция with con: фиксирует при выходе из блока и откатывает при исключении, но соединение не закрывает.
Изложение. Транзакция представляет собой группу операций, которая выполняется как единое целое. Её гарантии описывает аббревиатура ACID. Атомарность: применяются либо все операции, либо ни одна, и сбой на середине откатывает сделанное. Согласованность: база переходит из одного корректного состояния в другое, не нарушая ограничений. Изолированность: параллельные транзакции не видят промежуточных состояний друг друга; насколько строго, задаёт уровень изоляции. Долговечность: подтверждённые командой COMMIT данные сохраняются даже при внезапном отключении питания, потому что до ответа клиенту изменения записаны в журнал на диске. Функция save_run из главы «Работа с базами данных» сохраняет заход и его точки одной транзакцией: заход без точек и точки без захода одинаково бессмысленны. Модуль sqlite3 открывает транзакцию неявно перед первой изменяющей командой, commit её фиксирует, rollback откатывает. Ту же пару обеспечивает конструкция with con:, которая фиксирует транзакцию при выходе из блока и откатывает при исключении, но соединение, в отличие от файла в конструкции with open, не закрывает. Долговечность требует времени: каждая фиксация ждёт, пока диск подтвердит запись.

Демонстрация:
откат и фиксация
01
save_run: заход 42 с двумя точками
02
Заход 43 с пустым значением во второй точке
03
with con: и повтор номера захода
04
1000 вставок: фиксация каждой и одна транзакция
Что наблюдается
Ошибка во второй точке откатывает и сам заход 43; фиксация каждой вставки — 776 мс, одна транзакция — 1,8 мс, в 430 раз быстрее.
root@lab — запись со стенда
Демонстрация. Скрипт tx.py работает с базой lab.db, где 41 заход и миллион измерений. Функция save_run из главы «Работа с базами данных» сохраняет заход 42 с двумя точками: счётчики становятся 42 и 1 000 002. Затем она получает заход 43, у второй точки которого значение пустое: первая точка вставляется, вторая нарушает ограничение NOT NULL, и rollback откатывает всю транзакцию — счётчики прежние, а самого захода 43 в таблице нет. Тот же результат даёт конструкция with con:, внутри которой заход 44 вставляется дважды: второй раз нарушает ограничение UNIQUE на паре кампании и номера захода, транзакция откатывается, а соединение остаётся открытым. Перейдём к цене фиксации: скрипт bulk.py вставляет 1000 строк четырьмя способами. Когда каждая вставка фиксируется отдельно, это занимает 776 мс: тысяча фиксаций — тысяча ожиданий подтверждения от диска виртуальной машины. Те же вставки в одной транзакции занимают 1,8 мс, через executemany — 1,6 мс, многострочным VALUES — 2,2 мс: важна не форма запроса, а число фиксаций. Таким образом, скрипт сбора данных записывает точки группами и фиксирует их раз в несколько секунд.

Уровни
изоляции

УровеньГрязное чтениеНеповторяемое чтениеФантомыАномалия сериализации
READ UNCOMMITTEDвозможновозможновозможнывозможна
READ COMMITTEDнетвозможновозможнывозможна
REPEATABLE READнетнетвозможнывозможна
SERIALIZABLEнетнетнетнет
Пояснение
По умолчанию: PostgreSQL — READ COMMITTED, MySQL InnoDB — REPEATABLE READ, SQLite — SERIALIZABLE.
Важно
На строгих уровнях PostgreSQL может прервать транзакцию ошибкой сериализации: её повторяют.
Изложение. Полная изоляция, при которой параллельные транзакции дают тот же результат, что и выполненные по очереди, дорога, поэтому стандарт SQL определяет четыре уровня изоляции через аномалии, допустимые на каждом из них. Рассмотрим их по порядку. Грязное чтение — транзакция видит незафиксированные изменения другой. Неповторяемое чтение — повторный запрос той же строки внутри транзакции возвращает другое значение, потому что другая транзакция успела её изменить и зафиксировать. Фантомы — повторный запрос по условию возвращает новые строки. Аномалия сериализации — результат параллельных транзакций невозможно получить ни при каком порядке их последовательного выполнения. Таблица показывает минимальные требования стандарта; реализации бывают строже. PostgreSQL по умолчанию работает на уровне READ COMMITTED, где каждый оператор видит данные, зафиксированные к его началу; на уровне REPEATABLE READ вся транзакция видит снимок на момент первого запроса, и фантомов в PostgreSQL на нём тоже нет, а READ UNCOMMITTED ведёт себя как READ COMMITTED. MySQL с движком InnoDB по умолчанию использует REPEATABLE READ, а SQLite обеспечивает сериализуемость, разрешая в каждый момент одну пишущую транзакцию. Строгие уровни требуют от приложения дополнительной логики: на уровнях REPEATABLE READ и SERIALIZABLE PostgreSQL может прервать транзакцию ошибкой сериализации, и её необходимо повторить целиком.

Уровень	Грязное чтение	Неповторяемое чтение	Фантомы	Аномалия сериализации
READ UNCOMMITTED	возможно	возможно	возможны	возможна
READ COMMITTED	нет	возможно	возможны	возможна
REPEATABLE READ	нет	нет	возможны	возможна
SERIALIZABLE	нет	нет	нет	нет

Блокировки
и WAL в SQLite
журнал DELETE
старые страницы — в lab.db-journal,
новые — в lab.db при фиксации
COMMIT ждёт ухода всех читателей
читатели и писатель блокируют друг друга
журнал WAL
новые страницы дописываются
в lab.db-wal
читатели видят снимок, писатель не ждёт
контрольная точка переносит страницы в lab.db
Пояснение
Писатель всегда один: второй ждёт timeout (в sqlite3 — 5 с) и получает database is locked.
Важно
WAL не работает на сетевом диске (NFS, SMB): все процессы должны быть на одной машине.
Изложение. SQLite разграничивает доступ блокировками всего файла базы. В режиме журнала по умолчанию, DELETE, транзакция перед изменением страницы копирует её прежнее содержимое в файл lab.db-journal, а новые данные пишет прямо в lab.db; при сбое прежние страницы восстанавливаются из журнала. Для фиксации транзакции писателю необходимо получить исключительную блокировку, и он ждёт, пока не закончатся все читающие транзакции; новые читатели, в свою очередь, ждут окончания фиксации. В режиме WAL, который включает команда PRAGMA journal_mode=WAL, новые версии страниц дописываются в файл lab.db-wal, а основной файл не меняется до контрольной точки, которая переносит страницы обратно. Читатель видит снимок базы на момент начала своей транзакции, что позволяет читать и писать одновременно: график строится, пока скрипт сбора пишет данные. Писатель в обоих режимах один: вторая пишущая транзакция ждёт освобождения блокировки время timeout, по умолчанию 5 с в модуле sqlite3, и затем получает ошибку database is locked; транзакцию, которая будет писать, начинают командой BEGIN IMMEDIATE, чтобы ждать блокировку сразу, а не посреди работы. Режим WAL запоминается в файле базы, но требует, чтобы все процессы работали на одной машине: на сетевом диске он неприменим.

Демонстрация:
блокировки и WAL
01
Два соединения: писатель A и читатель B
02
Запись B во время транзакции A
03
COMMIT A при открытом чтении B
04
Режим DELETE и режим WAL
Что наблюдается
Запись B ждёт 0,5 с и получает database is locked в обоих режимах; COMMIT при открытом чтении проходит только в WAL, а B до конца своей транзакции видит 2 строки.
root@lab — запись со стенда
Демонстрация. Скрипт locks.py открывает к одной базе два соединения с явным управлением транзакциями и временем ожидания блокировки 0,5 с: A пишет, B читает. Рассмотрим сначала режим DELETE. Соединение A начинает транзакцию BEGIN IMMEDIATE и вставляет строку: A видит две строки, B — одну, незафиксированная строка ему не видна. Попытка B вставить свою строку ждёт 0,5 с и завершается ошибкой database is locked: писатель может быть только один. После COMMIT в A соединение B видит две строки. Затем B открывает читающую транзакцию, A вставляет ещё строку, и его COMMIT через 0,5 с тоже получает database is locked — фиксации мешает открытое чтение. Транзакция A при этом не теряется: после COMMIT в B повторный COMMIT в A проходит, и B видит три строки. В режиме WAL первые шаги те же — второй писатель по-прежнему получает отказ, — но COMMIT при открытом чтении B проходит сразу, а B до конца своей транзакции видит снимок с двумя строками и лишь в новой транзакции видит три. Рядом с базой появляются файлы locks.db-wal и locks.db-shm.

MVCC
в PostgreSQL
страница таблицы runs
версия 1: energy = 5.0
xmin = 745, xmax = 746 (удалена в 746)
версия 2: energy = 5.5
xmin = 746, xmax = 0 (действующая)
снимок, взятый до фиксации 746
видит версию 1
снимок, взятый после фиксации 746
видит версию 2
Пояснение
UPDATE — новая версия и отметка xmax у старой; мёртвую версию убирает VACUUM, когда её не видит ни один снимок.
Важно
Долгая транзакция удерживает старый снимок: мёртвые версии накапливаются, таблица и индексы разрастаются.
Изложение. PostgreSQL изолирует транзакции не блокировками на чтение, а многоверсионностью, MVCC. UPDATE не меняет строку на месте: он записывает новую версию строки, а у старой отмечает в служебном поле xmax номер изменившей транзакции; у каждой версии в поле xmin записан номер создавшей её транзакции. Каждый запрос работает со снимком — сведениями о том, какие транзакции были зафиксированы к его началу, — и по xmin и xmax решает, какую из версий видеть. Такая схема обеспечивает независимость чтения и записи: читатели не ждут писателей, а писатели не ждут читателей; ждать друг друга приходится только транзакциям, изменяющим одну и ту же строку. Откат дёшев: транзакция лишь помечается прерванной, а её версии становятся невидимыми. Обратная сторона — мёртвые версии, которые не видит уже ни один снимок: их убирает VACUUM, обычно запускаемый автоматически. Долгая транзакция или забытый открытый сеанс удерживают старый снимок, VACUUM не может удалить версии, которые этот снимок ещё видит, и при частых изменениях таблица и индексы разрастаются, а простой запрос начинает читать тысячи мёртвых версий ради одной живой. Обычный VACUUM освобождает место внутри файла, а уменьшает файл, лишь когда освободились страницы в самом его конце; сжимает таблицу VACUUM FULL, переписывая её под исключительной блокировкой.

Демонстрация:
MVCC и изоляция
01
Сеанс A: строка energy = 5.0, затем UPDATE
02
Сеанс B на уровне READ COMMITTED
03
Сеанс B на уровне REPEATABLE READ
04
Свой UPDATE в B после UPDATE в A
Что наблюдается
READ COMMITTED: B видит новую версию, energy=5.5, и его UPDATE даёт 6,5; REPEATABLE READ: B видит старую версию с xmax=746, а его UPDATE — could not serialize access.
root@lab — запись со стенда
Демонстрация. Скрипт mvcc.py открывает к PostgreSQL в контейнере два соединения через драйвер psycopg, о котором подробнее говорится в пятой части. Сеанс A работает в режиме автофиксации: каждая его команда — отдельная транзакция. Он создаёт таблицу runs со строкой energy = 5.0, а сеанс B начинает транзакцию на заданном уровне изоляции и читает строку вместе со служебными полями xmin и xmax. На уровне READ COMMITTED B сначала видит версию с xmin=740; после того как A изменил строку и зафиксировал изменение, тот же запрос в той же транзакции B возвращает уже новую версию, xmin=741 и energy = 5.5; это и является неповторяемым чтением. Собственный UPDATE в B прибавляет единицу к последнему зафиксированному значению, и после COMMIT значение равно 6,5. На уровне REPEATABLE READ B после изменения в A продолжает видеть старую версию: xmin=745 и xmax=746, то есть версия уже удалена транзакцией 746, но снимок B был взят до её фиксации. Когда B пытается изменить эту строку, PostgreSQL отвечает ошибкой could not serialize access due to concurrent update: изменение по устаревшему снимку потеряло бы обновление из A, и транзакцию B необходимо повторить.

Python и базыDB-API: sqlite3 и psycopg, SQL-инъекции и параметры, ORM SQLAlchemy, pandas и форматы файлов
05
Зачем СУБД
Модель и SQL
Индексы и планы
Транзакции
Python и базы
Эксплуатация и выбор
Изложение. Пятая часть посвящена работе с базами из программ на Python: общему интерфейсу драйверов на примере sqlite3 и psycopg, параметрам запросов и SQL-инъекциям, ORM SQLAlchemy и связке баз данных с pandas.

DB-API:
sqlite3 и psycopg
import sqlite3
 
con = sqlite3.connect('lab.db')
with con:              # транзакция
    con.executemany(
        'INSERT INTO measurements'
        ' (run_id, channel, t, value)'
        ' VALUES (?, ?, ?, ?)', points)
rows = con.execute(
    'SELECT t, value FROM measurements'
    ' WHERE run_id = ?', (7,)).fetchall()
import psycopg
 
with psycopg.connect(dsn) as conn:
    rows = conn.execute(
        'SELECT t, value FROM measurements'
        ' WHERE run_id = %s', (7,)).fetchall()
    with conn.cursor().copy(
            'COPY measurements (run_id,'
            ' channel, t, value) FROM STDIN'
            ) as copy:
        for row in points:
            copy.write_row(row)
Пояснение
PEP 249: connect, execute, fetchall, commit одинаковы; различаются знаки параметров — ? и %s.
Важно
Соединение с PostgreSQL дорогое: сервис берёт его из пула (psycopg_pool), а не открывает на запрос.
Изложение. Драйверы баз данных в Python следуют общему интерфейсу DB-API, описанному в PEP 249: функция connect возвращает соединение, метод execute выполняет запрос с параметрами, fetchone и fetchall или итерация по курсору возвращают строки кортежами, commit и rollback управляют транзакцией. Общий интерфейс позволяет писать для SQLite и PostgreSQL почти одинаковый код. Различаются знаки параметров: модуль sqlite3 использует вопросительные знаки, драйвер PostgreSQL psycopg — %s. Различается и контекстный менеджер: with con: в sqlite3 только завершает транзакцию, а with psycopg.connect(...) as conn: фиксирует транзакцию и закрывает соединение. Для массовой загрузки в PostgreSQL вместо тысяч INSERT используют команду COPY, которой psycopg передаёт строки потоком: это самый быстрый способ загрузить данные. Соединение с сервером PostgreSQL — отдельный процесс, поэтому сервис открывает соединения не на каждый запрос, а берёт из пула, например psycopg_pool.ConnectionPool. Третья версия psycopg является заменой psycopg2 для нового кода: она умеет работать асинхронно, передаёт параметры серверу отдельно от текста запроса и поддерживает конвейерную отправку команд.

SQL-инъекция
и параметры
channel = input('Канал: ')
 
# так нельзя: запрос склеен с чужим текстом
cur.execute('SELECT t, value FROM measurements '
            f"WHERE channel = '{channel}'")
# ввод: ' OR '1'='1
# условие: channel = '' OR '1'='1' — все строки
 
# правильно: текст запроса и данные раздельно
cur.execute('SELECT t, value FROM measurements '
            'WHERE channel = ?', (channel,))
01
Всегда параметры
и для своих данных, и для чисел
02
Имена — не параметры
таблицу и столбец берут из белого списка
03
executescript
выполнит все операторы склеенной строки
04
Права
сервис работает от роли без права на DROP
Изложение. Когда часть запроса приходит извне — из формы веб-страницы, аргумента командной строки или присланного файла, — склеивать её с текстом запроса нельзя. Покажем это на примере: если в f-строку подставить ввод ' OR '1'='1, условие превращается в channel = '' OR '1'='1', которое истинно для каждой строки, и запрос возвращает всю таблицу; ввод с точкой с запятой и командой DROP TABLE в драйвере, исполняющем несколько операторов подряд, уничтожит таблицу. Такая атака называется SQL-инъекцией и является одной из самых старых и до сих пор распространённых уязвимостей; её классическая иллюстрация — комикс xkcd 327 о школьнике по имени Bobby Tables. Параметризованный запрос неуязвим по построению: текст запроса и значения передаются драйверу раздельно, и введённая строка остаётся строкой при любом содержимом. Значения передают только параметрами, даже собственные. Имя таблицы или столбца параметром быть не может; если его выбирает пользователь, допустимые имена проверяют по белому списку. Дополнительную защиту обеспечивают права: сервис подключается к базе от роли, которой разрешены только нужные операции с нужными таблицами.

Демонстрация:
SQL-инъекция
01
Обычный ввод BPM01
02
Ввод ' OR '1'='1
03
Ввод с DROP TABLE через execute
04
Тот же ввод через executescript
Что наблюдается
' OR '1'='1 — склейка отдаёт все 1 000 000 строк, параметр — 0; execute отказывается выполнять второй оператор, а executescript удаляет measurements.
root@lab — запись со стенда
Демонстрация. Скрипт inject.py получает имя канала аргументом командной строки и считает его точки двумя способами: запросом, склеенным f-строкой, и запросом с параметром. Для обычного ввода BPM01 оба способа возвращают 200 000 строк. Ввод ' OR '1'='1 превращает склеенный запрос в условие, истинное для всех строк, и он возвращает 1 000 000 — все измерения всех каналов; параметризованный запрос ищет канал с именем ' OR '1'='1 и находит 0 строк. Ввод с точкой с запятой и командой DROP TABLE в методе execute не срабатывает: модуль sqlite3 отказывается выполнять больше одного оператора и выдаёт ProgrammingError. Это свойство драйвера, а не защита: тот же ввод, переданный в executescript, который выполняет операторы подряд, удаляет таблицу measurements, и в базе остаются только experiments и runs.

ORM
SQLAlchemy 2.x
class Run(Base):
    __tablename__ = 'runs'
    id: Mapped[int] = mapped_column(
        primary_key=True)
    energy_mev: Mapped[float]
    measurements: Mapped[list['Measurement']] = \
        relationship(back_populates='run')
 
engine = create_engine('sqlite:///orm.db')
stmt = (select(Measurement.channel, Run.energy_mev)
        .join(Run).where(Run.energy_mev > 10.0))
for channel, energy in session.execute(stmt):
    print(channel, energy)
01
Классы и связи
таблицы — классы, строки — объекты, ключи — атрибуты
02
Тот же SQL
запрос строится выражениями; echo=True печатает SQL
03
N+1
связь в цикле — запрос на объект; selectinload — два запроса
04
Миграции
изменения схемы оформляет Alembic
Пояснение
PostgreSQL: create_engine('postgresql+psycopg://…') и установленный драйвер psycopg.
Изложение. ORM, объектно-реляционное отображение, связывает таблицы с классами, строки с объектами, а внешние ключи — с атрибутами-связями; стандартом в Python является SQLAlchemy. Во второй версии модели описываются аннотациями Mapped и функцией mapped_column, запросы строятся функцией select, а выполняются в сеансе Session, который отслеживает изменённые объекты и при commit превращает их в INSERT и UPDATE. Смена SQLite на PostgreSQL сводится к другому адресу базы в create_engine и установленному драйверу psycopg; для простых моделей остальной код не меняется, а различия типов и поведения проявляются на сложных схемах. ORM генерирует обычный SQL, и параметр echo=True выводит его в журнал. Типичная ошибка — проблема N+1: обращение к связи run.measurements в цикле по заходам выполняет отдельный запрос для каждого захода; опция selectinload позволяет загрузить связанные объекты одним дополнительным запросом. Та же ошибка возникает при обращении к m.run в цикле по точкам: JOIN в select служит только отбору строк и связь не заполняет, поэтому запрос на слайде выбирает сразу нужные столбцы обеих таблиц. Изменения схемы в проекте с ORM оформляют миграциями Alembic. ORM оправдан в приложении с десятком связанных таблиц и типовыми операциями создания, чтения, изменения и удаления, а аналитику с агрегатами и оконными функциями короче и быстрее писать на SQL.

Демонстрация:
ORM и запрос N+1
01
Модели Run и Measurement, три захода
02
SQL, построенный select(), и результат
03
Ленивая загрузка и selectinload
04
Тот же скрипт с адресом PostgreSQL
Что наблюдается
Один код для SQLite и PostgreSQL: SQL различается только записью параметра, ? и %(energy_mev_1)s; ленивая загрузка — 4 запроса, selectinload — 2.
root@lab — запись со стенда
Демонстрация. Скрипт orm.py описывает модели Run и Measurement, как в главе «Работа с базами данных», создаёт по ним таблицы и сохраняет через сеанс три захода с двумя точками в каждом; адрес базы скрипт получает аргументом. Для SQLite запрос select по классам превращается в SQL с JOIN и условием WHERE runs.energy_mev > ?, где вопросительный знак — параметр драйвера pysqlite, то есть модуля sqlite3. Результат — 4 строки, первая из них ('BPM01', 0.375, 12.5). Затем скрипт считает запросы, отправленные в базу: при ленивой загрузке обращение к связи measurements у каждого из трёх заходов выполняет отдельный запрос, всего 4 — один за заходами и три за их точками; с опцией selectinload запросов 2 — заходы и затем все их точки одним запросом с условием IN. Тот же скрипт с адресом postgresql+psycopg работает с PostgreSQL в контейнере без единой правки кода: текст SQL тот же, отличается только запись параметра, %(energy_mev_1)s в стиле psycopg, а результат и число запросов совпадают.

pandas:
read_sql и to_sql
df = pd.read_sql(
    '''SELECT r.energy_mev,
              AVG(m.value) AS mean_signal
       FROM measurements AS m
       JOIN runs AS r ON r.id = m.run_id
       WHERE m.channel = 'BPM01'
       GROUP BY r.id''', con)
k, b = np.polyfit(df.energy_mev,
                  df.mean_signal, 1)
df.to_sql('run_summary', con,
          if_exists='replace', index=False)

ФорматДля чего
CSVобмен с любыми программами
SQLiteжурнал: запись по строке, выборка по индексу
Parquetархив для анализа: столбцы, сжатие
Важно
Таблицу to_sql создаёт без ключей и ограничений: схему постоянных данных пишут вручную.
Изложение. Функция read_sql выполняет запрос и возвращает DataFrame, to_sql записывает DataFrame в таблицу. Тяжёлую фильтрацию и агрегацию отдают базе: в примере в pandas приходят 40 строк сводки вместо миллиона точек, а дальше работают обычные средства — подгонка прямой методом наименьших квадратов, график, сохранение результата. Функция read_sql принимает соединение sqlite3 или объект SQLAlchemy; для PostgreSQL используют create_engine с адресом postgresql+psycopg. Функция to_sql создаёт таблицу сама, но без первичного и внешних ключей и ограничений, поэтому для постоянных данных схему необходимо описать вручную, и тогда to_sql лишь дописывает строки в готовую таблицу. Формат хранения выбирают по способу доступа. CSV — формат обмена: его читает любая программа, но он хранит текст, а не типы, и для выборки одного канала читается целиком. SQLite подходит для журнала, который пополняется по записи и читается выборочно по индексу. Parquet хранит таблицу по столбцам со сжатием и статистикой групп строк, поэтому быстро читается целиком и при фильтре пропускает группы строк, где нужного значения заведомо нет, — это формат архива для анализа. Строка на каждый отсчёт приемлема для медленных каналов журнала, но не для осциллограмм и изображений с АЦП на мегагерцах: такие массивы хранят в файлах HDF5 или Parquet либо массивом в одной строке, а база содержит метаданные и путь к файлу. SQL-запросы прямо к файлам Parquet и CSV, без загрузки в базу, выполняет встраиваемая аналитическая СУБД DuckDB.

Формат	Для чего
CSV	обмен с любыми программами
SQLite	журнал: запись по строке, выборка по индексу
Parquet	архив для анализа: столбцы, сжатие

Демонстрация:
pandas и Parquet
01
read_sql: сводка по заходам в DataFrame
02
Прямая через точки, to_sql и схема
03
Миллион строк в CSV, SQLite и Parquet
04
Чтение целиком и одного канала
Что наблюдается
Наклон 0,0200 на МэВ восстановлен; Parquet — 4,5 МиБ и 16 мс на чтение всего, SQLite — 725 мс на всё, но 3,6 мс на один канал по индексу.
root@lab — запись со стенда
Демонстрация. Скрипт pandas_sql.py передаёт базе запрос с соединением и группировкой и получает DataFrame из 40 строк — по строке на заход с энергией, средним сигналом BPM01 и числом точек, 5000 в каждом. Функция numpy.polyfit проводит через зависимость сигнала от энергии прямую по методу наименьших квадратов и восстанавливает заложенный в модели наклон 0,0200 на мегаэлектронвольт с нулевым смещением. Функция to_sql записывает сводку в таблицу run_summary, и её схема в sqlite_master показывает, что pandas создал столбцы по типам DataFrame, но без первичного ключа и ограничений. Скрипт formats.py выгружает миллион измерений в CSV и Parquet и сравнивает чтение с SQLite, беря медиану трёх запусков. CSV занимает 22,5 МиБ и читается целиком за 119 мс; база SQLite — 33,4 МиБ и 725 мс, потому что строки проходят через модуль sqlite3 по одной; Parquet со сжатием занимает 4,5 МиБ и читается за 16 мс. Для одного канала одного захода соотношение обратное: CSV приходится прочитать целиком, 121 мс, Parquet с фильтром — 15 мс, поскольку весь файл умещается в одну группу строк, а SQLite находит 5000 строк по индексу за 3,6 мс.

Эксплуатация и выборРезервные копии и PITR, репликация, масштабирование, мониторинг, нереляционные хранилища, выбор СУБД
06
Зачем СУБД
Модель и SQL
Индексы и планы
Транзакции
Python и базы
Эксплуатация и выбор
Изложение. Шестая часть посвящена жизни базы после того, как она создана: резервным копиям и восстановлению, журналу WAL, репликации и масштабированию, мониторингу, а также нереляционным хранилищам и выбору СУБД.

Резервные
копии

СпособЧто получаетсяОграничение
cp файла базыкопия, если к базе никто не подключёнв работе и в WAL — неполная копия
backup(), VACUUM INTOсогласованная копия SQLite в работеодна машина, одна база
pg_dumpлогическая копия базына момент начала, без PITR
pg_basebackup и архив WALфизическая копия и журнал: PITRвесь кластер, та же версия
Важно
Копия без проверенного восстановления копией не считается: восстановление репетируют.
Для физика
Правило 3-2-1: три копии на двух видах носителей, одна из них — вне здания.
Изложение. Резервная копия защищает от событий, которых транзакции не предотвращают: ошибочной команды DROP TABLE или DELETE без WHERE, сбоя диска, потери машины. Копирование файла базы командой cp корректно, только когда к базе никто не подключён; в режиме WAL подтверждённые транзакции до контрольной точки находятся в отдельном файле, и копия одного основного файла их теряет. SQLite делает согласованную копию во время работы с базой методом Connection.backup() из Python или командой VACUUM INTO. Для PostgreSQL рассмотрим два способа. Утилита pg_dump создаёт логическую копию одной базы — SQL-команды или сжатый архив, который восстанавливает pg_restore, — на момент начала копирования; её можно развернуть и на более новой версии сервера. Утилита pg_basebackup снимает физическую копию файлов всего сервера, а вместе с непрерывным архивом журнала WAL позволяет восстановиться на любой момент времени. Хранят копии по правилу «три-два-один»: три копии на двух видах носителей, одна из них — вне здания. Главное правило — копия без проверенного восстановления копией не считается: восстановление репетируют по инструкции на отдельной машине и проверяют, что данные на месте, а инструкцию пишут так, чтобы её выполнил дежурный ночью, а не только автор.

Способ	Что получается	Ограничение
cp файла базы	копия, если к базе никто не подключён	в работе и в WAL — неполная копия
backup(), VACUUM INTO	согласованная копия SQLite в работе	одна машина, одна база
pg_dump	логическая копия базы	на момент начала, без PITR
pg_basebackup и архив WAL	физическая копия и журнал: PITR	весь кластер, та же версия

Демонстрация:
копия базы в WAL
01
writer.py: 500 строк в базе WAL, соединение открыто
02
cp основного файла
03
backup() и VACUUM INTO
04
Копия после завершения писателя
Что наблюдается
Пока писатель работает, данные в wal.db-wal (28 872 байта), а копия cp — без таблицы; backup() и VACUUM INTO — по 500 строк; после закрытия журнал перенесён в wal.db.
root@lab — запись со стенда
Демонстрация. Скрипт writer.py имитирует процесс сбора данных: он переводит базу wal.db в режим WAL, записывает и фиксирует 500 строк и продолжает работать с открытым соединением. Команда ls показывает, что основной файл занимает 4096 байт — одну страницу с заголовком, — а данные находятся в файле wal.db-wal размером 28 872 байта рядом с файлом общей памяти wal.db-shm. Копия основного файла командой cp открывается как база без таблиц: консоль python3 -m sqlite3 отвечает no such table: m. Таким образом, подтверждённые данные в такую копию не попали. Метод Connection.backup() из скрипта backup.py и команда VACUUM INTO, выполненная прямо в консоли, делают согласованные копии с 500 строками. После завершения писателя командой kill его соединение закрывается штатно: последнее соединение выполняет контрольную точку, журнал переносится в основной файл, который вырастает до 20 480 байт, файлы -wal и -shm исчезают, и теперь простая копия тоже содержит 500 строк.

Демонстрация:
pg_dump и pg_restore
01
DROP TABLE в транзакции и ROLLBACK
02
pg_dump всей базы в архив
03
DROP TABLE без транзакции
04
pg_restore в новую базу и проверка
Что наблюдается
DROP TABLE внутри транзакции откатывается вместе с ней; архив pg_dump занимает 8,0 МиБ; после необратимого DROP TABLE восстановленная база снова содержит 1 000 000 строк.
root@lab — запись со стенда
Демонстрация. В контейнере pg таблица measurements с миллионом строк заполнена тем же скриптом fill.sql, что и в демонстрации EXPLAIN ANALYZE, а алиас psql запускает клиент внутри контейнера. Первая команда открывает транзакцию, удаляет таблицу и откатывает транзакцию: в PostgreSQL даже изменения схемы транзакционны, и после ROLLBACK таблица на месте со всеми 1 000 000 строк. Затем pg_dump создаёт логическую копию базы в сжатом формате custom и записывает её на стенд в файл lab.dump размером 8 425 662 байта, около 8,0 МиБ. Команда DROP TABLE без транзакции выполняется с автофиксацией, и таблица удалена безвозвратно. Восстановление идёт по правилам репетиции: createdb создаёт пустую базу restored, pg_restore разворачивает в неё архив, и запрос подтверждает, что в восстановленной таблице снова 1 000 000 строк. Восстановление в отдельную базу позволяет сначала проверить данные, а затем вернуть нужную таблицу в рабочую базу, например командой pg_restore с ключом -t.

WAL:
PITR и реплики
основной сервер
пишет WAL раньше,
чем файлы таблиц
WAL
архив WAL и базовая копия
восстановление на момент (PITR)
физическая реплика
поток WAL; только чтение; та же версия
логическая репликация
изменения строк; другая версия или СУБД
Для физика
Ошибочный DROP TABLE в 18:03: из ночной копии и архива WAL база восстанавливается на 18:02:59.
Важно
Реплика не заменяет копию: ошибочная команда доходит до неё за доли секунды.
Изложение. Журнал упреждающей записи, WAL, является основой надёжности PostgreSQL: каждое изменение сначала записывается в журнал, и только потом меняются файлы таблиц, поэтому после сбоя сервер восстанавливается, проигрывая журнал от последней контрольной точки. Тот же журнал решает ещё три задачи. Для восстановления базы на любой момент достаточно базовой физической копии и архива сегментов WAL: ночная копия и архив журнала до 18:02:59 возвращают таблицу, ошибочно удалённую в 18:03. Это восстановление на момент времени, PITR; момент задаёт параметр recovery_target_time. Если передавать журнал потоком на другой сервер, получается физическая реплика — побайтовая копия всего сервера, доступная только для чтения; она требует той же основной версии PostgreSQL и той же архитектуры и служит для резервирования и разгрузки чтения. Логическая репликация расшифровывает журнал в изменения строк выбранных таблиц и передаёт их подписчику, который может работать на другой версии PostgreSQL; так переходят на новую версию с минимальным простоем, а сторонние средства тем же способом передают изменения в аналитическую систему. Реплика не заменяет резервную копию: ошибочный DROP TABLE доходит до неё за доли секунды.

Масштабирование
01
Пул соединений
PgBouncer: тысячи клиентов на десятки процессов сервера
02
Реплики для чтения
отчёты и графики — с реплики, запись — на основной
03
Секционирование
PARTITION BY RANGE (t): месяц — секция
04
Шардирование
данные по ключу на нескольких серверах
Для физика
Временной ряд датчиков секционируют по месяцам: архив старше срока хранения удаляется одной командой.
Пояснение
Встроенное шардирование — у ClickHouse, MongoDB, YDB; в PostgreSQL — расширение Citus или своё в приложении.
Важно
Шардирование планируют заранее: переделка работающей базы в десятки терабайт занимает месяцы.
Изложение. Один сервер PostgreSQL на современном оборудовании годами обслуживает базу лаборатории и даже института, и масштабирование начинают с дешёвых шагов. Пул соединений, например PgBouncer, позволяет тысячам клиентов работать через десятки серверных процессов. Реплики разгружают основной сервер от чтения: отчёты и графики строятся по реплике, хотя она может отставать на доли секунды. Секционирование представляет собой разбиение большой таблицы на части по диапазону, например временного ряда датчиков по месяцам: запрос за неделю читает одну секцию, а данные старше срока хранения удаляются одной командой вместо медленного DELETE. Шардирование распределяет данные по нескольким серверам по ключу; запросы внутри одного шарда остаются быстрыми, а соединения и транзакции между шардами дороги, поэтому небольшие справочники копируют на каждый шард или денормализуют. Встроенное шардирование есть у ClickHouse, MongoDB и YDB, а в PostgreSQL его добавляет расширение Citus или реализует само приложение. Если заранее ясно, что данные не поместятся на один сервер, шардирование закладывают с самого начала: переделка работающей базы в десятки терабайт отнимает месяцы работы целой команды.

Мониторинг
базы
01
Ресурсы
процессор, память, место и inode, операции диска в секунду
02
Запросы
число в секунду, задержка p50 и p99, доля по типам
03
Ошибки
тревога по порогу, а не по каждой записи журнала
04
Признаки СУБД
медленные запросы, мёртвые версии, отставание реплик
-- самые дорогие запросы
SELECT calls, mean_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
 
-- мёртвые версии строк
SELECT relname, n_dead_tup
FROM pg_stat_user_tables;
Важно
Нижние пороги: нагрузка, упавшая до нуля, — тоже авария.
Изложение. СУБД наблюдают как любое приложение, но с поправкой на её особенности. Ресурсы: процессор, память, свободное место и inode на диске, число операций ввода-вывода в секунду; диск для базы — самый частый узкий ресурс. Запросы: их число в секунду, задержки по перцентилям p50 и p99, а не по среднему, которое скрывает медленные запросы, и распределение по типам — рост доли чтений указывает на отказ кеша перед базой, резкое увеличение числа UPDATE — на ошибку в приложении. Ошибки в журнале под нагрузкой бывают всегда, например при обрыве соединения клиентом, поэтому тревогу поднимают по превышению порога. Специфические признаки PostgreSQL: самые дорогие запросы показывает расширение pg_stat_statements, число мёртвых версий строк — представление pg_stat_user_tables, отставание реплик — pg_stat_replication. Необходимо задавать и нижние пороги: если нагрузка на базу упала до нуля, это авария, хотя ни один верхний порог не превышен — например, данные удалены, и запросам стало нечего делать. Если выделенного администратора баз данных нет, первым вариантом рассматривают управляемую СУБД облачного провайдера, где копии, обновления и мониторинг берёт на себя провайдер.

Нереляционные
хранилища

ТипПримерРольОграничение
ключ — значение в памятиValkey, Redisкеш, очереди, счётчикипотеря записей после снимка RDB; блокировка сервера командой KEYS *
документыMongoDBразнородные метаданныесхема — в коде или в валидаторе $jsonSchema
столбцыClickHouseаналитика миллиардов строквставка партиями от тысяч строк
временные рядыInfluxDB, TimescaleDBдатчики, медленный контрольсроки хранения и прореживание
Для физика
Данные ускорительного комплекса: PostgreSQL — строгие метаданные, MongoDB — разнородные измерения, Redis — текущие состояния устройств.
Изложение. Под отдельные профили нагрузки существуют нереляционные хранилища. Valkey и Redis хранят пары «ключ — значение» в оперативной памяти и отвечают за доли миллисекунды: это кеш результатов тяжёлых запросов, очереди задач, счётчики. По умолчанию они периодически сохраняют снимок памяти RDB и при сбое теряют записанное после него; журнал команд AOF включается отдельно и сокращает потерю примерно до секунды. Команды выполняются в одном потоке, поэтому KEYS со звёздочкой, перебирающая все ключи, на время перебора блокирует сервер для всех клиентов, а перебор ведут командой SCAN. В 2024 году Redis сменил лицензию на несвободную, и сообщество под эгидой Linux Foundation продолжило открытый проект под названием Valkey; в 2025 году Redis 8 вышел и под свободной лицензией AGPLv3. MongoDB хранит документы со вложенной структурой и подходит для разнородных метаданных, где у соседних записей разные поля; схему по умолчанию проверяет код, но коллекции можно задать валидатор $jsonSchema. ClickHouse хранит данные по столбцам и считает агрегаты по миллиардам строк за секунды, но рассчитан на вставку крупными партиями: каждая вставка создаёт на диске новый кусок, и поток вставок по одной строке приводит к ошибке Too many parts. Базы временных рядов InfluxDB и TimescaleDB, расширение PostgreSQL, автоматически прореживают и удаляют старые данные и строят агрегаты по окнам времени. В системе хранения данных ускорительного комплекса, описанной в главе «Работа с базами данных», используются сразу три базы: так называемое полиглотное хранение оправдано, когда профили данных действительно различны, а цена ему — отсутствие транзакции, охватывающей все базы.

Тип	Пример	Роль	Ограничение
ключ — значение в памяти	Valkey, Redis	кеш, очереди, счётчики	потеря записей после снимка RDB; блокировка сервера командой KEYS *
документы	MongoDB	разнородные метаданные	схема — в коде или в валидаторе $jsonSchema
столбцы	ClickHouse	аналитика миллиардов строк	вставка партиями от тысяч строк
временные ряды	InfluxDB, TimescaleDB	датчики, медленный контроль	сроки хранения и прореживание

Выбор
СУБД
01
Скучная технология
десятилетия в работе, известные ошибки, специалисты
02
Копии и PITR
восстановление на момент и его проверка
03
Объём и рост
поместится ли на один сервер
04
Нагрузки
транзакции и аналитика — в разных системах
05
Истории неудач
не только успехи в блоге разработчиков
06
Безопасность
аутентификация, TLS, права по умолчанию
Пояснение
Один исследователь и его скрипты — SQLite; общая база группы — PostgreSQL; вторая СУБД — когда первая измеримо не справляется.
Для физика
Тип JSONB в PostgreSQL покрывает потребность в документах для большинства задач лаборатории.
Изложение. Выбор СУБД начинают с вопроса, насколько технология «скучна» в смысле эссе Д. Маккинли «Choose Boring Technology» (2015): система, которая работает десятилетиями, имеет известные ошибки, документацию, сотни специалистов и платную поддержку, реже приводит к неожиданностям, чем модная база с тремя выпусками. Далее проверяют, есть ли резервное копирование с восстановлением на момент времени и насколько просто проверить восстановление. Оценивают объём и рост данных: если ясно, что один сервер не справится, нужна система со встроенным шардированием или заранее спланированное своё. Разные нагрузки разводят по разным системам: транзакционная база для записи и аналитическая копия для отчётов проще в эксплуатации, чем одна система для всего. Ищут истории неудач, а не только успехов: если в блоге разработчиков сообщается лишь о достижениях, а на форумах встречаются только вопросы о подключении, то о слабых местах системы известно мало и первым с ними столкнётся сам пользователь. Проверяют безопасность по умолчанию: например, у FoundationDB сетевой доступ к порту без настроенного TLS даёт полные права, а встроенная авторизация помечена в документации как экспериментальная. Таким образом, для лаборатории практический вывод прост: данные одного исследователя — SQLite, общая база группы — PostgreSQL, где для документов достаточно типа JSONB; вторую систему заводят, когда первая измеримо перестала справляться с конкретной нагрузкой.

Итоги
лекции
01
СУБД берёт на себя одновременный доступ, целостность, поиск и восстановление — то, с чем не справляются файлы.
02
Схема с ключами и ограничениями — документация, которую база проверяет сама; SQL описывает результат, а не перебор.
03
Индекс превращает просмотр миллиона строк в спуск по B-дереву; план выбирается по статистике.
04
Транзакция — всё или ничего; изоляцию дают блокировки в SQLite и версии строк в PostgreSQL.
05
Значения — только параметрами; копия базы — только с проверенным восстановлением.
Изложение. Отметим основные выводы лекции. СУБД берёт на себя то, с чем не справляются папки с файлами: одновременный доступ, целостность данных, быстрый поиск и восстановление после сбоев. Схема с первичными и внешними ключами и ограничениями является документацией, которую база проверяет при каждой записи, а SQL описывает нужный результат, оставляя способ его получения планировщику. Индекс на основе B-дерева превращает просмотр миллиона строк в спуск по нескольким страницам, а план запроса выбирается по статистике, которую необходимо поддерживать актуальной. Транзакция выполняется целиком или не выполняется вовсе; одновременный доступ SQLite разграничивает блокировками файла и журналом WAL, а PostgreSQL — версиями строк. Из Python значения в запрос передают только параметрами, ORM и pandas при этом генерируют тот же SQL. Наконец, резервная копия ценна только вместе с проверенным восстановлением, а систему выбирают по нагрузке, начиная с проверенных временем SQLite и PostgreSQL.

Упражнения
и задание
01
Схема своей установки
три-четыре таблицы с ключами и импорт накопленных CSV
02
План запроса
поиск медленного запроса, индекс и проверка плана
03
Изоляция
повторение опыта MVCC на уровне SERIALIZABLE
04
Копия и восстановление
pg_dump, восстановление в новую базу, сверка строк
Пояснение
Задание курса: «Деплой стартапа „Котики в мир“» — приложение, контейнеры, сеть и база.
Для физика
Журнал своей установки в SQLite — первый шаг от папок с CSV к общей базе группы.
Изложение. Упражнения служат самопроверке. Первое — спроектировать для своей установки или лабораторной работы схему из трёх-четырёх таблиц с первичными и внешними ключами и импортировать в неё накопленные файлы CSV; при импорте целесообразно сначала собрать файлы в один DataFrame, чтобы различия в наборах столбцов не обрывали загрузку на середине. Второе — найти в своём журнале запрос, выполняющийся полным просмотром, построить подходящий индекс и убедиться по плану, что он используется. Третье — повторить опыт с двумя сеансами PostgreSQL на уровне SERIALIZABLE и найти пару транзакций, которую этот уровень прерывает, а REPEATABLE READ пропускает. Четвёртое — сделать копию базы pg_dump, восстановить её в новую базу и сверить число строк в каждой таблице. Заданием курса к этой лекции служит «Деплой стартапа „Котики в мир“»: приложение, контейнеры, сеть и база данных вместе.

Источники1
Книга курса: главы «Базы данных» и «Работа с базами данных»
phys-dev.github.io/soft-dev-book/dev/bd.html
2
Лекция «Базы данных», КИТ 2024, Young&&Yandex; Е. Дюков
youtube.com/watch?v=Y0uqMf2vGpg
3
Документация PostgreSQL 17
postgresql.org/docs/17
4
Документация SQLite
sqlite.org/docs.html
5
Kleppmann M. Designing Data-Intensive Applications. – O'Reilly, 2017; рус. пер.: Клеппман М. Высоконагруженные приложения. – СПб.: Питер, 2018
dataintensive.net
6
Документация SQLAlchemy 2 и psycopg 3
docs.sqlalchemy.org/en/21, psycopg.org/psycopg3/docs
7
Winand M. Use The Index, Luke: SQL Indexing and Tuning
use-the-index-luke.com
8
Рогов Е. В. PostgreSQL 18 изнутри. – М.: ДМК Пресс, 2026
postgrespro.ru/education/books/internals
Изложение. Основой лекции являются главы книги курса «Базы данных» и «Работа с базами данных»; устройство СУБД, эксплуатация и выбор системы следуют лекции «Базы данных» Курсов информационных технологий 2024 года. Для самостоятельного изучения рекомендуются документация PostgreSQL и SQLite, книга Мартина Клеппмана о проектировании приложений, работающих с большими объёмами данных, документация SQLAlchemy и psycopg, руководство Маркуса Винанда об индексах и книга Егора Рогова о внутреннем устройстве PostgreSQL.

Спасибо за вниманиеВопросы
phys-dev.github.io/soft-dev-book
COMMIT
Изложение. Надпись на последнем слайде — COMMIT, команда, которая фиксирует транзакцию: после неё изменения становятся видимыми другим транзакциям и сохраняются при сбое. Она парная к SQL на титуле: лекция начиналась с языка запросов и завершается фиксацией сделанного. Лекция завершается ответами на вопросы.