В ноябре был на конференции Highload++, о которой подробно писал в телеграм-канале.

Было много хороших тезисов про работу с БД, на одном из докладов приводился топ 10. Ниже — углублённый разбор каждой рекомендации: что именно имел в виду спикер и как это применять в реально работе.

1️⃣ Коннекшены — это ограниченный ресурс

Суть:

Подключение к базе — это не просто TCP-сокет. Это память, кэш, контекст транзакции и иногда рабочие потоки в СУБД. Неограниченное открытие коннектов легко приводит к too many connections.

Практика:

Kubernetes-сервис без лимитов на пул: при пике каждый pod открыл 100 соединений → PostgreSQL дошёл до лимита и начал отвечать ошибками.

Как решать:

  • В Go: db.SetMaxOpenConns, db.SetMaxIdleConns, db.SetConnMaxLifetime.
  • Используйте прокси‑пулеры (PgBouncer) в режиме transaction pooling для большого количества коротких соединений.
  • Мониторьте количество active/idle conns (Prometheus метрики драйвера или pg_stat_activity).

Шаблон (Go):

1
2
3

db.SetMaxOpenConns(50)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(30 * time.Minute)

2️⃣ 1 connection = 1 транзакция в один момент времени

Суть:

Транзакция «привязывает» состояние к коннекту. Если вы начинаете BEGIN и ждёте — этот коннект занят. Под нагрузкой занятые коннекты быстро приводят к депрессии пула.

Практика:

Транзакции открывались заранее, чтобы потом по коду делать дополнительные SELECT’ы — под нагрузкой 90% пула было в состоянии idle in transaction.

Как решать:

  • Держите транзакции минимальными: BEGIN → DML → COMMIT/ROLLBACK.
  • Никогда не вызывайте внешние I/O (HTTP, heavy CPU) внутри транзакции.
  • Если надо координировать внешние вызовы — рассматривайте outbox pattern.

Outbox схема (упрощённо):

  1. В транзакции пишем данные + запись в outbox таблицу.
  2. Commit.
  3. Отдельный воркер читает outbox и шлёт HTTP/сообщения.

3️⃣ Через connection надо постоянно отправлять запросы

Суть:

Idle транзакции и «пустые» ожидания — беда: они держат locks, блокируют VACUUM и тянут пул.

Практика:

ORM автоматически открывала транзакцию на весь HTTP‑запрос. Когда логика делала несколько вызовов внешних сервисов — транзакции висели десятки миллисекунд. Под пиковой нагрузкой это накопилось в серьёзное торможение.

Как решать:

  • Избегайте авто‑транзакций вокруг всего запроса.
  • Используйте явные короткие транзакции.
  • Включите idle_in_transaction_session_timeout в Postgres, чтобы ловить просроки.

4️⃣ Нельзя делать HTTP‑запросы в транзакционном контексте

Суть:

Внешние вызовы ненадёжны и медленны. Если транзакция ждёт внешнего ответа — она удерживает ресурсы в БД.

Практика:

В одном сервисе в транзакции делали вызов биллинга. Биллинговый сервис стал отвечать 3s вместо 200ms — база начала деградировать.

Как решать:

  • Outbox (см. выше).
  • События после commit (eventual consistency).
  • Компенсирующие транзакции, если нужен синхронный эффект.

Шаблон для outbox (SQL):

1
2
3
4
5

BEGIN;
UPDATE orders SET status='paid' WHERE id=?;
INSERT INTO outbox (...) VALUES (...);
COMMIT;
-- после commit отдельный воркер идет в биллинг

5️⃣ Медленные запросы мешают быстрым

Суть:

Один «тяжёлый» запрос (full table scan, сложные сортировки, агрегации) забирает CPU / IO / Memory и может вытеснить быстрые запросы. Это особенно критично на репликах и при общем ресурсном пуле.

Практика:

Аналитический запрос на 40 млн строк в проде запускал temp‑файлы на диске и занимал 60% CPU. Обычные API запросы начали расти в latency.

Как решать:

  • Используйте EXPLAIN (ANALYZE, BUFFERS).
  • Установите statement_timeout для user / role в Postgres.
  • Переносите аналитические запросы на реплику или в аналитическую базу (ClickHouse, DWH).
  • Материализованные представления, предварительные агрегаты, batch jobs.

6️⃣ Нужно добавить индексы

Суть:

Индексы — мощный инструмент, но с побочными эффектами: они ускоряют чтение, замедляют запись и занимают место.

Практика:

Вероятно, каждый сталкивался с медленными запросами из-за отсутствия или неоптимального индекса. Про кейс, когда композитный индекс помог значительно ускорить запрос, писал в другой статье.

Как решать:

  • Делайте индексы по реальным паттернам WHERE/JOIN/ORDER BY.
  • Предпочитайте composite индексы, если запрос использует несколько колонок.
  • Убедитесь, что порядок колонок в composite соответствует WHERE и ORDER BY.
  • Удаляйте неиспользуемые индексы (pg_stat_user_indexes поможет).

Шаблон (SQL):

Плохой вариант:

1
2

WHERE user_id = ? AND created_at > ?
-- отдельные индексы -> может не использоваться

Оптимальный вариант:

1

CREATE INDEX idx_user_created ON events (user_id, created_at DESC);

7️⃣ Есть запросы, которые можно просто переписать — OR/IS NULL и др.

Суть:

Операторы OR, IS NULL, NOT IN и др. часто ломают использование индексов. Иногда простая алгебра запросов даёт огромный выигрыш.

Типичные паттерны:

  • OR по индексируемым полям → full scan. Можно переписать в UNION ALL или использовать COALESCE.
  • NOT IN → лучше NOT EXISTS.
  • LEFT JOIN → INNER JOIN, если гарантирован match.

Шаблон (SQL):

Плохо:

1

WHERE user_id = 10 OR user_id IS NULL

Лучше:

1
2
3
4
5

WHERE COALESCE(user_id, 10) = 10
-- или
WHERE user_id = 10
UNION ALL
SELECT ... WHERE user_id IS NULL AND ...

8️⃣ Лучше заранее проектируйте API под keyset pagination

Суть:

Offset пагинация деградирует с ростом offset: СУБД вынуждена пробежать и отбросить OFFSET N строк.

Практика:

У сервиса хранилось ~90M событий, API выдавал 50 элементов через /events?page=N, offset на странице 1000 = offset 50 000.

Время нарастает линейно.

Как решать:

Keyset (seek) — стабильный и быстрый способ. Это пагинация не по позиции (offset), а по значению поля, которое уже есть в индексе.

Имеет смысл использовать для лент, логов, таблиц с большим количеством строк.

Шаблон (SQL)

1
2
3

WHERE (created_at, id) < (last_created_at, last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

9️⃣ При проектировании БД обратите особое внимание на JSON

Суть:

JSON даёт гибкость, но скрывает схему. Частые ошибки: хранить активные поля в JSON и затем ожидать быстрых запросов по ним.

Как решать:

  • JSONB хорош для нечасто фильтруемых/поисковых данных.
  • Если по полям JSON нужно фильтровать часто — вынесите их в столбцы (normalization).
  • Для JSONB используйте GIN индексы для поиска по ключам: CREATE INDEX ON table USING GIN (data jsonb_path_ops).
  • Помните про размер и bloat; часто лучше явная колонка.

Пример:

Если user.preferences->'emails' постоянно используется в WHERE — вынесите pref_emails в отдельный столбец и индексируйте.

🔟 Не делайте типичных ошибок — делайте нетипичные

Суть:

Следовать шаблонам — хорошо, но прод‑системы требуют адаптации под нагрузку и модель данных. Иногда нестандартное решение выигрывает.

Примеры «нетипичных» подходов:

  • Шардинг по пользователю для уменьшения contention.
  • CQRS: раздельные модели для записи и чтения.
  • Использование materialized views + background refresh вместо тяжёлых агрегаций.

Но: не изобретайте велосипед без причин. Профайлинг и метрики сначала.

Запомнить

  1. Ограничивайте пул коннектов, ставьте PgBouncer если нужно.
  2. Транзакция = один коннект; держите транзакции короткими.
  3. Не держите коннект в простое и не делайте внешние вызовы внутри транзакции.
  4. Вынесите HTTP в outbox/после commit.
  5. Аналитика и тяжёлые запросы — на реплику или в DWH.
  6. Индексы — подумайте перед созданием; composite > набор отдельных.
  7. Переписывайте OR/NOT/IS NULL в эквивалент, чтобы сохранить индекс.
  8. Планируйте keyset pagination заранее.
  9. JSON — удобно, но не для часто фильтруемых полей.
  10. Профилируйте, измеряйте, затем применяйте «нетипичные» архитектурные приёмы.

Читайте другие посты из мира Биг Теха и присоединяйтесь к обсуждению в Телеграм: @time2code