2

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.

2
  • Why not use an "online" tool like pg_squeeze or pg_repack
    – user1822
    May 20, 2022 at 11:05
  • Thanks for pointing this out, we're using Heroku and they don't support these extensions.
    – Todd
    May 20, 2022 at 17:36

1 Answer 1

1

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.

3
  • It's interesting because we ran a test on a fork of our db and saw a lot of WAL during VACUUM FULL and I didn't understand why. So I thought creating a new table and dropping the old could potentially have less WAL. We're migrating a large jsonb column out of the table.
    – Todd
    May 20, 2022 at 17:37
  • 1
    VACUUM FULL will log the entire new table and all of it indexes to WAL, so that could be substantial.
    – jjanes
    May 20, 2022 at 19:47
  • Note that if you drop a column, the space will not be reclaimed by VACUUM (FULL). Perhaps that is the problem here. May 23, 2022 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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