VACUUM-related issue that I heard recently was; long transaction prevents lots of dead tuples generated during that from being vacuumed and also prevents index entries from being marked as lp_dead, which could decrease the performance of range search query using index.
Conversation
Replying to
I wonder if the Postgres 14 index deletion stuff would help. The basic rules about which tuples we can remove don't change, so you might wonder how it ever could. New code is "more forgiving" if there are only a few LP_DEAD bits set when leaf pages cannot fit additional versions.
1
1
Replying to
You mean that stuff would help reducing the number of leaf pages by avoiding "unnecessary" split in that case, and which may improve the query performance? Maybe yes, but I'm guessing it can improve only a little.
1
Because in the case, the issue was that the range search query needs to fetch lots of index entries that were deleted but can neither be vacuumed nor marked as LP_DEAD bits because of long transaction, even if leaf pages are already cached.
2
Replying to
Sometimes the problem is that it's impossible to delete any dead tuples -- no question. But when the DB has frequent long running queries, that don't take very long each time (say 5 minutes), then it may look like the same problem, even though it isn't. It's complicated.
1
This is based on a pgbench based test case from Victor Yegorov, which included many 5 minute long queries to hold back cleanup, but never "by more than 5 minutes" at any point in time:
