Базы данных

Файлов для хранения данных достаточно до тех пор, пока накопленное не потребовалось нескольким людям одновременно.

В настоящей главе рассматриваются задачи, которые берёт на себя система управления базами данных, и различия таких систем, затем реляционная модель и язык SQL на примере SQLite, индексы и планы запросов. Работа с базами из программ на Python, транзакции и одновременный доступ, эксплуатация и нереляционные хранилища рассматриваются в следующей главе, «Работа с базами данных». Сквозным примером обеих глав служит журнал измерений установки — кампании, заходы и точки, снятые с датчиков. Скрипт, заполняющий журнал синтетическими данными, приведён в разделе об SQL, и большинство листингов работает с ним.

Листинги главы выполнены на одной машине — виртуальной машине под Python 3.12 со встроенной библиотекой SQLite 3.45, на которой записаны демонстрации лекции «Базы данных». PostgreSQL 17 работал на ней в контейнере Docker, а программы на Python подключались к нему драйвером psycopg 3.3. Консоли sqlite3 на машине нет, поэтому запросы к SQLite выполнены модулем sqlite3 из Python. Времена выполнения зависят от машины и меняются от запуска к запуску, тогда как планы запросов, числа строк и значения воспроизводятся.

Слайды к главе. Назначение СУБД, реляционная модель и SQL, индексы и планы запросов изложены также в первых трёх частях лекции «Базы данных» с демонстрациями в терминале, а остальные части лекции относятся к главе «Работа с базами данных»; слайды лекции доступны на сайте книги и в PDF.

Назначение СУБД

Ограничения хранения в файлах

Хранение данных в лаборатории почти всегда развивается по одному сценарию. Сначала появляется result.csv. Затем result_final.csv, result_final_v2.csv и папка 2026-08-20/ с двадцатью файлами, названными по энергии пучка, номеру захода и фамилии оператора. Через год накапливаются десятки гигабайт CSV-файлов, и вопрос «показать все измерения канала BPM01 при энергии выше 10 МэВ» превращается в вечер работы со скриптом, рекурсивно обходящим папки и разбирающим имена файлов.

Файлы и папки не справляются с четырьмя задачами. Первая из них — одновременный доступ. Скрипт сбора данных записывает файл, а исследователь в этот момент открывает его для анализа и читает недописанную строку; два процесса, пишущие в один файл одновременно, оставляют его повреждённым. Вторая — целостность. CSV не проверяет ничего: в столбец с температурой попадает строка "error", порядок столбцов от файла к файлу различается, а измерение ссылается на заход, удалённый кем-то вместе с папкой. Третья — поиск. Чтобы найти точки одного канала одного захода, приходится читать все файлы целиком; на миллионах записей это занимает минуты, тогда как при правильно организованных данных тот же запрос выполняется за миллисекунды. Четвёртая — восстановление. Сбой посреди записи оставляет файл в промежуточном состоянии, а вернуть по ошибке удалённую папку неоткуда.

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

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

Виды нагрузки

Системы управления базами данных различаются прежде всего нагрузкой, под которую они построены. OLTP (online transaction processing, обработка транзакций в реальном времени) представляет собой множество коротких операций, каждая из которых читает или меняет несколько строк: запись нового захода, оплата в интернет-магазине. Ответ нужен за миллисекунды, а данные должны оставаться согласованными при тысячах одновременных клиентов. OLAP (online analytical processing, аналитическая обработка) — это редкие тяжёлые запросы, которые просматривают миллионы и миллиарды строк ради нескольких чисел, например статистика параметров пучка за сезон; ответ допустимо ждать секунды. Временные ряды сочетают обе черты: непрерывный поток мелких вставок «метка времени — значение» и аналитические запросы по окнам времени.

В ускорительной установке встречаются все три вида: журнал запусков представляет собой OLTP, анализ архива за сезон — OLAP, а система медленного контроля (slow control), опрашивающая сотни датчиков вакуума, температуры и токов каждую секунду годами, — временные ряды. От профиля нагрузки зависит устройство системы. OLTP-базы хранят таблицу по строкам и поддерживают транзакции, аналитические хранят данные по столбцам, что ускоряет агрегацию по нескольким столбцам миллиардов строк. Использовать систему не по назначению можно, но дорого: аналитика на PostgreSQL упирается в полное чтение больших таблиц, а частые мелкие вставки в ClickHouse перегружают его фоновые слияния.

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

Вместо перечня сотен систем удобнее несколько признаков, по которым они различаются: объём данных, вид нагрузки, язык запросов, транзакции и репликация. Таблица сравнивает по этим признакам четыре системы, которые часто встречаются вместе.

SQLite хранит базу в одном файле и уместна до сотен гигабайт, хотя формат допускает файл до 281 ТБ; PostgreSQL обслуживает терабайты на одном сервере, ClickHouse — петабайты на кластере, а Valkey и Redis ограничены оперативной памятью. PostgreSQL почти полностью соответствует стандарту SQL, у ClickHouse собственный диалект, а у Valkey вместо языка запросов набор команд, и фильтрацию по значениям выполняет приложение. SQLite и PostgreSQL обеспечивают все гарантии транзакций, обозначаемые аббревиатурой ACID и рассматриваемые в следующей главе. В ClickHouse привычных транзакций нет, но вставка блока строк в одну партицию таблицы семейства MergeTree атомарна; Valkey выполняет каждую команду атомарно, а группу команд MULTI/EXEC — подряд, без вклинивания чужих запросов, но без отката. Встроенной репликации у SQLite нет: начиная с версии 3.47 в поставку входит утилита sqlite3_rsync для разовой синхронизации копии, а непрерывную репликацию обеспечивают сторонние средства. PostgreSQL поддерживает физическую и логическую репликацию, ClickHouse реплицирует таблицы семейства Replicated с координацией через ClickHouse Keeper, Valkey передаёт изменения репликам асинхронно. Ни одна система не превосходит остальные по всем признакам, и систему выбирают по нагрузке и объёму: SQLite не заменит кластер, а кластер ради сотни мегабайт не разворачивают.

Встраиваемая и клиент-серверная архитектура

По архитектуре СУБД делятся на встраиваемые и клиент-серверные. SQLite представляет собой библиотеку, которая работает внутри процесса программы: обращение к базе — вызов функции, без сети и отдельного сервиса, а вся база — файл на локальном диске, рядом с которым в режиме журнала WAL лежат служебные файлы -wal и -shm. Поэтому SQLite не требует установки и настройки, но права доступа к базе сводятся к правам на файл, а работать с ней одновременно надёжно могут только процессы одной машины.

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

Для опытов сервер удобно запускать в контейнере Docker (см. главу «Docker»). Пароль lab годится только для учебного контейнера, принимающего соединения на петлевом интерфейсе 127.0.0.1. Параметр -c передаёт настройку серверу; модуль pg_stat_statements понадобится в разделе о мониторинге следующей главы, где используется тот же контейнер.

docker run -d --rm --name pg -e POSTGRES_PASSWORD=lab -p 127.0.0.1:5432:5432 \
    postgres:17-alpine -c shared_preload_libraries=pg_stat_statements
alias psql='docker exec -i pg psql -U postgres'   # клиент psql внутри контейнера

Серверные процессы перечисляет представление pg_stat_activity. Откроем к серверу три соединения из Python (драйвер psycopg рассматривается в следующей главе) и выведем список процессов:

import psycopg

DSN = "host=127.0.0.1 user=postgres password=lab"
clients = [psycopg.connect(DSN) for _ in range(3)]      # три открытых соединения
with psycopg.connect(DSN) as conn:
    rows = conn.execute("SELECT pid, backend_type, state FROM pg_stat_activity "
                        "ORDER BY backend_type, pid").fetchall()
for pid, backend_type, state in rows:
    print(pid, backend_type, state or "")

68 autovacuum launcher
65 background writer
64 checkpointer
84 client backend idle
85 client backend idle
86 client backend idle
87 client backend active
69 logical replication launcher
67 walwriter

Каждому из четырёх соединений, трём простаивающим (idle) и одному выполняющему запрос (active), соответствует отдельный процесс client backend со своим номером; остальные процессы представляют собой фоновые службы сервера, к которым относятся автоочистка, запись журнала и контрольные точки. Текст выполняемого запроса показывает столбец query того же представления.

Реляционная модель

Слайды к главе. Реляционная модель, нормализация, создание таблиц с ограничениями, выборка, соединение и агрегация изложены также во второй части лекции «Базы данных» с демонстрацией в терминале; слайды лекции доступны на сайте книги и в PDF.

Реляционная модель, предложенная Эдгаром Коддом в 1970 году, до сих пор считается основной. Данные укладываются в таблицы, называемые в теории отношениями (relations). Строка таблицы содержит запись об одном объекте, а столбец задаёт атрибут, наделённый фиксированным типом. Порядок строк в таблице не определён: упорядоченными они приходят только при явной сортировке в запросе.

Спроектируем схему для типичного эксперимента. Имеются измерительные кампании, внутри каждой находятся заходы (runs), различающиеся параметрами установки, а внутри каждого захода — точки измерений, снятые с датчиков.

experiments ──< runs ──< measurements
   (1)          (N)          (N)

Таблица experiments описывает кампанию: её название, установку и дату начала. Таблица runs описывает заход: его кампанию, номер, энергию пучка и оператора. Таблица measurements описывает отдельную точку: её заход, канал, время и значение. Знак ──< читается как связь «один ко многим»: у одной кампании много заходов, а у одного захода много снятых точек.

Ключи и связи

Первичным ключом (primary key) называется столбец или набор столбцов, однозначно идентифицирующий строку. Обычно им является суррогатный целочисленный id, выдаваемый базой самостоятельно.

Внешним ключом (foreign key) называется столбец, ссылающийся на первичный ключ другой таблицы. Столбец runs.experiment_id хранит id кампании, и таким образом строки связываются между собой. СУБД следит за валидностью ссылки: нельзя вставить заход с experiment_id = 42, если кампании с таким id нет, и нельзя удалить кампанию, на которую ссылаются заходы. SQLite выполняет эту проверку только после команды PRAGMA foreign_keys = ON, о которой речь пойдёт ниже.

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

Нормализация

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

Такая схема работает до первой правки. Если в фамилии оператора обнаружилась опечатка, обновлять приходится миллион строк, а при сбое скрипта на середине данные остаются противоречивыми. Это явление называется аномалиями обновления.

Нормализацией называется устранение дублирования, при котором каждый факт хранится один раз, а всё остальное на него ссылается. Формально она описывается нормальными формами. Первая нормальная форма (1НФ) требует атомарных значений без повторяющихся групп: не список каналов в одной ячейке, а отдельная строка на канал. Вторая и третья (2НФ и 3НФ) сводятся к мнемонике: неключевые атрибуты зависят «от ключа, от всего ключа и ни от чего, кроме ключа». Энергия является свойством захода, а не точки измерения, следовательно, ей место в runs. Название установки принадлежит кампании, поэтому оно хранится в experiments.

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

Основы SQL на примере SQLite

SQL (Structured Query Language) — декларативный язык для реляционных баз. Пользователь описывает нужный результат, а СУБД самостоятельно решает, как его получить.

Изучение ведётся на SQLite, встраиваемой СУБД без сервера: вся база лежит в одном файле, а драйвер входит в стандартную библиотеку Python (модуль sqlite3). При этом SQLite остаётся полноценной реляционной СУБД с транзакциями, работающей с базами в десятки и сотни гигабайт. Для лабораторных данных этого достаточно.

Консоль SQLite запускается командой sqlite3 lab.db, которая создаёт файл базы, если его ещё нет; сама консоль устанавливается отдельно, в Ubuntu — пакетом sqlite3. Начиная с Python 3.12, без установки доступна консоль модуля: python3 -m sqlite3 lab.db открывает интерактивный сеанс, а python3 -m sqlite3 lab.db "запрос" выполняет один запрос и печатает строки результата кортежами Python. Служебных команд консоли sqlite3, таких как .tables, в ней нет, и их заменяет запрос к системной таблице sqlite_master. Запросы этого раздела выполнены именно так. Просмотреть базу в графическом интерфейсе позволяет программа DB Browser for SQLite.

CREATE TABLE: создание таблиц

Приписки после типов задают ограничения, соблюдаемые базой самостоятельно, независимо от поведения прикладного кода. Схема журнала сохранена в файле schema.sql:

CREATE TABLE experiments (
    id         INTEGER PRIMARY KEY,   -- первичный ключ, база выдаёт сама
    name       TEXT NOT NULL UNIQUE,  -- название кампании, без повторов
    setup      TEXT,                  -- описание установки
    started_at TEXT NOT NULL          -- дата в ISO 8601: '2026-08-20'
);

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,      -- момент начала в UTC: '2026-08-20T03:15:00Z'
    UNIQUE (experiment_id, run_number)  -- номер захода уникален внутри кампании
);

CREATE TABLE measurements (
    id      INTEGER PRIMARY KEY,
    run_id  INTEGER NOT NULL REFERENCES runs(id),
    channel TEXT NOT NULL,            -- имя канала: 'BPM01', 'T_magnet', ...
    t       REAL NOT NULL,            -- время от начала захода, с
    value   REAL NOT NULL             -- измеренное значение
);

Ограничения NOT NULL, UNIQUE и внешние ключи обеспечивают целостность, отсутствовавшую у CSV: попытка записать некорректные данные завершится ошибкой, а не порчей, обнаруживаемой через полгода. Первичный ключ уникален и по стандарту не может быть пустым. В SQLite столбец INTEGER PRIMARY KEY совпадает с внутренним номером строки rowid и заполняется автоматически, а первичный ключ другого типа из-за давней ошибки, сохранённой ради совместимости, допускает NULL, если столбец не объявлен NOT NULL, а таблица — не STRICT и не WITHOUT ROWID.

Внешние ключи SQLite проверяет только при явно включённой проверке, и включать её необходимо в каждом новом соединении:

PRAGMA foreign_keys = ON;

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

import sqlite3

with open("schema.sql", encoding="utf-8") as f:
    schema = f.read()
INSERT = ("INSERT INTO runs (experiment_id, run_number, energy_mev, started_at) "
          "VALUES (42, 1, 12.5, '2026-08-20T03:15:00Z')")      # кампании 42 нет
for pragma in ("OFF", "ON"):
    con = sqlite3.connect(":memory:")
    con.executescript(schema)
    con.execute(f"PRAGMA foreign_keys = {pragma}")
    try:
        con.execute(INSERT)
        print(f"foreign_keys = {pragma}: заход записан")
    except sqlite3.IntegrityError as e:
        print(f"foreign_keys = {pragma}: {e}")

foreign_keys = OFF: заход записан
foreign_keys = ON: FOREIGN KEY constraint failed

PostgreSQL и MySQL с движком InnoDB проверяют внешние ключи по умолчанию. Однако MySQL без предупреждения игнорирует объявленные ключи в таблицах MyISAM, а дамп, созданный утилитой mysqldump, начинается с команды SET FOREIGN_KEY_CHECKS=0, которая на время его загрузки снимает проверку и в InnoDB.

Другая особенность SQLite — динамическая типизация: тип столбца лишь указывает предпочтительный способ хранения, и в столбец REAL можно записать строку. Таблицы с ключевым словом STRICT, появившиеся в версии 3.37, обеспечивают строгую проверку типов:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE loose (temperature REAL)")
con.execute("CREATE TABLE strict (temperature REAL) STRICT")
con.execute("CREATE TABLE channels (name TEXT PRIMARY KEY, unit TEXT)")
con.execute("INSERT INTO loose VALUES ('error')")
print(con.execute("SELECT temperature, typeof(temperature) FROM loose").fetchone())
for sql in ("INSERT INTO strict VALUES ('error')",
            "INSERT INTO channels VALUES (NULL, 'мА')",
            "INSERT INTO channels VALUES (NULL, 'В')"):
    try:
        con.execute(sql)
        print("принято:", sql)
    except sqlite3.IntegrityError as e:
        print("отказ:  ", e)

('error', 'text')
отказ:   cannot store TEXT value in REAL column strict.temperature
принято: INSERT INTO channels VALUES (NULL, 'мА')
принято: INSERT INTO channels VALUES (NULL, 'В')

Строка 'error' сохранилась в столбце температуры с типом text, таблица STRICT её отвергла, а в таблицу каналов с ключом name TEXT PRIMARY KEY легли две строки с пустым ключом. Поэтому естественный ключ в SQLite объявляют NOT NULL явно, а для новых таблиц, где важны типы, используют STRICT.

Отдельного внимания требует время. Момент начала захода в схеме хранится строкой ISO 8601 в UTC с суффиксом Z: строка местного времени без смещения при переводе часов и при обмене данными между площадками даёт повторы и пропуски меток. Рассмотрим ночь перевода часов в Женеве, где расположен ЦЕРН:

from datetime import datetime, timezone
from zoneinfo import ZoneInfo

geneva = ZoneInfo("Europe/Zurich")
for utc in ("2026-10-25T00:30:00", "2026-10-25T01:30:00"):    # два разных момента
    moment = datetime.fromisoformat(utc).replace(tzinfo=timezone.utc)
    local = moment.astimezone(geneva)
    print(f"{utc} UTC = {local:%Y-%m-%d %H:%M} в Женеве, смещение {local:%z}")

2026-10-25T00:30:00 UTC = 2026-10-25 02:30 в Женеве, смещение +0200
2026-10-25T01:30:00 UTC = 2026-10-25 02:30 в Женеве, смещение +0100

Два момента, разделённые часом, записываются одной и той же местной меткой «02:30», и без смещения восстановить порядок событий невозможно. В PostgreSQL для таких данных предназначен тип timestamptz: он хранит момент времени в UTC, а при выводе переводит его в часовой пояс сеанса.

CREATE TABLE shots (id int PRIMARY KEY, started_at timestamptz NOT NULL);
INSERT INTO shots VALUES (1, '2026-08-20 10:15:00+07');  -- время со смещением
SET TIME ZONE 'UTC';
SELECT started_at FROM shots;
SET TIME ZONE 'Europe/Zurich';
SELECT started_at FROM shots;

$ psql < tz.sql
CREATE TABLE
INSERT 0 1
SET
       started_at
------------------------
 2026-08-20 03:15:00+00
(1 row)

SET
       started_at
------------------------
 2026-08-20 05:15:00+02
(1 row)

Момент, записанный как 10:15 по времени UTC+7, хранится как 03:15 UTC и в сеансе с поясом Europe/Zurich выводится как 05:15 по летнему времени Женевы.

INSERT: вставка данных

Заполняемые базой столбцы перечислять не требуется: id она выдаст очередной. experiment_id для захода необходимо указать, иначе неизвестно, к какой кампании он относится.

INSERT INTO experiments (name, setup, started_at)
VALUES ('Калибровка датчиков положения', 'стенд ЛИУ', '2026-08-20');

INSERT INTO runs (experiment_id, run_number, energy_mev, operator, started_at)
VALUES (1, 1, 12.5, 'Иванов', '2026-08-20T03:15:00Z');

-- несколько строк одним запросом
INSERT INTO measurements (run_id, channel, t, value) VALUES
    (1, 'BPM01',    0.0, 0.412),
    (1, 'BPM01',    0.1, 0.418),
    (1, 'T_magnet', 0.0, 42.3);

Последняя форма, со списком кортежей после VALUES, вставляет группу строк одним запросом и, значит, одной транзакцией. Тысяча отдельных INSERT, каждый из которых фиксируется сам по себе (так работает консоль sqlite3), выполняется на два порядка медленнее; внутри одной транзакции разница исчезает, что показывает опыт в разделе о транзакциях следующей главы.

Журнал для примеров

Дальнейшие примеры работают с журналом, который заполняет синтетическими данными скрипт make_lab.py. В журнале две кампании и 41 заход, из которых последний оставлен без измерений; в каждом из остальных сорока заходов с пяти каналов снято по 5000 точек с частотой 100 Гц, всего миллион строк. Энергия пучка растёт от 5 МэВ в первом заходе до 25 МэВ в последнем с шагом 0,5 МэВ. Сигнал датчиков положения BPM в модели задан пропорциональным энергии с коэффициентом 0,02 на мегаэлектронвольт, к нему добавлены медленное колебание с амплитудой 0,01 и шум; у настоящего датчика сигнал отражает положение пучка, а от энергии зависит через дисперсию орбиты. Температура магнита медленно растёт, ток пучка экспоненциально спадает. Скрипт использует модуль sqlite3, подробно рассматриваемый в следующей главе, и записывает весь журнал одной транзакцией.

"""Создаёт lab.db по схеме schema.sql и заполняет журнал миллионом измерений."""
import math
import os
import random
import sqlite3
import time
from datetime import datetime, timedelta, timezone

N_RUNS = 41                                   # последний заход — без измерений
CHANNELS = ["BPM01", "BPM02", "BPM03", "T_magnet", "I_beam"]
POINTS = 5000                                 # точек на канал в заходе, 100 Гц
START = {1: datetime(2026, 8, 20, 1, tzinfo=timezone.utc),   # начало кампаний, UTC
         2: datetime(2026, 9, 1, 1, tzinfo=timezone.utc)}


def points(run_id, energy):
    """Точки захода: сигнал BPM задан пропорциональным энергии, T растёт, ток спадает."""
    for channel in CHANNELS:
        for i in range(POINTS):
            t = i / 100
            if channel.startswith("BPM"):
                v = 0.02 * energy + 0.01 * math.sin(t) + random.gauss(0, 0.002)
            elif channel == "T_magnet":
                v = 40 + 0.05 * energy + 0.001 * t
            else:
                v = 100 * math.exp(-t / 500)
            yield run_id, channel, t, round(v, 5)


if os.path.exists("lab.db"):
    os.remove("lab.db")
random.seed(1)                                # те же данные при каждом запуске
con = sqlite3.connect("lab.db")
con.execute("PRAGMA foreign_keys = ON")
with open("schema.sql", encoding="utf-8") as f:
    con.executescript(f.read())
start = time.perf_counter()
with con:                                     # весь журнал — одна транзакция
    con.execute("INSERT INTO experiments (name, setup, started_at) VALUES "
                "('Калибровка датчиков положения', 'стенд ЛИУ', '2026-08-20'), "
                "('Ресурсные испытания', 'стенд ЛИУ', '2026-09-01')")
    for n in range(1, N_RUNS + 1):
        experiment = 1 if n <= 30 else 2
        k = n - 1 if experiment == 1 else n - 31          # номер захода в кампании
        started = START[experiment] + timedelta(days=k // 10, hours=k % 10)
        energy = 5 + 0.5 * (n - 1)
        cur = con.execute(
            "INSERT INTO runs (experiment_id, run_number, energy_mev, operator, started_at) "
            "VALUES (?, ?, ?, ?, ?)",
            (experiment, n, energy, random.choice(["Иванов", "Петрова"]),
             started.strftime("%Y-%m-%dT%H:%M:%SZ")))
        if n != N_RUNS:
            con.executemany("INSERT INTO measurements (run_id, channel, t, value) "
                            "VALUES (?, ?, ?, ?)", points(cur.lastrowid, energy))
elapsed = time.perf_counter() - start
total = con.execute("SELECT count(*) FROM measurements").fetchone()[0]
con.close()
print(f"{total} измерений за {elapsed:.1f} с; lab.db = {os.path.getsize('lab.db') / 2**20:.1f} МиБ")

1000000 измерений за 0.8 с; lab.db = 33.4 МиБ

Миллион строк для СУБД немного, но этого уже достаточно, чтобы увидеть разницу между полным просмотром таблицы и поиском по индексу. Список таблиц, который в консоли sqlite3 выводит команда .tables, даёт запрос к sqlite_master:

$ python3 -m sqlite3 lab.db "SELECT name FROM sqlite_master WHERE type = 'table'"
('experiments',)
('runs',)
('measurements',)

SELECT: выборка

Вопрос, требовавший вечера работы со скриптом-обходчиком папок, записывается в несколько строк:

-- первые три точки канала BPM01 первого захода, по возрастанию времени
SELECT t, value
FROM measurements
WHERE run_id = 1 AND channel = 'BPM01'
ORDER BY t
LIMIT 3;

(0.0, 0.09673)
(0.01, 0.09858)
(0.02, 0.10112)

-- десять последних заходов с энергией выше 10 МэВ
SELECT run_number, energy_mev, started_at
FROM runs
WHERE energy_mev > 10
ORDER BY started_at DESC
LIMIT 10;

(41, 25.0, '2026-09-02T01:00:00Z')
(40, 24.5, '2026-09-01T10:00:00Z')
(39, 24.0, '2026-09-01T09:00:00Z')
(38, 23.5, '2026-09-01T08:00:00Z')
(37, 23.0, '2026-09-01T07:00:00Z')
(36, 22.5, '2026-09-01T06:00:00Z')
(35, 22.0, '2026-09-01T05:00:00Z')
(34, 21.5, '2026-09-01T04:00:00Z')
(33, 21.0, '2026-09-01T03:00:00Z')
(32, 20.5, '2026-09-01T02:00:00Z')

WHERE фильтрует строки, ORDER BY сортирует отобранное (DESC задаёт убывание), LIMIT ограничивает результат. Звёздочка SELECT * возвращает все столбцы, что удобно в консоли, однако в скриптах нужные столбцы перечисляются явно: тогда смысл запроса не меняется при добавлении столбца в таблицу. Сортировка меток started_at как строк корректна, поскольку все они записаны в одном формате и в одном часовом поясе.

Части запроса записываются в одном порядке, а логически выполняются в другом: сначала FROM и JOIN формируют строки, затем WHERE отбирает их, GROUP BY и HAVING группируют, SELECT вычисляет столбцы результата, а ORDER BY и LIMIT сортируют и обрезают. Поэтому по стандарту псевдоним столбца, введённый в SELECT, нельзя использовать в WHERE: на запрос SELECT energy_mev * 1000 AS energy_kev FROM runs WHERE energy_kev > 24000 PostgreSQL отвечает ошибкой column "energy_kev" does not exist. SQLite в качестве расширения стандарта такой запрос выполняет, и при переносе в PostgreSQL его придётся исправить.

JOIN: соединение таблиц

Нормализация разложила данные по трём таблицам, а JOIN собирает разложенное обратно. Соединение сопоставляет строки по условию, обычно «внешний ключ равен первичному»:

-- точки канала BPM01 захода 31 вместе с энергией и названием кампании
SELECT e.name, r.run_number, r.energy_mev, 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' AND r.run_number = 31
ORDER BY m.t
LIMIT 2;

('Ресурсные испытания', 31, 20.0, 0.0, 0.40551)
('Ресурсные испытания', 31, 20.0, 0.01, 0.40252)

AS m задаёт псевдоним, избавляющий от длинных имён. Без ORDER BY запрос вернул бы две первые строки в порядке хранения, и совпадение этого порядка с порядком по времени не гарантировано. Кроме обычного (внутреннего) JOIN существует LEFT JOIN, сохраняющий строки левой таблицы даже при отсутствии пары справа и заполняющий столбцы правой таблицы значением NULL. Таким образом находят заходы без измерений:

-- заходы без единого измерения
SELECT r.run_number
FROM runs AS r
LEFT JOIN measurements AS m ON m.run_id = r.id
WHERE m.id IS NULL;

(41,)

GROUP BY: агрегация

Агрегатные функции (COUNT, AVG, SUM, MIN, MAX) сворачивают отобранную группу строк в одно число, а GROUP BY задаёт признак группировки:

-- сводка по заходам: число точек и статистика сигнала BPM01
SELECT r.run_number,
       r.energy_mev,
       COUNT(*)               AS n_points,
       ROUND(AVG(m.value), 4) AS mean_value,
       MIN(m.value)           AS min_value,
       MAX(m.value)           AS max_value
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 DESC
LIMIT 3;

(40, 24.5, 5000, 0.49, 0.47509, 0.50626)
(39, 24.0, 5000, 0.48, 0.46358, 0.49628)
(38, 23.5, 5000, 0.47, 0.45421, 0.48687)

HAVING фильтрует после группировки, по вычисленным агрегатам; WHERE отбирает строки до неё. Отметим, что в списке SELECT при группировке допустимы столбцы группировки, агрегаты и столбцы, однозначно определяемые первичным ключом группы: r.run_number и r.energy_mev определяются ключом r.id, поэтому запрос корректен и по стандарту, и в PostgreSQL. Средний сигнал растёт на 0,01 на каждые 0,5 МэВ, как и заложено в модели, а разброс от минимума до максимума, около 0,03, складывается из колебания с амплитудой 0,01 и шума. Главное практическое следствие состоит в том, что агрегация выполняется там, где лежат данные, и в программу приходит сводка из нескольких строк вместо миллиона точек.

Индексы и планы запросов

Слайды к главе. Устройство B-дерева, цена и выбор индекса, планировщик и статистика, планы запросов в SQLite и PostgreSQL изложены также в третьей части лекции «Базы данных» с демонстрациями в терминале; слайды лекции доступны на сайте книги и в PDF.

B-дерево

Без индекса запрос с WHERE заставляет базу просматривать всю таблицу, строку за строкой: это полный просмотр, SCAN в плане SQLite и Seq Scan в PostgreSQL. Индекс представляет собой дополнительную структуру, обычно B-дерево, в которой значения индексируемых столбцов хранятся упорядоченными вместе со ссылками на строки таблицы: в SQLite ссылкой служит номер строки rowid, в PostgreSQL — адрес версии строки. Индекс позволяет находить строки по значению столбца за логарифмическое время, наподобие предметного указателя в книге. Каждый узел дерева представляет собой страницу размером в несколько килобайт с сотнями ключей. Оценим глубину дерева: при ветвлении порядка ста первые три уровня вмещают сто, десять тысяч и миллион ключей, поэтому для миллиона строк дереву достаточно трёх-четырёх уровней, и поиск сводится к спуску от корня к листу. Ключи в листьях упорядочены, поэтому диапазон значений читается подряд, от листа к соседнему, и строки выдаются уже упорядоченными; в PostgreSQL соседние страницы каждого уровня к тому же связаны ссылками, а SQLite переходит к следующему листу через родительский узел.

Оценим выигрыш на журнале. Скрипт plan.py печатает план запроса, который возвращает команда EXPLAIN QUERY PLAN, и медиану времени пяти выполнений:

"""План запроса из файла .sql и медиана времени пяти выполнений."""
import sqlite3
import statistics
import sys
import time

sql = open(sys.argv[1], encoding="utf-8").read()
con = sqlite3.connect("lab.db")
for row in con.execute("EXPLAIN QUERY PLAN " + sql):
    print("план:", row[3])
times = []
for _ in range(5):
    start = time.perf_counter()
    rows = con.execute(sql).fetchall()
    times.append((time.perf_counter() - start) * 1000)
print(f"строк: {len(rows)}, первая: {rows[0]}")
print(f"время: {statistics.median(times):.2f} мс (медиана из 5)")

Запрос q1.sql считает средний сигнал канала BPM01 захода 7:

SELECT round(avg(value), 4) FROM measurements
WHERE run_id = 7 AND channel = 'BPM01';

$ python3 plan.py q1.sql
план: SCAN measurements
строк: 1, первая: (0.16,)
время: 19.27 мс (медиана из 5)
$ du -m lab.db
34 lab.db
$ python3 -m sqlite3 lab.db "CREATE INDEX idx_m_run_channel ON measurements (run_id, channel)"
$ du -m lab.db
50 lab.db
$ python3 plan.py q1.sql
план: SEARCH measurements USING INDEX idx_m_run_channel (run_id=? AND channel=?)
строк: 1, первая: (0.16,)
время: 0.26 мс (медиана из 5)

Без индекса план — SCAN measurements, полный просмотр миллиона строк. С индексом план меняется на SEARCH ... USING INDEX, и тот же запрос выполняется в 74 раза быстрее, а результат, средний сигнал 0,16, не меняется. Индекс занял 16 МиБ, почти половину объёма таблицы. Время поиска почти не зависит от размера таблицы: число прочитанных страниц растёт как логарифм числа строк.

Индекс по нескольким столбцам упорядочен сначала по первому столбцу, затем по второму, как телефонный справочник по фамилии и имени. Индекс (run_id, channel) ускоряет поиск по run_id и по паре run_id и channel, а по одному channel — только перебором значений run_id, пропуском (skip scan): SQLite выбирает такой план после сбора статистики, если значений первого столбца немного, PostgreSQL — начиная с версии 18.

Цена и выбор индекса

Индекс не бесплатен. Он занимает место: в опыте выше индекс по двум столбцам увеличил файл на 16 МиБ. Каждая вставка, изменение и удаление обновляет все индексы таблицы, поэтому таблица с десятком индексов пишется заметно медленнее, и индексы строят под частые запросы, а не на все столбцы подряд. Индекс эффективен, когда условие отбирает малую долю строк; если же под условие попадает большая часть таблицы, чтение строк вразброс по индексу обходится дороже последовательного чтения всей таблицы, и планировщик выбирает полный просмотр.

Порядок столбцов определяет, какие запросы ускоряются. Рассмотрим запрос q2.sql, который выбирает 5000 точек того же канала, упорядоченных по времени:

SELECT t, value FROM measurements
WHERE run_id = 7 AND channel = 'BPM01'
ORDER BY t;

$ python3 plan.py q2.sql
план: SEARCH measurements USING INDEX idx_m_run_channel (run_id=? AND channel=?)
план: USE TEMP B-TREE FOR ORDER BY
строк: 5000, первая: (0.0, 0.15967)
время: 2.04 мс (медиана из 5)
$ python3 -m sqlite3 lab.db "CREATE INDEX idx_m_run_channel_t ON measurements (run_id, channel, t)"
$ python3 plan.py q2.sql
план: SEARCH measurements USING INDEX idx_m_run_channel_t (run_id=? AND channel=?)
строк: 5000, первая: (0.0, 0.15967)
время: 1.72 мс (медиана из 5)
$ python3 -m sqlite3 lab.db "DROP INDEX idx_m_run_channel"

Строки находятся по индексу, но индекс (run_id, channel) не содержит столбца t, и для сортировки строится временное B-дерево (USE TEMP B-TREE FOR ORDER BY). Индекс (run_id, channel, t) отдаёт точки канала сразу упорядоченными по времени, а прежний индекс становится избыточным: его роль выполняет начало нового, и его удаляют, чтобы не замедлять запись. Индекс, содержащий все столбцы, нужные запросу, называется покрывающим: таблицу при таком запросе не читают вовсе.

Первичный ключ и ограничение UNIQUE индексируются автоматически. Столбцы внешних ключей в SQLite и PostgreSQL индексируют вручную, если их не покрывает индекс, начинающийся с того же столбца; MySQL создаёт такой индекс сам. Без индекса на measurements.run_id удаление захода при включённой проверке внешних ключей просматривает все измерения. План такой проверки показывает тот же EXPLAIN QUERY PLAN:

import sqlite3

con = sqlite3.connect("lab.db")
con.execute("PRAGMA foreign_keys = ON")
for sql in ("DELETE FROM experiments WHERE id = 1", "DELETE FROM runs WHERE id = 41"):
    print(sql)
    for row in con.execute("EXPLAIN QUERY PLAN " + sql):
        print("   ", row[3])

DELETE FROM experiments WHERE id = 1
    SEARCH experiments USING INTEGER PRIMARY KEY (rowid=?)
    SEARCH runs USING COVERING INDEX sqlite_autoindex_runs_1 (experiment_id=?)
DELETE FROM runs WHERE id = 41
    SEARCH runs USING INTEGER PRIMARY KEY (rowid=?)
    SEARCH measurements USING COVERING INDEX idx_m_run_channel_t (run_id=?)

Ссылки на удаляемую кампанию ищутся по индексу sqlite_autoindex_runs_1, который создало ограничение UNIQUE (experiment_id, run_number), а ссылки на удаляемый заход — по новому индексу (run_id, channel, t). До создания индексов вторая строка плана удаления захода имела вид SCAN measurements. Таким образом, индексы строят под реальные частые запросы, а их использование проверяют планом.

Планировщик и статистика

Один и тот же запрос можно выполнить многими способами: прочитать таблицу целиком или спуститься по индексу, соединить таблицы вложенным циклом, хешированием или слиянием, в том или ином порядке. Планировщик перебирает варианты и выбирает самый дешёвый по оценке стоимости в условных единицах. В PostgreSQL чтение страницы подряд оценивается в одну условную единицу, чтение страницы вразброс — в четыре, обработка строки — в 0,01; эти веса задаются параметрами seq_page_cost, random_page_cost и cpu_tuple_cost. Оценка зависит от селективности условия, то есть доли строк, которые оно отберёт, а её планировщик берёт из статистики: числа строк, числа различных значений, самых частых значений и гистограммы распределения.

Статистику собирает команда ANALYZE. В PostgreSQL её запускает фоновая автоочистка, когда изменилась заметная доля таблицы, по умолчанию десятая часть строк; статистику столбца показывает представление pg_stats. SQLite собирает статистику только по команде ANALYZE или PRAGMA optimize и хранит её в таблице sqlite_stat1, где для индекса записаны число строк и среднее число строк на одно значение каждого начального набора столбцов ключа. После сбора статистики запрос по одному каналу, без условия на заход, переходит от полного просмотра к пропуску по индексу:

SELECT round(avg(value), 4) FROM measurements
WHERE channel = 'BPM01';

$ python3 plan.py skip.sql
план: SCAN measurements
строк: 1, первая: (0.295,)
время: 29.20 мс (медиана из 5)
$ python3 -m sqlite3 lab.db "ANALYZE"
$ python3 -m sqlite3 lab.db "SELECT tbl, idx, stat FROM sqlite_stat1"
('measurements', 'idx_m_run_channel_t', '1000000 25000 5000 1')
('runs', 'sqlite_autoindex_runs_1', '41 21 1')
('experiments', 'sqlite_autoindex_experiments_1', '2 1')
$ python3 plan.py skip.sql
план: SEARCH measurements USING INDEX idx_m_run_channel_t (ANY(run_id) AND channel=?)
строк: 1, первая: (0.295,)
время: 11.42 мс (медиана из 5)

Строка 1000000 25000 5000 1 означает, что в индексе миллион строк, на одно значение run_id приходится 25 000 строк, на пару run_id и channel — 5000, а на полный ключ — одна. Значений run_id всего сорок, и планировщик перебирает их, для каждого спускаясь по индексу к нужному каналу (ANY(run_id)): запрос выполняется в 2,6 раза быстрее полного просмотра. Средний сигнал 0,295 соответствует модели: 0,02 · 14,75 МэВ, где 14,75 МэВ — средняя энергия сорока заходов.

Когда данные меняются быстрее, чем обновляется статистика, например после массовой загрузки, план опирается на устаревшую картину, и запрос, выполнявшийся за миллисекунды, начинает занимать секунды. Покажем это в уменьшенном виде на скрипте stats.py: в таблице users 200 000 пользователей, все из Москвы, по городу построен индекс, а запрос считает москвичей старше 30 лет.

"""Статистика планировщика SQLite: один и тот же запрос до и после ANALYZE."""
import os
import random
import sqlite3
import time

if os.path.exists("users.db"):
    os.remove("users.db")
random.seed(1)
con = sqlite3.connect("users.db")
con.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, city TEXT, age INTEGER, bio TEXT)")
with con:                                  # 200 000 пользователей, все из Москвы
    con.executemany("INSERT INTO users (city, age, bio) VALUES ('Москва', ?, ?)",
                    ((random.randint(18, 80), "x" * 100) for _ in range(200_000)))
con.execute("CREATE INDEX idx_city ON users (city)")
SQL = "SELECT count(*) FROM users WHERE city = ? AND age > 30"


def show(stage, city):
    plan = con.execute("EXPLAIN QUERY PLAN " + SQL, (city,)).fetchone()[3]
    start = time.perf_counter()
    n = con.execute(SQL, (city,)).fetchone()[0]
    ms = (time.perf_counter() - start) * 1000
    print(f"{stage}, город {city}:")
    print(f"   {plan:44s} {n:6d} строк {ms:6.2f} мс")


def stat():
    print("   sqlite_stat1:", *con.execute("SELECT idx, stat FROM sqlite_stat1").fetchone())


show("без статистики", "Москва")
con.execute("ANALYZE")
stat()
show("после ANALYZE", "Москва")
with con:                                  # 200 000 жителей 2000 других городов
    con.executemany("INSERT INTO users (city, age, bio) VALUES (?, ?, ?)",
                    ((f"Город-{random.randint(1, 2000)}", random.randint(18, 80), "y" * 100)
                     for _ in range(200_000)))
    con.execute("INSERT INTO users (city, age, bio) VALUES ('Новосибирск', 40, 'z')")
show("статистика устарела", "Новосибирск")
con.execute("ANALYZE")
stat()
show("ANALYZE в том же соединении", "Новосибирск")
con.close()
con = sqlite3.connect("users.db")
show("новое соединение", "Новосибирск")

без статистики, город Москва:
   SEARCH users USING INDEX idx_city (city=?)   158623 строк  13.31 мс
   sqlite_stat1: idx_city 200000 200000
после ANALYZE, город Москва:
   SCAN users                                   158623 строк   9.21 мс
статистика устарела, город Новосибирск:
   SCAN users                                        1 строк  14.42 мс
   sqlite_stat1: idx_city 400001 200
ANALYZE в том же соединении, город Новосибирск:
   SCAN users                                        1 строк  14.64 мс
новое соединение, город Новосибирск:
   SEARCH users USING INDEX idx_city (city=?)        1 строк   0.01 мс

Без статистики SQLite считает индекс полезным и выбирает поиск по нему: 158 623 строки за 13,3 мс. ANALYZE записывает в sqlite_stat1 строку 200000 200000 — на одно значение города приходятся все строки, — и планировщик переходит к полному просмотру, который оказывается быстрее, 9,2 мс: индекс вёл к чтению всех строк по номерам вразброс. Затем в таблицу добавляются 200 000 жителей двух тысяч других городов и один житель Новосибирска, а статистика не обновляется. Планировщик по-прежнему считает город неселективным и ради одной строки просматривает всю таблицу за 14,4 мс.

Повторный ANALYZE записывает новую статистику 400001 200, то есть 200 строк на город, и соединение сразу перечитывает её, однако план в нём остался прежним. Причина — особенность SQLite версий 3.45–3.49. Первый ANALYZE, обнаружив в индексе единственное значение при числе строк больше ста, пометил idx_city как непригодный для поиска по равенству; повторный ANALYZE эту отметку не снимает, и она сохраняется в соединении до нового чтения схемы. Новое соединение читает схему заново и выбирает индекс: та же строка находится за 0,01 мс. В SQLite 3.50 и новее отметка удалена, и повторный ANALYZE меняет план и в том же соединении, так что Python, собранный с такой библиотекой, уже в строке «ANALYZE в том же соединении» покажет поиск по индексу. Вывод от версии не зависит: после массовой загрузки статистику собирают сразу, а в SQLite 3.45–3.49 долго работающая программа после этого переоткрывает соединение.

EXPLAIN ANALYZE в PostgreSQL

В PostgreSQL план показывает команда EXPLAIN, а с ключом ANALYZE запрос ещё и выполняется, и к каждому узлу плана добавляются фактические числа. План представляет собой дерево узлов: нижние узлы читают таблицы полным просмотром Seq Scan, по индексу Index Scan или через битовую карту страниц Bitmap Heap Scan, узлы выше соединяют, сортируют и агрегируют; читают план от вложенных узлов к внешним. Главное в выводе — сравнение оценки планировщика rows с фактическим числом строк actual rows: расхождение на порядок указывает на устаревшую статистику или на условие, которое планировщик оценить не может. Ключ BUFFERS показывает, сколько страниц по 8 КиБ прочитано из общего кеша (hit) и с диска (read); в PostgreSQL 18 он включён по умолчанию. Execution Time — время выполнения на сервере без передачи результата клиенту.

Перейдём к PostgreSQL и повторим в нём опыт с индексом. Скрипт fill.sql создаёт таблицу measurements и заполняет её миллионом строк заход за заходом и канал за каналом, как в журнале SQLite, только каналы здесь идут по алфавиту, а к сигналу добавлен равномерный шум без колебания; затем ANALYZE собирает статистику.

\timing on
CREATE TABLE measurements (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    run_id  int NOT NULL,
    channel text NOT NULL,
    t       real NOT NULL,
    value   real NOT NULL);
INSERT INTO measurements (run_id, channel, t, value)
SELECT r, c, i / 100.0, 0.02 * (4.5 + 0.5 * r) + random() / 500
FROM generate_series(1, 40) r,
     unnest(ARRAY['BPM01', 'BPM02', 'BPM03', 'T_magnet', 'I_beam']) c,
     generate_series(0, 4999) i
ORDER BY r, c, i;
ANALYZE measurements;

$ psql < fill.sql
Timing is on.
CREATE TABLE
Time: 2.039 ms
INSERT 0 1000000
Time: 2292.181 ms (00:02.292)
ANALYZE
Time: 37.061 ms

Запрос explain.sql выполняется с ключами ANALYZE и BUFFERS, а оценки стоимости и время каждого узла отключены ради краткости вывода:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT avg(value) FROM measurements
WHERE run_id = 7 AND channel = 'BPM01';

$ psql < explain.sql
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=7353
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=7353
         ->  Partial Aggregate (actual rows=1 loops=3)
               Buffers: shared hit=7353
               ->  Parallel Seq Scan on measurements (actual rows=1667 loops=3)
                     Filter: ((run_id = 7) AND (channel = 'BPM01'::text))
                     Rows Removed by Filter: 331667
                     Buffers: shared hit=7353
 Planning:
   Buffers: shared hit=85 read=1
 Planning Time: 0.213 ms
 Execution Time: 16.411 ms
(16 rows)
$ psql -c 'CREATE INDEX m_run_ch ON measurements (run_id, channel)'
CREATE INDEX
$ psql < explain.sql
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=38 read=7
   ->  Index Scan using m_run_ch on measurements (actual rows=5000 loops=1)
         Index Cond: ((run_id = 7) AND (channel = 'BPM01'::text))
         Buffers: shared hit=38 read=7
 Planning:
   Buffers: shared hit=115 read=1
 Planning Time: 0.268 ms
 Execution Time: 0.359 ms
(9 rows)

Без индекса план представляет собой параллельный полный просмотр: два дополнительных процесса (Workers Launched: 2) и основной разбирают страницы таблицы диапазонами по мере готовности; actual rows=1667 loops=3 означает в среднем 1667 строк на каждый из трёх процессов, и каждый отбрасывает в среднем 331 667 строк. Всего из кеша прочитано 7353 страницы, и запрос выполняется за 16,4 мс. С индексом план — Index Scan: прочитано 45 страниц, 38 из кеша и 7 с диска, а время выполнения — 0,36 мс, в 46 раз меньше. Страниц прочитано в 160 раз меньше, поскольку точки одного канала одного захода лежат в таблице рядом, и индекс ведёт прямо к ним; если бы строки вставлялись вразброс, точки канала оказались бы на разных страницах, и чтение по индексу обошлось бы дороже.

Если в узле сортировки появляется строка Sort Method: external merge Disk: ..., сортировке не хватило памяти work_mem — по умолчанию 4 МБ на каждый узел сортировки, а хеш-таблице вдвое больше (hash_mem_multiplier = 2), — и запрос пишет временные файлы. EXPLAIN ANALYZE действительно выполняет запрос, поэтому изменяющие команды проверяют внутри транзакции, которую затем откатывают: BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;. Подсказок планировщику в ядре PostgreSQL нет, их добавляют расширения, и относятся к ним настороженно: закреплённый план опирается на прошлые предположения о данных.

Резюме

СУБД берёт на себя то, с чем не справляются папки с файлами: одновременный доступ, целостность данных, быстрый поиск и восстановление после сбоев. Схема с первичными и внешними ключами и ограничениями служит документацией, которую база проверяет при каждой записи; в SQLite для этого необходимы команда PRAGMA foreign_keys = ON в каждом соединении и, где важны типы, таблицы STRICT, а время записывается в UTC. SQL описывает нужный результат, оставляя способ его получения планировщику. Индекс на основе B-дерева заменяет просмотр миллиона строк спуском по трём-четырём страницам, однако занимает место и замедляет запись, поэтому индексы строят под частые запросы и проверяют их использование планом. Таким образом, скорость запроса определяется схемой, индексами и статистикой, по которой планировщик выбирает план, и после массовой загрузки статистику собирают заново. Работа с базами из программ на Python, транзакции, резервные копии, репликация и выбор хранилища рассматриваются в главе «Работа с базами данных».

Полезные ссылки

• Документация SQLite
• Документация PostgreSQL 17
• SQLBolt, интерактивный учебник SQL с упражнениями
• PostgreSQL Tutorial
• Use The Index, Luke — руководство М. Винанда по индексам и планам запросов
• Рогов Е. В. PostgreSQL 18 изнутри
• DB Browser for SQLite