🔬 B-tree в PostgreSQL 16: от "я умею CREATE INDEX" до "блять, вот как оно работает"
Знаешь что общего между B-tree индексом и твоим пониманием баз данных? Оба выглядят простыми снаружи, но внутри — охуенно сложная штука, которую ты игнорируешь. Разница в том, что B-tree реально работает.
🌳 Как B-tree работает внутри (и почему ты это игнорил)
B-tree — это дерево блоков. Не путай с бинарным деревом из учебника, где каждая нода хранит один ключ. В PostgreSQL каждая страница индекса (8KB) хранит сотни ключей. Это как книжная полка: на одной полке (странице) лежит дохуя книг (ключей).
Структура:
Корневая страница: указатели на ветки
Внутренние страницы: еще указатели
Листовые страницы: ключи + TID (Tuple ID - физический адрес строки)
Почему это важно:
Высота 3 = 3 чтения с диска (если нет в кэше)
На 10M строк: высота обычно 3
На 1B строк: высота обычно 4
Разница между 3ms и 4ms может стоить $500/месяц
🔥 Дедупликация в PostgreSQL 16: наконец-то не жрет место
До PG13: одинаковые значения дублировались. Колонка status с 3 значениями на 10M строк = 10M записей в индексе.
В PostgreSQL 16:
Как проверить дедупликацию:
🐕 WoofWoof сравнение: собака vs твой индекс
Пес помнит где лежит его миска. Одна миска = одна локация в памяти.
Твой индекс БЕЗ дедупликации: 10 миллионов записей "status=pending". Это как если бы Рекс хранил 10 миллионов копий информации "миска на кухне". Песька не дебил, он помнит один раз. PostgreSQL 16 научился делать так же.
⚡ Составные индексы: порядок колонок = жизнь или смерть
Это НЕ одно и то же:
Работает оптимально:
Правило: первая колонка = самое частое условие WHERE. Вторая = сортировка или доп фильтр.
💩 Отмазка | Реальность: составные индексы
"Я создам два индекса: на user_id и на created_at"
Реальность: PostgreSQL может использовать только один индекс на запрос (до PG16, Bitmap Index Scan помогает, но это медленно). Два индекса = 2× места, 2× время на INSERT/UPDATE, хуевая производительность.
"Порядок не важен, PostgreSQL сам разберется"
Реальность: WHERE user_id = 123 с индексом (created_at, user_id) = Seq Scan на 5M строк. Execution time: 800ms вместо 3ms. Поздравляю, твой продакшн лег.
🎯 Partial индексы: когда не нужно индексировать все
Когда использовать:
Колонка с перекосом (90% null, 10% не null)
Логическое удаление (deleted_at IS NULL)
Статусы (status IN ('pending', 'processing'))
Реальный кейс от Иванова И.И.:
🔨 Expression индексы: когда нужна трансформация
Частые кейсы:
🐕 Хвостик vs твой сеньор
Собака понимает что "СИДЕТЬ" и "сидеть" — одна команда. Один паттерн в памяти.
Твой сеньор создает индекс на email, потом ищет по LOWER(email). Это как кричать песелю "СИDEТЬ" латиницей и удивляться что не работает.
PostgreSQL 16 позволяет создать индекс на LOWER(email) — научить собаку понимать команду в любом регистре. Но твой сеньор об этом не знает.
🚀 Новые оптимизации PostgreSQL 16
1. Улучшенная дедупликация:
До PG13: вообще не было
PG13-15: базовая дедупликация
PG16: умная дедупликация с posting lists
Экономия 20-40% места на индексах с повторами
2. Параллельное построение индексов:
-- PG16: incremental sort умнее использует частично отсортированные данные из индекса (user_id, created_at)
-- На 25% быстрее чем PG15
4. Bloom filter pushdown:
В PG16 при JOIN оптимизатор лучше предсказывает селективность индексов.
❌ Когда B-tree идет нахуй
1. Полнотекстовый поиск:
2. JSONB запросы:
3. Геоданные:
4. Массивы:
🐕 для закрепления
У собаки есть специализация: он пастушья собака, отлично работает со стадом. Но я не заставляю его нырять за рыбой — для этого нужен лабрадор.( рыбрадор ))
B-tree — пастушья собака индексов: сортировки, диапазоны, точные поиски — охуенно. Но полнотекст, JSONB, геоданные — это не его задача. Для этого есть GIN, GiST, SP-GiST.
Ты же пытаешься заставить овчарку (B-tree) ловить рыбу (индексировать JSONB). Пес умнее — он знает свои ограничения. 🎯 Итого: чеклист для B-tree в PG16
✅ Используй B-tree для:
Диапазоны: WHERE created_at > '2025-01-01'
Сортировки: ORDER BY created_at
Сравнения: <, <=, >=, >
✅ В PG16 получаешь бонусом:
Дедупликация: -20-40% размер
Быстрее на INSERT с дедупликацией
Умнее incremental sort
Параллельное построение
❌ НЕ используй B-tree для:
LIKE '%text%' — нужен GIN + tsvector
JSONB операторы @>, ? — нужен GIN
Массивы @>, && — нужен GIN
Геоданные — нужен GiST
P.S. Проверь версию прямо сейчас:
```
SELECT version();
-- PostgreSQL 16.x = ты молодец
-- PostgreSQL 12-15 = обновляйся, получишь 20-40% экономии места
-- PostgreSQL 11 и ниже = иди обновляйся нахуй
```
Следующая часть: Hash индексы в PG16. Почему все их игнорируют и правы ли они.
Знаешь что общего между B-tree индексом и твоим пониманием баз данных? Оба выглядят простыми снаружи, но внутри — охуенно сложная штука, которую ты игнорируешь. Разница в том, что B-tree реально работает.
🌳 Как B-tree работает внутри (и почему ты это игнорил)
B-tree — это дерево блоков. Не путай с бинарным деревом из учебника, где каждая нода хранит один ключ. В PostgreSQL каждая страница индекса (8KB) хранит сотни ключей. Это как книжная полка: на одной полке (странице) лежит дохуя книг (ключей).
Структура:
Корневая страница: указатели на ветки
Внутренние страницы: еще указатели
Листовые страницы: ключи + TID (Tuple ID - физический адрес строки)
-- Проверим структуру индекса
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT * FROM bt_metap('idx_users_email');
-- magic: 340322 (идентификатор B-tree)
-- version: 4 (в PG16)
-- root: номер корневой страницы
-- level: высота дерева (обычно 2-4)
-- fastroot, fastlevel: оптимизация для частых вставок
Почему это важно:
Высота 3 = 3 чтения с диска (если нет в кэше)
На 10M строк: высота обычно 3
На 1B строк: высота обычно 4
Разница между 3ms и 4ms может стоить $500/месяц
🔥 Дедупликация в PostgreSQL 16: наконец-то не жрет место
До PG13: одинаковые значения дублировались. Колонка status с 3 значениями на 10M строк = 10M записей в индексе.
В PostgreSQL 16:
CREATE INDEX idx_orders_status ON orders(status);
-- До PG13:
-- Индекс: 180MB (10M записей × ~18 bytes)
-- PostgreSQL 16 с дедупликацией:
-- Индекс: 110MB (экономия 38%!)
-- Механизм: группирует одинаковые ключи + массив TID
Как проверить дедупликацию:
SELECT
pg_size_pretty(pg_relation_size('idx_orders_status')) as index_size,
pg_stat_get_tuples_inserted('idx_orders_status'::regclass) as inserts;
-- Если inserts >> unique values = дедупликация
работает
🐕 WoofWoof сравнение: собака vs твой индекс
Пес помнит где лежит его миска. Одна миска = одна локация в памяти.
Твой индекс БЕЗ дедупликации: 10 миллионов записей "status=pending". Это как если бы Рекс хранил 10 миллионов копий информации "миска на кухне". Песька не дебил, он помнит один раз. PostgreSQL 16 научился делать так же.
⚡ Составные индексы: порядок колонок = жизнь или смерть
Это НЕ одно и то же:
-- Вариант А:
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
-- Вариант Б:
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);
Работает оптимально:
-- Индекс А (user_id первый):
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2025-01-01';
SELECT * FROM orders WHERE user_id = 123; -- РАБОТАЕТ
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at; -- ИДЕАЛЬНО
-- Индекс Б (created_at первый):
SELECT * FROM orders WHERE created_at > '2025-01-01' AND user_id = 123;
SELECT * FROM orders WHERE created_at > '2025-01-01'; -- РАБОТАЕТ
SELECT * FROM orders WHERE user_id = 123; -- НЕ ИСПОЛЬЗУЕТ ИНДЕКС, ЕБАТЬ
Правило: первая колонка = самое частое условие WHERE. Вторая = сортировка или доп фильтр.
💩 Отмазка | Реальность: составные индексы
"Я создам два индекса: на user_id и на created_at"
Реальность: PostgreSQL может использовать только один индекс на запрос (до PG16, Bitmap Index Scan помогает, но это медленно). Два индекса = 2× места, 2× время на INSERT/UPDATE, хуевая производительность.
"Порядок не важен, PostgreSQL сам разберется"
Реальность: WHERE user_id = 123 с индексом (created_at, user_id) = Seq Scan на 5M строк. Execution time: 800ms вместо 3ms. Поздравляю, твой продакшн лег.
🎯 Partial индексы: когда не нужно индексировать все
-- Плохо: индекс на все 10M строк
CREATE INDEX idx_users_email ON users(email);
-- Размер: 450MB
-- Хорошо: только активные (20% от всех)
CREATE INDEX idx_active_users_email ON users(email)
WHERE is_active = true;
-- Размер: 90MB (экономия 80%!)
-- Скорость: та же, но только для активных
Когда использовать:
Колонка с перекосом (90% null, 10% не null)
Логическое удаление (deleted_at IS NULL)
Статусы (status IN ('pending', 'processing'))
Реальный кейс от Иванова И.И.:
-- Было:
CREATE INDEX idx_orders_all ON orders(status, created_at);
-- 10M строк, индекс 850MB
-- 95% заказов в статусе 'completed' (не интересны)
-- Стало:
CREATE INDEX idx_orders_active ON orders(status, created_at)
WHERE status IN ('pending', 'processing', 'shipping');
-- 500K строк, индекс 45MB
-- Скорость: в 3 раза быстрее (меньше страниц читать)
-- Экономия: 805MB места + 200% производительность
🔨 Expression индексы: когда нужна трансформация
-- Проблема: функция убивает индекс
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE LOWER(email) = 'test@mail.ru';
-- Index NOT USED! Seq Scan!
-- Функция LOWER() делает индекс бесполезным
-- Решение: индекс на выражение
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'test@mail.ru';
-- Index Scan! 3ms вместо 800ms
Частые кейсы:
-- Поиск по дате без времени
CREATE INDEX idx_orders_date ON orders(DATE(created_at));
-- Поиск подстроки
CREATE INDEX idx_users_domain ON users(
substring(email from '@(.*)$')
);
-- JSONB поля
CREATE INDEX idx_metadata_color ON products(
(metadata->>'color')
);
🐕 Хвостик vs твой сеньор
Собака понимает что "СИДЕТЬ" и "сидеть" — одна команда. Один паттерн в памяти.
Твой сеньор создает индекс на email, потом ищет по LOWER(email). Это как кричать песелю "СИDEТЬ" латиницей и удивляться что не работает.
PostgreSQL 16 позволяет создать индекс на LOWER(email) — научить собаку понимать команду в любом регистре. Но твой сеньор об этом не знает.
🚀 Новые оптимизации PostgreSQL 16
1. Улучшенная дедупликация:
До PG13: вообще не было
PG13-15: базовая дедупликация
PG16: умная дедупликация с posting lists
Экономия 20-40% места на индексах с повторами
2. Параллельное построение индексов:
-- В PG16 быстрее на 30-50%
CREATE INDEX CONCURRENTLY idx_huge
ON huge_table(column_name);
-- Использует все CPU cores эффективнее
3. Incremental sort оптимизация:
sql-- Запрос:
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;
-- PG16: incremental sort умнее использует частично отсортированные данные из индекса (user_id, created_at)
-- На 25% быстрее чем PG15
4. Bloom filter pushdown:
В PG16 при JOIN оптимизатор лучше предсказывает селективность индексов.
❌ Когда B-tree идет нахуй
1. Полнотекстовый поиск:
-- НЕ РАБОТАЕТ НОРМАЛЬНО:
CREATE INDEX idx_bad ON articles(content);
SELECT * FROM articles WHERE content LIKE '%postgres%';
-- Seq Scan, потому что % в начале
-- ПРАВИЛЬНО:
CREATE INDEX idx_good ON articles
USING GIN (to_tsvector('russian', content));
2. JSONB запросы:
-- B-tree НЕ ПОМОЖЕТ:
CREATE INDEX idx_fail ON products(metadata);
SELECT * FROM products
WHERE metadata @> '{"color": "red"}';
-- Seq Scan
-- Нужен GIN:
CREATE INDEX idx_win ON products
USING GIN (metadata jsonb_path_ops);
3. Геоданные:
-- B-tree бесполезен для координат
CREATE INDEX idx_wrong ON locations(lat, lng);
SELECT * FROM locations
WHERE ST_Distance(point, 'POINT(55.75 37.61)') < 1000;
-- Seq Scan
-- Нужен GiST:
CREATE INDEX idx_right ON locations
USING GIST (point);
4. Массивы:
-- B-tree не работает с оператором @>
CREATE INDEX idx_nope ON articles(tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
-- Seq Scan
-- Нужен GIN:
CREATE INDEX idx_yes ON articles USING GIN (tags);
🐕 для закрепления
У собаки есть специализация: он пастушья собака, отлично работает со стадом. Но я не заставляю его нырять за рыбой — для этого нужен лабрадор.( рыбрадор ))
B-tree — пастушья собака индексов: сортировки, диапазоны, точные поиски — охуенно. Но полнотекст, JSONB, геоданные — это не его задача. Для этого есть GIN, GiST, SP-GiST.
Ты же пытаешься заставить овчарку (B-tree) ловить рыбу (индексировать JSONB). Пес умнее — он знает свои ограничения. 🎯 Итого: чеклист для B-tree в PG16
✅ Используй B-tree для:
Диапазоны: WHERE created_at > '2025-01-01'
Сортировки: ORDER BY created_at
Сравнения: <, <=, >=, >
✅ В PG16 получаешь бонусом:
Дедупликация: -20-40% размер
Быстрее на INSERT с дедупликацией
Умнее incremental sort
Параллельное построение
❌ НЕ используй B-tree для:
LIKE '%text%' — нужен GIN + tsvector
JSONB операторы @>, ? — нужен GIN
Массивы @>, && — нужен GIN
Геоданные — нужен GiST
P.S. Проверь версию прямо сейчас:
```
SELECT version();
-- PostgreSQL 16.x = ты молодец
-- PostgreSQL 12-15 = обновляйся, получишь 20-40% экономии места
-- PostgreSQL 11 и ниже = иди обновляйся нахуй
```
Следующая часть: Hash индексы в PG16. Почему все их игнорируют и правы ли они.