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.bo...@gmail.com> 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 > >
