Первым шагом к масштабной оптимизации является понимание вашего узкого места, которым в данном случае является время. Что заставляет обновление каждой строки в таблице из десятков миллионов масштабироваться экспоненциально? Причиной является конструкция MVCC Postgres. Postgres никогда не модифицируется...
Когда дело доходит до инженерии данных, масштаб имеет большое значение. Обновление одной строки небольшой таблицы SQL, состоящей из десяти-тысячи строк, не вызывает никаких проблем. Но как только вы начнете работать с десятками миллионов строк и вам придется обновлять их все, простая команда SQL UPDATE станет неэффективной. Оно превращает минуты в часы.
Первым шагом к масштабной оптимизации является понимание вашего узкого места, которым в данном случае является время. Что заставляет обновление каждой строки в таблице из десятков миллионов масштабироваться экспоненциально? Причиной является конструкция MVCC Postgres.
Что на самом деле делает UPDATE в MVCC
Postgres никогда не изменяет строку на месте. Каждое UPDATE записывает новую версию строки и помечает старую версию как мертвую. Такова цена MVCC: читатели получают согласованные снимки без блокировки авторов, за счет того, что старые версии остаются до тех пор, пока ВАКУУМ не очистит их.
Для одного ОБНОВЛЕНИЯ это невидимо. Для миллионов UPDATE в одной и той же таблице это суммируется. Каждое UPDATE добавляет на страницы таблицы мертвый кортеж, и каждый индекс таблицы получает новую запись, указывающую на новую версию строки, в то время как старая запись остается до VACUUM.
По мере накопления мертвых кортежей:
Каждая страница таблицы содержит меньше оперативных данных.