New vacuum option to do only freezing

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: New vacuum option to do only freezing
Date: 2018-10-01 10:20:53
Message-ID: CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Add-FREEZE_ONLY-option-to-VACUUM-command.patch application/x-patch 18.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-10-01 10:42:53 Re: de-deduplicate code in DML execution hooks in postgres_fdw
Previous Message Amit Langote 2018-10-01 10:20:29 Re: executor relation handling