🧠 Почему PostgreSQL иногда "зависает" на UPDATE и как это пофиксить

Сегодня я расскажу про одну интересную особенность PostgreSQL, с которой сталкивался лично: внезапные подвисания при UPDATE большого количества строк. Причём CPU почти не загружен, а запрос как будто "висит".

📌 Проблема часто кроется в отсутствии индекса на колонку фильтра в WHERE. Пример:

UPDATE orders SET status = 'archived' WHERE created_at < '2022-01-01';

Если на created_at нет индекса, то PostgreSQL делает sequential scan всей таблицы. А теперь внимание: если в таблице много "мертвых" строк, которых ещё не убрал autovacuum, то PostgreSQL должен:

1. Прочитать кучу ненужных версий строк (MVCC).
2. Проверять видимость каждой строки.
3. Иногда ещё и ждать завершения других транзакций, держащих старые снапшоты.

🛠 Что делать:
- Проверить наличие индекса на колонку фильтра:

  CREATE INDEX idx_orders_created_at ON orders(created_at);
  

- Проверить состояние autovacuum:

  SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
  

- Можно вручную запустить:

  VACUUM ANALYZE orders;
  

🔥 Лайфхак: если UPDATE всё равно медленный, попробуй его разбить на батчи по 10 000 строк. Это снизит нагрузку и ускорит выполнение.


#db

👉 @database_info

🧹 VACUUM FULL — спасение от bloated-таблиц или тихий враг?

Сегодня хочу поговорить о том, что многие DBA используют как "универсальную таблетку" — VACUUM FULL. Но стоит ли?

💡 Что делает VACUUM FULL:
Он полностью перебирает таблицу и создаёт её заново, убирая все мёртвые строки. Результат — таблица становится компактной, как после дефрагментации. Это помогает, если у тебя:

- Много UPDATE или DELETE
- Таблица разрослась до абсурдных размеров
- Autovacuum не справляется

🔎 Как понять, что таблица bloated:

SELECT
  schemaname, relname, 
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  (pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_overhead
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Если table_size сильно больше, чем объём живых данных — у тебя блоат.

⚠️ Осторожно: VACUUM FULL = эксклюзивная блокировка!

Пока он работает:
- Все запросы к таблице ждут
- Могут “залипать” аппы
- В проде без окон — 💥

🛠 Что делать вместо:
- Пробовать pg_repack — он делает примерно то же, но без блокировок
- Делать регулярный autovacuum + мониторинг
- Разбивать массивные UPDATE/DELETE на батчи

VACUUM FULL — это инструмент, который стоит использовать осознанно. Как хирургический скальпель — мощный, но не для каждодневного применения. А ты когда последний раз делал VACUUM FULL в проде? 😏

#db

👉 @database_info

📊 Зачем DBA нужно уметь читать планы выполнения запросов (EXPLAIN)?

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

Когда приходит запрос от разработчика: "Почему тормозит?" — ты открываешь EXPLAIN (ANALYZE, BUFFERS) и видишь:

Seq Scan on users  (cost=0.00..44231.00 rows=1000000 width=64)
  Filter: (status = 'active')

И тут всё понятно: фильтрация идёт по колонке без индекса, Postgres делает полный проход по таблице. Один CREATE INDEX — и запрос летит 🚀

Но не всё так просто. Иногда план говорит:

Index Scan using idx_users_status on users
  Index Cond: (status = 'active')

А запрос всё равно медленный. Почему?

➡️ Buffers: shared hit=5 read=100000 dirtied=0 — вот оно. Индекс-то используется, но данные не в кэше, приходится читать с диска. А диск медленный. Решение? Подумать о горячем кэше, пачке RAM или REINDEX, если индекс раздулся.

Каждый EXPLAIN — как рентген. Не читаешь — лечишь наугад.

#db

👉 @database_info

Как работают джойны SQL?

На приведенной ниже схеме подробно показано, как работают 4 типа объединений SQL.

🔹INNER JOIN возвращает совпадающие строки в обеих таблицах
🔹LEFT JOIN возвращает все записи из левой таблицы и соответствующие записи из правой
🔹RIGHT JOIN возвращает все записи из правой таблицы и соответствующие записи из левой
🔹FULL OUTER JOIN возвращает все записи, в которых есть совпадения, в левой или правой таблице

#db

👉 @database_info

Сегодня расскажу вам про одну часто недооценённую, но крайне полезную SQL-фишку — CROSS APPLY в SQL Server (и его аналог в других СУБД — LATERAL).

Когда обычный JOIN бессилен

Допустим, у нас есть таблица Orders, и мы хотим для каждой строки выбрать топ-1 продукт по сумме, но выборка зависит от строки — тут уже обычный JOIN не справится. Вот пример, где приходит на помощь CROSS APPLY:

SELECT 
    o.OrderID,
    p.ProductName,
    p.Amount
FROM Orders o
CROSS APPLY (
    SELECT TOP 1 *
    FROM Products p
    WHERE p.OrderID = o.OrderID
    ORDER BY p.Amount DESC
) p;

Что делает CROSS APPLY?

Он буквально говорит: «Для каждой строки из Orders выполни подзапрос с её параметрами». Это похоже на foreach, где внутренняя выборка может меняться в зависимости от строки внешней таблицы.

Аналог в PostgreSQL:

SELECT 
    o.order_id,
    p.product_name,
    p.amount
FROM orders o,
LATERAL (
    SELECT *
    FROM products p
    WHERE p.order_id = o.order_id
    ORDER BY p.amount DESC
    LIMIT 1
) p;

🔥 Используйте CROSS APPLY, когда:
- Нужна подстрочная логика внутри запроса
- Не получается реализовать через обычный JOIN
- Вы работаете с функциями, которые возвращают таблицу (TVF)


#db

👉 @database_info

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

📌 Если у вас база под нагрузкой, и “что-то всё стало медленно”, первым делом проверьте:

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

Этот запрос показывает топ-5 самых долгих активных запросов. Обратите внимание на query_start — именно он поможет понять, кто завис и тормозит остальных.

А если хотите посмотреть историю медленных запросов за последние часы/дни — подключайте pg_stat_statements:

SELECT 
  calls, 
  total_time, 
  mean_time, 
  query 
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

🔍 Тут видно, какие запросы в сумме "съели" больше всего времени. И это гораздо честнее, чем смотреть только на mean_time или calls по отдельности.

💡 Совет: подключите pg_stat_statements на проде и делайте такой анализ хотя бы раз в неделю. Это поможет находить проблемные места в приложении до того, как начнётся пожар.

#db

👉 @database_info

🎯 Сегодня покажу простой способ ускорить запросы в PostgreSQL, даже не трогая сам SQL-код.

Часто вижу, как разработчики и админы оптимизируют запросы, играя с индексами или переписывая JOIN'ы. Но забывают про один мощный инструмент — ANALYZE.

ANALYZE обновляет статистику по таблицам. Эта статистика — хлеб для планировщика запросов. Если она устарела, PostgreSQL может выбрать неэффективный план, даже если у вас всё индексировано как надо.

👨‍🔧 Простой пример:

ANALYZE my_big_table;

Запускаешь — и вдруг сложный JOIN срабатывает в разы быстрее. Потому что PostgreSQL теперь знает, какие там объемы данных, сколько уникальных значений в колонках и т.п.

🧠 Совет: если ты регулярно заливаешь данные в таблицы (например, через ETL или бэкапы) — добавь ANALYZE в конец процедуры. Это дёшево, но может дать мощный прирост производительности.

Можно даже так:

VACUUM ANALYZE my_big_table;

Так ты и "мусор" уберёшь, и статистику обновишь за один проход.


#db

👉 @database_info

🧩 Как сделать 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