Базы данных и проектирование схемы

Правильно спроектированная база данных — это фундамент, на котором держится всё приложение

#Почему это важно

Представьте: ваш стартап вырос с 100 до 100 000 пользователей. Приложение, которое раньше работало мгновенно, теперь отвечает по 5-10 секунд. Логи показывают медленные запросы к базе данных. Вы смотрите на EXPLAIN ANALYZE и видите полное сканирование таблиц с миллионами строк.

Правильное проектирование базы данных с самого начала экономит сотни часов на оптимизацию и миграцию данных.

В этой теме разберём:

Нормализация данных (1NF, 2NF, 3NF)
Индексы и когда их использовать
Транзакции и ACID свойства
Уровни изоляции
Оптимизация запросов и EXPLAIN ANALYZE
Foreign keys и constraints
Денормализация для производительности
Шардирование и репликация

#1. Нормализация данных

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

#1.1. Первая нормальная форма (1NF)

Требования 1NF:

Каждая колонка содержит атомарные (неделимые) значения
Нет повторяющихся групп
У каждой строки есть уникальный идентификатор (PRIMARY KEY)

-- Плохо: не атомарные значения, повторяющиеся группы
CREATE TABLE users_bad (
    id INTEGER,
    name TEXT,
    phones TEXT,  -- "555-1234,555-5678" — несколько значений в одной ячейке
    favorite_colors TEXT  -- "red,blue,green"
);

-- Хорошо: атомарные значения
CREATE TABLE users_1nf (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE user_phones (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users_1nf(id),
    phone TEXT  -- Одно значение
);

CREATE TABLE user_favorite_colors (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users_1nf(id),
    color TEXT  -- Одно значение
);

#1.2. Вторая нормальная форма (2NF)

Требования 2NF:

Таблица уже в 1NF
Все неключевые атрибуты полностью зависят от первичного ключа (нет частичных зависимостей)

Частичная зависимость возникает, когда неключевой атрибут зависит только от части составного ключа.

-- Плохо: частичная зависимость
-- order_items зависит только от order_id, а не от (order_id, product_id)
CREATE TABLE order_items_bad (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,  -- Зависит только от order_id!
    customer_name TEXT,  -- Зависит только от order_id!
    PRIMARY KEY (order_id, product_id)
);

-- Хорошо: выносим зависимости в отдельные таблицы
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date DATE,
    customer_name TEXT
);

CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

#1.3. Третья нормальная форма (3NF)

Требования 3NF:

Таблица уже в 2NF
Нет транзитивных зависимостей (неключевые атрибуты не зависят друг от друга)

Транзитивная зависимость: A → B → C, где C зависит от B, а B зависит от A.

-- Плохо: транзитивная зависимость
-- city зависит от postal_code, а postal_code зависит от user_id
CREATE TABLE users_2nf (
    user_id INTEGER PRIMARY KEY,
    name TEXT,
    postal_code TEXT,
    city TEXT,  -- Зависит от postal_code, а не напрямую от user_id
    country TEXT  -- Тоже зависит от postal_code
);

-- Хорошо: выносим транзитивную зависимость
CREATE TABLE users_3nf (
    user_id INTEGER PRIMARY KEY,
    name TEXT,
    postal_code TEXT REFERENCES postal_codes(code)
);

CREATE TABLE postal_codes (
    code TEXT PRIMARY KEY,
    city TEXT,
    country TEXT
);

#1.4. Пример полной нормализации

Рассмотрим пример интернет-магазина:

-- Денормализованная таблица (плохо)
CREATE TABLE orders_denormalized (
    order_id INTEGER,
    order_date DATE,
    customer_id INTEGER,
    customer_name TEXT,
    customer_email TEXT,
    customer_address TEXT,
    product_id INTEGER,
    product_name TEXT,
    product_price DECIMAL,
    category_id INTEGER,
    category_name TEXT,
    quantity INTEGER,
    supplier_id INTEGER,
    supplier_name TEXT,
    supplier_contact TEXT
);

-- Проблемы:
-- 1. Избыточность: customer_name повторяется для каждого заказа
-- 2. Аномалии обновления: изменение email требует обновления всех строк
-- 3. Аномалии вставки: нельзя добавить клиента без заказа
-- 4. Аномалии удаления: удаление заказа удаляет информацию о клиенте

-- Нормализованная схема (хорошо)
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer_addresses (
    address_id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    address_line1 TEXT,
    address_line2 TEXT,
    city TEXT,
    postal_code TEXT,
    country TEXT,
    is_default BOOLEAN DEFAULT FALSE
);

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_category_id INTEGER REFERENCES categories(category_id)
);

CREATE TABLE suppliers (
    supplier_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    contact_email TEXT,
    contact_phone TEXT
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id INTEGER REFERENCES categories(category_id),
    supplier_id INTEGER REFERENCES suppliers(supplier_id),
    stock_quantity INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10, 2),
    shipping_address_id INTEGER REFERENCES customer_addresses(address_id)
);

CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,  -- Фиксируем цену на момент заказа
    subtotal DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

#2. Индексы

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

#2.1. B-Tree индексы (по умолчанию в PostgreSQL)

B-Tree (Balanced Tree) — сбалансированное дерево, оптимальное для:

Равенства (=)
Диапазонов (>, <, BETWEEN)
Сортировки (ORDER BY)
Префиксного поиска (LIKE 'prefix%')

-- Создание B-Tree индекса
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_products_price ON products(price);

-- Составной индекс (порядок колонок важен!)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- Этот индекс можно использовать для:
-- WHERE customer_id = ?                          ✓
-- WHERE customer_id = ? AND order_date = ?       ✓
-- WHERE customer_id = ? AND order_date > ?       ✓
-- WHERE order_date = ?                           ✗ (левая колонка пропущена)

-- EXPLAIN ANALYZE показывает использование индекса
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Результат:
-- Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=100)
--   Index Cond: (email = 'test@example.com'::text)
--   Actual Time: 0.050..0.051 rows=1 loops=1

Когда использовать B-Tree:

Первичные ключи (автоматически)
Foreign keys (рекомендуется для JOIN)
Колонки в WHERE, ORDER BY, GROUP BY
Уникальные ограничения (автоматически создаётся индекс)

#2.2. Hash индексы

Hash индексы оптимальны только для точного совпадения (=).

-- Создание Hash индекса
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

-- Работает только для:
-- WHERE email = 'test@example.com'    ✓
-- WHERE email LIKE 'test%'            ✗
-- WHERE email > 'test'                ✗
-- ORDER BY email                      ✗

-- EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Результат:
-- Hash Scan using idx_users_email_hash on users  (cost=0.28..8.30 rows=1 width=100)
--   Hash Cond: (email = 'test@example.com'::text)

Когда использовать Hash:

Только для точного поиска по уникальным или почти уникальным значениям
Когда не нужны диапазоны или сортировка
В PostgreSQL 10+ Hash индексы стали WAL-логируемыми и надёжными

#2.3. GIN индексы (Generalized Inverted Index)

GIN оптимален для:

Массивов (ARRAY)
Полнотекстового поиска (tsvector)
JSONB полей

-- Индекс для массивов
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]  -- Массив тегов
);

CREATE INDEX idx_articles_tags ON articles USING GIN(tags);

-- Запрос: найти статьи с тегом 'python'
EXPLAIN ANALYZE
SELECT * FROM articles WHERE tags @> ARRAY['python'];
-- @> означает "contains"

-- Индекс для JSONB
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type TEXT,
    payload JSONB  -- Гибкая структура данных
);

CREATE INDEX idx_events_payload ON events USING GIN(payload);

-- Запрос: найти события с определённым значением в JSONB
EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"user_id": 123}';

-- Индекс для конкретного ключа JSONB (более эффективный)
CREATE INDEX idx_events_payload_user_id ON events USING GIN((payload->'user_id'));

-- Полнотекстовый поиск
CREATE INDEX idx_articles_title_fts ON articles USING GIN(to_tsvector('english', title));

-- Запрос полнотекстового поиска
EXPLAIN ANALYZE
SELECT * FROM articles
WHERE to_tsvector('english', title) @@ to_tsquery('python & database');

#2.4. GiST индексы (Generalized Search Tree)

GiST оптимален для:

Геометрических данных (PostGIS)
Нечёткого поиска
Диапазонных типов

-- Геометрические данные (PostGIS)
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    coordinates GEOGRAPHY(POINT, 4326)  -- Широта/долгота
);

CREATE INDEX idx_locations_coords ON locations USING GIST(coordinates);

-- Запрос: найти точки в радиусе 1 км
EXPLAIN ANALYZE
SELECT * FROM locations
WHERE ST_DWithin(
    coordinates,
    ST_GeogFromText('POINT(37.6176 55.7558)'),  -- Москва
    1000  -- метры
);

-- Нечёткий поиск (pg_trgm extension)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_name_trgm ON users USING GIST(name gist_trgm_ops);

-- Запрос: нечёткий поиск по имени
EXPLAIN ANALYZE
SELECT * FROM users WHERE name % 'Александр';  -- % означает similarity

#2.5. Сравнение индексов

Тип индекса	Лучше всего для	Операции	Размер
B-Tree	Большинство случаев	=, >, <, BETWEEN, ORDER BY, LIKE 'prefix%'	Средний
Hash	Точное совпадение	=	Маленький
GIN	Массивы, JSONB, полнотекст	@>, &&, @@	Большой
GiST	Геометрия, нечёткий поиск	специфичные	Средний

#2.6. Практические рекомендации по индексам

-- 1. Не создавайте индексы на маленьких таблицах (< 1000 строк)
-- PostgreSQL часто предпочтёт sequential scan

-- 2. Избегайте избыточных индексов
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_email_active ON users(email, is_active);  -- Избыточный!

-- 3. Мониторьте неиспользуемые индексы
SELECT
    schemaname,
    indexrelname,
    idx_scan,  -- Количество сканирований
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. Удаляйте неиспользуемые индексы (они замедляют запись!)
DROP INDEX IF EXISTS unused_index;

-- 5. Используйте частичные индексы для частых запросов с фильтром
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';  -- Индекс только для pending заказов

-- 6. Используйте функциональные индексы для запросов с функциями
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Запрос должен использовать ту же функцию:
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- 7. CONCURRENTLY для создания индекса без блокировки таблицы
CREATE INDEX CONCURRENTLY idx_large_table_column ON large_table(column);
-- Важно: нельзя использовать внутри транзакции

#3. Транзакции и ACID свойства

Транзакция — это последовательность операций, которая выполняется как единое целое.

#3.1. ACID свойства

Atomicity (Атомарность):

Все операции транзакции выполняются или ни одна
Нет частичных результатов

Consistency (Согласованность):

Транзакция переводит базу из одного валидного состояния в другое
Все ограничения (constraints) соблюдаются

Isolation (Изоляция):

Параллельные транзакции не влияют друг на друга
Уровень изоляции определяет степень видимости изменений

Durability (Долговечность):

После COMMIT изменения сохраняются навсегда
Сохраняются даже после сбоя питания

#3.2. Пример транзакции в SQLAlchemy

from sqlalchemy import create_engine, Column, Integer, String, Decimal, ForeignKey
from sqlalchemy.orm import sessionmaker, declarative_base, relationship
from contextlib import contextmanager

Base = declarative_base()


class Account(Base):
    __tablename__ = 'accounts'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    balance = Column(Decimal(10, 2), default=0)

    transactions = relationship('Transaction', back_populates='account')


class Transaction(Base):
    __tablename__ = 'transactions'

    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey('accounts.id'))
    amount = Column(Decimal(10, 2), nullable=False)
    type = Column(String)  # 'debit' или 'credit'
    description = Column(String)

    account = relationship('Account', back_populates='transactions')


# Создание двигателя и сессии
engine = create_engine('postgresql://user:pass@localhost/dbname')
Session = sessionmaker(bind=engine)


@contextmanager
def transactional_session():
    """Контекстный менеджер для транзакций"""
    session = Session()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()


def transfer_money(from_account_id: int, to_account_id: int, amount: Decimal):
    """
    Перевод денег между счетами — атомарная операция.

    ACID в действии:
    - Atomicity: если второй UPDATE упадёт, первый откатится
    - Consistency: CHECK constraint гарантирует неотрицательный баланс
    - Isolation: другие транзакции не видят промежуточных изменений
    - Durability: после commit изменения сохранены на диске
    """
    with transactional_session() as session:
        # Блокируем строки для предотвращения race condition
        from_account = session.query(Account).filter(
            Account.id == from_account_id
        ).with_for_update().one()

        to_account = session.query(Account).filter(
            Account.id == to_account_id
        ).with_for_update().one()

        # Проверка достаточности средств
        if from_account.balance < amount:
            raise ValueError("Insufficient funds")

        # Списание
        from_account.balance -= amount
        debit = Transaction(
            account_id=from_account_id,
            amount=amount,
            type='debit',
            description=f'Transfer to account {to_account_id}'
        )
        session.add(debit)

        # Зачисление
        to_account.balance += amount
        credit = Transaction(
            account_id=to_account_id,
            amount=amount,
            type='credit',
            description=f'Transfer from account {from_account_id}'
        )
        session.add(credit)

        # commit происходит автоматически при выходе из контекста


# Пример с явной транзакцией
def complex_operation():
    session = Session()
    try:
        # Начало транзакции
        session.begin()

        # Операции...
        account = session.query(Account).get(1)
        account.balance += 100

        # Точка сохранения (savepoint) — можно откатиться к ней
        session.begin_nested()
        try:
            # Рискованная операция
            risky_operation(session)
        except Exception:
            # Откат к savepoint, основная транзакция продолжается
            session.rollback()

        # Фиксация транзакции
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()

#3.3. Уровни изоляции в PostgreSQL

PostgreSQL поддерживает 4 уровня изоляции:

-- Проверка текущего уровня
SHOW transaction_isolation;

-- Установка уровня для сессии
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Уровень	Dirty Read	Non-Repeatable Read	Phantom Read	Serialization Anomaly
Read Uncommitted	Возможно	Возможно	Возможно	Возможно
Read Committed (по умолчанию)	Нет	Возможно	Возможно	Возможно
Repeatable Read	Нет	Нет	Возможно	Возможно
Serializable	Нет	Нет	Нет	Нет

Важно: В PostgreSQL Read Uncommitted ведёт себя как Read Committed.

#3.4. Аномалии параллельных транзакций

Dirty Read (Грязное чтение): Чтение незакоммиченных данных другой транзакции.

-- Транзакция A                          -- Транзакция B
BEGIN;
UPDATE accounts SET balance = 500
WHERE id = 1;
                                        BEGIN;
                                        -- Dirty Read: видит незакоммиченные данные
                                        SELECT balance FROM accounts WHERE id = 1;
                                        -- Вернёт 500, хотя A ещё не закоммитил
                                        COMMIT;  -- или ROLLBACK

COMMIT;

Non-Repeatable Read (Неповторяемое чтение): Один и тот же запрос в одной транзакции возвращает разные результаты.

-- Транзакция A                          -- Транзакция B
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Вернёт 1000
                                        BEGIN;
                                        UPDATE accounts SET balance = 500
                                        WHERE id = 1;
                                        COMMIT;

SELECT balance FROM accounts WHERE id = 1;
-- Вернёт 500 — данные изменились!
COMMIT;

Phantom Read (Фантомное чтение): Появление новых строк при повторном выполнении запроса с условием.

-- Транзакция A                          -- Транзакция B
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Вернёт 10
                                        BEGIN;
                                        INSERT INTO orders (status) VALUES ('pending');
                                        COMMIT;

SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Вернёт 11 — появилась новая строка!
COMMIT;

Serialization Anomaly: Результат параллельных транзакций не соответствует никакому последовательному выполнению.

-- Классический пример: два инкремента
-- Ожидаемый результат: balance = 100 + 10 + 10 = 120

-- Транзакция A                          -- Транзакция B
BEGIN;
                                        BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- 100
                                        SELECT balance FROM accounts WHERE id = 1;
                                        -- 100
UPDATE accounts SET balance = 110
WHERE id = 1;
                                        UPDATE accounts SET balance = 110
                                        WHERE id = 1;
COMMIT;
                                        COMMIT;

-- Результат: balance = 110 (один инкремент потерян!)

#3.5. Практическое использование уровней изоляции

from sqlalchemy import event
from sqlalchemy.orm import Session


def read_committed_example():
    """
    Read Committed — по умолчанию в PostgreSQL.
    Подходит для большинства случаев.
    """
    with transactional_session() as session:
        # Каждое SELECT видит только закоммиченные данные
        accounts = session.query(Account).filter(Account.balance > 1000).all()
        # Другая транзакция может изменить данные между запросами


def repeatable_read_example():
    """
    Repeatable Read — гарантирует, что данные не изменятся
    в рамках транзакции.
    """
    with transactional_session() as session:
        # Установка уровня изоляции
        session.connection().execution_options(
            isolation_level="REPEATABLE_READ"
        )

        # Первое чтение
        account = session.query(Account).get(1)
        balance1 = account.balance

        # Другая транзакция может закоммитить изменения...

        # Второе чтение — вернёт то же значение!
        session.expire(account)  # Обновляем из БД
        balance2 = account.balance

        assert balance1 == balance2  # Гарантировано


def serializable_example():
    """
    Serializable — максимальная изоляция.
    Используется для критичных финансовых операций.
    Может вызывать ошибки serialization failure.
    """
    max_retries = 3

    for attempt in range(max_retries):
        try:
            with transactional_session() as session:
                session.connection().execution_options(
                    isolation_level="SERIALIZABLE"
                )

                # Чтение и запись
                account = session.query(Account).get(1)
                account.balance += 100

                # Если произошла конкуренция, PostgreSQL выбросит:
                # sqlalchemy.exc.OperationalError: could not serialize access
                # Нужно повторить транзакцию

        except Exception as e:
            if "serialize" in str(e).lower() and attempt < max_retries - 1:
                continue  # Повторить
            raise


# Рекомендации по выбору уровня:
# - Read Committed: 95% случаев (веб-приложения, CRUD)
# - Repeatable Read: отчёты, аналитика, консистентное чтение
# - Serializable: финансовые транзакции, инвентарь

#4. Оптимизация запросов и EXPLAIN ANALYZE

#4.1. EXPLAIN и EXPLAIN ANALYZE

-- EXPLAIN показывает план выполнения без запуска
EXPLAIN
SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN ANALYZE выполняет запрос и показывает реальное время
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Результат:
-- Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=100)
--   Index Cond: (email = 'test@example.com'::text)
--   Actual Time: 0.050..0.051 rows=1 loops=1
-- Planning Time: 0.100 ms
-- Execution Time: 0.080 ms

Ключевые метрики:

cost: оценка стоимости (первое число — startup cost, второе — total cost)
rows: оценочное количество строк
width: средний размер строки в байтах
Actual Time: реальное время выполнения (только в ANALYZE)
loops: сколько раз выполнился узел

#4.2. Типы сканирований

Sequential Scan (полное сканирование):

EXPLAIN ANALYZE
SELECT * FROM users WHERE name LIKE '%john%';

-- Seq Scan on users  (cost=0.00..100.00 rows=10 width=100)
--   Filter: (name ~~ '%john%'::text)
--   Rows Removed by Filter: 9990
--   Actual Time: 5.000..10.000 rows=10 loops=1

Происходит когда:

Нет подходящего индекса
Таблица маленькая
Запрос возвращает большую часть таблицы

Index Scan:

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Index Scan using idx_users_email on users
--   Index Cond: (email = 'test@example.com'::text)
--   Actual Time: 0.050..0.051 rows=1 loops=1

Использует индекс для поиска, затем обращается к таблице за данными.

Index Only Scan (самый быстрый):

EXPLAIN ANALYZE
SELECT email FROM users WHERE email = 'test@example.com';

-- Index Only Scan using idx_users_email on users
--   Index Cond: (email = 'test@example.com'::text)
--   Actual Time: 0.030..0.031 rows=1 loops=1

Все данные есть в индексе, таблица не нужна. Требует VACUUM для актуальности.

Bitmap Index Scan + Bitmap Heap Scan:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2025-01-01';

-- Bitmap Heap Scan on orders
--   Recheck Cond: ((status = 'pending'::text) AND (created_at > '2025-01-01'))
--   Actual Time: 0.500..2.000 rows=100 loops=1
--   ->  Bitmap Index Scan on idx_orders_status
--         Index Cond: (status = 'pending'::text)
--   ->  Bitmap Index Scan on idx_orders_date
--         Index Cond: (created_at > '2025-01-01'::date)

Используется когда нужно объединить несколько индексов.

#4.3. Оптимизация JOIN

-- Добавим foreign key индекс (часто забывают!)
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- EXPLAIN ANALYZE для JOIN
EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, p.name, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = 123;

-- Результат:
-- Nested Loop  (cost=0.86..50.00 rows=10 width=100)
--   ->  Index Scan using idx_orders_customer on orders o
--         Index Cond: (customer_id = 123)
--   ->  Index Scan using idx_order_items_order_id on order_items oi
--         Index Cond: (order_id = o.order_id)
--   ->  Index Scan using products_pkey on products p
--         Index Cond: (product_id = oi.product_id)

Типы JOIN:

Nested Loop: хорошо для маленьких таблиц или когда есть индекс
Hash Join: хорошо для больших таблиц без индексов
Merge Join: хорошо для отсортированных данных

#4.4. Common проблемы и решения

Проблема 1: Функция в WHERE ломает индекс

-- Плохо: функция на колонке
EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- Seq Scan!

-- Хорошо: функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Теперь Index Scan

Проблема 2: Неправильный порядок колонок в составном индексе

-- Индекс: (customer_id, order_date)
-- Запрос:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date > '2025-01-01';
-- Seq Scan! customer_id пропущен

-- Решение: создать отдельный индекс
CREATE INDEX idx_orders_date ON orders(order_date);

**Проблема 3: SELECT ***

-- Плохо: выбираем все колонки
SELECT * FROM orders;

-- Хорошо: только нужные колонки
SELECT order_id, order_date, total_amount FROM orders;
-- Может использовать Index Only Scan

Проблема 4: Отсутствие LIMIT при сортировке

-- Плохо: сортируем всю таблицу
SELECT * FROM orders ORDER BY created_at DESC;

-- Хорошо: ограничиваем результат
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
-- Использует индекс для сортировки

#4.5. Оптимизация в SQLAlchemy

from sqlalchemy.orm import joinedload, selectinload
from sqlalchemy import select


# Проблема N+1 запросов
def get_users_with_orders_bad(session):
    """Плохо: N+1 запрос"""
    users = session.query(User).all()  # 1 запрос
    for user in users:
        print(user.orders)  # N запросов!


def get_users_with_orders_good(session):
    """Хорошо: 2 запроса с eager loading"""
    users = session.query(User).options(
        joinedload(User.orders)  # Предзагрузка через JOIN
    ).all()
    for user in users:
        print(user.orders)  # Без дополнительных запросов


def get_users_with_many_orders(session):
    """Для коллекций лучше selectinload"""
    users = session.query(User).options(
        selectinload(User.orders)  # Отдельный запрос с IN (...)
    ).all()


# Использование EXPLAIN в SQLAlchemy
from sqlalchemy.dialects import postgresql

query = select(User).where(User.email == 'test@example.com')
print(str(query.compile(dialect=postgresql.dialect())))

#5. Foreign Keys и Constraints

#5.1. Foreign Keys

-- Создание foreign key
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE  -- Удаление клиента удаляет заказы
        ON UPDATE CASCADE  -- Изменение ID клиента обновляет заказы
);

-- Варианты ON DELETE:
-- CASCADE: удаляет зависимые строки
-- SET NULL: устанавливает NULL (колонка должна быть nullable)
-- SET DEFAULT: устанавливает значение по умолчанию
-- RESTRICT: запрещает удаление (проверяет сразу)
-- NO ACTION: запрещает удаление (проверяет в конце транзакции)

-- Добавление FK к существующей таблице
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE;

-- Удаление FK
ALTER TABLE order_items
DROP CONSTRAINT fk_order_items_order;

#5.2. CHECK Constraints

-- Простые проверки
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INTEGER CHECK (stock_quantity >= 0),
    status TEXT CHECK (status IN ('active', 'inactive', 'discontinued'))
);

-- Сложные проверки
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    total_amount DECIMAL(10, 2),
    shipping_cost DECIMAL(10, 2),
    CHECK (total_amount >= shipping_cost),  -- Общая сумма >= доставка
    CHECK (total_amount >= 0)
);

-- Проверка с несколькими колонками
CREATE TABLE discounts (
    product_id INTEGER,
    discount_percent DECIMAL(5, 2),
    valid_from DATE,
    valid_to DATE,
    CHECK (discount_percent BETWEEN 0 AND 100),
    CHECK (valid_to > valid_from)  -- Дата окончания больше даты начала
);

#5.3. UNIQUE Constraints

-- Уникальность одной колонки
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL  -- UNIQUE создаёт индекс автоматически
);

-- Уникальность нескольких колонок (составной UNIQUE)
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    granted_by INTEGER,
    granted_at TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    UNIQUE (user_id, role_id)  -- Один пользователь — одна роль
);

-- Частичный UNIQUE (уникальность с условием)
CREATE TABLE user_sessions (
    session_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    is_active BOOLEAN,
    UNIQUE (user_id) WHERE is_active = TRUE  -- Только одна активная сессия
);

#5.4. NOT NULL и DEFAULT

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,  -- Обязательно
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0,  -- Значение по умолчанию
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Изменение на NOT NULL (если нет NULL значений)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Добавление DEFAULT
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

#5.5. Constraints в SQLAlchemy

from sqlalchemy import CheckConstraint, UniqueConstraint, ForeignKeyConstraint


class Product(Base):
    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)
    price = Column(Decimal(10, 2), nullable=False)
    stock = Column(Integer, nullable=False, default=0)
    status = Column(String, nullable=False)

    __table_args__ = (
        CheckConstraint('price > 0', name='check_price_positive'),
        CheckConstraint('stock >= 0', name='check_stock_non_negative'),
        CheckConstraint(
            "status IN ('active', 'inactive', 'discontinued')",
            name='check_status_valid'
        ),
        UniqueConstraint('name', 'category_id', name='uq_product_name_category'),
    )


class Order(Base):
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True)
    customer_id = Column(
        Integer,
        ForeignKey('customers.id', ondelete='CASCADE', onupdate='CASCADE'),
        nullable=False
    )
    total_amount = Column(Decimal(10, 2))
    shipping_cost = Column(Decimal(10, 2))

    __table_args__ = (
        CheckConstraint(
            'total_amount >= shipping_cost',
            name='check_total_ge_shipping'
        ),
    )

#6. Денормализация для производительности

Иногда нормализация вредит производительности. Денормализация — сознательное нарушение нормализации для ускорения чтения.

#6.1. Когда денормализовать

Сценарии для денормализации:

Чтение значительно чаще записи (100:1 и более)
JOIN слишком дорогие для частых запросов
Требуется агрегация в реальном времени
Full-text search по связанным данным

#6.2. Примеры денормализации

Пример 1: Кэширование агрегатов

-- Нормализованная схема
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total_amount DECIMAL(10, 2)
);

-- Запрос: общая сумма заказов клиента (медленно при большом объёме)
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE customer_id = 123
GROUP BY customer_id;

-- Денормализация: храним агрегат
ALTER TABLE customers ADD COLUMN total_orders_amount DECIMAL(10, 2) DEFAULT 0;

-- Обновляем агрегат при каждом изменении orders
CREATE OR REPLACE FUNCTION update_customer_total()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE customers SET total_orders_amount = total_orders_amount + NEW.total_amount
        WHERE customer_id = NEW.customer_id;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE customers SET total_orders_amount = total_orders_amount + NEW.total_amount - OLD.total_amount
        WHERE customer_id = NEW.customer_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE customers SET total_orders_amount = total_orders_amount - OLD.total_amount
        WHERE customer_id = OLD.customer_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_customer_total
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_customer_total();

-- Теперь запрос мгновенный
SELECT total_orders_amount FROM customers WHERE customer_id = 123;

Пример 2: Дублирование данных для JOIN

-- Нормализованная схема (требует JOIN)
SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Денормализация: дублируем имя и email в orders
ALTER TABLE orders ADD COLUMN customer_name TEXT;
ALTER TABLE orders ADD COLUMN customer_email TEXT;

-- Запрос без JOIN
SELECT order_id, order_date, customer_name, customer_email
FROM orders
WHERE customer_email = 'test@example.com';

-- Триггер для синхронизации
CREATE OR REPLACE FUNCTION sync_customer_data()
RETURNS TRIGGER AS $$
BEGIN
    NEW.customer_name := (SELECT name FROM customers WHERE customer_id = NEW.customer_id);
    NEW.customer_email := (SELECT email FROM customers WHERE customer_id = NEW.customer_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_customer_data
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_customer_data();

Пример 3: Материализованные представления

-- Создаём материализованное представление
CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email;

-- Создаём индекс на представлении
CREATE INDEX idx_customer_summary_total ON customer_order_summary(total_spent);

-- Запрос к представлению (очень быстро)
SELECT * FROM customer_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC
LIMIT 100;

-- Обновление представления (периодически)
REFRESH MATERIALIZED VIEW customer_order_summary;

-- Обновление без блокировки чтений (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_summary;

#6.3. Trade-offs денормализации

Аспект	Нормализация	Денормализация
Чтение	Медленнее (JOIN)	Быстрее
Запись	Быстрее	Медленнее (обновление дубликатов)
Целостность	Гарантирована БД	Требует триггеров/кода
Размер	Меньше	Больше (дубликаты)
Сложность	Выше (много таблиц)	Ниже (меньше JOIN)

Рекомендации:

Начинайте с нормализации
Профилируйте производительность
Денормализуйте только узкие места
Документируйте денормализованные поля
Используйте триггеры для поддержания консистентности

#7. Шардирование и репликация

#7.1. Репликация

Репликация — копирование данных на несколько серверов для:

Отказоустойчивости (failover)
Масштабирования чтения
Географического распределения

Типы репликации в PostgreSQL:

Streaming Replication (асинхронная):

-- Настройка primary (postgresql.conf)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64MB

-- Настройка standby (postgresql.conf)
hot_standby = on

-- standby создаёт файл standby.signal и настраивает primary_conninfo

Primary (read-write)
    │
    ├──► Replica 1 (read-only)  ← Для отчётов
    ├──► Replica 2 (read-only)  ← Для аналитики
    └──► Replica 3 (read-only)  ← Для бэкапа

Синхронная репликация (гарантирует сохранность данных):

-- primary (postgresql.conf)
synchronous_standby_names = 'replica1,replica2'
synchronous_commit = on

-- COMMIT ждёт подтверждения от реплик
-- Задержка записи, но данные не потеряются

Подключение к реплике в SQLAlchemy:

from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

# Primary для записи
primary_engine = create_engine(
    'postgresql://user:pass@primary:5432/dbname',
    poolclass=QueuePool,
    pool_size=10
)

# Replica для чтения
replica_engine = create_engine(
    'postgresql://user:pass@replica:5432/dbname',
    poolclass=QueuePool,
    pool_size=20  -- Больше пул для чтения
)

# Роутинг запросов
class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if self._flushing or self._is_write_operation():
            return primary_engine
        return replica_engine

    def _is_write_operation(self):
        # Проверка типа операции
        return False

Session = sessionmaker()
session = RoutingSession()

# Запись
session.add(User(name='Alice'))
session.commit()  → primary

# Чтение
users = session.query(User).all()  → replica

#7.2. Шардирование

Шардирование — горизонтальное разделение данных по разным серверам.

Стратегии шардирования:

1. Шардирование по диапазону:

Шард 1: user_id 1-1000000
Шард 2: user_id 1000001-2000000
Шард 3: user_id 2000001-3000000

2. Шардирование по хэшу:

def get_shard_id(user_id, num_shards=4):
    return hash(user_id) % num_shards

# user_id 1 → шард 1
# user_id 2 → шард 2
# user_id 3 → шард 3
# user_id 4 → шард 0

3. Шардирование по географии:

Шард EU: европейские пользователи
Шард US: американские пользователи
Шард ASIA: азиатские пользователи

Пример шардирования в Python:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


class ShardedDatabase:
    def __init__(self, num_shards=4):
        self.num_shards = num_shards
        self.engines = {
            i: create_engine(f'postgresql://user:pass@shard{i}:5432/dbname')
            for i in range(num_shards)
        }
        self.sessions = {
            i: sessionmaker(bind=engine)()
            for i, engine in self.engines.items()
        }

    def get_shard_id(self, user_id):
        return hash(user_id) % self.num_shards

    def get_session(self, user_id):
        shard_id = self.get_shard_id(user_id)
        return self.sessions[shard_id]

    def get_user(self, user_id):
        session = self.get_session(user_id)
        return session.query(User).get(user_id)

    def create_user(self, user):
        session = self.get_session(user.id)
        session.add(user)
        session.commit()


# Использование
db = ShardedDatabase(num_shards=4)

# Запросы автоматически идут на нужный шард
user = db.get_user(user_id=12345)
db.create_user(User(id=67890, name='Bob'))

PostgreSQL Citus для шардирования:

-- Установка расширения Citus
CREATE EXTENSION citus;

-- Шардирование таблицы
SELECT create_distributed_table('users', 'user_id');

-- Citus автоматически распределяет данные по шардам
-- Запросы выполняются параллельно на всех шардах
SELECT COUNT(*) FROM users;  -- Параллельно на всех шардах

#7.3. Сравнение подходов

Подход	Масштабирование	Сложность	Когда использовать
Вертикальное	Ограничено	Низкая	До 100GB данных
Репликация	Чтение	Средняя	Чтение >> записи
Шардирование	Запись и чтение	Высокая	Очень большие объёмы

Рекомендации:

Начните с вертикального масштабирования
Добавьте репликацию для масштабирования чтения
Шардируйте только при необходимости (миллионы строк, высокая нагрузка)
Используйте managed решения (AWS RDS, Google Cloud SQL) когда возможно

Тип индекса

Лучше всего для

Операции

Размер

B-Tree

Большинство случаев

=, >, <, BETWEEN, ORDER BY, LIKE 'prefix%'

Средний

Hash

Точное совпадение

Маленький

GIN

Массивы, JSONB, полнотекст

@>, &&, @@

Большой

GiST

Геометрия, нечёткий поиск

специфичные

Средний

Уровень

Dirty Read

Non-Repeatable Read

Phantom Read

Serialization Anomaly

Read Uncommitted

Возможно

Read Committed (по умолчанию)

Нет

Возможно

Repeatable Read

Нет

Возможно

Serializable

Нет

Аспект

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

Денормализация

Чтение

Медленнее (JOIN)

Быстрее

Запись

Быстрее

Медленнее (обновление дубликатов)

Целостность

Гарантирована БД

Требует триггеров/кода

Размер

Меньше

Больше (дубликаты)

Сложность

Выше (много таблиц)

Ниже (меньше JOIN)

Primary (read-write) │ ├──► Replica 1 (read-only) ← Для отчётов ├──► Replica 2 (read-only) ← Для аналитики └──► Replica 3 (read-only) ← Для бэкапа

Подход

Масштабирование

Сложность

Когда использовать

Вертикальное

Ограничено

Низкая

До 100GB данных

Репликация

Чтение

Средняя

Чтение >> записи

Шардирование

Запись и чтение

Высокая

Очень большие объёмы