Какой вид индекса используется по умолчанию в PostgreSQL?

# Индексы в PostgreSQL

Краткий ответ

B-tree (B-дерево) - это индекс, используемый по умолчанию в PostgreSQL.

Когда вы пишете:

CREATE INDEX idx_user_email ON users(email);

PostgreSQL по умолчанию создаёт B-tree индекс, даже если вы не указали USING BTREE.

Виды индексов в PostgreSQL

Всего есть 6 основных типов:

1. B-tree (Индекс по умолчанию)

CREATE INDEX idx_users_email ON users(email);
-- Эквивалентно:
CREATE INDEX idx_users_email ON users USING BTREE (email);

Структура: Сбалансированное дерево, где листья указывают на данные

       [M]
      /   \
    [D]   [T]
   / \    / \
 [A][G][P][X]

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

• ✅ Общий случай (95% индексов именно B-tree)
• ✅ Диапазонные запросы: WHERE age > 18
• ✅ Сортировка: ORDER BY email
• ✅ LIKE с префиксом: WHERE name LIKE 'John%'
• ✅ IS NULL / NOT NULL

Примеры:

-- Отличные для B-tree
WHERE email = 'user@example.com'  -- Точное совпадение
WHERE age BETWEEN 18 AND 65       -- Диапазон
WHERE name LIKE 'John%'           -- Префиксный поиск
WHERE created_at > '2025-01-01'  -- Дата диапазон
ORDER BY email ASC                -- Сортировка

Сложность поиска: O(log n), где n - количество строк

// Java аналогия - TreeMap использует B-tree
TreeMap<String, User> usersByEmail = new TreeMap<>();
usersByEmail.get("user@example.com"); // O(log n)
usersByEmail.tailMap("a").keySet();   // Диапазон

2. Hash - для точного совпадения

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

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

• ✅ Только точное совпадение: WHERE email = 'x'
• ❌ Диапазоны: WHERE email > 'a' - не работает!
• ❌ Сортировка - не помогает
• ❌ NULL - не работает

Примеры:

-- Хорошо для Hash
WHERE email = 'john@example.com'
WHERE user_id = 42

-- Плохо для Hash
WHERE email LIKE 'john%'  -- Hash индекс не поможет
WHERE age > 18            -- Hash индекс не поможет

Сложность: O(1) среднее (лучше чем B-tree!)

// Java аналогия - HashMap
HashMap<String, User> usersByEmail = new HashMap<>();
usersByEmail.get("user@example.com"); // O(1)
// Но нет методов для диапазонов

Важно: В PostgreSQL 14+ Hash индексы стали более надёжными, но всё ещё реже используются.

3. GiST (Generalized Search Tree) - для сложных типов

CREATE INDEX idx_places_location ON places USING GIST (location);

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

• ✅ Геометрические типы: POINT, POLYGON, CIRCLE
• ✅ Full-text search
• ✅ Диапазоны дат
• ✅ JSON

Примеры:

-- Геосинтаксис
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    location GEOMETRY(Point, 4326)
);
CREATE INDEX idx_places_location ON places USING GIST (location);

SELECT * FROM places 
WHERE location <-> POINT(55.7558, 37.6173) < 0.1;
-- <-> это оператор расстояния

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING GIST (tsv);
SELECT * FROM articles WHERE tsv @@ 'java | database';

4. GIN (Generalized Inverted Index) - для массивов и JSON

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

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

• ✅ Массивы: WHERE tags @> ARRAY['java']
• ✅ JSON поля: WHERE data @> '{"status": "active"}'
• ✅ Full-text search
• ✅ Когда много значений в одной колонке

Примеры:

-- Массивы
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- Быстрый поиск
SELECT * FROM articles WHERE tags @> ARRAY['java', 'spring'];

-- JSON
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);
SELECT * FROM users WHERE metadata @> '{"verified": true}';

Сложность: Быстрый поиск, медленный INSERT/UPDATE

5. BRIN (Block Range Index) - для больших таблиц

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);

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

• ✅ Очень большие таблицы (100M+ строк)
• ✅ Столбцы, отсортированные физически
• ✅ Временные ряды (logs, events)
• ❌ Много обновлений

Примеры:

-- Таблица логов, отсортирована по timestamp
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    level TEXT,
    message TEXT
);
-- BRIN экономит память в 100x раз vs B-tree!
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);

SELECT * FROM logs 
WHERE timestamp > '2025-03-20' AND timestamp < '2025-03-22';

Размер индекса: 1MB вместо 100MB (в 100 раз меньше!)

6. SP-GiST (Space-Partitioned GiST) - для радиусных деревьев

CREATE INDEX idx_locations ON places USING SPGIST (location);

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

• ✅ Геопространственные данные (квадрант дерево)
• ✅ IP адреса (inet тип)
• ✅ Когда данные распределены неравномерно

Примеры:

CREATE INDEX idx_ips ON networks USING SPGIST (ip_range);
SELECT * FROM networks 
WHERE ip_range >> INET '192.168.1.0/24';

Сравнительная таблица

Практические примеры из production

Сценарий 1: Типичное веб-приложение (B-tree)

CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    active BOOLEAN DEFAULT true
);

-- B-tree - отличный для поиска по email
CREATE INDEX idx_users_email ON users(email);

-- B-tree - отличный для временных диапазонов
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- B-tree - отличный для WHERE active = true
CREATE INDEX idx_users_active ON users(active) WHERE active = true;

-- Составной индекс
CREATE INDEX idx_users_active_email ON users(active, email);

Сценарий 2: Аналитическая таблица (BRIN)

-- Таблица событий: 500M строк, отсортирована по дате
CREATE TABLE events (
    id BIGSERIAL,
    timestamp TIMESTAMP NOT NULL,
    user_id UUID,
    event_type TEXT,
    data JSONB
);

-- BRIN вместо B-tree - экономия памяти!
CREATE INDEX idx_events_timestamp ON events USING BRIN (timestamp);
-- Размер: 5MB вместо 500MB

-- Всё ещё можно искать эффективно
SELECT COUNT(*) FROM events 
WHERE timestamp > '2025-01-01' 
AND event_type = 'purchase';

Сценарий 3: JSON данные (GIN)

CREATE TABLE users_metadata (
    user_id UUID PRIMARY KEY,
    metadata JSONB
);

CREATE INDEX idx_users_metadata ON users_metadata USING GIN (metadata);

SELECT * FROM users_metadata
WHERE metadata @> '{"plan": "premium", "verified": true}';

Сценарий 4: Массивы тегов (GIN)

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- Быстрый поиск
SELECT * FROM articles WHERE tags @> ARRAY['java', 'performance'];

Проверка индексов в production

-- Какие индексы есть?
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Используется ли индекс?
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'john@example.com';

-- Выходит: "Index Scan using idx_users_email" ✅

-- Какой размер индекса?
SELECT 
    indexrelname, 
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'users';

-- Выходит: "idx_users_email | 2256 kB"

Ответ на вопрос собеседования

Вопрос: Какой вид индекса используется по умолчанию в PostgreSQL?

Ответ:

B-tree (B-дерево). Это универсальный индекс, который подходит для 95% случаев. Он эффективен для:

• Точного совпадения
• Диапазонных запросов
• Сортировки
• NULL проверок

Если вы создаёте индекс без указания USING, PostgreSQL автоматически использует B-tree.

Для специальных случаев есть другие типы: Hash (точное совпадение, O(1)), GIN (для массивов/JSON), BRIN (для больших таблиц), GiST (для геометрии и FTS). Но B-tree остаётся лучшим выбором по умолчанию благодаря его универсальности и производительности.