0

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 VACUUM.

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.

2

You can set old_snapshot_threshold and then wait out that amount of time before retrying the VACUUM.

(Note that this is a last resort. First resort would be just to identify the long running connections, and making them go away if they are not really needed, or downgrading their isolation level).

  • Thanks for the suggestion. I tried setting old_snapshot_threshold to 1 minute (and restarted postgres) and waiting for 1 minute, 2 minutes, and 125 seconds, but it didn't seem to help the situation. I'll keep trying a few more options, but at the moment it seems a bit less predictable than I would hope. – Jeremy Sep 19 at 12:46
  • I tried it myself, and it worked for me. Could you describe that the hanging around connections are doing? – jjanes Sep 19 at 16:20
  • The other transactions are doing basically the same thing, with other tables. That is: truncating a table, dropping indexes, loading it with data, creating indexes, vacuuming, then changing a view definition to point to the new table. The data that are inserted are coming from a foreign server, using a foreign data wrapper, which may add some complication. Did you have to add any additional time beyond the value of old_snapshot_threshold? – Jeremy Sep 19 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.