Re: Determining Indexes to Rebuild on new libc

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.