Модуль 06 — DDL, индексы, ограничения и последовательности

DDL (Data Definition Language) управляет структурой объектов БД: CREATE создаёт, ALTER изменяет, DROP удаляет.

-- Создание таблицы бонусов пользователей
CREATE TABLE user_bonuses (
    id           bigint          PRIMARY KEY,
    user_id      bigint          NOT NULL,
    community_id bigint          NOT NULL,
    bonus        numeric(5,2)   NOT NULL DEFAULT 0,
    CONSTRAINT fk_user_bonuses_user_id
        FOREIGN KEY (user_id)      REFERENCES users(id),
    CONSTRAINT fk_user_bonuses_community_id
        FOREIGN KEY (community_id) REFERENCES communities(id)
);

-- ALTER: добавить ограничения постфактум
ALTER TABLE user_bonuses
    ADD CONSTRAINT ch_nn_user_id
        CHECK (user_id IS NOT NULL),
    ADD CONSTRAINT ch_range_bonus
        CHECK (bonus BETWEEN 0 AND 100);

Заполним user_bonuses на основе истории лайков — чем больше лайков в сообществе, тем выше бонус.

INSERT INTO user_bonuses (id, user_id, community_id, bonus)
SELECT
    ROW_NUMBER() OVER() AS id,
    l.user_id,
    p.community_id,
    CASE
        WHEN COUNT(*) = 1 THEN 10.5
        WHEN COUNT(*) = 2 THEN 22.0
        ELSE                    30.0
    END AS bonus
FROM   likes l
JOIN   posts p ON p.id = l.post_id
GROUP BY l.user_id, p.community_id;

-- Проверить результат
SELECT
    u.name,
    p.title,
    p.score,
    CAST(p.score * (1 - ub.bonus / 100.0) AS integer) AS bonus_score,
    c.name AS community_name
FROM   likes l
JOIN   users       u  ON u.id  = l.user_id
JOIN   posts       p  ON p.id  = l.post_id
JOIN   communities c  ON c.id  = p.community_id
JOIN   user_bonuses ub
           ON ub.user_id      = l.user_id
          AND ub.community_id = p.community_id
ORDER BY u.name, p.title;

Индекс — организованная структура для быстрого поиска. BTree-индекс сокращает время поиска с O(N) до O(log N). При N = 1 000 000 строк это разница между 1 000 000 и 20 итерациями.

-- Индексы на внешние ключи (обязательно для производительности JOIN)
CREATE INDEX idx_posts_community_id       ON posts(community_id);
CREATE INDEX idx_views_user_id            ON views(user_id);
CREATE INDEX idx_views_community_id       ON views(community_id);
CREATE INDEX idx_likes_user_id            ON likes(user_id);
CREATE INDEX idx_likes_post_id            ON likes(post_id);

-- Функциональный индекс: поиск по имени без учёта регистра
CREATE INDEX idx_users_name ON users(UPPER(name));

-- Нужен запрос с тем же выражением, чтобы индекс сработал
SELECT * FROM users
WHERE  UPPER(name) = 'ANNA';

-- Многоколоночный индекс (covering index)
CREATE INDEX idx_likes_multi
    ON likes(user_id, post_id, liked_at);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_posts_unique
    ON posts(community_id, title);

-- Частичный уникальный индекс
CREATE UNIQUE INDEX idx_likes_unique_date
    ON likes(user_id, post_id)
    WHERE liked_at = '2026-01-01';

EXPLAIN ANALYZE показывает план выполнения запроса и реальное время — без этого инструмента оптимизировать SQL вслепую.

-- Посмотреть план без выполнения
EXPLAIN
SELECT * FROM posts WHERE community_id = 1;

-- Выполнить и показать реальное время
EXPLAIN ANALYZE
SELECT * FROM posts WHERE community_id = 1;

-- Принудительно отключить Sequential Scan для теста
SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT * FROM posts WHERE community_id = 1;
SET enable_seqscan = on;  -- вернуть обратно

-- Создать последовательность
CREATE SEQUENCE seq_user_bonuses
    START WITH 1
    INCREMENT BY 1;

-- Установить начальное значение по MAX(id) — надёжно при наличии пробелов
SELECT SETVAL('seq_user_bonuses',
    (SELECT MAX(id) FROM user_bonuses));
-- SETVAL(name, n) устанавливает last_value = n, следующий NEXTVAL вернёт n+1

-- Использовать последовательность как DEFAULT для колонки
ALTER TABLE user_bonuses
    ALTER COLUMN id SET DEFAULT NEXTVAL('seq_user_bonuses');

-- Теперь можно добавлять строки без явного id
INSERT INTO user_bonuses (user_id, community_id, bonus)
VALUES (1, 2, 15.0);

-- SERIAL и BIGSERIAL — сокращения в PostgreSQL
-- BIGSERIAL ≡ BIGINT + CREATE SEQUENCE + DEFAULT NEXTVAL

Хорошая практика — документировать таблицы и столбцы прямо в базе данных. Это видно в IDE и помогает новым разработчикам.

-- Комментарий к таблице
COMMENT ON TABLE user_bonuses
    IS 'Бонусные баллы пользователей в разрезе сообществ';

-- Комментарии к столбцам
COMMENT ON COLUMN user_bonuses.user_id
    IS 'Ссылка на пользователя (users.id)';
COMMENT ON COLUMN user_bonuses.community_id
    IS 'Ссылка на сообщество (communities.id)';
COMMENT ON COLUMN user_bonuses.bonus
    IS 'Бонус в процентах (0–100). Зависит от количества лайков.';