erquhart•11mo ago

Indexing on arrays

I have a lot of very small join documents in dedicated tables that would work perfectly as arrays of nested objects. They're naturally limited to fit the recommendation of 10 or less nested docs. I can't use this approach, though, as indexing isn't supported. It would be amazing to have index support on arrays. I could drop a quarter of my tables and a whole lot of relational machinery.

12 Replies

Indy•11mo ago

Fascinating. Just so I understand the use case: You have something like: - Person and who has 5 favorite books - Right now you have a separate book table and some other table that keeps track of favorites - Instead you want to just have the 5 book records inlined in an array in the person record - But now you can't index on say, book title or book author ?

erquhartOP•11mo ago

Close, I would keep the books in their own table, but inline the favorites. Index is needed if say I want to paginate through all users that have favorited the book, so a dedicated favorites table is required.

users: defineTable({
  favoriteBooks: v.optional(v.array(v.id('book'))),
  ...otherFields,
})

users: defineTable({
  favoriteBooks: v.optional(v.array(v.id('book'))),
  ...otherFields,
})

I have two use cases - arrays of ids and arrays of objects that include ids.

Indy•11mo ago

Ok yea, makes sense. I'll discuss with the low level db experts here 🙂 It does seem super convenient.

erquhartOP•11mo ago

Convex champions the nested docs approach, this would be a game changer for nested docs viability at scale.

Indy•11mo ago

So you can index into nested documents. Arrays are tricky I think.

erquhartOP•11mo ago

Yeah no doubt on that, just hadn't seen much conversation on this and wanted to offer my use case. I remember paginated subscriptions being tricky as well before Convex lol y'all made it seem too easy

Michal Srb•11mo ago

I'm curious @erquhart about the product use cases where you know you won't need more than 10 relations - can you share what they are?

erquhartOP•11mo ago

@Michal Srb I can DM

james•11mo ago

arrays are generally somewhat of an anti-pattern in relational modeling since they allow adding a sneaky other layer of relations outside of the schema, i.e., the one-to-many relation from the document to each element in the array the obvious way for us to add indexing into an array mostly ends up looking the same as a document with many relations under the covers, since there needs to be an index entry for each element in the array. basically this is an inverted index into the document for now i'd prefer not to add this feature and instead have folks double down on relational modeling. if indexing or per-document overhead gets too expensive i'd love to try to optimize that first of course there's nothing really wrong with using array fields, especially when it's much more convenient, provided it doesn't get large or we need to index into it if this makes pagination awkward then we may want to look into improving our pagination support

erquhartOP•11mo ago

they allow adding a sneaky other layer of relations outside of the schema

This is absolutely what I'm going for lol, although I'm not sure how they fall outside the schema. To your point, though, if relationships were more turnkey I probably wouldn't be reaching for this. I'll have a better informed take once I give Ents a try.

Gorka Cesium•4mo ago

i just stumbled on this case. Is

erquhartOP•4mo ago

Yes.

Indexing on arrays

Did you find this page helpful?