Re: Determining Indexes to Rebuild on new libc

[Jim Mlodgenski <jimmy76@xxxxxxxxx>]

On Thu, Aug 4, 2022 at 10:29 AM Don Seiler <don@xxxxxxxxx> wrote:

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)?

I don't think you can make that assumption with the UUID data. Try this simple example and you can see the sort order changes on the newer OS and can lead to duplicates on your primary key if you don't reindex after the upgrade.

CREATE TABLE t1 (c1 varchar PRIMARY KEY);
INSERT INTO t1 VALUES ('1-a'), ('1a'), ('1-aa');
SELECT * FROM t1 ORDER BY c1;