> On 29 Aug 2025, at 13:39, Andrey Borodin <x4mmm@yandex-team.ru> 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.