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

[GuDron]

Сегодня я расскажу про одну интересную особенность 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 строк. Это снизит нагрузку и ускорит выполнение.