We are searching the best solution for one to many and many to many relations in CockroachDB. We got relatively large tables (~3 billion rows -> in our Messages table).
One to many: we have to list parents (threads) where child (messages) rows match the query. A single join costs >100-300ms for our tables:
select parent.* from parent join child on parent.id = child.parentId where child.a = 1 and child.b = 2 etc…
(Right now they are not interleaved tables, but as I read the documents, interleaved table joins somehow have a little performance hit.)
As we testing it, sometimes two separate queries have better performance than a join. Is there a better join solution for it?
Many to many: we have to add labels to messages. (Label * <—> * Message)
There’s of course a many-to-many join table solution, but your JOIN implementation hasn’t got the best performance yet (or we do it the wrong way).
The ARRAY type would be a good solution, if we could index an ARRAY field, but we can’t.
We could use your JSONB datatype in beta version, but we have to filter for normal columns and this JSONB column as well. But CockroachDB can use 1 index for a query so we can’t do:
select … where intColumn = 2 and jsonbColumn ?& array[‘label1’, ‘label2’]
2 together: there are queries where we would like to query only the parents, where their children have to match different filters including a many to many query. If we have the best solutions for the first two, then of course we can mix them.
Are there any best practice or recommendation for these cases?
Thanks for any help!