A decade or two ago, VACUUM in PostgreSQL was a simple procedure that read an entire table and then removed no longer visible row versions from the table and its indexes. Every 200 million transactions, the table was traversed again to freeze old transaction numbers so the transaction counter wouldn’t overflow.
Over time, this seemingly simple procedure has been optimized. VACUUM now knows which pages to visit, both for vacuuming and freezing. Traversals can be accelerated by omitting indexes and TOAST tables. Various phases can utilize parallelization. Autovacuum now knows how to handle INSERT-only tables. Emergency freeze runs are far less intimidating than they used to be.
In this talk, we’ll explore these modern VACUUM features and show how DBAs can take advantage of the improved performance.
Thursday, September 4 at 14:10 – 14:55
Room: Maximilian
