3

On production I have database of size 150 GB. Many rows are deleted from this table and Vacuum is applied on this. Now I need to release unused space from DB to disk for OS. So Vacuum Full needs to be applied. Streaming replication is configured on production server with three secondary nodes. What is best way ?

  1. Pause replication and run Vacuum FULL on master node. And then Start replication again.
  2. Stop replication and and recreate after Vacuum FULL on master node.
Darshan Shah
  • 157
  • 1
  • 1
  • 15
  • 2
    If you are concerned about WAL generation on your replication servers then you could just recreate them after you perform the VACUUM FULL on your primary. If you aren't concerned about WAL generation then I'd say just run the VACUUM FULL and allow the secondaries to catch up via replication. Pausing would just add additional replication lag. – gsteiner Sep 01 '20 at 13:12
  • Ok gsteiner. Yes I'm concerned about WAL. as table is 150 GB in size and Vacuum Full will recreate it so huge log will be generated and this log needs to be applied on secondary also. I'm concerned that it will not create any issue for replication. – Darshan Shah Sep 01 '20 at 13:29

1 Answers1

7

You need to do nothing of the above. VACUUM (FULL) is replicated just like any other data modification.

On the standby servers the same restriction applies as on the primary server: while VACUUM (FULL) is running, the affected tables are locked to any concurrent access.

Since VACUUM (FULL) rewrites the table, it will generate a lot of WAL, so of course replication performance may be affected. But stopping replication will only delay, not reduce that, and rebuilding the standby will generate even more traffic.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks Laurenz Albe for quick response. Yes Vacuum FULL will apply automatically on standby and lock the table on staby as well during the vacuuming , but for huge database, will Vacuum FULL effect the replication due to huge WAL data? – Darshan Shah Sep 01 '20 at 13:19
  • 1
    I have added something about that to the answer. – Laurenz Albe Sep 01 '20 at 13:26