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.