Thread: Vacuum related question
Hello All!
I'm currently using 17.2 version and working on optimizing vacuums.
I would be greateful if you could confirm (or deny) my idea.
Current situation:
Every 4-5 days vacuum to prevent wraparound is running in my DB, which of course is bad. There are a few tables that are constantly being updated (both inserts and updates) - they are quite big - ranging from a few hundred million to over a billion rows.
Other tables get only inserts (with similar size - they may reach over a billion rows).
Solution:
I would like to have a quick vacuum to freeze old tuples - it would be quick because it would omit indexes (because switch INDEX_CLEANUP would be set as false). Additionally for tables with dead tuples I would run vacuum with INDEX_CLEANUP set as true during weekend. Omitting indexes makes a huge difference in terms of vacuum time.
Would it be a problem from performance point of view (e.g. accumulating some bloat?) if tables that do not contain dead tuples are vacuumed only with flag INDEX_CLEANUP set as false? I have a feeling that it is fine, but unfortunately I couldn't find any clear evidence when browsing Internet.
Thanks in advance,
Bartek
I'm currently using 17.2 version and working on optimizing vacuums.
I would be greateful if you could confirm (or deny) my idea.
Current situation:
Every 4-5 days vacuum to prevent wraparound is running in my DB, which of course is bad. There are a few tables that are constantly being updated (both inserts and updates) - they are quite big - ranging from a few hundred million to over a billion rows.
Other tables get only inserts (with similar size - they may reach over a billion rows).
Solution:
I would like to have a quick vacuum to freeze old tuples - it would be quick because it would omit indexes (because switch INDEX_CLEANUP would be set as false). Additionally for tables with dead tuples I would run vacuum with INDEX_CLEANUP set as true during weekend. Omitting indexes makes a huge difference in terms of vacuum time.
Would it be a problem from performance point of view (e.g. accumulating some bloat?) if tables that do not contain dead tuples are vacuumed only with flag INDEX_CLEANUP set as false? I have a feeling that it is fine, but unfortunately I couldn't find any clear evidence when browsing Internet.
Thanks in advance,
Bartek