Postgresql

---
name: postgresql
description: Глубокая работа с PostgreSQL — проектирование схемы и типов, индексы (btree/GIN/GiST/BRIN/partial/covering), чтение планов EXPLAIN ANALYZE и оптимизация запросов, транзакции/изоляция/блокировки, JSONB и полнотекстовый поиск, партиционирование, репликация, пулы соединений и миграции без простоя. Use при работе с Postgres, проектировании схемы или оптимизации SQL.
---

# Навык: PostgreSQL

Реляционная БД по умолчанию для сильносвязанных данных с транзакциями и сложными запросами.

## Схема и типы
- Правильные типы: `text` вместо `varchar(n)` без нужды, `timestamptz` (не `timestamp`), `numeric` для денег, `uuid`, `boolean`, `enum`/`domain`.
- Ограничения на уровне БД: `NOT NULL`, `CHECK`, `UNIQUE`, внешние ключи с `ON DELETE`. Данные защищает схема, а не приложение.
- Генерируемые столбцы, `DEFAULT`, идентификаторы `GENERATED ALWAYS AS IDENTITY`.

## Индексы
- **btree** — равенство/диапазоны/сортировка (дефолт). **GIN** — `jsonb`, массивы, полнотекст. **GiST** — гео/диапазоны. **BRIN** — огромные append-only по времени.
- **Составной** индекс: порядок столбцов = порядок фильтрации (leftmost prefix). **Partial** (`WHERE`) — под горячий срез. **Covering** (`INCLUDE`) — index-only scan.
- Индексируй столбцы из `WHERE`/`JOIN`/`ORDER BY`; не плоди лишние (замедляют запись). `CREATE INDEX CONCURRENTLY` — без блокировки таблицы.

## Оптимизация запросов
- `EXPLAIN (ANALYZE, BUFFERS)` — читай снизу вверх: **Seq Scan** на большой таблице, **Nested Loop** с большим rows, расхождение estimated/actual → проблема.
- Убирай **N+1** (JOIN/`= ANY($1)` вместо цикла), избегай `SELECT *`, функций по индексируемому столбцу (`WHERE lower(email)=` → индекс по выражению).
- `ANALYZE`/автовакуум для актуальной статистики; пагинация по keyset (`WHERE id > $last`) вместо больших `OFFSET`.

## Транзакции, изоляция, блокировки
- Уровни: `READ COMMITTED` (дефолт) → `REPEATABLE READ` → `SERIALIZABLE`. Выше уровень — больше сериализационных ошибок (готовь retry).
- `SELECT ... FOR UPDATE`/`FOR NO KEY UPDATE` — явные блокировки строк; следи за порядком захвата (deadlock).
- Короткие транзакции; никакого внешнего I/O внутри транзакции.

## JSONB и поиск
- `jsonb` для полуструктурированных данных; операторы `->`, `->>`, `@>`, индекс `GIN (col jsonb_path_ops)`.
- Полнотекст: `tsvector`/`tsquery` + GIN; для fuzzy — `pg_trgm`.

## Масштаб
- **Партиционирование** (declarative, по диапазону/списку/хэшу) для больших таблиц — прунинг партиций ускоряет запросы, упрощает архивацию.
- **Репликация**: стриминг-реплики для чтения; разноси read/write. Расширения: `PostGIS`, `pg_stat_statements` (поиск медленных запросов), `TimescaleDB` для time-series.

## Пулы и миграции
- Пул соединений обязателен (**PgBouncer**/пул драйвера); Postgres плохо переносит тысячи коннектов.
- Миграции без простоя по схеме **expand → migrate → contract**: сначала добавь nullable-столбец/индекс `CONCURRENTLY`, задеплой код, потом бэкфилл, затем `NOT NULL`/удаление старого. Не переименовывай столбцы в один шаг.

## Антипаттерны
- Логика/индексы «в приложении» вместо БД; отсутствие FK; `OFFSET` для глубокой пагинации; `text`-хранение денег/времени; один гигантский индекс на всё; долгие транзакции.

## Через MCP
MCP-сервер `postgres` (read-only) — для инспекции: смотри схему, гоняй `EXPLAIN ANALYZE`, проверяй индексы и данные без правок. Изменения схемы/данных — только миграциями с подтверждением человека.