New vacuum option to do only freezing
Masahiko Sawada <sawada.mshk@gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-10-01T10:20:53Z
Lists: pgsql-hackers
Attachments
- v1-0001-Add-FREEZE_ONLY-option-to-VACUUM-command.patch (application/x-patch) patch v1-0001
Hi, Attached patch adds a new option FREEZE_ONLY to VACUUM command. This option is same as FREEZE option except for it disables reclaiming dead tuples. That is, with this option vacuum does pruning HOT chain, freezing live tuples and maintaining both visibility map and freespace map but does not collect dead tuples and invoke neither heap vacuum nor index vacuum. This option will be useful if user wants to prevent XID wraparound a table as quick as possible, especially when table is quite large and is about to XID wraparound. I think this usecase was mentioned in some threads but I couldn't find them. Currently this patch just adds the new option to VACUUM command but it might be good to make autovacuum use it when emergency vacuum is required. This is a performance-test result for FREEZE option and FREEZE_ONLY option. I've tested them on the table which is about 3.8GB table without indexes and randomly modified. * FREEZE INFO: aggressively vacuuming "public.pgbench_accounts" INFO: "pgbench_accounts": removed 5 row versions in 8 pages INFO: "pgbench_accounts": found 5 removable, 30000000 nonremovable row versions in 491804 out of 491804 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 722 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 4.20 s, system: 16.47 s, elapsed: 50.28 s. VACUUM Time: 50301.262 ms (00:50.301) * FREEZE_ONLY INFO: aggressively vacuuming "public.pgbench_accounts" INFO: "pgbench_accounts": found 4 removable, 30000000 nonremovable row versions in 491804 out of 491804 pages DETAIL: freeze 30000000 rows There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 3.10 s, system: 14.85 s, elapsed: 44.56 s. VACUUM Time: 44589.794 ms (00:44.590) Feedback is very welcome. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Commits
-
Add toast-level reloption for vacuum_index_cleanup
- ce59b75d449d 12.0 landed
-
heap_prepare_freeze_tuple: Simplify coding
- 2bf372a4ae26 12.0 landed
-
Fix missing word.
- 6665305e17ed 12.0 landed
-
Allow VACUUM to be run with index cleanup disabled.
- a96c41feec6b 12.0 landed
-
Don't mark pages all-visible spuriously
- d2599ecfcc74 11.0 cited