Hi team, We are running spicedb with cockroachdb. We just had some issues in production. Cockroachdb was reporting that ix_relation_tuple_by_subject_relation had some hot ranges. This index is defined as

INDEX ix_relation_tuple_by_subject_relation (userset_namespace ASC, userset_relation ASC, namespace ASC, relation ASC)

When checking the db we saw that the cardinality of tuples

(userset_namespace, userset_relation, namespace, relation)

was very small. It seems that cockroachdb then assigns the same page to all data with the same tuple and creates a hot tuple. We solved the incident by doing

create INDEX ix_relation_tuple_by_subject_relation_v2 on relation_tuple (userset_namespace ASC, userset_relation ASC, namespace ASC, relation ASC, object_id);

and

alter index ix_relation_tuple_by_subject_relation set not visible;

This way the index has a high cardinality key (object_id) and cockroachdb can split between different nodes. However, even though the old index is not visible, it is still maintained, so we still have a couple of nodes with high cpu. Do you foresee any problem from dropping the index? Also, are you aware in what queries this is used? We couldn't find it in the database console. Finally, would it make sense to open a pr with the change to spicedb? Cheers

what APIs does your workload mostly consist of?

I would say mostly in volume: check bulk permissions, lookup subjects, lookup resources

hmm... that is interesting. re: the cardinality issue: do you have particularly wide relations? i.e. are there small numbers of subjects with a large number of objectIDs attached to them?

in the table subject_id would be userset_object_id, right?

gotcha, so you have a lot of public resources

thanks!

@Gabriel make sure to update to the latest version of SpiceDB

Found the offending query:

SELECT
  namespace,
  object_id,
  relation,
  userset_namespace,
  userset_object_id,
  userset_relation,
  caveat_name,
  caveat_context
FROM
  relation_tuple AS OF SYSTEM TIME 1753239450000000000
WHERE
  ((userset_namespace = $1 AND userset_object_id IN ($2,) AND userset_relation = $3))
  AND namespace = $4
  AND relation = $5
ORDER BY
  userset_namespace, userset_object_id, userset_relation, namespace, object_id, relation
LIMIT
  9223372036854775807

With our data distribution and some specific parameters it was using `ix_relation_tuple_by_subject_relation`instead of

ix_relation_tuple_by_subject

> With our data distribution and some specific parameters it was using ix_relation_tuple_by_subject_relationinstead of ix_relation_tuple_by_subject with everyone's

cool, thanks