Skip to content

Instantly share code, notes, and snippets.

@dmitry-osin

dmitry-osin/postgresql-zero-to-hero-rus.md

Last active January 21, 2026 16:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dmitry-osin/d325a6f25a5f979f4b4da462ad0caac4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dmitry-osin/d325a6f25a5f979f4b4da462ad0caac4 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL от новичка до эксперта на русском
Raw
postgresql-zero-to-hero-rus.md

Модуль 0: Фундамент реляционных баз и SQL

=========================================

Тема 0.1. Реляционная теория и Моделирование

Прежде чем написать первую строчку кода, нужно понять, как "думает" реляционная база данных (RDBMS). В отличие от написания кода на Java, где мы оперируем объектами и классами, здесь мы работаем с множествами и математической логикой.

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

Реляционная модель (от англ. relation — отношение) представляет данные в виде двумерных таблиц.

  • Отношение (Relation): В обиходе мы называем это Таблицей. Это структура данных, состоящая из заголовка (названия колонок) и тела (набор строк).
    • Аналогия с Java: Это как List<User>, где сама концепция списка пользователей — это таблица.
  • Кортеж (Tuple): Это Строка (Row) или Запись. Каждый кортеж представляет собой один конкретный экземпляр объекта.
    • Аналогия с Java: Это один объект new User(...).
  • Атрибут (Attribute): Это Столбец (Column). Характеристика, описывающая сущность (имя, возраст, цена). У каждого атрибута есть Домен (Domain) — допустимый диапазон значений (или Тип данных).

2. ER-диаграммы (Entity-Relationship)

Это чертеж вашей базы данных. Проектирование всегда начинается с карандаша и бумаги (или инструментов вроде draw.io).

Существует три основных вида связей между таблицами:

  1. Один к Одному (1:1):
    • Одна запись в таблице А соответствует одной записи в таблице Б.
    • Пример: Гражданин и Паспорт. У одного гражданина один действующий паспорт.
    • На практике: Часто такие таблицы объединяют в одну, если нет жесткой необходимости их разделять (например, для безопасности).
  2. Один ко Многим (1:M): — Самая частая связь.
    • Одна запись в таблице А связана с множеством записей в таблице Б.
    • Пример: Пользователь и Заказы. Один User может сделать много Orders, но конкретный Order принадлежит только одному User.
    • Реализация: ID родителя (User) кладется в таблицу ребенка (Order) как внешний ключ.
  3. Многие ко Многим (M:M):
    • Записи из таблицы А могут быть связаны со множеством записей таблицы Б и наоборот.
    • Пример: Студенты и Курсы. Один студент ходит на много курсов. На один курс ходит много студентов.
    • Важно: В реляционных базах прямая связь M:M невозможна. Она всегда реализуется через третью (связующую) таблицу (Junction Table), превращаясь в две связи 1:M.

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

Нормализация — это процесс организации данных для уменьшения избыточности (дублей) и проблем с целостностью.

  • 1НФ (Первая нормальная форма) — Атомарность:
    • В одной ячейке должно быть только одно значение.
    • Ошибка: В поле phone_numbers хранить "89991112233, 89994445566".
    • Решение: Вынести телефоны в отдельную таблицу или сделать две строки.
  • 2НФ и 3НФ (Нормализация ключей и зависимостей):
    • Суть: Данные в строке должны зависеть только от первичного ключа этой строки (Primary Key), а не от других полей.
    • Пример ошибки: В таблице Orders хранить поля: order_id, product_name, supplier_company, supplier_city.
    • Проблема: Поле supplier_city зависит от supplier_company, а не от order_id (заказа). Если поставщик переедет, нам придется обновлять тысячи строк заказов.
    • Решение (3НФ): Вынести поставщика в отдельную таблицу Suppliers.
  • BCNF (Нормальная форма Бойса-Кодда):
    • Более строгая версия 3НФ. "Каждый детерминант должен быть потенциальным ключом". На старте достаточно понимать 3НФ: "В каждой таблице должна лежать информация только об одной конкретной сущности".

4. Constraints (Ограничения)

Это правила, которые база данных принудительно проверяет перед сохранением данных. Это ваша защита от "мусора" в данных.

Рассмотрим их на примере SQL-кода создания таблицы (DDL).

-- Пример таблицы "Пользователи"
CREATE TABLE users (
    -- PK (Primary Key): Первичный ключ.
    -- Гарантирует, что каждая строка уникальна и не равна NULL.
    -- Обычно это суррогатный id (число).
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

    -- NOT NULL: Запрещает сохранять пустоту. У юзера обязательно должно быть имя.
    username TEXT NOT NULL,

    -- UNIQUE: Гарантирует уникальность значений во всей колонке.
    -- Два пользователя не могут иметь одинаковый email.
    email TEXT NOT NULL UNIQUE,

    -- CHECK: Произвольное условие проверки.
    -- База выдаст ошибку, если попытаться сохранить возраст меньше 0.
    age INTEGER CHECK (age >= 0),
    
    -- DEFAULT: Значение по умолчанию, если оно не передано при вставке.
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Пример таблицы "Заказы" для демонстрации FK
CREATE TABLE orders (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    
    -- FK (Foreign Key): Внешний ключ.
    -- Ссылка на таблицу users.
    -- Гарантирует, что мы не можем создать заказ для несуществующего пользователя.
    -- ON DELETE CASCADE означает: если удалим юзера, удалятся и все его заказы.
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    
    order_number TEXT NOT NULL
);

Краткий итог 0.1:

  1. Мыслим таблицами.
  2. Сначала рисуем схему (ER-диаграмму).
  3. Разбиваем данные так, чтобы не было дубликатов (Нормализация).
  4. Навешиваем "замки" (Constraints), чтобы данные были чистыми.

Тема 0.2. Базовый SQL (DML & DDL)

SQL (Structured Query Language) делится на несколько смысловых групп. Две самые главные, с которыми вы будете работать 99% времени — это DDL и DML.

1. DDL (Data Definition Language) — Определение структуры

Эти команды меняют схему базы данных: создают таблицы, меняют типы колонок, удаляют объекты. Это "строительные работы".

Создание, Изменение, Удаление

-- 1. CREATE: Создание новой таблицы
-- Мы создаем таблицу товаров (products)
CREATE TABLE products (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- Уникальный номер
    name TEXT NOT NULL,                                 -- Название товара
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),    -- Цена (10 цифр всего, 2 после запятой)
    category TEXT DEFAULT 'General',                    -- Категория по умолчанию
    is_active BOOLEAN DEFAULT true                      -- В наличии ли товар
);

-- 2. ALTER: Изменение существующей таблицы
-- Представим, что мы забыли добавить описание товара.
-- Добавляем новую колонку:
ALTER TABLE products ADD COLUMN description TEXT;

-- Или решили, что название может быть пустым (плохая идея, но для примера):
ALTER TABLE products ALTER COLUMN name DROP NOT NULL;

-- 3. DROP: Удаление таблицы
-- ВНИМАНИЕ: Это удаляет таблицу целиком вместе со всеми данными безвозвратно.
-- DROP TABLE products;

2. DML (Data Manipulation Language) — Манипуляция данными

Эти команды работают с наполнением таблиц. Это то, что называют CRUD (Create, Read, Update, Delete).

INSERT (Create) — Вставка данных

-- Вставка одной строки
-- Указываем колонки, в которые кладем данные. ID генерируется сам.
INSERT INTO products (name, price, category) 
VALUES ('Ноутбук XPS 15', 1500.00, 'Electronics');

-- Вставка нескольких строк сразу (Bulk Insert) - это эффективнее
INSERT INTO products (name, price, category, description) 
VALUES 
    ('Мышь Logitech', 25.50, 'Electronics', 'Беспроводная мышь'),
    ('Кофе зерновой', 15.00, 'Food', '100% Арабика'),
    ('Стол офисный', 120.00, 'Furniture', 'Деревянный стол');

UPDATE (Update) — Обновление данных

Критически важно: Всегда используйте WHERE, иначе обновите все строки в таблице.

-- Повышаем цену на конкретный товар (по ID)
UPDATE products 
SET price = 1600.00 
WHERE id = 1;

-- Обновляем несколько полей сразу для категории 'Food'
UPDATE products 
SET is_active = false, 
    description = 'Товар временно недоступен'
WHERE category = 'Food';

DELETE (Delete) — Удаление данных

Также требует осторожности с WHERE.

-- Удаляем конкретный товар
DELETE FROM products 
WHERE id = 4;

-- Удаляем все товары дешевле 10 условных единиц
DELETE FROM products 
WHERE price < 10.00;

3. Выборка и Фильтрация (SELECT & WHERE)

Самая мощная часть SQL. SELECT не меняет данные, он только возвращает результат.

-- Базовый SELECT: Выбрать всё (*) из таблицы
SELECT * FROM products;

-- Хорошая практика: Перечислять конкретные колонки (экономит трафик сети)
SELECT name, price FROM products;

Фильтрация (WHERE)

Оператор WHERE отсеивает строки, которые не подходят под условие.

-- 1. Точное совпадение и сравнение
SELECT * FROM products 
WHERE category = 'Electronics' AND price > 1000;

-- 2. IN: Проверка на вхождение в список
-- Найти товары, которые либо Еда, либо Мебель
SELECT * FROM products 
WHERE category IN ('Food', 'Furniture');

-- 3. BETWEEN: Диапазон (включительно)
-- Товары с ценой от 100 до 500
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

-- 4. LIKE: Поиск по шаблону (строки)
-- % означает "любое количество любых символов"
-- _ означает "ровно один любой символ"

-- Найти товары, начинающиеся на "Ноут" (Ноутбук, Ноуты...)
SELECT * FROM products 
WHERE name LIKE 'Ноут%';

-- Найти товары, где в названии есть слово "Phone" (iPhone, Smartphone...)
-- ILIKE в PostgreSQL делает поиск нечувствительным к регистру (Phone, phone)
SELECT * FROM products 
WHERE name ILIKE '%phone%';

4. Сортировка и Пагинация

В реальных приложениях мы редко выгружаем миллион строк сразу. Мы показываем их страницами.

ORDER BY (Сортировка)

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

-- Сортировка по цене от дешевых к дорогим (ASC - по возрастанию, default)
SELECT * FROM products 
ORDER BY price ASC;

-- Сортировка по категории (А-Я), а внутри категории — по цене убывания (DESC)
SELECT * FROM products 
ORDER BY category ASC, price DESC;

LIMIT и OFFSET (Пагинация)

  • LIMIT: Сколько строк вернуть.
  • OFFSET: Сколько строк пропустить с начала.
-- Задача: Показать "Страницу №2", если на странице по 10 товаров.
-- Значит, нужно пропустить первые 10 и взять следующие 10.

SELECT * FROM products 
ORDER BY id -- Всегда сортируйте при пагинации, иначе результаты могут "скакать"
LIMIT 10    -- Размер страницы
OFFSET 10;  -- Пропустить (Номер страницы - 1) * Размер страницы

Резюме 0.2:

  • DDL (CREATE) строит "скелет" данных.
  • DML (INSERT, UPDATE, DELETE) меняет "мясо" данных.
  • SELECT — основной инструмент чтения.
  • Без WHERE операции UPDATE и DELETE уничтожают данные.
  • Без ORDER BY порядок строк не гарантирован.

Тема 0.3. Соединения (JOINs) и Теория множеств

В нормализованной базе данных (как мы обсудили в теме 0.1) данные разбросаны по разным таблицам. Пользователи отдельно, заказы отдельно, товары отдельно.

Чтобы собрать их обратно в единую осмысленную строку для отчета или API, используется механизм JOIN (соединение). Это операция, которая сопоставляет строки одной таблицы со строками другой на основе условия (обычно: Primary Key = Foreign Key).

1. Основные виды JOINs

Для примеров используем две абстрактные таблицы:

  • Users (id, name) — список пользователей.
  • Orders (id, user_id, amount) — заказы, ссылающиеся на пользователей.

INNER JOIN (Внутреннее соединение)

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

  • Логика: "Покажи мне пользователей И их заказы".
  • Результат: Если у пользователя нет заказов, он не попадет в выборку. Если есть "потерянный" заказ без пользователя, он тоже не попадет.
SELECT 
    u.name, 
    o.amount
FROM users u             -- Таблица 1 (Левая)
INNER JOIN orders o      -- Таблица 2 (Правая)
    ON u.id = o.user_id; -- Условие связи (PK = FK)

LEFT JOIN (Левое внешнее соединение)

Возвращает все строки из ЛЕВОЙ таблицы (та, что написана после FROM), и сопоставленные строки из правой. Если совпадения нет, поля правой таблицы будут заполнены NULL.

  • Логика: "Покажи мне ВСЕХ пользователей, и их заказы, если они есть".
  • Результат: Юзеры без заказов будут в списке, но в колонке amount будет NULL.
SELECT 
    u.name, 
    o.amount
FROM users u             -- Левая таблица (Главная для этого запроса)
LEFT JOIN orders o       -- Правая таблица
    ON u.id = o.user_id;

-- Лайфхак: Как найти пользователей, у которых НЕТ заказов?
-- WHERE o.id IS NULL

RIGHT JOIN и FULL OUTER JOIN

  • RIGHT JOIN: Зеркальное отражение LEFT JOIN. Берет всё из правой таблицы. Используется крайне редко, так как проще поменять таблицы местами и сделать LEFT JOIN.
  • FULL OUTER JOIN: Объединение левого и правого. Показывает всё: пары (где совпало), левые без пары и правые без пары. Используется для сверки данных или сложных отчетов.

CROSS JOIN (Декартово произведение)

Соединяет каждую строку левой таблицы с каждой строкой правой таблицы. Условие ON не требуется.

  • Математика: Если в таблице A 100 строк, а в B 100 строк, результат будет 10,000 строк.
  • Опасность: Может "положить" базу, если таблицы большие.
  • Применение: Генерация матриц. Например, есть таблица Colors (Red, Blue) и Sizes (S, M, L). CROSS JOIN создаст все комбинации вариантов товара.

2. Операции над множествами

Если JOIN соединяет таблицы горизонтально (добавляет новые колонки к строке), то операции множеств соединяют результаты запросов вертикально (добавляют новые строки).

Важное условие: количество и типы колонок в запросах должны совпадать.

UNION и UNION ALL (Объединение)

Склеивает результаты двух SELECT-запросов.

  • UNION: Склеивает и удаляет дубликаты (это требует ресурсов на сортировку).
  • UNION ALL: Просто склеивает всё подряд. Быстрее, так как не ищет дубли.
-- Пример: Получить список всех email'ов из таблиц Клиентов и Сотрудников
SELECT email FROM clients
UNION ALL
SELECT email FROM employees;

INTERSECT (Пересечение)

Возвращает строки, которые есть и в первом, и во втором наборе. (Аналог retainAll в Java).

EXCEPT (Разность)

Возвращает строки, которые есть в первом наборе, но отсутствуют во втором. (Аналог removeAll в Java). В Oracle этот оператор называется MINUS.

3. Практическая задача

Задача: Спроектировать ER-схему для приложения "Список рецептов". Сущности:

  1. Category (Категория: Супы, Десерты). Связь с рецептами 1:M.
  2. Recipe (Рецепт).
  3. Ingredient (Продукт: Соль, Курица).
  4. Recipe_Ingredient (Связь M:M: В одном рецепте много ингредиентов, один ингредиент во многих рецептах).

Вот SQL-код, реализующий эту схему с учетом всех знаний из Модуля 0:

-- 1. Таблица Категорий (Справочник)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY, -- SERIAL - это сокращение для автоинкремента integer
    name TEXT NOT NULL UNIQUE
);

-- 2. Таблица Рецептов
CREATE TABLE recipes (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title TEXT NOT NULL,
    instructions TEXT, -- Инструкция может быть длинной
    
    -- Связь с категорией (1:M)
    -- Если категорию удалят, поле станет NULL (SET NULL), чтобы рецепт не исчез
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- 3. Таблица Ингредиентов (Справочник продуктов)
CREATE TABLE ingredients (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL UNIQUE, -- Нельзя создать две "Соли"
    calories_per_100g INTEGER CHECK (calories_per_100g >= 0)
);

-- 4. Связующая таблица (M:M)
-- Она соединяет Рецепт и Ингредиент, плюс хранит контекст (количество)
CREATE TABLE recipe_ingredients (
    recipe_id BIGINT REFERENCES recipes(id) ON DELETE CASCADE,
    ingredient_id BIGINT REFERENCES ingredients(id) ON DELETE RESTRICT,
    
    amount TEXT NOT NULL, -- Например, "200 гр" или "1 шт"
    
    -- Составной первичный ключ (Composite Primary Key)
    -- Гарантирует, что один ингредиент не добавится в один рецепт дважды
    PRIMARY KEY (recipe_id, ingredient_id)
);

Разбор решения:

  1. Нормализация: Ингредиенты вынесены отдельно. Если "Курица" переименуется в "Цыпленок", это изменится во всех рецептах.
  2. Типы данных: Используем TEXT для строк (стандарт Postgres), BIGINT для ID (чтобы не переполнилось).
  3. Constraints: UNIQUE для имен, CHECK для калорий, PK для связок.
  4. FK Actions: ON DELETE CASCADE для связки ингредиентов означает: если удалим рецепт, связи удалятся сами. Но ON DELETE RESTRICT в ингредиентах не даст удалить "Соль", пока она используется хотя бы в одном рецепте.

Модуль 1: PostgreSQL — Инструментарий разработчика

==================================================

Мы переходим от теории "сферической базы данных в вакууме" к конкретному инструменту — PostgreSQL. Это мощная объектно-реляционная СУБД, и она дает вам инструменты, которых нет в стандарте SQL.

Тема 1.1. Продвинутые типы данных

Выбор типа данных в Postgres — это вопрос не только семантики, но и производительности.

1. Числа: Как не потерять деньги

В Java у вас есть int, long, double, BigDecimal. В Postgres есть их прямые аналоги, но с нюансами.

  • integer (4 байта) и bigint (8 байт):
    • Стандартные целочисленные типы.
    • Совет: Если есть малейший шанс, что id перевалит за 2 млрд, используйте bigint. В современном мире 4 байта экономии не стоят боли от переполнения integer (Integer Overflow).
  • numeric / decimal:
    • Это число произвольной точности. Аналог BigDecimal в Java.
    • Оно хранит числа точно, без погрешностей округления.
    • Применение: ДЕНЬГИ. Никогда не храните деньги в double.
    • Минус: Медленнее в расчетах, чем integer или real.
  • real и double precision:
    • Числа с плавающей точкой (IEEE 754).
    • Применение: Координаты, физические измерения, где не важна точность до копейки, но важна скорость.
CREATE TABLE financials (
    id BIGINT PRIMARY KEY,
    -- numeric(15, 2) означает: всего 15 цифр, из них 2 после запятой.
    -- Это идеально для валют.
    salary NUMERIC(15, 2), 
    
    -- А вот это плохо для денег, так как 100.1 + 200.2 может дать 300.300000000004
    bad_salary DOUBLE PRECISION 
);

2. Строки: Миф о VARCHAR(255)

Если вы пришли из MySQL или старых учебников, вы привыкли писать VARCHAR(255). В PostgreSQL это не имеет смысла.

  • text: Строка произвольной длины.
  • varchar(n): Строка ограниченной длины.
  • Внутреннее устройство: В Postgres text и varchar под капотом работают абсолютно одинаково. Нет никакой разницы в производительности. Ограничение длины varchar(n) — это просто дополнительная проверка (constraint), которая даже добавляет крошечный оверхед.
  • Совет: Используйте text везде, кроме случаев, когда ограничение длины — это бизнес-требование (например, код страны из 2 букв).

3. Временные метки: Главная ловушка новичка

Есть два основных типа времени:

  1. timestamp (timestamp without time zone): Просто "фотография" времени на часах (например, "2023-10-05 10:00"). Оно ничего не знает о часовых поясах.
  2. timestamptz (timestamp with time zone): Время с учетом часового пояса.

Золотое правило: Всегда используйте timestamptz.

  • Как это работает: Postgres не хранит таймзону внутри поля.
    • Когда вы сохраняете данные в timestamptz, Postgres конвертирует их в UTC и сохраняет.
    • Когда вы читаете данные, Postgres конвертирует UTC обратно в таймзону вашего соединения (клиента/сервера).
  • Почему timestamp опасен: Если сервер в Москве, а клиент в Лондоне, использование timestamp без зоны приведет к путанице на 3 часа, которую невозможно будет разгрести.

4. JSONB: NoSQL внутри SQL

Postgres позволяет хранить JSON-документы и эффективно искать по ним. Существует два типа: json и jsonb.

  • json: Хранит данные просто как текст. Медленно парсится каждый раз.
  • jsonb (binary): Парсит JSON при вставке, удаляет пробелы, сортирует ключи и хранит в бинарном формате. Поддерживает индексы.

Аналогия: Это как Map<String, Object> в Java, который можно сохранить прямо в базу.

CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    payload JSONB -- Храним всё что угодно
);

-- Пример вставки
INSERT INTO logs (id, payload) 
VALUES (1, '{"service": "auth", "status": "error", "details": {"code": 500}}');

-- Пример выборки: Найти все логи, где сервис = auth
-- Оператор ->> возвращает текст
SELECT * FROM logs WHERE payload->>'service' = 'auth';

5. Массивы (Arrays)

Вы можете хранить списки прямо в одной ячейке. Это нарушает 1НФ (атомарность), но иногда это очень удобно и прагматично (например, список тегов к статье).

CREATE TABLE books (
    id BIGINT PRIMARY KEY,
    title TEXT,
    tags TEXT[] -- Массив текстовых строк
);

INSERT INTO books (id, title, tags) 
VALUES (1, 'Learning Java', ARRAY['programming', 'java', 'backend']);

-- Поиск: Найти книги, где есть тег 'java'
-- Оператор @> означает "содержит"
SELECT * FROM books WHERE tags @> ARRAY['java'];

Резюме 1.1:

  1. Деньги храним в NUMERIC.
  2. Строки храним в TEXT.
  3. Время всегда в TIMESTAMPTZ (хранится как UTC).
  4. Для гибких данных берем JSONB (не JSON!).
  5. Для простых списков можно использовать массивы TEXT[], не создавая лишних таблиц, но осторожно.

Тема 1.2. Функции и Операторы

SQL — это не просто язык запросов, это полноценный язык обработки данных. Часто эффективнее трансформировать данные прямо в базе (где они лежат), чем тащить миллионы "сырых" строк в Java-приложение и обрабатывать их в памяти.

1. Работа со строками (String Functions)

В Postgres работа со строками очень похожа на Java, но синтаксис лаконичнее.

  • Конкатенация: Вместо функции concat(), в Postgres чаще используют оператор ||. 
    SELECT 'Mr. ' || first_name || ' ' || last_name AS full_name 
FROM users;
    Нюанс: Если хоть одна часть NULL, результат всего выражения || будет NULL. Если нужно этого избежать, используйте concat() — он игнорирует NULL-ы.
  • Регулярные выражения (Regex): Postgres имеет мощную встроенную поддержку regex.
    • LIKE — простейшие шаблоны (%, _).
    • ~ — оператор для POSIX regex (чувствителен к регистру).
    • ~* — то же самое, но не чувствительно к регистру.
    -- Найти email'ы в домене gmail.com или yahoo.com (регистронезависимо)
SELECT email 
FROM users 
WHERE email ~* '@(gmail|yahoo)\.com$';
  • Форматирование: substring(), lower(), upper(), initcap() (делает Первая Буква Заглавной).

2. Работа с датами (Date & Time Functions)

Это "суперсила" PostgreSQL. Аналитические запросы (отчеты) строятся именно на этом.

  • Арифметика дат: В Postgres можно просто прибавлять и отнимать интервалы. 
    -- Получить дату дедлайна (сейчас + 7 дней)
SELECT now() + INTERVAL '7 days';
-- Кто зарегистрировался за последний месяц?
SELECT * FROM users 
WHERE created_at > (now() - INTERVAL '1 month');
  • date_trunc (Усечение даты): Самая важная функция для построения графиков и отчетов. Она "округляет" время вниз до заданной точности (год, месяц, день, час). Аналог в Java: LocalDateTime.truncatedTo(ChronoUnit.HOURS). 
    -- Округляем до начала месяца (2023-10-15 14:30 -> 2023-10-01 00:00)
-- Это нужно, чтобы сгруппировать продажи по месяцам.
SELECT 
    date_trunc('month', created_at) AS sale_month, 
    count(*) 
FROM orders 
GROUP BY sale_month;

3. Обработка NULL (COALESCE и NULLIF)

NULL в SQL — это "отсутствие значения". Любая математическая операция с NULL дает NULL (5 + NULL = NULL).

  • COALESCE(val1, val2, ...): Возвращает первый не-NULL аргумент. Аналог в Java: Optional.ofNullable(val).orElse(default). 
    -- Если у пользователя нет nickname, вывести "Anonymous"
SELECT username, COALESCE(nickname, 'Anonymous') 
FROM users;
  • NULLIF(val1, val2): Возвращает NULL, если аргументы равны. Иначе возвращает первый аргумент. Классический пример: Защита от деления на ноль. 
    -- Если count = 0, NULLIF вернет NULL.
-- Число / NULL даст NULL (а не ошибку "division by zero").
SELECT total_sum / NULLIF(count, 0) as average 
FROM stats;

4. Условная логика (CASE WHEN)

Это аналог if-else или switch прямо внутри SQL запроса. Позволяет трансформировать данные на лету.

SELECT 
    name,
    price,
    CASE 
        WHEN price = 0 THEN 'Free'
        WHEN price < 100 THEN 'Cheap'
        WHEN price < 500 THEN 'Medium'
        ELSE 'Expensive'
    END AS price_category
FROM products;

Пример использования в агрегации (Pivot): Допустим, мы хотим посчитать одним запросом, сколько у нас активных и заблокированных юзеров.

SELECT 
    count(*) AS total_users,
    -- Если условие верно, считаем 1, иначе NULL (count игнорирует NULL)
    count(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    count(CASE WHEN status = 'banned' THEN 1 END) AS banned_count
FROM users;

Резюме 1.2:

  • Используйте || для склейки строк и ~* для мощного поиска.
  • Используйте арифметику с INTERVAL и функцию date_trunc для работы со временем.
  • Всегда обрабатывайте потенциальные NULL через COALESCE.
  • Используйте CASE, чтобы перенести бизнес-логику категоризации данных в базу (это быстрее, чем тащить все объекты в Java и перебирать их в цикле).

Тема 1.3. Группировка и Агрегация

Если SELECT ... WHERE позволяет нам найти иголку в стоге сена, то группировка и агрегация позволяют нам взвесить этот стог, измерить его объем и понять, из чего он состоит. Это основа любой аналитики.

1. Концепция группировки (GROUP BY)

Представьте, что у вас есть таблица sales (продажи) с миллионом строк. Мы хотим узнать выручку по каждому магазину.

Оператор GROUP BY берет все строки и раскладывает их по "корзинам" (buckets) на основе значения указанной колонки. После этого база данных схлопывает каждую корзину в одну итоговую строку.

Важное правило: Если вы используете GROUP BY, то в SELECT вы можете писать только:

  1. Колонки, по которым группируете.
  2. Агрегатные функции (сумма, счетчик и т.д.). Остальные поля (например, id конкретной продажи) недоступны, так как они потеряли индивидуальность при схлопывании.
-- Считаем общую выручку и количество продаж для каждого магазина
SELECT 
    store_id,           -- Колонка группировки
    SUM(amount) as total_revenue, -- Агрегатная функция
    COUNT(*) as sales_count       -- Агрегатная функция
FROM sales
GROUP BY store_id;

2. Фильтрация групп (HAVING vs WHERE)

Это классический вопрос на собеседованиях и частая ошибка новичков.

  • WHERE: Фильтрует строки ДО группировки. ("Не учитывай в расчетах продажи меньше 100 рублей").
  • HAVING: Фильтрует результаты ПОСЛЕ группировки. ("Покажи мне только те магазины, у которых общая выручка больше миллиона").
SELECT 
    category,
    AVG(price) as avg_price
FROM products
WHERE is_active = true        -- 1. Сначала отсеиваем неактивные товары
GROUP BY category             -- 2. Потом группируем по категориям
HAVING AVG(price) > 500;      -- 3. В конце оставляем только дорогие категории

3. Стандартные агрегатные функции

  • COUNT(*): Считает количество строк (включая NULL).
  • COUNT(column): Считает количество строк, где в column не NULL.
  • SUM(column): Сумма.
  • AVG(column): Среднее значение.
  • MIN(column) / MAX(column): Минимум и максимум.

4. Специфичные функции PostgreSQL

Postgres предлагает мощные функции, которые позволяют собирать данные в списки или строки прямо в запросе. Это очень полезно, чтобы не делать N+1 запросов в Java.

string_agg (Склеивание строк)

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

SELECT 
    department_id,
    -- Аргументы: поле, разделитель
    string_agg(employee_name, ', ') as team_list
FROM employees
GROUP BY department_id;

-- Результат:
-- 1 | "Иванов, Петров, Сидоров"
-- 2 | "Смитт, Джонсон"

array_agg (Сбор в массив)

Собирает значения в реальный массив Postgres. Это удобнее для обработки в Java (JDBC вернет это как java.sql.Array).

SELECT 
    order_id,
    array_agg(product_id) as product_ids -- Получаем массив ID товаров в заказе
FROM order_items
GROUP BY order_id;

-- Результат:
-- 105 | {12, 45, 99}
-- 106 | {10, 10}

Резюме 1.3:

  1. GROUP BY создает группы строк.
  2. WHERE фильтрует исходные данные (сырье), HAVING фильтрует результат (агрегат).
  3. Используйте string_agg и array_agg, чтобы денормализовать данные "на лету" и получать удобные структуры для бэкенда.

Модуль 2: Внутреннее устройство (Internals)

===========================================

Здесь мы перестаем относиться к базе как к черному ящику. Понимание внутренностей — это ключ к ответу на вопрос: "Почему мой запрос тормозит, хотя я добавил индексы?".

Тема 2.1. Архитектура процессов и памяти

PostgreSQL устроен иначе, чем большинство Java-приложений (которые являются многопоточными, multithreaded). Postgres использует многопроцессную модель (multiprocess).

1. Модель процессов (Process Model)

Когда вы запускаете PostgreSQL, стартует не один процесс, а целая "команда".

  1. Postmaster (Main Process):
    • Это "босс". Он слушает порт (обычно 5432).
    • Он не обрабатывает запросы сам.
    • Его задача: принять соединение, породить (fork) новый рабочий процесс и передать клиента ему.
  2. Backend Processes (Workers):
    • На каждое соединение клиента создается отдельный процесс ОС (postgres: user db host...).
    • Аналогия: Это как CGI-скрипты в старом вебе. В отличие от Java (где на каждый запрос выделяется легкий поток/Thread), здесь выделяется тяжелый процесс.
    • Последствие: Создание соединения в Postgres — это дорого. Это занимает время и память (минимум 2-3 МБ на процесс, даже если он простаивает).
    • Вывод: Именно поэтому в Java обязательно нужен Connection Pool (HikariCP). Нельзя открывать соединение на каждый чих.

2. Архитектура памяти (Memory Architecture)

Память в Postgres делится на две большие зоны: Общая (Shared) и Локальная (Local).

A. Shared Memory (Общая память)

Эта память выделяется при старте сервера и доступна всем процессам сразу.

  1. Shared Buffers:
    • Это главный кэш базы данных.
    • Здесь хранятся "страницы" (pages) данных — блоки по 8 КБ, считанные с диска.
    • Когда вы делаете SELECT, Postgres сначала ищет данные здесь. Если нашел (Hit) — супер. Если нет (Miss) — идет на диск.
    • Настройка: Обычно ставят 25-40% от RAM сервера. Не больше, потому что есть еще кэш ОС.
  2. WAL Buffers:
    • Буфер для записи журнала транзакций (о нем в теме 2.4). Данные сначала пишутся сюда, а потом сбрасываются на диск.

B. Local Memory (Локальная память)

Эта память выделяется каждому бэкенд-процессу индивидуально.

  1. work_mem:
    • Память для сортировок (ORDER BY) и хэш-таблиц (JOIN, GROUP BY).
    • Опасность: Этот лимит применяется на каждую операцию внутри одного запроса.
    • Пример: Если work_mem = 10MB, а в запросе 3 сортировки и 2 джоина, один запрос может съесть 10 * 5 = 50MB. Умножьте на 100 активных пользователей — получите OOM (Out Of Memory) и падение сервера.
  2. temp_buffers:
    • Память для временных таблиц.

C. OS Cache (Кэш операционной системы)

Postgres очень "хитрый". Он не пытается кэшировать всё сам, как Oracle. Он полагается на ядро Linux.

  • Если данные не поместились в shared_buffers, они могут остаться в свободной памяти Linux (Page Cache). Чтение оттуда тоже очень быстрое.
  • Поэтому мы не отдаем всю память под shared_buffers, а оставляем "воздух" для ОС.

3. TOAST (The Oversized-Attribute Storage Technique)

Postgres хранит данные страницами по 8 КБ (8192 байта). Что делать, если вы хотите сохранить в поле TEXT статью на 1 МБ? Она физически не влезет в страницу.

Тут вступает в дело механизм TOAST:

  1. Если строка превышает ~2 КБ, Postgres нарезает длинное поле на кусочки.
  2. Эти кусочки сжимаются (алгоритм LZ4 или PGLZ).
  3. И они сохраняются в отдельную скрытую системную таблицу (TOAST-таблицу).
  4. В основной таблице остается лишь маленький "указатель" на эти данные.

Почему это важно знать:

  • SELECT * — зло. Если вы выбираете тяжелое поле description, Postgres вынужден лезть в TOAST-таблицу, распаковывать данные и отдавать их вам. Если вам нужно только id и title, не трогайте тяжелые поля — база будет летать, так как будет читать только основную таблицу.

Резюме 2.1:

  1. Postgres тяжелый на подъем: процессы вместо потоков. Нужен Connection Pool.
  2. shared_buffers — общий кэш.
  3. work_mem — память для работы запроса. Будьте осторожны с ее увеличением.
  4. Длинные тексты хранятся отдельно (TOAST) и сжимаются. Не тяните их без нужды.

Тема 2.2. MVCC (Multi-Version Concurrency Control)

Это, пожалуй, самая важная тема для понимания того, как Postgres ведет себя под нагрузкой.

MVCC (Управление параллелизмом через многоверсионность) — это механизм, который позволяет множеству транзакций работать с одними и теми же данными одновременно, не блокируя друг друга.

Главный принцип: "Читатели не блокируют писателей, а писатели не блокируют читателей". В старых базах данных, если кто-то обновлял таблицу, остальные ждали. В Postgres, если вы обновляете строку, я все равно могу прочитать её "старую" версию.

1. Как работают изменения физически

В PostgreSQL (в отличие от некоторых других БД) строки неизменяемы (immutable). Вы не можете просто взять и переписать байты на диске поверх старых.

  • INSERT: Все просто. Создается новая версия строки (tuple).
  • DELETE: Строка не удаляется физически. На нее просто вешается "бирка": "Эта строка мертва начиная с транзакции N". Но байты остаются на диске.
  • UPDATE: Это самое интересное. Для Postgres UPDATE = DELETE + INSERT.
    1. Старая версия строки помечается как "мертвая".
    2. Создается абсолютно новая версия строки с новыми данными.

Последствие: Если вы обновили 1 миллион строк, Postgres записал на диск 1 миллион новых строк, а старый миллион остался лежать мертвым грузом, занимая место.

2. Системные столбцы (xmin, xmax, ctid)

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

SELECT id, name, xmin, xmax, ctid FROM users;
  • xmin (Transaction ID Min): ID транзакции, которая создала эту версию строки.
  • xmax (Transaction ID Max): ID транзакции, которая удалила (или обновила) эту версию строки.
    • Если xmax = 0, значит строка жива и актуальна.
    • Если xmax заполнено, значит строка удалена (или обновлена), но, возможно, еще видна старым транзакциям.
  • ctid: Физический адрес строки: (номер_блока_на_диске, номер_строки_в_блоке). Например (0, 1).
    • Важно: При UPDATE ctid меняется, так как новая версия строки ложится в новое место.

Пример логики видимости: Представьте, что я начал транзакцию №100. Я делаю запрос. Postgres сканирует таблицу и решает, показывать ли мне строку, по правилам:

  1. xmin < 100? (Строка создана в прошлом?) — Да.
  2. xmax пуст ИЛИ xmax > 100? (Строка еще не удалена или удалена в будущем?) — Да. Вывод: Я вижу строку.

3. Почему COUNT(*) работает медленно

Это классическая жалоба: "Почему в таблице на 10 млн строк SELECT count(*) FROM table выполняется 5 секунд? В MyISAM это было мгновенно!"

  • Причина: Из-за MVCC Postgres не знает точного числа "живых" строк заранее.
  • В таблице может лежать 20 млн физических записей: 10 млн живых и 10 млн мертвых (после вчерашнего апдейта).
  • Более того, для транзакции А количество строк может быть 10 млн, а для транзакции Б (которая началась раньше) — 10.5 млн.
  • Итог: Postgres вынужден каждый раз сканировать таблицу (или индекс) и проверять xmin/xmax для каждой строки: "Видна ли эта строка текущему пользователю?".

4. Bloat (Раздувание)

Так как UPDATE и DELETE не освобождают место сразу, таблица со временем наполняется "мертвыми душами" (dead tuples).

  • Это явление называется Bloat.
  • Если у вас таблица занимает 10 ГБ, но полезных данных там на 1 ГБ, а 9 ГБ — это мертвые версии строк, значит ваш Bloat = 90%.
  • База начинает тормозить, потому что чтобы прочитать 1 полезную строку, ей приходится считывать с диска 9 мусорных.

Для борьбы с этим существует VACUUM.

Резюме 2.2:

  1. Postgres никогда не перезаписывает данные на месте.
  2. UPDATE порождает новую версию строки.
  3. Старые версии остаются на диске, пока их не почистят.
  4. COUNT(*) всегда пересчитывает строки вживую (Full Scan), чтобы соблюсти изоляцию транзакций.

Тема 2.3. VACUUM и Autovacuum

Если MVCC оставляет за собой "мусор" (мертвые версии строк), то VACUUM — это уборщик, который этот мусор вычищает. Без него база данных рано или поздно заполнит всё место на диске и остановится.

1. Роль VACUUM (Обычный вакуум)

Команда VACUUM (без параметров) делает следующее:

  1. Проходит по страницам таблицы.
  2. Находит мертвые версии строк (которые уже никому не видны).
  3. Помечает место, которое они занимали, как "свободное для повторного использования" (Free Space Map).

Важный нюанс: Обычный VACUUM не уменьшает размер файла на диске!

  • Аналогия: Представьте швейцарский сыр. Вакуум выедает дырки (освобождает место внутри). Размер куска сыра остается тем же, но теперь новые данные можно заливать в эти дырки.
  • Плюс: Он работает без блокировок. Ваше приложение может спокойно читать и писать в таблицу, пока идет вакуум.

2. VACUUM FULL (Полный вакуум)

Это "ядерное оружие".

  1. Создает новую копию таблицы с нуля.
  2. Переносит туда только живые данные, плотно упаковывая их.
  3. Удаляет старую таблицу.
  4. Результат: Размер файла на диске уменьшается до минимума.

Опасность:

  • Exclusive Lock: Он блокирует таблицу намертво. Никто не может даже прочитать данные, пока процесс не закончится.
  • Совет: В продакшене используйте его только в крайних случаях и только во время технического обслуживания (downtime). Вместо него часто используют утилиту pg_repack (сторонний тул), который делает то же самое, но без блокировок.

3. Visibility Map (Карта видимости)

Чтобы VACUUM не сканировал каждый раз всю таблицу (гигабайты данных), Postgres поддерживает специальную структуру — Visibility Map. Это битовая карта, где для каждой страницы данных (8 КБ) стоит флажок:

  • "На этой странице нет мертвых строк".
  • "На этой странице есть мусор".

Вакуум смотрит на карту и пропускает "чистые" страницы. Это ускоряет процесс в разы. Также эта карта позволяет делать Index Only Scan (когда Postgres берет данные прямо из индекса, не заглядывая в таблицу, потому что знает, что все строки там точно "живые").

4. Transaction ID Wraparound (Счетчик транзакций)

Это самый страшный кошмар администратора Postgres.

  • Идентификатор транзакции (xmin/xmax) — это 32-битное число.
  • Максимальное значение: ~4 миллиарда.
  • Проблема: Когда счетчик доходит до конца, он сбрасывается в 0 (закольцовывается).
  • Катастрофа: Если это произойдет, база решит, что все ваши старые данные (с ID 100, 200...) были созданы "в будущем" (так как сейчас ID снова 0), и скроет их. Данные исчезнут.

Защита (Freezing): Вакуум выполняет еще одну критическую задачу — Freezing (Заморозка). Он находит старые строки и меняет их ID на специальный "замороженный" ID. Это говорит базе: "Эта строка настолько старая, что она видна всем всегда, независимо от текущего счетчика".

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

5. Настройка Autovacuum

Autovacuum — это фоновый процесс (демон), который сам просыпается и запускает VACUUM для таблиц, где накопилось много мусора.

Стандартная проблема: Настройки по умолчанию слишком "ленивые" для высоконагруженных систем.

  • autovacuum_vacuum_scale_factor = 0.2 (20%).
    • Значение: Вакуум придет, когда изменится 20% таблицы.
    • Ситуация: У вас таблица на 100 ГБ. Вакуум придет только когда накопится 20 ГБ мусора. Удалять 20 ГБ мусора за раз — это очень долго и нагружает диск.

Best Practice (Тюнинг): Для больших таблиц настройки делают более агрессивными, чтобы убирать мусор чаще, но мелкими порциями.

-- Пример настройки для конкретной большой таблицы
ALTER TABLE big_data_table SET (
    -- Запускать вакуум, когда изменится 1% данных (а не 20%)
    autovacuum_vacuum_scale_factor = 0.01,
    
    -- ИЛИ запускать каждые 100 000 изменений (независимо от процента)
    autovacuum_vacuum_threshold = 100000
);

Резюме 2.3:

  1. VACUUM нужен для очистки мусора (после MVCC) и защиты от переполнения счетчика транзакций.
  2. Стандартный вакуум не возвращает место ОС, а оставляет его для будущих вставок.
  3. VACUUM FULL блокирует всё — избегайте его.
  4. Для больших таблиц Autovacuum нужно настраивать агрессивнее, чтобы он не приходил слишком поздно.

Тема 2.4. Write-Ahead Logging (WAL)

Это финальная часть "фундамента" Postgres. WAL (Журнал предзаписи) — это механизм, обеспечивающий букву D (Durability) в аббревиатуре ACID. Он гарантирует, что даже если сервер выдернут из розетки, подтвержденные транзакции не пропадут.

1. Концепция: "Сначала запиши в журнал"

Представьте, что изменение данных в базе (random I/O) — это поиск нужной страницы в толстой книге и исправление буквы карандашом. Это медленно. Запись в конец журнала (sequential I/O) — это просто дописывание строки на листочке. Это очень быстро.

Золотое правило WAL:

PostgreSQL никогда не меняет страницу данных на диске, пока не запишет информацию об этом изменении в WAL-лог и не сбросит его на диск.

2. Физический процесс сохранения данных

Когда вы делаете COMMIT транзакции, происходит следующее:

  1. Изменение в памяти: Изменения применяются к страницам в shared_buffers. Эти страницы становятся "грязными" (dirty pages). На диск они пока не пишутся.
  2. Запись в WAL: Описание изменения ("В блоке №100 по адресу 5 записать число 42") пишется в WAL-буфер.
  3. fsync (Сброс на диск): WAL-буфер принудительно сбрасывается в файл на диске (fsync). Только после успешного завершения этой операции Postgres говорит клиенту: "Транзакция успешна".

Почему это эффективно: Мы подменяем множество медленных произвольных записей (в разные файлы таблиц и индексов) одной быстрой последовательной записью в WAL-файл.

3. Checkpoints (Контрольные точки)

Если бы мы только писали в WAL, журнал рос бы бесконечно, а восстановление занимало бы годы. Чтобы этого не случилось, существует процесс Checkpointer.

  • Что делает: Раз в заданное время (по умолчанию 5 минут или при накоплении объема WAL) он просыпается и принудительно записывает все "грязные" страницы из shared_buffers в реальные файлы таблиц.
  • Смысл: После чекпоинта старые записи в WAL больше не нужны (данные уже надежно лежат в таблицах). Старые сегменты WAL можно удалять или переиспользовать.

Настройка (postgresql.conf): В нагруженных системах дефолтные настройки чекпоинта часто становятся причиной "фризов" (просадок производительности), так как диск внезапно нагружается записью гигабайтов данных.

  • Совет: Увеличивают max_wal_size и checkpoint_timeout, чтобы чекпоинты происходили реже, но были более "размазаны" по времени.

4. Crash Recovery (Восстановление после сбоя)

Что происходит, если питание отключилось через секунду после коммита, но до чекпоинта?

  • Данные в shared_buffers (и в файлах таблиц) потеряны или неактуальны.
  • Но запись об изменении сохранилась в WAL на диске.

При следующем старте Postgres видит, что был сбой (некорректное завершение):

  1. Он читает WAL с момента последнего успешного чекпоинта.
  2. Он проигрывает (Replay) все изменения снова.
  3. База данных приходит в состояние, которое было ровно в момент сбоя. Ни один подтвержденный байт не потерян.

Дополнительная роль WAL: Помимо надежности, WAL используется для Репликации (Тема 6.2). Мы просто передаем WAL-файлы на другой сервер, и он проигрывает их у себя, становясь точной копией мастера.

Модуль 3: Продвинутый SQL и Аналитика

=====================================

Мы переходим к тому, что отличает просто работающую базу от быстрой базы. В этом модуле мы будем учиться не просто доставать данные, а доставать их эффективно.

Тема 3.1. Индексы (Indexing)

Индекс — это избыточная структура данных, которая ускоряет чтение (SELECT), но замедляет запись (INSERT, UPDATE, DELETE), так как индекс тоже нужно обновлять.

1. B-Tree (Сбалансированное дерево) — Стандарт

В 95% случаев, когда вы пишете CREATE INDEX, создается B-Tree. Это сбалансированное дерево поиска.

  • Как это работает: Данные отсортированы. Корневой узел указывает на ветви, ветви — на листья. Листья содержат ссылку на физическую строку в таблице (ctid).
  • Сложность: Поиск одной записи занимает O(log N). Даже в таблице на миллиард строк нужно всего 4-5 "прыжков", чтобы найти данные.
  • Для чего подходит:
    • Равенство (WHERE id = 5)
    • Диапазоны (WHERE age BETWEEN 18 AND 30, WHERE salary > 5000)
    • Сортировка (ORDER BY date) — база просто читает индекс по порядку, ей не нужно сортировать данные в памяти (Sort).

2. Типы сканирования (Scan Types)

Когда вы делаете EXPLAIN, вы видите, как именно Postgres решил доставать данные.

  1. Seq Scan (Sequential Scan):
    • Читает всю таблицу подряд с диска.
    • Когда выбирается: Если таблица маленькая или если вы запрашиваете большую часть таблицы (например, > 20% строк). В таких случаях читать подряд быстрее, чем скакать по индексу.
  2. Index Scan:
    • Идет в индекс -> Находит адрес строки -> Идет в таблицу за данными.
    • Проблема: Это Random I/O (случайное чтение). Если нужно достать много строк, диск может "захлебнуться".
  3. Bitmap Heap Scan:
    • Компромисс. Сначала идет в индекс -> Собирает все адреса строк в битовую карту (в памяти) -> Сортирует их по физическому расположению -> Читает таблицу последовательно.
  4. Index Only Scan:
    • Святой Грааль производительности.
    • База берет данные прямо из индекса и даже не заглядывает в таблицу (heap).
    • Условие: Все запрашиваемые колонки должны быть в индексе + карта видимости (Visibility Map) должна подтвердить, что строки "живые".

3. Составные индексы и Правило левого префикса

Вы можете создать индекс сразу по нескольким колонкам.

CREATE INDEX idx_users_last_first ON users (last_name, first_name);

Правило левого префикса: Индекс работает как телефонная книга, отсортированная сначала по фамилии, потом по имени.

  • WHERE last_name = 'Ivanov' — Работает (ищем по фамилии).
  • WHERE last_name = 'Ivanov' AND first_name = 'Ivan' — Работает (еще эффективнее).
  • WHERE first_name = 'Ivan' — НЕ РАБОТАЕТ. Вы не можете искать в телефонной книге по имени, не зная фамилии. Индекс не будет использоваться.

4. Продвинутые техники

Индексы по выражению (Function-based indexes)

Полезно, если вы часто ищете без учета регистра, но не хотите хранить дублирующие данные.

-- Индекс хранит уже результат функции lower(email)
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Запрос будет использовать индекс:
SELECT * FROM users WHERE lower(email) = 'dmitry@example.com';

Частичные индексы (Partial indexes)

Экономит место и ускоряет поиск, если вас интересует только часть данных.

-- Индексируем только активных пользователей
-- Этот индекс будет крошечным и очень быстрым
CREATE INDEX idx_active_users ON users (created_at) WHERE status = 'active';

-- Запрос должен содержать то же условие WHERE:
SELECT * FROM users WHERE status = 'active' AND created_at > '2025-01-01';

5. Специальные типы индексов (Postgres Uniques)

В Postgres есть индексы, которых нет в других БД (например, в MySQL).

GIN (Generalized Inverted Index)

Это инвертированный индекс. Как предметный указатель в конце книги (Слово -> Список страниц).

  • Применение:
    • JSONB: Поиск ключей внутри JSON-документа.
    • Массивы: Поиск элемента в массиве.
    • Полнотекстовый поиск: Поиск слов в тексте.
-- Индекс для поиска внутри JSONB
CREATE INDEX idx_logs_payload ON logs USING GIN (payload);

-- Теперь этот запрос будет мгновенным (а не полным перебором):
SELECT * FROM logs WHERE payload @> '{"service": "auth"}';

GiST (Generalized Search Tree)

Используется для специфических данных, где нельзя просто сказать "больше" или "меньше".

  • Применение: Геометрия (PostGIS, "найти точки в радиусе 5 км"), полнотекстовый поиск.

BRIN (Block Range INdex)

Индекс для Big Data.

  • Идея: Вместо того чтобы хранить позицию каждой строки (как B-Tree), он хранит минимальное и максимальное значение для группы страниц (блока).
  • Размер: BRIN-индекс занимает килобайты, даже если таблица весит терабайты.
  • Применение: Огромные таблицы логов или временных рядов, где данные пишутся последовательно (время всегда растет).
-- Индекс для таблицы логов на 100 ТБ
CREATE INDEX idx_audit_date ON audit_logs USING BRIN (created_at);

Резюме 3.1:

  1. B-Tree — дефолтный выбор. Помните про левый префикс в составных индексах.
  2. Смотрите EXPLAIN: стремитесь к Index Scan или Index Only Scan, избегайте Seq Scan на больших таблицах.
  3. Используйте Partial Index, чтобы не индексировать мусор.
  4. Для JSONB и массивов используйте GIN.
  5. Для огромных исторических таблиц — BRIN.

Тема 3.2. Window Functions (Оконные функции)

Оконные функции — это одна из самых мощных возможностей SQL, появившаяся в стандарте SQL:2003.

Главное отличие от GROUP BY:

  • GROUP BY "схлопывает" строки. Если у вас было 100 продаж, и вы сгруппировали их по месяцам, вы получите 12 строк (по одной на месяц). Детали отдельных продаж теряются.
  • Оконные функции не меняют количество строк. Если было 100 продаж, останется 100 строк. Но к каждой строке добавляется новая колонка с вычисленным значением, которое зависит от "соседей" (окна).

1. Синтаксис: OVER()

Магия происходит внутри конструкции OVER.

SELECT 
    column_name,
    FUNCTION_NAME() OVER (
        PARTITION BY ... -- Как делить данные на группы ("окна")
        ORDER BY ...     -- Как сортировать данные внутри окна
    )
FROM table_name;

2. Ранжирование (Ranking)

Классическая задача: "Найти топ-3 зарплаты в каждом отделе". Без оконных функций это требует сложных подзапросов.

  • row_number(): Просто нумерует строки по порядку (1, 2, 3, 4...).
  • rank(): Дает одинаковым значениям одинаковый ранг, но делает пропуски (1, 1, 3...).
  • dense_rank(): Дает одинаковым значениям одинаковый ранг без пропусков (1, 1, 2...).
SELECT 
    employee_name,
    department,
    salary,
    -- Нумеруем сотрудников внутри КАЖДОГО департамента по убыванию зарплаты
    row_number() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employees;

-- Чтобы оставить только топ-3, этот запрос нужно обернуть в CTE (подзапрос),
-- так как нельзя использовать оконные функции в WHERE.

3. Аналитика: Заглядываем в будущее и прошлое

Функции LAG (предыдущий) и LEAD (следующий) позволяют обращаться к соседним строкам. Задача: Сравнить продажи текущего месяца с предыдущим и посчитать разницу.

SELECT 
    month,
    sales_amount,
    -- LAG берет значение sales_amount из предыдущей (1) строки
    LAG(sales_amount, 1) OVER (ORDER BY month) as prev_month_sales,
    
    -- Считаем рост/падение прямо тут
    sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month) as diff
FROM monthly_sales;

4. Нарастающий итог (Running Total)

Мы можем использовать обычные агрегатные функции (SUM, AVG, COUNT) как оконные. Если добавить ORDER BY внутри OVER, агрегат становится нарастающим.

Задача: Показать, как рос баланс счета транзакция за транзакцией.

SELECT 
    transaction_date,
    amount,
    -- Суммирует amount от начала времен до ТЕКУЩЕЙ строки
    SUM(amount) OVER (ORDER BY transaction_date) as running_balance
FROM transactions;

Если добавить PARTITION BY user_id, то баланс будет считаться отдельно для каждого пользователя.

5. Скользящее среднее (Moving Average)

Используется для сглаживания графиков. Можно задать рамки окна вручную: ROWS BETWEEN ....

SELECT 
    date,
    price,
    -- Средняя цена за текущий день и 2 предыдущих (3-day moving average)
    AVG(price) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as smooth_price
FROM stock_prices;

Резюме 3.2:

  1. Оконные функции добавляют аналитику к строкам, не уменьшая их количество.
  2. PARTITION BY — делит данные на независимые куски (как GROUP BY, но внутри окна).
  3. ORDER BY — определяет порядок обработки строк внутри окна (важно для row_number, lead, running total).
  4. Используйте row_number() для топов и пагинации.
  5. Используйте LAG/LEAD для поиска изменений (дельты).

Тема 3.3. CTE (Common Table Expressions)

CTE (обобщенные табличные выражения), или конструкция WITH — это способ задать временный именованный результат, который существует только в рамках одного запроса.

Если вы когда-нибудь писали запрос с тремя уровнями вложенности (SELECT ... FROM (SELECT ... FROM (SELECT ...))), вы знаете, что такой код невозможно читать и отлаживать. CTE превращает эту "лапшу" в стройный список логических шагов.

1. Простые CTE: Читаемость кода

Представьте CTE как объявление переменных или временных функций в коде.

Плохой стиль (Вложенные подзапросы):

SELECT avg(total) 
FROM (
    SELECT user_id, sum(amount) as total 
    FROM orders 
    WHERE created_at > '2023-01-01' 
    GROUP BY user_id
) as user_totals;

Хороший стиль (CTE):

WITH user_totals AS (
    -- Шаг 1: Считаем сумму для каждого юзера
    SELECT user_id, sum(amount) as total 
    FROM orders 
    WHERE created_at > '2023-01-01' 
    GROUP BY user_id
)
-- Шаг 2: Считаем среднее по полученным данным
SELECT avg(total) FROM user_totals;

Плюс: Вы можете ссылаться на user_totals несколько раз в основном запросе (например, сравнить конкретного юзера со средним), не дублируя код подзапроса.

2. Рекурсивные CTE (Recursive CTE)

Это "киллер-фича" для работы с иерархическими данными:

  • Деревья категорий (Электроника -> Телефоны -> Смартфоны).
  • Организационная структура (Директор -> Начальник отдела -> Сотрудник).
  • Ветки комментариев (Reddit style).

Рекурсивный запрос состоит из двух частей, объединенных UNION ALL:

  1. Anchor member (Якорь): Стартовая точка (корневые узлы).
  2. Recursive member (Рекурсивная часть): Запрос, который ссылается на само CTE и находит "детей".

Пример: Построение дерева сотрудников (Кто кому подчиняется) Предположим, у нас есть таблица employees с полями id, name, manager_id.

WITH RECURSIVE subordinates AS (
    -- 1. Якорь: Находим "Биг Босса" (у него нет начальника)
    SELECT 
        id, 
        name, 
        manager_id, 
        1 as level,              -- Уровень иерархии
        name::text as path       -- Путь (хлебные крошки)
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 2. Рекурсия: Ищем тех, чей начальник уже найден на предыдущем шаге
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        s.level + 1,             -- Увеличиваем уровень
        s.path || ' -> ' || e.name
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id -- JOIN с самим CTE
)
SELECT * FROM subordinates;

3. CTE и Производительность (Materialization)

До версии Postgres 12 CTE всегда были "забором" для оптимизатора (Optimization Fence). База данных обязательно выполняла CTE полностью, сохраняла результат в память, и только потом выполняла основной запрос. Это было безопасно, но иногда медленно (не работали индексы из основного запроса).

В современных версиях Postgres (12+) оптимизатор умный: он может "встроить" (inline) код CTE в основной запрос, как будто это обычный подзапрос, если это выгоднее.

Вы можете управлять этим поведением вручную:

  • WITH temp_data AS NOT MATERIALIZED (...) — заставь базу встроить код (как макрос).
  • WITH temp_data AS MATERIALIZED (...) — заставь базу выполнить это один раз и запомнить результат. Полезно, если CTE очень тяжелый, а вы обращаетесь к нему много раз.

Модуль 4: Java Integration & Transactions

=========================================

Как Senior Java разработчик, вы наверняка используете @Transactional в Spring. Но чтобы понимать, почему иногда возникают OptimisticLockException, дедлоки или данные обновляются не так, как ожидалось, нужно понимать физику транзакций в базе.

Тема 4.1. Транзакции и Изоляция

Транзакция — это единица работы. Либо всё, либо ничего.

1. ACID: Четыре столпа

  • Atomicity (Атомарность): Транзакция не может быть выполнена частично. Если упал сервер или вылетела ошибка на последнем UPDATE, откатятся (ROLLBACK) абсолютно все изменения этой транзакции.
  • Consistency (Согласованность): Транзакция переводит базу из одного валидного состояния в другое. Все Constraints (Foreign Keys, NOT NULL, CHECK) должны быть соблюдены.
  • Isolation (Изоляция): Влияние параллельных транзакций друг на друга. Об этом ниже подробно.
  • Durability (Долговечность): Если база сказала "ОК" (Commit), данные сохранены на диске (благодаря WAL), даже если через миллисекунду сервер сгорит.

2. Аномалии чтения

Чтобы понять уровни изоляции, нужно сначала понять проблемы, которые они решают.

  1. Dirty Read (Грязное чтение): Транзакция А читает данные, которые изменила транзакция Б, но Б еще не сделала COMMIT.
    • Риск: Б делает ROLLBACK, а А уже работает с неверными данными.
    • В Postgres: Невозможно. Postgres архитектурно не умеет отдавать "грязные" данные (даже на уровне Read Uncommitted).
  2. Non-repeatable Read (Неповторяющееся чтение): Транзакция А читает строку (SELECT salary WHERE id=1). Транзакция Б меняет эту строку и комитит. А снова читает строку и видит другое значение.
  3. Phantom Read (Фантомное чтение): Транзакция А читает набор строк (SELECT * WHERE salary > 100). Б добавляет новую строку с salary=200. А снова делает тот же запрос и видит новую строку ("фантома"), которой не было раньше.
    • Отличие от Non-repeatable: Там меняется содержимое существующей строки, здесь меняется количество строк в выборке.
  4. Serialization Anomaly: Результат выполнения параллельных транзакций не соответствует никакому варианту их последовательного выполнения.

3. Уровни изоляции (Isolation Levels) в PostgreSQL

В Spring вы настраиваете это через @Transactional(isolation = Isolation.X).

A. Read Committed (По умолчанию)

  • Как работает: Каждый запрос (Statement) внутри транзакции видит снимок данных на момент начала этого конкретного запроса.
  • Эффект:
    • Dirty Read: Нет.
    • Non-repeatable Read: Возможно. Если вы сделаете два одинаковых SELECT внутри одной транзакции, между ними данные могут измениться.
    • Phantom Read: Возможно.
  • Когда использовать: В 90% случаев. Это стандарт.

B. Repeatable Read

  • Как работает: Вся транзакция видит снимок данных на момент начала первого запроса в этой транзакции.
  • Эффект: Вы работаете в "замороженном времени". Даже если другие меняют данные, вы видите старую версию.
  • Особенность Postgres: В стандарте SQL этот уровень допускает Фантомы. В PostgreSQL реализация Repeatable Read предотвращает и Фантомы тоже. Это строже стандарта.
  • Ошибка сериализации: Если две транзакции Repeatable Read попытаются обновить одну и ту же строку, вторая упадет с ошибкой: ERROR: could not serialize access due to concurrent update. Приложение должно поймать это и повторить транзакцию (Retry).

C. Serializable

  • Как работает: Эмуляция того, что транзакции выполняются строго по очереди (одна за другой), хотя физически они идут параллельно.
  • Цена: Используются специальные "предикатные блокировки" (SIReadLock). Очень высокий шанс получить ошибку сериализации и необходимость ретраев.
  • Когда использовать: Только для критически важных финансовых операций, где несогласованность недопустима ни при каких обстоятельствах. Сильно бьет по производительности.

4. Практический пример конфликта (Lost Update)

Это то, с чем вы столкнетесь в Java. Два менеджера одновременно редактируют товар.

  1. Менеджер А читает: Количество = 10.
  2. Менеджер Б читает: Количество = 10.
  3. А продает 2 шт, пишет 8. Commit.
  4. Б продает 1 шт, пишет 9. Commit.

Итог: На складе 7 товаров (10 - 2 - 1). В базе записано 9. Продажа А "потерялась".

Решения:

  1. Атомарный апдейт: UPDATE goods SET count = count - 2 ... (Работает на Read Committed).
  2. Pessimistic Locking (SELECT FOR UPDATE): Блокируем строку на чтении (см. тему 4.2).
  3. Optimistic Locking (Java level): Поле @Version в Hibernate. При сохранении проверяется, не изменилась ли версия.

Тема 4.2. Блокировки (Locking)

Управление конкурентным доступом в Postgres строится не только на MVCC (которое позволяет читать, не блокируя писателей), но и на системе блокировок, которая предотвращает хаос при одновременной записи.

1. Блокировки уровня таблицы (Table-level locks)

Эти блокировки затрагивают всю таблицу целиком.

  • Явные: Вы можете вызвать их командой LOCK TABLE users ..., но в разработке приложений это делается крайне редко.
  • Неявные: Postgres сам накладывает их при выполнении команд.
    • ACCESS SHARE: Накладывается при SELECT. Совместима почти со всем, кроме удаления таблицы.
    • ROW EXCLUSIVE: Накладывается при UPDATE, DELETE, INSERT. Позволяет другим читать таблицу и даже менять другие строки, но запрещает менять структуру таблицы (DDL).
    • ACCESS EXCLUSIVE: Самая жесткая блокировка. Накладывается при ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL.
      • Эффект: Никто не может ни читать, ни писать в таблицу, пока транзакция не завершится.
      • Опасность: Если вы запустите миграцию ALTER TABLE users ADD COLUMN... на проде в час пик, всё приложение "встанет", ожидая доступа к таблице users. (В Postgres 11+ добавление колонки с дефолтным значением оптимизировано и блокирует таблицу лишь на доли секунды, но старые версии или сложные ALTER могут быть долгими).

2. Блокировки уровня строки (Row-level locks)

Это то, с чем мы работаем чаще всего для предотвращения состояния гонки (Race Condition).

SELECT FOR UPDATE

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

Решение: Пессимистическая блокировка.

BEGIN;

-- Мы не просто читаем строку, мы "захватываем" её.
-- Если другая транзакция попытается изменить эту строку ИЛИ сделать SELECT FOR UPDATE,
-- она "повиснет" и будет ждать, пока мы не сделаем COMMIT или ROLLBACK.
SELECT balance FROM wallets WHERE user_id = 1 FOR UPDATE;

-- ... тут логика приложения на Java (проверка if balance > 100) ...

UPDATE wallets SET balance = balance - 100 WHERE user_id = 1;

COMMIT; -- Тут блокировка снимается автоматически.

Вариации:

  • FOR SHARE: Блокирует изменение строки другими, но позволяет другим читать её.
  • NOWAIT: "Попытайся заблокировать. Если занято — не жди, а сразу падай с ошибкой".
  • SKIP LOCKED: "Дай мне те строки, которые никем не заняты". Идеально для реализации очередей задач на базе SQL (несколько воркеров разбирают задачи, не конфликтуя).

3. Deadlocks (Взаимные блокировки)

Классическая проблема: "Два барана на мосту".

  1. Транзакция А блокирует строку 1.
  2. Транзакция Б блокирует строку 2.
  3. Транзакция А пытается заблокировать строку 2 (и ждет Б).
  4. Транзакция Б пытается заблокировать строку 1 (и ждет А).

Итог: Они будут ждать друг друга вечно.

Postgres имеет детектор дедлоков. Он просыпается (по умолчанию через 1 секунду ожидания), видит кольцевую зависимость и убивает одну из транзакций с ошибкой deadlock detected, чтобы спасти вторую.

Как избегать:

  1. Порядок блокировок: Всегда блокируйте ресурсы в одном и том же порядке (например, сортируйте ID перед обновлением пачки записей). Если все идут от меньшего ID к большему, дедлок невозможен.
  2. Минимизация времени: Держите транзакции короткими. Не делайте HTTP-вызовы или сложные вычисления внутри открытой транзакции с блокировками.

4. Advisory Locks (Рекомендательные блокировки)

Это уникальная фича Postgres. Это блокировки "понарошку", которые не привязаны к физическим таблицам или строкам. Вы блокируете абстрактное число.

  • Сценарий: У вас есть распределенное Java-приложение (10 инстансов). Вам нужно, чтобы тяжелый отчет генерировал только один инстанс одновременно.
  • Решение: 
    -- Пытаемся захватить "виртуальный замок" с ID 12345
-- Вернет true, если захватили. false, если занято.
SELECT pg_try_advisory_lock(12345);
    Это работает быстрее и проще, чем Redis или ZooKeeper, если у вас уже есть Postgres.

Резюме 4.2:

  1. Осторожно с ALTER TABLE — это эксклюзивная блокировка.
  2. Используйте SELECT FOR UPDATE для защиты финансовых операций (Pessimistic Locking).
  3. Сортируйте ID при массовых обновлениях, чтобы избежать Deadlocks.
  4. Используйте SKIP LOCKED для очередей и Advisory Locks для синхронизации распределенных процессов.

Тема 4.3. JDBC & Connection Pooling

Это тема, где архитектура базы данных встречается с реальностью Java-приложения.

1. Почему создание соединения — это дорого?

Как мы обсуждали в Теме 2.1, PostgreSQL использует процессную модель.

Когда ваше Java-приложение делает DriverManager.getConnection():

  1. Сеть: TCP 3-way handshake (установка соединения).
  2. Postgres: Postmaster делает fork() (клонирует процесс) — это операция ОС, она не мгновенная.
  3. Память: Новый процесс выделяет себе память (Kernel memory + shared buffers mapping).
  4. Аутентификация: Проверка пароля, SSL handshake.
  5. Инициализация: Загрузка кешей, настроек сессии.

Итог: Создание соединения может занимать от 10 до 100+ мс. Если делать это на каждый HTTP-запрос (который должен отвечать за 50 мс), приложение будет работать отвратительно медленно.

2. Пул соединений на стороне приложения (HikariCP)

В мире Java стандартом де-факто является HikariCP (он встроен в Spring Boot по умолчанию).

Принцип работы: HikariCP создает, например, 10 соединений на старте и держит их открытыми вечно.

  • Приложение: "Дай соединение!" -> Hikari: "Вот тебе готовое из коробки".
  • Приложение: "Я всё (close)" -> Hikari: "Ок, я не закрываю его реально, а просто кладу обратно в коробку".

Best Practices настройки HikariCP:

  1. maximumPoolSize: Самая важная настройка.
    • Миф: "Чем больше, тем лучше. Поставлю 100, чтобы всем хватило".
    • Реальность: Процессор базы имеет ограниченное число ядер. Если у вас 4 ядра и 100 активных соединений, они будут драться за процессорное время (Context Switching), и производительность упадет.
    • Формула: Количество ядер * 2 + эффективное число шпинделей (дисков).
    • Совет: Для большинства нагрузок пул размером 10-20 работает быстрее, чем пул 100.
  2. connectionTimeout: Сколько ждать свободного соединения, если пул пуст.
    • Ставьте разумное значение (например, 2-5 секунд), чтобы быстро падать (Fail Fast), если база легла, а не висеть 30 секунд.
  3. leakDetectionThreshold: Позволяет найти места в коде, где вы забыли закрыть соединение (connection leak).

3. PgBouncer: Пул на стороне базы (Middleware)

Иногда пула в Java недостаточно.

  • Проблема: У вас микросервисная архитектура. 50 микросервисов, каждый запущен в 20 экземплярах (Kubernetes pods).
  • Математика: 50 * 20 = 1000 подов. Если каждый под откроет хотя бы 10 соединений (Hikari), к базе придет 10,000 соединений. Postgres умрет от оверхеда на переключение контекста процессов, даже если запросов мало.

Решение: Ставим между сервисами и базой PgBouncer. Это легкий прокси. Он может держать 10,000 входящих соединений от Java, но использовать всего 100 реальных соединений к Postgres.

Режимы работы PgBouncer:

  1. Session Pooling (По умолчанию):
    • Клиент получает соединение на всё время жизни сессии (пока не отключится).
    • Плюс: Поддерживает все фичи SQL (временные таблицы, SET variables).
    • Минус: Не решает проблему 10,000 висящих клиентов, если они не закрывают соединения.
  2. Transaction Pooling (Агрессивный):
    • Клиент получает реальное соединение к базе только на время транзакции (BEGIN ... COMMIT).
    • Как только транзакция завершена, соединение отбирается и отдается другому клиенту, даже если Java-клиент думает, что соединение еще открыто.
    • Эффект: Можно обслужить 10,000 клиентов с помощью 50 соединений к базе.
    • Ограничения: Нельзя использовать Prepared Statements (в старых версиях), сессионные переменные (SET my_var = 1), временные таблицы, так как следующая транзакция может уйти в другое соединение.

Модуль 5: Оптимизация производительности (Query Tuning)

=======================================================

Добро пожаловать в "высшую лигу". Здесь мы перестаем гадать, почему запрос медленный, и начинаем читать мысли планировщика (Query Planner).

Тема 5.1. EXPLAIN

Команда EXPLAIN — это рентген для вашего SQL-запроса. Она показывает План выполнения (Query Plan) — пошаговую инструкцию, которую составила база данных для получения результата.

1. Синтаксис и Виды

Есть два основных режима работы:

  1. EXPLAIN (Теоретический):
    • База не выполняет запрос.
    • Она только прикидывает, как бы она его выполняла.
    • Плюс: Мгновенно. Безопасно для DELETE/UPDATE.
    • Минус: Показывает только догадки (estimates), которые могут отличаться от реальности.
  2. EXPLAIN ANALYZE (Практический):
    • База реально выполняет запрос.
    • Замеряет точное время на каждом этапе.
    • Плюс: Показывает правду.
    • Минус: Запрос нагружает базу.
    • ОПАСНОСТЬ: Если вы напишете EXPLAIN ANALYZE DELETE ..., данные реально удалятся!
    • Лайфхак: Оборачивайте в транзакцию с откатом: 
      BEGIN;
EXPLAIN ANALYZE DELETE FROM users WHERE id < 100;
ROLLBACK; -- Данные спасены
  3. EXPLAIN (ANALYZE, BUFFERS) (Профессиональный):
    • Показывает не только время, но и сколько страниц (блоков по 8 КБ) было прочитано из памяти (shared hit) и сколько с диска (read). Это лучший метрика нагрузки.

2. Структура Плана

План — это дерево узлов (Nodes). Читать его нужно снизу вверх и изнутри наружу.

Пример вывода:

QUERY PLAN
------------------------------------------------------------------------------------------------
 Aggregate  (cost=45.50..45.51 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=1)
   ->  Hash Join  (cost=15.00..40.00 rows=1000 width=4) (actual time=0.020..0.035 rows=950 loops=1)
         Hash Cond: (orders.user_id = users.id)
         ->  Seq Scan on orders  (cost=0.00..20.00 rows=2000 width=8) (...)
         ->  Hash  (cost=10.00..10.00 rows=500 width=4) (...)
               ->  Seq Scan on users  (cost=0.00..10.00 rows=500 width=4) (...)

Как это читать (снизу вверх):

  1. Seq Scan on users: Сначала база просканировала таблицу юзеров.
  2. Hash: Построила из них Хеш-таблицу (в памяти).
  3. Seq Scan on orders: Просканировала таблицу заказов.
  4. Hash Join: Соединила заказы с хеш-таблицей юзеров.
  5. Aggregate: Посчитала итоговый результат.

3. Расшифровка метрик (Cost vs Actual)

В каждой строке плана вы видите цифры. Понимать их — критически важно.

A. Cost (Стоимость) — cost=0.00..20.00

Это абстрактные "попугаи", в которых планировщик оценивает сложность.

  • 0.00 (Startup Cost): Сколько "попугаев" нужно, чтобы получить первую строку. Для Seq Scan это 0 (начать читать можно сразу). Для Sort это много (нужно сначала все отсортировать, прежде чем отдать первую строку).
  • 20.00 (Total Cost): Сколько стоит получить все строки.
  • Из чего складывается: Чтение страницы с диска стоит (по умолчанию) 1.0, обработка строки процессором — 0.01 и т.д.

B. Rows (Строки) — rows=1000

Это прогноз планировщика: "Я думаю, что этот узел вернет 1000 строк".

C. Actual Time — actual time=0.020..0.035

Появляется только в EXPLAIN ANALYZE.

  • Время в миллисекундах.
  • Первое число: время до получения первой строки.
  • Второе число: время до получения последней строки.

D. Actual Rows — rows=950

Это факт: сколько строк реально вернул узел.

4. Где искать проблемы?

Сравнивайте Estimated Rows (прогноз) и Actual Rows (факт).

  • Если цифры совпадают (1000 vs 950): Планировщик молодец, статистика актуальна, он выбрал оптимальный алгоритм.
  • Если цифры расходятся в разы (1 vs 10000): Это катастрофа.
    • Планировщик думал, что строк будет мало, и выбрал алгоритм Nested Loop (хорош для малого числа данных).
    • А строк оказалось 10 тысяч. Nested Loop захлебнулся.
    • Причина: Устаревшая статистика.
    • Лечение: Запустить ANALYZE table_name.

Резюме 5.1:

  1. Всегда смотрите план, если запрос тормозит.
  2. EXPLAIN — быстро, EXPLAIN ANALYZE — точно (но выполняет запрос!).
  3. Используйте BUFFERS, чтобы видеть нагрузку на память/диск.
  4. Главный враг производительности — разница между rows (estimate) и rows (actual).

Тема 5.2. Статистика

Откуда планировщик (Query Planner) знает, что в таблице users ровно 1000 строк, где country = 'Russia', не читая саму таблицу? Он смотрит в статистику.

Статистика — это метаданные о распределении значений в ваших колонках. Это "шпаргалка", по которой планировщик строит математическую модель стоимости запроса.

1. Как это работает (Математика)

Когда вы пишете WHERE age > 25, планировщик не бежит проверять данные. Он делает вероятностную оценку:

  1. Смотрит в статистику колонки age.
  2. Видит гистограмму распределения.
  3. Считает: "Ага, значения от 25 до 100 составляют примерно 30% от всех данных".
  4. Умножает 30% на общее число строк в таблице.
  5. Получает rows estimate.

Если статистика врет (устарела), планировщик примет неверное решение (например, выберет Seq Scan вместо индекса), и запрос будет работать в 100 раз медленнее.

2. Таблица pg_statistic и представление pg_stats

Сырые данные хранятся в системной таблице pg_statistic, но она нечитаема для людей. Для нас есть удобное представление pg_stats.

Вы можете сами посмотреть, что база знает о ваших данных:

SELECT 
    tablename, 
    attname AS column_name, 
    null_frac,             -- Доля NULL значений (например, 0.1 = 10% пустых)
    n_distinct,            -- Количество уникальных значений
    most_common_vals,      -- Топ самых частых значений (для частых элементов)
    most_common_freqs,     -- Частота этих значений
    histogram_bounds       -- Границы корзин гистограммы (для диапазонов > <)
FROM pg_stats 
WHERE tablename = 'orders';

Важные метрики:

  • n_distinct: Если число отрицательное (например, -0.5), это значит "50% строк уникальны". Если положительное (например, 5), это значит "всего 5 уникальных вариантов" (как enum: Male, Female, Other...).

3. Команда ANALYZE

Эта команда заставляет Postgres пересчитать статистику. Она не читает всю таблицу (это было бы долго). Она берет случайную выборку (сэмплирование) — обычно 30 000 строк (настраивается), и по ним строит картину мира.

  • Autovacuum: Обычно запускает ANALYZE автоматически после изменения определенного % данных.
  • Ручной запуск: Обязательно запускайте ANALYZE table_name, если вы:
    1. Только что залили много данных (Bulk Insert).
    2. Создали индекс (по индексу тоже собирается статистика).
    3. Видите в EXPLAIN, что rows estimate сильно врет.

4. Проблема коррелированных колонок (Cross-Column Statistics)

Это классическая ловушка, где стандартная статистика ломается.

Представьте запрос:

SELECT * FROM locations WHERE city = 'Paris' AND country = 'France';

Планировщик считает вероятности независимо:

  1. Вероятность city = 'Paris' = 0.001 (1 из 1000).
  2. Вероятность country = 'France' = 0.1 (1 из 10).
  3. Итоговая вероятность (как в школе): 0.001 * 0.1 = 0.0001.

Реальность: Если город — Париж, то страна — обязательно Франция. Эти колонки жестко связаны (коррелируют). Реальная вероятность должна быть равна вероятности самого Парижа (0.001). Итог: Планировщик занизил ожидаемое число строк в 10 раз.

Решение (Extended Statistics): В новых версиях Postgres (10+) можно научить базу видеть эту связь:

-- Создаем расширенную статистику на зависимость колонок
CREATE STATISTICS st_city_country (dependencies) ON city, country FROM locations;

-- Собираем данные
ANALYZE locations;

Теперь планировщик поймет, что эти колонки идут парой, и оценка rows будет точной.

Резюме 5.2:

  1. Планировщик слеп без статистики.
  2. Проверяйте pg_stats, чтобы понять, видит ли база перекосы в данных (data skew).
  3. Если залили данные — сделайте ANALYZE.
  4. Если колонки логически связаны (Модель машины + Марка, Город + Страна), используйте CREATE STATISTICS.

Тема 5.3. Оптимизация JOINs

Вы написали SELECT * FROM A JOIN B ON A.id = B.id. Но как именно база данных физически соединит эти данные? Postgres выбирает один из трех алгоритмов, основываясь на стоимости и статистике. Понимание этих алгоритмов — ключ к пониманию того, почему запрос "висит".

1. Nested Loop (Вложенный цикл)

Самый простой и интуитивный алгоритм.

  • Как работает: 
    for (Row outer : tableA) {           // Внешний цикл
    for (Row inner : tableB) {       // Внутренний цикл
        if (outer.id == inner.id) {
            emit(outer, inner);
        }
    }
}
  • Оптимизация: Если на внутренней таблице (tableB) есть индекс, то вместо полного перебора внутренний цикл превращается в быстрый поиск по индексу (Index Scan).
  • Когда эффективен:
    • Когда одна из таблиц очень маленькая (или после фильтрации WHERE осталось мало строк).
    • Это основной алгоритм для быстрых OLTP-запросов (например, найти юзера и его профиль).
  • Проблема: Если обе таблицы большие (10k * 10k), это приведет к 100 миллионам операций. Процессор "сгорит".

2. Hash Join (Хеш-соединение)

Алгоритм для обработки больших массивов данных без индексов.

  • Как работает:
    1. Build Phase: База берет меньшую таблицу и целиком строит из нее Хеш-таблицу в оперативной памяти (work_mem). Ключ хеша — поле соединения (id).
    2. Probe Phase: База сканирует большую таблицу, для каждой строки считает хеш и проверяет, есть ли совпадение в хеш-таблице.
  • Сложность: $O\left(N+M\right)$ . Линейная зависимость! Это намного быстрее квадратичной сложности Nested Loop на больших объемах.
  • Требования:
    • Работает только для условий равенства (ON a.id = b.id). Не работает для >, <.
    • Требует памяти. Если хеш-таблица не влезает в work_mem, она начинает сбрасываться на диск (temp files), и производительность резко падает.
  • Когда эффективен: Тяжелая аналитика, отчеты, отсутствие индексов.

3. Merge Join (Соединение слиянием)

Алгоритм "застежки-молнии".

  • Как работает:
    1. Сначала сортирует обе таблицы по ключу соединения (если они еще не отсортированы индексом).
    2. Ставит указатели на начало обоих списков.
    3. Двигает указатели вниз, сопоставляя значения. Как только значения совпали — соединяет.
  • Когда эффективен:
    • Когда таблицы уже отсортированы (например, соединяем по Primary Key или по полю, где есть B-Tree индекс). В этом случае это самый быстрый алгоритм, не требующий памяти под хеш-таблицу.
    • Для условий диапазона (range joins).

Практическое задание (Модуль 5)

Задача: Взять "Дневник снов", сгенерировать 1 млн записей, написать запрос поиска по тексту сна и оптимизировать его с помощью GIN-индекса.

Выполните этот скрипт у себя (или проанализируйте его здесь), чтобы увидеть разницу в 1000 раз.

1. Подготовка данных (1 млн строк)

-- Создаем таблицу
CREATE TABLE dreams (
    id SERIAL PRIMARY KEY,
    dream_text TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Генерируем 1 000 000 записей (это займет пару секунд)
-- Используем md5 для генерации случайного "мусора" в тексте
INSERT INTO dreams (dream_text)
SELECT 
    'I had a dream about ' || md5(random()::text) || ' and then I saw a ' || 
    CASE WHEN random() < 0.001 THEN 'unicorn' ELSE 'cat' END -- "Единорог" будет редким словом
FROM generate_series(1, 1000000);

2. Медленный поиск (Seq Scan) Пытаемся найти сны про "единорога" с помощью стандартного LIKE.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM dreams WHERE dream_text LIKE '%unicorn%';
  • Результат:
    • Seq Scan: База читает 1 млн строк с диска.
    • Execution Time: ~200-500 мс (зависит от железа).
    • Buffers: Прочитано ~15 000 страниц.

3. Оптимизация (Full Text Search + GIN) LIKE '%...%' (с процентом в начале) нельзя ускорить обычным B-Tree индексом. Нам нужен полнотекстовый поиск.

-- Добавляем колонку tsvector (лексемы слов)
ALTER TABLE dreams ADD COLUMN search_vector tsvector;

-- Заполняем её (превращаем текст в вектор)
UPDATE dreams SET search_vector = to_tsvector('english', dream_text);

-- Создаем GIN индекс
CREATE INDEX idx_dreams_search ON dreams USING GIN(search_vector);

-- АНАЛИЗИРУЕМ (обновляем статистику)
ANALYZE dreams;

4. Быстрый поиск (Bitmap Heap Scan) Используем оператор @@ (match) вместо LIKE.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM dreams 
WHERE search_vector @@ to_tsquery('english', 'unicorn');
  • Результат:
    • Bitmap Heap Scan (через GIN индекс).
    • Execution Time: ~0.5 - 2 мс.
    • Ускорение: В ~200-500 раз!

Модуль 6: Администрирование и Архитектура (Expert)

==================================================

Мы выходим на уровень System Design. Как Senior-разработчик, вы не обязаны быть DBA, но вы обязаны понимать, как обеспечить отказоустойчивость (HA) и масштабируемость вашей системы, чтобы не совершить архитектурных ошибок еще на этапе проектирования.

Тема 6.1. Конфигурация сервера (postgresql.conf)

"Из коробки" PostgreSQL настроен крайне консервативно, чтобы запуститься даже на микроволновке. Для продакшена эти настройки (в файле postgresql.conf) нужно менять обязательно.

1. Память (Memory Tuning)

  • shared_buffers:
    • Сколько памяти Postgres забирает себе под кэш страниц при старте.
    • Рекомендация: 25% - 40% от RAM.
    • Почему не 100%? Postgres полагается на кэш файловой системы (OS Cache). Если вы отдадите базе всё, ОС начнет свопить, и производительность умрет.
  • work_mem:
    • Лимит памяти на одну операцию (сортировку, хеш-таблицу) внутри запроса.
    • Ловушка: Это значение умножается на количество параллельных операций.
    • Пример: work_mem = 50MB. Пришло 100 юзеров, каждый делает сложный запрос с 2 сортировками. Потребление: 100 * 2 * 50MB = 10GB. OOM Killer убьет базу.
    • Рекомендация: Начинайте с 4MB-16MB. Поднимайте только для конкретных сессий/юзеров.
  • maintenance_work_mem:
    • Память для служебных операций (VACUUM, CREATE INDEX).
    • Рекомендация: Ставьте больше (например, 1GB), чтобы индексы строились быстрее и вакуум работал эффективнее.

2. Диск и SSD (Storage Tuning)

  • random_page_cost:
    • Стоимость случайного чтения. По умолчанию 4.0 (наследие эры HDD, где головке диска нужно время на перемещение).
    • Для SSD: Обязательно меняйте на 1.1. Это скажет планировщику: "Случайное чтение (Index Scan) почти так же дешево, как последовательное (Seq Scan)". База начнет чаще использовать индексы.
  • effective_io_concurrency:
    • Сколько параллельных запросов к диску может делать система. Для SSD ставьте 200+.

3. Observability (Наблюдаемость)

Чтобы видеть медленные запросы, обязательно включите расширение pg_stat_statements. В postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

После этого вы сможете делать SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC и видеть топ самых тяжелых запросов в системе.

Тема 6.2. Репликация и High Availability (HA)

Один сервер — это точка отказа (SPOF). Если он сгорит, бизнес встанет.

1. Физическая потоковая репликация (Physical Streaming Replication)

Это стандарт индустрии.

  • Master (Primary): Принимает запись (INSERT/UPDATE) и чтение. Генерирует WAL-лог.
  • Slave (Standby/Replica): Подключается к мастеру, получает поток WAL-записей и применяет их у себя.
  • Режим: Replica всегда находится в режиме Read-Only. Вы можете перенаправить на нее аналитические запросы (отчеты), чтобы разгрузить мастер.

2. Синхронная vs Асинхронная

  • Асинхронная (Default):
    • Мастер подтверждает транзакцию (COMMIT), как только записал её на свой диск. В фоне данные летят на реплику.
    • Риск: Если мастер сгорит, последние миллисекунды данных могут не успеть долететь до реплики.
    • Плюс: Максимальная скорость.
  • Синхронная (synchronous_commit = on):
    • Мастер ждет, пока реплика скажет: "Я получила и записала данные". Только потом отдает "ОК" клиенту.
    • Риск: Если реплика упала или сеть лагает, весь мастер встает колом на запись.
    • Плюс: RPO = 0 (Zero Data Loss).

3. Failover и Patroni

Postgres сам не умеет автоматически переключать мастера. Если мастер упал, кто-то должен сказать реплике: "Теперь ты главный". Для этого используют Patroni (на базе etcd/Consul).

  • Это "робот-админ", который следит за здоровьем кластера.
  • Если мастер исчез, Patroni проводит выборы, назначает новую реплику мастером и переписывает DNS/Config, чтобы приложение подключилось к новому лидеру.

4. Логическая репликация (Logical Replication)

Позволяет реплицировать не весь сервер (байты), а конкретные таблицы (данные).

  • Use Case: ETL, переливка данных в аналитическое хранилище, синхронизация между микросервисами (CDC — Change Data Capture).

Тема 6.3. Бэкапы и PITR

"Репликация — это не бэкап". Если вы сделаете DROP TABLE на мастере, он мгновенно реплицируется и удалит таблицу на слейве.

1. Логические бэкапы (pg_dump)

  • Создает текстовый файл с SQL-командами (CREATE TABLE... INSERT...).
  • Плюс: Можно восстановить одну таблицу; можно перенести данные между разными ОС или мажорными версиями Postgres.
  • Минус: Очень медленное восстановление (нужно заново прогнать все инсерты и построить индексы). На базах 100GB+ практически неприменимо для Disaster Recovery.

2. Физические бэкапы (pg_basebackup, pgBackRest)

  • Копирует файлы базы данных побитово.
  • Плюс: Быстрое восстановление (просто скопировал файлы и запустил).
  • Инструмент: pgBackRest — золотой стандарт. Умеет делать инкрементальные бэкапы (копировать только то, что изменилось), сжимать данные и лить их в S3.

3. PITR (Point-In-Time Recovery)

Машина времени. Позволяет восстановить состояние базы на любую секунду в прошлом.

  • Как работает: Берем последний полный физический бэкап (например, ночной) + накатываем поверх него архив WAL-логов до нужной временной метки (14:59:59).
  • Сценарий: Разработчик случайно удалил важные данные, но заметил это через час. Откатываемся на час назад.

Тема 6.4. Partitioning & Sharding

Что делать, если в одной таблице orders стало 100 миллиардов строк и 10 ТБ данных? Индексы перестают влезать в память, вакуум работает вечность.

1. Партиционирование (Partitioning)

Разбиение одной логической таблицы на несколько физических кусков на одном сервере.

  • Декларативное партиционирование: Вы говорите базе: "Дели таблицу orders по дате".
    • orders_2023_01
    • orders_2023_02
  • Partition Pruning: Если вы делаете запрос WHERE date = '2023-01-15', планировщик поймет, что нужно читать только таблицу orders_2023_01, игнорируя остальные терабайты данных.
  • Удобство администрирования: Чтобы удалить старые данные, не нужно делать долгий DELETE (который плодит мусор). Можно просто сделать DROP TABLE orders_2020 (мгновенно освобождает место).

2. Шардинг (Sharding)

Разбиение данных по разным физическим серверам. PostgreSQL "из коробки" не умеет делать это прозрачно (как MongoDB).

  • Citus: Расширение, превращающее Postgres в распределенную базу данных.
  • Application-side sharding: Вы сами в Java-коде решаете: "User ID 1-1000 идут на сервер А, 1001-2000 на сервер Б". Это сложно поддерживать, но дает бесконечную масштабируемость.

Финал обучения

Что мы охватили:

  1. Фундамент: Нормализация, SQL, Constraints.
  2. Инструменты: Типы данных, JSONB, Агрегация.
  3. Внутренности: MVCC, WAL, VACUUM, Buffer Cache.
  4. Аналитика: Индексы (B-Tree, GIN), Оконные функции, EXPLAIN.
  5. Java + БД: Транзакции, Изоляция, Connection Pooling.
  6. Expert: Настройка сервера, Репликация, Партиционирование.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment