Re: Patch: VACUUM should ignore (CREATE |RE)INDEX CONCURRENTLY for xmin horizon calculations

On Wed, 26 Nov 2025 at 12:07, Hannu Krosing <hannuk@google.com> wrote:
>
> So what are the options for a clean fix ?
> (the "Discussion:
> https://postgr.es/m/17485-396609c6925b982d%40postgresql.org" link
> gives 503 back so can't immediately check myself)
>
> Do we also need to make sure that CIC will not miss hot-pruned tuples
> ? What is the exact mechanism for missing them ?

The issue is CIC has a normal snapshot which would then be ignored by vacuum:

1. CIC, in second heap scan phase, gets a snapshot.
2. Another backend HOT-updates a tuple visible to CIC's snapshot, and commits.
   ^ HOT, so no insertion into the index
3. Another backend prunes the old tuple of step 2
   ^ now the tuple that CIC's snapshot should have seen is being
removed before CIC completes.
    If CIC's scan didn't scan this tuple yet, then the second scan's
guarantee of inserting all missing tuples is no longer guaranteed: the
missing tuple may have been inserted during the first heap scan phase.

> Or should we just try to have a separate frozen per-table Xmin to be
> used by CIC ?

In that case VACUUM and prune operations would have to build
visibility horizons for each table, and I don't think that's viable;
especially so in a heavily partitioned workload. It'd also be
prohibitively expensive to add a per-table Xmin -- we don't always
know which tables will be accessed by any backend until they lock that
table, but at that time they may already have a snapshot that they
need tuples from. If we lazily included that backend, there may be
visibility horizons and prune operations that were built and executed
ahead of them notifying the other backends of their use, and pruned
away still-visible tuples like in the aforementioned CIC hot pruning
issue.

If you're interested in improving CIC and reducing its impact on
visibility horizons, you may be interested in reviewing Mihail's work
in [0].


Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

[0]
https://postgr.es/m/flat/CADzfLwWrGYGE8%3Dcg%2B6C57Nypv1Y-1mBv8BVzzPWVJy5EfR6YfQ%40mail.gmail.com#4cb06452a314aa851a7dde936e817bb3