Thanks Maxim and Jeff.
1. Do you have any pointers to the killbits issue on hot standby slaves? We do use a hot standby instance for many queries. So I want to learn more about it.
2. I am now considering partitioning the table. I am curious if we can set up partitions by mutable columns. More specifically, <status, created>, where the status is mutable, and usually ends up in terminal states (success, failure or aborted).
I could not find any documentation on the performance implication of partitioning by mutable column, any guidance would be helpful. I had previously underestimated the impact of index on a mutable column, so I want to be cautious this time.
On Fri, 1 Sept 2023 at 11:02, Maxim Boguk <maxim.boguk@xxxxxxxxx> wrote:
But anyway, PostgreSQL has features to prevent the index bloat from becoming too severe of a problem, and you should figure out why they are not working for you. The most common ones I know of are 1) long open snapshots preventing clean up, 2) all index scans being bitmap index scans, which don't to micro-vacuuming/index hinting the way ordinary btree index scans do, and 3) running the queries on a hot-standby, where index hint bits must be ignored. If you could identify and solve this issue, then you wouldn't need to twist yourself into knots avoiding non-HOT updates.I am not sure that kill bits could be a complete fix for indexes with tens of millions dead entries and only a handful of live entries. As I understand the mechanics of killbits - they help to avoid excessive heap visibility checks for dead tuples, but tuples with killbit are still should be read from the index first. And with many millions of dead entries it isn't free.PS: ignoring killbits on hot standby slaves is a source of endless pain in many cases.
--Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
