Content-Type: multipart/alternative;
 boundary="------------vLWPIgcga8bDIgNQJ34Q33NK"
Message-ID: <5816758c-a8e5-1076-5c9f-7e59539f0696@gmail.com>
Date: Thu, 4 Aug 2022 09:55:50 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: Determining Indexes to Rebuild on new libc
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAHJZqBBWUAzKKkTaOf_QDYtmuMavntg4ba-_ScNJRKkuxtRsyw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CAHJZqBBWUAzKKkTaOf_QDYtmuMavntg4ba-_ScNJRKkuxtRsyw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/5816758c-a8e5-1076-5c9f-7e59539f0696%40gmail.com>
Precedence: bulk

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

On 8/4/22 09:29, Don Seiler wrote:
> Good morning,
>
> As we're staring down the eventuality of having to migrate to a newer OS 
> (currently on Ubuntu 18.04 LTS), we're preparing for the collation change 
> madness that will ensue. We're looking at logical replication but there is 
> a lot to unpack there as well given the number of databases and the 
> massive size of a few of them. I had been inclined to bite the bullet and 
> do logical replication (or dump/restore on smaller DBs) but the timeframe 
> for the project is being pushed up so I'm looking for shortcuts where 
> possible (obviously without risking DB integrity). This would also give me 
> the opportunity for other modifications like enabling data checksums on 
> the new DBs that I had been sorely wanting for years now.
>
> One question that gets asked is if we could do physical replication, cut 
> over, and then only rebuild indexes that "need it" in order to minimize 
> the subsequent downtime. i.e. can we determine which indexes will actually 
> have a potential problem. For example, a lot of indexes are on 
> text/varchar datatype fields that hold UUID data and nothing more (basic 
> alphanumeric characters and hyphens embedded). If we can be certain that 
> these fields truly only hold this type of data, could we skip rebuilding 
> them after the cutover to a newer OS (eg Ubuntu 22.04 LTS with the newer 
> libc collation)?

Join pg_indexes to information_schema to get only the indices with character 
fields.

If *ALL* your UUID indices helpfully have "uuid" embedded in their names, 
then you can exclude them, too.

Otherwise, you must scan each indexed text field looking for anything 
besides low-ASCII characters, and hope that no such data has been added to 
since you scanned.

For example: SELECT field_1 from foo where field_1 >= CHR(128);

CHECK constraints would of course also ensure that it can never happen...

-- 
Angular momentum makes the world go 'round.
--------------vLWPIgcga8bDIgNQJ34Q33NK
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 8/4/22 09:29, Don Seiler wrote:<br>
    <blockquote type="cite"
cite="mid:CAHJZqBBWUAzKKkTaOf_QDYtmuMavntg4ba-_ScNJRKkuxtRsyw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Good morning,
        <div><br>
        </div>
        <div>As we're staring down the eventuality of having to migrate
          to a newer OS (currently on Ubuntu 18.04 LTS), we're preparing
          for the collation change madness that will ensue. We're
          looking at logical replication but there is a lot to unpack
          there as well given the number of databases and the massive
          size of a few of them. I had been inclined to bite the bullet
          and do logical replication (or dump/restore on smaller DBs)
          but the timeframe for the project is being pushed up so I'm
          looking for shortcuts where possible (obviously without
          risking DB integrity). This would also give me the opportunity
          for other modifications like enabling data checksums on the
          new DBs that I had been sorely wanting for years now.</div>
        <div><br>
        </div>
        <div>One question that gets asked is if we could do physical
          replication, cut over, and then only rebuild indexes that
          "need it" in order to minimize the subsequent downtime. i.e.
          can we determine which indexes will actually have a potential
          problem. For example, a lot of indexes are on text/varchar
          datatype fields that hold UUID data and nothing more (basic
          alphanumeric characters and hyphens embedded). If we can be
          certain that these fields truly only hold this type of data,
          could we skip rebuilding them after the cutover to a newer OS
          (eg Ubuntu 22.04 LTS with the newer libc collation)?</div>
      </div>
    </blockquote>
    <br>
    Join pg_indexes to information_schema to get only the indices with
    character fields.<br>
    <br>
    If <b>ALL</b> your UUID indices helpfully have "uuid" embedded in
    their names, then you can exclude them, too.<br>
    <br>
    Otherwise, you must scan each indexed text field looking for
    anything besides low-ASCII characters, and hope that no such data
    has been added to since you scanned.<br>
    <br>
    For example: SELECT field_1 from foo where field_1 &gt;= CHR(128);<br>
    <br>
    CHECK constraints would of course also ensure that it can never
    happen...<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------vLWPIgcga8bDIgNQJ34Q33NK--