Subject: Re: reindexing db
To: pgsql-admin@lists.postgresql.org
References: <292966DF-135C-4483-81A9-5027FC3C8A82@elevated-dev.com>
 <f477709e-6371-1ba3-84df-37e5df8e5d83@gmail.com>
 <214E5DBC-1F6F-49C1-8B83-9A2EB33E969A@gmail.com>
 <39a8cdb5-bfbb-9ae8-5867-c4234373277c@gmail.com>
 <A54CA469-6EAA-40AD-AD37-7E877268786A@gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <98c19d44-8d4c-f608-5432-eef048965552@gmail.com>
Date: Tue, 8 Dec 2020 01:42:44 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <A54CA469-6EAA-40AD-AD37-7E877268786A@gmail.com>
Content-Type: multipart/alternative;
 boundary="------------1ACBB74B8C9EE8F66C7BB950"
Content-Language: en-US
Precedence: bulk

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


On 12/8/20 1:21 AM, Paul Förster wrote:
> Hi Ron,
>
>> On 08. Dec, 2020, at 07:59, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> Probably a habit from my VAX/VMS days, but I prefer to enumerate every table, not only for tracking but so that if the command fails 90% of the way through, I can skip over the the completed portions.
> doing this IMHO defeats the purpose of concurrency because you'd have to issue reindex statements sequentially. Also, but I definitely can't say for sure, reindexdb should skip and report indexes which it has a problem with and then continue to do the remaining indexes. But as I said, I may be wrong with this.

The CONCURRENTLY clause is about rebuilding *an index* online instead of 
blocking all other access to the table, not about reindexing multiple tables 
in parallel.

Thus, it's perfectly valid to write something like

DB=test
while read line;
do
     psql $DB "REINDEX TABLE $line CONCURRENTLY;"
done < list_of_tables.txt

You might also be able to play with GNU Parallel and concurrently run 
multiple "REINDEX TABLE $line CONCURRENTLY;" statements.

-- 
Angular momentum makes the world go 'round.

--------------1ACBB74B8C9EE8F66C7BB950
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>
    <br>
    <br>
    <div class="moz-cite-prefix">On 12/8/20 1:21 AM, Paul Förster wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:A54CA469-6EAA-40AD-AD37-7E877268786A@gmail.com">
      <pre class="moz-quote-pre" wrap="">Hi Ron,

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 08. Dec, 2020, at 07:59, Ron <a class="moz-txt-link-rfc2396E" href="mailto:ronljohnsonjr@gmail.com">&lt;ronljohnsonjr@gmail.com&gt;</a> wrote:

Probably a habit from my VAX/VMS days, but I prefer to enumerate every table, not only for tracking but so that if the command fails 90% of the way through, I can skip over the the completed portions.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
doing this IMHO defeats the purpose of concurrency because you'd have to issue reindex statements sequentially. Also, but I definitely can't say for sure, reindexdb should skip and report indexes which it has a problem with and then continue to do the remaining indexes. But as I said, I may be wrong with this.</pre>
    </blockquote>
    <br>
    The CONCURRENTLY clause is about rebuilding <b>an index</b> online
    instead of blocking all other access to the table, not about
    reindexing multiple tables in parallel.<br>
    <br>
    Thus, it's perfectly valid to write something like<br>
    <br>
    DB=test<br>
    <tt>while read line; <br>
      do <br>
          psql $DB "REINDEX TABLE $line CONCURRENTLY;" <br>
      done &lt; list_of_tables.txt</tt><br>
    <br>
    You might also be able to play with GNU Parallel and concurrently
    run multiple <tt>"REINDEX TABLE $line CONCURRENTLY;" statements.</tt><br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------1ACBB74B8C9EE8F66C7BB950--