pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDetermining Indexes to Rebuild on new libc
5+ messages / 4 participants
[nested] [flat]
* Determining Indexes to Rebuild on new libc
@ 2022-08-04 14:29 Don Seiler <don@seiler.us>
2022-08-04 14:55 ` Re: Determining Indexes to Rebuild on new libc Ron <ronljohnsonjr@gmail.com>
2022-08-04 15:03 ` Re: Determining Indexes to Rebuild on new libc Jim Mlodgenski <jimmy76@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Don Seiler @ 2022-08-04 14:29 UTC (permalink / raw)
To: pgsql-admin
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)?
Thanks,
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Indexes to Rebuild on new libc
2022-08-04 14:29 Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
@ 2022-08-04 14:55 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ron @ 2022-08-04 14:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Indexes to Rebuild on new libc
2022-08-04 14:29 Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
@ 2022-08-04 15:03 ` Jim Mlodgenski <jimmy76@gmail.com>
2022-08-04 15:48 ` Re: Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
1 sibling, 1 reply; 5+ messages in thread
From: Jim Mlodgenski @ 2022-08-04 15:03 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
On Thu, Aug 4, 2022 at 10:29 AM Don Seiler <don@seiler.us> 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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Indexes to Rebuild on new libc
2022-08-04 14:29 Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
2022-08-04 15:03 ` Re: Determining Indexes to Rebuild on new libc Jim Mlodgenski <jimmy76@gmail.com>
@ 2022-08-04 15:48 ` Don Seiler <don@seiler.us>
2022-08-04 16:01 ` Re: Determining Indexes to Rebuild on new libc Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 5+ messages in thread
From: Don Seiler @ 2022-08-04 15:48 UTC (permalink / raw)
To: Jim Mlodgenski <jimmy76@gmail.com>; +Cc: pgsql-admin
On Thu, Aug 4, 2022 at 10:03 AM Jim Mlodgenski <jimmy76@gmail.com> wrote:
>
> 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;
>
>
Looks like you're right. Testing with PG 12.11 pgdg packages for their
respective Ubuntu versions:
18.04:
testdb=# SELECT * FROM t1 ORDER BY c1;
c1
------
1a
1-a
1-aa
(3 rows)
22.04:
testdb=# SELECT * FROM t1 ORDER BY c1;
c1
------
1-a
1a
1-aa
(3 rows)
So it looks like we'd have to rebuild any UUID index to be safe anyway,
which are used a lot for identifiers. *sadness intensifies*
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Determining Indexes to Rebuild on new libc
2022-08-04 14:29 Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
2022-08-04 15:03 ` Re: Determining Indexes to Rebuild on new libc Jim Mlodgenski <jimmy76@gmail.com>
2022-08-04 15:48 ` Re: Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
@ 2022-08-04 16:01 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Ribe @ 2022-08-04 16:01 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: Jim Mlodgenski <jimmy76@gmail.com>; pgsql-admin
> On Aug 4, 2022, at 9:48 AM, Don Seiler <don@seiler.us> wrote:
>
> So it looks like we'd have to rebuild any UUID index to be safe anyway, which are used a lot for identifiers. *sadness intensifies*
Actually, given that collation change, I'd be hesitant to assume ANY text index was safe. Names, addresses, part #s... All can have hyphens.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-08-04 16:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-04 14:29 Determining Indexes to Rebuild on new libc Don Seiler <don@seiler.us>
2022-08-04 14:55 ` Ron <ronljohnsonjr@gmail.com>
2022-08-04 15:03 ` Jim Mlodgenski <jimmy76@gmail.com>
2022-08-04 15:48 ` Don Seiler <don@seiler.us>
2022-08-04 16:01 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox