Horton

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

Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com>

View thread
From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
To: Michael Paquier <michael@paquier.xyz>, 'Masahiko Sawada' <sawada.mshk@gmail.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>, Laurenz Albe <laurenz.albe@cybertec.at>, "Jamison, Kirk" <k.jamison@jp.fujitsu.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Amit Kapila <amit.kapila16@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Fujii Masao <masao.fujii@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2019-02-28T00:48:18Z
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.

Attachments

From: Michael Paquier [mailto:michael@paquier.xyz]
> This makes the test page-size sensitive.  While we don't ensure that tests
> can be run with different page sizes, we should make a maximum effort to
> keep the tests compatible if that's easy enough.  In this case you could
> just use > 0 as base comparison.  I can fix that by myself, so no need to
> send a new version.

Good idea.  Done.


> Should we also document that the parameter is effective for autovacuum?
> The name can lead to confusion regarding that.

I'm not sure for the need because autovacuum is just an automatic execution of vacuum, and existing vacuum_xxx parameters also apply to autovacuum.  But being specific is good anyway, so I added reference to autovacuum in the description.


> Also, shouldn't the relopt check happen in should_attempt_truncation()?
> It seems to me that if we use this routine somewhere else then it should
> be controlled by the option.

That makes sense.  Done.


> At the same time, we also have REL_TRUNCATE_FRACTION and
> REL_TRUNCATE_MINIMUM which could be made equally user-tunnable.
> That's more difficult to control, still why don't we also consider this
> part?

I thought of it, too.  But I didn't have a good idea on how to explain those parameters.  I'd like to separate it.


> Another thing that seems worth thinking about is a system-level GUC, and
> an option in the VACUUM command to control if truncation should happen or
> not.  We have a lot of infrastructure to control such options between vacuum
> and autovacuum, so it could be a waste to not consider potential synergies.

Being able to specify this parameter in postgresql.conf and SET (especially ALTER DATABASE/USER to target specific databases/applications) might be useful, but I'm not sure...  I'm less confident about whether VACUUM command can specify this, because this is a property of table under a specific workload, not a changable property of each VACUUM action.  Anyway, I expect it won't be difficult to add those configurability without contradicting the design, so I'm inclined to separate it.


From: Masahiko Sawada [mailto:sawada.mshk@gmail.com]
> Yeah, that would work. Or it's kind of hackie but the rolling back the
> insertion instead of INSERT and DELETE might also work.

That's good, because it allows us to keep running reloptions test in parallel with other tests.  Done.


Regards
Takayuki Tsunakawa