thoughts on "prevent wraparound" vacuum

From: Michail Nikolaev <michail(dot)nikolaev(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: thoughts on "prevent wraparound" vacuum
Date: 2019-07-20 12:35:57
Message-ID: CANtu0oiZ5L24c3R6ZwqJzHJZp+r+bjbT=boO73+bALyUWWu+ug@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello.

Currently I am working a lot with cluster consist a few of big tables.
About 2-3 TB. These tables are heavily updated, some rows are removed, new
rows are inserted... Kind of typical OLTP workload.

Physical table size keeps mostly stable while regular VACUUM is working. It
is fast enough to clean some place from removed rows.

But time to time "to prevent wraparound" comes. And it works like 8-9 days.
During that time relation size starting to expand quickly. Freezing all
blocks in such table takes a lot of time and bloat is generated much more
quickly.

Of course after aggressive vacuum finishes table are not shrink back (some
kind of repacking required). And even after repacking - relation shrinking
causes all cluster to stuck for some time (due exclusive locking, see (1)).

So, I was thinking about it and I saw two possible solutions:

1. Track two block pointers for aggressive vacuum. One is to freeze all
blocks and other is to perform regular vacuum on non-all-visible blocks.
Second one is circular (could process table multiple times while first one
is moving from start to end of the table). And some parameters to spread
resources between pointers is required.

2. Separate "to prevent wraparound" from regular Vacuum to allow them run
concurrently. But it seems to be much more work here.

Could you please share some thoughts on it? Is it worth to be implemented?

Thanks.

[1]
https://www.postgresql.org/message-id/c9374921e50a5e8fb1ecf04eb8c6ebc3%40postgrespro.ru

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-07-20 13:22:44 Re: [PATCH] Incremental sort (was: PoC: Partial sort)
Previous Message Michael Paquier 2019-07-20 09:19:34 Re: Compiler warnings with MinGW