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

>
> 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