На какие констрейнты PostgreSQL создаёт индексы?

На какие констрейнты PostgreSQL создаёт индексы

PostgreSQL автоматически создаёт индексы для некоторых типов констрейнтов, чтобы оптимизировать проверку ограничений и поиск данных. Рассмотрим, какие именно.

1. PRIMARY KEY

Для PRIMARY KEY PostgreSQL обязательно создаёт уникальный индекс (B-tree).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Индекс создаётся автоматически
    username VARCHAR(100)
);

-- Это эквивалентно:
CREATE TABLE users (
    id SERIAL,
    username VARCHAR(100),
    PRIMARY KEY (id)  -- Индекс: users_pkey (B-tree, UNIQUE)
);

-- Проверить индекс:
\d users
-- Увидим: users_pkey btree PRIMARY KEY

2. UNIQUE

Для UNIQUE констрейнта PostgreSQL обязательно создаёт уникальный индекс (B-tree).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE  -- Индекс создаётся автоматически
);

-- Это создаст индекс: users_email_key (B-tree, UNIQUE)

-- Для составного UNIQUE
CREATE TABLE user_profiles (
    user_id INT REFERENCES users(id),
    platform VARCHAR(50),
    username VARCHAR(100),
    UNIQUE (user_id, platform)  -- Индекс на две колонки
);

3. FOREIGN KEY

Для FOREIGN KEY PostgreSQL не создаёт индекс автоматически, но настоятельно рекомендуется создать его вручную, так как:

• Проверка ограничения требует поиска в родительской таблице
• DELETE на родительской таблице требит поиска в дочерней
• Без индекса эти операции медленные

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)  -- Индекс НЕ создаётся!
);

-- Рекомендуется добавить индекс вручную:
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Или использовать сочетание UNIQUE + FOREIGN KEY
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(id)  -- UNIQUE создаст индекс
);

4. CHECK Констрейнт

Для CHECK констрейнта PostgreSQL не создаёт индекс. Проверка выполняется обычным вычислением условия.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2),
    stock INT,
    CHECK (price > 0),       -- Без индекса
    CHECK (stock >= 0)       -- Без индекса
);

-- CHECK используется только при вставке/обновлении, поэтому индекс бесполезен

5. DEFAULT

DEFAULT не требует индекса и PostgreSQL его не создаёт.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Без индекса
    status VARCHAR(20) DEFAULT 'draft'                -- Без индекса
);

6. NOT NULL

NOT NULL не требует индекса для проверки самого констрейнта, но часто индекс полезен для поиска значений.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL  -- Индекса нет, но может быть полезен
);

-- Если часто ищем по email:
CREATE INDEX idx_users_email ON users(email);

Какие индексы НЕ создаются автоматически

Практический пример

CREATE TABLE orders (
    -- PRIMARY KEY автоматически создаёт индекс
    id SERIAL PRIMARY KEY,
    
    -- UNIQUE автоматически создаёт индекс
    order_number VARCHAR(50) UNIQUE,
    
    -- FOREIGN KEY НЕ создаёт индекс
    user_id INT NOT NULL REFERENCES users(id),
    
    -- FOREIGN KEY НЕ создаёт индекс
    product_id INT NOT NULL REFERENCES products(id),
    
    -- CHECK и DEFAULT НЕ создают индексы
    quantity INT NOT NULL CHECK (quantity > 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Рекомендуемые дополнительные индексы
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Проверить все индексы
\d orders

Просмотр индексов

-- Все индексы в таблице
\d table_name

-- Информация об индексах через системный каталог
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- Какой индекс использует констрейнт
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';

Влияние на производительность

PRIMARY KEY и UNIQUE:

-- Индекс ускоряет поиск
SELECT * FROM users WHERE id = 5;  -- Быстро (индекс есть)
SELECT * FROM users WHERE email = 'user@example.com';  -- Быстро (индекс есть)

FOREIGN KEY без индекса:

-- Медленно! Требует полного сканирования
DELETE FROM users WHERE id = 1;  -- Ищет все заказы этого пользователя

-- Быстро! С индексом
CREATE INDEX idx_orders_user_id ON orders(user_id);
DELETE FROM users WHERE id = 1;  -- Быстро находит заказы

Типы индексов

PostgreSQL создаёт разные типы индексов:

-- B-tree (по умолчанию для PRIMARY KEY и UNIQUE)
CREATE INDEX idx_name ON table_name(column_name);

-- Hash (для равенства)
CREATE INDEX idx_name ON table_name USING HASH(column_name);

-- BRIN (для больших таблиц)
CREATE INDEX idx_name ON table_name USING BRIN(column_name);

-- GiST (для геометрических типов)
CREATE INDEX idx_name ON table_name USING GIST(column_name);

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

1. FOREIGN KEY — всегда добавляйте индекс на колонку, которая ссылается на другую таблицу
2. Часто используемые в WHERE — добавляйте индексы
3. Составные UNIQUE — индекс создаётся автоматически
4. Составные FOREIGN KEY — добавляйте индекс вручную

# В SQLAlchemy (миграция Goose в raw SQL)
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,  -- Индекс автоматически
    user_id INT NOT NULL REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Обязательно добавьте индекс на FOREIGN KEY
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at);

Заключение

PostgreSQL автоматически создаёт индексы только для:

• PRIMARY KEY — обязательно
• UNIQUE — обязательно

Для остальных констрейнтов (FOREIGN KEY, CHECK, DEFAULT, NOT NULL) индексы не создаются автоматически и должны быть добавлены вручную, если требуется для производительности.