Clear ccnew indexes

[thiamsantos]

Is your feature request related to a problem? Please describe.

Hello, in our database we are getting some "phantom ccnew" indexes on the oban jobs.
It probably appears because we run the Oban.Plugins.Reindexer frequently.

According the PostgreSQL docs:

If the index marked INVALID is suffixed ccnew, then it corresponds to the transient index created during the concurrent operation, and the recommended recovery method is to drop it using DROP INDEX, then attempt REINDEX CONCURRENTLY again. If the invalid index is instead suffixed ccold, it corresponds to the original index which could not be dropped; the recommended recovery method is to just drop said index, since the rebuild proper has been successful.

https://www.postgresql.org/docs/current/sql-reindex.html

Describe the Solution You'd Like

Would make sense to include on the Reindexer plugin a step to drop those ccnew indexes that could be added from previous runs?

Describe Alternatives You've Considered

I thought about creating a recurrent job on my application to run the same logic. As it seems to be caused by the runs of the reindexer, makes sense to me to be a part of the plugin.

[sorentwo]

There is a cleanup step built into the Reindexer for this exact situation: https://github.com/sorentwo/oban/blob/main/lib/oban/plugins/reindexer.ex#L182. It looks like the name pattern it uses doesn't match your indexes. I'm pushing a fix that matches the inverted name now.

FWIW, I don't suggest using the Reindexer unless you've identified bloat in your args or meta columns. The plugin was added to address a problem with some older PG versions accumulating bloat when indexing empty args/meta, and it shouldn't be necessary for most apps.