When VACUUM decides which rows are safe to freeze or permanently remove it currently ignores backends which have PROC_IN_VACUUM or PROC_IN_LOGICAL_DECODING bits set.
This patch adds PROC_IN_SAFE_IC to this set, so backends running CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY and where the index is "simple" - i.e. not expression indexes or conditional indexes are involved - these would be ignored too. The reasoning behind this is simple: 1) Why this is safe: a) The vacuum operation can not run on the same table that vacuum is working on because of locks. b) The CIC operation only runs on a single table in one transaction, so it can not touch other tables 2) Why this is useful: CIC can take significant amount of time, and in case of high-traffic database with vacuum cleanups blocked a significant amount of dead rows can accumulate which can have significant impact on certain workloads. The worst affected are the ones that are considered anti-patterns anyway, like updatein a single counter row from all DML, but this can work "well enough" if all the DML transactions are tiny and and the performance can be maintained between vacuum runs by just setting the deleted flags in indexes and heap which currentlyis also blocke. Future improvements It would be good to do some more introspection to determine if the CIC skipping is also safe for specifioc cases of expression and conditional indexes which are currently excluded from setting the PROC_IN_SAFE_IC flag. --- Hannu
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 200f72c6e25..2e321219e8d 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -1775,8 +1775,12 @@ ComputeXidHorizons(ComputeXidHorizonsResult *h) * Skip over backends either vacuuming (which is ok with rows being * removed, as long as pg_subtrans is not truncated) or doing logical * decoding (which manages xmin separately, check below). + * Also skip over backends doing (RE)INDEX CONCURRENTLY on plain + * indexes as these also can not affect vacuum on other tables and + * we don't want long indexing operations to hold back vacuum cleanup */ - if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING)) + if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING + | PROC_IN_SAFE_IC)) continue; /* shared tables need to take backends in all databases into account */
