Hey guys, I'm running the migration from SpiceDB 1.13.0 to 1.14.0 using the incremental migrations. I'm running into some super long migration times on the add-xid-constraints step (more than 1h with 4M tuples so the migration aborts). Locally I tested the migration with around 2M entries and it takes less than 5 minutes. My suspicion is that in production we've incrementally increased Postgres from v12 to v14 and some indexes are not quite correct. I've reindexed the tuples table but it still seems like it takes a long time. The explain analyse on the query

select * from relation_tuple WHERE (namespace, object_id, relation, userset_namespace, userset_object_id,     userset_relation, created_transaction, deleted_transaction) IN (SELECT namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, created_transaction, deleted_transaction FROM relation_tuple WHERE created_xid IS NULL LIMIT 1000);

shows me an execution time of 20 second per migration update step with limit 1000 (so that would take a very long time to finish...). Anyone seen anything similar or any advice?

cc @Joey

@Francisco Correia I believe someone else reported this, and their solution was to add an index to support the query, but I don't recall the exact one to add. doing an

EXPLAIN ANALYZE

on the query might yield some insights

We managed to rewrite the query so that it is significantly faster and does the same migration but I'm not sure if it's possible to use it in the scope of this migration. About that index do you have any link to that thread?