Subject: Re: vacuumlo
To: pgsql-admin@lists.postgresql.org
References: 
 <DBAPR08MB5687F490FBCC6065B1F6224BC0FE9@DBAPR08MB5687.eurprd08.prod.outlook.com>
 <CAOBaU_YNAsTVHXS1UFw_XBt2YOZjwsJ3KMnZXrwqKb+86WAhpg@mail.gmail.com>
 <DBAPR08MB5687AE8E62995E251E23E26BC0CB9@DBAPR08MB5687.eurprd08.prod.outlook.com>
 <9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel@cybertec.at>
From: Mladen Gogala <gogala.mladen@gmail.com>
Message-ID: <edb97021-ca64-a0e2-0181-f1eb280c9c58@gmail.com>
Date: Mon, 30 Aug 2021 13:38:14 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel@cybertec.at>
Content-Type: multipart/alternative;
 boundary="------------A656BDAF2879F7925B1C885F"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/edb97021-ca64-a0e2-0181-f1eb280c9c58%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------A656BDAF2879F7925B1C885F
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

That can be verified by checking RELFILENODE of the table. VACUUM FULL 
changes RELFILENODE:

|mgogala=# select relfilenode from pg_class where relname='emp';
  relfilenode
-------------
        52178
(1 row)

mgogala=# vacuum full emp;
VACUUM
mgogala=# select relfilenode from pg_class where relname='emp';
  relfilenode
-------------
        52182
(1 row)

|

According to unsubstantiated rumors from 
https://www.postgresql.org/docs/13/catalog-pg-class.html, the definition 
of RELFILENODE column is as follows:

relfilenode oid

Name of the on-disk file of this relation; zero means this is a “mapped” 
relation whose disk file name is determined by low-level state

||Change of the RELFILENODE means that the relation has got a new file. 
And that means that vacuum full rewrites the table, just as Laurenz has 
said. Note that OID itself is NOT changed.
||

On 8/30/21 1:15 PM, Laurenz Albe wrote:
> VACUUM (FULL) will write a compact copy of the table, then delete the old one,
> so it temporarily needs more space.  It may be a good idea to put "pg_wal" into
> a different file system, so that WAL space cannot run out even if the data files
> fill the file system.  Then PostgreSQL won't crash, and the files will be removed.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com


--------------A656BDAF2879F7925B1C885F
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>That can be verified by checking RELFILENODE of the table. VACUUM
      FULL changes RELFILENODE:</p>
    <p><code class="structfield"><font color="blue">mgogala=# select
          relfilenode from pg_class where relname='emp';<br>
           relfilenode <br>
          -------------<br>
                 52178<br>
          (1 row)<br>
          <br>
          mgogala=# vacuum full emp;<br>
          VACUUM<br>
          mgogala=# select relfilenode from pg_class where
          relname='emp';<br>
           relfilenode <br>
          -------------<br>
                 52182<br>
          (1 row)</font><br>
        <br>
      </code><br>
    </p>
    <p>According to unsubstantiated rumors from
      <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/13/catalog-pg-class.html">https://www.postgresql.org/docs/13/catalog-pg-class.html</a>, the
      definition of RELFILENODE column is as follows:<br>
      <br>
      <font face="monospace" color="blue">relfilenode oid<br>
        <br>
        Name of the on-disk file of this relation; zero means this is a
        “mapped” relation whose disk file name is determined by
        low-level state</font><br>
    </p>
    <p><code></code>Change of the RELFILENODE means that the relation
      has got a new file. And that means that vacuum full rewrites the
      table, just as Laurenz has said. Note that OID itself is NOT
      changed.<br>
      <code class="structfield"></code></p>
    <div class="moz-cite-prefix">On 8/30/21 1:15 PM, Laurenz Albe wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:9395a02bc37b8c7a3ca09441d288ff49e1c19c39.camel@cybertec.at">
      <pre class="moz-quote-pre" wrap="">VACUUM (FULL) will write a compact copy of the table, then delete the old one,
so it temporarily needs more space.  It may be a good idea to put "pg_wal" into
a different file system, so that WAL space cannot run out even if the data files
fill the file system.  Then PostgreSQL won't crash, and the files will be removed.</pre>
    </blockquote>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------A656BDAF2879F7925B1C885F--