Horton

reloption to prevent VACUUM from truncating empty pages at the end of relation

Fujii Masao <masao.fujii@gmail.com>

View thread
From: Fujii Masao <masao.fujii@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-04-17T17:35:47Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add TRUNCATE parameter to VACUUM.

  2. Add vacuum_truncate reloption.

  3. Allow VACUUM to be run with index cleanup disabled.

Hi,

I'd like to propose to add $SUBJECT for performance improvement.

When VACUUM tries to truncate the trailing empty pages, it scans shared_buffers
to invalidate the pages-to-truncate during holding an AccessExclusive lock on
the relation. So if shared_buffers is huge, other transactions need to wait for
a very long time before accessing to the relation. Which would cause the
response-time spikes, for example, I observed such spikes several times on
the server with shared_buffers = 300GB while running the benchmark.
Therefore, I'm thinking to propose $SUBJECT and enable it to avoid such spikes
for that relation.

Also, first of all, if other transactions need to extend the relation
(i.e., need new pages) as soon as VACUUM truncates the empty pages at the end,
that truncation would not be so helpful for performance. In this case,
the truncation and extension of the relation are unnecessarily repeated,
which would decrease the performance. So, to alleviate this situation,
$SUBJECT is useful, I think.

Thought?

Regards,

-- 
Fujii Masao