We have a number of tables that have an active version, an inactive version, and a view pointing at the active one. We periodically truncate the inactive table, drop the indexes, load a bunch of data into the table, re-build the indexes and redefine the view to point to the newly loaded table. This is reasonably standard data warehouse stuff.
The issue we're running into is that we are unable to use index-only scans on the freshly-loaded tables after our script runs due to the visibility map not being updated. I tried adding a
VACUUM step to our script, using pg_background. I've tried both
VACUUM ANALYZE and
VACUUM FREEZE ANALYZE, but neither will update the visibility map if there's a transaction that started before the
The only workaround I have right now is to fail out of the script if
pg_class.relallvisible = 0 for the backing table after the
VACUUM FREEZE, but this is not ideal. Are there any other ways to force the visibility map to update?
To be clear, there will be no user activity on the inactive table until the script updates the view definition, so I'm free to do pretty much anything that I want to it, but performance is somewhat of an issue (e.g, I would not want to
VACUUM FULL) and my scripts can't hold any locks that would prevent selects on other tables.