Postgres Pro
Downloads
Home   >   mailing lists

Re: Index bloat and REINDEX/VACUUM optimization for partial index - Mailing list pgsql-performance

From Peter Geoghegan
Subject Re: Index bloat and REINDEX/VACUUM optimization for partial index
Date
Msg-id CAH2-Wz=Szg0BZfuFW=ixj-mQQOSAgCHdS9kv+Lvvxv5M_JLS_A@mail.gmail.com
Whole thread Raw
In response to Index bloat and REINDEX/VACUUM optimization for partial index  (jayaprabhakar k <jayaprabhakar@gmail.com>)
Responses Re: Index bloat and REINDEX/VACUUM optimization for partial index
List pgsql-performance
Tree view 
On Mon, Aug 28, 2023 at 5:33 PM jayaprabhakar k <jayaprabhakar@gmail.com> 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

pgsql-performance by date:

Previous
From: jayaprabhakar k
Date:
Subject: Index bloat and REINDEX/VACUUM optimization for partial index
Next
From: Philippe Pepiot
Date:
Subject: Range partitioning query performance with date_trunc (vs timescaledb)