PostgreSQL и SQL с нуля · от psql до индексов

Чтобы дальше всё шло, нужно уверенно жить в psql, читать схему и понимать, зачем индекс и кэш. Этот урок ставит фундамент: одна СУБД (Postgres), один сквозной пример (схема BatSchool) и одна привычка, читать EXPLAIN.

К концу занятия ты сможешь развернуть локальный Postgres, подключиться через psql, набросать четыре связанные таблицы, написать SELECT с JOIN и агрегатами и объяснить, почему один и тот же запрос с индексом летает, а без него ползёт.

0. Зачем СУБД вообще

Можно хранить данные в JSON-файле. Можно даже в нескольких файлах с папками по дате. Это работает ровно до тех пор, пока приложение запускается в одном процессе и пользователь у тебя один. Дальше всё ломается.

Представь, что у BatSchool два менти одновременно сдают одно и то же ДЗ. Сервер читает progress.json, дописывает строку, пишет обратно. Если две записи приходят с разницей в 50 миллисекунд, одна из них пропадает: второй процесс прочитал старую версию файла и затёр свежую. Это lost update, и в файловом хранилище его починить тяжело.

СУБД решает четыре задачи, которые поверх файлов делать руками больно:

Задача	Что даёт СУБД
Конкурентный доступ	блокировки и снапшоты, чтобы два клиента не затёрли друг друга
Транзакции	“всё или ничего”, даже если посреди записи отвалилось питание
Поиск по большим объёмам	индексы, чтобы не сканировать миллион строк ради одной
Структура	схема, типы колонок, внешние ключи, проверки на уровне БД

Дополнительно идёт durability: данные доживают до завтра, даже если сервер сегодня вечером упал.

Альтернативы остаются. Лог-файлы, CSV для аналитика, плоские конфиги для настроек, файловое хранилище для картинок и видео. Но как только в системе появляются два пользователя и хоть одно поле, которое нужно искать быстрее, чем за O(n), нужна СУБД.

1. Виды БД на одной карте

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

Класс	Примеры	Когда брать
Реляционные (RDBMS)	PostgreSQL, MySQL, SQLite	OLTP по умолчанию: транзакции, JOIN, схема
Документные	MongoDB, Couchbase	гибкая схема, вложенные документы как единое целое
Key-value	Redis, DynamoDB, etcd	быстрый GET/SET по ключу, сессии, кэш, очереди
Колоночные (OLAP)	ClickHouse, DuckDB, BigQuery	аналитика по миллиардам строк, агрегаты, мало UPDATE
Поисковые	Elasticsearch, OpenSearch, Meilisearch	полнотекст и фасеты
Графовые	Neo4j, Memgraph	обходы графа на несколько прыжков (друзья друзей)
Time-series	TimescaleDB, InfluxDB, Prometheus	метрики, события по времени
Векторные	pgvector, Qdrant, Weaviate	поиск по эмбеддингам, RAG

Деление условное: Postgres умеет JSONB и pgvector, ClickHouse умеет SQL, Redis давно перерос KV. Но правило по умолчанию в backend такое: бери Postgres, пока не упрёшься в конкретное ограничение. Это Postgres-first архитектура. Гибридная типизация, расширения (pg_trgm, pgvector, pg_partman), JSONB и партиции снимают 90% задач. Остальные 10% решаются отдельной специализированной БД рядом.

Этот курс идёт по той же логике. Уроки 01-04 про Postgres. Урок 05 знакомит с Redis (KV-кэш и очереди) и ClickHouse (OLAP), чтобы было понятно, когда подкладывать соседа. Урок 06 собирает архитектуру данных в систему: CDC, lakehouse, разделение OLTP и OLAP. Углублённая аналитика лежит в разделе 26-data-engineering.

2. Первый разговор с psql

Поднимем локальный Postgres и зайдём в него. В репозитории BatSchool это уже сделано, но давай посмотрим, из чего состоит конфигурация.

# docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: batschool
      POSTGRES_PASSWORD: batschool
      POSTGRES_DB: batschool
    ports:
      - '5432:5432'
    volumes:
      - postgres-data:/var/lib/postgresql/data

Запуск и подключение:

docker compose up -d postgres
psql 'postgres://batschool:batschool@localhost:5432/batschool'

psql, это не только окно для SQL. Это твой швейцарский нож по схеме: метакоманды на обратной косой показывают таблицы, индексы, права, размеры.

Минимальный набор, который нужно знать наизусть:

\l                  список баз
\c batschool        подключиться к базе
\dt                 таблицы текущей схемы
\d users            детальный осмотр таблицы users
\di                 индексы
\du                 роли и права
\df                 функции
\dn                 схемы
\timing             печатать время выполнения каждого запроса
\x                  переключить вертикальный вывод (удобно для широких строк)
\q                  выход

Файл ~/.psqlrc поднимает удобства один раз и навсегда:

\set QUIET 1
\pset null '∅'
\pset linestyle unicode
\pset border 2
\timing on
\set COMP_KEYWORD_CASE upper
\set HISTSIZE 10000
\unset QUIET

Что это даёт. NULL теперь видно явно вместо пустого места, рамки таблиц рисуются юникодом, \timing on всегда печатает миллисекунды запроса, история длиннее. Привычка к видимому NULL дешевле, чем неделя на дебаг “у нас тут пустая строка или null”.

Внутри psql работают и SQL-запросы:

SELECT version();
SELECT now(), current_database(), current_user;

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

3. Реляционная модель и нормальные формы

В реляционной модели данные живут в отношениях, по-простому, в таблицах. У каждой таблицы есть схема (имена и типы колонок), есть строки. У каждой строки есть первичный ключ, и одна или несколько колонок могут быть внешним ключом на другую таблицу.

Соберём схему BatSchool. Нам нужны менти (users), уроки (lessons), прогресс по урокам и сданные ДЗ. Стартовая версия в DDL:

CREATE TABLE users (
  id          bigserial PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  display_name text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE lessons (
  id          bigserial PRIMARY KEY,
  section     text NOT NULL,
  slug        text NOT NULL,
  title       text NOT NULL,
  level       text NOT NULL CHECK (level IN ('J','M','MS','S','L')),
  estimate_min integer NOT NULL CHECK (estimate_min > 0),
  UNIQUE (section, slug)
);

CREATE TABLE progress (
  user_id     bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  lesson_id   bigint NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  state       text NOT NULL CHECK (state IN ('todo','in_progress','done')),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, lesson_id)
);

CREATE TABLE homework_submissions (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  lesson_id   bigint NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  hw_id       text NOT NULL,
  submitted_at timestamptz NOT NULL DEFAULT now(),
  url         text NOT NULL,
  UNIQUE (user_id, lesson_id, hw_id)
);

Несколько правил, которые здесь уже выполнены:

• У каждой таблицы есть первичный ключ. В progress он составной: один менти, один урок, одна запись.
• Любая ссылка между таблицами оформлена через REFERENCES с явным ON DELETE CASCADE. Удалили менти, его прогресс ушёл вместе с ним.
• Уникальность бизнес-смысла вынесена в UNIQUE (section, slug) и UNIQUE (user_id, lesson_id, hw_id). БД не даст вставить дубликат, даже если приложение ошибётся.
• Перечисления (level, state) ограничены CHECK. Это не самый красивый способ (бывает enum), но самый простой и портабельный.
• Все таймстампы в timestamptz (UTC внутри), а не голый timestamp. Часовые пояса всегда лучше хранить с зоной.

Нормальные формы кратко

Нормализация, это набор правил, как разнести данные по таблицам, чтобы каждый факт хранился ровно в одном месте.

• 1NF: значения в колонках атомарные. Не “теги: js, ts, fp” в одной строке, а отдельная таблица lesson_tags.
• 2NF: при составном PK каждый неключевой атрибут зависит от ВСЕГО ключа, а не от его части. У progress(user_id, lesson_id) нет колонки lesson_title, потому что она зависит только от lesson_id, для неё есть таблица lessons.
• 3NF: неключевые атрибуты не зависят от других неключевых. Если бы в users лежали city_id и city_name, нарушение: имя города зависит от city_id, выноси в cities.

На практике денормализация начинается, когда 3NF мешает скорости. Хранить lesson_title прямо в progress, чтобы не делать JOIN на каждый список менти? Иногда оправдано на чтении, но требует синхронизации на записи. В курсе мы держим строгую 3NF до момента, когда EXPLAIN покажет реальную проблему.