Rebuilding invalid indexes

AI-generated summary

The discussion revolves around reindexing invalid indexes after upgrading a Discourse site from PostgreSQL 10 to 13. pfaffman encountered warnings about indexes with _ccnew suffixes during a reindex concurrently operation, which are temporary indexes created during concurrent reindexing. supermathie explains that these _ccnew indexes are part of PostgreSQL’s concurrent reindex process and are skipped if invalid. Falco clarifies that _ccnew indexes result from failed attempts to create unique indexes and must be manually dropped. pfaffman confirms that dropping the invalid indexes resolved the issue, allowing a successful concurrent reindex.

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.

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:

_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.

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