Re: Index bloat and REINDEX/VACUUM optimization for partial index

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(dot)boguk(at)gmail(dot)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
>
>