🧩 Как сделать backup PostgreSQL с минимальной нагрузкой на прод?

Сегодня покажу один из самых эффективных способов бэкапа PostgreSQL — с помощью pg_basebackup + реплики.

Сценарий: у нас есть продовый PostgreSQL и настроенная горячая реплика (streaming replication). Зачем использовать реплику для бэкапа?

Причины:
- 💡 На проде бэкап может замедлить отклик приложения.
- 🔁 Реплика — отличный способ разгрузить основной сервер.
- ⏱ Бэкап с pg_basebackup возможен только на стопнутой БД или через репликацию.

Как сделать:

pg_basebackup -h replica.host -U repl_user -D /backup/pg -F tar -z -P

Пояснения:
- -h — адрес реплики
- -U — пользователь с правами репликации
- -D — куда класть бэкап
- -F tar -z — формат архива и сжатие
- -P — прогресс в консоли

Важно:
Пользователь repl_user должен быть прописан в pg_hba.conf и иметь роль REPLICATION.

А если добавить в cron, то получишь стабильный ночной бэкап без боли.

#db

👉 @database_info

🚀 Сегодня покажу, как быстро диагностировать «тормоза» в PostgreSQL — без всяких внешних тулов и дополнительных логов. Только pg_stat_activity и немного здравого смысла.


🔍 Проблема: пользователи жалуются — "всё тормозит". Как понять, что именно?

💡 Решение: открываем сессию в psql от суперпользователя и запускаем:

SELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

📌 Что это нам даёт:
- Видим все активные (и зависшие) запросы.
- Сколько времени они уже выполняются (duration).
- На чём конкретно «висят»: CPU, IO, Lock, Client и т.д. (wait_event_type + `wait_event).

Пример:

wait_event_type: Lock
wait_event: relation

→ Сразу ясно: кто-то держит блокировку на таблицу, и все остальные ждут.


🔥 Бонус: чтобы найти виновника, можно запустить:

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
  AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
  AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
  AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
  AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
  AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
  AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
  AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Этот запрос покажет, кто кого блокирует, и с каким запросом.

🙌 Это простая, но мощная техника диагностики. Помогала мне не раз в проде — особенно, когда времени мало, а багов много.

Ты пользуешься pg_stat_activity в проде? Или сразу лезешь в лог? Расскажи в комментах!


#db

👉 @database_info

Как быстро найти “тяжёлые” запросы в PostgreSQL

Сегодня покажу простой способ найти самые ресурсоёмкие запросы, которые прямо сейчас выполняются в PostgreSQL. Это помогает, когда база начинает “тормозить”, а понять почему — сложно.

Используем pg_stat_activity и pg_stat_statements. Но сначала убедись, что pg_stat_statements включён:

-- Проверка:
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- Включение (если не установлен):
CREATE EXTENSION pg_stat_statements;

Теперь сам запрос на поиск “тяжёлых” запросов:

SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 5;

А если интересует то, что прямо сейчас выполняется — тогда так:

SELECT
pid,
now() - query_start AS duration,
state,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
ORDER BY
duration DESC;

Я часто сохраняю эти запросы в отдельный .sql-файл, чтобы запускать сразу при проблемах с производительностью. Полезно добавить в .psqlrc алиас или даже обернуть в скрипт.

Как вы ищете “тяжёлые” запросы в проде? Поделитесь в комментариях.

#db

👉 @database_info

VACUUM FULL — когда, зачем и с какими рисками

Сейчас поговорим про одну из самых «страшных» команд в арсенале DBA — VACUUM FULL. Она часто спасает, когда база разрослась до неприличных размеров… но может и «уронить» прод, если запустить не вовремя.

Что делает VACUUM FULL?

Он освобождает табличное пространство, физически удаляя "мертвые" строки и переписывая всю таблицу заново. Это не просто очистка, а именно перезапись. Поэтому:

- Таблица полностью блокируется на запись и чтение.
- Используется временное дисковое пространство (вплоть до размера таблицы).
- Может существенно сократить размер базы — особенно, если давно не было очистки.

Когда применять:

- После массового удаления данных.
- Когда обычный VACUUM не помогает уменьшить размер базы.
- При миграции/переезде базы, чтобы максимально уменьшить backup.

Чего опасаться:

- На больших таблицах — это может занять часы.
- Блокировки = пользователи «висят».
- Нужно много свободного места на диске.

Альтернатива: если задача — просто освободить место и база под нагрузкой, рассмотрите pg_repack. Он позволяет делать реорганизацию без блокировок (но требует отдельной установки).

Лично я использую VACUUM FULL только в окне обслуживания или на read replica.

А вы? Когда последний раз делали VACUUM FULL?

#db

👉 @database_info

🧵 Сегодня я покажу вам простой, но мощный способ отладки сложных SQL-запросов

Когда у вас в проекте появляется монструозный запрос с десятками джойнов, подзапросов и оконных функций — ловить ошибки становится больно. Но есть подход, который реально спасает: инкрементальная отладка.

💡 Суть: разбиваем запрос на небольшие части и поочередно проверяем каждую

Вот как это делаю я:

1. Начинаю с ядра — самого внутреннего подзапроса или CTE. Проверяю, что он возвращает ожидаемые данные.
2. Добавляю следующий уровень логики — джойны, условия, группировки. Каждый раз выполняю и проверяю результат.
3. Для удобства использую WITH (CTE) — это даёт имена промежуточным результатам и делает запрос читабельным.
4. Сложные выражения и агрегаты выношу в отдельные CTE — это помогает быстрее изолировать проблему.
5. Если запрос очень тяжёлый — сохраняю промежуточные результаты в временные таблицы.

🔥 PostgreSQL позволяет использовать EXPLAIN (ANALYZE, BUFFERS) для профилирования на каждом этапе. Очень помогает найти, где тормозит.

Если хотите, могу на неделе разобрать один такой "тяжёлый" запрос от подписчика. Скиньте в комменты 👇

#db

👉 @database_info

⚠️ Антипаттерн: использовать NULL без оглядки

На первый взгляд NULL — это просто “нет значения”. Но в реальности — это тихий саботаж:

🔸 NULL != NULL. Да-да, сравнение NULL = NULL даст false или unknown. Это ломает привычную логику и может убить фильтры.

🔸 Агрегации ведут себя странно. COUNT(column) не считает NULL'ы. AVG, SUM — тоже их игнорируют. Итог: неверная статистика.

🔸 Индексы и WHERE column IS NULL. Не все СУБД эффективно используют индексы при таких запросах. Можно словить тормоза.

🔸 NOT IN + NULL = 💥. Запрос WHERE id NOT IN (subquery) может вернуть пустой результат, если в подзапросе есть хотя бы один NULL.

💡 Как избежать проблем:

1. Всегда осознанно работай с NULL — используй IS NULL и IS NOT NULL, не = и !=.
2. По возможности избегай NULL в колонках, где это не нужно. Лучше использовать значения по умолчанию.
3. Добавляй проверки в коде: COALESCE, IFNULL, NVL и аналоги.
4. Понимай, как твоя СУБД работает с NULL в индексах и фильтрах.

🎯 Вывод: NULL — не пустота, а “неизвестность”. Обращайся с ним осторожно, иначе баги будут неявными и неприятными.

Сохрани, чтобы не отловить баг на проде 🐛

#db

👉 @database_info

🎯 Мини-гайд
Транзакции в SQLite: просто, но со своими нюансами

SQLite — это встраиваемая база данных, и она немного отличается от привычных серверных СУБД (PostgreSQL, MySQL) в части работы с транзакциями. Но транзакции там есть, и работают по принципу ACID — атомарность, согласованность, изолированность и долговечность.

Разберёмся по полочкам:


🔹 Как начинается и заканчивается транзакция?

BEGIN TRANSACTION;
-- какие-то запросы
COMMIT;

Или, в случае ошибки:

ROLLBACK;

Можно использовать синонимы:
- BEGIN = BEGIN DEFERRED
- BEGIN IMMEDIATE
- BEGIN EXCLUSIVE

Они отличаются уровнем блокировок.


🔹 Типы транзакций

1. DEFERRED (по умолчанию)
🔒 Блокировки ставятся только при первом доступе к таблице (на чтение/запись).

2. IMMEDIATE
🔒 Сразу ставит блокировку на запись (write-lock). Полезно, если точно знаешь, что будешь писать — исключишь гонки.

3. EXCLUSIVE
🔒 Блокирует БД полностью. Даже другие чтения не пройдут.


🔹 Особенности SQLite

- Одна запись за раз: SQLite поддерживает одновременные чтения, но только одну запись одновременно. Остальные получат "database is locked".
- Авто-коммиты: если явно не начать транзакцию — SQLite будет делать коммит после каждого запроса.
- Журналирование: SQLite использует WAL (write-ahead log) или rollback journal — в зависимости от настроек. WAL — более производителен для параллельного чтения.


💡 Советы

- При пакетной вставке всегда оборачивай в транзакцию:

BEGIN;
INSERT INTO users VALUES (...);
INSERT INTO users VALUES (...);
...
COMMIT;

→ Это в разы быстрее, чем отдельные INSERT с автокоммитом.

- Если получаешь ошибку database is locked, проверь:
- Не оставил ли ты открытые транзакции
- Не работают ли несколько процессов с БД одновременно без координации


💬 Вывод: транзакции в SQLite — простые, но критически важные для производительности и корректности. Даже в одиночной БД нужна дисциплина.

Сохрани, чтобы не потерять!

#db

👉 @database_info

🚨 SELECT * - скрытый враг в проде

На dev-сервере всё шустро. В проде — беда: запросы висят, база потеет. И вроде бы всё ок... пока не заглянешь в SQL:

SELECT * FROM users WHERE status = 'active';

На первый взгляд — удобно. Но:

🔻 Проблемы “SELECT *”:
– Тянет все колонки, даже ненужные. А их может быть 30+.
– Увеличивает нагрузку на сеть и память приложения.
– Ломает кэш — ведь даже малейшие изменения в колонках меняют структуру результата.
– Убивает индекс-only scan: Postgres не может использовать покрывающий индекс, если явно не указаны поля.


✅ Как надо:

🎯 Выбирай только нужные поля:

SELECT id, name, email FROM users WHERE status = 'active';

💡 Хочешь “быстро протестить” в dev-е? Ок. Но не пускай такое в прод. Автоматизируй линтинг SQL, если надо.


Вывод:
SELECT * — это не “удобно”, это дорого. И ты за него уже платишь.

Сохрани, чтобы не словить боль в проде.
А у тебя где последний раз встречалось SELECT *?

#db

👉 @database_info

Антипаттерн: NULL в WHERE — и ты в ловушке

Когда в таблице есть NULL, а в WHERE ты пишешь что-то вроде:

SELECT * FROM users WHERE age != 30;

Ты ожидаешь, что выберутся все, кто не 30.
Но если age IS NULL — такие строки пропадут из выборки!

Почему? Потому что NULL != 30 не TRUE, это UNKNOWN.
А SQL возвращает строки только там, где WHERE → TRUE.

Как избежать?

1. Будь явно осторожен с NULL:

   SELECT * FROM users 
   WHERE age != 30 OR age IS NULL;
   

2. Логика на уровне схемы:
– Если поле нужно всегда — делай NOT NULL.
– Если допускаешь NULL, продумывай поведение выборок.

3. Не верь глазам своим:
Даже count(*) и count(column) ведут себя по-разному из-за NULL.

Вывод:
NULL — это не ноль, не пустая строка и не "ничего".
Это "мы не знаем". И SQL ведёт себя с ним очень осторожно.

Сохрани, чтобы не словить грабли.

#db

👉 @database_info

🚨 Как понять, почему запрос тормозит?

Сегодня покажу простой, но действенный подход к диагностике медленных SQL-запросов. Когда к тебе приходит прод с жалобой "что-то всё виснет", важно не паниковать, а системно подойти к анализу.

Вот что я делаю первым делом:

1. Включаю EXPLAIN (ANALYZE)
Это ваш лучший друг. Не EXPLAIN, а именно ANALYZE, чтобы получить реальные значения времени, а не план на бумаге.

2. Смотрю на узлы с наибольшим временем
Часто виновник — Seq Scan по большой таблице или Nested Loop с миллионами итераций.

3. Ищу несработавшие индексы
Был ли Index Scan или Index Only Scan? Если нет — стоит проверить, почему не сработал индекс. Может, фильтр не селективный?

4. Проверяю фильтрацию и сортировку
ORDER BY может убить всё. Особенно если не по индексу.

5. Думаю про статистику
ANALYZE делали недавно? PostgreSQL может строить плохой план, если у него устаревшие данные.


🛠 Если ты часто отлаживаешь SQL — советую поставить pgMustard или использовать EXPLAIN DEPOT. Они визуализируют планы и сразу показывают узкие места.

💬 Расскажи, как ты ищешь узкие места в запросах? Есть свой лайфхак?

#db

👉 @database_info