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

[jayaprabhakar k <jayaprabhakar@xxxxxxxxx>]

Thanks Peter. It is 14.4, But on AWS RDS Aurora instance. I am trying to read the links you shared - B-Tree Deletion and deduplication, etc. I still don't fully understand what I need to do. In the BTree documentation,

> The average and worst-case number of versions per logical row can be kept low purely through targeted incremental deletion passes. It's quite possible that the on-disk size of certain indexes will never increase by even one single page/block despite constant version churn from UPDATEs.

In our case, almost all the tuples stop being covered by the index as they fail the predicate, and only a tiny 1000s of rows pass the index predicate at any point in time. But, we still see the index size continue to increase, index lookups become slow over time, and  vacuum (non full) doesn't reduce the index size much.

Do we need to do anything specific to better utilize the targeted incremental deletion passes?

SELECT VERSION();
                                             version                                            
-------------------------------------------------------------------------------------------------
 PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit
(1 row)

On Mon, 28 Aug 2023 at 18:49, Peter Geoghegan <pg@xxxxxxx> wrote:

On Mon, Aug 28, 2023 at 5:33 PM jayaprabhakar k <jayaprabhakar@xxxxxxxxx> wrote:
> REINDEX requires a full table scan
>
> Roughly create a new index, rename index, drop old index.
> REINDEX is not incremental. running reindex frequently does not reduce the future reindex time.

You didn't say which Postgres version you're on. Note that Postgres 14
can deal with index bloat a lot better than earlier versions could.
This is known to work well with partial indexes. See:

https://www.postgresql.org/message-id/flat/CAL9smLAjt9mZC2%3DqBeJwuNPq7KMAYGTWWQw_hvA-Lfo0b3ycow%40mail.gmail.com

--
Peter Geoghegan