В каких случаях PostgreSQL может автоматически создавать индекс

Автоматическое создание индексов в PostgreSQL

PgSQL не автоматически создает индексы в большинстве случаев, но есть несколько ключевых исключений. Давайте разберемся подробно.

1. PRIMARY KEY и UNIQUE constraints

PRIMARY KEY и UNIQUE создают индекс автоматически:

-- При создании таблицы
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- автоматически создается индекс
    email VARCHAR(255) UNIQUE  -- автоматически создается индекс
);

-- Это эквивалентно:
CREATE TABLE users (
    id SERIAL,
    email VARCHAR(255)
);
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
CREATE INDEX users_pkey ON users (id);  -- создается автоматически
CREATE INDEX users_email_key ON users (email);  -- создается автоматически

2. FOREIGN KEY (иногда)

PostgreSQL НЕ создает индекс на стороне "many" автоматически, но многие ORMы это делают:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)  -- индекс НЕ создается автоматически
);

-- Нужно создать вручную (это не автоматическое создание в PgSQL)
CREATE INDEX posts_user_id_idx ON posts(user_id);

Почему это важно? Foreign Key проверки требуют индекса для производительности. SQLAlchemy и Django автоматически создают такие индексы через миграции.

3. Ключевое исключение: FOREIGN KEY в конце таблицы

В очень старых версиях PostgreSQL (до 9.0) при удалении записи в referenced таблице система автоматически создавала временные индексы для поиска зависимостей. Это больше не происходит.

4. Partitioned Tables

При создании partitioned таблицы:

CREATE TABLE events (
    id BIGINT,
    event_date DATE,
    data JSONB
) PARTITION BY RANGE (event_date);

-- Индекс на основной таблице может быть создан, но НЕ на партициях
CREATE INDEX events_id_idx ON events (id);
-- Индекс на партициях создается автоматически как child-индексы

Практический пример из реальной работы

# SQLAlchemy пример
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)  # автоматический индекс
    email = Column(String, unique=True)  # автоматический индекс

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)  # автоматический индекс
    user_id = Column(Integer, ForeignKey("users.id"))  # индекс НЕ создается
    user = relationship("User")

# SQLAlchemy сама добавит индекс на user_id через миграцию
# Это не автоматическое создание PostgreSQL, а логика ORM

Миграция в Alembic/Goose

-- Правильная миграция для FOREIGN KEY
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    CONSTRAINT posts_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Нужно явно создать индекс
CREATE INDEX posts_user_id_idx ON posts(user_id);

Таблица: Что создается автоматически

Важная деталь: Отличие от других БД

-- MySQL автоматически создает индекс на FOREIGN KEY
-- PostgreSQL - НЕ создает (это плюс - вы контролируете, что индексировать)

Лучшие практики

1. Явно создавай индексы для FOREIGN KEY

   CREATE INDEX posts_user_id_idx ON posts(user_id);
   

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

3. Профилируй медленные запросы - не создавай индексы "на авось"

4. Используй EXPLAIN ANALYZE перед добавлением индекса

   EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 5;
   

Заключение

PostgreSQL создает индексы автоматически ТОЛЬКО для:

• PRIMARY KEY
• UNIQUE constraints
• Partitioned таблиц (частично)

Все остальные индексы нужно создавать явно, включая индексы для FOREIGN KEY - это по замыслу, давая разработчику контроль над оптимизацией.