Всем привет. Кто как борется с большим увеличением записи при заполнении

hint bits?
wiki.postgresql.org/wiki/Hint_Bits

В общих словах симптомы такие.
Есть таблица, в которую идёт постоянный upsert небольшими батчами. Доля update = 70% от insert, а батчи с update и insert всегда в разных транзакциях.
И если в неё только писать, то запись на диск минимальна.

Но стоит включить на этой таблице select-запросы по последним записанным данным, так сразу утилизация полосы записи на диск возрастает в ~15 раз.
По pg_stat_statements видно, что select сопровождается большим количеством dirtied-страниц (12% от read + hit, сами read и hit примерно равны). Однако прирост полосы записи на диск составляет не +12%, а 15 раз.

Какой процесс в основном пишет (по iotop):
wal_writer + checkpointer в разных соотношениях, но ещё и постгресовые читающие бэкэнды.
Но если bgwriter накрутить в активное состояние, то он становится безусловным лидером по объёму записи на диск. Конечно постгресовые читающие бэкэнды перестают при этом писать.

Если глянуть pg_waldump -z, то наибольший Combined size = 14% приходится на Type = XLOG.

Может кто посоветует какие доп. средства для диагностики?

Но если это из-за hint bits, то как можно снизить этот эффект?
Понятно, что вынос читающих запросов на реплику может решить проблему, но хочется более системно подойти к проблеме, а не лечить симптомы.

вакуум поагрессивнее, чтобы успевал хинты проставить обратно до того как страницы на диск упали

😻 спасибо. А такое сильное увеличение записи - нормально?

Ну если горячий датасет в шаред буфферс не влазит, то да, придётся жонглировать

Коллеги, я правильно понял, что SELECT может порождать dirtied-страницы? Я полагал, что только update/delete/truncate на это способны Буду благодарен, если тыкнете в документацию

Нет, select не порождает грязные страницы. Но может вызвать запись на диск в определенных случаях. Посмотрите здесь https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f или на Хабре цикл статей Егора Рогова для полного понимания ситуации

Спасибо Как раз обдумывал как же объяснить разработчику что тысяча апдейтов по одной строчке убивает систему И тут натыкаюсь на нечто мне самому неизвестное

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

Да, правильно.

Это по ссылке в сообщении и можете увидеть экспериментально

Поток апсертов никогда не заканчивается. Но как вариант подходит, конечно

Именно что порождает, но при определенных условиях. В Вашей ссылке это тоже указано. Попробуйте и увидите сами..

Я не до конца точно выразился. Селект может пометить страницу как грязную при определенных условиях, но изменения данных (порождение грязных страниц) он не делает