WAL efficient alternative to VACUUM FULL



  • Performing a VACUUM FULL produces a lot of WAL. We need downtime to perform this as well. What if we manually created a new table and copied over all the data. Then we could simply drop the original table and rename the new table? Would this create much fewer WAL entries? Could this be potentially faster?

    Our example is that we're starting to migrate large jsonb columns out of tables. So the result is a significant reduction in size.

    We're not able to use pg_repack because we're using Heroku and they don't support this extension.



  • Creating and dropping is basically equivalent to VACUUM FULL. The lock will be held for less time, but that is because changes not locked out during the copy will be discarded when the drop is done, so that is probably not much of a win. If the new table is a tiny fraction of the old one, the WAL generated (either way) should be small.

    The most efficient alternative is probably not to get in a situation where VACUUM FULL is needed in the first place. Knowing how you got here could be important to getting out of it. For example, maybe just reindexing some of the indexes would be sufficient.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2