Rebuilding invalid indexes

I just upgraded a 1.8 million post site to 2.7.0.beta2 and with it the PG10 to PG13 upgrade. I saw that beta3 just came out and has “improved database migration performance” so I went ahead and upgraded again.

When I try to do a reindex concurrently, I get

WARNING:  cannot reindex invalid index "public.allowed_pm_users_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_allowed_pm_users_on_user_id_and_allowed_pm_user_id_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_allowed_pm_users_on_allowed_pm_user_id_and_user_id_ccnew" concurrently, skipping

I was going to drop and reindex those, but I don’t see any ccnew in the current source, so I’m not sure what to do.

1 Like

Looks like the _ccnew index is part of how the concurrent reindex happens:

  • Create a new index in the catalogs which is a copycat of the one reindexed (with some exceptions, for example partition indexes don’t have their inheritance dependency registered at creation, but at swap time). This new, temporary is suffixed with “_ccnew”. Bref.

Can you reindex those indexes without the concurrently keyword?

or, from later in that post:

Then, REINDEX TABLE CONCURRENTLY will skip invalid indexes because in the event of successive and multiple failures then the number of indexes would just ramp up, doubling at each run, causing a lot of bloat on the follow-up reindex operations:

It is however possible to reindex invalid indexes with just REINDEX INDEX CONCURRENTLY:

1 Like

_ccnew are indexes that were tried to be created by a previous reindex concurrently but couldn’t be done, usually because they are violating a uniqueness check. The failed attempts to reindex concurrently will sit in there and should be dropped manually.

The second time you run reindex concurrently, PostgreSQL will skip those failure artifacts.

2 Likes

Thanks Rafael! That did it. I dropped those indexes and did another concurrent reindex with no issues.

1 Like