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

From: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, 'Amit Kapila' <amit(dot)kapila16(at)gmail(dot)com>
Cc: "Jamison, Kirk" <k(dot)jamison(at)jp(dot)fujitsu(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: reloption to prevent VACUUM from truncating empty pages at the end of relation
Date: 2019-02-01 02:05:51
Message-ID: 0A3221C70F24FB45833433255569204D1FB9150E@G01JPEXMBYT05
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

From: Jamison, Kirk [mailto:k(dot)jamison(at)jp(dot)fujitsu(dot)com]
> >On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
> wrote:
> >>
> >> On 2018-Nov-15, Laurenz Albe wrote:
> >>
> > > > This new option would not only mitigate the long shared_buffers
> > > > scan, it would also get rid of the replication conflict caused by
> > > > the AccessExclusiveLock taken during truncation, which is discussed
> > > > in
> > > >
> https://www.postgresql.org/message-id/c9374921e50a5e8fb1ecf04eb8c6eb
> > > > c3%40postgrespro.ru and seems to be a more difficult problem than
> > > > anticipated.
> > >
> > > FWIW I was just reminded yesterday that the AEL-for-truncation has
> > > been diagnosed to be a severe problem in production, and with no other
> > > solution in sight, I propose to move forward with the stop-gap.
>
> I just want to ask whether or not we could proceed with this approach for
> now and
> if it is possible that we could have this solution integrated before PG12
> development ends?

As most people seem to agree adding the reloption, here's the patch. It passes make check, and works like this:

postgres=# CREATE TABLE a (c int) WITH (shrink_enabled = off);
postgres=# INSERT INTO a VALUES(1);
postgres=# DELETE FROM a;
postgres=# SELECT pg_relation_size('a');
pg_relation_size
------------------
8192
(1 row)

postgres=# VACUUM a;
postgres=# SELECT pg_relation_size('a');
pg_relation_size
------------------
8192
(1 row)

postgres=#

As Tom said, we want to shorten the shared buffer scan during table truncation as a separate undertaking. Kirk will do it for PG 13. I'd appreciate much help from many people, because I'm afraid it will be very dificult.

And Tom mentioned likewise, I recognize I have to refresh my memory for fixing the data corruption by failed TRUNCATE...

Regards
Takayuki Tsunakawa

Attachment Content-Type Size
disable-vacuum-truncation.patch application/octet-stream 4.0 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-02-01 02:07:02 Re: tab-completion debug print
Previous Message Michael Paquier 2019-02-01 02:00:52 Re: allow online change primary_conninfo