Hello, On Sun, Aug 31, 2025 at 4:16 PM Andrey Borodin <[email protected]> wrote:
> > > > On 29 Aug 2025, at 13:39, Andrey Borodin <[email protected]> wrote: > > > > What if we just abort a scan, that stepped on the page where tuples were > moved out? > ... > What do you think? > We have a database on which we have bulk insertions and deletions of significant parts of the table. btree- and gist-bloat becomes a significant issue there so much that we have to resort to making ad-hoc cron-like solutions[1]. REINDEX CONCURRENTLY also sometimes crashes due to memory pressure leaving half-dead indexes behind which we have to clean up and keep reindexing until success. [2] Anything that improves the situation and makes Postgres handle this automatically would improve the experience significantly. Regarding locks: I think that baseline to compare to here is "what would happen if I had to REINDEX instead" and that is EXCLUSIVE LOCK at some point. I'd set that as a baseline for the endeavour. I think it may dramatically simplify correctness checks for the first iterations and relieve the pain for most of the cases. A similar mechanic for GiST will also be helpful. 1. https://github.com/konturio/insights-db/blob/main/scripts/reindex-bloated-btrees.sh 2. https://github.com/konturio/insights-db/blob/main/scripts/drop_invalid_indexes.sql
