Francisco Correia

03/10/2023, 1:02 AM
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?


03/10/2023, 10:20 PM
cc @Joey


03/10/2023, 10:46 PM
@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
on the query might yield some insights

Francisco Correia

03/11/2023, 5:30 PM
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?
The indexes that exist seem pretty optimal. Especially because I can't reproduce the problem locally. The
Index Scan using ix_relation_tuple_by_subject
is the one that seems to be taking way too long. I posted the picture of the migration query and an equivalent formulation using a join instead of an IN.